Lesson 9 - Charts

Throughout this course we’ve been following the pattern of a relatively common workbook progression, from determining your needs and developing an initial layout, all the way through adding data, formulas and formatting, and preparing for distribution. The last lesson took something of a step back from the perspective of already having completed the essential elements of a workbook, then coming back to add graphical elements to make your points stand out. This lesson will continue on that theme, because Charts generally come at the very end of a workbook’s development; once you have your functionality and your data in place, then it’s time to start creating snapshots of it. This is exactly what charts do: they are a graphical representation of your worksheet data. Not everyone can look at a series of numbers and immediately identify a trend or pattern. Most people could probably see some of the same things given some time, but using Charts can help people come to those conclusions faster. You have a story to tell with your numbers and Charts are a fantastic way to help you tell your story better. Unfortunately, Charts are also one of the most inefficiently used tools in Excel. It’s not because Charts are inherently difficult to build, in fact they’re remarkably easy. The issue lies largely in that people don’t know how to properly build them. Building a chart is one of the most time consuming tasks there is in Excel, and there are admittedly quite a few elements in a chart to change, let alone multiple charts. The mistake people make is building the same chart and making the same changes over and over again, not realizing that it only needs to be built once and can be reused in multiple workbooks.

In this lesson we’ll be discussing the broad theory of charts, what kind of charts there are and when you might use them, followed by building one chart from each of Excel’s six standard Chart categories, and showing you how to save each one of those chart types as a favorite that you can use again and again. While we do that we’ll also cover some of the key tools that you can use to turn plain old boring Excel charts into something that people will think came from the Marketing department. Who says data has to be boring?

When do you use Charts?

Hypothetically, you can use a chart any time you think that a graphical representation of figures would tell a better story faster than the figures themselves, or if a chart would simply support the figures better than they could on their own. From a more practical standpoint charts do have a time and a place though. While charts can tell a great story, you should know that they merely augment the underlying data. It’s not hard to overwhelm a workbook with too many charts that only display minute differences between each other, or have little or no relevance to the subject. By the same token you can also not include enough charts to get your point across. It’s all a balance of what’s most appropriate for the audience, and fortunately, the only judge of that is you. Just as important as the content you chart is how to place your charts both in relation to the worksheet and other charts; while certain circumstances might dictate a particular format, it’s generally going to be up to you. Some people prefer to intersperse charts with data, others keep data hidden and only show charts, some people prefer charts sheets (a worksheet that Excel dedicates for single charts), and some people prefer “Dashboard” type views in which charts are clustered together with or without supporting data. Regardless of the format you choose you also need to hold yourself to providing a layout that’s as user friendly as possible. If you overwhelm your audience with charts thrown all over the place then the valuable information that they can impart is lost.

 

 

Figure 336  

Charting Rules of Thumb

The most important thing to remember when working with charts is to make sure that your data structure can support them. Many times data that does not follow proper spreadsheet design conventions simply can’t be charted (without a lot of work). In general your data needs to be in a Row/Column format with consistently formatted and labeled data. This means that data types can’t be mixed within the same column or row (e.g. mixing currency and dates in the same recordset). Merged cells can also cause problems with charting. Note that in this example the Top Left Cell in the data range has been excluded, and isn’t part of the Row or Column Headers. This empty cell indicates to Excel that you’re using this data for charting and that the first row and columns should be kept separate from the data. Sound charting structure has all of these elements, as well as separation between any other data. You can have multiple contiguous chart ranges, but you should have at least one blank row/column between the different datasets, otherwise you can unintentionally chart additional ranges that you might not have intended. As you’ve seen throughout this course, so many things in Excel rely on having a properly designed worksheet that it’s better to get it right from the start. Fortunately, once you get in the habit of building worksheets correctly, it becomes second nature.

Chart Terminology/Components

This is just a general overview of the different elements that make up a standard chart, and is by no means all inclusive.

• • Plot Area – The only absolutely necessary component of any chart is the actual data itself. Charts do not need any of the secondary descriptive elements, like Chart Title, Legends, or Axis Labels, but it certainly helps. Especially if you have several charts where it’s important to be able to quickly note differences between them.
• • Horizontal & Vertical Axes/Labels/Titles – These are automatically added based on the information that you have in your Column Headers and data range. For the Vertical Axis, Excel will analyze your data and determine the appropriate data range to display with regards to the high and low data values. Excel will do everything it can to display Horizontal labels as true to form as possible, but many times Excel is forced to cram everything together, at which point you choose a different scale to display (we’ll discuss that later). In addition to the data labels, you can also add data titles. In the following example you might use “Revenue” for the Vertical Axis Title and “Months” for the Horizontal Axis Title.
• • X-Axis/Y-Axis –Horizontal vs. Vertical Axes
• • Legend – This is a summary of your Row headers. Legend entries will be colored coded to their chart equivalents. So in the following example, the East Region in the Legend is blue, corresponding to the first column of data, which is also blue.
• • Chart Title – Self-Explanatory. If you have a title row for your data, you can link that to the Chart Title via formula so it doesn’t have to be entered by hand.
• • Data Table – These chart examples display a data table, which is just an option of including the chart data with the chart. It’s not necessary by any means; it’s just one of the many Chart Layout options you have available to you.

 

