Visualizing Data with PivotChart: Part 1

In today's world, Excel is used in almost all industries, whether it be financial services, IT services, automotive, manufacturing, or many others. Excel deals with large amounts of data and finds insights in it. Without summarizing the information in a chart, understanding can become quite hectic. The need for visualization tools in Excel is undeniable.

In a previous guide, we learned about various aspects of PivotTables with very little emphasis on Pivot Charts. In this series of two guides (beginner and advanced) on Pivot Charts, we will further explore the topic and visualize data using Pivot Charts.

In this beginner guide, Part 1, we will cover the following plots:

• Bar charts
• Line charts
• Scatter plots

In Part 2, we will cover more advanced charts, such as stock, treemap, histogram, box and whisker, and combo charts.

We will be using a dummy data set consisting of data related to colleges in various countries, their fee structures, and their minimum acceptable test scores (GRE and GMAT). Given below is the original data set, which holds eight columns and 10 rows:

We will be building various Pivot Charts based on the given data set.

Note: The above data set doesn't relate to actual data for the mentioned colleges. Please refer to the respective college websites for original information.

To build a pivot chart, the first thing you need to do is to construct a PivotTable. Here we have constructed a PivotTable using column names Country and Program under the Rows section and the sum of the tuition fee, accommodation fee, and miscellaneous charges under the Values section.

Next, to build any of the Pivot Charts on the given PivotTable, you first need to select those columns you need to visualize, then in the menu bar click on Insert, and then click on the PivotChart icon. This will popup a new dialog box with the name Insert Chart. Now, you can select any of the given charts to plot your selected data.

If you are dealing with numerical data, then bar charts are the first visualization tool that comes to mind. There are a variety of bar charts that can help you summarize numerical data and show you its relative importance. To get a brief understanding of how bar charts work, you can visit the bar chart webpage on the Investopedia website.

We will now learn how to make a variety of bar charts on the PivotTable described in the above section. If you are in the Insert Chart popup dialog box, you can click on the Bar tab, which will show you six types of bar charts. There are really three main kinds of these charts, namely, clustered, stacked, and 3D charts; the remaining are merely a combination of these three charts with the inclusion of a hundred percent stacked theme.

You can use any of the above three charts or the remaining three charts to analyze the given PivotTable data. As you can observe, the bar charts make it very clear as to which program in which country has the highest and the lowest fee structure in terms of tuition, accommodation, and miscellaneous charges.

You can also control the Chart Elements, including Axes, Axis Title, Chart Title, Data Labels, Data Table, Gridlines, and Legend, using the + sign available outside the chart.

A line chart can be used to plot either a single or multiple numerical data columns. You can explore more about line charts and their use on the Investopedia line chart web page.

To learn how to make a line chart in Excel's PivotTable, we will be using the same PivotTable as above and all of the three numerical data types, i.e., tuition fee, accommodation fee, and miscellaneous charges.

To create a line chart from multiple numerical data columns, you need to first select the data from the complete PivotTable and follow the same procedure as during the bar chart creation. The only difference is that you need to select line instead of bar inside the Insert Chart dialogue box. Inside the line section, you will observe seven types of available line charts. In this example, we create a line chart with markers, as shown below:

A scatter plot can be used to show a relationship between two numerical quantities or one numerical and one non-numerical quantity. For a brief overview, you can visit this webpage on the Statisticshowto website.

Sadly, we cannot create a scatter plot on a PivotTable directly using its data. However, there is no restriction on copying the data from a PivotTable and then using it to create a scatter plot. In this section, we will learn how to create a basic scatter plot based on one numerical quantity and one non-numerical quantity.

First of all, we create a new PivotTable using the College column under the Rows section and the Min GMAT Score column under the Values section. We filter the result based on the Program column and further select only the Master's (Biz). Once we are done with this process, we have a PivotTable as shown below:

Program Master's (Biz)

The follow the given steps to create a scatter plot on this PivotTable data:

1. Copy the text and the GMAT score data of these four universities and paste them in two new blank columns somewhere in the same sheet.

1. Next, click on a new empty cell and go to the Insert tab under the menu bar. Locate the Charts section, which is available just before the PivotChart section. Click on the scatter plot icon. This will bring an empty scatter plot on the worksheet.

2. Moving ahead, right-click on the empty scatter plot and click on Select Data.... This will open up a new dialogue box as shown below:

1. Click on the Add button, which opens up a new dialog box where you can input the series name and its X and Y data values. I have used Master's (Biz) as the series name, but you can select anything you want. Now select the college names in the series X data and select the GMAT score as part of the Y data. For me, the completed dialog box looks like this:

1. Now press OK a few times and after a bit of tweaking the Chart Elements, you can have a scatter plot like this:

In this guide, you have learned how to take your first steps in building Pivot Charts. You have observed that there are some restrictions on building specific charts directly using PivotTable data; however, you also learned a bypass way to still visualize such data. In Part 2, we will learn to create more advanced charts including stock, treemap, histogram, box and whisker, and combo charts.

• Calculating Cell Values With Formulas
• Working with Statistics Functions in Excel
• Working with Finance Functions in Excel