Guided project

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

Build it step by step

1. Step 1

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

   D2fx=B2-C2

   Then fill down to D7.

2. Step 2

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

   E2fx=C2/B2

   Then fill down to E7.

3. Step 3

   Total the budget column in B8.

   B8fx=SUM(B2:B7)

4. Step 4

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

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

5. Step 5

   Name the category with the biggest overspend in H2.

   H2fx=INDEX(A2:A7,MATCH(MIN(D2:D7),D2:D7,0))

   	A	B	C	D	E	F	G	H
   1	Category	Budget	Actual	Left	% used		Over budget	2
   2	Groceries	600	684.35	-84.35	1.1405833333		Biggest overspend	Groceries
   3	Rent	1500	1500	0	1
   4	Transport	250	198.4	51.6	0.7936
   5	Dining	200	262.9	-62.9	1.3145
   6	Utilities	180	171.25	8.75	0.95138888889
   7	Fun	150	95	55	0.63333333333
   8	Total	2880	2911.9	-31.9