How to see a big tabular excel into small view/datasets and update the master table

**Ravisca2003** · 12-04-2021, 12:49 AM

Dear All,
I am working with a very Big Excel sheet with 150 col and more than 6000 records There are 5 manager who need different data each time and I have to put filters and hide col etc to make worksheet manageable .

I am trying to understand, if we can create smaller views from the big table and only see what we want to see. I also need to update the details sometimes like missing phone numbers etc in the master table which should be possible while we are reviewing the data from the smaller data set /view.

Please note that I am familiar with concept of Pivots to prepare specified views but the problem is that if we during a meeting there is a record which need to be updated , i have to go back to the main table, find the record manually and them make changes in the master. Hence i posted this question in this forum to check if there is a better solution which exist.

Capture.PNG

**Excelko** · 12-08-2021, 07:08 PM

Hi Ravisca,
Question is if you want to maintain one "master" excel sheet or split data into multiple sheets. If you have all data in one sheet you can create new sheets with pivot tables or make new table using Filter() function in office 365.
But in this case you would need to update records in "master" sheet as that is source of data.
Other option is to make multiple sheets that you will than consolidate into one.
In this case you will need to do changes in separate sheets.

**alansidman** · 12-08-2021, 07:21 PM

You may want to explore the functionality of Power Query which is an add in option for XL2013

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

**Ravisca2003** · 12-09-2021, 05:09 AM

Thanks Alan for responding. I will check this option of Power query

How to see a big tabular excel into small view/datasets and update the master table

LinkBack

Thread Tools

Rate This Thread

Display

How to see a big tabular excel into small view/datasets and update the master table

Re: How to see a big tabular excel into small view/datasets and update the master table

Re: How to see a big tabular excel into small view/datasets and update the master table

Re: How to see a big tabular excel into small view/datasets and update the master table

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Slide master: don't show shapes that are overflowing the master in normal view

Please help me using this small excel calcultor in userform calculator view

Vba-interactive userform to input/view/update data in table

[SOLVED] code to update certain cells in a master table from a smaller table on another workeheet?

Update Master Table from Subworksheets that are Sent to Clients

Excel Pivot Table shows zeros from a SQL View hitting an Oracle View via a Linked Server

Update Master Table

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions