Excel for Bookkeepers.

Bookkeeping runs on two tools, and they're not rivals — they're a pair. Accounting software (QuickBooks®) is the system of record: it enforces double-entry, tracks customers and vendors, and never lets debits drift from credits. The spreadsheet is the workbench: it's where you analyze, clean, prototype, and answer the one-off questions software menus weren't built for. Every working bookkeeper lives in both.

A useful rule of thumb: if the data needs to be permanent and trusted, it belongs in the software. If it needs to be examined, reshaped, or explained, pull it into a spreadsheet. Budgets, cleanup projects, client questions like "what did we spend on fuel by month?", and pre-software businesses all live comfortably in Excel.

Try it: Open the Companion Workbook and click through its tabs without touching anything. Notice the shape: raw entries live on Income and Expenses, categories live on Chart of Accounts, and the Dashboard contains no typed numbers at all — only formulas reading the other tabs. That separation (data in one place, analysis in another) is the single most important habit in this whole path.

Everything in a spreadsheet has an address. Columns are letters, rows are numbers, so the cell where column E meets row 5 is E5. A rectangle of cells is a range, written with a colon: E5:E304 means "E5 down through E304." Formulas always start with = — type =E5+E6 into any empty cell and you've written your first one.

Now the concept that unlocks everything else: references shift when you copy. Copy =E5+E6 down one row and it becomes =E6+E7 — Excel assumes you want the same pattern, not the same cells. That's called a relative reference, and it's why one formula can total a thousand rows. When you don't want shifting — say a tax rate sitting in one cell — you lock it with dollar signs: $B$1 stays $B$1 no matter where you copy it. While editing a formula, tapping F4 cycles the locks on and off.

Try it: On the Income tab, click any Amount cell and look at the formula bar. Then click the Total income cell — see how it reads a whole range. Type =E5*2 in a spare cell, copy it down three rows, and watch the reference walk. Then change it to =$E$5*2, copy again, and watch it hold still. That's the whole lesson in your fingers.

Here's a thing that confuses every beginner: formatting changes what a value looks like, never what it is. The cell holds 1240; the format decides whether you see 1240, $1,240.00, or Jan 24. Dates especially — under the hood a date is just a number (more on that in Lesson 9), dressed up by its format. So when a column "looks wrong," check the format before assuming the data is broken.

For bookkeeping, three formats do most of the work: currency with two decimals for money, date (pick one style and stick to it), and an accounting-style format that shows negatives in parentheses — (1,240.00) — the way financial statements do. Then there's conditional formatting, which changes a cell's appearance based on its value: make negative numbers red, or highlight any expense over $500. It turns a wall of numbers into a page where problems glow.

Try it: On the Dashboard, notice the money cells show a dash when they're zero — that's a number format doing quiet design work. Try it: select a range, open the format options, and apply currency. Then add a conditional rule that highlights values over 1,000 and watch your biggest months light up.

Four functions handle most everyday math: SUM adds a range, AVERAGE finds the mean, COUNT counts how many cells contain numbers (its cousin COUNTA counts anything non-empty), and MAX/MIN find the extremes. Syntax is identical for all of them: =SUM(E5:E304). Learn one, you've learned them all.

Two working habits. First, AutoSum (the Σ button, or Alt+= on Windows) writes the SUM for you and usually guesses the right range — verify the guess before accepting it. Second, always total a bigger range than you currently need — =SUM(E5:E304) with only 40 rows filled costs nothing today and keeps working as the data grows. Formulas that must be rewritten every time data grows are formulas that eventually get forgotten.

Try it: The Income tab's Total income cell and the Expenses tab's total both use the oversized-range trick — click them and check the ranges against the filled rows. Then, in a spare cell, find your largest single expense with =MAX and count your income entries with =COUNT. Two formulas, two real answers about the business.

Raw entries arrive in the order life happened, but questions come by vendor, by size, by date. Sorting reorders rows; filtering hides the rows you don't care about right now. The one rule that prevents disasters: always sort whole rows together. Select a single column and sort it alone, and every amount detaches from its vendor — quietly, permanently. Let Excel expand the selection when it offers.

The professional upgrade is converting a range into an Excel Table (Ctrl+T). Tables give you filter buttons on every header, formatting that maintains itself, and — the real prize — formulas and ranges that grow automatically when you add rows. A table also lets you write formulas with column names instead of letter-number ranges, which reads almost like English.

