Free Yearly Expense Tracking Spreadsheet: Setup Guide

How-to basics · Updated November 21, 2026

Yes. You can make a free yearly expense tracking spreadsheet in Excel for the web, or use a free workbook template and adapt it. A useful tracker needs a transaction log, consistent spending categories, and a summary that totals expenses by month and category; the setup below shows how to build those pieces without paid add-ins.

What a yearly expense tracker includes

A yearly tracker records individual expenses throughout the year and turns them into totals you can review. Unlike a simple list of monthly bills, it can show both when money was spent and what it was spent on. That makes it easier to spot patterns, check a category against a planned limit, and compare one month with another.

Keep the original transactions in one worksheet rather than typing monthly totals into separate pages. A practical starting layout is:

  • Date: the date the expense was made or posted, used consistently.
  • Category: a label such as groceries, housing, transport, or utilities.
  • Description: a short explanation, such as weekly shop or electricity bill.
  • Payment method: optional; useful if you reconcile different accounts.
  • Amount: the expense as a positive number, with refunds entered as negative numbers.

A second worksheet can hold the yearly summary, category budgets, and monthly totals. The log is the source data; summary formulas should calculate from it. This separation helps prevent a changed summary figure from accidentally altering the underlying record.

How to get a free spreadsheet

Excel for the web is available at no cost with a Microsoft account, although access, storage, and features can depend on the account and Microsoft’s current terms. Open Excel in a browser, create a blank workbook, and save it to OneDrive. You can also look for a suitable workbook from Excel’s template screen, but available templates can change, and you may need to adjust a monthly or budget template to cover a full year.

For a blank workbook in Excel for Microsoft 365 on Windows, create two sheets and name them Transactions and Summary. On the Transactions sheet, put the five column headings in A1:E1 in the order listed above. On Summary, reserve a cell such as B1 for the year, then create a row for each month and a separate category table. On a Mac, the workbook structure and formulas are the same; some ribbon labels or window layouts can differ slightly. Excel for the web also supports normal worksheet formulas, though its interface is browser-based.

Set up categories and enter transactions

Decide on categories before entering a year of records. Broad labels are easier to maintain than dozens of near-duplicates: for example, choose either “Dining out” or “Restaurants,” not both. You can add more detail later if it helps answer a real question. Categories should describe the expense, not the payment account; a credit-card purchase is still groceries, transport, or another appropriate category.

To reduce spelling differences, keep a category list on the Summary sheet and use a dropdown for the category column. In Excel for Microsoft 365 on Windows, select the entry cells, choose Data > Data Validation, set Allow to List, and select the cells containing your category names as the source. The Data Validation command is also available in Excel for Mac and Excel for the web, though the dialog may look different. If you do not want to set up a dropdown, copy and paste category names consistently.

Enter transactions as they occur or import records from a bank and review each row. A bank export may include transfers, card payments, refunds, or duplicate pending and posted transactions. A transfer between your own accounts is not ordinary spending, and counting both a card purchase and the later card payment would double-count the same expense. Treat refunds consistently: entering a refund as a negative amount reduces net spending in that category.

Calculate monthly and yearly totals

Make each month row on Summary start with the first day of that month as a real Excel date, not text. For example, A4 can contain January 1 of the year in B1; display it as “Jan” if you prefer using a date format. Put the monthly expense total in B4. Assuming transaction dates are in Transactions!A2:A1000, amounts are in Transactions!E2:E1000, and A4 contains the first day of the month, enter:

=SUMIFS(Transactions!$E$2:$E$1000,Transactions!$A$2:$A$1000,">="&A4,Transactions!$A$2:$A$1000,"<"&EDATE(A4,1))

In the displayed formula, the criteria operators must be ordinary formula text: type =SUMIFS(Transactions!$E$2:$E$1000,Transactions!$A$2:$A$1000,">="&A4,Transactions!$A$2:$A$1000,"<"&EDATE(A4,1)) only if your HTML editor preserves the characters. The intended Excel formula is =SUMIFS(Transactions!$E$2:$E$1000,Transactions!$A$2:$A$1000,">="&A4,Transactions!$A$2:$A$1000,"<"&EDATE(A4,1)).

For clarity, Excel syntax uses the less-than and greater-than characters directly, not HTML entities. The actual formula to enter is =SUMIFS(Transactions!$E$2:$E$1000,Transactions!$A$2:$A$1000,">="&A4,Transactions!$A$2:$A$1000,"<"&EDATE(A4,1)). It includes dates from the first day of the month up to, but not including, the first day of the next month, so it also works for months of different lengths. For illustration, if January has a $120 grocery entry and a $45 utility entry, the result is $165. Copy the formula down beside the other months, changing the month-start date in column A for each row.

For a category total covering the year, assume the year is in Summary!B1, the category name is in Summary!D4, transaction categories are in Transactions!B2:B1000, dates are in Transactions!A2:A1000, and amounts are in Transactions!E2:E1000. Use:

