mime-version: 1.0 content-type: multipart/related; boundary="----=_NextPart_01C90F69.BE8B4230" This document is a Single File Web Page, also known as a Web Archive file. If you are seeing this message, your browser or editor doesn't support Web Archive files. Please download a browser that supports Web Archive, such as Windows® Internet Explorer®. ------=_NextPart_01C90F69.BE8B4230 mime-version: 1.0 content-location: file:///C:/679C5AB7/xl_IntroducingExcelCharts_AF10287547.htm content-transfer-encoding: quoted-printable content-type: text/html; charset="us-ascii"
= This article is an excerpt from Excel 2007 Charts by John Walkenbach. A.k.a. = Mr. Spreadsheet, John is arguably the foremost authority on Excel. His forty= -plus books include Excel 2007 Power Programming with VBA, Excel = 2007 Formulas, and the bestselling Excel 2007 Bible, all published by Wiley. He has also written hundreds of articles and created the award-winning Power Utility Pack. To buy this b= ook, visit Wiley.com.
In This Article
When most people think of a spreadsheet product such as Exce= l, they think of crunching rows and columns of numbers. But, as you probably know already, Excel is no slouch when it comes to presenting data visually, in = the form of a chart. This chapter presents an introductory overview of Excel’s charting ability, and contains enough information for a typi= cal user to start creating and customizing charts.
I’ll start with the basics. A chart is a visual representation of numeric values. Charts (al= so known as graphs) have been an integral part of spreadsheets since the early days of Lotus 1-2-3. Charts generated by early spreadsheet products were extremely crude by today’s standards. But, over the years, quality a= nd flexibility have improved significantly. You’ll find that Excel prov= ides you with the tools to create a wide variety of highly customizable charts.=
In the first edition of this book, I offered an apolo= gy for the relatively poor visual quality of Excel charts. I’m pleased to be able to retract that apology for the Excel 2007 edition. The charting feat= ure in Excel 2007 has come a long way, and Excel is now capable of producing c= harts with much better visual appeal.
Displaying data in a well-conceived chart can make your numb= ers more understandable. Because a chart presents a picture, charts are particularly useful for summarizing a series of numbers and their interrelationships. Making a chart can often help you spot trends and patt= erns that might otherwise go unnoticed.
Figure 1 shows a worksheet that contains a simple column cha= rt that depicts a company’s sales volume by month. Viewing the chart makes it very apparent that sales were off in the summer months (June through Augus= t), but they increased steadily during the final four months of the year. You could, of course, arrive at this same conclusion simply by studying the numbers. But viewing the chart makes the point much more quickly.
Figure 1: A simple column cha= rt depicts the sales volume for each month.
A column chart is just one of many different types of charts= that you can create with Excel. By the way, creating this chart was simple: I selected the data in A1:B13, and then I pressed Alt+F1.
Before you can create a chart, you must have some numbers = 8212; sometimes known as data. The data, of course, is stored in the cells in a = worksheet. Normally, the data that is used by a chart resides in a single worksheet, = but that’s not a strict requirement. As you’ll see, a chart can use data that’s stored in any number of worksheets, and the worksheets c= an even be in different workbooks.
A chart is essentially an “object” that Excel cr= eates upon request. This object consists of one or more data series, displayed graphically. The appearance of the data series depends on the selected chart type. For example, if you crea= te a line chart that uses two data series, the chart contains two lines, and ea= ch line represents one data series. The data for each series is stored in a separate row or column. Each point on the line is determined by the value = in a single cell, and is represented by a marker. You can distinguish the lines= by their thickness, line style, color, or data markers.
Figure 2 shows a line chart that plots two data series acros= s a 6-year period. The series are identified by using different data markers (squares versus circles), shown in the legend at the bottom of the chart. The lines also use different colors, which is = not apparent in the grayscale figure.
Figure 2: This line chart dis= plays two data series.
A key point to keep in mind is that charts are dynamic. In o= ther words, a chart series is linked to the data in your worksheet. If the data changes, the chart is updated automatically to reflect those changes.
After you’ve created a chart, you can always change it= s type, change the formatting, add new data series to it, or change an existing da= ta series so that it uses data in a different range.
Charts can reside in either of two locations in a workbook:<= /p>
* In a worksheet= (an embedded chart)
* On a separate c= hart sheet
An embedded chart basically floats on top of a worksh= eet, on the worksheet’s drawing layer. The charts shown previously in this chapter are both embedded charts.
As with other drawing objects (such as a text box or a shape= ), you can move an embedded chart, resize it, change its proportions, adjust its borders, and perform other operations. Using embedded charts enables you to view the chart next to the data that it uses. Or, you can place several embedded charts together so that they print on a single page.
When you create an Excel 2007 chart, it’s always an em= bedded chart. The exception to this rule is when you select a range of data and p= ress F11 to create a default chart. Such a chart is created on a chart sheet.= p>
To make any changes to the actual chart in an embedded chart object, you must click it to activ= ate the chart. When a chart is activated, Excel displays the three Chart Tools context tabs shown in Figure 3: Chart ToolsèDesign, Cha= rt ToolsèLayout, and Chart ToolsèFormat.
Figure 3: Activating a chart = displays additional tabs on the Excel Ribbon.
You can move an embedded chart to its own chart sheet, so yo= u can view it by clicking a sheet tab. When you move a chart to a chart sheet, t= he chart occupies the entire sheet. If you plan to print a chart on a page by itself, using a chart sheet is often your better choice. If you have many = charts to create, you may want to put each one on a separate chart sheet to avoid cluttering your worksheet. This technique also makes locating a particular chart easier because you can change the names of the chart sheets’ t= abs to provide a description of the chart that it contains.
Figure 4 shows a chart on a chart sheet. When a chart sheet = is activated, Excel displays the Chart Tools context tabs, as described in the previous section.
Figure 4: A chart on a chart = sheet
Previous versions of Excel have a Size with Window op= tion for charts on a chart sheet. When this setting is enabled, the chart adjus= ts itself when you resize the workbook window (it always fits perfectly in the window). This feature is not available in Excel 2007.
In Microsoft Office 2007, the charting feature has undergone= some major changes. If you’ve used the charting feature in a previous ver= sion of Excel, this sidebar provides a summary of what’s new and whatR= 17;s changed.
The Office 2007 charting engine is compatible with other Off= ice 2007 apps such as Word and PowerPoint. In most (but not all) cases, charts created in a previous version can render without a problem in Office 2007. Excel 2007 offers the following charting changes:
* A chart is a shape: In Office 20= 07, an embedded chart’s container is a shape object. Although you can’= ;t change the type of shape, you can format the shape using most of the new shape-formatting options.
* Built-in chart layouts: Each cha= rt type has a number of different predefined layouts that you can apply with a sin= gle mouse click.
* Built-in chart styles: Each char= t type has a number of different predefined styles that you can apply with a sing= le mouse click.
* More colors: The old 56-color limitation has been lifted, and a chart can now use any number of colors. = And the default color choices are much more pleasing to the eye than the gaudy colors in previous versions.
* Enhanced formatting: Office 2007 provides many significant enhancements in formatting chart elements, inclu= ding quite a few interesting graphic effects.
* Chart Wizard: In previous versio= ns, most users invoked the Chart Wizard to assist with creating a chart. The C= hart Wizard is no longer available.
* Size with Window: In previous ve= rsions, charts located on a chart sheet had a Size with Window command that would automatically resize the chart when the window size was changed. This feat= ure has been removed.
* Create a series by dragging: You= can no longer create a new chart series by selecting cells and dragging the selec= tion into a chart. However, you can accomplish the same effect by copying and pasting.
* Direct manipulation of data points:<= /b> In the past, you could select a data point in a chart and move it — whi= ch also changed the underlying data. This rarely used action no longer works.=
* Keyboard shortcut: Office 2007 s= upports a new shortcut key combination (Alt+F1) to create an embedded chart (of the default type) from the current range selection. Pressing F11 still creates= a chart sheet from the current range selection.<= /p>
* Rotating 3-D charts: In previous versions, you could use your mouse to directly manipulate the view of a 3-D chart. In Office 2007, changing the view requires the use of a dialog box.=
* Pattern fills: Pattern fills (su= ch as diagonal lines) for chart elements are no longer supported. However, if you open an older file that uses pattern fills, the patterns will continue to display in Office 2007.
* Double-clicking to format: In th= e past, double-clicking a chart element (for example, an axis) displayed a formatt= ing dialog box appropriate for that element. Double-clicking no longer display= s a formatting dialog box. However, Office 2007 features a stay-on-top formatt= ing dialog box that lets you modify the selected chart element.<= /span>
* Adding text to a chart: In previ= ous versions, you could add arbitrary text to a chart by selecting the chart a= nd then typing the text (Excel created a text box automatically). That action= no longer works in Office 2007. You need to explicitly insert a text box.
* PivotCharts retain their formatting:= In previous versions, custom formatting applied to a PivotChart was lost when= the underlying PivotTable was refreshed. This annoying bug has been fixed in O= ffice 2007.
* Log scale enhancements: If you use a logarithmic scale for a chart’s value axis, you are no longer limited to base-10 numbers. In addition, the minimum and maximum scale values for a log chart= are no longer constrained to powers of 10.
* Chart templates: Chart templates= are much easier to create and apply in Office 2007. However, they still suffer= from some limitations.
* No built-in custom chart types: Previous versio= ns of Excel included several built-in custom chart types (including several combination charts). These custom chart types are no longer available.
* Charts and dialog sheets: In Excel 2007, you ca= n no longer add a chart to a dialog sheet. (Dialog sheets are fairly obsolete, anyway.)
* Document themes: Office 2007 sup= ports document themes. A theme can be applied to a document to change its overall look by using different fonts, colors, and graphic effects. Applying a doc= ument theme changes the appearance of the charts.
* Object model changes: If youR= 17;re a VBA programmer, you’ll also discover some new twists in the charting object model.
Noticeably absent from this list are any new chart types. Unfortunately, Microsoft hasn’t added any new chart types in Office = 2007. Maybe in the next release . . .
A chart is made up of many different elements, and all of th= ese elements are optional. Yes, you can create a chart that contains no chart elements — an empty chart. It’s not very useful, but Excel all= ows it.
Refer to the chart in Figure 5 as you read the following description of the chart’s elements.
Figure 5: Parts of a chart
This particular chart is a “combination” chart t= hat displays two data series: Income and Profit Margin. Income is plotted as vertic= al columns, and the Profit Margin is plotted as a line with square markers. E= ach bar (or marker on the line) represents a single data point (the value in a cell).
The chart has a horizontal axis, known as the category axis.= i> This axis represents the category for each data point (January, February, and so on). This axis doesn’t have a label because the category units are obvious.
Notice that this chart has two vertical axes. These are know= n as value axes, and each one has a different scale. The axis on the l= eft is for the column series (Income), and the axis on the right is for the li= ne series (Profit Margin).
The value axes also display scale values. The axis on the le= ft displays scale values from 0 to 250,000, in major unit increments of 50,00= 0. The value axis on the right uses a different scale: 0 percent to 14 percen= t, in increments of 2 percent. For a value axis, you can control the minimum and maximum values, as well as the increment value.
A chart with two value axes is appropriate because the two d= ata series vary dramatically in scale. If the Profit Margin data was plotted u= sing the left axis, the line would not even be visible.
If a chart has more than one data series, you’ll usual= ly need a way to identify the data series or data points. A legend, for example, is often used to identify the various series in a chart. In this example, the legend appears at the bottom of the chart. Some charts also display data labels to identif= y specific data points. The example chart displays data labels for the Profit Margin series, but not for the Income series. In addition, most charts (including= the example chart) contain a chart tit= le and additional labels to identify the axes or categories.
The example chart also contains horizontal gridlines (which correspond to the values on the left axis). Gridlines are basically extensions of the value axis scale, which makes it easier for the viewer to determine the magnitude of the data points.
In addition, all charts have a chart area (the entire background area of the chart) and a plot area (the part that shows the actual chart, including the plotted data, the axes, and the axis labels).
Charts can have additional parts or fewer parts, depending o= n the chart type. For example, a pie chart (see Figure 6) has “slices” and= no axes. A 3-D chart may have walls= i> and a floor (see Figure 7).
Figure 6: A pie chart
Several other types of items can be added to a chart. For ex= ample, you can add a trend line or di= splay error bars.
Refer to Chapters 4 and 5 for additional information = about the elements available for various chart types.
Figure 7: A 3-D column chart<= /p>
The following table lists the limitations of Excel charts.<= /p>
Charts in a worksheet = &n= bsp; Limited by available memory
Worksheets referred to by a ch= art = 255
Data series in a chart = &n= bsp; 255
Data points in a data series
Data points in a data series (= 3-D charts) 4,000
Most users never find these limitations to be a problem. Ho= wever, one item that frequently does = cause problems is the limit on the length of the SERIES formula. Each argument is limited to 255 characters, and in some situations, that’s simply not enough characters. Refer to Chapter 3 for more information about SERIES formulas.