Setting up a car budget spreadsheet

The plan is deliberately plain. One tab where every car expense is written down the day it happens. One tab for fuel, because fuel is the only cost frequent enough to deserve its own. One tab that summarizes by month without you touching it. And one tab that turns the once-a-year bills into a monthly number you can set aside. The formulas below work in both Excel and Google Sheets; where the two differ, we say so.

Name the first tab Log. This is the only tab you type into regularly, so keep it narrow. Row 1 holds headers; data starts in row 2.

Log

Column F turns each date into a label like 2026-01, which is what the summary tab groups on. Type the formula once in F2 and fill it down as far as you like. The amounts above are placeholders to show the shape of the data, not typical costs.

Two habits make this tab work. Use a short, fixed list of categories — Loan, Insurance, Fuel, Maintenance, Repair, Tires, Parking, Tolls, Registration, Wash, Other — and enforce it with a dropdown (Data → Data validation in either program) so "Gas" and "fuel" never split one category in two. And write the odometer every time, even for a parking receipt. It costs a glance at the dashboard and it is the only way to get cost per mile later.

Fuel entries also go in the Log, so the money is counted once. This second tab exists to track efficiency. Log every fill-up, and fill the tank all the way each time, or the math drifts.

Fuel

Row 3 reads: 268 miles on 10.1 gallons, so =ROUND(D3/C3,1) returns 26.5. The fuel-per-mile cell divides what you paid for the previous tank (the gas you just burned) by the miles it carried you: $38.90 over 268 miles is about $0.145. In practice, add a column for price paid and point the formula at the row above instead of typing a number.

Row 2 has no "since last" because there is no last. That is normal; start computing from row 3.

This tab is all formulas. Categories run down column A, months run across row 1, and one formula fills the grid.

Summary

The apostrophe in '2026-01 forces the header to stay text. Without it, both programs helpfully turn it into a date, and the SUMIFS — which is matching the text labels from Log column F — quietly returns zero. The dollar signs are what let one formula be filled across twelve months and down eleven categories without breaking: $A2 locks the column, B$1 locks the row.

Mid-year, "per month" in column O understates things, because the empty months count as zero. Swap it for =N2/COUNTIF(B$13:M$13,">0") to divide only by months that have any spending in them.

Underneath the grid, add the number that matters most:

Summary

That second line is the spreadsheet's version of your real cost per mile. Treat the first few months with suspicion: until you have logged a full year, insurance paid annually or a big repair can make it swing hard.

Registration, an insurance premium paid in full, an inspection, a set of tires you can see coming. None of them show up monthly, so they show up as emergencies. This tab turns each one into a monthly set-aside.

Fund

DATEDIF counts whole months between today and the due date. The MAX(1, …) wrapper stops a division by zero in the month a bill comes due. Because TODAY() moves, column E recalculates itself: a bill you started late demands more each month, which is the honest answer. Fill column B from real paperwork — last year's notice, an actual quote — and not from a guess; fees and premiums vary too much from place to place for anyone else's number to be useful.

Once you pay a bill, roll its due date forward a year (or to whenever it recurs) and the set-aside resets on its own. If you want to see how those bills tend to cluster, the car bills that arrive once a year lays them out by month.

Keeping it alive

A spreadsheet like this dies from friction, not from bad formulas. Keep receipts in one place until the weekend and enter them in one sitting; ten minutes a week is plenty. Freeze row 1 on the Log tab so headers stay visible. And resist adding columns. Every extra field is one more thing to skip on a busy day, and a log with gaps is worse than a short log that is complete.

Look at the Summary tab once a month, the Fund tab whenever you get paid, and the cost-per-mile cell once a year — ideally before you start shopping for the next car.