By Imonikhe Ayeni
This practical Excel reference starts with workbook basics and formulas, then moves through lookups, data cleaning, PivotTables, charts, Power Query, Power Pivot, statistical analysis and automation.
Understand the workbook, worksheet, cells, ranges, tables and the habits that make analytical work reliable.
A1
B2:D20
Table1[Revenue]Keep raw data separate from calculations and outputs. Avoid merged cells inside datasets and use one header row.Build reliable calculations using relative, absolute and mixed references.
=B2*C2
=B2*$F$1
=$A2*B$1=SUM(B2:B100)
=AVERAGE(B2:B100)
=MIN(B2:B100)
=MAX(B2:B100)Use IF, IFS, AND, OR and error handling to translate business rules into reproducible logic.
=IF(C2>=80,"High","Standard")
=IFS(C2>=90,"A",C2>=80,"B",C2>=70,"C",TRUE,"Other")=IFERROR(A2/B2,0)Join information across tables using modern lookup functions and understand alternatives for older workbooks.
=XLOOKUP(A2,Customers[ID],Customers[Region],"Not found")=INDEX(Customers[Region],MATCH(A2,Customers[ID],0))Clean identifiers, construct labels and derive useful calendar fields.
=TRIM(A2)
=UPPER(A2)
=TEXTJOIN(" - ",TRUE,A2,B2,C2)=YEAR(A2)
=MONTH(A2)
=EOMONTH(A2,0)
=TODAY()Use FILTER, UNIQUE, SORT and SEQUENCE to create live analysis outputs without manual copying.
=FILTER(A2:D100,D2:D100="Wales")
=UNIQUE(B2:B100)=SORT(UNIQUE(B2:B100))Use tables to make models easier to read, extend and audit.
=[@Quantity]*[@UnitPrice]=SUBTOTAL(109,Table1[Revenue])Standardise values, remove duplicates and prevent bad inputs.
Restrict entries using lists, numbers, dates or custom formulas.=CLEAN(TRIM(A2))Summarise large datasets quickly and build interactive analytical views.
Rows define grouping, Columns split categories, Values aggregate measures and Filters/Slicers support interaction.Know when to use a worksheet formula, PivotTable calculation, Power Pivot measure or DAX measure.Choose visuals that answer a question clearly rather than decorating the worksheet.
Use bars for comparisons, lines for trends, scatterplots for relationships and cards/KPIs for headline metrics.Align objects, minimise clutter, use consistent number formats and place the most important insight first.Use Power Query for repeatable import, transformation and combination of data.
Get Data → transform types → filter → split/merge → append/join → load.Once a query is built, replace manual cleaning with Refresh where possible.Explore scenarios, targets and optimisation questions.
Find the input needed to reach a target output.Optimise an objective while respecting constraints.Use descriptive statistics and selected inferential tools responsibly.
=MEDIAN(B2:B100)
=STDEV.S(B2:B100)
=QUARTILE.INC(B2:B100,1)Use for regression, ANOVA, correlation and other standard analyses when appropriate.Automate repetitive tasks after the manual process is stable and understood.
Useful for repeatable workbook automation in modern Microsoft 365 workflows.Still important for many established desktop Excel workbooks and macros.Consolidate your skills through end-to-end projects.
Clean a sales dataset, calculate KPIs, build PivotTables and create a one-page dashboard.Build a refreshable Power Query pipeline, forecasting/what-if model and management dashboard.Use this section as a quick revision page before an interview or when a term comes up at work. The aim is to understand the idea well enough to explain it in plain language before memorising syntax.
A relative reference changes when copied. An absolute reference uses $ signs to lock the row, column or both.
XLOOKUP is easier to read and supports exact matching and left lookups directly. INDEX/MATCH remains useful in older Excel versions and legacy workbooks.
Power Query prepares and reshapes data. A PivotTable summarises already-loaded data for analysis.
Power Query handles data extraction and transformation. Power Pivot manages relationships and analytical measures in the Data Model.
Use a Table for structured datasets that should expand automatically. Named ranges are useful for readable references, parameters or fixed areas.
These short tasks test whether you can apply the tool, explain your reasoning and validate the result. In a live exercise, say your assumptions aloud and check the output rather than rushing straight to syntax.
Answer: Use XLOOKUP with an explicit not-found result.
=XLOOKUP(A2,Customers[ID],Customers[Region],"Not found")Answer: Use SUMIFS with criteria for region and year.
=SUMIFS(Sales[Revenue],Sales[Region],"Wales",Sales[Year],2026)Answer: Check whether ranges should be fixed with absolute references or whether Excel Table structured references would be safer.
Why: Many spreadsheet failures are reference problems rather than function problems.
Answer: Use Power Query's folder connector, apply the transformation once, combine the files, then refresh when new files arrive.
Why: This is more reproducible than monthly copy-and-paste.
Answer: Use a PivotTable for fast interactive grouped summaries and slicing. Use formulas when you need cell-level logic, custom layouts or values that feed other calculations.