Try it: On the Expenses tab, filter to a single category and watch the noise vanish; clear the filter and sort by amount, largest first — your top-five costs in ten seconds. That question ("where does the money actually go?") is the one every business owner asks, and you just answered it without a single formula.

IF is the spreadsheet learning to make decisions. The shape: =IF(test, value_if_true, value_if_false). So =IF(F5>500,"Review","OK") reads a cell and renders a verdict. This is how you build review columns — a bookkeeping habit worth gold, because the spreadsheet flags the exceptions and you only inspect what's flagged.

Tests can be compound: AND requires every condition to be true, OR requires any one of them. =IF(AND(D5="Meals (business)",F5>100),"Check receipt","") flags only large meal expenses — precise flags beat noisy ones. And IFERROR is the graceful safety net: =IFERROR(B7/B8,0) shows 0 instead of the ugly #DIV/0! when a month has no data yet. Wrap it around anything that might divide by nothing or look up something missing.

Try it: Add a Review column to the Expenses tab: in the first empty column, write an IF that flags any amount over an amount you choose, and copy it down. You've just built your first automated review — the seed of every audit checklist you'll ever run.

This is the bookkeeper's power lesson. SUMIF adds only the cells that meet a condition: =SUMIF(D5:D404,"Rent",F5:F404) reads "look through the categories, and wherever it says Rent, add the matching amount." One formula, one category total. SUMIFS (plural) stacks multiple conditions — category AND date range AND anything else — with the sum range moving to the front: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2…).

Dates are where SUMIFS earns its keep. "January rent" means two date conditions: on-or-after Jan 1, and before Feb 1 — written as ">="&DATE(2026,1,1) and "<"&DATE(2026,2,1). That before-the-next-month trick cleanly captures every day of January, including the 31st, without edge cases. Master this one pattern and you can answer any by-category-by-month question a client will ever ask.

Try it: Click any month cell on the Dashboard and read its SUMIFS in the formula bar — it's exactly the pattern above. Now rebuild one from scratch in a spare cell: total one category for one month, and check your answer against the Dashboard. When they match, this lesson is yours.

A lookup fetches matching data from somewhere else: give it a value, tell it where to search, and it returns the neighbor. The modern tool is XLOOKUP: =XLOOKUP(B2, Vendors!A:A, Vendors!C:C, "Not found") — find B2 in the vendor list, return that vendor's category, and say "Not found" instead of erroring when it's missing. This is how vendor lists, rate tables, and account mappings talk to your transaction data.

You'll also inherit spreadsheets built on VLOOKUP, XLOOKUP's ancestor: =VLOOKUP(B2, Vendors!A:C, 3, FALSE). It searches the first column of a block and returns from the column number you specify — and that trailing FALSE means exact match, which is almost always what bookkeeping wants. Leave it off and VLOOKUP happily returns near-matches, which in financial data means silently wrong answers. Know VLOOKUP well enough to read it; write XLOOKUP when it's your choice.

Try it: Build a three-row vendor list on a spare sheet — vendor name, default category — then write an XLOOKUP that fetches the category when you type a vendor name. Congratulations: you've just prototyped the logic behind QuickBooks bank rules.

Here's the secret that makes date math click: to Excel, a date is just a number — the count of days since the beginning of 1900. January 15, 2026 is really 46037 wearing a costume. Which means dates subtract like numbers: =TODAY()-C5 gives exactly how many days old the invoice in C5 is. That one subtraction is the beating heart of every A/R aging report ever built.

Three functions cover the bookkeeping calendar. TODAY() is always the current date — it updates itself, so anything built on it stays current. EOMONTH(A5,0) returns the last day of A5's month (change the 0 to 1 for next month's end — perfect for due dates). And TEXT(A5,"mmm yyyy") converts a date into a clean label like "Jan 2026" for report headers. Together: age anything, bucket anything by month, label everything readably.

Try it: In a spare cell, type =TODAY()-A5 next to any dated row and format the result as a plain number — days elapsed, live and self-updating. Then try =TEXT(A5,"mmm yyyy") and watch a raw date become a report label.

Exported data arrives dirty. Bank feeds SHOUT IN CAPS, systems pad names with invisible spaces, and one column holds what should be three. The cleanup toolkit: TRIM strips stray spaces (=TRIM(B5) fixes " Acme Landscaping " — and those invisible spaces silently break lookups, so TRIM first, always), PROPER fixes capitalization (=PROPER(B5) turns ACME LANDSCAPING into Acme Landscaping), and the & operator glues text together: =B5&" — "&C5 combines vendor and description into one label.