Figure 337  

• • This next example displays those Chart elements in relation to the data that’s being charted.

 

Figure 338  

• • Columns vs. Data – As you can see in the column chart examples the first row of data (East) is represented in the first column in each period, followed by the second and so on (a Bar chart would be similar, as it’s just a Column chart laid on its side). A Line chart, on the other hand, would not reflect the data based on the header position, but the first numerical value, which in this case is the West region with $8,000, the lowest value.

 

Figure 339  

• • Chart Area Visualizations – Remember in the Function/Formula lesson you saw how editing a formula will cause Excel to highlight all of the Formula elements in both the Formula and referenced ranges? Charts act in a similar fashion when you select the chart’s Plot Area. Provided your data is within view of the Chart, you’ll see the Chart Data area highlighted in different sections. The previous chart example showed the Row Headings outlined in Green, the Column Headings in Purple, and the data in Blue. In addition, when the chart data is highlighted, you can use the drag category handles to decrease or expand the current chart area. Excel will only let you resize the chart elements in ways that it can actually chart, so don’t be surprised that you can’t drag in every direction.
• • Formatting - Note how Excel carries your cell formatting over to your chart. If you were to change the date or number format in the data it would automatically reflect in the chart output. E.G. if you were to format the data range as Currency with two decimal points, the chart data and legend would display in the same format. In general, you want to keep chart data as concise as possible, limiting extraneous data like decimals unless it’s absolutely necessary. Sometimes little things like that can add up and clutter a chart more than it’s worth.

Getting Started with Charts

While we reviewed chart types and some of the basics when we discussed the Ribbon elements, we’re going to go over them again here. However this time we’re also going to get behind all of the specifics, starting with selecting the dataset, the chart type, formatting it, and the chart’s final placement in relation to the worksheet and any other charts. Before you even draw a chart you should have a general idea of what you want the data to say, where the data is, and where you want the chart to be (the current worksheet, a different sheet, or a chart sheet). Once you’ve done that it’s time to select your data; if your data is contiguous you only need to choose a single cell in the data range, provided you want to chart the entire range, otherwise you’ll need to select just the range you want to chart, or the different chart elements individually. In the following example you’ll see a contiguous data range that is only using a portion of the data for the chart. In this case the data range needs to be selected manually. If not, Excel will try to chart the entire range, which may or may not be too bad, depending on the outcome you expect/want.

 

Figure 340  

• • Next decide on a preliminary chart type. As we’ve reviewed, there are six primary categories of charts, as well as some more obscure chart types that have more specific uses.

 

Figure 341  

All of the following examples are included in Lesson 9’s companion workbook.

There will be data for each, the initial unformatted chart, a formatted chart, and several other examples.

Column & Bar Charts – These display your data in vertical or horizontal columns. Both are especially good at displaying multiple data points for several groups, such as Revenue, Expense and Profit by company. Because they are almost identical, we’re only going to review Column charts here, but there is a Bar chart example in the companion workbook.

 

Figure 342  

• • The most common are column charts are the 2-D & 3-D style - the only difference between the two is the additional illustration to give the 3-D column a 3-Dimensional feel. 3-D charts can be difficult to use with a lot of data though, because the additional illustration area takes up needed space. Unless it’s a must have design, then if possible, stick to 2-D chart types (in all styles). There should be enough additional formatting options to make up for it. With regards to the other styles, sometimes a Cylinder chart is appropriate, but rarely. And to reiterate from our earlier chart introductions, Cone & Pyramid shapes should only be used as a last resort, because they don’t always accurately represent your data.
• • In the following examples we’ll walk through how to set up a column chart, as well as deal with a data range that is too big to plot accurately. In this case we’re not going to define the data range, but allow Excel to choose it. And, as you can see, it’s pretty obvious that Excel just tried to bite off more than it could chew. All we really wanted plotted was the Customer name and the financials associated with each. But Excel gave us the whole thing. You could easily go back and just select that particular data, in which case the chart would turn out the way that you wanted it. Or there is another alternative, which will work with all of your charts; however it won’t necessarily fix things if you are going to base multiple charts off of this same range.

 

Figure 343  

