When you create a pivot table it groups the items from your data, and calculates a total for each group. In the screen shot below, you can see the total sales for each category, in each city in the West region.
Duplicate Items Appear in Pivot Table
Create Pivot Chart from Data in Excel 2013
With a new feature in Excel 2013, you can create a pivot chart right from the source data – you don’t have to build a pivot table first.
Continue reading “Create Pivot Chart from Data in Excel 2013”
Prevent Subtotals from Appearing
If you add more than one field to the Rows or Columns area in a pivot table, some of the fields automatically get subtotals. In the screen shot below:
- the City field has a subtotal, because it is an outer field – there is at least one field after it in the layout. Outer fields get subtotals by default.
- the Category field does not have a subtotal, because it is an inner field – there is no field after it in the layout. The innermost field in the Rows or Columns area doesn’t get a subtotal.
Add All Remaining Fields to Pivot Table
After you create a pivot table, you can add or remove fields by using the check boxes in the field list. Text fields are automatically added to the Row Labels area, and numeric fields go into the Values area.
Changing Pivot Chart Affects Excel Pivot Table
After you create a pivot table, you can insert a pivot chart, based on that pivot table. However, if you change the layout of the pivot chart, it will also change the pivot table’s layout.
For example, if I remove City from the chart in the screen shot below, the City field will also be remove from the pivot table.
Continue reading “Changing Pivot Chart Affects Excel Pivot Table”
Prevent Changes to Pivot Table Setup
After you set up a pivot table, you might want to prevent other people from making changes to the way that it is set up. With a bit of programming, you can restrict what happens to a pivot table.
Show Survey Responses in Pivot Chart
I’ve updated one of the pivot chart sample files on my Contextures website. On the main sheet, there are two pivot charts, showing survey responses by department, and by years of service.
At the top of the sheet, you can select a question from the dropdown list, and view the survey results for that question.
Scroll Through Report Filter Items
In Excel 2010, slicers were added to Excel, and they make it easy to select one or more items in a pivot table’s Report Filter.
Running Total Stops at Year End
A quick way to show how amounts accumulate over time is to build a pivot table, and show the values as a running total.
However, if you group the dates by year and month, the running total stops at the end of each year, and starts again at the start of the next year. There is no setting you can adjust to change this behavior.
Create a Pivot Table in Excel 2013
There is a new feature in Excel 2013, that makes it even easier to get started with pivot tables.
Just select a cell in your source data table, and on the Ribbon’s Insert tab, click Recommended PivotTables.