Excel formulas every admin assistant should know
Ten simple formulas that save time on everyday spreadsheet work.
You don't need advanced Excel for most admin work. These formulas cover a lot. They work in Excel and, with small differences, in Google Sheets. Replace the cell names with your own.
| What you want | Formula |
|---|---|
| Add numbers | =SUM(B2:B10) |
| Show a result based on a condition | =IF(C2>=100,"Yes","No") |
| Add numbers that match a condition | =SUMIF(A:A,"Pens",C:C) |
| Count items that match | =COUNTIF(B:B,"Unpaid") |
| Look up a value in another table | =VLOOKUP(A2,Sheet2!A:C,3,FALSE) |
| Modern lookup (newer Excel and Sheets) | =XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C) |
| Remove extra spaces from text | =TRIM(A2) |
| Join text from two cells | =A2&" "&B2 |
| Show today's date | =TODAY() |
| Format a date as text | =TEXT(A2,"dd mmm yyyy") |
Useful features that aren't formulas
- Dropdown lists (Data Validation) keep entries consistent, such as statuses like Paid or Unpaid.
- Conditional formatting highlights late or important rows automatically.
- Freeze panes keeps your header row visible when you scroll.
Tips
- Keep one table per sheet, with one header row and no blank rows.
- Don't type numbers as text, or formulas may ignore them.
- Test a formula on a few rows before copying it down.
To see these ideas in a finished sheet, look at my Excel templates. They use totals, status dropdowns and automatic highlights.
Have a task in mind?
Tell me what you need done and by when. I'll reply with how I can help.