• • What Plots and What Doesn’t?
• • Excel’s default behavior is to plot only visible ranges. This means that you can hide rows and columns and as you do, the corresponding data in the chart will be hidden as well. Given the example data range you could just hide the first 4 columns and you would have the display you want as a result. The Hidden Range method works great if you are only going to be basing one chart off of a particular data range, or if you will have multiple charts that can share the same visible range. If you have multiple charts that are using both hidden and displayed data then you’ll probably need to select the specific ranges for each.

 

Figure 344  

• • Create - Now that you know a bit more about charts and their data, we’re going to build a Column chart. We’ll use the same data set as in the last example, but this time only selecting the data we want plotted (company & financial data). Next go to the Insert tab, Chart, Select the Chart type and style and then let Excel do its thing.
• • Inspect – Now that Excel has created your chart, it’s time to ensure that it’s correct. Similar to validating a workbook for accuracy, you need to do the same with charts. While it’s generally easier to spot errors in charts, because of their visual nature, it shouldn’t be taken for granted. Is your data properly plotted? Are your data points properly represented for the period axis (horizontal)? What about the vertical axis distribution? Is your Legend correct? Is your chart an accurate representation of your data? Are you happy with the chart type or would it tell a story better as something different? Now is the time to address changes before you invest too much time in it. As long as the underlying data is in good shape, then most of the time your charts will come out very close to what you need.
• • Design - If you don’t like any of your major chart elements, now is the best time to change them. And this doesn’t mean something is wrong at all with what you’ve got, you might just want to see what the chart looks like 3-D vs. 2-D, or maybe with the way your data is laid out that a Bar chart might look better than a Column chart. This is where your major formatting takes place, so whatever it might be, go ahead and change it now. All of your options are on the Chart Tools Ribbon in the Design Group.
• • Chart Type and Style - simply look back through all of your options and choose the one you like better. Unfortunately, Chart Type isn’t supported by Live Preview yet, so if you change one of the Type options, you’ll need to apply it to see if it’s what you want, and recall the Change Chart dialog again if it’s not. It’s not hard to see when a chart type might not be the right choice for your data, because it’s going to look visibly awkward, as this example of our current company data displayed as a Line chart indicates. The horizontal data points indicated in the Legend aren’t following a timeline, so they shouldn’t be in a continuous line since one point isn’t related to the next. E.G. each line (Quantity, Revenue, etc.) shows a relationship between corporations when there isn’t one. IBM and CitiGroup’s Revenue shares no common link, so it shouldn’t be displayed that way.

 

Figure 345  

• • Switch the Rows/Columns - in case the data might look better the other way around.

 

Figure 346  

• • Layout – Maybe you want to add a data table.

 

Figure 347  

• • Styles – Or you didn’t like the columns that Microsoft picked.

 

Figure 348  

• • Move Chart – Maybe you decide that the chart(s) would look better on their own sheet, so you move it/them.

 

Figure 349  

• • Here are before and after shots of the default Column chart that Excel created, and the same chart a few mouse clicks later with some formatting adjustments. It’s nothing ground-breaking, but you’ll quickly see how easy it is to change the default chart formats. Throughout this course we’ve continued to mention all of the great work that Microsoft has done in adding a plethora of graphical options to almost everything you can do in Excel (and all of Office for that matter). But by the same token you’ve also seen that it’s impossible to please everyone, so they do their best and default to the basics, letting you create your own options if you want. Charts are no exception, so one thing we’ll focus on is not only how to create a general chart theme(s) that you like, we’ll also show you how to make Excel use your favorite chart themes instead of its defaults. And nothing says you have to limit yourself to one theme, in fact you can have several. You might want a very professional style for your company financials, but a more relaxed style for displaying sales performance to your team. You can do whatever you want; you just need to create the themes.

 

Figure 350  

• • Changing a Chart’s Display Units – Many times when you create a chart you’re dealing with rather large numerical values which take up too much space on the chart. In the past the only way to change this was by creating a false data set that reduced these values, usually by dividing them by a factor of 10. Fortunately, Microsoft realized this and has created a function to automatically reduce those values by the scale you determine. Here is our Column chart example with a realistic data set involving revenues and costs in the millions of dollars. You can see how the Y-Axis labels reduce the overall size of the chart data that can be represented.

 

Figure 351  

• • All you need to do to rectify this situation is right-click on the y-Axis label area and select Format Axis and then select the scale that’s appropriate for your particular values. The change is dramatic, and it’s significantly easier than having to alter your data to accommodate the scale change.

 

Figure 352  

Line Charts – Probably as common as Column charts, Line charts will display your data in a series of horizontal lines. Line charts are generally used to show data over a given period, like weeks or months. With a line chart the Horizontal (X) Axis can only be a Category (text), like the corporations in the Column chart example, or Dates that are all evenly spaced across the axis (the chart will automatically space the date equally regardless of the data’s spacing).

 

Figure 353  

