Cellwise

Every page (30)

1. Add VAT to a price: net, VAT and total formulas

   Two cells that turn a net price and a rate into VAT and a gross total.

   invoice vat arithmetic

2. Age or years between two dates (DATEDIF)

   Whole years and leftover months between two dates with DATEDIF.

   dates datedif

3. Auto-number invoices (INV-0001, INV-0002) with a formula

   Generate invoice numbers that count up as you fill in rows, with leading zeros.

   invoice text freelancer

4. Average that ignores zeros and blanks

   AVERAGEIF with not-equal-to-zero skips zeros so they do not drag the average down.

   average averageif

5. Budget vs actual: difference, over/under flag and no divide-by-zero

   Three formulas to see whether you are over budget, with a safe percent that handles a zero budget.

   budget finance

6. Budget vs actual: variance, percent and an Over/OK flag

   Variance in money and percent, an Over/OK flag, and a total, with no divide-by-zero error.

   budget finance conditional-formatting

7. Calculate days overdue for an invoice in Excel and Google Sheets

   One formula that returns how many days an unpaid invoice is past its due date, and zero if paid.

   invoice dates freelancer

8. Count unique values in Excel and Google Sheets

   Count distinct values with UNIQUE, or with a helper column in older Excel.

   unique count cleanup

9. Count with several conditions, including blanks

   COUNTIFS with text, blank and number conditions together.

   countifs cleanup

10. Days left, working days left and the pace you need

    Three formulas for a project deadline: days left, working days left and tasks per day needed.

    project tracker dates

11. Days overdue and a simple late fee for unpaid invoices

    Count days past due (zero if paid or not yet due) and work out a simple monthly late fee.

    invoice dates freelancer

12. Extract the domain from an email and a trailing number from text

    Two text formulas: the domain after the @, and a number at the end of a cell.

    text cleanup email

13. Find duplicates on two columns and across sheets

    Flag rows that repeat on two columns, or that already appear on another sheet or list.

    duplicates countifs cleanup

14. Fix #N/A in VLOOKUP

    Show Not found instead of #N/A, and clean stray spaces with TRIM.

    vlookup errors iferror

15. Flag overdue invoices and colour the row red automatically

    A TRUE/FALSE overdue test and how to use it as a conditional formatting rule in Excel and Google Sheets.

    invoice conditional-formatting freelancer

16. Get the latest value for a name or ID

    Return the last matching row's value with XLOOKUP, INDEX/FILTER, or an older-Excel formula.

    lookup xlookup filter

17. Highlight overdue invoices automatically in Excel and Google Sheets

    One conditional-formatting formula that colours every unpaid invoice past its due date.

    invoice conditional-formatting freelancer

18. How to calculate a monthly loan payment in Excel and Google Sheets (PMT)

    Work out a monthly loan payment and the total interest with PMT. Tested on sample data.

    loan PMT finance budget

19. Join first, middle and last name without extra spaces

    TEXTJOIN skips blanks so names join cleanly; TRIM option for older Excel.

    text textjoin names

20. Low-stock alert and reorder quantity in Excel and Google Sheets

    Flag items at or below their reorder level, work out how many to order, and count the items to reorder.

    inventory small-business

21. Monthly totals by category with SUMIFS and EDATE

    Build a category-by-month grid from a list of transactions with one SUMIFS formula you can fill across and down.

    sumifs dashboard dates

22. Percent complete from a status column

    Count Done tasks and divide by all tasks, then label the project Done, In progress or Not started.

    project tracker countif

23. Rank values with COUNTIF, plus ties, 2nd largest and top name

    Rank a list with COUNTIF and handle ties, then pull the Nth largest value and the top name.

    rank countif large

24. Remove duplicates but keep the latest row

    Flag the newest row per name as keep and the older ones as dup, then filter.

    duplicates cleanup maxifs

25. Running balance formula for income and expenses

    A running total down a column: income so far minus spending so far.

    budget dashboard balance

26. Share of total and percent change formulas

    What share of the total a row is, and how much it changed from the previous row.

    percent dashboard

27. Split first and last name in one cell

    Formulas that take the first and last word from a full name, including a middle name.

    text cleanup names

28. Sum if a cell contains text (wildcards)

    SUMIFS with asterisks to total rows whose text contains a word.

    sumifs wildcards text

29. Sum or count between two dates in Excel and Google Sheets

    Use SUMIFS and COUNTIFS with two date conditions to total or count a date range.

    dates sumifs countifs

30. XLOOKUP vs VLOOKUP vs INDEX/MATCH: which to use, and what to do when nothing is found

    Three ways to look up a value in Excel and Google Sheets, tested on sample data: which one to pick, how to look left, and how to show 'Not found' instead of #N/A.

    lookup xlookup vlookup index-match excel-vs-sheets