Use Excel to create advanced graphs and PivotCharts

Sometimes we take for granted what users know. While Microsoft Excel may be familiar to you, if you are on a help desk you will have to explain how the application works to users that fall within a vast range of skill level and knowledge. This series of Excel tutorials can make that training much simpler. This installment of the series shows you how to use the graphing component related to Excel's PivotTable feature: PivotCharts.

In the previous part of this series, you learned how to create and manipulate simple graphs in Excel. In this part, you'll delve a little deeper into graph creation and will also learn how to use the graphing component related to Excel's PivotTable feature: PivotCharts.

This data analysis series consists of these articles:

The sample

For many of the examples in this article, I will be using the data from the table shown in Figure A.

Figure A

A fairly typical data set provides a good place to demonstrate Excel's graphing abilities.

More data views

With a graph, the type of diagram you select is critical when it comes to getting your point across. For example, if you wanted to diagrammatically show how much of your monthly payment went toward rent, you would probably not use a line graph. Instead, a pie chart would be more appropriate since it shows you slices of a whole.

In the last article, we looked at line and pie charts. In this section, I'm going to walk you through one of Excel's pretty nifty graphing features: the 3-D bar chart. With 3-D charts, your chart manipulation options expand to allow you to actually rotate the chart to get exactly the view that you want.

For this example, I'd like to create a bar chart that you can manipulate beyond just changing the colors, text, and line types.

Remember the basic steps for creating a graph are to select the data that you want to appear on the graph and then click the Chart Wizard button. For this example, since I'm going to use all of the data on the graph, I can just click anywhere on the data table and click the Chart Wizard button.

In Step 1 of the wizard, I have selected to create a 3-D Column chart, as you can see in Figure B.

Figure B

The 3-D bar is a sub-type of a normal bar chart.

The second step of the wizard asks you to select your data range and to determine whether data is separated by columns or rows. For this example, I didn't need to select a range. For the series data, I chose "Rows" since I want the category to be selectable (Figure C). If I had chosen the columns option, the graph would be a little more difficult to manipulate. This will make more sense in a few minutes.

Figure C

The data range matches my selection, and I want the "Rows" data series.

On the next screen of the wizard, I have the opportunity to provide chart and data details. For this example, all I've done is to provide a title for the chart, as shown in Figure D.

Figure D

This step provides fields for better explanation of what's on your graph.

I'm not going to show a screenshot for Step 4. This is the step that asks where you want to place the new graphâ€"on its own tab, or on the same sheet as the data. I've opted to show the graph on its own tab in Excel. The resulting graph is shown below in Figure E.

Figure E

This isn't the best view of the data, but there's a good reason.

This graph isn't perfect. It's the right kind of graph, but the data isn't really the best for this kind of comparison. This is mainly because of the way the bars a lined up and because of the large range of values. The scale has to run from $40.00 all the way to $1,000.00, meaning that there isn't room to show minor variations in amounts. This graph would probably be more useful if the rent series was omitted, for example. After all, the rent isn't usually a variable payment (and neither is the normal car payment, for that matter), so placing it on a graph like this won't provide you with a whole lot of information.

But, since the data series is set up as row data, each color specifies a category. Even better, with a click of the mouse and a tap of the keyboard, I can eliminate a series. For example, if I want to get rid of the rent and car payment series, all I need to do is select one of the rent bars and hit the Delete key on the keyboard. The same is true for the car payment.

Take a look at Figure F, which omits the rent portion of the chart. Likewise, since the car payment amount never changes, I omitted that from the chart as well.

Figure F

With the removal of the two series, the graph looks quite a bit different.

This still isn't the best graph, but you'll quickly notice that the differences from month-to-month are much more pronounced now. This is largely because the scale now tops out at $250 rather than $1,000. I'm going to show you an interesting feature and then show you some ways that you can manipulate this graph to make it more readable.

Hold your mouse over one of the bars in the graph. A tool tip appears providing you with information about that data point. See Figure G.

Figure G

The tool tip provides with you all of the information related to the bar.

Advanced graph manipulation