• • Create - There is no difference in creating a Line chart (or any other chart for that matter). Select your chart range, chart type and have at it. Once you’ve gotten the initial chart drawn and your design elements selected, it’s time to move on to the Layout phase, which is the second step in creating your charting masterpiece. You’re encouraged to work with the companion examples as we go along and change them as you see fit, because just as Microsoft can’t please everyone, it’s quite likely that your style will be a bit different than the examples given.
• • Default Chart - In the following example you’ll see our Line chart data set, as well as what Excel thought we wanted for our chart.
• • Unfortunately, Excel didn’t quite get it right this time, which happens. Excel can only make its best guess as to what you want (and sometimes it’s completely clueless), so be prepared to make some corrections from time to time. Fortunately, this is very easy to fix. Just goto Chart Tools, Design, Switch Row/Column, and you’ll instantly have the layout you want. You can also see here how Line charts can have overlapping lines, or they can be stacked.

 

Figure 354  

• • Layout – Assuming that your chart is everything you want it to be from a data and type standpoint, then you can drag it to where you want it, keeping an eye for how large or small it will have to be when you place it. It’s generally a good idea to get your chart in place and determine how big it needs to be (roughly), before you start formatting it because the chart’s size can impact some features, especially Font size. If you format the chart and then shrink it, you may end up having to reformat it, although you generally won’t have a problem if you start small then increase the chart size. In this section we’re going to discuss the primary Layout tools and options available to you from the Chart Tools’ Layout group.
• • Current Selection – This is just a way to scroll through all of the individual chart elements in a list as opposed to clicking on them in the actual chart. Selecting one of the elements will automatically activate it in the chart and allow you to make changes to it. This is a matter of preference and efficiency, for example, you might choose to use it if you happen to be closer to this option than the chart itself.

 

Figure 355  

• • Insert – Excel doesn’t limit you to textured fills and backgrounds, you can actually insert your own Pictures, Shapes or Text Boxes in charts, either as backgrounds or actual chart elements. From the Insert menu your options are limited to having the image as another part of the chart, as the image isn’t very controllable. Unless you want to insert something that won’t interfere with the chart elements, like a company logo, you’ll probably use the more flexible image options found in the Format section. The commentary text boxes in some of the chart examples might give you an idea of what you can do with these options, and it’s decidedly less than exciting.
• • Labels – Unless your chart is amazingly descriptive in its data representation you’ll probably want some type of labels on it to direct users to explanations of key elements, like the chart Title or Legend. Excel gives you several pre-formatted chart layouts as you saw in the Column chart examples, and here is where you can take those selections a bit farther. Each selection has its own sub-menu of options you can choose. As you’ll see you have over 25 different variables you can apply.

 

Figure 356  

• • Axes – This determines how you format the X (Horizontal) and Y (Vertical) axes. You can choose to display the Axes or not, and whether or not to display gridlines in the chart area or not. And if you do decide to display gridlines, you can choose to display minor, major gridlines, or both. If you decide that the standard options aren’t enough for you then you can always opt for the “More Options” dialogs, which offer an incredible amount of flexibility. Take a look at some of the changes you can make with the Major Gridlines options.

 

 

Figure 357  

• • Background – The background option is full of selections, but it can get overwhelming very fast. It’s important that whatever elements you add to a chart don’t actually end up detracting from the chart’s overall message by distracting the audience. This goes back to the conservative approach with your designs. While we all want charts to look great, there’s a difference between making a purely artistic statement and actually letting your figures speak to the issue(s) that you’re trying to communicate. Following are some examples of the Plot area fill that you can apply. The first is a Picture/Texture fill; the second is a Gradient fill. As you’ll see there are a lot of settings for you to explore as you define your ultimate chart.

 

Figure 358  

• • 3-D Options – If you worked at all on the Line chart example you might have noticed that the Chart Wall/Floor and 3-d Rotation options were disabled. These options will only be enabled for 3-D chart types. If you want to try those options simply change any of the example charts to 3-D types and see what you can do.
• • Analysis – This is where you can add additional information to your charts that Excel can calculate for you.

 

Figure 359  

• • Trendlines – For any chart group you select, Excel can calculate any number of Trend types (Linear, Exponential, Forecast or Moving Average). The Linear trend is the most common, but you may well find circumstances where the others prove useful as well. When you select any of the Trendline options you’ll be prompted for which data set the trend should apply. In this case a Trendline shows that product A is trending downward. While a Trendline isn’t going to show any level of scientific accuracy (although the calculations behind it are), because it is just a visual representation of the figures, it is a good indicator of performance, which can help you to make an informed decision.
• • As you can probably tell by now, you have virtually limitless choices when you put together your charts, so many so that it’s remarkable that so many corporate charts all look surprisingly similar. But it’s simple really: most people don’t realize how easy it is. Fortunately, you’re learning not to be bound by those misconceptions.

 

