With the new 2016 release, Microsoft Excel provides its enthusiastic users as many as 15+ chart variations. But the pivot chart in excel remains a solid favorite with students, home users and business professionals alike. This is that single chart that can pack in a fair bit of information without overwhelming the reader. In addition, it can be built on a pivot table and combined with almost every other chart type, making it one enormously flexible tool for data analysis!
In this post, we show you how you can build your first pivot chart in 10 easy steps.
Step 1: Prepare your data in a tabular format
The first step is to tabularize your raw data into even rows and columns. It is best to work this chart on a table with at least 2-3 columns, so the pivot chart has enough columns fields to project meaningful statistics. The pivot chart in excel works with null figures too; but for a balanced chart, do ensure that you have no null values in key columns. Also make sure your data is clean, with no duplicate values.
For our example, we will create a pivot chart with data from a mid-scale publisher, to capture sales statistics figures across popular periodicals.
The table will contain 3 field columns: the name of the Journal, the Month of publication, and the total number of Sales (in hundreds). The combination of (Journal + Month) will uniquely identify a row in the table. Further, care has been taken to ensure that there are no null values for any row. The table data has also been sorted in order of month, but this is not a necessity.
- Prepare similar data in a fresh sheet in Excel, and name the containing worksheet as-as “Raw Data”.
Step 2: Create a Pivot Table for the Data
A pivot table ideally holds the data that feeds into a pivot chart in excel. Do note – at this point, you can directly create the pivot chart referencing the prepared raw data. Excel will implicitly create a pivot table and a pivot chart. But this can overwhelm new users as the resulting chart will have too many parameters to manage.
For easier understanding, we will explicitly create a pivot table, and then use it to feed a pivot chart in excel. With this, you will be able to easily understand the in-between steps and better manage the data parameters that feed into the chart. Once you become an expert, you can create the pivot chart directly from raw data.
To create the pivot table,
- Click on the Insert tab in the main ribbon
- Click on pivot table drop-down button, and select pivot table
- Here, you can feed in data through any of the following options:
- Manually select the range of cells that make up the raw data. The cells will get highlighted and their table positions will be captured (as shown in the picture above). OR,
- Use an external data source (like a Microsoft Access Database connection, or any other data source connection).
We will choose the first option by manually selecting the cells in the table created in Step-1. Be sure to include all rows and columns of the created table data.
- Select the “New Worksheet” checkbox, so the required pivot table is created in a fresh Excel sheet, for better clarity. Excel will create the pivot table in a new Worksheet. Name this “Pivot table”.
Step 3: Get Familiar with the Pivot Table parameters
In this step, there are no explicit actions. But it is a marker to help you understand the different parameters of the Pivot Table.
Once the pivot table is created, click anywhere on the table to reveal 2 tabs:
- The actual pivot table displayed on the left-hand side of the worksheet. Only an empty template is displayed when you first create the table. Once the table’s parameters are defined and customized, the table is populated with values from the raw data provided.
- Field list, containing the columns you want to populate into the table. This is further divided into:
- Pivot table fields: By default, this will contain all the column fields available in the raw data selected.
- Row labels and Column labels: This will determine the raw-data fields (rows and columns) that are displayed in the pivot table. (So a column field can feed into the table statistics by selecting in (a) above, but it will remain hidden from view until it is explicitly chosen in the Column label.)
- Report filter: This is to set any restrictions to filter out rows from the raw data.
- Values: This will determine the statistical data computed by the pivot table.
Together, this screen marks the “PivotTable Tools” view. Any time you want to alter or refresh your pivot table, return this view. In further steps, we will learn to customize the pivot table created so it reveals figures relevant to our raw data.
Step 4: Choose the Column fields applicable to the pivot table
By default, all the columns fields selected in the raw data are available for use in a pivot table. However, they must be explicitly selected to apply to the newly created pivot table. We will keep it simple by checking all columns of our raw data under the “PivotTable Field List” tab.
Step 5: Customize the Pivot Table Fields
The order in which you select the column fields for the pivot table determines the order in which they are added to the table. This, in turn, controls the Row labels and Column labels and affects the final values computed in the pivot table. So as per the order selected in Step 4, (1) Journal 2) Month and 3) Sales), the table will display the total Sales for every Journal, grouped by Month.
- Now, let us uncheck all the boxes and try this revised order: 1) Month 2) Journal 3) Sales. This will display the total Sales for every Month, grouped by Journal.
As you can see, the order of selection has a significant impact on the way the pivot table is rendered. For our example, we will retain the order captured in Step 4.
Step 6: Customize the Pivot Table Values
By default, MS Excel uses the SUM function to calculate the values rendered in the table.
- For instance, let us consider that you only add 2 column fields, in the following order: 1) Month 2) Sales. This will automatically display the total SUM of Sales for each month.
Now consider that you want to display the total number of publications that were sold every month, across all journals. You can render this by using the COUNT function as follows:
- Click on the “Values” tab of the Pivot table worksheet view. By default, this is set to the SUM function.
- Click on the drop-down box and select “Value Field Settings…” and select the COUNT function in the resulting list box.
- The table will compute the total count of sales each month (indicating the number of publications sold), as listed below.
Step 7: Create a Chart to project the Pivot Table
Now that we have a fully functional pivot table, any chart-type that projects this table serves as an intermittent pivot chart in excel. In our example, we will create a basic 3D-Column chart to project our pivot table.
- Click on the pivot table created in Step 5 and then click on the Insert tab.
- Click on the Column drop-down menu and select 3D-Column.
This will automatically project your chart into a column graph.
A few things to note here:
- The chart name is based on the Value field column. So here, it is the Sum of SALES.
- The chart takes the pivot table’s field columns. Here, columns 1) Journal 2) Month and 3) Sales were chosen to create the displayed chart. Together, these parameters capture the Journal-wise projection of Sales data, broken-down by month.
And voila, you have created your first (intermittent) pivot-table based chart! Well done! J
Do note, you can vary the charts you create on the pivot table by experimenting with the different chart types provided by Excel under Insert Tab – Charts. In each case, the chart is a projection of the pivot table data and hence serves as a pivot chart in excel. In further steps, you will learn how to create and customize a pivot chart directly from the input raw data.
Step 8: Create a Pivot Chart
Now that you’re familiar with a pivot table and the charts projected of its data, we will skip steps 2-7 and see how we can directly project our raw-data into a pivot chart. Yes, no table, no intermittent chart, we create a pivot chart from the raw data, straight from Step 1! You now have the knowledge to understand and customize its parameters right, so the pivot chart is clean and relevant.
To create a pivot chart,
- Select the cells that contain your raw data, as created in Step 1.
- Click on the Insert tab, and click on the “PivotTable” drop-down menu. Select “PivotChart”.
- Create the Pivot Chart just as you created the Pivot Table in Step 2, going with the default settings and clicking on Ok.
- The pivot chart gets created as displayed below. Name this worksheet as “Pivot Chart”.
A few things to note:
- The view that opens up when you click on the Pivot Chart area is the “PivotChart Tools” View. Now, this is a very familiar view, as this combines the sum of Steps 2-7. When you click on the cells containing the pivot table, this view becomes available. (Note: This view further allows you to customize the pivot table preferences, but we will go with the default settings for our example.)
- By default, Excel renders a Column chart, much like what we explicitly created in Step 7. You can change this effortlessly using the “Change Chart Type” button in the Design tab (as highlighted in the pic above).
In further steps, we will customize this pivot chart in excel to render the final data chart we desire. J
Step 9: Parameterize the Pivot Chart
Okay then, we will quickly summarize the steps applied in the earlier chart to our newly created pivot chart.
- Click on the cells containing the pivot table to access the “PivotTable Field List” view on the right-hand side.
- Check Journal, Month and Sales, in order. This will project Journal-wise sales data broken down by month.
- Retain the value field to the default setting: Sum of SALES.
And the pivot chart now is now complete!
Step 10: Customize the Pivot Chart in excel
In this final step, we will apply 3 customizations to our created pivot chart in excel, so you understand the range of alterations you can make to it with a single mouse-click.
Customize the chart area:
- The default pivot chart gets rendered with blue column lines. But what if you desire a more dramatic display? This is possible using the various “Chart Styles” available in the “PivotChart Tools” ribbon.
- Explore the chart styles available on your system and choose an option with a color-scheme and display that suits your preferences.
- And…. the pivot chart’s display instantly alters as per the new suiting, colorful and glamorous! J
Customize the chart type – 1:
The default column graph serves most chart needs. But what if your data is not best suited to this chart type? For instance, our data projects sales for 8 Journals, across 3 months, totaling 24 column bars in total. Perhaps this is more legible when projected as horizontal lines as opposed to columns? You can test this with a single mouse-click, as captured below.
- Click on “Change Chart Type,” the left-most button available in the “PivotChart Tools” view.
- The displayed list-box shows chart types supported in your version of Microsoft Excel. Regardless of the chart-type you choose, the current settings will continue (like the input data fields, the value field, etc.)
- Select the “Bar” chart type and click Ok. The altered chart display is shown below.
As you can see, this is a tad more readable. J
Customize the chart type – 2:
In our final example, let us consider that you want to explore the total market share each publication has. You no longer care about the month-wise breakdown, but only the total sales figures.
- The first step is to uncheck all chart column fields, and reselect only 2 columns in order: 1) Journal and 2) Sales. The resulting table looks like this.
- Next, the bar or column charts are not the best suited for this data. So click on “Change Chart Type” command button, scroll down the displayed list-box, and select the Pie chart. The resultant pivot chart in excel looks as displayed below:
As you can see, this is a finer representation of your data, than the bar and line graphs. You can also explore the “Chart Layouts” and “Chart Styles” commands to alter the visual display as per your requirements.
The Closing Note…
The Pivot Chart in excel is essentially any chart that is rendered from a pivot table, with defined field columns and values. In this post, you have learned the finer nuances of this chart so no matter which chart type you choose, you are able to effectively render the chart to suit your data.
Do look for further posts on the different types of charts available in Excel for advanced knowledge.
This has been a guide to Pivot chart in excel remains a solid favorite with students, home users and business professionals alike. These are the following external link related to pivot chart in excel.