98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Automatically updating Master Spreadsheet | Excel forum
Automatically updating Master Spreadsheet | Excel forum
Resolved · Medium Priority · Version 365
Tue 25 Jun 2024, 13:58Delegate Charlotte said...
Charlotte has attended:
Excel Advanced course
Project Management - Framework & Processes course
Building Confidence and Assertiveness at Work course
Conflict Management and Resolution Course 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?
Thu 27 Jun 2024, 09:47Trainer Marius said...
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
Thu 27 Jun 2024, 10:54
Edited on Thu 27 Jun 2024, 11:21Delegate Charlotte said...
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
Thu 27 Jun 2024, 11:51Trainer Marius said...
RE: Automatically updating Master Spreadsheet
Hi Charlotte,
You're very welcome. Please don't hesitate if you need assistance in future!
Marius
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Highlighting only Text cellsTo select onlt text value cells in a spreadsheet, click on Edit-Go to(F5) |