Figure 360  

• • Case in point: if you were to decide that this Trendline wasn’t detailed enough, or that you wanted to add lines for additional data points, then naturally there is an expanded Trendline dialog. Following is an example with a gradient, dashed line on Product B that took all of 10 seconds to add.

 

Figure 361  

• • Lines – These are relatively simple to understand. They’re vertical lines that drop from either the high points of the highest data set to the floor, or the highest data set to the lowest.

 

Figure 362  

• • Up/Down & Error Bars – These are just another way to indicate variances in your chart data.

 

Figure 363  

• • Properties – This is nothing more than a place to give your chart a descriptive name. If you change the chart title here, you will see that change reflected in the Name box next to the Formula Bar. You don’t need to change the chart title, but if you have multiple charts you might consider it just to keep track of them. By default Excel will name and number each successive chart that you add (e.g. Chart 1, Chart 2, Chart 3, etc.)

 

Figure 364  

• • Here’s an example of a finished Line Chart.

 

Figure 365  

• • Now you’ve seen how to select your chart data and type and create your chart, as well as how to begin preparing your chart for its final distribution by going through the Plot, Design and Layout steps. The next step is the detail formatting, where you add Data Labels, format the Data Series, change Shape Styles, Effects, Fonts, Word Art, etc. Most of these changes are dictated by the general chart size, which is why you leave this step until you get towards the end. None of these steps are mutually exclusive and you might not always follow them in any given order, which is fine. What matters is that you know how to quickly build and format charts the way that you want them without spending any more time on them than necessary.
• • Pie Charts – A Pie chart’s sole purpose is to represent the share each element has of the total pie. The example below is a very basic example of a pie chart, and in this section we’re going to talk about ways to add more detail to your charts with formatting elements. While Pie charts are also relatively common, their use isn’t recommended because they can inaccurately represent data from a visual perspective, especially 3-D pie charts due to the way that they render when drawn, where the bottom/front part of the chart can look bigger than it actually is because of pixel density.

 

Figure 366  

• • The first thing you can do with a Pie chart is expand the slices, which can be done as a group, or individual slices. To expand all of the slices, simply select the plot area (anywhere in the pie) and drag outwards in one motion. When you first select the Pie chart’s plot area you’ll see every intersecting point highlighted, to move individual slices, select each one and drag it out individually.

 

Figure 367  

• • Adding Detail - Next is adding detail to the Chart Plot Area. This generally means adding labels of some sort.

 

Figure 368  

• • Format Data Label - With Pie charts you can add labels that represent each slice’s actual values, or each slice’s percentage of the whole. Just right click on the plot area to make your selection. For other chart types the Data labels represent the actual values, and you can choose to add labels to one series or multiple. You also have a lot of options with regards to formatting the labels so don’t be shy about experimenting.

 

Figure 369

• • Format Data Series – Pie charts are notorious when it comes to the default chart aligning the Data Labels where they’re not crowded into each other, as seen in this next example. In the past you were generally required to move and place most of the label elements by hand in order to space them properly. But now you can use the Series Options’ Angle of first slice slider to move the labels into a better position. You’ll still probably need to make some manual adjustments, but it beats having to do it all yourself, and that’s one of the points of this lesson: given how time consuming charts can be, any amount of time you can save helps you become more efficient. Even if it’s just a small aspect of chart design, every little bit can add up.

 

Figure 370  

• • Here’s an example of a finished Pie chart

 

Figure 371  

• • Now that you know how to add the Data Labels and adjust the Data Series we’ll be discussing the final formatting elements in more detail. Particularly, formatting the rest of the chart elements so that everything comes together.

 

 

 

 

Figure 372  

• • Area Charts – These allow you to compare like data over a given timeline. They generally obscure more information than they show, and should only be used if there are enough differences between the data sets to differentiate them well. You might need to select a few different styles in order to get all of your data points to display. The example below shows the corporate data set we used in the Column chart example. As you can see it’s not the kind of data that’s a good fit for an Area chart as unrelated data is plotted on the same line. A better data set would be the Product A, B & C data, as seen in the next example.

 

Figure 373  

 

Figure 374  

• • But what’s wrong with this example? Right, there should be three data points, but you only see two! Unfortunately, Product A’s data is hidden behind B & C. The best way to solve that is either to convert the chart to a Stacked Area or 3D Area chart, then adjust the Fill’s Transparency level, as seen in the next example.

 

Figure 375  

• • The Stacked area chart shows each data set in relation to the total of the three, while the 3D chart shows each data set as its own independent measure. To change the style you choose from Chart Tools, Design, Styles. To adjust the transparency of the individual products, right-click on the one you want to change and choose Format Data Series, Fill. You can adjust transparency and color at the bottom.

 

