Spreadsheet how-to recipes
Common spreadsheet tasks with copy-paste formulas for Microsoft Excel, Google Sheets, and LibreOffice Calc — each one executed and verified in a real engine, not just documented.
- How to average a range while ignoring zeros (and blanks) verified
- How to combine cells with a line break between them verified
- How to convert text that looks like a number into a real number verified
- How to count non-blank (non-empty) cells verified
- How to count the number of unique values in a range verified
- How to calculate the number of days between two dates verified
- How to extract the first word (text before the first space) verified
- How to get unique values (remove duplicates) with a formula verified
- How to hide formula errors (show 0 or blank instead of #DIV/0!, #N/A) verified
- How to join a list of cells into one comma-separated string verified
- How to look up a value with two criteria (multi-condition lookup) verified
- How to find the 2nd (or Nth) largest or smallest value verified
- How to calculate percentage change between two numbers verified
- How to calculate each row as a percentage of the total verified
- How to capitalize the first letter of each word (proper case) verified
- How to rank values (highest = 1) with a formula verified
- How to remove extra spaces from text verified
- How to round a number to the nearest multiple (5, 10, 0.25...) verified
- How to make a running (cumulative) total verified
- How to split text into separate columns with a formula verified
- How to sum values by category (conditional sum) with SUMIF verified
- How to sum the top N values in a range verified
- How to get the text after the last delimiter (e.g. a file extension) verified
- How to VLOOKUP to the left (return a value from a column before the match) verified
- How to SUMIF with a wildcard (sum rows that start with / contain text) verified