Introduction
This tutorial shows how to pivot Tables: Automatically Update & Refresh When Source Data Changes in Excel. The method uses Power Query, PivotTable, Data Validation, VBA, so the workbook needs to support those features.
The example uses reporting data. You can use the same idea with similar business data, as long as your columns are clear and your source data is consistent.
Quick answer
Set up the source data first, then use PivotTables to create the result you need. Keep the input range clean, check the output, and save a copy before changing important files.
What you'll build
You will build a cleaner Excel workflow for reporting data. The goal is to reduce copy-and-paste work and make the output easier to check.
Prerequisites
Use a version of Excel that supports Power Query, PivotTable, Data Validation, VBA. Keep the source data in a simple table shape with one header row and no blank rows inside the dataset.
Useful shortcuts or keys in this workflow include F8.
1. Prepare the source data
Open the workbook that contains the data you want to use. Make sure each column has a clear header.
Remove blank rows inside the dataset. Check that dates, numbers, names, and status fields use a consistent format.
2. Build the Excel result
Use Power Query, PivotTable, Data Validation, VBA to create the result described by the task. Select only the fields needed for the final output.
Keep the output separate from the raw data. This makes it easier to test and easier to reuse.
3. Format and review the output
Apply number, date, and currency formats after the data appears. Do not rely on formatting alone to prove the result is correct.
Scan the first few rows and the last few rows. Make sure the records match the rule you meant to apply.
Test that it works
Change one source record and check whether the output changes as expected.
Compare the result against a small sample that you can verify by hand.
Troubleshooting
If the result is blank, check the source range, spelling, filters, and column headers.
Refresh the data if the output does not update.
Check formatting after the data is returned.
If the workbook does not update, refresh the data before changing the setup.
When this spreadsheet stops being enough
This Excel setup works well when one person owns the file and the process is small.
It becomes harder when people rename files, move folders, download new reports, or depend on someone remembering each step.
Automate this process
A more automated workflow can pull source data on a schedule, apply the same rules every time, validate records, and send only exceptions to staff.
If your team repeats this spreadsheet process every week, I can review the workflow and identify which parts can run automatically.