Charlotte has attended:
Excel Advanced course
Project Management - Framework & Processes course
Building Confidence and Assertiveness at Work course
Conflict Management and Resolution course
Introduction to Management course
Report Writing Mastery: From Beginner to Pro course
Automatically updating Master Spreadsheet
We use Excel to track our spend so we have sheets that correspond to each month (January - December) where each expense is put into a table.
We wanted to create a Master sheet on the same workbook which collates all of the information from all sheets to put it into one big master table. This way we can filter to see who has spent the most over the year, what we have spent the most on etc. by just filtering one table rather than 12.
All tables have the same columns. How do I go about doing this?
RE: Automatically updating Master Spreadsheet
Hello Charlotte,
Thank you for your question.
You can achieve this by using Excel Power Query:
Ensure that your datasets are in table form (you can create tables by selecting a range and pressing CTRL + T). Rename each table in the Table Design tab.
Go to the Data tab and select Get Data > From Other Sources > Blank Query.
In the Power Query Editor, enter the formula: =Excel.CurrentWorkbook() in the formula bar and press ENTER. (Excel will now automatically find all the formatted tables in your file)
Click the double-headed arrow to select the columns you want to combine.
Leave the “Use original column name as prefix” option unchecked.
Click OK, then select Close & Load to create a new table combining the datasets.
Remember to adjust these steps based on your specific column names and data ranges.
The benefit of doing it this way is that you can refresh your master table by simply clicking Refresh in the Data tab in Excel. Also, formatted tables grow with any new data.
Let me know if you need further assistance!
Kind regards
Marius Barnard
STL
RE: Automatically updating Master Spreadsheet
Hi Marius,
That is super helpful thank you!
I struggled to find how to remove the totals for each table but managed to filter this to exclude them.
Much appreciated,
Charlotte
RE: Automatically updating Master Spreadsheet
Hi Charlotte,
You're very welcome. Please don't hesitate if you need assistance in future!
Marius
