Excel Charts
Sum to Target
Solve the subset sum problem with pure Excel formulas. Three approaches from step-by-step breakdown to a single 275-character formula using SEQUENCE, BASE, MMULT, and FILTER.
Dynamic Hyperlinks
Formula-driven hyperlinks that auto-update when sheets are renamed or moved. Includes a Navigator pattern used as a standard in SumProduct financial models.
Percentage Change Chart
Column chart with year-over-year variance arrows and percentage labels. Uses invisible series, positive/negative splits, and error bars for the connectors.
Marimekko Chart
Variable-width stacked bar chart for market share and product mix analysis. 7 combined chart objects create a consulting-grade Mekko visualisation.
Bubble Pies Chart
Overlay mini pie charts on a bubble chart to show market positioning, relative scale, and sector composition simultaneously. Based on Andy Pope's technique.
Waffle Chart
10×10 grid percentage visualisation with simple (single value) and stacked (A/B/C categories) variants. Uses SEQUENCE and conditional formatting.
Horizontal RainCloud Chart
Distribution visualisation combining density curves, box plots, and scatter plots. 34 chart objects create 1-cloud and 3-cloud comparison layouts.
Vertical RainCloud Chart
Vertical orientation of the RainCloud chart with 27 chart objects. Includes 1-cloud and 3-cloud layouts with density, box plot, and scatter layers.
Point & Figure + Bollinger Bands
Classic P&F technical analysis chart built from MSFT stock data with Bollinger Band overlays. X/O columns with formula-driven bin width and switch detection.
Christmas Tree Generator
Festive Excel creation with randomised Unicode ornaments, conditional formatting lights, sugar canes, and presents. Press F9 to regenerate!
Excel Tips and Tricks
VBA Code
fn_Sum_by_Color
Sum cells whose background color matches a reference cell. Ideal for color-coded financial models.
fn_Range_Has_Notes
Returns a TRUE/FALSE array indicating which cells have legacy comments (Notes) attached.
fn_Extract_Comments_Thread
Extract all threaded comments and replies into a structured table with Cell, Author, Date, and Text columns.
fn_NMATCH
Like MATCH but finds the Nth occurrence of a value. Supports array inputs for lookup value and instance number.
fn_ROTATE
Rotate any 2D range 90 degrees clockwise with an optional count for 180 or 270 degree rotations.
fn_Does_File_Path_Exist
Returns TRUE/FALSE for each cell, checking whether the file path actually exists on disk.
fn_Is_Hyperlink
Returns TRUE/FALSE indicating whether each cell contains a hyperlink.
Workbook Statistics Report
Generate comprehensive stats: formula density, dynamic arrays, unique formulae, and cross-sheet dependencies.
Workbook Error Scanner
Scan every sheet for #REF!, #VALUE!, #N/A and all other errors. Produces a clickable hyperlinked report.
Dynamic Array Spill Resolver
Detect and fix #SPILL! errors by automatically inserting rows and columns where dynamic arrays need space.
VBA Code Inspector
List every Sub, Function, and Property across all open workbooks with source code retrieval.
Workbook Documentation Suite
Document named ranges, embedded objects, unique formulas (R1C1), and chart series across the entire workbook.
Data Validation Inspector
List all data validation rules across a workbook, and find cells that violate their own validation constraints.
Audit & Formula Tracing Tools
Enhanced Trace Dependents/Precedents with safety checks for large networks, plus Toggle R1C1 reference style.
Power Query Manager
Extract, export, import, and remove Power Query definitions with VBA for version control and migration.
Clipboard Power Tools
Copy cell addresses, formulas as text, and select ranges from clipboard text using the Windows API.
Text Case Transformations
Six in-place text case macros: UPPER, lower, Proper, Title Case, tOGGLE, and Sentence case.
Smart Fill in All Directions
Fill blank cells from the nearest value in any direction: down, up, right, or left.
Auto-Grouping by Title Hierarchy
Automatically create outline groups from 2 to 8 levels based on which column contains the row title.
Mass Naming & Hyperlink Tools
Bulk-create numbered named ranges, convert cell references to names, and add hyperlinks to formula cells.
Auto Save & Version Control
Timed auto-save and automatic version numbering with initials prefix. Lightweight version control for Excel.
Formula Calculation Timer
Measure single-cell recalculation time or full workbook rebuild time to four decimal places.
Fill Blank Cells to the Left
Fill all blank cells in selected rows with the value from the nearest non-blank cell to the right.
Auto Sheet Setup
Reset all visible, hidden, and very hidden sheets to cell A1 with 100% zoom for clean presentations.
Remove Same-Sheet References
Clean up redundant same-sheet references in your formulas automatically. Handles quoted and unquoted sheet names.
My Tech Stack
The tools and technologies I use daily for data analysis, reporting, and automation.