Monthly Budget Template in Numbers: Build and Fill It

Quick Answer: Start a monthly budget in Apple Numbers from a budget-style template in the template chooser, or from a blank sheet with two tables: a Budget table listing categories and planned amounts, and a Transactions table holding your bank's CSV. One SUMIFS formula per category pulls actual spending from the transactions, and Numbers' categories feature can subtotal the same data without any formulas.

A monthly budget in Numbers needs two things: a list of categories with planned amounts, and your real transactions for the month. Pick a budget-style template or start blank, import your bank's CSV export into a second table, then add one SUMIFS formula per category to calculate actual spending. The same steps work on a Mac, iPhone and iPad.

Numbers is free on Apple devices and handles a personal budget comfortably. What trips people up is not the arithmetic but the plumbing: getting a bank file into the right table, and keeping the planned figures separate from the real ones. This walkthrough handles both, with every menu name taken from Apple's Numbers User Guide.

Template or blank sheet?

When you create a spreadsheet, Numbers opens its template chooser. Apple's guide describes templates as starting points with coordinated fonts and colors and placeholder content you replace, and you can browse them by category in the sidebar of the chooser. Apple's template set changes from version to version, so rather than describing one template cell by cell, here is how to judge whichever budget template you see:

If a template fails two of these, a blank spreadsheet with the two tables below is quicker than bending the template to fit. Apple's article on personalizing templates covers replacing placeholder text and images if you keep one.

Build the two tables

Whatever you started from, end up with this structure on one sheet.

Budget table (call it Budget):

Transactions table (call it Transactions): the columns your bank's export provides, usually Date, Description and Amount, plus one column you add yourself called Category.

Keep category names identical in both tables. "Dining out" and "Dining Out" are different values to a formula.

How do I import my bank CSV into Numbers?

Download the month's transactions from your bank as CSV. Then:

On a Mac. Apple's import guide for Mac says to drag the file onto the Numbers icon in the Dock or Applications folder, or use File > Open. If the columns land wrong (everything in one column, or amounts split oddly), select the table and go to the Format sidebar, Table tab, Adjust Import Settings, Delimited tab. There you can choose the delimiter, set a text qualifier, pick the text encoding and tick Treat consecutive delimiters as one, then click Update Table.

On an iPhone or iPad. In the document manager, tap Browse, find the file, and tap its thumbnail. Apple's iPhone import guide lists the same delimiter, encoding and text qualifier options if the table needs fixing.

One warning from Apple applies on every device: you cannot adjust import settings after you edit the imported table. Fix the columns first, then start typing categories.

Once the import looks right, copy its rows into your Transactions table (or rename the imported table to Transactions and add the Category column).

When your bank only gives you a PDF

Some banks and card issuers offer statements only as PDFs, and some CSV exports cover only recent months. Numbers cannot import a PDF statement as a table. Options:

• Copy and paste from the PDF in Preview, then clean up the columns. Workable for a short statement.
• Use a converter. QuickBankConvert turns an uploaded statement PDF into a CSV with date, description, a signed amount (spending negative) and balance, which Numbers imports with the steps above. Our roundup of bank statement converters for Mac compares it with other Mac-friendly tools.

The formula that fills in "Actual"

Apple's guide explains that you reference another table with a double colon (for example Table 2::B2) and can refer to a whole column by its header name. Its SUMIFS reference gives the syntax as SUMIFS(sum-values, test-values, condition, …).

Click the first Actual cell in the Budget table, type = to open the formula editor, and enter:

=-SUMIFS(Transactions::Amount, Transactions::Category, "="&A2)

Then fill it down. The minus sign is there because most bank exports show spending as negative numbers; flipping the sign makes Actual read as a positive amount spent. If your bank shows spending as positive, drop the minus. For Left, use =B2-C2.

A worked month

Suppose October's transactions, after you tag them, contain these debits:

• Groceries: 84.12, 126.40, 61.35 and 97.80
• Dining out: 42.50, 18.75 and 36.20
• Utilities: 148.33
• Transport: 65.00

The Budget table then calculates:

Add the total row with SUM(Planned), SUM(Actual) and SUM(Left), which use the header-name references Apple describes. If Actual for a category stays at zero, check for a spelling difference in the Category column before suspecting the formula.

Skip formulas: let categories do the subtotals

Numbers has a feature that does much of this without SUMIFS. Apple's intro to categories explains that you can organize a table by the values in a source column; every row sharing a value becomes a group, with a summary row above it.

Apply it to the Transactions table using Category as the source column. Then, per Apple's article on summarizing group data, click an empty cell in a summary row under the Amount column, click the Summary Action menu button and choose a function such as subtotal. Numbers adds the same calculation to every group at that level. You can also select that column of summary results and click Chart in the toolbar for a quick spending chart.

Categories are excellent for reviewing a month. The SUMIFS table is better for comparing against a plan, because the planned amounts live beside the actuals. Many people keep both.

Running it every month

1. Duplicate last month's sheet and rename it.
2. Clear the Transactions table, keeping the headers.
3. Import the new CSV and tag each row's Category. Making the Category column a pop-up menu keeps spellings consistent: select the cells, then in the Format sidebar's Cell tab choose Pop-Up Menu from the Data Format menu and type your category names, as Apple's controls in cells article describes.
4. Check that Actual totals look sensible, then adjust next month's Planned column.

For an Excel or Google Sheets version of the same idea, see building a budget spreadsheet from bank statements, and for Mac-specific PDF handling, converting a PDF to Excel on a Mac.

Related guides

• Create a budget spreadsheet from bank statements
• Best bank statement converters for Mac
• Bank statement PDF to CSV