For splitting, Text to Columns (on the Data tab) breaks one column into several at a delimiter — a comma, a dash, a space. The professional workflow for any messy export: paste the raw data on its own sheet, build clean columns beside it with formulas, then copy the clean version and Paste Special → Values so the results become plain data. Never clean in place; you can't un-break an original you overwrote.

Try it: Type " acme LANDSCAPING " (with the spaces) in a spare cell, then build =PROPER(TRIM(...)) next to it and watch two functions nest into one repair. Nesting — feeding one function's output into another — is how small tools combine into real power.

Consistency is a bookkeeping superpower, and data validation is how a spreadsheet enforces it. A validation rule restricts what a cell accepts; the list type turns the cell into a dropdown. Why it matters: to SUMIF, "Rent", "rent ", and "Rnt" are three unrelated categories — your Dashboard splits one expense across three lines and every total lies a little. Dropdowns make the wrong spelling impossible instead of merely discouraged.

Setup: select the cells, open Data → Data Validation, choose List, and point the source at a range — like the category column on a Chart of Accounts sheet. The elegant part: because the dropdown reads that range, editing the category list updates every dropdown instantly. One source of truth, referenced everywhere. That pattern — maintain a list once, consume it everywhere — is the same architecture QuickBooks uses for its own lists.

Try it: This is exactly how the Companion Workbook is wired: the Category dropdowns on Income and Expenses read the Chart of Accounts tab. Prove it — add a category of your own on Chart of Accounts, then open any Category dropdown and find it waiting.

A pivot table answers "how much, by what, over when?" without a single formula. Select your data, Insert → PivotTable, and you get a drag-and-drop report builder: drag Category into Rows, Amount into Values, and Date into Columns — and a category-by-month expense report assembles itself in seconds. The same report as your Dashboard's SUMIFS grid, built by dragging instead of typing.

So when formulas, when pivots? Formulas for reports with a fixed, designed layout that updates live as data changes — like a dashboard. Pivots for exploration — when the question keeps changing and you want to slice five different ways in five minutes. One honest catch: pivots don't update themselves. After adding data, right-click → Refresh, or last week's entries silently won't be in the numbers. It's the #1 pivot mistake in the wild.

Try it: Select the Expenses data (headers included), insert a pivot on a new sheet, and build category rows with amount values. Then drag Category out and drop in Paid With instead — the entire report rebuilds around the new question. That instant pivot is where the name comes from.

Owners don't read tables; they read pictures. Chart choice is about matching shape to question: column charts compare amounts side by side (expenses by category), line charts show movement through time (monthly revenue trend — time always runs along the bottom), and pie charts show shares of one whole — legitimate only with a handful of slices; at ten slices it's confetti, use columns instead.

Creating one is the easy part: select the data, Insert, pick the type. The craft is in the trimming — delete the gridlines, drop the legend when there's one series, title the chart with the takeaway ("Software costs doubled since March") rather than the description ("Expenses"). A chart earns attention by saying one thing clearly; every decoration you remove makes the data louder.

Try it: Select the Dashboard's month names and net profit row and insert a line chart — the year's story in one shape. Then chart the category totals as columns. Two charts on one sheet and you've built a mini financial dashboard an owner would actually read.

The final skill closes the loop: moving data between QuickBooks and Excel. Outbound is built in — nearly every QBO report has an Export to Excel button. Exports arrive with decorative headers, merged title cells, and subtotal rows baked in, so the first move is always the same: copy the real data to a fresh sheet and strip the decoration. Now every skill in this path applies — pivot it, chart it, SUMIFS it, answer the question the canned report couldn't.

Inbound matters most for bank transactions (CSV imports typically want simple Date / Description / Amount columns — your Lesson 10 cleanup skills are exactly what preps a raw file). Two cautions: match QuickBooks' expected format precisely, dates especially, and treat any mass import or bulk change with respect — in software, "undo" gets complicated. When a big import feels risky, that instinct is correct; verify the format on a few rows first.

Try it: Export any QBO report to Excel (or use the Companion Workbook's data as a stand-in), copy it to a clean sheet, strip the decoration, and pivot it. Raw export to real insight in under five minutes — that round trip is the whole path, working together. And notice what you can suddenly see: the software's records, examined on the workbench. Both tools, one bookkeeper. That's the job.