Over the months, I have talked a lot about Pivot Tables because I believe that they are an awesome and free add-in for Microsoft Excel. This article will be the last one for awhile on Pivot Tables so I want to cover a topic related to how to hide columns that you do not want the end users of the pivot table to see or use.
The first and easiest way to hide a column from a user is to never include it in the Pivot Table source tables in the first place. When importing data from SQL Server, this is not only easy but is also highly recommended. I’ve covered the basics on loading data from SQL Server in the past and will not repeat all those steps here. However, I will pick up the process with the Table Import Wizard as shown in the following figure. This figure appears after you have selected the connection (or created a new one) to a specific database in one of your SQL Servers.
Note that this figure shows a list of the tables in the database. You can select the tables you want to import to your PowerPivot window by selecting the checkbox to the left of one or more of the tables. That that in the fifth column, you can provide a Friendly Name for the table rather than the default which is the table name itself. However, the last column, Filter Details allows you to filter not only the rows that you import, but also the columns. To define a filter for a table, click the Preview & Filter button in the bottom right of this dialog.
The screen that appears shows a grid of your data including the column names in the header and several rows of sample data. Rather than just importing the entire table, you can choose which columns you want to keep and which columns you to exclude. By default, the import dialog assumes you want to import all the columns. However, by clicking the checkbox to the left of the column name to remove the checkmark, you can exclude columns that you do not want. In the following figure, I’ve excluded the columns: ProductSubcategoryDescription, ETLLoadID and LoadDate. (Note, ProductSubcategoryDescription appears to be a duplicate of ProductSubcategoryName.)
Clicking OK returns me to the dialog page where I can continue to select other tables and filter them. When I click the Finish button on the Table Import Wizard page, Excel PowerPivot begins the process of downloading the selected data to the PowerPivot Window. After Excel loads the data, you can open a downloaded table by clicking on the tab displaying the table’s name. As shown in the following figure, the downloaded table for ProductSubcategory does not contain the three fields specifically excluded.
This is the best way to eliminate data that you do not want to display to the end user of the Pivot Table for at least 4 reasons.
Eliminating columns that you do not want increases the speed of the data import.
- Fewer columns downloaded means a small table size for a given number of records.
- A smaller table size directly correlates to better performance.
- Finally, Excel PowerPivot tables have a 2GB file size limit. The fewer columns you include, the more rows you can import.
Suppose you already imported your tables when you realize that several columns simply are not needed. You can then use the Hide Columns option in the Design ribbon of the PowerPivot window to display a list of the fields in the current table (remember to first select the table you want to work with by clicking on its tab). The resulting dialog as shown below displays all the columns in the table along with two columns with checkboxes. The first column lets you select whether the field appears in the PowerPivot window. The second column lets you select whether the field appears in the resulting Pivot Table Field List.
In this figure I hiding some columns in the PowerPivot Window, but not in the Pivot Table itself. Why do this? When building calculated columns, you member from a few weeks back that you can either enter the name of the column you want to use in your expression or you can simply click in one of the rows of that column. If I have a very wide table with lots of columns, I may come into this window, hide first all the columns with the (Select All) option and then just turn on the columns I need. Then I build my expression by clicking in the column to select them rather than trying to make sure I spell them correctly. Then after the expression is built, I turn all of the columns back on.
On the other hand, you might want to hide columns from the PivotTable, but not the PowerPivot window. You might do this because you need to see the column in the PowerPivot window so you can build expressions using those columns, but you want users to work with the calculated column only, not the columns that went into defining it.
For this example, I will simply eliminate the columns ProductSubcategoryKey and UpdateDate from the PivotTable leaving only 3 active fields. When I build a pivot table from this dataset, the PowerPivot Field List only includes those three fields as shown in the following figure.
If you build PivotTables for end user or even to publish to SharePoint, this is an excellent way to include the column in the PowerPivot window so you can create calculated columns, but then hide those unnecessary columns from the user eliminating any confusion.
Suppose you have a pivot table like the one shown in the next figure. Notice that the columns use a hierarchy that goes from year to quarter to month. (To get the months in the correct order, remember to check out my blog entry on creating custom lists to sort data.)
Suppose you only want the first month and last month of each quarter (skipping the middle month). You may not want to filter the imported data because in other Pivot tables or Pivot charts, you might need all the months. Therefore, you need a way to filter the data in the output PowerPivot table. You can do this with a Set.
With the PivotTable selected, open the Options ribbon and find the Fields, Items, & Sets option in the Calculations group of this ribbon as shown below.
When you click on this option, a dropdown menu appears that lets you define a set. Basically a set is either a selection of rows or columns that you want to show. In this case, we want to create a custom set of columns so select Create Set Based on Column Items…
A dialog with all of the columns appears. In this dialog, begin by giving this combination of columns (set) a unique identifying name. In the following figure, I named my set: First_and_Last. Then go through the list of columns selecting the columns you want to get rid of and clicking the Delete Row button. When you have finished, click the OK button.
When you PowerPivot table appears, you will see that the months of February and May do not appear in quarter 1 and 2 respectively.
Want to return to the table with all of the months again. Simply look at the Column Labels box in your PowerPivot Field List. Notice that column label no long shows the hierarchy: Year, Quarter, Month as it did before, but now shows the name of the Set you just created. Remove the set name by dragging it out of the box and place the year, quarter, month hierarchy back in the Column Labels box.
You can define multiple sets, but each set must be based off the full set of columns and rows. You cannot build a set based on another set. Your sets appear as if they were a separate dimension table in the field list under the name Sets at the bottom of the list. When you open Sets, you will see each of the sets you have created and can drag and drop them into the label pivot boxes as you choose.
Our discussion would not be complete with the easiest way to hide columns or rows that Excel has supported for many versions. Simply select the column(s) and right click the select column header to display the dropdown menu as shown below.
You can select the Hide option to hide the data. However, remember that any observant user will see notice the bold line separating the columns where the hidden column resides or they may notice the skip in the column letters or row numbers along with border of the Excel spreadsheet.
Obviously this last method is not very secure. But then the only really secure method is to never import columns into the PowerPivot window that you do not want the user to ‘discover’.
I hope you found this series on PowerPivot interesting. I hope to return to it with new topics at a future time. In the meantime, I’m going to explore some SQL Server features over the next several weeks.
C’ ya later.