Chapter 6 Spreadsheet Charts
Chapter Outline
Chart Basics
Creating your first chart
Creating a chart on this sheet.
Titles
Combination Chart
What’s Wrong With This Chart
Review of Menus
Chart Basics
|
Chart Basics 1. Pick the chart type that you want. 2. Know if your data series (Y-Axis
Values) is in rows or columns
(which is first data series). 3. Pick the titles for the Chart, X-Axis and
Y-Axis. Figure 1 |
Spreadsheets deal with numbers and charts display numbers graphically. The old adage a picture is worth a thousand words holds true when it comes to charts. You can look at a series of numbers and not see a thing. Put those same numbers in a chart and you can see a trend or a pattern or something you could not spot intuitively by just looking at the raw data.
Making charts from your spreadsheet data is as easy as one two three, most of the time. OF COURSE you will want to follow the chart basic guidelines as shown in Figure 1. Let’s also go over some chart terminology see Figure 2 so we know what we are talking about and then we can learn how to create one.
Title - is just a descriptive title to your chart, in this case Sales by Quarter.
X Axis - is the different categories of your data and is often called the category axis. In this case the different sales quarters. Some other examples of X Axis categories would be the actual sales people, grades, expenses, etc.
Y Axis - is the actual data values for each category and is often called the value axis. In this case the dollar amount of the sales for each quarter. Some other examples to match the X Axis samples would be the actual sales amounts, the number of people who received each grade, the actual amount of the expenses, etc.
Scale -
is the increments on the Y Axis. These are set automatically but they can also
be set manually.
Gridlines - are to help direct your eyes to the axis. In the sample, only Y-Axis gridlines are showing. X-axis gridlines would start from the X-axis and go up.
Legend - shows you what each data series stands for. If you count the items in your legend that is how many data series you have.
1st Data Series - is the first set of data values in your chart and corresponds to the 1st item in your legend. In Figure 2 it is VT and Figure 3 it would correspond to row 3.
2nd Data Series - is the 2nd set of data values in your chart and corresponds to the 2nd item in your legend. In Figure 2 it is NH and Figure 3 it would correspond to row 4.
1st X Category - is the first grouping of your data. In Figure 2 it is the 1st quarter or column B in Figure 3.
Let’s look at the actual spreadsheet data Figure 3 I used to create the chart in Figure 2.
Notice that VT is the 1st data series, NH is the 2nd data series and NY is the 3rd data series. These are your Y values. The 1st Quarter sales is your 1st X category.
Let’s look at the same data only charted just the opposite Figure 4. In this case the data series is in columns and the categories are in rows. Our first data series is column B, the 2nd data series is column C etc. Our first X category is in row 3 and the second is in row 4 etc. Both charts are correct only they emphasis different points. You have to keep in mind what point you are trying to make. Here we have sales by state instead of sales by quarter.
Creating your first chart
The good news is that when you create a chart chances are that it will be exactly what you want. The bad news is that if it does not look the way you want, it may be troublesome to fix. Most of the time (99%) the chart should be what you want. If you find you are spending twenty minutes on your chart just to get it to look its best, your best bet is to just start over. Keep in mind that chart making is a clicking thing, in other words you will use the mouse a lot.
Your
first step in creating the chart is to draw a rough sketch of how you think it
will look (you can draw it in your mind if you like). This will give you an
idea of the chart type that you want to use. Look at your data and get a
picture of what your 1st data series and your 1st x category are going to be.
Make a note as to whether your data series goes in a row or column.
The second step is to decide if you want the chart on the current sheet (kind of like a Clip-art picture) or on a new sheet altogether. I find it easier to put the chart on a new sheet.
Okay now for the actual chart, you need to highlight the data that you want to chart including the text labels. Let’s use your personal budget. It should look like Figure 5 after you highlight the range.
Now choose Insert, Chart... to start the chart wizard. The chart wizard is a series of four dialog boxes that step you through the process of creating a chart. You fill out the dialog boxes and presto, your chart is created! Usually you will be happy with what it creates automatically.
The first dialog box Figure 6 is to pick the chart type that you want to use. It is hard to say which is the best one since it all depends on what you are trying to say (i.e. your chart is trying to represent your numbers). The good news is that it is not hard to change the type to another one if you change your mind. You can experiment by clicking on the type of chart first and then Press and hold to view sample. Let me quickly give you a brief description of what each one is good for.
Bar/Column - charts are good for comparing both
categories and data series
Line - charts are good for showing trends and
fluctuations over time. Also good for showing predictions.
Pie - charts are good for getting the percentage
of how each category fits into the whole picture. You can only use one data
series for a pie chart.
XY Scatter - charts are good for showing a correlation
between your data points.
Area - charts are good for showing the total of all
the data series and the area difference between your data series.
Doughnut - charts are similar to a pie chart only with
a hole in the middle. Really good for Grandma’s Bake Shop.
Radar - charts are good for drawing spider webs.
Actually I don’t know what they are good for but I would guess they are similar
to line charts in that they can show trends or fluctuations.
3-D charts have the same usefulness as the 2-D counterparts.
Misc. Charts -
try them to see if you like them!
Simply
choose the one that you feel will best represent the point that you are trying
to make and go
on to step 2 of the chart wizard.
This step Figure 7 is where you tell the wizard whether your data series goes in rows or columns. The computer may have guessed correctly already. If not simply choose the correct option button and the sample preview will change. Leave the Series tab alone but feel free to peek at it. In this case I have one data series and ten X - categories.
Figure 8 shows you step 3 of the process. This is where you put the finishing touches on your chart. You add your titles for the chart, X axis and Y axis. You can also specify information about the Axis, Gridlines, Legend, Data Labels (shows the actual data value on the chart) and a Data Table (choose the check box as it is easier to see and understand why it is that way). You do not need to change all (or any of) these options. In this case I only have one data series so I am going to turn the legend option off. Keep in mind that you can always choose <Back and go back to any of the 4 steps.
The
last step of the process is to decide if you want the chart placed on a
separate sheet or on the worksheet. I said earlier, that I prefer to place the
chart on a separate sheet but this is just a matter of personal preference.
Pick whichever option you like best and presto you have a chart! Your chart
will be saved with the workbook. If you place the chart on its own sheet you
can name the chart sheet now or the same as you did the worksheets.
You can change your paper’s orientation to match the chart. If you look at this chart in Print Preview (Portrait) it seems out of proportion. However if you change the orientation to Landscape (mine was automatically in landscape) the chart just looks better. You can add headers and footers all the same as you did for sheets (File, Page Setup...). In fact under Page Setup you can have the chart automatically scaled to fit the paper size you have chosen. Let’s take a look at the chart for the moment in Figure 10.
Notice the resize handles. Well they really are not resize handles they only look like them. It does mean that we have the chart selected. You select the chart by single clicking it with your mouse. If you choose Format, Selected Chart Area... you will get the dialog box shown in Figure 11.
This
dialog box allows you to put a border around your chart as well as change the color
and pattern for the area around the chart. The best thing
for you to do is to try it and see what happens. You can always choose Edit, Undo. The font tab
is pretty self-explanatory as it is similar to the word processor and
spreadsheet. Let’s look at the chart again in Figure 12. Can you notice
that the highlight is different this time? That is because I clicked on a
different area this time.
This time if you choose Format you will have Selected Plot Area and you would get this dialog box Figure 13. This is how you can change the gray area in your chart. Simply choose None in the Area section.
You
should notice on the formula bar it tells you what part of the chart you have
highlighted. The corresponding menu option Format,
Selected ... changes to match. It is a matter of clicking and
looking until you do it so many times you do not think about it anymore. You
can change most anything that you want by clicking on the correct part, be it
the Axis, title, gridlines or data series. For the most part I usually just
double click on a part as this is a shortcut to the dialog box.
One of the handy dialog boxes deals with the Axis. You simply click on the X or the Y Axis and then you can Format Selected Axis. Looking at the dialog box Figure 16 let’s talk about some of the features that you can do. As always you should just try all of them to see what happens. Practice makes perfect.
Patterns, Font, Number and Alignment are all pretty self- explanatory or very
similar to stuff you have already done. Take a look and try them to see what
they do. I just want to point out the Scale. If you want to specify
the scale, all or part of it, this is the place to do it. You do not have to
start your scale (minimum) at zero you can start it
anywhere you want, positive or negative. You just turn the check box for Auto
off and type in the number. The same is true for the maximum number on your scale. The Major Unit will be where the numbers on the scale show up
at the specified interval. In this case every 50 be will listed on the scale.
The Minor Unit in this case is 10 and will show up as tick
marks on the scale. Now the above dialog box is all just the default settings
for my chart and I never saw the minor tick marks. So what’s the deal? Well you
need to look at Patterns and you will see the Tick Mark Type for the Minor Units is set to none. So if you want the minor
tick marks to show up you need to turn the option on.
If you click on one of the data series (for example: click on the bar) and choose Format, Selected Data Series you will get the dialog box Figure 15. The Patterns tab is how you could customize the pattern or color for that bar (or series). Once you look at that tab it is self-explanatory. The Data Labels tab enables you to show data labels for this series. Data labels are the actual numerical value for that point on the chart. You should have noticed the numerical values just on top of the columns Figure 12.
Creating a chart on this sheet.
I have explained how to create a chart on its own sheet. You can create your chart as an object directly on the worksheet. Take a peek at Figure 16, it is really pretty easy to do. In the last step of the wizard you simply choose the option As Object in: and this will place the chart on the current spreadsheet. You would resize the chart just like you would a clip-art picture. Use caution because if you click and drag the chart to move it you may move the actual chart inside of the picture box. When you do it, you will know just what I mean.
Looks pretty sharp doesn’t it? We can see how each expense relates to the others. Oh, did I tell you that when you change a number in the spreadsheet it will automatically change in the chart as well.
When you want to make a change to the chart, like we described above, you will just need to double click the chart object that you want to change. This will give you the correct dialog box to work with. When I first made this chart some of the X category labels were on top of each other. To fix this I simply clicked on one of the categories and dragged it away a little. You may have to click a couple times until you get it. Single click only. What you want is to have a highlight around just the one category that you want to move.
I hope you have realized that when you are dealing with charts it is a matter of clicking on everything and seeing what you can do with it. You can consider each piece of the chart an object that you can format individually from all the other objects.
Titles
If you forgot to add any titles to your chart when you were doing the chart wizard you still can get them on the chart. Simply choose Chart, Chart Options..., and you can then fill out the dialog box the way you want. When you choose OK you will be given an object on the chart that you can format any way you want.
Combination Chart
There are so many chart types that it is really hard to give you a good example of all of them. I suggest trying all the different types to see how they look. Remember to choose the chart type that best portrays your point.
There is one more chart example that I do want to give and that is a combination column and line chart that I find useful on occasion. Given the spreadsheet in Figure 17, notice the added Average row.
I want to graph the sales quarters on the X axis and have the states as my data series (Y axis). This was the chart I made earlier in the chapter. Well I want to see the average sales for all the states on the same chart. I would start by highlighting A2:E5 (remember we don’t want the totals). Since I want to also add the average data series I am going to hold the Ctrl key down while I highlight A7:E7. Holding the Ctrl key down will allow me to highlight a non consecutive range. Now I will go ahead and proceed to mark a column chart as I normally would.
After I make the column chart I am going to change the average bar to a line. I start by selecting the average column Figure 18. Since the average data series is selected, that is the data series that I can change.
I simply choose Chart, Chart Type... and choose a line chart. Be sure to specify the check box Apply to selection (it should be already). Pretty easy. Your chart will now look something Figure 19.
I like the way it shows how each state compares to the average, either above or below for that quarter. Again I suggest playing around with the different types to see what works for you. Remember if you click on one data series you can change just that series by specifying Apply to selection. If you want to change the whole chart, start by selecting the whole chart. Happy charting. Don’t forget if you spend to much time it may be easier just to start over.
What’s Wrong With This Chart
Looking at the chart in Figure 20 what is wrong with it? Technically nothing is wrong. However I have plotted the totals for the states on the same chart. This throws the proportions off by belittling the sales for each state. My personal opinion is that the totals should be on a separate chart altogether. On the chart below Figure 21 I have taken the totals off. You decide which you like better. It depends on what are you trying to say with your chart.
After all I may not want to use a column chart but possibly an Area chart like shown in Figure 22. The Area chart does show the total for each quarter by stacking the series on top of each other. The top line represents the total for all three states.
The biggest point to remember is to just keep trying the different charts to see what you like best. In the above examples I created the chart once and then just copied the chart to a new sheet (Edit, Move or Copy Sheet..., Create a Copy), just like you copied worksheets. This way I was able to look at the different charts and pick the one I liked best. I then simply deleted the extra chart sheets.
Looking at Figure 23 it shows a case where the blank column has messed up our pie chart. Remember that when you create a pie chart it only uses the first data series, which is column B in this sample. Let’s look at how a blank data series would look in a column chart.
It
may not be evident right away but notice that the legend has 2 data series. You
could delete the legend and who would know? I would and it would be marked
wrong. If you look at the bars themselves they are not centered between the
tick marks on the X axis. This is the indication that you have a blank data
series.
Not a problem, you can fix it. You could go back to the spreadsheet and get rid of the blank column and simply redo your chart.
Review of Menus
Menu Pull Down Menu Key Board Toolbar
Insert
Chart
As New Sheet
On This Sheet 
Format
Selected Chart Area
Selected Plot Area
Selected Axis
Patterns
Scale
Font
Number
Alignment
Selected Data Series
Patterns
Chart, Chart Type
Selected Series
Edit
Move Copy Sheet