Exploring Pivot Table Features in Microsoft Excel
In this article, we will briefly describe most of the options and features given to the user in two Ribbons that appear in Excel when selecting a PivotTable.Two unique ribbons, one called Optionsand the otherDesigncontain many helpful features for the Excel user. Below are screenshots of each and our tips on how to use them.
The Options Ribbon
1. PivotTable
a. PivotTable Name: This name is not for visual or printing purposes but rather a named reference.Use it to always refer to a specific value in a specific PivotTable.
2. Active Field
a. Name: Similar to PivotTable name.
b. Field Settings: Here you can edit the way a field is summarized, which will be detailed in a future article.You can also add Calculated Fields.
3. Group:Grouping can consolidate your data according to preset choices.For instance, a series of mm/dd/yyyy dates can be grouped by month.
4. Sort & Filter
a. Sort: You can sort your PivotTable as you would a normal table.
b. Slicers: Slicers allow you to filter the PivotTable by any item in any field, whether that field is part of your PivotTable or not.
5. Data:Refresh your PivotTable to update it after the source data changes.
6. Actions:Quickly start over, select, or move your PivotTable on the spreadsheet.
7. Calculations
a. Show Values As: Additional ways of calculating your selected fields.For example, you can not only sum the sales of each customer, you could display it as a % of the Grand Total.
b. Fields, Items, & Sets: Add a calculated field.For instance, if your sales are very high and you want them displayed in 1000s to shrink the table size, add a calculated field of Sales-Field / 1000.
8. Tools:PivotCharts are similar to regular Excel charts but with PivotTable filtering features and drawn from the PivotTable data.
Design Ribbon
1. Layout
a. Subtotals: With multiple fields in either the Row or Column labels you can view subtotals of each group in additional to a grand total of all groups.
b. Grand Totals: Grand totals can be selected for Rows, Columns, or both.
c. Report Layout: Three types of layouts give you display options.Try out each before you print.
d. Blank Rows: Insert blank lines between multiple row groups to make your PivotTable easier to read
2. PivotTable Style Options:These four options will change how rows and columns are emphasized in the color schemes of the default styles.
3. PivotTable Styles:Many Light, Medium, and Dark options to give your PivotTable color and borders so that you don’t spend your valuable time manually formatting.
If you try out the options given in these two ribbons with each PivotTable you create, we promise that your productivity and effectiveness will increase quickly.
