Spreadsheets
Formulas, lookup functions, pivot tables, validation and charts in Excel, LibreOffice and Google Sheets.
Choosing the right chart: lines, bars, pies and scatter plots
Which chart to use for each question: trend over time, comparison, composition or relationship between variables, plus axis and colour mistakes to avoid.
Comparing two lists: finding missing, new and changed entries
How to compare two lists in a spreadsheet: missing values, new entries, changed rows and duplicates, using COUNTIF, MATCH and FILTER with reliable keys.
Conditional formatting with formulas: highlighting whole rows
How to write formula-based conditional formatting rules: whole rows, due dates, duplicates, comparisons between columns and the right order of rules.
Data validation: drop-down lists and rules against input errors
How to use data validation to build drop-down lists, restrict numbers and dates, enforce custom rules and find the invalid values that are already present.
Dates and times in spreadsheets: how they really work
Why dates are numbers, how to calculate differences, working days, end-of-month due dates and hours worked, and how to avoid regional format problems.
Designing a spreadsheet that is readable and easy to maintain
How to organise a spreadsheet: separate data, parameters, calculations and results, data as a list, clear names, built-in checks and notes for users.
Dynamic arrays: FILTER, SORT and UNIQUE explained with examples
How dynamic array formulas work: FILTER with several conditions, SORT, UNIQUE, the # spill operator and the #SPILL! error, with real use cases.
Structured tables in Excel: why to use them for every list
What changes when you turn a range into a table: automatic expansion, readable structured references, calculated columns and a ready-made total row.
#N/A, #VALUE!, #REF!: what spreadsheet formula errors mean
A guide to spreadsheet error messages: #N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NUM! and #SPILL!, with their most frequent causes and how to fix each one.
Google Sheets: QUERY, IMPORTRANGE and functions Excel lacks
Typical Google Sheets functions: QUERY to interrogate data, IMPORTRANGE between files, ARRAYFORMULA, SPLIT and regular expressions, with examples.
INDEX and MATCH: flexible lookups in any spreadsheet program
How to combine INDEX and MATCH to look up values in any direction, in two dimensions and with several criteria, in Excel, LibreOffice Calc and Sheets.
LET and LAMBDA: more readable formulas and custom functions
How to use LET to name the steps of a formula and LAMBDA to create reusable custom functions in Excel and Google Sheets, with practical examples.
LibreOffice Calc vs Excel: practical differences and compatibility
What changes between Excel and LibreOffice Calc: ODS and XLSX formats, command names, separators, functions, macros and checks before sending a file.
Loan payments and amortisation schedules with PMT and IPMT
How to use PMT, IPMT, PPMT and NPER to work out a loan payment and build a month-by-month amortisation schedule in a spreadsheet, with checks.
Nested IFs: when to replace them with IFS, SWITCH or a table
How to write conditions with IF, AND and OR without getting lost, and when it is better to switch to IFS, SWITCH or a lookup table with a lookup.
Numbers stored as text: how to spot them and convert them
Why some numbers will not add up, how to spot them, how to convert them with VALUE and NUMBERVALUE, and how to handle decimal separators across countries.
Pivot tables: summarising thousands of rows in a few clicks
What a pivot table is, how to prepare the data, how to use rows, columns, values and filters, and how to group dates, with a step-by-step sales example.
Protecting sheets and cells: what really locks and what does not
How locked cells, sheet and workbook protection, opening passwords and protected ranges in Google Sheets work, and the limits of each one you should know.
Relative, absolute and mixed references in formulas
How relative, absolute and mixed references work in spreadsheets, when to use the dollar sign, and how to avoid mistakes when you copy formulas around.
Rounding in spreadsheets: number format or the ROUND function
Why number formatting does not really round, when to use ROUND and its variants, where stray cents come from and the limits of floating-point maths.
SUMIFS and COUNTIFS: totals with several conditions
How to use SUMIFS, COUNTIFS and AVERAGEIFS with text, numbers, dates and wildcards, and how to build a monthly summary that updates by itself.
Text functions: extracting, joining and cleaning spreadsheet data
How to use LEFT, MID, FIND, SUBSTITUTE, TRIM and TEXTJOIN to split names, extract codes and normalise messy lists with simple, repeatable formulas.
VLOOKUP: how it really works and where its limits are
A practical guide to VLOOKUP: the four arguments, exact versus approximate match, the most common mistakes, and when it is time to use something else.
What-if analysis: Goal Seek, data tables and Solver explained
How to answer «what if…?» questions with Goal Seek, one- and two-variable data tables, scenarios and Solver, starting from a simple pricing model.
XLOOKUP: the modern lookup function explained with examples
How to use XLOOKUP instead of VLOOKUP: syntax, looking to the left, a value when nothing is found, the last occurrence and band lookups with real examples.