You can analyze PivotTable data in many ways, including sorting to quickly see trends. We'll also look at how to filter, summarize and calculate your data.
Want more?
Create a PivotTable to analyze external data
Create a PivotTable to analyze data in multiple tables
You can analyze PivotTable data in many ways.
One of the most common ways is sorting, it helps you quickly see trends in your data.
Right-click a value, such as the Grand Total for the Arts & Photography genre, point to Sort, click Sort Largest to Smallest, and the Genres are sorted from the largest to smallest Grand Total Sales amounts.
On the Quick Access Toolbar, click Undo, to undo the sort.
You can also filter your PivotTable. It helps you focus on the data you want to analyze.
Click the down-arrow next to Row Labels. Since we clicked the down-arrow for a label, point to Label Filters.
Click an option, such as Begins With. Type your criteria, such as the letter c, and click OK. And only genres of books that start with "c" are displayed.
To remove the filter, click the down arrow again, it now looks like a funnel because a filter is applied. And click Clear Filter from "Genre".
Slicers are one of the best ways to filter your PivotTable data.
For information about using slicers, see the 4th video in this course-- Use slicers, timelines, and PivotCharts to analyze your PivotTable data.
To view only the items in your PivotTable that you want, you can select the cells that contain the items.
These can be text or dates under Row Labels. You can't use numbers.
Right-click them (Genres in this example), point to Filter, click Keep Only Selected Items, and only the selected Genres are displayed.
To show only the three genres with the highest Grand Totals, right-click a Genre, point to Filter, click Top 10-- I know, clicking Top 10 to see the Top 3 doesn't seem to make sense, but look.
Change 10 to 3, click OK, and the top three genres are displayed.
Until now, the values in the PivotTable have been displayed as the sum of the Sales Amount field, but you can use other functions.
For example, right-click a cell in the Grand Total column, point to Summarize Values By (there are a lot of options, such as Min and Max), click an option (such as Average), and now the values in the PivotTable are summarized as averages.
You can see that the average Arts & Photography Sales is $1400.
In addition to summarizing the Sales figures, you can show them as a calculation.
For example, right-click a cell in the Grand Total column, point to Show Values As (there are a lot of options, such as % of Row Total and % of Column Total). Pick an option (such as % of Grand Total), and you can see that Arts & Photography represents a little over 10% of Sales and how each store contributes to that figure.
For more information about calculations in PivotTables, see the link in the course summary.
Up next: Use slicers, timelines, and PivotCharts to analyze your PivotTable data.
No comments:
Post a Comment