Excel Guides
Sky Dashboards knowledge base
Plain-English walkthroughs of the Excel functions, shortcuts and techniques I use to build dashboards.
Each guide has the syntax, a worked example you can copy and the mistakes to avoid. 32 guides across 10 topics, free to read.
Shortcuts & Formatting 5Lookup & Reference 3Logical 3Math & Statistics 3Date & Time 2Text 2Dynamic Arrays & LAMBDA 4Financial 3What-If Analysis 2Dashboard Building 5
Start here
Alt H O WShortcuts & FormattingAdjust Column WidthResize columns with the mouse, set an exact width, AutoFit to the contents and do it all from the keyboard.Read guide → 6 minCtrl+ArrowShortcuts & FormattingExcel Shortcuts Cheat SheetThe shortcuts worth learning, grouped by job: moving, selecting, editing, formatting, formulas and the ribbon.Read guide → 7 min=XLOOKUP()Lookup & ReferenceXLOOKUP FunctionFind a value in one range and return the matching value from another, in any direction, with a built-in fallback.Read guide → 7 min=INDEX(MATCH())Lookup & ReferenceINDEX MATCHPair two functions into a lookup that looks left, survives inserted columns and works in every version of Excel.Read guide → 6 min=SUMIFS()Math & StatisticsSUMIFS FunctionAdd up only the rows that match your conditions, from a single category to a date range with several filters.Read guide → 6 minAlt N V TDashboard BuildingPivot TablesSummarise hundreds of rows by month, product or channel in a few clicks, then slice the answer any way you like.Read guide → 7 min
All Excel guides by topic
Shortcuts & Formatting
Move faster and keep sheets readable.
$A$1Absolute & Relative ReferencesRelative, absolute and mixed cell references explained with small worked examples. Learn the F4 cycle, lock a single input cell, fill a whole grid with one formula and see how references behave in Excel Tables.Read guide →Alt H O WAdjust Column WidthEvery way to change column width in Excel: dragging, the Column Width dialog, AutoFit, the Alt H O W shortcut and copying widths between sheets. Includes the layout habits I use to keep dashboards lined up.Read guide →#,##0Custom Number FormatsHow custom number formats work in Excel: the four sections, the codes that build them, scaling to thousands and millions with trailing commas, colours, conditions and date codes. Includes a reference table of codes with examples.Read guide →Ctrl+ArrowExcel Shortcuts Cheat SheetA practical Excel shortcut reference for Microsoft 365 on Windows, with Mac equivalents where they are reliable. Covers navigation, selection, editing, number formats, F2, F4 and F9, rows and columns, sheets and ribbon key tips.Read guide →Alt W F FFreeze PanesHow to freeze the top row, the first column or any block of rows and columns in Excel, with the Alt W F shortcuts. Also covers unfreezing, Split versus Freeze Panes and the usual reasons the command misbehaves.Read guide →Lookup & Reference
Pull the right value from another table.
=INDEX(MATCH())INDEX MATCHHow INDEX and MATCH work alone and together, why the pair is sturdier than VLOOKUP, two-way lookups with INDEX MATCH MATCH, lookups on more than one condition, and where XMATCH fits in.Read guide →=VLOOKUP()VLOOKUP FunctionHow VLOOKUP works, the difference between FALSE and TRUE, why approximate match needs sorted data, the usual causes of #N/A, and how to stop a hard-coded column number from breaking your formulas.Read guide →=XLOOKUP()XLOOKUP FunctionHow XLOOKUP works, argument by argument: exact and approximate matches, left lookups, returning several columns at once, finding the last match, two-way lookups and wildcards. Includes which Excel versions have it.Read guide →Logical
Make formulas decide, and fail gracefully.
=IF()IF FunctionHow the IF function works, the comparison operators it relies on, returning text, numbers or blanks, nesting IFs, combining them with AND, OR and NOT, and the mistakes that cause wrong answers.Read guide →=IFERROR()IFERROR FunctionHow IFERROR works, the seven error types it catches, why IFNA is often the better choice for lookups, how to handle division by zero, and how to avoid hiding mistakes you need to see.Read guide →=IFS()IFS & SWITCH FunctionsHow IFS and SWITCH work, the TRUE catch-all that stops IFS returning #N/A, default values in SWITCH, when each beats a nested IF, and which Excel versions support them. With a customer tier example.Read guide →Math & Statistics
Conditional totals, counts and products.
=COUNTIFS()COUNTIFS FunctionHow COUNTIFS counts the rows that meet every condition you set. Covers blanks and non-blanks, wildcards, date windows, finding duplicates and how COUNTIFS compares with COUNTIF, COUNTA and COUNTBLANK.Read guide →=SUMIFS()SUMIFS FunctionHow SUMIFS works, with criteria that use operators, cell references, wildcards and date ranges. Covers OR logic, the SUMIF argument order trap and a monthly sales-by-category summary you can reuse.Read guide →=SUMPRODUCT()SUMPRODUCT FunctionHow SUMPRODUCT multiplies ranges and adds the results. Covers weighted totals and averages, conditions with boolean arrays and the double unary, OR logic, performance on large ranges and when SUMIFS is the better choice.Read guide →Date & Time
Month ends, working days and schedules.
=EOMONTH()EOMONTH FunctionHow EOMONTH returns the last day of a month a set number of months away. Covers first-of-month dates, monthly timeline headers, days in a month, EDATE for same-day dates and why the result can appear as a number.Read guide →=NETWORKDAYS()NETWORKDAYS FunctionHow NETWORKDAYS counts the working days between two dates, including both ends. Covers holiday lists, NETWORKDAYS.INTL weekend codes and strings, and WORKDAY for deadlines, with a studio project timeline as the example.Read guide →Text
Join, split and format text cleanly.
=TEXT()TEXT FunctionHow the TEXT function applies a number format inside a formula. Covers format codes for numbers, percentages and dates, dynamic chart titles and KPI labels, why the result cannot be summed and when cell formatting is the better choice.Read guide →=TEXTJOIN()TEXTJOIN FunctionHow TEXTJOIN joins text with a delimiter and skips empty cells. Compared with the & operator, CONCAT and CONCATENATE, plus joining a filtered list, line breaks with CHAR(10) and TEXTSPLIT for splitting text back apart.Read guide →Dynamic Arrays & LAMBDA
Formulas that spill and functions you write yourself.
=FILTER()FILTER FunctionHow to use FILTER in Excel: the syntax, spill ranges and the #SPILL! error, AND and OR conditions, sorting the result, top-N lists with TAKE and referring to the result with the # operator.Read guide →=LAMBDA()LAMBDA FunctionHow to use LAMBDA in Excel: the syntax, testing a LAMBDA in a cell, saving it as a named function in Name Manager, practical margin and tax examples, optional parameters and the MAP, BYROW, BYCOL, REDUCE and SCAN helpers.Read guide →=LET()LET FunctionHow to use LET in Excel: the syntax, naming intermediate results, a before and after rewrite of a long formula, the rules for names, multi-line layout with Alt+Enter and combining LET with FILTER.Read guide →=UNIQUE()UNIQUE FunctionHow to use UNIQUE in Excel: distinct and exactly-once lists, sorted unique lists, counting distinct values, feeding a drop-down list from a spill range and finding unique combinations across columns.Read guide →Financial
Returns, present values and loan payments.
=PMT()PMT FunctionHow to use PMT in Excel: the arguments, matching monthly rates and periods, why the result is negative, a worked equipment loan, saving towards a target with fv, and a short amortisation table built with IPMT and PPMT.Read guide →=XIRR()XIRR FunctionHow to use XIRR in Excel: the syntax, why dates matter compared with IRR, the sign convention, a worked equipment example and how to fix #NUM! errors. Also covers IRR and MIRR for evenly spaced cash flows.Read guide →=XNPV()XNPV FunctionHow to use XNPV in Excel: the syntax, how each cash flow is discounted by its date, a worked example with dated cash flows, the NPV first-period timing gotcha and how XNPV relates to XIRR.Read guide →What-If Analysis
Test assumptions without rebuilding the model.
Alt A W GGoal SeekGoal Seek changes one input until a formula hits the target you set. This guide covers the dialog, a worked break-even example for a candle shop, why the answer is approximate and when to switch to Solver.Read guide →Alt A W TSensitivity Analysis with Data TablesData tables rerun a formula for a list of inputs, or for every combination of two. Learn one- and two-variable tables, row versus column input cells, the TABLE array, speed settings and how to colour a price by volume profit grid.Read guide →Dashboard Building
The tools behind every Sky Dashboards template.
Alt H LConditional FormattingA practical guide to conditional formatting: Highlight Cells and Top/Bottom rules, Data Bars, Color Scales, Icon Sets, formula rules with the right $ anchoring, rule order and Stop If True, Tables, and keeping workbooks fast.Read guide →Alt A V VDrop-Down Lists (Data Validation)Step-by-step drop-down lists in Excel using Data Validation: typed sources, ranges, table-backed lists, dynamic lists from UNIQUE, dependent drop-downs, input messages, error alerts and checking existing data.Read guide →Ctrl+TExcel TablesHow to create, name and use Excel Tables. Covers Ctrl+T, structured references such as [@Amount] and #Totals, calculated columns, the total row, slicers, tables as chart and PivotTable sources, and converting back to a range.Read guide →Alt N V TPivot TablesHow to build a pivot table from an Excel Table, use the Rows, Columns, Values and Filters areas, change Summarize Values By and Show Values As, group dates, refresh, add slicers and timelines, and control GETPIVOTDATA.Read guide →Insert > SparklinesSparklinesHow to insert Line, Column and Win/Loss sparklines, the difference between the data range and the location range, grouped sparklines, markers, axis settings, empty cells, KPI tiles and how to remove them when the Delete key will not.Read guide →Rather skip the formulas?
Every technique here is already built into my ready-made dashboard templates. Add your rows and the charts update.
Browse templates →