Skip to main content

Pivot table for Each item in Report Filter - Hidden Feature




The pivot table below shows products across the top and customers down the side. The pivot table is sorted so the largest customers are at the top. The Sales Rep field is in the report filter.

This could be any pivot table, but the Rep field has been added to the Filter area.

If you open the Rep dropdown, you can filter the data to any one sales rep.

The typical way to use the Filter field is to open the dropdown in B1 and choose a sales rep from the list.

This is a great way to create a report for each sales rep. Each report summarizes the revenue from a particular salesperson‘s customers, with the biggest customers at the top. And you get to see the split between the various products.

Here is the pivot table, now showing numbers for the selected sales rep.

The Excel team has hidden a feature called Show Report Filter Pages. Select any pivot table that has a field in the report filter. Go to the Analyze tab (or the Options tab in Excel 2007/2010). On the far left side is the large Options button. Next to the large Options button is a tiny dropdown arrow. Click this dropdown and choose Show Report Filter Pages....

But here is another way to use the Filter field. Change the drop-down in B1 back to (All). Then, look on the left side of the Analyze tab. There is an Options button. To the right of the Options button is a drop-down arrow. Open that and choose Show Report Filter Pages. The other two items in this menu are Options and Generate GetPivotData.

Excel asks which field you want to use. Select the one you want (in this case the only one available) and click OK.

This is the Show Report Filter Pages dialog. It says "Show All Report Filter Pages Of" and then gives you a list of all the fields in the Filter area. In the current case, there is only one field there - Rep. Choose that field and click OK.

Over the next few seconds, Excel starts inserting new worksheets, one for each sales rep. Each sheet tab is named after the sales rep. Inside each worksheet, Excel replicates the pivot table but changes the name in the report filter to this sales rep.

The Show Report Filter Pages command has inserted many new worksheets to the left of the original pivot table. Each worksheet name has the next sales rep name as the sheet name. On each sheet, the Rep drop-down in B1 is showing the appropriate sales rep for that page.

You end up with a report for each sales rep.

This would work with any field. If you want a report for each customer, product, vendor, or something else, add it to the report filter and use Show Report Filter Pages.

Labels:

Comments

Post a Comment

Popular posts from this blog

Data Cleaning Functions in Excel

  The CLEAN function Using the CLEAN function removes nonprintable characters text. For example, if the text labels shown in a column are using crazy nonprintable characters that end up showing as solid blocks or goofy symbols, you can use the CLEAN function to clean up this text. The cleaned‐up text can be stored in another column. You can then work with the cleaned text column. The CLEAN function uses the following syntax: CLEAN(text) The text argument is the text string or a reference to the cell holding the text string that you want to clean. For example, to clean the text stored in Cell A1, use the following syntax: CLEAN(A1) The CONCATENATE function The CONCATENATE function combines, or joins, chunks of text into a single text string. The CONCATENATE function uses the following syntax: CONCATENATE(text1,text2,text3,...) The text1, text2, text3, and so on arguments are the chunks of text that you want to combine into a single string. For example, if the city, state, and ZIP co...
Post a Comment
Read more

Import CSV In Power BI

  Import CSV file Click on Get Data à More à File option and select Text/CSV . Navigate to the CSV file which needs to be imported FL_insurance_sample.csv . Select the file and click on Open. FL_insurance_sampleDownload In the CSV window on top we have 3 dropdowns, preview of data and data load options.  Select Load and it will load to power query editor window. In the Power query editor the CSV file is loaded as a Queries . In power query editor we can edit, clean and transform the file as required. 3 Dropdowns and Data Load File Origin – Type of file origin. By default its 1252 Wester European (Windows). It’s the file type as per OS and region and country. Delimiter – Delimiter for column separation. By default it detects the delimiter from data , If the delimiter is not from the default options then we can select custom delimiter. Data Type Detection – By default it detects data types of columns based on top 200 rows, we can select entire data or do not detect data type ...
Post a Comment
Read more

Formatting In Excel - helps you find meaning in the spreadsheet

  Formatting In Excel -  helps you find meaning in the spreadsheet  Spreadsheets are often seen as boring and pure tools of utility, but that doesn't mean that we can't bring some style and formatting to our spreadsheets Formatting helps your user find meaning in the spreadsheet without going through each and every individual cell. Cells with formatting will draw the viewer's attention to the important cells. In Excel, formatting worksheet data is easy. You can use several fast and simple ways to create professional-looking worksheets that display your data effectively. For example, you can use document themes for a uniform look throughout all of your Excel spreadsheets, styles to apply predefined formats, and other manual formatting features to highlight important data. Formatting a Data Raw Data   Using Font, Number tabs as shown in image to do a simple formatting   Formatting a Data with help of formatting tools in Excel  As shown in image , we have Prod...
Post a Comment
Read more