Figure 376  

• • Once you’ve adjusted the chart’s primary elements you can continue with the final formatting steps, which would be applying Shape and WordArt Styles. That’s where you see these examples go from a white background and default font to “finished” examples. You certainly don’t need to take the additional step to modify those elements, in fact, there are probably as many times that you’ll leave a chart rather non-descript (other than the data displayed) as you will take the additional steps to fully format one. An example might be a chart that’s bound for the company stock prospectus vs. the annual report. They can be exactly the same charts, but the first layout dictates a very Spartan approach, while the next needs some of that Marketing department flair. Again, there are so many possible formatting options available here that you just need to experiment on your own.

 

Figure 377  

• • As you can see in the final examples, the 3D Area charts are a bit more of a challenge to format, and it is difficult to make the smaller values in the back stand out clearly even with transparency. You may well spend more time trying to get an Area chart to look right than it’s worth. They can also be difficult for users to fully comprehend unless the data set is completely straightforward. If you find yourself with data that is even in the least bit ambiguous, then an Area chart probably isn’t the right chart choice.

 

Figure 378  

• • Scatter Charts – Scatter charts are a lot like line charts, except where Line charts are good for comparing data sets over a given time period, Scatter charts are good for comparing pairs of data against each other. Scatter charts can also plot two series of data as one. Where a line chart’s Horizontal (X) axis must either be text or a date, a Scatter chart can plot a numeric value on the Horizontal axis. This means that you can plot two numeric values against each other.

 

Figure 379  

• • An example of this would be daily temperature and precipitation over a given period. Here’s an example of both and you’ll see why line charts aren’t always a great choice when it comes to strictly numerical data.

 

Figure 380  

• • As you can see, the Rainfall plot is nearly meaningless in comparison to the Temperatures, while the Scatter chart ties them together with intersecting values. You might have noticed by now that we’ve been progressing through the most to least popular of the charts.
• • Data Point Types - As with Line charts you have the ability to use a Line, Markers or both to plot your data. You would choose the Marker option if you have quite a few data points and the addition of lines might make it hard to read. This is especially true with Scatter charts as line might wrap all over the place when following the data points, which isn’t so much of a problem with Line charts since the data is plotted along consistent intervals. Here’s an example of the earlier Scatter chart with lines added.

 

Figure 381  

• • Scatter charts can also take a bit of getting used to for the un-initiated, so unless you’re in an engineering or statistical environment where people would be used to seeing them, they’re probably best left alone. Here’s an example of a formatted Scatter chart.

 

Figure 382  

Using a Default Chart & Templates

If you’ve followed along with either the companion workbook or on your own, you’ve no doubt seen that charts can be complicated to set up, especially if you’re not used to doing it. What stops most people from either using charts or using them efficiently is getting bogged down in constantly recreating them. Imagine a report with 10—15 charts and you have to create them each by hand. Even if it only takes you 5-10 minutes per chart that’s a good chunk of your morning gone (5-10 minutes to create a chart is a perfectly reasonable time frame by the way). Now that you know exactly what kind of chart you’d use in any situation, and you’ve set up the perfect chart types, styles and formats for your needs, it’s time to show you how to use it over and over again. Now don’t think that this is going to be a panacea for never having to put any effort into charts again, because there will always be a certain degree of personalization involved, just as there are with your workbooks. But what this will do is help you eliminate the redundant work, which is often the most time consuming.

• • Default Chart – The default chart type for Excel is the Column chart. If you primarily use a different type of chart, like Line or Pie, you can change the default chart to that type, so that every new chart you create will be the type that you determine. When you have your primary chart created, just select it and then goto Insert, Chart, Launch the Chart Dialog and select “Set as Default Chart”. You can also do it from the Chart Tools ribbon Design, Change Chart Type. Unfortunately, this isn’t as useful as it might sound, as it only sets the default chart type and nothing more. To reuse an entire chart style, you need to create a chart template. The Default chart option will only apply when you use the ALT+F1 or F11 shortcuts for creating charts. If you go through the Ribbon and select a particular chart type to insert, the default chart won’t override your selection.
• • Chart Template – This feature can be incredibly useful, especially if you create a lot of charts. This is actually very much like workbook templates that you create, except you can use multiple chart templates in one workbook. To start first create your first chart (or just open the workbook with the chart(s) in it). Assuming that your chart(s) are formatted the way that you want, and then activate the chart and goto Chart Tools, Design, Type, Save As Template. You’ll immediately be presented with a Save Chart Template dialog, which will prompt you for a Chart name. Enter something descriptive and Save it.

 

Figure 383  

• • In the future if you ever want to apply that chart style to any chart, simply select the chart in question, goto Chart Tools, Design, Type, Change Chart Type, Templates, Select the template that you want to apply. Note that the descriptive name you entered when you saved the template makes no difference here, as all the Template manager will show you is a thumbnail indicating the chart type. If you have multiple charts that are the same type, you might have to pick and choose before you get the right one.

 

