To get a balance sheet to Excel with its structure intact, ask for a software export first, then a text PDF, and treat a scan as a last resort. In Excel, give every row a level, a line type (item, subtotal or total) and a period column per year, then recalculate each subtotal and compare it with the printed figure.
The example throughout is a fictional client, Harbor and Pine Bakery LLC, which sent a two-year balance sheet as a PDF for December 31, 2026 and 2025. All names and amounts are examples. Both years balance, and the checks at the end prove it.
Where balance sheets come from: software reports, PDFs and scans
Balance sheets reach a firm in three forms: a software export, a PDF the software generated, or a scan or photo of a printed page. The export is cleanest, because each amount is already a number in its own cell. A generated PDF is next. A scan is hardest, because every figure must be read from an image.
An export from the books usually arrives as a spreadsheet or CSV file. Its weak point is formatting rather than reading: indent levels may be stored as leading spaces, subtotals may be typed values instead of formulas, and negative amounts may carry parentheses. The pitfalls section below deals with each of these.
A PDF the software generated contains real text. Excel can read the tables inside it directly. Microsoft's page on importing data from data sources with Power Query gives the path as Data, Get Data, From File, From PDF; a Navigator window then lists the tables it found, and you either load one or open it in Power Query to clean it first. The same page notes the PDF connector needs .NET Framework 4.5 or later on the computer.
A scan has no text layer, so the PDF connector usually finds no tables. Excel's Data from Picture feature reads a table from an image file or a screenshot, and asks you to review and correct the results before you click Insert Data. Microsoft advises an image that shows only the table and a photo taken straight on, not at an angle. What goes wrong when software reads a scanned statement, and how to catch it, is covered in where financial statement OCR misreads figures.
The same order of preference applies to bank statements, which have their own layout problems; the guide on how to convert a bank statement PDF to Excel covers them.
Keeping balance sheet line hierarchy and subtotals in Excel
Balance sheet line hierarchy is the nesting of lines under headings: cash under current assets, current assets under total assets. Indentation shows it on the page, but indentation does not survive conversion reliably. Keep it in Excel by adding a level column, a line-type column and a section column, so the structure lives in data rather than in spacing.
Here is the Harbor and Pine Bakery page as it should look in Excel. Column A is the level (1 for section totals, 2 for subtotals, 3 for detail lines), column B is the line type, and column C is the section the line belongs to.
| A: Level | B: Type | C: Section | D: Line | E: 2026 | F: 2025 |
|---|---|---|---|---|---|
| 3 | item | Assets: current | Cash | 14,250 | 11,900 |
| 3 | item | Assets: current | Accounts receivable | 6,180 | 5,420 |
| 3 | item | Assets: current | Inventory | 9,570 | 8,680 |
| 2 | subtotal | Assets: current | Total current assets | 30,000 | 26,000 |
| 3 | item | Assets: noncurrent | Equipment, net | 42,000 | 47,000 |
| 1 | total | Assets | Total assets | 72,000 | 73,000 |
| 3 | item | Liabilities: current | Accounts payable | 8,400 | 7,950 |
| 3 | item | Liabilities: current | Accrued wages | 3,100 | 2,550 |
| 3 | item | Liabilities: current | Current portion of loan | 6,000 | 6,000 |
| 2 | subtotal | Liabilities: current | Total current liabilities | 17,500 | 16,500 |
| 3 | item | Liabilities: long-term | Loan, less current portion | 24,000 | 30,000 |
| 1 | total | Liabilities | Total liabilities | 41,500 | 46,500 |
| 3 | item | Equity | Member capital | 15,000 | 15,000 |
| 3 | item | Equity | Retained earnings | 15,500 | 11,500 |
| 1 | total | Equity | Total equity | 30,500 | 26,500 |
| 1 | total | Liabilities and equity | Total liabilities and equity | 72,000 | 73,000 |
Three rules make this layout work. Type the label without leading spaces, because the level column now carries the indent. Keep the printed subtotal and total amounts as typed values in their own rows, so you can compare them with your own sums later. Mark them as subtotal or total in column B, so no formula ever adds them in by mistake.
The line-type column is what keeps a total from being counted twice. A plain SUM over column E would add the detail lines, the subtotals and the totals together and return a meaningless figure. Every formula in this workbook sums only rows whose type is "item".
Extracting statement lines as structured rows like these, with a label, amount, period, level and type on each line, is the subject of what financial statement extraction captures from each line.
How do you handle multi-column balance sheets?
Multi-column balance sheets show more than one date side by side: a comparative year, the last several month-ends, or a budget column. Handle them by giving each date its own amount column, with the date in the header row, and by keeping every column aligned to the same row structure. Never stack the years as separate blocks of rows.
Comparative balance sheets differ in which side holds the current year. Harbor and Pine puts 2026 on the left. Another client's report may put the prior year first. Before any formula touches the numbers, read the header on the PDF and type the date into the Excel header. A two-year sheet with the columns swapped will still balance, so the checks below will not catch it.
Month-end balance sheets add columns quickly. Twelve month-end columns plus a year-end column all share the same lines, so the level, type and section columns apply to all of them. Copy the check formulas across each column.
Watch for lines that exist in one period and not another. A loan taken out in 2026 shows a balance in the 2026 column and a blank in 2025. Leave the blank as a zero, not empty text, so it adds correctly. If a line was renamed between years, such as "Notes payable" becoming "Loan, less current portion", keep one row and note the rename beside it.
Columns that are not balances do not belong in the same grid. A "change" column or a percentage column printed on the report should be recalculated in Excel rather than imported. Building dollar and percent change columns across several years is covered in the guide to multi-year financial statement comparison.
Balance sheet to Excel pitfalls: parentheses, dashes and totals typed as values
The pitfalls that break a converted balance sheet are formatting symbols Excel reads as text: parentheses around negative numbers, a dash standing in for zero, currency signs, thousands separators in the wrong place and footnote markers. The other common pitfall is a subtotal row typed as a plain value that someone later includes in a sum.
Parentheses. On a printed statement, (1,200) means negative 1,200. After conversion, it may arrive as the text "(1,200)". Replace the parentheses with a leading minus sign before formatting. Microsoft's page on finding or replacing text and numbers on a worksheet covers Replace All across a column. Once the values are true negatives, the page on changing the way negative numbers are displayed shows how to put the parentheses back as a display format.
Dashes. A lone dash in an amount column means zero on many statements. Replace it with 0. Left as text, it is skipped by SUM, which Microsoft's SUM function page says ignores text values, so the total looks fine and is quietly incomplete.
Numbers stored as text. Even without symbols, converted amounts often arrive as text. Excel marks them with a small green triangle. Microsoft's guide to converting numbers stored as text to numbers gives the Convert to Number option and the VALUE function.
Label and amount in one cell. A conversion sometimes puts "Accounts receivable 6,180" in a single cell. Excel's Text to Columns wizard splits it, though labels with spaces may need a second pass.
Footnote markers. A superscript "1" after an amount can merge into it, turning 9,570 into 9,5701. The recalculation check catches this, because the section no longer adds up.
Totals typed as values. If the subtotal rows are not tagged by type, a later SUM over the whole column double-counts them. Tag every subtotal and total row as described above.
How do I check a converted balance sheet in Excel?
Check a converted balance sheet by recalculating every subtotal and total from its detail lines and comparing each result with the printed figure. Then confirm that total assets equal total liabilities and equity in every column. Any difference other than zero means a misread, missing or misplaced number, and its size often says which.
With the layout above, and headers in row 1, total current assets sits in row 5. Put this check in column G of that row, then copy it to each subtotal and total row, for the 2026 column:
=E5-SUMIFS(E:E, $B:$B, "item", $C:$C, $C5 & "*")
The & "*" adds a wildcard, so the "Assets" total sums every item whose section starts with "Assets", covering both current and noncurrent lines. Microsoft's SUMIFS function reference documents the asterisk as matching any sequence of characters. Leave the last row out of that copy, because no item's section starts with "Liabilities and equity". It needs its own check: total assets less total liabilities less total equity should be zero.
For Harbor and Pine Bakery, every check returns zero:
| Check (printed total less recalculated items) | 2026 | 2025 |
|---|---|---|
| Total current assets: 30,000 and 26,000 printed | 0 | 0 |
| Total assets: 72,000 and 73,000 printed | 0 | 0 |
| Total current liabilities: 17,500 and 16,500 printed | 0 | 0 |
| Total liabilities: 41,500 and 46,500 printed | 0 | 0 |
| Total equity: 30,500 and 26,500 printed | 0 | 0 |
| Assets less liabilities less equity | 0 | 0 |
Now suppose the conversion read inventory for 2026 as 9,510 instead of 9,570. Total current assets would recalculate to 29,940, sixty short of the printed 30,000, and the check cell would show 60. The total assets check would show 60 as well, because it sums the same items, while the liability and equity checks stay at zero. A small difference confined to one section's checks points to a single misread digit in that section.
If the converted balance sheet will be compared with a trial balance or an income statement, keep it in the same workbook. The guide to an income statement to Excel with periods and subtotals sets up a matching tab and links net income to equity.
A firm that receives balance sheets from many clients, mixed in with bank statements and tax forms, spends real time just finding them. Schema CPA reads balance sheets and income statements along with the rest of a client's documents and sends the figures to Excel or CSV; the list of document types Schema CPA reads is on the homepage.