Previously, I showed you how to perform some simple manipulation of your graph to be able to help you make it look attractive and better reinforce the point you're trying to make. Now, we will move on to graph manipulation. Manipulating a 3-D graph in Excel can be a little tricky, but once you do it, you'll probably find that it's pretty neat, and potentially very useful.

Basically, Excel gives you the ability to rotate a 3-D graph any way you like. To do so, you need to click somewhere on the graph "wall" after which you can manipulate the graph.

Figure H

The corner control is key to graph rotation capability.

Now, click one of the corner controls. You'll notice that your mouse pointer changes to a plus sign.

Figure I

It's a little hard to see in this shot, but that is the plus icon control.

Now, while holding down the left mouse button, drag one of the corner controls around the screen. Notice that your graph rotates as you move the controls. While you are dragging it around the screen, your graph appears to be a wire frame instead.

Figure J

The wire frame helps to conserve computing resources. It takes a lot of processing power to redraw the graph while you move it.

When you're done dragging, your graph looks completely different, and you can get different views on the data.


Up to this point, the graphing options I've shown you have been handcrafted. That is, you've had a data set, and decided what kind of graph to create, and then gone ahead and put together a graph that met your needs. If you need to change something about that graph, you need to look through a few screens of settings and find what you want to change. And, if you want to add or remove a series, or other data, it's not always as straightforward as it should be.

Previously, I went over the topic of PivotTables with you, and showed you how you can harness their flexibility to quickly and easily analyze your data or create useful reports. PivotCharts are the companion graphing tool for PivotTables.

The example screenshots I show you in this section will go back to using the budget spreadsheet I used in earlier parts of this series.

Figure K

The budget spreadsheet is a fairly typical layout and provides a good example.

Just like with a PivotTable, a PivotChart is started by placing your cursor somewhere inside your data table and choosing Data | PivotTable and PivotChart Report from the menu. This is where things change a bit. Under "What kind of report do you want to create", instead of selecting PivotTable, select the "PivotChart report" option. (See Figure L)

Figure L

This will give you a blank chart to start with.

The last two steps of the wizard are the same regardless of whether your just creating a PivotTable, or adding a chart. When you're done with the wizard, you'll see a screen similar to the one shown below in Figure M.

Figure M

Here's a look at your drawing board.

You might notice that this looks a lot like a blank PivotTable sheet and, like that sheet, this one has various places to which you drag fields to design your report any way you like. (See Figure N)

As an example, I'll do the following:

  • Place the Month field into the "Category" area.
  • Place the Department field into the "Series" area.
  • Place the Amount field into the "Data Items" area.

Figure N

With three clicks and drags, I was able to whip up this chart.

Just like with a normal chart, you can make all kinds of modifications to your PivotChart. Just about everything available with a normal chart is available here. For example, if you right-click one of the axes, you can format the text. You can even add the source data table by right-clicking the chart background and choosing Chart Options | Data Table | Show Data Table. Both of these modifications are shown in Figure O.

Figure O

Change any option you like!

One of the first things you might want to do is use a different type of chart. After all, a bar graph isn't always the best option. To change your graph type, from either the PivotTable toolbar or the standard toolbar, click the Chart Wizard button. Or, even easier, right-click the chart background and choose the Chart Type option (Figure P) from the resulting shortcut menu.

Figure P

You can change the PivotChart to any kind of chart you like.

For this example, I'll change the chart type to a 3-D pie chart. See the sample below in Figure Q. I've also moved the Department field to the Category slot, and removed the Month field completely. Why did I remove the month field? A pie chart can't break down both a category and a series at the same time. By leaving the month field, the pie chart was being based on just the first month's worth of data. By removing the field, I was able to get a pie chart that accurately reflected the total of each category for all months.

Figure Q

Here's a down-and-dirty pie chart.

Analysis tools

When it comes to graphs, Excel can easily provide you with some outstanding options, provided you don't mind a little trial and error. Between PivotTables, PivotCharts, sorting, filtering and subtotals, you should be able to analyze data in whatever way you like using Excel's built-in tools.

This also completes my Excel training series. However, if there is a topic that has not been addressed, please let me know and I will write it. My next training series takes on Word, and includes some Word/Excel integration topics.