Displaying a portion of a table in a separate worksheet

Question

On my first worksheet in the workbook, I have a large table with multiple columns and rows (roughly a 1000 rows and 20 columns).

I was wondering if it is possible to display part of this table in another worksheet in the workbook based on the criteria from one of the columns in the main table.

To help explain this, I will use an example from the actual workbook.

On worksheet 1 (called General Employee Info which is the main table), in Column A each row has a unique number identifying an employee (each employee has their own ID number).

Column B holds their office location (a city/municipality). Column C holds the state the office is in. Column D holds their salary amount per year.

There are multiple more columns with this type of employee information, which needs no further explanation.

My question is, can I label Sheet 2 in the workbook "New York State" and have that sheet display all the same information as Sheet 1 (all the same columns) but only display the employees that work in the state of New York?

And then have another sheet labelled "California" and have only the employees that work in California on that sheet and so on... In other words, have a separate sheet for each state.

It's essentially like filtering the main table using the State column to show only one state, but I want the results in a separate sheet instead of filtering the main table which holds all the information.

I was thinking of using a pivot table in each new sheet, but am not sure that's the right approach.

Is this possible? I hope I was clear in explaining the problem.

Answer 1

I really recommend using the excel tables feature if you are using Excel 2013 or later as a starting point. Tables rock for many reasons, including making your sheets more readable for users, and making formulas for things like INDEX/MATCH much less error prone.

Once the tables are in place with the main data, to subset I use the data modeling capability to link back to those tables.

These steps in Excel 2013 - version active around 2018. It's changed in Office 365 (see below)

1. create a sheet, or go where you want the table in your current sheet.
2. click the 'data' tab and select "connections" in the connections section.
3. from there select the drop down on the add button and select Add to Data Model.. Connections Dialog
4. Click the tables tab. Click the table you want.
5. now close the dialog
6. on the data tab, click Existing Connections in the "get external connections" of the data tab.
7. Click the Table tab
8. Select the table you want
9. Select the table radio button
10. hit return (or new sheet or a different cell if that's what you want)
11. poof your table from another sheet is now reflected in the current worksheet.

At this point you can subset using data slicing, hide columns, insert new columns with new formulas on the right or left edges of the table, etc.

You do have to refresh this separately using the 'refresh all' button on the tool bar.

In Excel as of October 2019 as (Thank you Josh for point this out!) It's gotten better as they include some of the 'power' tools in excel, and made a toolbar item that gets to the functions. Specifically:

1. Create a Table with the source data by selecting the data in the sheet, and using insert Table.

2. Select the Table

3. On the Data Tab click the small icon in the lower middle with the tooltip "from Table/Range"

4. This will Open the Power Query Editor. Microsoft has a tutorial; but it looks a little different now

5. After you've built your query; press "Close and Load" in the editor. Without options it will create a new sheet. If you choose the option with the ellipsis you have a few other options.