=SUMIFS(Transactions!$E$2:$E$1000,Transactions!$B$2:$B$1000,D4,Transactions!$A$2:$A$1000,">="&DATE($B$1,1,1),Transactions!$A$2:$A$1000,"<"&DATE($B$1+1,1,1))

That formula adds only entries matching the category and falling within the selected calendar year. If the category is Groceries and its entries total $480, the result is $480. Copy it down next to each category. The formula ranges stop at row 1000, so extend them if the log grows beyond that row, or use an Excel Table so new records are easier to include in calculations.

Compare spending with a budget

A budget is optional; the spreadsheet is still useful for recording actual spending alone. If you do set category limits, place the planned amount and actual amount in adjacent columns. Assuming the budget is in B4 and the annual actual total in C4, enter this in D4 to show the amount remaining:

=B4-C4

For example, a $600 grocery limit and $480 of recorded groceries leave $120. A negative result means recorded spending exceeded the amount entered as the budget; it does not explain why or determine what a future month will cost. Check that budget and actual figures cover the same period before comparing them.

You can also calculate an average monthly total. Assuming January through December totals are in Summary!B4:B15, enter =AVERAGE(B4:B15) in another summary cell. If the twelve monthly totals add to $6,000, their average is $500 per month. Because this formula includes months with zero spending, it describes the average across all twelve months. That can be more useful for a yearly overview than averaging only months with transactions.

Make the workbook easier to use

Format the transaction range as an Excel Table by selecting it and choosing Insert > Table, or use Ctrl+T on Windows. On a Mac, Command+T is commonly used to create a table. Confirm that the table has headers. Tables add filters and make it easier to sort transactions by date or category; they also expand when new rows are added. Keep dates as dates and amounts as numbers, then apply a date or currency number format rather than typing currency symbols into the values.

For a clearer review, freeze the header row, keep the category list in one place, and use filters to inspect a particular month. Conditional formatting can flag values, but it is not required for correct totals. Avoid merging cells in the transaction log, since merged cells make sorting and filtering harder. Save a backup copy periodically, especially before reorganizing columns or importing a large bank file.

Common problems and checks

A total that looks too high or low usually points to the source entries, not complicated arithmetic. Check for duplicate bank rows, card payments counted as purchases, transactions assigned to inconsistent categories, or dates stored as text. A date that looks like a date may still be text after a poorly formatted import, in which case date-based formulas may omit it. Confirm that the cells contain usable dates and that the year in the summary matches the dates you intend to count.

Check the amount convention as well. If expenses are positive and refunds negative, category totals represent net spending. If you instead mix positive purchases with negative expenses, the total will not mean what you expect. A quick reconciliation can help: select one month, compare a few spreadsheet rows with the source statement, and verify that the monthly total reflects the same transactions. The workbook is only as complete as the records entered; it cannot know about cash purchases or accounts you have not included.

Costs, privacy, and alternatives

A basic tracker does not require a paid budgeting add-in. Excel for the web can be a no-cost option for creating and editing a workbook, while the desktop Microsoft 365 application requires an applicable license. Google Sheets is another free spreadsheet option for many personal accounts, but its menus and some formula details differ from Excel. A paper ledger or a notes app can work for a small number of entries, although totals and year-wide comparisons usually require more manual work.

Consider privacy before storing financial records online or on a shared computer. A spreadsheet does not automatically connect securely to your bank or verify that imported records are genuine. Do not put account passwords, full payment-card numbers, or security codes in the workbook. Use a device and cloud account you control, review sharing permissions, and remove personal details from exports you do not need. For household sharing, decide who can edit the file and keep a backup outside the shared working copy.

Frequently Asked Questions

Is there a free yearly expense tracking spreadsheet?
Yes. You can create one in Excel for the web or adapt a suitable free workbook template. A transaction log and a summary sheet are enough to track monthly and category totals; a paid app is not necessary for this basic setup.

Can I use Excel for free to track expenses?
Excel for the web is available without charge with a Microsoft account, subject to Microsoft’s current account and service terms. The desktop Excel app may require a paid Microsoft 365 subscription or another license.

How do I track expenses for a whole year in Excel?
Record each transaction on one sheet with a date, category, and amount. On a second sheet, use date-based SUMIFS formulas to total each month and category, and make sure the summary year and transaction dates agree.

Should I enter expenses as positive or negative numbers?
For the layout described here, enter purchases as positive amounts and refunds as negative amounts. That convention makes a SUMIFS total show net spending. Use one convention consistently and label it clearly.

Can I use the same spreadsheet next year?
Yes. Save a new copy, update the summary year and month-start dates, and retain the transaction history in the original file if you want an archive. Check that formula ranges include the new rows and that the summary is not accidentally mixing years.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.