Cellwise Try it free

Guided project · 5 steps Free

Monthly budget in Excel

Turn raw spending into a budget that flags problems on its own.

You will use: SUM, COUNTIF, MIN, MATCH, INDEX

Starting data

ABCDEFG
1CategoryBudgetActualLeft% usedOver budget
2Groceries600684.35Biggest overspend
3Rent15001500
4Transport250198.4
5Dining200262.9
6Utilities180171.25
7Fun15095
8Total

Build it step by step

  1. Step 1

    How much is left in each category? Write it in D2 so it fills to D7.

    D2=B2-C2

    Then fill down to D7.

  2. Step 2

    What share of each budget is used? Write it in E2, fill to E7.

    E2=C2/B2

    Then fill down to E7.

  3. Step 3

    Total the budget column in B8.

    B8=SUM(B2:B7)

  4. Step 4

    How many categories went over? Count the negative amounts left, in H1.

    H1=COUNTIF(D2:D7,"<0")

  5. Step 5

    Name the category with the biggest overspend in H2.

    H2=INDEX(A2:A7,MATCH(MIN(D2:D7),D2:D7,0))
    ABCDEFGH
    1CategoryBudgetActualLeft% usedOver budget2
    2Groceries600684.35-84.351.1405833333Biggest overspendGroceries
    3Rent1500150001
    4Transport250198.451.60.7936
    5Dining200262.9-62.91.3145
    6Utilities180171.258.750.95138888889
    7Fun15095550.63333333333
    8Total28802911.9-31.9

Guides for the formulas in this project