After you create a pivot table, you can insert a pivot chart, based on that pivot table. In this example, the chart shows sales data, per city, over two years. See how to change the chart layout, after you build it.
Top 10 Filters are a quick and easy way to focus on key items in a pivot table. Instead of looking at all the data, hide everything except a set number of top or bottom items.
In the screen shot below, you can see the Pivot Table Top 10 Filter dialog box. There are four drop down lists, where you can change the settings, but Excel won’t let you link to a worksheet cell, like it does in some dialog boxes.
When you create a new pivot table in Excel, it uses the Compact Layout, by default. That layout has all the row fields in a single column, with each field slightly indented from the previous field. You can read more about the Compact Layout on my Contextures website.
NOTE: If you have my PivotPower Premium add-in installed, you can select a layout in your Preference Settings. That makes it easy to apply all your favourite settings to a new pivot table, or any existing pivot tables.
Long ago, there was a Chart Wizard in Excel, and it had some built-in Combination Chart Types, like Column-Line. The Chart Wizard disappeared in Excel 2007, along with the combo chart type options.
You could still create combo charts in Excel 2007 and 2010, but it wasn’t quite as obvious how to do that.
Combo Charts in Excel 2013
In Excel 2013, it’s easier to change a chart to a combo chart.
- Right-click on the chart, and click Change Chart Type
- At the left, select Combo as the category
- At the top, select one of the built-in combo types, or click the last option, to create a custom combo type.
- At the bottom, you can change the chart type for any series, and select which series to plot on a Secondary axis.
- Click OK when you’re finished, to see the completed chart on the worksheet.
Download the Sample File
You can download the sample file for this tutorial from my Contextures website: Pivot Chart Tutorial
Watch the Video
To see the steps for creating a pivot chart, and changing it to a combination column-line chart, please watch this short video.
Or watch on YouTube: Create Column Line Chart for Excel 2013 Pivot Table
When you create a pivot table in your workbook, the source data records are saved in a special memory area – a pivot cache. Then, when you close the file, Excel can save the source data in this pivot cache, or clear that memory.
Keep reading, to see how you can change this setting, and learn the advantages and disadvantages of either choice.
Note: If you want to re-create the source data, see: Re-create the Source Data
A frequent question about pivot tables is how to create one from data on different worksheets, or even in different file. Last week, I updated my page on this topic, which shows several ways you can accomplish this goal.
- Multiple Consolidation Ranges
- Microsoft Query
- Power Query
When you create a pivot table, do you just slap it together, and hope for the best, or do you spend time experimenting with different calculations, layouts and field arrangements?
Yes, it’s easy to fall into a rut, and create the same type of pivot table, each time that you build one. That makes it quick and easy to get the job done, but you might not be presenting the data in the best way possible.
It’s time to give your pivot table a makeover, so it looks better, and its data is easier to read and understand.
In addition to using fields from the source data, you can create calculated fields in a pivot table, to add your own formulas. For example, add a field that multiplies the total sales by 3%, to show a Bonus amount.
You can learn the basics of Calculated Fields on my Contextures website.
Last week, someone asked me how they could get rid of the extra items that appear in the filter, when you group a date field. Maybe you’ve seen them too – they start with a less than or greater than symbol, and show the first and last dates in your pivot table data.
You can see an example in the screen shot below.
One of the top pivot table fears in our survey was “How do you insert a calculated field?” And no wonder it was near the top of the list – pivot table formulas can be very confusing!
So, let’s take a look at calculated fields, and its close companion, the calculated item.