Figure 384  

 

Figure 385 Standard chart vs chart template

Sparklines

While Sparklines aren’t technically a part of Charts, they fit a remarkably similar need, so this is as good a place as any to discuss them. As previously introduced, Sparklines allow you to insert mini charts in a cell that represents the data you’ve selected (it doesn’t have to be adjacent to the data, but it’s a generally a good idea, so that you maintain a visual reference to it). Sparklines are a great adjunct to your data, but unlike charts that can be stand-alone and don’t necessarily need the supporting data to be relevant, Sparklines actually seem to be more relevant when they’re displayed in conjunction with their data. The amazing thing about Sparklines is how truly easy they are to use. Frankly, they’re a lot easier to set up than Charts, so don’t be surprised if you find yourself using these quite a bit. They are also a great substitute for situations where a chart might not be appropriate due to size limitations.

• • You can insert a Sparkline several ways, by selecting the data range, or the Sparkline destination, then the Sparkline type (you can choose from Line, Column or Win/Loss). Note that Sparklines can only be placed in a single cell per data range, they can’t span multiple rows or columns, but you can select multiple rows or columns when you’re setting up your Sparklines. As soon as you select the Sparkline type the Range/Location dialog will appear to verify your selections. Or you can simply select the Sparkline type and use the dialog range selections to do the rest. It’s just a matter of choice and what you prefer.

 

Figure 386 select the data range or Sparkline destination

• • In this example we selected a three-row range to apply Sparklines. Even though the Sparklines that will be inserted represent their respective rows, you don’t have to select and apply one row/range at a time, which is a real time saver. Once you confirm your selections Excel will place the Sparkline(s) on your sheet, and the Sparkline Design Tools Ribbon tab will be activated.
• • Here you can edit the Data points, change the Sparkline type, Show certain data points, as well as select a Style for the Sparkline. Once again, you have too many options to detail here, but feel free to experiment with the examples in the Lesson 9 workbook, or create your own.

 

Figure 387  

• • Here are several different examples of Sparklines and the variations that you can create. Note the addition of Up/Down-High/Low indicators in other colors.

 

Figure 388  

 

Charting Errata

Here are a few chart Tips & Tricks that might come in handy at some point in time, although they don’t necessarily fall into the realm of your everyday charting activities.

• • Display Hidden Data – Right-click on the chart’s plot area and choose the Select Data Option, then Hidden and Empty Cells.

 

Figure 389  

• • Interactive Charts – As mentioned earlier, a Chart’s default nature is to ignore hidden rows and columns, although as you just saw you can force charts to display hidden data.
• • Keeping in mind that hidden data won’t plot, we can use AutoFilter to create interactivity with charts by selectively choosing which data to show vs. hide. This can give your users a lot of flexibility and let them review certain scenarios even if you’re not there. This is a great tool for interactive reporting tools like Dashboards where you might want to give your users flexibility, and you don’t want to prepare 27 different scenarios. In this first example, there’s far too much data for the chart to be meaningful; it’s just too cluttered.

 

Figure 390  

• • By adding a Filter to the data you give the user the ability to pare down the field and create their own meaningful charts, while allowing them to make the data comparisons that they want, meaning that you don’t have to do it. When the user applies the filter, only the visible filtered data will plot.
• • What to do about 0 Values? You will often run into situations where your data isn’t complete and your chart flat lines when it hits it. This is often seen when plotting a timeline series, like weeks or months, when you have year-to-date information, but no data for the portion of the period that hasn’t yet occurred. In this case you need to tell Excel to ignore the missing data, which you can do via formula, specifically NA(). In the following example you’ll see some linked data that goes to zero because you can only have data for periods that have actually occurred. So we need to tell Excel to ignore the missing data.

 

Figure 391  

 

• • The formula is a simple one and it’s similar to testing for a DIV/0 error: =IF(B9,B9,NA()), which essentially says that if a value exists for B9, then return B9’s value, otherwise return #N/A. Since N/A won’t plot, this solves the flat line data problem. If you have data that isn’t linked, but is manually entered then you can simply replace missing values with =NA().

 

Figure 392  

• • Quickly Adjusting Chart Data Ranges – So far you’ve seen that you don’t necessarily need to select an entire data range in order to have Excel chart it. If an entire data range is to be charted then all you need to do is have the active cell somewhere within the data range and Excel will automatically include the entire range. You’ve also seen what can happen if the data range has areas that you don’t want to chart, in which case you need to select the chart range for Excel. But what if you want to adjust the plot range after the chart’s been created? It’s a lot easier than you might think. You don’t need to go into the chart data source manager. Remember, that as with functions and formulas, Excel will highlight the chart’s data ranges for you when you select the chart plot area, so you need look no farther than the range highlights in the data itself. Going back to the interactive charting example, you can just drag the area selections to the range that you want, either expanding or decreasing the plot data range.

 

