If you’re working with a large amount of data, creating charts from pivot tables is an excellent way to visualize the information. Whether you’re looking at sales trends by region, or the amount of time a blog post has been published, a Pivot chart is a great way to show this data visually.
Creating charts from pivot tables is easy and straightforward. To begin, first select the data table that contains the information you want to chart. Once the table is selected, go to the Insert tab and click Pivot Chart on the ribbon.
Once the Pivot chart wizard appears, you can drag the fields from the table into FILTERS, LEGEND (SERIES), AXIS (CATEGORIES) and VALUES areas. You can also arrange the fields in these areas to create a custom chart that best fits your needs.
When you’re finished, the chart will appear on a new sheet. If you wish, you can also move the chart to another sheet by right-clicking the chart and clicking Move Chart.
To make the charts easier to read, you can choose a different scale for the data. This will reduce the number of bars on your chart and give the chart a clearer look.
The scales you choose for your chart depend on the type of data you’re viewing. For example, if you’re interested in sales by region, you may prefer to use a bar chart instead of a column chart.
Step 6: Organize Your Source Table
Before you can create your pivot chart, you must organize your source table into rows and columns. You can do this by adding unique headers to each column. These will later turn into the field names of your pivot table.
Once you’ve organized your data, convert it into an Excel table. The advantage of doing this is that your data range becomes “dynamic.” This means that as you add or delete entries to your table, the range for your pivot table will automatically change. This makes it a lot easier and less prone to errors if you make any changes to your data.
Alternatively, you can just copy the pivot chart and paste it into a different workbook temporarily. This can be useful if you need to make quick changes to your data and don’t want to have to create a whole new pivot table.
There are some limitations to using this method. The main one is that you can’t change the data source directly in a pivot chart, but if you watch the video below, you’ll learn how to do it.
How to Use a Named Range
If you’re using Excel 2013 or higher, the data for your pivot chart can be named. This means that you can easily select the chart’s range without having to click on the cell or row numbers.
This will make it easier to navigate and change the range for your pivot chart if you need to do so in the future. It’s also recommended if you want to be sure that your chart won’t be affected by dynamic functions, such as the RANDOM and TODAY functions, which can cause your data to change automatically.
