automatically updating master sp

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Automatically updating Master Spreadsheet | Excel forum

Automatically updating Master Spreadsheet | Excel forum

resolvedResolved · Medium Priority · Version 365

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

Edited on Thu 27 Jun 2024, 11:21

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


 

Excel tip:

Highlighting only Text cells

To select onlt text value cells in a spreadsheet, click on Edit-Go to(F5)


In the Go To dialog box, click Special.
In the Go To Special dialog box, select Constants.
Click OK.

View all Excel hints and tips


Server loaded in 0.08 secs.