Excel and Google Sheets how-to
Make a running total (cumulative sum)
Monthly sales in one column, the total so far in the next. One formula, filled down.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 1200 | 1200 |
| 3 | Feb | 950 | 2150 |
| 4 | Mar | 1430 | 3580 |
| 5 | Apr | 800 | 4380 |
| 6 | May | 1610 | 5990 |
| 7 | Jun | 1100 | 7090 |
Sample data. Months in A, sales in B, the running total in C.
The formula
Click C2 and type:
=SUM($B$2:B2)Then drag the small square at the bottom right of C2 down to C7. C2 shows 1200, C3 2150 and C7 7090, the total of all six months.
- $B$2 is locked, so every row starts adding at January.
- B2 has no dollar signs, so it moves down a row each time: C3 adds B2:B3, C4 adds B2:B4, and so on.
Why the dollar signs matter
Without them the whole range moves down, so C3 becomes:
=SUM(B3:B3)and shows 950, only February, not the total so far. If your running total just repeats the column next to it, the first cell is missing its $ signs. In Excel for Windows, press F4 right after typing B2 to add them.
The add-the-row-above way
Another common way: C2 is just the first month, and every row below adds its own sales to the total above it. In C2:
=B2and in C3, filled down:
=C2+B3C3 shows 2150 and C7 7090, the same as SUM. The catch: if a sales cell holds text such as n/a, a + gives #VALUE! and every total below it breaks. SUM skips text, so with sales of 100, n/a and 50, =SUM($B$2:B2) still gives 100, 100 and 150.
Keep it blank on empty rows
Filled down past your last month, the total repeats 7090 on every empty row. To leave those rows blank:
=IF(B2="","",SUM($B$2:B2))Rows with sales show the total so far (7090 in June); rows with no sales yet stay empty.
Running percentage of the total
To see how much of the whole period you have reached, divide the total so far by the grand total:
=SUM($B$2:B2)/SUM($B$2:$B$7)Format the column as a percentage. By March it shows 50.5%: half of the six months of sales came in the first three.
A running balance, checkbook style
With deposits in C, payments in D and the opening balance in E1, the balance in E2 is the balance above, plus money in, minus money out:
=E1+C2-D2Fill it down. With an opening balance of 500, a 200 deposit and a 75 payment, E2 shows 625; a 120 payment on the next row brings it to 505, and a 350 deposit after that to 855. Empty cells count as zero, so a row with only a payment works. SUM, IF and the $ signs work the same in every current version of Excel and in Google Sheets.
Templates with the math already done
Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.
- Checkbook Register SpreadsheetA running balance, a cleared balance and a reconcile tab for one bank account.
- Monthly Budget TrackerBudget vs actual, bills and savings.
- Income and Expense TrackerProfit and loss with a sales tax log.
More how-tos
- Find a price by its code with XLOOKUPType a product code, get its price back from your price list. One formula, no helper columns.
- Total each category with SUMIFSAdd up all the Fuel, all the Office, all the Meals: one formula per category, and more rules when you need them.
- Check two conditions at once with IF and ANDFree shipping only when the order is 50 or more and ships in Canada: one IF, one AND, done.
- Add leading zeros to numbersType 00021 and Excel or Google Sheets shows 21, because to them it is just the number 21. Here is how to get the zeros back, and keep them.
- Fix #DIV/0! in an averageAn average of nothing is a division by zero, so AVERAGE shows #DIV/0! until the first number arrives. One IF fixes it.
- Count rows that meet two conditions with COUNTIFSHow many of Ana's deals were 500 or more? COUNTIFS answers it in one cell, and takes as many rules as you need.
- Calculate percentage changeSales went from 400 to 500. That is a 25% increase, and one short formula works it out for every row.
- Calculate years of service from a start dateAna started on 15 March 2019. How many full years has she worked? DATEDIF counts them, and the same formula works out an age from a date of birth.
- Combine first and last name into one cellFirst names in one column, last names in the next, and you need "Ana Lopez" in one cell. The & sign joins them.
- Find duplicates in a list with COUNTIFA mailing list where some people signed up twice. One formula shows how many times each email appears, so the repeats stand out.
- Split a full name into first and last nameYour list has "Ana Lopez" in one cell and you need Ana and Lopez in two. Two short formulas find the space and split there.
- Total amounts by month with SUMIFSA list of dated expenses and you want one total per month. SUMIFS adds every amount between the first and the last day of the month.
- Count working days between two datesAna is off from 1 Oct to 14 Oct. How many working days is that? NETWORKDAYS counts Monday to Friday and leaves out the weekends.
- Hide #N/A in VLOOKUP with IFERRORYour order list has a code that is not in the price list, and VLOOKUP shows #N/A. IFERROR puts your own words there instead.
- Count cells that contain textA stock list where some items have a note and most do not. One COUNTIF with an asterisk counts the notes and skips the empty cells.
- Round to 2 decimal placesA 15% discount turns 12.99 into 11.0415. ROUND makes it 11.04 and keeps your totals right to the cent.
- Free pricing calculatorsPrice a job from your real costs and margin. No sign up.