Figure 393 drag the plot area wherever you want it

• • Magnitude Charts – There are often occasions where you need to chart two disparate data types, like Company Profit in dollars and percentages. If you recall the Line vs. Scatter chart examples, you saw that data points that are vastly different can cause graphing problems as one data series will be meaningful, while the other will be not much more than a flat line at the bottom of the chart range. You could try to build two individual charts and put them side-by-side, but a lot gets lost in the comparison that way. Fortunately, there is a trick to plotting disparate data points in the same chart. First you plot your data points just as you would normally which gives you a chart like this, and if you look very closely you’ll see the percentage values just barely visible along the bottom of the X (Horizontal) axis. Not very useful is it?

 

Figure 394  

• • Fear not, the fix is mere mouse clicks away! Select the chart (any part of it), then got Chart Tools, Layout, Current Selection, Find “Series GP%” in the list and select it, and you’ll see the Percentage data selected along the X axis.

 

Figure 395  

• • Then select Format Selection and you’ll be presented with the Format Data Series dialog. Select the Secondary Axis option and you’ll immediately see that the Percent values have been plotted on the same scale as your dollar values. Unfortunately, that view is about as useful as not seeing the values plotted, since they obscure the dollar values.

 

 

Figure 396  

• • Right about now you’re probably thinking that was a monumental waste of time, but you’re almost there. Close the Data Series dialog, then go back to Chart Tools, Design, Change Chart Type and select Line Chart. Your chart is immediately transformed into multiple charts layered on top of each other, and your Percentage labels are displayed on a secondary Y (Vertical) axis! Who needs a Marketing department when you can do it yourself?

 

Figure 397 Moving the legend to the bottom will make the display a bit more relative

• • Charting Non-Contiguous Ranges - As the following example will demonstrate, Chart elements don’t need to be contiguous in order to work, as only the Header row and data specific to the Central region has been selected. Be aware that if you decide to use complex ranges like this Excel will not readily expose your data range by highlighting it like it would with a standard data range. To view a complex data range you actually need to select the chart, then in Chart Tools, select Design, Select Data Source. At that point, Excel will show your data range bordered by the “dancing ants”, as well as displaying the Select Data Source Dialog, where the data range will be highlighted in the Chart Data range box. Don’t worry too much about charting complex ranges for now

 

Figure 398  

Unit Summary: Lesson 9 - Charts

• • In this lesson you learned about the various types of Charts you can have in Excel.
• • You learned that charts work best when the data is laid out in a cohesive Row/Column format, which is directly tied to good spreadsheet design, so the concepts reinforce each other.
• • You saw ways to quickly draw charts either embedded in a worksheet or as an independent Chart Sheet with a single keystroke sequence.
• • We discussed the core chart types, how to create each and what types of data are best for each chart type.
• • We also went over some charting tips and tricks that should help you overcome obstacles that you might encounter as you start to further explore what you can do with charts.
• • You were exposed to the multitude of ways that you can format charts be it changing styles to adding customized backgrounds and even WordArt elements.
• • You also saw how to reuse your favorite chart designs over and over again, instead of creating charts from scratch each time!
• • Most of all, this lesson should have encouraged you to not settle for the plain old boring charts that so many people feel compelled to not only create but share (giving charts a bad name). Hopefully you learned that most people create those charts because 1) they don’t know any better and 2) up until now, charts took far too long to create.

Review Questions – Lesson 9 - Charts

1. Name 4 of the 6 primary Chart types.

a. __________________________________________________

b. __________________________________________________

c. __________________________________________________

d. __________________________________________________

2. Under what circumstances would you use each of the above charts?

a. __________________________________________________

b. __________________________________________________

c. __________________________________________________

d. __________________________________________________

3. What’s the difference between the Horizontal (X) axis and the Vertical (Y) Axis?

a. __________________________________________________

4. Why would you include a Data Table in a chart?

a. __________________________________________________

5. How can you link a Chart Title to a cell?

a. __________________________________________________

6. Why would you adjust a Chart’s Display Units? How do you do it?

a. __________________________________________________

b. __________________________________________________

Lesson Assignment – Lesson 9 - Charts

Your assignment is work with all of the Charts included in the companion workbook. Once you’re comfortable with each one of them start creating your own data sets and applying the charting techniques that you’ve learned in the lesson.

Acknowledgments:

• • Jon Peltier, www.peltiertech.com – John is a Microsoft Excel MVP, and is widely recognized as the best charting guru on the planet. His website is full of advanced Charting techniques and tutorials.