After converting this month’s PDF with Adobe Acrobat’s PDF to Excel tool, merge it with last month’s Excel data using Excel’s Power Query to append the rows. Adobe says the converter turns PDF tables into Excel rows and columns you can edit, and works with XLS and XLSX files.
This article is for anyone who has a finished spreadsheet and wants a tool to merge it with last month’s Excel data.
- Which Tool Merges It With Last Month’s Data?
- What Merges Last Month’s Excel Data Into This Month’s Spreadsheet
- Merging It With Last Month’s Data Without Duplicates
- Frequently Asked Questions
- What happens if the columns are in a different order?
- Can I do this in Excel for the web?
- Will the combined data update when next month’s file arrives?
- Is there a size limit when merging in Google Sheets?
Which Tool Merges It With Last Month’s Data?
Power Query merges it with last month’s data when you append the two tables into one list. Microsoft’s support page says an append operation creates a new query that contains all rows from a first query followed by all rows from a second query. It also says that to combine tables based on matching column values, you use Merge instead. For monthly files, append stacks the new rows under the old ones. Use Merge only when you need to match rows on a key column.
- Append versus Merge: Microsoft’s support page says Append combines rows from multiple tables, while Merge creates a new query from two queries in a join operation on a common column value.
- Two append styles: Microsoft’s Power Query page says an Intermediate Append creates a new query for each append operation, while an Inline Append appends data to a query until you reach a final result.
- Minimum inputs: Microsoft’s support page says an append operation requires at least two queries.
- Header names: Microsoft’s Power Query documentation says an append is based on the column header names in both tables, not on their position.
- Across workbooks: Microsoft’s combine data page says Power Query lets you automatically import and combine data from multiple tables or sheets, even across workbooks.
- Any source: Microsoft’s Power Query page says Merge and Append work on any query with a tabular shape, whatever the data source.
- Convert each month the same way: Adobe’s page says its converter keeps your tables, rows, and columns in the format of your original file.
What Merges Last Month’s Excel Data Into This Month’s Spreadsheet
What merges last month’s Excel data into this month’s spreadsheet depends on the tool: Excel’s Power Query, Excel’s VSTACK formula, Google Sheets, and Adobe Acrobat’s PDF tools each cover part of the job. Microsoft’s combine data page says that if your sheets share the same column structure, you can dynamically stack them with VSTACK, and that Consolidate summarizes values instead of appending them. Google’s arrays help page describes joining ranges into one continuous column with curly braces. Pick the tool that matches where your data lives.
| Platform | Best For | Key Details |
| Excel Power Query | Appending monthly tables and refreshing later | Microsoft says append stacks all rows from a first query followed by a second, based on header names |
| Excel VSTACK formula | Stacking sheets that share the same columns | Microsoft says VSTACK works when sheets have the same column structure |
| Excel for the web | Combining data without desktop Excel | Microsoft says if Consolidate is missing, use Power Query or formulas instead |
| Google Sheets | Stacking ranges across spreadsheets | Google says IMPORTRANGE pulls cells from another spreadsheet, and curly braces join ranges into one column |
| Adobe Acrobat PDF to Excel | Producing the new month’s spreadsheet | Adobe says it turns PDF tables into editable Excel rows and columns, in XLS or XLSX |
- Consolidate: Microsoft’s combine data page says the Consolidate feature summarizes values, so use VSTACK or Power Query when you want to append data instead.
- Excel for the web: Microsoft’s combine data page says some versions, including Excel for the web, may not have Consolidate.
- Edit on the web: Adobe’s page says you can edit the converted file in Microsoft Excel for the web after signing in.
- Google arrays: Google’s arrays help page says you can join multiple ranges into one continuous range using curly braces and a semicolon.
- Google IMPORTRANGE: Google’s IMPORTRANGE help page says it imports a range of cells from a specified spreadsheet, and that the sheet name in the range is optional.
- Google import limit: Google’s IMPORTRANGE help page says it is capped at 10 MB of received data per request.
- Vendor claims: these details come from each product’s own help pages, so confirm the version you use.
Merging It With Last Month’s Data Without Duplicates
Merging it with last month’s data without duplicates starts with using the same column headers in both files and appending only the new rows. Microsoft’s Power Query documentation says an append performs the combination by header names, so a renamed column will not line up. Convert each month’s PDF the same way, and compare the headings before you append. If rows overlap between months, check for repeated entries after you append, or use Merge on a key column such as an invoice number to compare the two tables.
- Same headers: Microsoft’s Power Query documentation says append matches on column header names, so keep the headings identical from month to month.
- Same structure for VSTACK: Microsoft’s combine data page says VSTACK needs the sheets to have the same column structure.
- Merge before conversion: Adobe’s Merge PDFs tool combines multiple files into a single PDF, which lets you convert two months in one pass.
- Protected PDFs: Adobe’s page says to unlock a password protected PDF with its password before you convert it to a spreadsheet.
- Scans: Adobe’s page points to an Acrobat Pro trial for OCR, so a scanned PDF may need a different workflow.
- Keep the original: keep last month’s file untouched, and append into a copy.
Frequently Asked Questions
What happens if the columns are in a different order?
It depends on the tool. Microsoft’s Power Query documentation says an append uses column header names rather than position, so reordered columns still line up if the names match. VSTACK, in contrast, needs the sheets to share the same column structure. Check the header row in both files before you combine them, and fix any renamed columns first.
Can I do this in Excel for the web?
Partly. Microsoft’s combine data page says that if your version lacks the Consolidate feature, such as Excel for the web, you can use Power Query or formulas instead. Adobe’s page says you can edit the converted file in Excel for the web after signing in. Check which features your version supports before you plan a monthly process around them.
Will the combined data update when next month’s file arrives?
Formula links can. Microsoft’s combine data page describes VSTACK as a dynamic stack, and Power Query as a way to automatically import and combine data from multiple tables. Google’s help page says IMPORTRANGE updates can propagate across sheets. Test with a small sample first, and keep a copy of each month’s source file so you can rebuild the combined table.
Is there a size limit when merging in Google Sheets?
For IMPORTRANGE, yes. Google’s help page says Sheets must download the entire range, which is affected by slow networks, and that it is capped at 10 MB of received data per request. It also says to limit chains of IMPORTRANGE across multiple sheets. For large monthly tables, test a full import first and consider splitting the range.
To merge it with last month’s Excel data, append the two tables in Power Query, and use Merge only when you need to match rows on a key column.
