Back to lesson
1. In the data set below, the PivotTable is showing the Class (Alcohol) and the Categories (Bottled Beer, Canned Beer, etc…) on rows. What steps did the author take in order to group the data this way?
Select the Bottled Beer, Canned Beer and Coolers/Ciders, right clicked them and selected to Group those items.
They added a new column in the source data table to classify the categories by a Master Category, then pulled that on to the Pivot between Class and Category.
They went to the PivotTable Tools > Analyze tab (Options in Excel 2010) and chose Fields, Items & Sets > Create Set
2. Assume you have a data source that has a single column full of dates, as shown below. You’d like to use this column but display your PivotTable with Months on rows and Years on columns. Since you have over 500,000 rows of data in the table, you’d like to keep your file working as quickly as possible, so you…
Create a Month and Year column in your data source, refresh the PivotTable and place Month on rows and Year on columns.
Select the Date on the PivotTable > PivotTable Tools > Options > Grouping > Group by Months. Repeat the process for Group by Years and then move Years to the Columns area.
Pull the POSChitDate field onto the rows area of your PivotTable, right click the dates in the worksheet, choose to Group by Months and Years, then drag Years to the Columns area.
3. How do you completely remove a grouping level that you’ve set up?
Pull the grouping level off the PivotTable.
Right click the Grouping level in the PivotTable > UnGroup and make sure to repeat this until every item you grouped is now ungrouped.
Select the grouping level in the Field Well List and press the Delete key.
Back to the top
© 2017 GoSkills Ltd.
Skills for career advancement