OmniDocCustomize Pivot Chart Type, Axes and Top Items
Learn how to change a pivot chart to a line chart, add a secondary axis, swap axes, show only the top 10 items and handle grand totals in Excel.
You built a pivot chart from a pivot table, and now it is almost right. The columns are the wrong type, the categories run the wrong way, a long tail of small items crowds the axis, or the total you need is missing. Creating a pivot chart gets it onto the sheet, but changing an existing one takes a few more controls.
This guide is for anyone who already has a working pivot chart in Excel and wants to adjust it. You will learn how to switch the chart to a line graph, put one series on a secondary axis, swap the horizontal and vertical layout, show only the top 10 items, and understand why grand totals don't appear in the chart. It also covers the one thing a pivot chart can't do directly, which is a scatter plot, and a general workaround that starts from a regular range.
The examples use a 12-month sales table by region, but any table with one category field and one or more value fields works the same way.
How do you customize a pivot chart's type, axes and top items? The short answer
Change the chart type from the chart's own settings, use the series settings for a secondary axis, and apply a Top 10 value filter to the row field, because the chart follows the pivot table.
| Your situation | What to do | Why |
|---|---|---|
| You want to show a trend over time | Change the chart type to a line chart | Lines make direction easier to read across ordered categories |
| One series uses a very different scale | Move that series to the secondary axis | Both series stay readable on one chart |
| Category names are long | Change to a bar chart | Labels fit along the vertical axis |
| The chart has too many small items | Apply a Top 10 value filter to the row field | The chart and table show only the largest items |
| You need a scatter plot | Build a regular range and chart that | Pivot charts don't offer scatter types |
Before you start
- Confirm the chart is a pivot chart. Click the chart and look for the field buttons on the chart itself. A chart built from a plain range won't respond to pivot filters, so the top 10 steps below won't affect it.
- Refresh the pivot table. Right-click the pivot table and refresh it. The chart reads from the table, so stale data gives you a top 10 list based on old numbers.
- Decide which field is the category and which is the series. Most axis changes come down to which field sits on the horizontal axis and which sits in the legend. Knowing this first saves guesswork.
- Save a copy of the workbook. Rearranging fields changes the table's shape, and a copy makes it easy to compare before and after.
Changing a pivot chart to a line chart, step by step
- Click the pivot chart. Changes apply only to the selected chart, so make sure its controls are active before you start.
- Open the chart type settings and choose a line type. Excel lists chart types for the whole chart, and a line type will apply to every series at once.
- Pick a plain line or a line with markers. Markers help when you have few data points, because each value stays visible.
- Check the legend and the horizontal axis. Switching type can change how categories are spaced, so confirm that the months or categories still appear in the right order.
- Select one series if you need a secondary axis. Click that series directly so only it is highlighted, then open its format settings.
- Assign the series to the secondary axis. A second vertical scale appears on the right. Add a short label to each scale so readers know which series it belongs to.
Switching the x and y axes
In a pivot chart, the horizontal axis usually shows the row field and the vertical axis shows the values. The simplest way to swap the two layouts is to change the chart from a column chart to a bar chart. Bar charts place categories down the vertical axis and values across the horizontal axis, which often reads better when category names are long.
If you need the fields themselves reorganized, use the field areas in the PivotTable Fields pane. Move the category field into the column area and the value field into the row area, or drag fields between the axis and legend areas on the chart. This changes the table's layout as well, so check the totals afterward.
Showing only the top 10 items
Apply the filter to the row field in the pivot table, not to the chart. Open the filter for that field, choose the value filter option, and select Top 10. Set it to show the top 10 items by the sum of the value field you want to rank by. The table and chart both update.
Pivot filters remove the other items completely. They don't create a single bar that adds up the remaining items. If readers need to see how much the excluded items contribute, add a total from the full data in a regular range and place it next to the chart.
Why grand totals don't appear in the chart
The pivot table can show grand totals, but the chart does not plot them. A grand total is usually much larger than any single category, so including it would flatten the rest of the chart. Pivot charts skip it on purpose.
To compare each category against the total, read the grand total from the table, or build a small regular range that lists each category with its share of the total. A chart built from that range can then show the shares.
Scatter plots: a workaround from a regular range
Pivot charts don't offer scatter types, so you can't turn a pivot chart into a scatter plot. A scatter plot needs two numeric columns, one for the horizontal position and one for the vertical position, and it needs them in a normal range.
Copy the values you need from the pivot table, paste them as values into a new area of the sheet, and label the two columns. You can also write formulas that point to the source data so the range updates on its own. Select the range, then insert a scatter chart from it. Keep in mind that pasted values won't change when the pivot table refreshes, so repaste them or check the formulas after the source data changes.
Check the result
Once the chart looks right, check each change against the table behind it.
| Check | How to see it | If it's wrong |
|---|---|---|
| Chart type | Look at the chart after the change | Select the chart and choose the type again |
| Top 10 filter | Count the categories on the chart and compare with the table | Confirm the filter sits on the row field and ranks by the correct value field |
| Secondary axis | Confirm a second scale appears and only one series uses it | Reselect that series and assign the axis again |
| Grand totals | Compare the table total with your own sum | Use the table or a regular range for the total |
| After a refresh | Refresh the pivot table and look at the chart again | Reapply any series settings the refresh reset |
If the workbook is going into a translated report, you can upload the .xlsx file on omnidoc.top for translation. OmniDoc keeps tables and formulas in Excel files, but chart labels and legend text may need a manual look after translation, so open the result and check them.
❓ FAQ
1. Is there a keyboard shortcut for creating a pivot chart?
This guide doesn't cover shortcuts, because key combinations vary by Excel version. Check your version's keyboard shortcut list, or use the chart tools with the keyboard to move through options.
2. Why does my chart still show every item after I set a top 10 filter?
The filter may be on a different field, or it may rank by a value field that you didn't intend. Open the pivot table, confirm which field carries the Top 10 filter, and refresh the table.
3. Will changing the chart type remove my pivot filters?
No. The chart type changes how the data is drawn, but the filters stay on the pivot table. If a filter seems to vanish, check the field buttons on the chart.
4. Can I show a grand total as a line on the chart?
Not from the pivot itself, because grand totals are not plotted. Build a regular range that includes the total as its own column, then chart that range.
📝 Summary
A pivot chart follows its pivot table, so most customizations happen in the chart type settings, the series settings and the table filters. For a scatter plot or a total on the chart, build a regular range and chart that instead. Check each change against the table after a refresh.
