{"text_format":"1","workbook":"xlsx","n_sheets":21,"sheets":["Camera","Linked_Pic","Guage Chart","Calendar","SheetTab Name","NamedRange","Dropdown","Validation","Grouping","x-Table-Contents","x-TOC","Consolidate","ISODD","MOD","Percent","UniqueCountA","XLookup","TextSplit","IFERROR","Clean","OPEN"],"not_rendered":{"charts":2,"images":3,"macros":0,"external_links":0,"defined_names":18,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Camera","index":0,"n_rows":53,"n_cols":17,"rows_kept":53,"cut":false,"hidden":false,"merged":[[39,1,39,13],[34,1,34,13],[35,1,35,13],[36,1,36,13],[37,1,37,13],[38,1,38,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 1 of 21: Camera"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"Table-Contents","Go to Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"TOC","Go to TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌",null,"Month","JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"]
["Tip","What It Does","How to Use It",null,"Sales",50,3,7,6,9,9,5,9,8,4,8,5]
["📷 Camera Tool","Snap live views of data","Add “Camera” to Quick Access Toolbar"]
[]
["Camera Tool"]
["What it does: The Camera Tool takes a live “snapshot” of any range of cells (including charts, tables, or formatted data) and turns it into a dynamic image. When the original data changes, the image updates automatically in real time. It’s like creating a linked picture that stays current without using formulas or copy-paste."]
[]
["The Trick - How to do it"]
["STEP","INSTRUCTION"]
[1,"Right-click anywhere on the Quick Access Toolbar → Customize Quick Access Toolbar"]
[2,"In the “Choose commands from” dropdown, select Commands Not in the Ribbon"]
[3,"Scroll down and find Camera, then click Add >> to move it to your Quick Access Toolbar"]
[4,"Select the range of cells you want to capture"]
[5,"Click the Camera button on your Quick Access Toolbar"]
[6,"Click on your worksheet (or another sheet) where you want to place the live image"]
["Pro Tips"]
["You can resize, rotate, or format the resulting image like any picture."]
["To edit what the camera shows, just move or resize the original range — the image follows."]
["Combine multiple camera shots on one dashboard sheet for a clean summary view."]
["Why It's Great"]
["The Camera Tool is fantastic for building clean, professional-looking dashboards and reports. It lets you display key data or charts in creative layouts (like placing them at angles or overlapping) while keeping everything automatically updated."]
["Real-World Uses"]
["Create executive summary dashboards that pull from multiple worksheets."]
["Build clean printable reports where you want precise control over layout."]
["Show before/after scenarios or multiple views of the same data on one page."]
["Impress stakeholders with dynamic, visually appealing presentations without complex formulas."]
["Version Notes"]
["Available in all modern versions of Excel (Excel 2010 through Excel 365). Still works reliably in the latest versions."]

{"sheet":"Linked_Pic","index":1,"n_rows":55,"n_cols":4,"rows_kept":55,"cut":false,"hidden":false,"merged":[[40,1,40,13],[35,1,35,13],[36,1,36,13],[37,1,37,13],[38,1,38,13],[39,1,39,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 2 of 21: Linked_Pic"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"Table-Contents","Go to Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"TOC","Go to TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["🖼️ Linked Picture Paste","Live image of a cell range","Copy → Home → Paste →  Linked Picture"]
[]
[]
["Linked Picture Paste (Live Image of a Cell Range)"]
["What it does: Linked Picture Paste creates a live, dynamic image of any selected cell range (including charts, tables, or formatted data). Just like the Camera Tool, the image automatically updates whenever the source data changes. It’s a quick and easy way to turn data into a visual snapshot without adding extra buttons or tools."]
[]
["The Trick - How to do it"]
["Step","Instruction"]
[1,"Select the range of cells, chart, or table you want to capture"]
[2,"Press Ctrl + C to copy the selection"]
[3,"Go to the location where you want the live image (can be on the same or different sheet)"]
[4,"Right-click and choose Paste Special"]
[5,"Select Linked Picture (it’s in the Paste Special options)"]
[6,"Click OK — the live image appears"]
["Pro Tips"]
["You can resize, move, and format the linked picture just like a regular image."]
["Hold Alt while resizing to snap it neatly to grid lines."]
["To update the link or change what it shows, simply adjust the original range."]
["This method is often faster than the Camera Tool since it doesn’t require customizing the toolbar."]
["Why It's Great"]
["Linked Picture Paste gives you a super quick way to create clean, professional visuals that stay updated automatically. It’s perfect when you want to show data in creative layouts without cluttering your spreadsheet with formulas or extra charts."]
["Real-World Uses"]
["Build attractive executive dashboards by pulling key metrics from multiple sheets."]
["Create clean printable reports with perfect visual layout control."]
["Display multiple views of the same data side-by-side or in interesting arrangements."]
["Design professional presentations that impress clients and management."]
["Version Notes"]
["Available in Excel 2010 and all newer versions, including Excel 365. Works consistently across modern Excel versions."]

{"sheet":"Guage Chart","index":2,"n_rows":56,"n_cols":24,"rows_kept":56,"cut":false,"hidden":false,"merged":[[43,1,43,13],[36,20,36,21],[42,1,42,13],[35,1,35,13],[37,1,37,13],[38,1,38,13],[39,1,39,13],[40,1,40,13],[36,1,36,13],[41,1,41,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 3 of 21: Guage Chart"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"x-Table-Contents","Go to x-Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"x-TOC","Go to x-TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["🎛️ Gauge Charts","Create speedometers & thermometers","Combine doughnut + pie charts with formatting"]
[]
[]
["Gauge Charts\n(Create Speedometers & Thermometers)"]
["What it does: Gauge Charts (also called Speedometers or Thermometers) are visual displays that show a single value within a range, similar to a car speedometer or thermometer. They make it easy to see performance against targets at a glance, using colors to indicate zones like poor, average, and good."]
[]
["The Trick - How to do it"]
["Step","Instruction"]
[1,"Set up your data: Zones, Value, Color. The top items that will show in color must add up to 100. Then there must be an invisible (formatted to not show) amount of 100."]
[2,"Set up the needle data. The actual line will be where the needle appears on the data, the Pointer width if the size of the needle. The remainder formula =200-78-2",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"ZONE DATA","VALUE","COLOR",null,"NEEDLE DATA"]
[3,"MAKE THE DOUGHNUT CHART: Select your Poor through Invisible labels and amounts and insert a Doughnut Chart (Insert → Charts → Pie → Doughnut)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Poor",50,"Red",null,"Actual",55,null,"⬅️ Change this to move the needle"]
[4,"Right click on any segment of the Doughnut Chart → Format Data Series → click the icon of the chart → Series Options → Angle of first slice = 270 and Doughnute Hole Size = 75",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Average",30,"Yellow",null,"Pointer Width",3,null,"⬅️ Change this to change the needle size"]
[5,"Format the charts: Remove legends, adjust colors for zones (green/yellow/red), and hide the Inviible segment by removing the fill color",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Good",20,"Green",null,"Remainder",{"$f":"200-V38-V39","v":142}]
[6,"Set up the Needle Data. The actual number is the location of the needle on the gauge, the pointer width is exactly that. In the remainder line your formula must be =200-the actual number-pointer width number.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Invisible",100,"Clear"]
[7,"MAKE THE NEEDLE:  Select the needle content cells - Actual, Pointer Width, and Remainder, and their numbers and create a Pie Chart",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Must = 200 👉",{"$f":"SUM(R38:R41)","v":200}]
[8,"Fine-tune: Make the background transparent and combine elements for a clean gauge look"]
[9,"Change the Actual number to change where the needle is, change the Pointer Width number to change the size of the pointer."]
["Pro Tips"]
["Combine with the Camera Tool or Linked Picture for cleaner dashboard layouts."]
["Keep it simple — too many colors or elements can make it look cluttered."]
["Why It's Great"]
["Gauge Charts turn boring numbers into engaging visuals that are instantly understandable. They’re especially powerful for dashboards because they grab attention and communicate performance quickly."]
["Real-World Uses"]
["Show KPI performance (sales targets, budget usage, project completion)."]
["Create executive dashboards for management reviews."]
["Track individual or team goals in HR or operations reports."]
["Build interactive scorecards for presentations and client updates."]
["Version Notes"]
["Works in all versions of Excel from 2010 through Excel 365. No add-ins required — uses built-in chart features."]

{"sheet":"Calendar","index":3,"n_rows":69,"n_cols":24,"rows_kept":69,"cut":false,"hidden":false,"merged":[[47,1,47,13],[46,1,46,13],[45,1,45,13],[34,1,34,13],[43,1,43,13],[42,1,42,13],[41,1,41,13],[37,1,37,13],[36,1,36,13],[35,1,35,13],[38,1,38,13],[39,1,39,13],[40,1,40,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 4 of 21: Calendar"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"x-Table-Contents","Go to x-Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"x-TOC","Go to x-TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["📅 SEQUENCE Calendar","Auto-generate monthly grids","=SEQUENCE(5, 7, A1 - WEEKDAY(A1, 2) + 1, 1)"]
[]
["SEQUENCE Calendar\n(Auto-generate Monthly Grids)"]
["What it does: The SEQUENCE function lets you automatically generate a full monthly calendar grid based on a start date. It creates a dynamic calendar that updates instantly when you change the month or year — no manual filling or copying required.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"=SEQUENCE(5, 7, X33 - WEEKDAY(X33, 2) + 1, 1)"]
[]
["The Trick - How to do it",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"DATE(2026,6,1)","v":"2026-06-01"},null,null,null,null,null,null,null,"=DATE(2027,6,1)"]
["Step","Instruction",null,null,null,null,null,null,null,null,null,null,null,null,null,"MON","TUE","WED","THU","FRI","SAT","SUN"]
[1,"In a cell (e.g., P33), enter the first day of the desired month: =DATE(2026, 6, 1)",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"_xlfn.SEQUENCE(5, 7, P33 - WEEKDAY(P33, 2) + 1, 1)","v":"2026-06-01"},"2026-06-02","2026-06-03","2026-06-04","2026-06-05","2026-06-06","2026-06-07",null,"=SEQUENCE(5, 7, X33 - WEEKDAY(X33, 2) + 1, 1)"]
[null,"The formula =DATE(2026, 6, 1) 2026 = year, 6 = month (June), and 1 = the day.",null,null,null,null,null,null,null,null,null,null,null,null,null,"2026-06-08","2026-06-09","2026-06-10","2026-06-11","2026-06-12","2026-06-13","2026-06-14"]
[2,"I added in a row of days, but not a requirement",null,null,null,null,null,null,null,null,null,null,null,null,null,"2026-06-15","2026-06-16","2026-06-17","2026-06-18","2026-06-19","2026-06-20","2026-06-21"]
[3,"Enter this formula in the top-left cell of where you want the calendar to start to start on Monday:",null,null,null,null,null,null,null,null,null,null,null,null,null,"2026-06-22","2026-06-23","2026-06-24","2026-06-25","2026-06-26","2026-06-27","2026-06-28"]
[null,"=SEQUENCE(5, 7, P33 - WEEKDAY(P33, 2) + 1, 1)",null,null,null,null,null,null,null,null,null,null,null,null,null,"2026-06-29","2026-06-30","2026-07-01","2026-07-02","2026-07-03","2026-07-04","2026-07-05"]
[null,"Enter this formula in the top-left cell of where you want the calendar to start to start on Sunday:"]
[null,"=SEQUENCE(5, 7, P33 - WEEKDAY(P33, 1) + 1, 1)"]
[4,"Press Enter — the entire 35-cell grid will fill automatically"]
[5,"Format the grid as desired"]
[6,"Add day headers (Sun, Mon, Tue, etc.) above the grid"]
[]
[null,"Formula Breakdown (for better understanding):"]
[null,"=SEQUENCE(5, 7, P33 - WEEKDAY(P33, 2) + 1, 1)"]
[null,"rows = 5 → Creates 5 rows"]
[null,"columns = 7 → Creates 7 columns (one for each day of the week)"]
[null,"P33  is the reference date"]
[null,"WEEKDAY(P33, 2) returns weekday number where Monday = 1, Sunday = 7 (This is the key: type 2 makes Monday = 1)"]
[null,"P33 - WEEKDAY(P33, 2) moves backward to the Sunday before the week and gets you to the day before Monday"]
[null,"The +1 adds 1 day to move from that Sunday to Monday and this is what forces the start on Monday\n"]
[null,"The last 1, is the step value to increment by 1 day\n"]
[]
["Pro Tips"]
["Change the 2 in WEEKDAY(...,2) to 1 if you want the week to start on Sunday."]
["Use a drop-down list for month and year selection to make it user-friendly."]
["Add conditional formatting: Cell value =TODAY()"]
["Hide the start date cell or move it off-screen for a cleaner final layout."]
["Why It's Great"]
["SEQUENCE Calendars are incredibly powerful because they’re fully dynamic and require almost no maintenance. One formula can replace hours of manual calendar building, and it stays accurate even as you change months or years."]
["Real-World Uses"]
["Create project planning calendars and Gantt-style overviews."]
["Build team vacation planners and resource scheduling tools."]
["Design personal or department activity trackers."]
["Make professional monthly reports and meeting planners for clients."]
["Version Notes"]
["Requires Excel 365 or Excel 2021 (Dynamic Arrays). The SEQUENCE function is not available in Excel 2019 or earlier versions."]

{"sheet":"SheetTab Name","index":4,"n_rows":56,"n_cols":16,"rows_kept":56,"cut":false,"hidden":false,"merged":[[39,1,39,13],[40,1,40,13],[41,1,41,13],[37,1,37,13],[38,1,38,13],[31,1,31,13],[32,1,32,13],[33,1,33,13],[35,1,35,13],[36,1,36,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 5 of 21: SheetTab Name"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"x-Table-Contents","Go to x-Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"x-TOC","Go to x-TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["Sheet Tab Name","Produce the name of the sheet tab","=TEXTAFTER(CELL(\"filename\", A1), \"]\")"]
[]
["The Trick - How to do it",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"=TEXTAFTER(CELL(\"filename\", A1), \"]\")"]
["STEP","INSTRUCTION"]
[1,"Click in the cell where you want the sheet tab name to appear"]
[2,"Type (or copy and paste) the following formula: =TEXTAFTER(CELL(\"filename\", A1), \"]\")"]
[3,"Press Enter on your keyboard"]
[]
[null,"Formula Breakdown (for better understanding):"]
[null,"CELL(\"filename\", A1)  →  Returns the full file path including the sheet name"]
[null,"The AI does nothing other than tell the formula to use the sheet that has a cell AI"]
[null,"TEXTAFTER(..., \"]\")  →  Extracts everything after the ] character — leaving only the sheet tab name"]
[null,"C:\\Users\\YourName\\Documents\\[Excel Example.xlsx]My Example"]
[null,"In the above example, the \\ separates the path, [ ] indicates the file name and My Example is the sheet tab name"]
[null,"The sheet tab extraction formula only leaves what is at the very end of the full file path"]
[]
[]
["Pro Tips"]
["Save first! The formula only works after the workbook has been saved at least once."]
["Works best when placed in a header row (row 1) and formatted with bold, larger font, and centered."]
["Why It's Great"]
["SEQUENCE Calendars are incredibly powerful because they’re fully dynamic and require almost no maintenance. One formula can replace hours of manual calendar building, and it stays accurate even as you change months or years."]
["Real-World Uses"]
["Calendars – Each month on its own sheet automatically shows the correct month name"]
["Dashboards & Reports – Professional headers that always match the sheet name"]
["Client Deliverables – Makes multi-sheet files look polished and well-organized"]
["Project Trackers – Sheet names like “Week 23”, “Client ABC”, etc."]
["Version Notes"]
["This formula uses the TEXTAFTER function, which is only available in Excel 365 and Excel 2021 or newer. It works excellently in these modern versions. For Excel 2019, Excel 2016, or older versions, you will need to use the alternative formula below instead: =MID(CELL(\"filename\", A1), FIND(\"]\", CELL(\"filename\", A1)) + 1, 255)"]

{"sheet":"NamedRange","index":5,"n_rows":84,"n_cols":25,"rows_kept":84,"cut":false,"hidden":false,"merged":[[62,1,62,13],[63,1,63,13],[66,0,66,13],[59,1,59,13],[60,1,60,13],[64,1,64,13],[61,1,61,13],[36,1,36,13],[37,1,37,13],[58,1,58,13],[57,1,57,13],[48,1,48,13],[50,1,50,13],[51,1,51,13],[53,1,53,13],[54,1,54,13],[55,1,55,13],[52,1,52,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 6 of 21: NamedRange"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"Table-Contents","Go to Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"TOC","Go to TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["📐 Dynamic Named Ranges","Auto-expand as data grows","=OFFSET($R$49,0,0,COUNTA($R:$R)-1,1)"]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["Name Box","Navigate","Click the dropdown in the Name Box - top left of the sheet"]
[]
["The Trick - How to do it"]
["The Name Box as an Instant Navigation Command Line",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Open the Selection Pane"]
["Step","Instruction"]
[1,"Jump anywhere: Type Sheet5!Z100 into the Name Box, hit Enter, and you teleport there."]
[2,"Navigate by objects on the sheet tab: Open the Selection Pane (Home > Find & Select > Selection Pane). It lists every shape, chart, and textbox on the sheet. Click an object’s name to select it, double‑click to rename it, and then jump to it later via the Name Box.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"👇 There is more, keep scrolling"]
[]
[]
["Pro Tips"]
["The Name Box (the tiny box to the left of the formula bar) is far more than a display. You can type a sheet name, a cell reference, or a named range, press Enter, and instantly jump there—no hyperlinks required. Paired with the Selection Pane, it becomes a full navigation cockpit."]
["Why It's Great"]
["This transforms how they physically move around large workbooks. It’s a zero‑formula, purely interactive way to “click & connect” with any part of the workbook. For seasoned users who still scroll endlessly, this alone feels like a superpower."]
[]
["The Trick - How to do it",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"NOTE: Named ranges with formulas do not always appear in the dropdown list"]
["Dynamic Named Ranges (Auto-expand as Data Grows)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Type MonthList in the name box - it uses =OFFSET($R$49,0,0,COUNTA($R:$R)-1,1)"]
["Step","Instruction",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Month","Jo","Mai","Cam","Juan"]
[1,"It does not matter what cell is selected, you create the name using the formula",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Jan",5,7,9,6,null,null,"There is a named range for Jo"]
[2,"Go to the Formulas tab → Defined Names group →  Name Manager → click New",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Feb",null,null,null,null,null,null,"It used the same formula with the adjusted cell/column"]
[3,"In the New Name window, fill in the following:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Mar",null,null,null,null,null,null,"=OFFSET(NamedRange!$S$49,0,0,COUNTA(NamedRange!$S:$S)-1,1)"]
[4,"Give it a clear name: This example is MonthList",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Use it in a calculation"]
[5,"Scope = Workbook",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(Jo)","v":5}]
[6,"In the \"Refers to\" box, enter a dynamic formula: =OFFSET($R$49,0,0,COUNTA($R:$R)-1,1)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Now you type it here"]
[7,"Click OK"]
[null,"Use the named range in formulas, charts, or data validation — it will now auto-expand"]
[]
[null,"Formula Breakdown (for better understanding):"]
[null,"OFFSET(  → The function that returns a range starting from a reference, shifted by rows/cols, and resized."]
[null,"$R$49  → The anchor cell — where our month list starts (January). It’s absolute so it never moves."]
[null,"0, 0,  → Row offset and column offset. We don’t want to shift anywhere, so both are zero."]
[null,"COUNTA($R$R)  → Counts every non‑blank cell in the entire column R. This includes the header “Month” in R48 plus all the months below."]
[null,"-1 → Removes the header from the count, giving us the exact number of months."]
[null,",1) → The height (number of rows) = the result of COUNTA($R:$R)-1. Width = 1 column."]
[null,"Add “April” in R52 → COUNTA becomes 5, -1 = 4, range automatically becomes R49:R52."]
[]
["Modern Alternative (Recommended): Convert your data into an Excel Table (Ctrl + T). Tables automatically create dynamic named ranges for every column (e.g., Table1[Sales])."]
[]
["Pro Tips"]
["Use descriptive names like MonthlySales or ValidProducts."]
["Do not leave any blank rows between months — COUNTA will stop at the first blank cell."]
["Combine with Data Validation to create auto-expanding drop-down lists. See the Dropdown sheettab"]
["Tables are usually better than formulas for most users — they’re cleaner and more powerful."]
["Why It's Great"]
["Dynamic Named Ranges eliminate broken formulas and outdated charts when new data is added. They save time, reduce errors, and make your workbooks much more professional and scalable."]
["Real-World Uses"]
["Create auto-expanding drop-down lists that grow as you add new items."]
["Build charts that automatically include new months or records."]
["Make robust Pivot Tables that update when source data grows."]
["Design flexible budgeting, inventory, or sales tracking models."]
["Version Notes"]
["OFFSET + COUNTA method works in all Excel versions (2007 and newer)."]
["Excel Tables (recommended) are available in Excel 2010 and all newer versions."]
["Fully compatible with Excel 365."]

{"sheet":"Dropdown","index":6,"n_rows":62,"n_cols":23,"rows_kept":62,"cut":false,"hidden":false,"merged":[[40,1,40,13],[34,1,34,13],[35,1,35,13],[37,1,37,13],[38,1,38,13],[39,1,39,13],[36,1,36,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 7 of 21: Dropdown"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"x-Table-Contents","Go to x-Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"x-TOC","Go to x-TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["📋 Dynamic Drop-Downs","Auto-updating dropdowns","Use UNIQUE() + SORT() in Data Validation"]
[]
["Dynamic Drop-Downs\n(Auto-updating Dropdowns)"]
["What it does: Dynamic Drop-Downs automatically update their list of choices when you add or remove items from the source data. This keeps your data validation lists current without having to manually edit them every time new options are added."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Use reference",null,"Use Dropdown",null,"CREATE DROPDOWN"]
["The Trick - How to do it",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Departments",null,"=Departments"]
["Step","Instruction",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Sales"]
[1,"We created a Departments list beginning in Q33 (see the Named Range sheet for more info)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Marketing"]
[2,"In Formulas → Define Name we used =OFFSET($Q$34,0,0,COUNTA($Q:$Q)-1,1)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Engineering"]
[null," to create the named range Departments",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"HR"]
[3,"Go to any cell and type =Departments to see the list - add a new department in the original list"]
[4,"Select cell U33 to test this: Go to Data tab → Data Tools → Data Validation"]
[5,"Under \"Allow\", choose List"]
[6,"In the Source box, type =Departments the check the In-cell dropdown box and click OK"]
[]
["Go back to the original Department list and add more, change some and see the changed."]
["Works forever, as long as column R contains only the header and months."]
[]
["GOLDEN RULE: Never put anything else in column R below the months."]
[]
["Pro Tips"]
["Items appear in the exact order they are on the sheet, so set them up for how you want to use them"]
["Use the Table method whenever possible — it’s more stable than OFFSET."]
["Add a blank cell at the top of your source list if you want users to be able to clear the dropdown."]
["Why It's Great"]
["Dynamic Drop-Downs save enormous time and prevent errors. They keep your forms and models flexible as your business grows, without constant maintenance."]
["Real-World Uses"]
["Create smart order forms and invoice templates that grow with your product list."]
["Build HR onboarding checklists with dynamic department or role selections."]
["Design project trackers with auto-updating assignee or status lists."]
["Make budgeting tools with dynamic account or category selections."]
["Version Notes"]
["Works in all Excel versions from 2007 onward."]
["Excel Tables method is available in Excel 2010 and newer."]
["Fully supported in Excel 365."]

{"sheet":"Validation","index":7,"n_rows":55,"n_cols":21,"rows_kept":55,"cut":false,"hidden":false,"merged":[[39,1,39,13],[34,1,34,13],[35,1,35,13],[36,1,36,13],[37,1,37,13],[38,1,38,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 8 of 21: Validation"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"Table-Contents","Go to Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"TOC","Go to TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["🗣️ Validation Messages","Friendly error prompts","Data > Validation > Error Alert"]
[]
["Validation Messages"]
["What it does: Validation Messages are helpful pop-up messages that appear when someone selects a cell with Data Validation. They include two types: Input Messages (that show helpful instructions before typing) and Error Alerts (that appear when someone enters invalid data). This guides users and reduces data entry errors."]
[]
["The Trick - How to do it",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"REP","STATE"]
["Step","Instruction",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Juan",null,null,"👈 Type in a state"]
[1,"Select the cell or range where you want to apply validation",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Pat"]
[2,"Go to Data tab → Data Validation → Data Validation",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Cam"]
[3,"Set up your validation rules (List, Whole Number, Date, etc.)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Mai"]
[4,"Switch to the Input Message tab"]
[5,"Check \"Show input message when cell is selected\", add a Title and Input message"]
[6,"Switch to the Error Alert tab, choose a Style (Stop, Warning, Information), and write a clear error message"]
[]
["Pro Tips"]
["Keep Input Messages short and friendly — one or two sentences maximum."]
["Use clear, action-oriented Error Alerts (e.g., “Please enter a date between 1/1/2025 and 12/31/2025”)."]
["You can use different Error Alert styles: Stop (blocks entry), Warning (allows override), or Information (just notifies)."]
["Combine with Dynamic Drop-Downs for even smarter data entry forms."]
["Why It's Great"]
["Validation Messages turn your spreadsheets into user-friendly tools. They prevent mistakes before they happen and provide immediate guidance, making your workbooks more professional and easier for others to use."]
["Real-World Uses"]
["Create order forms with clear instructions for product codes or quantities."]
["Build expense reports that guide users on valid date ranges and amounts."]
["Design HR forms with helpful messages for employee data entry."]
["Make client intake sheets that reduce back-and-forth corrections."]
["Version Notes"]
["Available in all versions of Excel (2007 and newer), including Excel 365. Fully supported with no limitations."]

{"sheet":"Grouping","index":8,"n_rows":70,"n_cols":26,"rows_kept":70,"cut":false,"hidden":false,"merged":[[39,1,39,13],[34,1,34,13],[35,1,35,13],[36,1,36,13],[37,1,37,13],[38,1,38,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 9 of 21: Grouping"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[10,"Table-Contents","Go to Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"TOC","Go to TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["📂 Grouping","Collapse/expand sections","Select → Data > Group"]
[]
["Grouping  (Rows & Columns)"]
["What it does: Grouping lets you collapse and expand sections of rows or columns, making large spreadsheets much easier to read and navigate. It creates a hierarchical structure with outline buttons so users can hide detailed data and show only the summaries when needed.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"👇 There is more, keep scrolling to the example"]
[]
["The Trick - How to do it"]
["Step","Instruction"]
[1,"Select the rows or columns you want to group - GO TO cell H57 for our example"]
[2,"Go to Data tab → Group (in the Outline group)"]
[3,"Choose Group → Rows or Columns → Click OK"]
[4,"Repeat for other sections you want to group"]
[5,"Use the small minus/plus buttons (or numbers at the top/left) to collapse and expand groups"]
[6,"To remove grouping: Select the grouped area → Data → Ungroup"]
[]
["Pro Tips"]
["Create multiple levels of grouping (sub-groups inside groups) for very organized reports."]
["Use Auto Outline (Data → Group → Auto Outline) if your data has subtotals."]
["Press Alt + Shift + Left Arrow to collapse and Alt + Shift + Right Arrow to expand groups quickly."]
["Combine with subtotals for powerful summary views."]
["Why It's Great"]
["Grouping transforms overwhelming spreadsheets into clean, professional-looking documents. Users can focus on the big picture or drill down into details with one click, greatly improving readability and usability."]
["Real-World Uses"]
["Organize large financial reports with monthly details collapsible under quarterly totals."]
["Create project plans where tasks can be hidden under major phases."]
["Build sales reports with region details grouped under national summaries."]
["Design budgets where individual expense lines collapse under department totals."]
["Version Notes"]
["Available in all versions of Excel (2007 and newer), including Excel 365. Works consistently across desktop versions."]
[]
[null,null,null,null,null,null,null,null,null,"JAN","FEB","MAR","QTR1","APR","MAY","JUN","QTR2","JUL","AUG","SEP","QTR3","OCT","NOV","DEC","QTR4","Yearly Total"]
[null,null,null,null,null,null,null,null,"NW - 1",4,1,2,{"$f":"SUM(J58:L58)","v":7},6,6,7,{"$f":"SUM(N58:P58)","v":19},2,8,6,{"$f":"SUM(R58:T58)","v":16},8,6,8,{"$f":"SUM(V58:X58)","v":22},{"$f":"SUM(Y58,U58,Q58,M58)","v":64}]
[null,null,null,null,null,null,null,null,"NW - 2",3,9,7,19,6,1,7,14,7,4,8,19,5,9,1,15,67]
[null,null,null,null,null,null,null,null,"NW - 3",7,6,3,16,5,2,2,9,3,2,1,6,7,4,2,13,44]
[null,null,null,null,null,null,null,null,"NW TOTAL",{"$f":"SUM(J58:J60)","v":14},16,12,42,17,9,16,42,12,14,15,41,20,19,11,50,175]
[null,null,null,null,null,null,null,null,"S-1",8,8,8,{"$f":"SUM(J62:L62)","v":24},5,7,6,{"$f":"SUM(N62:P62)","v":18},7,7,9,{"$f":"SUM(R62:T62)","v":23},8,5,5,{"$f":"SUM(V62:X62)","v":18},{"$f":"SUM(Z61)","v":175}]
[null,null,null,null,null,null,null,null,"S-2",7,7,9,23,6,6,9,21,2,3,5,10,3,5,2,10,175]
[null,null,null,null,null,null,null,null,"S-3",6,7,6,19,5,8,5,18,9,2,7,18,9,3,5,17,175]
[null,null,null,null,null,null,null,null,"S TOTAL",{"$f":"SUM(J62:J64)","v":21},22,23,66,16,21,20,57,18,12,21,51,20,13,12,45,525]
[null,null,null,null,null,null,null,null,"E-1",2,8,3,{"$f":"SUM(J66:L66)","v":13},5,2,6,{"$f":"SUM(N66:P66)","v":13},5,2,9,{"$f":"SUM(R66:T66)","v":16},7,6,6,{"$f":"SUM(V66:X66)","v":19},{"$f":"SUM(Z65,Z61)","v":700}]
[null,null,null,null,null,null,null,null,"E-2",2,8,5,15,2,2,6,10,3,8,6,17,5,7,8,20,875]
[null,null,null,null,null,null,null,null,"E-3",1,5,2,8,3,3,9,15,2,2,6,10,9,2,3,14,1050]
[null,null,null,null,null,null,null,null,"E TOTAL",{"$f":"SUM(J66:J68)","v":5},21,10,36,10,7,21,38,10,12,21,43,21,15,17,53,2625]
[null,null,null,null,null,null,null,null,"GRAND TOTAL",{"$f":"SUM(J69,J65,J61)","v":40},59,45,144,43,37,57,137,40,38,57,135,61,47,40,148,3325]

{"sheet":"x-Table-Contents","index":9,"n_rows":59,"n_cols":4,"rows_kept":59,"cut":false,"hidden":false,"merged":[[39,1,39,13],[34,1,34,13],[35,1,35,13],[36,1,36,13],[37,1,37,13],[38,1,38,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 10 of 21: x-Table-Contents"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"Table-Contents","Go to Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"TOC","Go to TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["Dynamic Table of Contents ","Creates an automatically updating, clickable index of every sheet in the workbook"," Using HYPERLINK + Sheet Names"]
[]
["Dynamic Table of Contents"]
["What it does: A Dynamic Table of Contents automatically creates a clickable list of all worksheets in your workbook (or specific sections). When you add, rename, or delete sheets, the table updates automatically, with hyperlinks that let users jump to any sheet with one click."]
[]
["The Trick - How to do it"]
["Step","Instruction"]
[1,"Insert a new sheet at the front of your workbook and name it \"Contents\""]
[2,"In cell A1, type \"Table of Contents\" as a title"]
[3,"Starting in cell A3, enter this formula: =TEXTAFTER(TEXTBEFORE(CELL(\"filename\",A1),\"[\"),\"]\") (to get workbook name)"]
[4,"In cell B3, enter this formula to list all sheet names: =MID(GET.WORKBOOK(1),FIND(\"]\",GET.WORKBOOK(1))+1,255)"]
[5,"Convert the list into a dynamic array (if using Excel 365) or drag the formula down"]
[6,"Select the sheet names and create hyperlinks: Insert → Link → Place in This Document"]
[]
["Modern Formula Method (Recommended - Excel 365): Use this single dynamic formula in cell A3: =HYPERLINK(\"#'\"&SORT(SHEETNAME())&\"'!A1\", SORT(SHEETNAME()))"]
[]
["Pro Tips"]
["Use the SHEETNAME() function if available in your version, or create a custom name using GET.WORKBOOK(1)."]
["Add icons or formatting to make it look professional."]
["Hide the Contents sheet from the tab bar if you want a cleaner navigation experience (right-click sheet tab → Hide)."]
["Combine with Grouping or a Navigation Pane for even better workbook navigation."]
["Why It's Great"]
["A Dynamic Table of Contents turns complex multi-sheet workbooks into professional, easy-to-navigate documents. It saves users time and makes your files much more user-friendly, especially when shared with others."]
["Real-World Uses"]
["Create professional financial models with easy navigation between summary, assumptions, and detailed sheets."]
["Build training manuals or dashboards with many supporting sheets."]
["Design client deliverables with clear navigation between sections."]
["Organize large project files with separate sheets for each phase or department."]
["Version Notes"]
["Modern dynamic array method (SHEETNAME()) works best in Excel 365."]
["Traditional GET.WORKBOOK method works in Excel 2010 and newer versions."]
["Hyperlinks function in all Excel versions."]

{"sheet":"x-TOC","index":10,"n_rows":194,"n_cols":7,"rows_kept":194,"cut":false,"hidden":false,"merged":[[25,0,26,0],[27,0,28,0]]}
[{"$f":"HYPERLINK(\"#TOC!A1\", \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"Sheet 11 of 21: x-TOC"}]
[]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?",null,"Cell","Formula"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""},null,"A4",{"$f":"B55","v":"=SEQUENCE(SHEETS())"}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""},null,null,"NOTE: New sheet tab? Will auto update on refresh"]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""},null,"B4",{"$f":"B67","v":"=TRANSPOSE(SheetList)"}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""},null,null,"NOTE: New sheet tab? Requires copy/paste or autofill to update"]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""},null,"C4",{"$f":"B86","v":"=HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)"}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""},null,null,"NOTE: New sheet tab? Will auto update on refresh"]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""},null,"D4",{"$f":"B110","v":"=IF(TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")"}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""},null,null,"NOTE: New sheet tab? Requires copy/paste or autofill to update"]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""},null,"IMPORTANT: See directions in Step 1 to create the SheetList"]
[10,"Table-Contents","Go to Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"TOC","Go to TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["1️⃣","A dynamic Table of Contents (TOC) sheet"]
[null,"It lists all sheet names, makes them clickable, and numbers them automatically."]
["2️⃣","A “smart label” on every other sheet"]
[null,"It will display its own name and provides a one‑click jump back to the TOC."]
["Together they show how CELL, SHEET(), SHEETS(), and HYPERLINK connect the whole workbook."]
[]
["Step 1: The Foundation – Create the SHEETLIST"]
["Excel has no built‑in =SHEETNAMES() function."]
["The trick is an Excel 4.0 macro in a Named Range."]
[]
["1️⃣","Go to Formulas > Name Manager > New."]
["2️⃣","Name it: SheetList"]
["3️⃣","In Refers to, paste:"]
[null,"=REPLACE(GET.WORKBOOK(1),1,FIND(\"]\",GET.WORKBOOK(1)),\"\")"]
["4️⃣","Click OK."]
[]
["Note: The workbook must be saved as .xlsm (macro-enabled)"]
["Now SheetList holds an array of all sheet names in the workbook, in order."]
[]
["Step 2: Build the Dynamic Table of Contents"]
["On a new sheet called TOC, set up these columns - name them what you want"]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[]
["Column A – Sequential Number - we named it NUMBER"]
["We will list the number of sheets using the =SEQUENCE function and the SheetList created in Step 1"]
["This Dynamic Array Formula is used because it spills results into multiple cells automatically — no need to drag the formula down."]
["We use SHEETS() to number all sheets from 1 to the total count"]
[]
["1️⃣","In the cell below the NUMBER header, type the following formula"]
["2️⃣","=SEQUENCE(SHEETS())"]
[null,"NOTE:   In the example above, you must remove the '  –  it is here to convert the function into text"]
["3️⃣","This spills down when you hit Enter"]
[null,"NOTE:   If the spill down runs into other spreadsheet content, the result will be a #SPILL! Error"]
[]
[]
["Column B – The name of the sheet - we named it SHEET NAME"]
["We will list the number of sheets using the SheetList created in Step 1"]
["The function =(SheetList) provides a list of names in a row"]
["We will nest this in the TRANSPOSE function to turn it into a vertical list"]
[]
["1️⃣","In the cell below the SHEETNAME header, type the following formula"]
["2️⃣","=TRANSPOSE(SheetList)"]
[null,"NOTE:   In the example above, you must remove the '  –  it is here to convert the function into text"]
["3️⃣","Use the AutoFill Handle to automatically copy the content to all sheet number rows"]
[]
[]
["Column C – Clickable Sheet Names - we named it LINK"]
["We will create a link to the actual sheet"]
["This formula takes every sheet name in column B (starting from B4) and automatically creates a clickable hyperlink for each one."]
["Each link will say “Go to [Sheet Name]” and jump to cell A1 on that sheet. "]
["Line-by-Line Breakdown:"]
["HYPERLINK This is the main function that creates a clickable link."]
["( Opens the function — all functions start with an opening parenthesis."]
["\"#'\" & B6 & \"'!A1\" This builds the destination (where the link should go)"]
["It creates text like: #'Summary'!A1 — which tells Excel to go to cell A1 on the sheet named “Summary”"]
[", Separates the destination link from the display text"]
["\"Go to \" & B6** This creates the friendly name — the text the user actually sees and clicks on (e.g., “Go to Summary”)"]
[") Closes the HYPERLINK function"]
[]
["1️⃣","In the cell below the LINK header, type the following formula"]
["2️⃣","=HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)"]
[null,"NOTE:   In the example above, you must remove the '  –  it is here to convert the function into text"]
["3️⃣","You will need to autofill this down to all rows"]
[null,"NOTE:   If you want the hyperlink style applied to the cells, select the link, to the Home tab ➡️ Styles ➡️ Cell Styles ➡️ Hyperlink"]
[null,"NOTE:   If you want the hyperlink style customized, right click on the hyperlink style and select Modify… to edit the current format"]
[]
[]
[]
["Column D –  indicates which is the currently active sheet - we named it CURRENT SHEET?"]
["We will create an indicator to identify which is currently the active sheet"]
["This formula checks whether the current worksheet (the one you’re looking at) matches the sheet name listed in column B."]
["Whatever sheet tab you are on, if the TOC is present, the proper cell in that column shows 👈 You are here"]
["If it doesn’t match, it shows nothing (blank cell)."]
["This helps users know which sheet they are currently on in the Table of Contents."]
["Line-by-Line Breakdown:"]
["IF = the main function, it checks a condition and returns one result if true, and another if false"]
["TEXTAFTER(CELL(\"filename\",A3),\"]\") = extracts the current sheet name from the workbook’s filename."]
["CELL(\"filename\",A3) gets the full file path and sheet name, and TEXTAFTER(..., \"]\") pulls out just the sheet name after the last ]."]
["= Compares the current sheet name with the sheet name in cell B4."]
["B4 The sheet name from your list in column B that we are checking against."]
["\"👈 You are here\" The text that appears if the current sheet matches the one in B4 (the “True” result)."]
["\"\" Empty text (blank cell) if the current sheet does not match (the “False” result)."]
[]
["1️⃣","In the cell below the CURRENT SHEET? header, type the following formula"]
["2️⃣","=IF(TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")"]
[null,"NOTE:   In the example above, you must remove the '  –  it is here to convert the function into text"]
["3️⃣","Use the AutoFill Handle to automatically copy the content to all sheet number rows"]
[]
["This compares the currently active sheet’s name (from the formula) to each entry in the TOC."]
["It will automatically show the arrow only on the row of the current sheet."]
[]
["Step 3: Put a “Smart Label” on Every Sheet"]
["On each sheet (Data, Charts, etc.), place a small header or footer that:"]
["Displays the sheet’s own name"]
["Shows its position in the workbook"]
["Provides a one‑click jump back to the TOC"]
["Now every sheet knows its own name, its index, and links back to the hub."]
[]
["1️⃣","In any cell (I put it in A1) on every sheet, type the following formula"]
["2️⃣","=HYPERLINK(\"#TOC!A1\", \"Sheet \" & SHEET() & \" of \" & SHEETS() & \": \" & TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))"]
[null,"NOTE:   In the example above, you must remove the '  –  it is here to convert the function into text"]
[null,"⬆️   Note:   Change the content of the \" \" to say whatever you want."]
[null,"⬆️   Note:   The & is the concatenate, add or remove them to change this to say what you want."]
["3️⃣","Use the AutoFill Handle to automatically copy the content to all sheet number rows"]
[]
["This compares the currently active sheet’s name (from the formula) to each entry in the TOC."]
["It will automatically show the arrow only on the row of the current sheet."]
["Now every sheet knows its own name, its index, and links back to the hub."]
[]
["⬇️   Note: Below is a different way to type the formula using CONCAT rather than the & option"]
["=HYPERLINK(\"#TOC!A1\", CONCAT(\"👈 Back to TOC | \",\"Sheet \",SHEET(),\" of \",SHEETS(),\": \",TEXTAFTER(CELL(\"filename\",INDIRECT(\"A1\")),\"]\")))"]
[]
["Customising the Colour (Optional)"]
["Make the “Current?” indicator stand out with conditional formatting:"]
[]
[]
["1️⃣","Select the TOC sheet’s contents, not including the headers/column titles"]
["2️⃣","Home → Conditional Formatting → New Rule → Use a formula…"]
["3️⃣","In the formula bar type:  =$D4=\"👈 You are here\""]
["4️⃣","Set a color fill and bold text."]
[]
[null,"⬆️   Note: In step 3, the cell address is the first cell of the top row for the TOC listing"]
["Now the active sheet row lights up every time you return to the TOC and recalculate."]
[]
["Why This Is a Wow!"]
["The TOC is fully dynamic: add/rename/reorder sheets, and everything updates (after a recalculation)."]
["SHEET() and SHEETS() give an automatic numbering system without hard‑coded totals."]
["The same CELL(\"filename\") trick now serves a practical, interactive purpose—tying the whole workbook together."]
["It’s all formula‑based, no VBA, and completely portable."]
["The active worksheet line is highlighted"]
[]
["Quick Refresh Tip"]
["The Table of Contents won’t auto‑update when sheets are added/renamed until you do something to refresh:"]
["Switch to the TOC sheet and press F9, or"]
["Double‑click any cell and press Enter, or"]
["Recalculate the workbook (Formulas → Calculate Now)."]
[]
["How to Format Hyperlinks in Your Table of Contents"]
["Using Styles from the Home Ribbon you can determine exactly what hyperlinks and followed hyperlinks look like"]
["Update the Style any time you want to change the look, it will automatically apply to each cell with the style applied"]
["Basic Hyperlink Formatting"]
[]
["1️⃣","Select the cell(s) containing your hyperlinks (e.g., column C)."]
["2️⃣","Click the Home  Ribbon ➡️ Styles Group ➡️ Cell Styles"]
["3️⃣","In the Data and Model section you will see Followed Hyperlink and Hyperlink"]
["4️⃣","Do the following to each"]
[null,"Right Click"]
[null,"Select Modify…"]
[null,"Cick the Format… button"]
[null,"Set the format you prefer"]
["5️⃣","Click OK"]
[]
["Now apply the Style format to all applicable cells."]
["All cells for the Followed Hyperlink and the Hyperlink will match."]
[]
[]
[]
[]
[]
["HYPERLINK This is the main function that creates a clickable link."]
["( Opens the function — all functions start with an opening parenthesis."]
["\"#'\" & B6 & \"'!A1\" This builds the destination (where the link should go). It creates text like: #'Summary'!A1 — which tells Excel to go to cell A1 on the sheet named “Summary”."]
["\"#'\" & B6 & \"'!A1\" This builds the destination (where the link should go). It creates text like: #'Summary'!A1 — which tells Excel to go to cell A1 on the sheet named “Summary”."]
[", Separates the destination link from the display text."]
["\"Go to \" & B6** This creates the friendly name — the text the user actually sees and clicks on (e.g., “Go to Summary”)."]
[") Closes the HYPERLINK function."]
["Full Formula (Ready to Copy & Paste):"]
["=HYPERLINK(\"#'\" & B6 & \"'!A1\", \"Go to \" & B6)"]
["Tip for Students: Paste this formula in cell C6, then drag the fill handle (small square in the bottom right) down to copy it for all your sheets. This is the most reliable way to create working hyperlinks in a Table of Contents."]

{"sheet":"Consolidate","index":11,"n_rows":84,"n_cols":18,"rows_kept":84,"cut":false,"hidden":false,"merged":[[39,1,39,13],[34,1,34,13],[35,1,35,13],[36,1,36,13],[37,1,37,13],[38,1,38,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 12 of 21: Consolidate"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"x-Table-Contents","Go to x-Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"x-TOC","Go to x-TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["Consolidate like data","Combines data from multiple ranges","Data tab > Data Tools > Consolidate"]
[]
["Data Consolidate\nwith Clickable Source Drill‑Down"]
["What it does: The Consolidate feature summarizes data from multiple worksheets or workbooks into one master summary. When you enable “Create links to source data,” it creates an automatic outline with clickable plus/minus buttons, allowing users to drill down and see the detailed source data behind each summarized number.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"👇 There is more, keep scrolling to the example"]
[]
["The Trick - How to do it"]
["Step","Instruction"]
[1,"Make sure all source ranges have the same layout and column headings - Go to H57 for the example"]
[2,"Go to the Data tab → Consolidate"]
[3,"Choose the function you want (usually Sum)"]
[4,"Click in the Reference box, select your first data range, then click Add"]
[5,"Repeat step 4 for all other ranges you want to consolidate"]
[6,"Check the box Create links to source data → Click OK"]
[]
["Pro Tips"]
["The result automatically creates grouped rows with drill-down capability (click the + buttons)."]
["Use consistent labels in the left column for best results."]
["You can consolidate by category (Position or Category labels) to group similar items."]
["To update the consolidation later, just right-click the summary and choose Refresh (if links were created)."]
["Why It's Great"]
["This is one of the most powerful but underused tools in Excel. It gives you a clean summary view while still allowing instant access to the underlying details — perfect for reports where you need both high-level and detailed information in one place."]
["Real-World Uses"]
["Combine sales data from multiple regions or stores into one national report."]
["Create department expense summaries with drill-down to individual transactions."]
["Consolidate budget vs actuals from several projects or divisions."]
["Build monthly management reports that pull from many weekly worksheets."]
["Version Notes"]
["Available in all versions of Excel (2007 and newer), including Excel 365. The drill-down (outline) feature works reliably across all modern versions."]
[]
[]
[null,null,null,null,null,null,null,null,"JAN","FEB","MAR","QTR1",null,null,"JUL","AUG","SEP","QTR3"]
[null,null,null,null,null,null,null,"NW - 1",4,1,2,{"$f":"SUM(I59:K59)","v":7},null,"NW - 1",2,8,6,{"$f":"SUM(O59:Q59)","v":16}]
[null,null,null,null,null,null,null,"NW - 2",3,9,7,19,null,"NW - 2",7,4,8,19]
[null,null,null,null,null,null,null,"NW - 3",7,6,3,16,null,"NW - 3",3,2,1,6]
[null,null,null,null,null,null,null,"NW TOTAL",{"$f":"SUM(I59:I61)","v":14},16,12,42,null,"NW TOTAL",12,14,15,41]
[]
[null,null,null,null,null,null,null,null,"APR","MAY","JUN","QTR2",null,null,"OCT","NOV","DEC","QTR4"]
[null,null,null,null,null,null,null,"NW - 1",6,6,7,{"$f":"SUM(I65:K65)","v":19},null,"NW - 1",8,6,8,{"$f":"SUM(O65:Q65)","v":22}]
[null,null,null,null,null,null,null,"NW - 2",6,1,7,14,null,"NW - 2",5,9,1,15]
[null,null,null,null,null,null,null,"NW - 3",5,2,2,9,null,"NW - 4",7,4,2,13]
[null,null,null,null,null,null,null,"NW TOTAL",{"$f":"SUM(I65:I67)","v":17},{"$f":"SUM(J65:J67)","v":9},{"$f":"SUM(K65:K67)","v":16},{"$f":"SUM(L65:L67)","v":42},null,"NW TOTAL",{"$f":"SUM(O65:O67)","v":20},{"$f":"SUM(P65:P67)","v":19},{"$f":"SUM(Q65:Q67)","v":11},{"$f":"SUM(R65:R67)","v":50}]
[]
[]
[null,null,null,null,null,null,null,"Sheri List","JAN","FEB","MAR","QTR1"]
[null,null,null,null,null,null,null,"NW - 1",4,1,2,{"$f":"SUM(I72:K72)","v":7}]
[null,null,null,null,null,null,null,"NW - 2",3,9,7,19]
[null,null,null,null,null,null,null,"NW - 3",7,6,3,16]
[]
[null,null,null,null,null,null,null,"Pam List","JAN","FEB","MAR","QTR1"]
[null,null,null,null,null,null,null,"NW - 1",8,8,8,{"$f":"SUM(I77:K77)","v":24}]
[null,null,null,null,null,null,null,"NW - 2",7,7,9,23]
[null,null,null,null,null,null,null,"NW - 3",6,7,6,19]
[]
[null,null,null,null,null,null,null,"Quami List","JAN","FEB","MAR","QTR1"]
[null,null,null,null,null,null,null,"NW - 1",2,8,3,{"$f":"SUM(I82:K82)","v":13}]
[null,null,null,null,null,null,null,"NW - 2",2,8,5,15]
[null,null,null,null,null,null,null,"NW - 3",1,5,2,8]

{"sheet":"ISODD","index":12,"n_rows":61,"n_cols":26,"rows_kept":61,"cut":false,"hidden":false,"merged":[[45,1,45,13],[34,1,34,13],[35,1,35,13],[36,1,36,13],[37,1,37,13],[38,1,38,13],[39,1,39,13],[41,1,41,13],[42,1,42,13],[43,1,43,13],[44,1,44,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 13 of 21: ISODD"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"Table-Contents","Go to Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"TOC","Go to TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["🎨 Formula-Based Formatting","Highlight with logic","=ISODD(ROW()) for zebra stripes"]
[]
["Formula-Based Formatting\n(Zebra Stripes with =ISODD(ROW()))"]
["What it does: Formula-Based Conditional Formatting lets you apply formatting (colors, fonts, borders, etc.) based on a custom formula. A very popular use is creating zebra stripes (alternating row colors) using =ISODD(ROW()), which makes large tables much easier to read by improving visual separation between rows.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"=ISODD(ROW())",null,null,null,null,null,"OR",null," =ISEVEN(ROW())"]
[]
["The Trick - How to do it"]
["Step","Instruction"]
[1,"Select the range you want to format (e.g., Q33:AA40)"]
[2,"Go to Home tab → Conditional Formatting → New Rule"]
[3,"Choose Use a formula to determine which cells to format"]
[4,"Enter this formula: =ISODD(ROW())"]
[5,"Click Format, go to Fill tab, and choose a  color (e.g., light gray or light blue)"]
[6,"Click OK twice to apply the rule"]
[]
[null,"Formula Breakdown (for better understanding):",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"GO TO THE NEXT SHEET FOR MORE IDEAS… 👉"]
[null,"ROW() → Returns the current row number"]
[null,"ISODD(ROW()) → Returns TRUE for odd-numbered rows (1, 3, 5...)"]
[null,"You can use =ISEVEN(ROW()) for even rows instead"]
[null,"The formula is relative, so it automatically adjusts for every row in your selected range"]
[]
["Pro Tips"]
["Apply the rule to entire columns (e.g., $A:$G) so it works even when new rows are added."]
["Combine multiple rules — for example, add another rule to highlight the current row using =ROW()=CELL(\"row\")."]
["Use subtle colors so the text remains highly readable."]
["For tables, consider using Excel Tables (Ctrl + T), which have built-in banding, but formula-based formatting gives you more control."]
["Why It's Great"]
["Formula-Based Formatting turns plain data into professional, easy-to-read reports. Zebra stripes especially reduce eye strain and help users track information across wide tables without losing their place."]
["Real-World Uses"]
["Format large transaction logs, customer lists, or inventory reports."]
["Create clean financial statements and budget trackers."]
["Improve readability in project task lists and schedules."]
["Design professional dashboards and management reports."]
["Version Notes"]
["Available in all versions of Excel (2007 and newer), including Excel 365. Conditional Formatting with formulas is a core feature that works consistently across all versions."]

{"sheet":"MOD","index":13,"n_rows":99,"n_cols":17,"rows_kept":99,"cut":false,"hidden":false,"merged":[[75,4,84,12],[49,1,49,13],[34,1,34,13],[35,1,35,13],[36,1,36,13],[37,1,37,13],[40,1,40,13],[38,1,38,13],[39,1,39,13],[41,1,41,13],[42,1,42,13],[43,1,43,13],[44,1,44,13],[45,1,45,13],[46,1,46,13],[47,1,47,13],[48,1,48,13],[51,1,51,13],[52,1,52,13],[53,1,53,13],[54,1,54,13],[55,1,55,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 14 of 21: MOD"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"x-Table-Contents","Go to x-Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"x-TOC","Go to x-TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["🧮 MOD() Logic","Create repeating patterns","‘=MOD(ROW(),3)=0"]
[]
["MOD() Logic"]
["What it does: The MOD function returns the remainder after dividing one number by another. In Excel, MOD() Logic is a powerful technique used to create repeating patterns, alternating formatting, check for even/odd numbers, and build smart conditional rules that cycle at regular intervals."]
[]
["The Trick - How to do it"]
["Step","Instruction"]
[null,"Identify what pattern or condition you want (e.g., every 3rd row, every other column, etc.)"]
[null,"Use the basic MOD formula: =MOD(number, divisor)"]
[null,"Combine it with conditional formatting, formulas, or filters"]
[null,"Example for zebra stripes: =MOD(ROW(),2)=1"]
[null,"You can shade columns with =MOD(COLUMN(),2)=0",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"=MOD(ROW()+COLUMN(),2)=0"]
[null,"Create checkerboards with =MOD(ROW()+COLUMN(),2)=0",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"=MOD(ROW(),3)=1"]
[null,"Apply the formula in Conditional Formatting or as a helper column"]
[1,"Select the range you want to format"]
[null,"(Example: Select Q42:Z50 or your entire data table)"]
[2,"Go to Home tab → Conditional Formatting → New Rule"]
[3,"Choose Use a formula to determine which cells to format"]
[4,"In the formula box, paste this formula to highlight every third row: "]
[null,"=MOD(ROW(),3)=1"]
[5,"Click Format → Go to the Fill tab → Choose your desired color (light gray is common)."]
[6,"Click OK → Click OK again."]
[null,"Test with different divisors to control the cycle (2 = every other, 3 = every third, etc.)"]
[]
[null,"Formula Breakdown (for better understanding):"]
[null,"MOD(ROW(), 2) → Returns 0 for even rows, 1 for odd rows",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"👇 There is more, keep scrolling to the math example in A75"]
[null,"=MOD(ROW(), 2)=0 → TRUE on even rows"]
[null,"=MOD(ROW(), 3)=0 → TRUE every 3rd row"]
[null,"=MOD(COLUMN(), 2)=1 → TRUE on odd columns The first number is usually ROW() or COLUMN(), and the second number controls the cycle length."]
[]
["Pro Tips"]
["Use MOD() instead of ISODD() or ISEVEN() when you need more flexible patterns (like every 3rd or 4th row)."]
["Combine with AND() or OR() for powerful conditional formatting (e.g., highlight every 5th row and weekends)."]
["Add a helper column with MOD logic to sort or filter data in repeating patterns."]
["Be careful with row 1 — test your formula starting from your actual data row."]
["Why It's Great"]
["MOD() Logic is simple but incredibly versatile. It allows you to create intelligent, repeating behaviors in your spreadsheets that would otherwise require complex nested formulas."]
["Real-World Uses"]
["Create advanced zebra striping or color banding beyond basic alternating rows."]
["Build weekly schedules that highlight every 7 days or shift rotations."]
["Design invoice or payment trackers that flag every 30th day."]
["Create custom report formatting that highlights summary rows every 5 or 10 records."]
["Version Notes"]
["Available in all versions of Excel (2007 and newer), including Excel 365. The MOD function is a core mathematical function with no version limitations."]
[]
[]
[]
["Invoice #","Inv. number","Audit flag"]
["INV-0001",1,{"$f":"IF(MOD(B76, 5)=0, \"Audit\", \"\")","v":""},null,"Audit flag column: Every time the invoice number divides evenly by 5, the remainder is zero — and that's your audit trigger. The IF wraps it: if the remainder equals zero, flag it; otherwise leave the cell blank. Change the 5 to any number to shift your cycle — every 3rd invoice, every 10th, every 12th, whatever your compliance interval requires.\n\nFormula: =IF(MOD(B76, 5)=0, \"Audit\", \"\")"]
["INV-0002",2,{"$f":"IF(MOD(B77, 5)=0, \"Audit\", \"\")","v":""}]
["INV-0003",3,""]
["INV-0004",4,""]
["INV-0005",5,"Audit"]
["INV-0006",6,""]
["INV-0007",7,""]
["INV-0008",8,""]
["INV-0009",9,""]
["INV-0010",10,"Audit"]
["INV-0011",11,""]
["INV-0012",12,""]
["INV-0013",13,""]
["INV-0014",14,""]
["INV-0015",15,"Audit"]
[]
[]
["Breakdown Formula: =IF(MOD(B76, 5)=0, \"Audit\", \"\"):"]
["Part","Meaning"]
["MOD(B2, 5)","Calculates the remainder when the number in cell B2 is divided by 5."]
[{"$f":"0","v":0},"Checks if the remainder is zero (meaning B2 is perfectly divisible by 5)."]
["IF( ... )","If the condition is true → return \"Audit\" If false → return blank (\"\")"]
["\"Audit\"","Text that appears when condition is met"]
["\"\"","Empty text (blank cell) when condition is not met"]

{"sheet":"Percent","index":14,"n_rows":63,"n_cols":19,"rows_kept":63,"cut":false,"hidden":false,"merged":[[42,1,42,13],[43,1,43,13],[44,1,44,13],[45,1,45,13],[35,1,35,13],[36,1,36,13],[37,1,37,13],[38,1,38,13],[39,1,39,13],[40,1,40,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 15 of 21: Percent"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"Table-Contents","Go to Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"TOC","Go to TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["📊 PERCENTOF() Function","Calculates percentage of total","‘=PERCENTOF(A1, A1:A10) → returns decimal (format as %)"]
[]
[]
["PERCENTOF() Function"]
["What it does: The PERCENTOF function calculates what percentage one number is of another (or of a total). It’s a clean, modern way to show proportions — for example, “Salesperson X made up 12.4% of total regional sales.” It returns the result as a decimal that you can format as a percentage.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"=PERCENTOF(R35,$R$39)"]
[]
["The Trick - How to do it",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Month","Sales","Percent of Sales"]
["Step","Instruction",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Jan",200]
[1,"Click in the cell where you want the percentage result",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Feb",300]
[2,"Type the formula: =PERCENTOF(part, total)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Mar",400]
[3,"Example: =PERCENTOF(R35,$R$39) (where R35 is the part and R39 is the total)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Apr",100]
[4,"Press Enter",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"TOTALs",{"$f":"SUM(R35:R38)","v":1000},{"$f":"SUM(S35:S38)","v":0}]
[5,"Format the cell as Percentage (Home → Number → Percentage)"]
[6,"Copy the formula down for other rows"]
[]
[null,"Formula Breakdown (for better understanding):"]
[null,"PERCENTOF(part, total) → Divides the first number by the second and multiplies by 100 internally"]
[null,"Example: =PERCENTOF(450, 1200) returns 0.375 (which displays as 37.5%)"]
[null,"You can also use it with cell references for dynamic calculations"]
[]
["Alternative (for older Excel versions): If PERCENTOF is not available, use the classic formula: = part / total (then format as Percentage)"]
[]
["Pro Tips"]
["Lock the total cell with absolute references ($B$15) when copying the formula down."]
["Combine with Conditional Formatting to highlight high or low percentages."]
["Use it inside charts or dashboards for clear proportion visuals."]
["Add rounding with =ROUND(PERCENTOF(B5,$B$15),3) for cleaner decimals."]
["Why It's Great"]
["PERCENTOF makes percentage calculations much more readable and intuitive compared to the traditional division method. It clearly communicates proportions, which helps stakeholders quickly understand relative performance."]
["Real-World Uses"]
["Show what percentage each salesperson contributes to total sales."]
["Analyze department expenses as a percentage of the overall budget."]
["Create product mix reports showing each item’s share of total revenue."]
["Build scorecards that display goal achievement percentages."]
["Version Notes"]
["The PERCENTOF function is available in Excel 365 (Insider Beta and newer versions) and Excel 2021+. For older versions, use the classic part/total method."]

{"sheet":"UniqueCountA","index":15,"n_rows":94,"n_cols":17,"rows_kept":94,"cut":false,"hidden":false,"merged":[[38,1,38,11],[37,1,37,11],[36,1,36,11],[45,1,45,11],[44,1,44,11],[43,1,43,11],[42,1,42,11],[40,1,40,11],[39,1,39,11]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 16 of 21: UniqueCountA"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"x-Table-Contents","Go to x-Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"x-TOC","Go to x-TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["UNIQUE + COUNTA for \"Quick Audits\"","Checks data integrity","=COUNTA(UNIQUE(Range))"]
[]
["UNIQUE + COUNTA for \"Quick Audits\"",null,null,null,null,null,null,null,null,null,null,null,null,"Fruits list begins in cell A65"]
["What it does: Combining the UNIQUE and COUNTA functions gives you a super-fast way to audit your data. UNIQUE extracts a list of distinct values, and COUNTA counts how many unique items exist. This is excellent for quickly spotting duplicates, checking data completeness, or verifying how many unique entries are in a column.",null,null,null,null,null,null,null,null,null,null,null,null,"Lists one of each item in the list","How many different fruits in the list","Counts how many times each fruit is in the list","List the unique items in alphabetical order"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Dynamic Array/Spill",null,"Fill this down","Dynamic Array/Spill"]
["The Trick - How to do it",null,null,null,null,null,null,null,null,null,null,null,null,"UNIQUE","COUNTA","COUNTIF","SORT"]
["Step","Instruction",null,null,null,null,null,null,null,null,null,null,null,"=UNIQUE(A65:A94)","=COUNTA(UNIQUE(A65:A94))","=COUNTIF($A$65:$A$94,N34)","=SORT(UNIQUE(A65:A94))"]
["Our data is found in cells A64:A94 - the header is Fruit in A64"]
[1,"Select a blank cell where you want the list of unique values (N34 in this example)"]
[2,"Enter the formula: =UNIQUE(range) Example: =UNIQUE(A65:A94)"]
[3,"In O34, count the unique items: =COUNTA(UNIQUE(A65:A94))"]
[4,"In P34, count how many of each item =COUNTIF($A$65:$A$94,N34)"]
[5,"Format the unique list nicely (borders, sorting, etc.)"]
[6,"Sort the results with =SORT(UNIQUE(A65:A94))"]
[]
[null,"Formula Breakdown (for better understanding):"]
[null,"UNIQUE(B2:B1000) → Automatically extracts all distinct values from the range and spills them downward."]
[null,"COUNTA(UNIQUE(B2:B1000)) → Counts how many unique items were found."]
[null,"The result updates automatically when you add, remove, or change data in the source range."]
[]
["Pro Tips"]
["Add SORT() around UNIQUE for an automatically sorted list: =SORT(UNIQUE(range))."]
["Use this on ID numbers, names, product codes, or account numbers to quickly audit for duplicates."]
["Combine with FILTER for more powerful audits: =COUNTA(UNIQUE(FILTER(range, condition)))."]
["Place these audit formulas on a separate “Dashboard” or “Summary” sheet."]
["Why It's Great"]
["This combination gives you instant visibility into the quality and structure of your data. Instead of manually removing duplicates or using complex PivotTables, you get instant answers with just one or two simple formulas."]
["It’s a perfect \"Wake-Up\" check for data integrity. If you have a list of 1,000 transactions and want to know how many unique customers are actually represented, this does it in a fraction of a second."]
["Real-World Uses"]
["Quickly check how many unique customers are in a sales list."]
["Audit employee IDs or invoice numbers for duplicates or missing entries."]
["Verify product codes or department names in large datasets."]
["Create summary dashboards that show “Total Unique Items” at a glance."]
["Version Notes"]
["Requires Excel 365 or Excel 2021 (Dynamic Arrays feature). Not available in Excel 2019 or earlier versions."]
[]
["Fruit"]
["Apple"]
["Banana"]
["Apple"]
["Orange"]
["Banana"]
["Grapes"]
["Apple"]
["Mango"]
["Orange"]
["Kiwi"]
["Banana"]
["Grapes"]
["Apple"]
["Mango"]
["Peach"]
["Kiwi"]
["Banana"]
["Apple"]
["Orange"]
["Mango"]
["Banana"]
["Mango"]
["Mango"]
["Banana"]
["Banana"]
["Grapes"]
["Apple"]
["Mango"]
["Orange"]
["Kiwi"]

{"sheet":"XLookup","index":16,"n_rows":94,"n_cols":11,"rows_kept":94,"cut":false,"hidden":false,"merged":[[41,8,41,9],[41,5,42,6],[30,0,32,2],[55,0,55,7],[57,1,57,7],[58,1,58,7],[59,1,59,7],[60,1,60,7],[61,1,61,7],[62,1,62,7],[64,1,64,7],[65,1,65,7]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 17 of 21: XLookup"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"x-Table-Contents","Go to x-Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"x-TOC","Go to x-TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[null,null,null,null,null,"Credit Score","Rate",null,"Credit Score","Rate"]
["🔧","🌟","📌",null,null,300,0.12,null,620,"=XLOOKUP(I26,F26:F31,G26:G31)","👈 XLOOKUP basic example"]
["Tip","What It Does","How to Use It",null,null,580,0.1]
["XLOOKUP","Finds Stuff","Get creative!",null,null,620,8.5000000000000006E-2,null,null,"=XLOOKUP(I26,credit_score,i_rate)"]
[null,null,null,null,null,670,6.7000000000000004E-2,null,null,"Example using named ranges"]
["A sample of the basic XLOOKUP is in J26",null,null,null,null,720,5.5E-2]
["=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])",null,null,null,null,780,0.04]
[]
[]
["We used what is in cell I26 to find the credit score and return the interest rate in J26."]
[]
["XLOOKUP using MATCH MODE",null,null,null,null,null,null,null,"Credit Score","Rate"]
["The 4th argument of XLOOKUP (Match Mode) can handle approximate matches in ways VLOOKUP cannot.",null,null,null,null,null,null,null,650,"=XLOOKUP(I37,F26:F31,G26:G31,,-1)","The -1 tells it to find the next lowest credit score"]
["Formula: =XLOOKUP(Value, LookupArray, ReturnArray, , -1)"]
["Find the next smaller match: =XLOOKUP(I37,F26:F31,G26:G31,,-1)",null,null,null,null,null,null,null,"Credit Score","Rate"]
["Find the next higher match: =XLOOKUP(I37,F26:F31,G26:G31,,1)",null,null,null,null,null,null,null,650,"=XLOOKUP(I40,F26:F31,G26:G31,,1)","The 1 tells it to find the next highest credit score"]
[]
[null,null,null,null,null,"DATA TO USE FOR THE XLOOKUP FUNCTION",null,null,"XLOOKUP WILDCARD EXAMPLES"]
[null,null,null,null,null,null,null,null,"Data to match\n👇","Formula\n👇"]
["XLOOKUP using WILDCARD MATCH",null,null,null,null,"Company","Rating",null,"Company","Rating"]
["Allows you to find a value using partial text patterns:",null,null,null,null,"ACME Co","AA-",null,"ACME*","=XLOOKUP(I45,F45:F49,G45:G49,\"Not there\",2)","The 2 at the end tells XLOOKUP to use the wildcard"]
[" *  matches any sequence of characters",null,null,null,null,"Xyz Co","BB+",null,"ACME","=XLOOKUP(I46,F45:F49,G45:G49,\"Not there\",2)"]
["?  matches any single character",null,null,null,null,"Your ACME","A",null,"*ACME","=XLOOKUP(I47,F45:F49,G45:G49,\"Not there\",2)"]
["You need data for any entity that contains the word “ACME”.",null,null,null,null,"Our ACME","BB+"]
["NOTE the 2 at the end tells XLOOKUP to use the wildcard",null,null,null,null,"US Co","NA",null,"=FILTER(F45:G48, ISNUMBER(SEARCH(\"ACME\", F45:F48)), \"No matches\")"]
["Use the wildcard behind ACME (I45):"]
[" =XLOOKUP(I45,F45:F49,G45:G49,\"Not there\",2)"]
["Just find ACME (I46):  =XLOOKUP(I46,F45:F49,G45:G49,\"Not there\",2)"]
["Use the wildcard before ACME (I47):  =XLOOKUP(I47,F45:F49,G45:G49,\"Not there\",2)"]
["You want all rows containing ACME (I48): =FILTER(F45:G48, ISNUMBER(SEARCH(\"ACME\", F45:F48)), \"No matches\")"]
[]
["The Trick - How to do it"]
["Step","Instruction"]
[1,"Start typing =XLOOKUP(      in your cell"]
[2,"Enter the lookup value, lookup array, and return array as usual"]
[3,"Add the match_mode argument (4th argument) for advanced behavior"]
[4,"Add the search_mode argument (5th argument) for binary or reverse search"]
[5,"Press Enter and watch the results spill if returning multiple columns"]
[6,"Test different match modes to see their effects"]
[]
[null,"Formula Breakdown (for better understanding):"]
[null,"Basic structure: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])"]
[]
[null,"Advanced Match Modes:"]
[null,"2 = Wildcard match (* and ?)"]
[null,"* = any number of characters"]
[null,"? = use a ? per character"]
[null,"1 = Exact or next larger"]
[null,"-1 = Exact or next smaller"]
[null,"0 = Exact match (default)"]
[]
[null,"Search Modes:"]
[null,"2 = Binary search (ascending) — very fast on sorted data"]
[null,"-2 = Binary search (descending)"]
[null,"-1 = Search from last to first"]
[]
["Pro Tips"]
["Use Wildcard mode (2) when searching partial text like product codes or names."]
["Use Binary Search (2 or -2) on very large sorted datasets for lightning-fast performance."]
["Return multiple columns by putting arrays in curly braces {}."]
["Always sort your data when using binary search mode."]
["Why It's Great"]
["These advanced options make XLOOKUP incredibly flexible — one function can replace VLOOKUP, HLOOKUP, INDEX/MATCH, and more, while offering better performance and cleaner results."]
["Real-World Uses"]
["Search for partial customer names using wildcards."]
["Perform ultra-fast lookups on massive sorted datasets using binary search."]
["Pull multiple pieces of information (price, category, stock) with one formula."]
["Build powerful dashboards that need flexible and fast lookups."]
["Version Notes"]
["Available in Excel 365 and Excel 2021+. Not available in Excel 2019 or earlier versions."]

{"sheet":"TextSplit","index":17,"n_rows":73,"n_cols":16,"rows_kept":73,"cut":false,"hidden":false,"merged":[[48,1,48,13],[36,1,36,13],[37,1,37,13],[45,1,45,13],[46,1,46,13],[47,1,47,13],[51,1,51,13],[52,1,52,13],[53,1,53,13],[54,1,54,13],[50,1,50,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 18 of 21: TextSplit"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"x-Table-Contents","Go to x-Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"x-TOC","Go to x-TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["TEXTSPLIT with Row + Column Delimiters","splits complex delimited text","=TEXTSPLIT(A2, \";\", \"|\", TRUE)"]
[]
["TEXTSPLIT with Row + Column Delimiters"]
["What it does: The TEXTSPLIT function splits text into multiple columns and rows based on delimiters. Using both row and column delimiters allows you to convert a single block of text (like copied data from a website or report) into a clean, organized table in one formula."]
[]
["The Trick - How to do it",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"US;GB;JP_AA;A+;AA-_100M;250M;80M"]
["Step","Instruction"]
["You receive a string like this that you need to split: US;GB;JP_AA;A+;AA-_100M;250M;80M",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"=TEXTSPLIT(P33,\";\",\"_\")"]
["(No spaces; the pattern is row1column1;row1column2;row1column3,row2…)"]
[1,"Click in the cell where you want the split results to appear (P35 in this example)"]
[2,"Enter the TEXTSPLIT formula with both column and row delimiters"]
[null,"=TEXTSPLIT(P33,\";\",\"_\")"]
[null,"First delimiter (;) = column delimiter.",null,null,null,null,null,null,null,null,null,null,null,null,null,"John,25;Sarah,32;Mike,29"]
[null,"It splits the text horizontally into three segments:"]
[null,"US;GB;JP | AA;A+;AA- | 100M;250M;80M",null,null,null,null,null,null,null,null,null,null,null,null,null,"=TEXTSPLIT(P40, \",\", \";\")"]
[null,"Second delimiter (_) = row delimiter."]
[null,"Within each segment, it breaks vertically at semicolons."]
[null,"The result spills into a 3×3 range"]
[3,"Press Enter — the results will spill into multiple rows and columns"]
[4,"Format the spilled range (add borders, headers, etc.)"]
[5,"Adjust delimiters if needed to match your data"]
[6,"(Optional) Wrap with TRIM() to clean extra spaces"]
[]
[null,"Formula Breakdown (for better understanding):"]
[null,"Basic syntax: =TEXTSPLIT(text, column_delimiter, row_delimiter, [ignore_empty], [match_mode], [pad_with])"]
[null,"Example: =TEXTSPLIT(A1, \";\", \"_\")"]
[null,"\";\" → Splits into columns (semicolon delimiter)"]
[null,"\"_\" → Splits into rows (underscore delimiter)"]
[]
["Real Example: If cell A1 contains: John,25;Sarah,32;Mike,29"]
["Formula: =TEXTSPLIT(A1, \",\", \";\") → Creates a clean 3-row, 2-column table (Name | Age)"]
[]
["Pro Tips"]
["Use different delimiters for rows and columns (common: comma for columns, semicolon or pipe for rows)."]
["Add TRUE as the 4th argument to ignore empty values: =TEXTSPLIT(A1, \",\", \";\", TRUE)."]
["Combine with TRIM: =TRIM(TEXTSPLIT(...)) to clean messy data."]
["Use it to quickly convert pasted text from PDFs, websites, or emails into usable tables."]
["Why It's Great"]
["TEXTSPLIT with both row and column delimiters is a massive time-saver. It can transform messy, unstructured text into a perfect table instantly — something that used to require multiple steps and helper columns."]
["Real-World Uses"]
["Convert copied data from websites or reports into clean Excel tables."]
["Split exported text files or logs into organized columns and rows."]
["Clean up messy survey responses or contact lists."]
["Quickly reformat data received from other systems or team members."]
["Version Notes"]
["Requires Excel 365 (Dynamic Arrays). Not available in Excel 2021, Excel 2019, or earlier versions."]

{"sheet":"IFERROR","index":18,"n_rows":63,"n_cols":23,"rows_kept":63,"cut":false,"hidden":false,"merged":[[39,1,39,13],[41,1,41,13],[42,1,42,13],[44,1,44,13],[43,1,43,13],[34,1,34,13],[35,1,35,13],[36,1,36,13],[37,1,37,13],[38,1,38,13]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 19 of 21: IFERROR"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"x-Table-Contents","Go to x-Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"x-TOC","Go to x-TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":""}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["🧘‍♀️ IFERROR() with Style","Handles errors gracefully","=IFERROR(A1/B1, \"Fallback value\")"]
[]
["IFERROR() with Style"]
["What it does: The IFERROR function catches errors (like #N/A, #DIV/0!, #VALUE!, etc.) and replaces them with a clean, user-friendly result instead of showing ugly error messages. Adding “style” means returning professional-looking messages, blanks, zeros, or custom text that keeps your reports clean and polished."]
[]
["The Trick - How to do it",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Person","Monthly Debt","Monthly Income","Debt-to-Income Ratio"]
["Step","Instruction",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Juan",1200,4000,"=S34/T34",null,"👈 Will create errors"]
[1,"Identify a formula that might produce an error",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Lisa",800,0]
[2,"Wrap it with IFERROR: =IFERROR(your_formula, value_if_error)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Jo",1500,5000]
[3,"Choose a stylish replacement: blank (\"\"), zero (0), “N/A”, or a helpful message",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Mai",600,3500]
[4,"Press Enter",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Tom",2000,0]
[5,"Copy the formula down as needed"]
[6,"(Optional) Combine with Conditional Formatting to highlight handled errors"]
[]
[null,"Formula Breakdown (for better understanding):",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Person","Monthly Debt","Monthly Income","Debt-to-Income Ratio"]
[null,"Basic syntax: =IFERROR(value, value_if_error)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Juan",1200,4000,"=IFERROR(S43/T43, \"N/A - No Income\")",null,"👈 Fixes the errors"]
[null,"value: the formula you want to try",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Lisa",800,0]
[null,"value_if_error: what to show if the formula fails",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Jo",1500,5000]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Mai",600,3500]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Tom",2000,0]
[]
["Pro Tips"]
["Use \"\" (blank) for very clean reports."]
["Use a light gray text like IFERROR(..., \"—\") for professional dashboards."]
["Nest IFERROR with other functions: =IFERROR(IF( condition, formula), \"Message\")."]
["Don’t overuse it — only wrap formulas where errors are expected and acceptable."]
["Combine with Data Validation or Conditional Formatting to make “Not Found” cells stand out subtly."]
["Why It's Great"]
["IFERROR with style keeps your spreadsheets looking professional and trustworthy. Instead of confusing error codes, viewers see clean, meaningful messages that improve readability and user experience."]
["Real-World Uses"]
["Clean up lookup formulas in sales reports (“Not Found” instead of #N/A)."]
["Prevent #DIV/0! errors in ratio and percentage calculations."]
["Create robust financial models that handle missing data gracefully."]
["Build client-facing dashboards that remain polished even when data is incomplete."]
["Version Notes"]
["Available in all versions of Excel since Excel 2007. Works in Excel 365, 2021, 2019, and earlier versions with no limitations."]

{"sheet":"Clean","index":19,"n_rows":60,"n_cols":9,"rows_kept":60,"cut":false,"hidden":false,"merged":[[35,1,35,3],[33,0,33,3],[45,1,45,3],[44,1,44,3],[43,1,43,3],[42,1,42,3],[40,1,40,3],[39,1,39,3],[38,1,38,3],[37,1,37,3],[36,1,36,3]]}
["👇 Click the link to return to the TOC Sheet"]
[{"$f":"HYPERLINK(\"#TOC!A1\", \"👈 Back to TOC | \" & \"Sheet \" & _xlfn.SHEET() & \" of \" & _xlfn.SHEETS() & \": \" & _xlfn.TEXTAFTER(CELL(\"filename\", INDIRECT(\"A1\")), \"]\"))","v":"👈 Back to TOC | Sheet 20 of 21: Clean"}]
["NUMBER","SHEET NAME","LINK","CURRENT SHEET?"]
[{"$f":"_xlfn.SEQUENCE(_xlfn.SHEETS())","v":1},{"$f":"TRANSPOSE(SheetList)","v":"Camera"},{"$f":"HYPERLINK(\"#'\" & B4 & \"'!A1\", \"Go to \" & B4)","v":"Go to Camera"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A3),\"]\")=B4, \"👈 You are here\", \"\")","v":""}]
[2,"Linked_Pic",{"$f":"HYPERLINK(\"#'\" & B5 & \"'!A1\", \"Go to \" & B5)","v":"Go to Linked_Pic"},{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A4),\"]\")=B5, \"👈 You are here\", \"\")","v":""}]
[3,"Guage Chart","Go to Guage Chart",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A5),\"]\")=B6, \"👈 You are here\", \"\")","v":""}]
[4,"Calendar","Go to Calendar",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A6),\"]\")=B7, \"👈 You are here\", \"\")","v":""}]
[5,"SheetTab Name","Go to SheetTab Name",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A7),\"]\")=B8, \"👈 You are here\", \"\")","v":""}]
[6,"NamedRange","Go to NamedRange",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A8),\"]\")=B9, \"👈 You are here\", \"\")","v":""}]
[7,"Dropdown","Go to Dropdown",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A9),\"]\")=B10, \"👈 You are here\", \"\")","v":""}]
[8,"Validation","Go to Validation",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A10),\"]\")=B11, \"👈 You are here\", \"\")","v":""}]
[9,"Grouping","Go to Grouping",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A11),\"]\")=B12, \"👈 You are here\", \"\")","v":""}]
[10,"x-Table-Contents","Go to x-Table-Contents",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A12),\"]\")=B13, \"👈 You are here\", \"\")","v":""}]
[11,"x-TOC","Go to x-TOC",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A13),\"]\")=B14, \"👈 You are here\", \"\")","v":""}]
[12,"Consolidate","Go to Consolidate",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A14),\"]\")=B15, \"👈 You are here\", \"\")","v":""}]
[13,"ISODD","Go to ISODD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A15),\"]\")=B16, \"👈 You are here\", \"\")","v":""}]
[14,"MOD","Go to MOD",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A16),\"]\")=B17, \"👈 You are here\", \"\")","v":""}]
[15,"Percent","Go to Percent",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A17),\"]\")=B18, \"👈 You are here\", \"\")","v":""}]
[16,"UniqueCountA","Go to UniqueCountA",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A18),\"]\")=B19, \"👈 You are here\", \"\")","v":""}]
[17,"XLookup","Go to XLookup",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A19),\"]\")=B20, \"👈 You are here\", \"\")","v":""}]
[18,"TextSplit","Go to TextSplit",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A20),\"]\")=B21, \"👈 You are here\", \"\")","v":""}]
[19,"IFERROR","Go to IFERROR",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A21),\"]\")=B22, \"👈 You are here\", \"\")","v":""}]
[20,"Clean","Go to Clean",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A22),\"]\")=B23, \"👈 You are here\", \"\")","v":"👈 You are here"}]
[21,"OPEN","Go to OPEN",{"$f":"IF(_xlfn.TEXTAFTER(CELL(\"filename\",A23),\"]\")=B24, \"👈 You are here\", \"\")","v":""}]
[]
["🔧","🌟","📌"]
["Tip","What It Does","How to Use It"]
["🧼 Clean & Trim","Remove hidden characters/spaces","‘=TRIM(CLEAN(A1))"]
[]
[]
["Clean & Trim"]
["What it does: The TRIM function removes extra spaces from text (leading, trailing, and multiple spaces between words). The CLEAN function removes non-printable characters (such as line breaks, tabs, and special symbols often brought in from other systems). Together, they are the go-to tools for cleaning messy imported or copied data."]
[]
["The Trick - How to do it",null,null,null,null,null,"CUSTOMER NAME",null,"CUSTOMER NAME"]
["Step","Instruction",null,null,null,null,"John   Wynner"]
[1,"Find the mess you want to clean",null,null,null,null,"       Mai Le"]
[2,"In a new column, enter the combined formula: =TRIM(CLEAN(G35))",null,null,null,null,"Cam J           Barnes"]
[3,"Press Enter and copy the formula down"]
[4,"(Optional) Convert the formulas to values: Copy → Paste Special → Values"]
[5,"Replace the original column with the cleaned version"]
[6,"Check results and re-apply formatting if needed"]
[]
[null,"Formula Breakdown (for better understanding):"]
[null,"CLEAN(text) → Removes non-printable characters (e.g., from databases or web data)"]
[null,"TRIM(text) → Removes leading/trailing spaces and reduces multiple spaces between words to a single space"]
[null,"TRIM(CLEAN(text)) → Best practice: Clean first, then trim"]
[]
["Pro Tips"]
["Always nest them as TRIM(CLEAN(...)) for maximum cleaning power."]
["Create a reusable cleaning formula and keep it on a helper sheet."]
["Use Power Query for very large datasets — it has more powerful cleaning tools."]
["Why It's Great"]
["Clean & Trim turns messy, unreliable data into professional, usable information instantly. These two simple functions prevent many errors in lookups, sorting, filtering, and reporting."]
["Real-World Uses"]
["Clean customer names, addresses, and emails imported from CRM systems."]
["Fix data copied from websites, PDFs, or Word documents."]
["Prepare messy exported reports before building PivotTables or dashboards."]
["Standardize product codes and inventory lists."]
["Version Notes"]
["Both CLEAN and TRIM functions are available in all versions of Excel (including Excel 2007 and newer). No version limitations."]

{"sheet":"OPEN","index":20,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}