Spreadsheets

Merge yearly expense tabs into one master transaction list

Combine multiple Excel sheets of expenses into one master list: stack yearly tabs, add a year column, fix column order and remove duplicates safely.

Editorial illustration for Merge yearly expense tabs into one master transaction list

To merge yearly expense tabs, first make every tab use the same column headings, then stack the rows into one table and add a Year column so you can still tell the periods apart. For a few hundred rows, copy and paste is fine. For thousands of rows, or if you will repeat this every year, use Power Query in Excel or a QUERY formula in Google Sheets.

What should you fix before merging?

  1. Check the headings. Date, Description, Category, Amount and Account should be spelt the same on every tab. Power Query matches columns by name, so Amt on one tab and Amount on another becomes two separate columns.
  2. Check the dates. Make sure every date is a real date and not text. Click a cell: a right-aligned value is usually a real date, a left-aligned one is often text.
  3. Check amounts. Debits and credits should follow one convention, either one signed Amount column or separate Debit and Credit columns on every tab.
  4. Take a backup copy of the workbook. A merge is much easier to undo if the original is untouched.

How do you merge small sheets by copy and paste?

Create a new tab called Master. Paste the first tab with its heading row, then paste only the data rows of each later tab underneath. Add a Year column on the right and fill it by tab as you go. If you would rather derive it, put the Indian financial year in the column with =IF(MONTH(A2)>=4,YEAR(A2),YEAR(A2)-1), which returns 2025 for any date from April 2025 to March 2026.

Then check the merge. Say the three tabs have 1,200, 1,350 and 1,500 rows. The master should have 1,200 + 1,350 + 1,500 = 4,050 data rows, and the sum of the Amount column should equal the three tab totals added together. If either number is off, a paste was cut short.

How do you merge with Power Query in Excel?

  1. On each yearly tab, click inside the data and press Ctrl+T to make it a table, then name each one in the Table Design tab, for example Exp2324, Exp2425, Exp2526.
  2. On the Data tab choose Get Data, then Combine Queries, then Append. Pick the first two tables, then add the rest as a three-or-more-table append.
  3. In the Power Query window, check that the Date column has a date type and Amount has a number type.
  4. Choose Close and Load. The master table appears on a new sheet.

To add the year, open each source query before appending and add a custom column named Year with the year as a fixed value, or build Year from the date as shown earlier. Next year, add the new table to the append and refresh. If you use a recent Excel 365, =VSTACK(Exp2324,Exp2425,Exp2526) also stacks tables, but it expects the columns in the same order, which Power Query does not.

How do you merge tabs in Google Sheets?

Stack ranges with curly brackets and a semicolon between them: ={'FY2324'!A2:E;'FY2425'!A2:E;'FY2526'!A2:E}. In some regional settings the separators differ, so use the comma-and-semicolon combination your sheet shows when you type an opening curly bracket. To drop blank rows and sort by date, wrap it: =QUERY({'FY2324'!A2:E;'FY2425'!A2:E;'FY2526'!A2:E},"select * where Col1 is not null order by Col1",0). Col1 means the first column, which should be the date.

Unlike Power Query, this stacks by position. If one year has Category before Description, fix that tab first.

What about duplicates after the merge?

Duplicates appear when a month was pasted into two yearly tabs, or a statement overlapped the year end. Do not rush into Remove Duplicates. Two genuine ₹20 chai payments on the same day with the same description look identical to it. Add a helper column that counts matches on date, amount and description, review the rows with a count above one, and only then delete. In Google Sheets, =UNIQUE(range) lists distinct rows but has the same blind spot.

When merging becomes a yearly chore

If you find yourself repeating this every April, the problem is the habit of starting a new tab each year. One continuous table with a Year or Financial year column avoids the merge altogether. And if you are about to start fresh in a tracker, SpendixAI accepts statements as PDF, CSV or Excel, so recent months can be imported straight from the bank file without recreating tabs; rows are reviewed before they are saved.

  • Same headings on every tab
  • Backup made
  • Row count equals the sum of tab counts
  • Amount total equals the sum of tab totals
  • Duplicates reviewed, not blindly deleted
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports