Lesson 11
Charts in the Microsoft Excel documents
Basic questions
1. Main parts of an Excel Chart.
2. Using the Chart Wizard for Quick Chart create.
3. How user can select Data to Plot.
4. How user can select and customize a Chart Type.
5. How user can edit and format Chart Elements.
Introduction
For years, writers and illustrators have been trying to figure out the precise exchange rate between pictures and words. The standard made famous by the old aphorism, of course, is a thousand words per picture. But if you can replace even a hundred words of tangled, technical prose with a compelling visual image, you’ve made a great exchange.
Sometimes a chart doesn’t need to replace any words at all. You can use a basic row-and-column worksheet grid to help you organize data and perform calculations, but it’s difficult and sometimes impossible to analyze information and see patterns by staring at a sea of numbers. It’s especially unfair to your readers to ask them to work with raw numbers.
Charts help you turumeric data into visual displays in which you can identify trends and pick out patterns at a glance [18].
Using the Chart Wizard for Quick Results
In the simple way to plot the data, click and drag the cell range containing the data. It’s okay to include the column headings in the selected range, because Excel will use these as default series names. After selecting the data, select Insert 
Chart…from the main menu bar or press the Chart Wizard button on the toolbar, it’s the eighth button from the left under the main menu bar and has a little bar chart image on it). This activates the Chart Wizard.
Expert users might be tempted to dismiss Excel’s Chart Wizard as just another tool for beginners, to be avoided at all costs. Big mistake. Don’t think of the Chart Wizard as a set of training wheelsinstead, think of it as a highly structured, superbly organized interface that lets you efficiently deal with every chart option in four steps.
Note. Each of the four steps in the Chart Wizard corresponds to one of four choices on the shortcut menu that appears when you right-click the plot area or chart area of any chart. If you want to change only the data source of a chart, it takes fewer clicks to use the shortcut menus than to restart the Chart Wizard [18].
In Step 1, use the Chart Type dialog box (Figure 193) to select any of Excel’s built-in chart types or select a custom chart type you’ve defined previously. Select a category from the Chart Type list, and then click the icon for the corresponding chart subtype that best represents the type of chart you want to create. If you selected a range of data before starting the Chart Wizard, click and hold the button below the chart types to see a preview of your data. Click Next.
Figure 193. The first steep of the Chart Wizard
In Step 2, fill in or edit the Source Data dialog box to define the location of data series and values for use with labels and axes. If you selected data before starting the Chart Wizard, most of these decisions are already made for you (Figure 194).
On the
The Series tab shows the data series that will be plotted on the chart. In this example, the first column in the selected range represents x-values and the second column represents y-axis data. You can have more than one column of y-axis data corresponding to a single column of x-axis data. In such cases you could plot more than one data series. Here, you have the opportunity to add more data series to your chart. Further, you can always access the series dialog to add new series to an existing chart [20].
To add a new series, select each field on the right side of the dialog and fill in the appropriate cell ranges corresponding to the range containing the series name, the series x-axis data (which could be the same for all series on a chart), and the series y-axis data. Pressing the little icon to the right of each range field allows you to temporarily switch back to your spreadsheet and select the ranges by using the mouse instead of trying to remember cell references and manually typing them in.
Figure 194. the Chart Wizard: Step 2
In Step 3, enter title text and configure the appearance of legends, gridlines, and other chart elements. Use any of the categories in the multitabbed Chart Options dialog box shown in Figure 195. (The exact options available depend on the chart type you selected in Step 1.) As you add title text and adjust other chart options, the preview window changes to show how your chart will look [20].
The Titles tab allows you to enter titles for major components of your chart, such as the chart title and the axes titles. If you have secondary axes (for plotting multiple series with different scales), you can also specify titles for the secondary axes.
The Gridlines tab allows you to select which, if any, gridlines you’d like to show on the chart. Major gridlines correspond to the major scale divisions for each axis, while the minor gridlines correspond to the minor scale divisions.
The Legend tab allows you to specify whether or not you want to include a legend on your chart and where you’d like it displayed. For this example, we really don’t need a legend, since we’re plotting a single data series. Legends are handy when you have multiple data series and would like a key that lets the user know which is which.
The final tab, Data Labels, allows you to specify whether you want data labels plotted next to each data point on your series and which label you want shown. You can choose from the series name itself, the x-value of each point, or the y-value of each point.
In Step 4, use the Location dialog box to specify whether you want to create a new chart sheet or embed a chart object in an existing worksheet. If you want to review your choices or make any changes, click the Back button. Click Finish to close the wizard and add the chart to your worksheet.
You can agonize over every detail of a chart, but sometimes it’s more effective to breeze through the Chart Wizard first, using its default settings and ignoring most of the fine details. After you’ve created a solid foundation for your chart, you can tweak individual elements. You can also click the Chart Wizard button to restart the wizard any time after you’ve created your chart. Many of the choices will be much clearer when you see what the first draft of the chart looks like.
Figure 195. Chart Wizard: Step 3, Titles and Axes tabs
Anatomy of an Excel Chart
When you create a new chart, Excel allows you to place it on its own chart sheet or embed a chart object within the worksheet that contains the data you want to chart. Working with a chart on its own sheet gives you the maximum working room for editing and formatting; embedding a chart within a worksheet lets you easily see the data and chart side by side.
Excel automatically maintains links between worksheet data and its graphic representation on the chart; if you change the numbers or text in the data range, the columns, pie slices, and other graphic elements on the chart change, too.
To create a chart, use either of these two basic techniques [18]:
· To create a chart and adjust all its options in four easy steps, click the Chart Wizard button on the Standard toolbar.
· To instantly create a chart on its own chart sheet using the default chart type, chart options, and formatting, open the worksheet that contains the data you want to chart. Select the range that contains the data you want to chart, and then press F11.
If the data you select is in a PivotTable, pressing F11 creates a new PivotChart on its own sheet using default formats.
You can choose from dozens of Excel chart types, ranging from basic bar charts appropriate in almost any simple analytical task to exotic data displays suited only for specialized scientific or engineering applications. Regardless of chart type, however, most charts include a set of standard elements and a palette of common options. Each row or column that contains numbers to be plotted, for example, makes up a data series. Each value within the series is called a data point. If the range you select for your chart includes worksheet headings, Excel uses them as labels along the category axis or value axis. The stacked-column chart in Figure 196 shows most of the common elements you’ll encounter as you work with charts.
Figure 196. Most Excel charts include some or all of these standard elements.
Note. To identify elements on an Excel chart, use the ScreenTips that pop up when the mouse pointer passes over objects. Even if you never use ScreenTips elsewhere, pay attention here because they are invaluable aids in identifying even the most obscure chart elements. Within the plot area, they identify the name of the data series and data points, as well as the precise value of each data point. If you don’t see these ScreenTips, this feature might have been disabled; to turn them back on, select Tools, Options, click the Chart tab, and select both check boxes in the Chart Tips area.
Data Series
Each group of related data points in a chart is called a data series. In almost all cases, a series consists of a contiguous range in a row or column on a worksheet, and when plotted in the chart each data series has a unique color or pattern. You can plot one or more data series in a chart. Bar charts, area charts, and column charts, like the one shown in Figure 197, typically contain multiple series. Pie charts consist of only one data series, in which the total always adds up to 100%. A line chart might contain one or multiple series [18].
Figure 197. This ScreenTip identifies the name of the data series and the name and value of the data point under the mouse pointer.
Data Markers
Each point in a data series becomes a single data marker on a chart. In column, bar, and pie charts, each column, bar, or pie slice is a marker that represents one data point. In stacked column charts, each segment within each column or bar is a marker. In line, xy (scatter), and radar charts, you can use dots or symbols to identify all the data points on a series. For example, a line chart that compares the trends of two or more stock prices over time might use a triangle, a diamond, and a square to mark data points on different series. Marker characters appear in the legend, as shown in Figure 198 [18].
Figure 198. When you’re printing in black and white, you can’t tell that each line is a different color. Instead, use data markers to differentiate series; the marker characters appear in the legend.
Note. Data markers are usually unnecessary when you’re creating a chart that will be viewed onlineinstead of marker characters, use colors to differentiate between series. Markers are most useful when you intend to print a line chart in black and white and you want each line to be easily identifiable.
Axes and Gridlines
Axes are the lines along each side of the plot area, which provide the scale for measurement or comparison of plotted data. The category (X) axis (usually horizontal) arranges your data by categoryby time, for example, or by division or employee. The value (Y) axis (usually vertical) defines how you want to plot the worksheet data. In the default column chart, for example, taller columns represent bigger numbers. In combination charts (discussed later in this chapter), it’s not unusual to see two value axes, both vertical. When you create a secondary value axis, the labels appear on the right of a column chart.
Note. In the default column chart type, the Y-axis is vertical and the X-axis is horizontal. Direction isn’t important, however; what matters most is the type of data you plot along each axis. In any chart, the X-axis contains categories. On a 2D chart, the Y-axis is for values; in a 3D chart the values go on the Z-axis, with the Y-axis reserved for different series of data.
Gridlines are horizontal or vertical lines that extend through the plot area to help you visualize the connections between data points and values or categories. Gridlines start with the tick marks on an axis and extend through the plot area.
Data Labels, Legends, and Titles
Data labels identify items on the category axis and define the scale of the value axis; you can also add labels to a single data marker, an entire data series, or all data markers in a chart. Depending on the chart type, data labels can show values, names of data series or categories, percentages, or a combination of these elements. Because of their tendency to clutter a chart, you should use data labels sparingly [18].
A legend is a color-coded key that identifies the colors or patterns that correspond to data series or categories. If you’ve defined data marker characters (as on a line chart), the legend includes the marker character as well.
Titles consist of descriptive text that identifies the chart or an axis. By default, titles are aligned to an axis or centered at the top of the chart.
Note. Add a chart title when you want to provide a descriptive label for the entire graphic. If you plan to paste the chart in a highly formatted Word document or PowerPoint presentation, however, you might want to leave the title off the chart while working in Excel and add the text in Word or PowerPoint instead. That way, when you change your document or presentation design, the title will change as well.
Plot Area
In a 2D chart, the plot area is the region enclosed by the axes; it includes all data series. In a 3D chart, the plot area also includes category names, tick-mark labels, and axis titles. Right-click and select Format Plot Area to add borders or background colors, textures, and fill effects behind the plotted data.
Chart Area
The chart area includes all chart elements. When you select the chart area, you’ll see eight small black squaresone in each corner and one in the middle of each side. Right-click and select Format Chart Area to change all text elements to a specific font or to add a background color or texture behind the entire chart.
Note. Many Excel experts don’t even know this secret: After selecting any part of a chart, you can move in rapid succession to other parts of the same chart by pressing the arrow keys. Use the up- and down-arrow keys to select major chart elements. Use the left- and right-arrow keys to select every available chart element in succession, including individual points within each data series, as well as every color key and text entry in the legend. The Name box (just to the left of the Formula bar) identifies which element is selected.
Working with Embedded Charts
If you chose to insert the chart as an object in an existing worksheet, it sits in its own layer on top of your data. Select the chart by clicking anywhere on it, and then point to one of its edges until the pointer turns into a four-headed arrow. Click and drag to slide the chart object into a new position. As you slide to any edge of the window, the worksheet scrolls in that direction.
When you move or resize cells underneath a chart object on a worksheet, Excel moves or resizes the chart as well. To change this link between chart and cells, right-click the chart area and select Format Chart Area from the shortcut menu. Click the Properties tab and select one of the three options in the Object Positioning section. Select Move but Don’t Size with Cells, for example, if you want the proportions of a chart and all objects on it to remain exactly the same even if you move it.
To change an embedded chart to a chart sheet, and vice versa, right-click the plot area or chart area and select Location from the shortcut menu. The two choices on this dialog box let you enter a name for a new chart sheet or select the name of an existing sheet on which to place an embedded chart [18].
You can view an embedded chart in a window without moving it to its own chart sheeta handy option when you want to edit or format an embedded chart without accidentally moving it or changing the zoom level of the worksheet. Right-click anywhere on the plot area or chart area of the embedded chart, and then select Chart Window from the shortcut menu. You can resize the window without affecting the position of the original embedded sheet. Right-click the title bar to print the chart, set page options, or run a spelling check. Click the Close (X) button to return to the worksheet window.
To resize an embedded chart, click once on the chart border to select the chart area. You’ll see eight small sizing handles along the borderone on each side and one in each corner. Point to any of these black squares and drag the pointera two-headed arrowin any direction to adjust the size and shape of the chart. As you drag, Excel adjusts the scale of all elements on your chart to match the new size and shape.
Excel doesn’t add sizing handles to a chart on a chart sheet; instead, the default settings for the chart sheet use your default paper size and expand the chart area to fit the entire page. So, if you select a zoom level of 100%, you see the entire chart. To resize the chart area of a chart sheet for printing, select File, Page Setup, and then click the Chart tab. Select the Custom option to display sizing handles so you can change the dimensions of the chart, and then click OK to return to the chart sheet. You caow move or resize the chart.
Selecting Data to Plot
Excel maintains links between worksheet data and the data series on a chart. When you create a chart, Excel automatically detects the data to be charted based on the current selection. If you select a single cell, Excel bases the chart data on the current regionan area that extends in each direction until you encounter the edge of the worksheet or a blank row or blank column. On the other hand, if you select a range of cells, Excel uses that range for the chart data.
Note. The number of points per series is limited to 32,000, and the total number of points per chart is limited to 256,000. The maximum number of series you can use in a chart is 255. If you have more series than this, you must filter your list before creating your chart. You should also seriously reconsider the point you’re trying to make, because even Stephen Hawking would have trouble absorbing that much information at once.
Be sure the range you select includes all the data to be charted, as well as the labels you’ll use for the categories. The range does not have to be contiguous. For example, to create a pie chart, you might want to select a row of column labels and a row of totals, ignoring the detail rows in between. Nor do you need to select all the data in a table, if all you want to chart is a subset of the datafor example, on a 12-month budget worksheet, you might want to show sales totals only for the months of October through December.
Caution. If the range you plan to chart ends with a row or column of totals, don’t include those totals in your selection; otherwise, the totals will create one column or pie slice that overwhelms all the others in the chart.
When you select the data source, Excel attempts to identify category headings, value axis labels, and data series; it also chooses whether to plot data by rows or by columns. This choice is based on the number of itemsif there are more columns than rows, Excel plots the data by column, placing the column headings along the category axis; if there are more rows than columns, or an equal number of rows and columns, Excel plots by row.
Normally, Excel plots data series from left to right and top to bottom. What do you do if your data source is arranged in alphabetical order, but you want to display the series in a different ordersay, with the two most productive regions listed first, or with dates in reverse order? If you don’t want to change the arrangement of data on the worksheet, you can change the plotting order of the data series [18]:
1. Click any data series in the chart you want to change.
2. Select Format, Selected Data Series (or press Ctrl+1), and click the Series Order tab.
3. In the Series Order box (Figure 199), select the series you want to move, and then click Move Up or Move Down. Repeat this step for each additional series you want to move. Watch the display in the Preview window to see the effect of your changes.
4. Click OK to apply your changes to the chart.
Figure 199. Series Order tab of the Format Data Series dialog box
Selecting and Customizing a Chart Type
When you create a new chart, Excel lets you select from 73 chart types in 14 categories (although a significant number of these choices are actually just minor variations of others in the same category). You can also choose from a gallery of 20 built-in custom chart types, and you can create and save your own chart types as well. The type of data you’re planning to plot usually dictates which type of chart you should choose.
Choosing a Standard Chart Type
When you start the Chart Wizard, the first step is to specify what type of chart you want to create. After you create a chart, you can easily change it to a new type; right-click the chart area or plot area and select Chart Type, or click the Chart Type button on the Chart toolbar to display the Chart Type dialog box. The following sections discuss all the standard Excel chart types and describe how you can best use them [18].
Column. This type of chart shows a comparison between values in one or more series, often over time.
Bar. Think of a bar chart as a column chart turned on its side, with values along the horizontal axis and categories on the vertical axis.
Line. This chart type displays a trend, or the relationship between values over a time period.
Pie. Pie charts show the relative size of all the parts in a whole for example, the ethnic composition of a city. Pie charts have no x- or y-axis, and only one data series can be plotted. Use pie charts when you have only a few numbers to chart and want to show how each number contributes to the whole.
XY (Scatter). Use a scatter chart to show correlations between different series of values when the element of time is unimportantusually used for scientific analyses.
Area. This chart type shows lines for parts of a series, adding all the values together to illustrate cumulative change. Unlike line charts, which emphasize the rate of change, area charts show the amount and magnitude of change.
Doughnut. The doughnut chart is similar to a pie chart, except that it can contain more than one data series. Each ring of the doughnut chart represents a data series.
Radar. Each category in a radar chart has its own value axis that extends from the center of the chart. Lines connect all the values in the same series.
Surface. Select this chart type to add a topographic layer over a column or area chart. Instead of assigning a color to each series, this chart type assigns different colors to similar values.
Bubble. Bubble charts are similar to scatter charts, except they contain three series of data rather than two. Instead of placing a uniform-sized dot at the point where each pair of x- and y-values intersect, the data markers are bubbles whose size is determined by the values in a third series.
Stock. Four built-in chart types make tracking open/high/low/close prices over time possible. Combination chart types in this category enable you to plot volume traded as well. You also can adapt these chart types for scientific use, to show high-low values in experimental data.
Cone/Cylinder/Pyramid. For the most part, these are simply glitzy versions of standard 3D column and bar charts. Options enable you to control whether each data marker tapers to a point or is tapered to the highest value in the series.
Using Combination Charts
The list of standard Excel chart types includes several combination charts, which mix two chart types in a single graphic. The Line-Column chart type, for example, lets you format one series of data along a line and another in columns; you’ll find this versatile chart type on the Custom Types tab of the Chart Types dialog box, along with other built-in custom designs.
In the Stock category on the Standard Types tab of the same dialog box, you’ll find combination charts that let you plot high, low, and closing stock prices on a line, with trading volume in columns. In this case, you use two value axes, one to the left of the chart area and the other on the right.
The Pie-of-Pie and Pie-of-Bar combination charts, both available as subtypes in the Pie category, offer a clever solution when you have so many data points that your chart is difficult to read. As the example in Figure 200 shows, you can use a Pie-of-Bar chart to combine several smaller slices into a single large slice, and then show the detail in a separate chart connected to the original.
To create either of these combination chart types, open the Chart Type dialog box and select the Pie type; then select the appropriate chart subtype from the list on the right and click OK [18]. To adjust which slices of the pie will go in the secondary (pie or bar) chart, right-click either pie and select Format Data Series. Then click the Options tab and adjust the settings as shown in Figure 201.
Using the Split Series By list, you can tell Excel to use a specific number of slices, or all slices below a certain value or percent. To move slices from the primary to the secondary chart, select the Custom option in this list; then close the Options dialog box and drag slices directly on the chart.
Figure 200. Use a Pie-of-Bar chart to keep small slices of the pie from getting lost.
Figure 201. Use this dialog box to shift slices of a pie from the primary chart to the secondary chart.
Editing and Formatting Chart Elements
Although the default chart settings are often good enough to get you started, Excel offers a broad range of chart options that give you complete control over the look of the chart and plot area. The easiest way to change many chart options is to rerun the Chart Wizard. The wizard’s dialog boxes pick up your current chart settings and let you change chart types, edit the source data, apply new formatting, or change the location of your chart [18].
Note. All the techniques described in this section work equally well with embedded chart objects, chart sheets, and PivotCharts.
To adjust individual chart objects, first select the object (the chart title or the category axis, for example), and then change its properties.
Note. Selecting a specific chart object by pointing to it can be difficult, especially on a small chart with many elements crowding one another for space. Try this simple shortcut: Use the Chart Objects drop-down list at the left of the Chart toolbar. Selecting any item from this list selects that item in the current chart. Then click the Format button just to its right to display the Format dialog box for the selected object.
Excel also lets you add an enormous number of attention-getting elements in the drawing layer on top of a chart. For example, you can add text boxes to data markers to explain anomalies in your data or call attention to key numbers. If you select the chart or plot area and start typing, Excel begins creating a text box immediately. After you’ve added the desired text, you can then move it anywhere on the chart and reformat it to your liking.
Adjusting Chart Options
Step 3 of the Chart Wizard displays a tabbed dialog box you can use to adjust various chart options. After you create a chart, you can display the same dialog box by right-clicking the chart area or plot area and selecting Chart Options from the shortcut menu. The following six categories of options might be available, depending on the chart type.
Titles. Create titles that appear on the top of the chart or next to any axis. If the nature of data along each axis isn’t immediately apparent, you can add explanatory text here, too. Click this tab and enter the text for the chart title and any available axes. In both locations, titles are nothing more than text boxes. The default font size for titles and other text objects on a chart is 10 point. Typically, this setting results in chart titles that are too small and legends that are too large. Use the Font tab on the Format dialog box to adjust the size of each object.
Axes. Click Automatic to allow Excel to format and display the axes that are appropriate for the chart type you’ve chosen. Normally, Excel is capable of analyzing the data type and using the correct settings. If your category axis includes dates, the Format Axis dialog box allows you to set the options shown in Figure 202. Adjust the Minimum and Maximum settings if you want to restrict the charted portion of your data; for example, in a list of daily temperature readings that encompasses several years, you might want to see just a few months’ results. Change the Base Unit option to chart time data using a different scale. For example, in a list that includes data for many days, select Month(s) or Year(s) to let Excel group the data before plotting [18].
Use the Category and Time-scale options to solve a particularly annoying charting problem: If you select a time series that skips some dates, Excel might leave gaps in the category axis. If you construct a stock chart using daily high, low, and closing prices, for instance, your time series will be missing values for Saturdays, Sundays, and holidays, when the market is closed. Unfortunately, Excel insists on adding phantom markers for those days, messing up the smooth line you expect to see.
The solution is to convert the Time-scale axis to a Category axis. Right-click the chart axis and select Chart Options. Click the Axes tab and select the Category option under the primary Category (X) Axis. When you close the dialog box, Excel changes the display of data to a simple series, with no gaps. If you open the Format Axis dialog box, you’ll see a different set of options, as shown in Figure 203.
This dialog box also lets you control the placement of tick marks, which are the small lines that indicate where each item on the category axis is plotted.
Figure 202. Time-scale axes let you control the start and stop dates as well as the interval for the plotted data.
Figure 203. If your time-scale axis includes some gaps in dates, convert it to a category axis. You might need to click the Categories in Reverse Order check box to display the dates correctly.
Note. If you have more items on your category axis than will fit, use the Number of Categories Between Tick-mark Labels option to suppress some of them. Enter a value of 2 here to see every other label on this axis; enter 10 to see every 10th label. You can also use the settings on the Alignment tab to rotate the text on the category axis labels and make extra room.
Gridlines. Gridlines help readers see where data points cross category or value axes. You can set major and minor gridlines for each axis. Normally, Excel does a decent job of setting intelligent defaults, but you often can clean up a chart and make it easier to read by adjusting these settings. In general, you should try to use as few gridlines as you can get away with. Watch the Preview window to see the effect as you add or remove gridlines.
Legend. A chart legend identifies each data marker according to its color or pattern on a chart. Options on this tab let you move or reformat the legend. If you don’t need to show a legend (perhaps because you want to label each column or pie slice individually), clear the Show Legend check box. The Placement options control where the legend first appears within the chart: Bottom, Corner, Top, Right, or Left. You can drag the legend box to position it more precisely later.
Data Labels. Use data labels when you want to display charted worksheet values, category labels, or percentages next to each point in a data series. Click the Legend Key Next to Label check box to add a color-coded key at the beginning of each label. The options in this dialog box control the placement and appearance of data labels for every data series. However, if you want to add labels for just one series, or even a single point, you can do so. Skip the Chart Options dialog box and instead open the Format dialog box for the series or point you want to label. Select the appropriate option on the Data Labels dialog box for that item [18].
Data Table. Display a worksheet-style table directly in your chart to show the plotted worksheet data alongside the chart itself. Each row in the data table represents a data series. If your chart includes a relatively small amount of data, a data table can make an effective addition.
Note. Data tables are available only in column, bar, line, area, and stock charts. You cannot add a data table to a pie, XY (scatter), doughnut, radar, surface, or bubble chart.
Changing Number Formats
Use the right-click shortcut menus to change the number format of any item on a chart; to adjust the value axis, for example, right-click and select Format Axis. Click the Number tab in the Format Axis dialog box, and then select a format from the Category list. You can select a built-in number format or create a custom format, just as you can when formatting worksheet data. Click OK to apply the new format to your chart data.
Normally, numbers that appear in Excel charts use the same format as the source data in the worksheet to which they’re linked. If you change the format of the numbers in the chart, you break the link to the format in the worksheet. Under many circumstances you’ll want to do exactly thatfor example, if numbers in your worksheet use the Currency format with two decimal places, but you don’t want to see a dollar sign or decimals in your chart. To reestablish the link so the data on the chart uses the same number format as the data on the worksheet, select the chart object (for instance, the Value axis), select its Format option, click the Number tab, and check the Linked to Source box.
Changing Text Formats
You can change the appearance of any text item on a chart. As with worksheet cells, Excel lets you change fonts as well as font sizes and character attributes. You can choose different colors for the text and its background. To keep labels from running into one another on any axis, rotate text to an angle.
Note. When you use the Chart Options dialog box to enter text for titles, Excel doesn’t allow you to enter line breaks or change formatting within the title. After you place the title on the chart, however, you can select and format individual words or characters as well as the entire title. To add a line break to a title, click to position the insertion point within the title on the chart, and then press Enter [18].
· To adjust font options for all text in your chart, right-click the chart area and select Format Chart Area. Click the Font tab of the resulting dialog box and adjust formatting as necessary. This dialog box is a great way to apply the same font to all text in your chart, but avoid the temptation to choose a standard size as well. In most cases, you’ll want to specify different font sizes for different items, such as the chart title, axes, and legend.
· To change text formatting for any text object on the chart, right-click the object and select its Format option. Click the Font tab and adjust options as desired.
Note. By default, text in an Excel chart is scalablethat is, as you resize the entire chart, the text gets larger or smaller so it remains in proportion with the rest of the chart elements. If you have carefully designed a text element and don’t want its font size to change, turn off automatic scaling. Right-click the object and click the Format menu. On the Font tab, clear the check mark from the Auto Scale box.
Are the category axis labels crowding the axis itself? Use the Offset box to specify the distance between the axis labels and the axis itself; the higher the number, the more distance between the two points.
Adding Background Colors, Textures, and Pictures
The default background for charts is plain white, but you can add background colors, textures, pictures, and gradient fills to an entire chart, to just the plot area, or to individual items such as data markers. In 3D charts, you also can add images to the walls and the floor.
These features use the Drawing tools shared by all Office programs [18]. If you’ve used Word to design a web page or created a presentation with PowerPoint, you can use the same backgrounds in Excel charts as in those files and then paste the charts into your web page or presentation with confidence that they’ll match the existing design.
Changing the Scale and Spacing of Axes
To make a chart easier to read, you might also want to adjust the scale on the value axis. Normally, the values on this axis start with 0 and extend to a number past the highest number in your data series. You might want to change the scale to start at a higher number, so you can more easily see the difference between data points. You can also adjust the display of large numbers [18]:
1. Right-click the value axis and select Format Axis.
2. Click the Scale tab to display the dialog box shown in Figure 204.
3. Enter the high and low values for the axis in the Minimum and Maximum boxes. Note that changing the default numbers automatically clears the check marks in the Auto column.
4. If you want to make large numbersthousands or millions, for exampleeasier to read, select an option from the Display Units drop-down list. If you select Millions, for example, Excel will display $85,000,000 as $85.
Click OK to apply the changes to your chart.
Changing the Data Source for an Existing Chart
How do you add, edit, or remove data series, category names, and axis labels when you’ve already created the chart? Use one of the following three techniques:
If the data source is simple and straightforward, with easy-to-identify headings for categories and values, right-click the chart area or plot area and select Source Data. Click the Data Range tab of the Source Data dialog box and select the new data range. If necessary, specify whether the series is in rows or columns. Click OK to apply the changes to the current chart.
To add, remove, or change an individual data series or the range that defines labels or names, click the Series tab on the Data Source dialog box and adjust the options there [18].
For example, if your current chart includes five data series, one for each year from 2001 through 2005, you might want to remove the first two years and add the years 2006 and 2007 to bring the chart up to date. Select the 2001 entry from the Series list and click Remove, and then do the same for the 2002 series. Note that the category name and value labels adjust automatically when you use this option.
To add or remove a single series from a chart embedded on a worksheet, select the data range that contains the headings and values you want to plot and drop it directly on the chart. Excel adds the data to the plot area, complete with new category labels and legend items, if necessary.
Figure 204. The Format Axis dialog box
Note. If you copy a worksheet range to the Clipboard, you can right-click the plot area or chart area and select Edit, Paste to add the series to an existing chart.
When you select a data series on an embedded chart, the Range Finder displays a colored line around the corresponding range within the data source; the Range Finder also adds a border around the value axis labels and category labels, using different colors for each. Drag the selection by using the rectangular handle in the lower-right corner of each selection to extend or move the data range for each series. On a chart where the data source consists of a single contiguous range, selecting the chart area causes the Range Finder to highlight all the data series in one color, the value axis labels in another color, and category names in still another color.
Note. Excel uses the Range Finder only when the chart and its corresponding data range are on the same worksheet. Take advantage of this feature to debug problems in charts. If your chart is on a separate sheet, right-click the chart area and select Location; then click the As Object In option and select the worksheet that contains the charted data. Resize and reposition the chart object so it’s near the corresponding range within the data source and use the color-coding to identify which data series is causing the problem. After you’ve fixed the chart, use the Location shortcut menu to move it back to its own chart sheet.
When using the Range Finder with charts, you should be aware of the following limitations [18]:
· The Range Finder works only with a chart object that is embedded on the same worksheet as the data. It does not work with charts on chart sheets.
· You can drag to expand the data range or a given series to include new data in either direction; however, this technique works only for contiguous series. If any series consists of a noncontiguous range, you must use the wizard or the Data Source dialog box to select the data.
· When you click an individual data point, the Range Finder highlights the series that contains that point.
Advanced Chart Options
Excel’s advanced chart options let you add details that help you spot trends more easily. For example, in a line chart that plots daily closing stock prices over time, you can add a trendline and a moving average that smooth out some of the peaks and valleys in the data. You can do the same with a column chart to show a smooth trend over time. Select the series, right-click, and select Add Trendline. For charts that project data, you can also add error bars that define the upper- and lower-error limits of your projections by using standard statistical measures. You’ll find these options on the Error Bars tab of the Format Data Series dialog box.
Using PivotTable and PivotChart
Basic questions
1. How PivotTable and PivotChart Reports Work
2. When Should You Use a PivotTable?
3. Creating a PivotTable
4. Editing and Updating a PivotTable
5. Creating and Editing PivotCharts
6. Formatting and Printing PivotTables
7. Grouping Items in a PivotTable
How PivotTable and PivotChart Reports Work
PivotTables and PivotCharts are powerful tools for automatically summarizing and analyzing data without ever having to add a formula or function. As the name implies, you start with a list exactly like the ones we’ve discussed in the first half of this chapter; then you snap the rows and columns into position on a grid, and end up with a sorted, grouped, summarized, totaled, and subtotaled report. PivotTable reports are best for cross-tabulating liststhe more categories, the better. You can reduce a list of thousands of items to a single line, showing totals by category or department. Or you can create complex, multilevel groupings that show total expenses by department, grouped by budget category and by quarter. You can hide or show detail for each group with a quick double-click. You can change the view or grouping in literally seconds, just by dragging items on or off the sheet and moving them between row, column, and page fields [18].
Start with a list that contains multiple fields, and then use Excel’s PivotTable Wizard to set up a blank PivotTable page with just a few clicks. Instead of sorting your list and entering formulas and functions, you drag fields around on the PivotTable page to create a new view of your listExcel groups the data and adds summary formulas automatically. PivotCharts (which we discuss later in this chapter) are the visual equivalent of PivotTables, letting you create killer charts just as quickly, by dragging fields on a chart layout page.
Unlike subtotals and outlines, which modify the structure of your list to display summaries, PivotTables and PivotCharts create new, independent elements in your workbook. When you add or edit data in a list, the changes show up in your PivotTables and PivotCharts as well; because they’re separate elements, you can easily change the structure of a PivotTable or PivotChart, too, and your changes won’t mess up the data in the underlying list.
Figure 205 shows the four main drop zones on a blank PivotTable page. The PivotTable toolbar includes buttons for every field in your list. Use row fields and column fields to define how you want Excel to group your list. Data items define which fields contain the information you want to summarize. Page fields let you further refine your view by displaying a separate PivotTable for each item in a group, as though the table were on its own virtual page. You can use multiple row fields, column fields, or both, and you can specify which summary action you want Excel to perform on data itemsthe sum, average, or count of all related values, for instance [18].
Figure 205. Drag field buttons from the toolbar and drop them on the layout to build a PivotTable on-the-fly.
What can you do with a PivotTable? The number of uses is limited only by your imagination. Despite their dramatically different structures, for example, each of the following four PivotTables started with the same list of information about publicly traded stocks, which we downloaded from an Internet-based online service. In its raw form, with its grand total of 106,224 separate data points, the list is a prescription for information overload. Each of the 6,639 rows contains 16 data fields for an individual publicly traded company, including its name, ticker symbol, and industry category, the exchange on which it trades, its high and low stock price for the past year, and financial measurements such as net profit margin and return on equity.
A decade ago, you had to be a professional financial analyst with access to a mainframe computer to crunch numbers this thoroughly; today, you can manage your own money with nothing more than a web browser and some Excel know-how.
Figure 206 shows a simple PivotTable that lets you see at a glance how many companies are in each industry category, along with the average increase or decrease in stock price from companies in that category over the past year. This PivotTable consists of a single row field and two data items.
In Figure 207, more detail is added, displaying individual statistics for each company, and grouping the detail rows in alphabetical order by industry name. For this PivotTable, the data is arranged in report format, similar to the banded reports that high-end database management programs produce. Note that this PivotTable includes four data items instead of two, and a slew of Excel formatting options are used to make the report more readablechanging fonts and font sizes, aligning type and adding background shading, and standardizing the number of decimal points in each column [18].
Figure 206. With no column fields and only one row field, this PivotTable quickly counts the number of companies in each category and calculates the average price change for the year.
Figure 207. To hide gridlines and group-related items in bands such as these, choose a report format instead of the default table layout.
To slice the data even more finely and add an extra analytical dimension, you can drag more buttons from the PivotTable toolbar to the row and column fields. Each row in the PivotTable is grouped using unique values in two categories, and there are two column headings as well, one for each unique value in the “
The resulting PivotTable, shown in Figure 208, is a concise and crystal-clear cross- tabulation, giving you a side-by-side analysis of the number of stocks that split in the past year versus those that didn’t, broken down by industry category and exchange.
There are literally hundreds of options in even a modestly complex PivotTable, but a PivotTable doesn’t have to be large or complex to be effective. The PivotTable in Figure 209, for example, neatly summarizes more than 100,000 data points in just a few rows and columns.
Figure 208. Add a column field to quickly compare related data points. Notice that the worksheet pane is frozen to keep headings visible when scrolling, just as with an ordinary worksheet.
Figure 209. Notice the grand totals under the rows in this PivotTable. Use the page field in the top-left corner to filter the entire list.
To produce this example, we used two column fields, two row fields, and one page fielda drop-down list that lets us filter the records in the entire table. Choosing (All) from the page field shows a summary of all data in the list; by selecting a different entry from the drop-down list, you can show the same breakdown for each industry name. Select one category at a time to flip through a series of otherwise identical PivotTables that focus on each category [18].
The layout Excel produced automatically included totals for each row and column; we kept only the grand total at the bottom of the PivotTable. We had to modify other default settings as well, including changing the default formula to calculate the average of our data items. To make the headings and totals easier to read, we did some rewording, and then changed fonts and alignment, added shading, and wrapped text.
When Should You Use a PivotTable?
PivotTables have several advantages over other worksheet models. Using the PivotTable Wizard, it’s easy to create a PivotTable that summarizes all or part of a list in dozens of different ways. Trying to accomplish the same task by entering formulas manually would take days. Also, because PivotTables and PivotCharts do not change your existing data or its arrangement on the worksheet, you can freely experiment with different PivotTable layouts. Use the Undo button to roll back any changes you make in a PivotTable layout. If you want to start over, you can delete the PivotTable page and run the wizard again.
PivotTables are the correct choice when all your data is in a list or in an external database you can query from Excel. PivotTables are not appropriate for structured worksheet models that include data-entry cells, subtotals, and summary rows. A PivotTable won’t do much good on an annual budget worksheet, for example, because it already includes rows, columns, and subtotals. On the other hand, if you enter the raw data in a list (or import it from an external database), with each row containing a month, department, budget category, and amount, you can easily re-create that same layout in PivotTable formand you’ll have many more analytical options available to you later.
Creating a PivotTable
To create a PivotTable from an existing list, start with Excel’s PivotTable Wizard. In this simple three-step process, Excel prompts you for basic details about the PivotTable you want to create, including the location of the data source and where you want the PivotTable to appear. After you finish with the wizard, you’ll be able to lay out your data directly on the worksheet.
You can create and edit a PivotTable layout directly on the sheet, or you can edit PivotTable layouts the old-fashioned way, using the Layout dialog box. On a slow computer, or with extremely large lists, you might prefer to use this technique, because it doesn’t actually begin rearranging data until you click OK. To open the Layout dialog box, click the Layout button in Step 3 of the PivotTable and PivotChart Wizard.
To build a new PivotTable, open the workbook that contains the list on which you plan to base the PivotTable. Then follow these steps [18]:
1. Click anywhere in your list. To build a PivotTable from a subset of the data in your list, select the range that contains the data.
2. Choose Data, PivotTable and PivotChart Report. The PivotTable Wizard appears, as shown in Figure 210.
Figure 210. If you don’t select a range first, the PivotTable Wizard assumes you want to base the new PivotTable on the entire list. Change the selection, if necessary, in the next step.
3. Specify the location of your datatypically an Excel list. If you choose the
4. The wizard asks you to specify the range in which your data is located. The default selection is your current list, or any range you selected before starting the wizard. Adjust the selection, if necessary, and click Next.
5. In its final step, the wizard asks you where you want to place the PivotTable. Choose the default option, New Worksheet.
Note. The PivotTable Wizard offers the option to place a PivotTable or PivotChart on an existing worksheet. In general, you should always choose to place a PivotTable on its own sheet. Adding a PivotTable to a sheet that contains data exposes you to the risk that changes you make to the list design will affect your PivotTable, or vice versa.
6. Click Finish to close the wizard and create a blank PivotTable page. Excel jumps to the new worksheet you just created and displays the PivotTable toolbar.
7. Drag field buttons from the PivotTable Field List and drop them into the appropriate regions in the layout. You must have at least one row or column field, and you must specify a data item.
Note. If you’re uncertain about exactly where to drop field buttons, watch the screen for two important clues. As the mouse pointer passes over each region of the PivotTable, Excel displays informative ScreenTips. When dragging fields around, watch the mouse pointerit changes shape to match the PivotTable layout, and a blue highlight in the pointer shows which of the four regions (row, column, data, or page) is under the pointer at any given moment. If you can’t figure out where to drop the selected field button, use the Add To list at the bottom of the PivotTable Field List window.
Don’t be surprised if the PivotTable doesn’t display properly at first. In particular, summary fields in the data area default to the SUM function. If you want to use COUNT, AVERAGE, or another summary function instead, see the next section.
Editing and Updating a PivotTable
After you create a PivotTable, it’s easy to rearrange fields and data items. Drag fields from one place to another to change the display of datafrom a row field to a column field, for example, if you want to see values side by side rather than one above the other. Right-click to display shortcut menus that let you adjust formatting and other options for each field. This section describes common procedures for editing PivotTables.
Use the PivotTable Field List to drag fields onto the layout. If you’re uncomfortable with drag-and-drop operations, use the Add To drop-down list to choose where you want the field to appear. If the Field List isn’t visible, right-click the PivotTable layout area and choose View Field List from the bottom of the shortcut menu. To make changes to the PivotTable report, use any or all of these techniques [18]:
· To change the list or data source on which the PivotTable is based, click the PivotTable Wizard button on the PivotTable toolbar. Click Back twice to return to the beginning of the wizard and make the required changes, and then click Finish. If you add new rows or columns to a list, you might need to perform this step before the data or fields will be available.
· To add a new field to the layout, drag a field button from the PivotTable toolbar and drop it on the layout. If you’re replacing an existing field, remove the old field first to reduce unnecessary calculations. When you drop a new field in the row or column area, Excel adds it as part of the hierarchy of fields that are already there and automatically groups items in the order in which they appear. Be careful to arrange these fields in the proper order. For example, if you have a list of product categories, each of which contains multiple products, place the category field to the left of the product name field, or the results will be nonsense.
Note. If your list includes two fields that have an absolute one-to-one correspondence, such as part numbers and part names, you can add them to the row area in either order and your list will appear correctly.
· To remove a field from any part of the PivotTable layout, drag the field button off the layout; when the pointer icon changes to include a red X, release the mouse button.
· To change the order of fields in rows, columns, or the data area, drag the field button and drop it in the correct location on the layout. Make sure you’re pointing to the field button and not its label; you’ll know you’ve aimed correctly when the mouse button turns to a four-headed pointer. Drag to another location and watch the mouse pointer and thick black lines for feedback on the correct “drop” location.
Note. Using the mouse to rearrange the order of data items on a PivotTable can be frustrating. It’s usually easier to right-click the field button you want to move, and then choose any of the options on the Order menu. Typically, you can move the item left or right one position, or move it to the beginning or end of the list.
· To change the summary function used in the data area (from SUM to COUNT or AVERAGE, for example), right-click the field button in the PivotTable and choose Field Settings from the shortcut menu. That action opens the PivotTable Field dialog box, shown in Figure 211. Select a function from the Summarize By list; if you want to change the name from its default, do so in the Name box, and then click OK to save the change.
Figure 211. The PivotTable Field dialog box
When you drag and drop buttons to arrange fields on a PivotTable page, Excel makes all sorts of decisions on your behalf. If these defaults aren’t correct, the following sections will help you change them.
Changing Sort Order and Other Display Options
The default sort order for rows and columns is usually alphanumeric. You can change the order of individual items by dragging them up or down (in the case of rows) or left or right (for columns). In other cases, you might want to adjust the default sort order. For example, if your PivotTable counts the number of items in each category, you might want to see categories with the highest number of items at the top of the list. To change the sort order, follow these steps [18]:
1. Right-click the PivotTable button for the row or column field and choose Field Settings.
2. Click the Advanced button to display the dialog box shown in Figure 212.
Figure 212. The PivotTable Field Advanced Options dialog box
3. Choose a sort order and the column by which to sort. The settings in Figure 212, for example, produce the list shown earlier in Figure 206, moving categories that contain the largest number of companies to the top of the list.
4. To show a specific number of records, choose Automatic from the AutoShow options section. This is a good way to create a “top 10” list, for example, showing only the categories that have the most items. Choose Top or Bottom from the Show drop-down list, and select a number between 1 and 255. Excel chooses records based on the sort order you defined in step 3.
5. Click OK to close the Advanced Options dialog box, and click OK again to close the PivotTable Field dialog box and return to the worksheet.
Adding and Removing Column and Row Subtotals
You can add subtotals to rows, columns, or both in a list. In some cases, Excel adds them automatically, even if they’re not appropriate. Subtotals can add a useful way to see the impact of groupings in your PivotTable, or they can add clutter between rows and columns. Depending on the design of your PivotTable and what Excel did automatically, you might need to add or remove these subtotals. In some cases, you can remove subtotals with the right-click shortcut menu. Right-click any of the subtotals and choose Hide. To add sub totals, you need to use the dialog boxes. To work with subtotals, follow these steps [18]:
1. Right-click the PivotTable button for the row or column heading that contains the subtotal, and choose Field Settings from the shortcut menu. Excel displays the PivotTable Field dialog box, as shown in Figure 213.
2. In the Subtotals section, choose Automatic to let Excel create subtotals for all items. Choose Custom and click a summary function to add one or more specific type of subtotals, such as Count and Average. Click None to remove all subtotals.
3. Click OK to exit the dialog box and make the changes you specified.
Figure 213. Use the Subtotals options to add, edit, or hide subtotals for a row or column.
Switching Between Table and Outline Layouts
The default layout for a PivotTable, as the name implies, is a tabular format. But that grid-style arrangement is not always the most effective way to present data. When you’re grouping a PivotTable by one row field and displaying data for a second row field, you probably want to use outline format instead; in that layout, the top-level row field appears in its own row, followed by each group of items. Which one should you choose? The question is partly dictated by the data in your table and partly by aesthetics; there is no right answer. Figure 214, for example, shows a table format. Figure 215 shows the same data arranged in outline format. In this case, the outline format is probably more appropriate, because each break in the grouping functions as a header for the list of details beneath it.
To switch between tabular and outline formats, follow these steps [18]:
1. Right-click the PivotTable button for the row field that’s farthest to the left, and choose Field Settings from the shortcut menu.
2. In the PivotTable Field dialog box, click the Layout button to display the dialog box shown in Figure 216.
3. To use a tabular layout, select the Show Items in Tabular Form option. To use an outline-style layout, select the Show Items in Outline Form option.
4. Adjust any other optionsto add a blank line or a page break after each group, for exampleand click OK.
Figure 214. In tabular format view, the categories are arranged in a column to the left of the items to which they belong.
Figure 215. In Outline view, the categories appear more like headers, and you can use space on the page or screen more efficiently.
Figure 216. PivotTable Layout dialog box
To make the tabular view in Figure 214 easier to read, a hidden option was used. Right-click anywhere on the table, choose Table Options, and then check the Merge Layout option. The effect is to merge all cells for the outside row and column labels.
Note. Excel includes a broad selection of PivotTable AutoFormats, divided more or less equally between table and outline (report) layouts. Use AutoFormats to quickly switch between table and outline layouts while also adjusting formatting options.
Removing Blank Cells and Error Messages
Because PivotTables automatically summarize all data, it’s common to see blank cells and error messages in the data area. #DIV/0 errors, for example, are especially common when calculating averages because in a long list, it’s almost certain that some items will have no matches in a particular row-and-column intersection. For example, if you’re calculating average sales with regions in the column area and product categories in the row area, some regions will have no sales for a particular category. These aren’t really errors; instead, you want the table to display a label such as NA, for “Not Applicable.”
Careful attention to blanks and error messages can make your PivotTable easier to read and make it look more professional. Here’s how to adjust the appearance of blank cells and errors [18]:
1. Right-click any part of the PivotTable and choose Table Options from the shortcut menu. Excel displays the PivotTable Options dialog box, as shown in Figure 217.
2. Select the For Error Values, Show check box. Click in the box to the right and fill in the information you want to display instead of the error message, such as NA.
3. Select the For Empty Cells, Show check box. If the field contains numeric data, enter 0 here; for a text field, enter the value you want Excel to display (NA, for instance) instead of leaving the cell blank.
4. Click OK to save your changes.
Figure 217. Use the options at the right of this dialog box to change the way a PivotTable displays blank cells and error messages.
Refreshing Data in a PivotTable
When you change the layout of a PivotTable, Excel automatically recalculates the resulting display of data. If you add or edit data in the underlying list, however, your changes do not appear immediately in the associated PivotTable. For PivotTable reports based on Excel lists, you must manually refresh the data in the PivotTable whenever you add, remove, or edit data. To be certain that the PivotTable reflects all recent changes, click the Refresh Data button on the PivotTable toolbar. If this toolbar isn’t visible, choose Data, Refresh Data.
Creating and Editing PivotCharts
A PivotChart is a chart based on data in a PivotTable. Like its row-and-column-based counterpart, you can rearrange a PivotChart by dragging field labels on a chart sheet. When you change the layout of a PivotChart, Excel automatically rearranges the corresponding data in your PivotTable, and vice versa.
In general, any time you can use a PivotChart instead of a conventional chart, you should jump at the opportunity, because they’re so much easier to create and edit.
PivotCharts follow almost exactly the same rules as charts you create from a conventional worksheet. The default chart type for a PivotChart is a stacked column chart, but you can change this to any chart type except X-Y (scatter) charts, bubble charts, and stock chart types. Chart options are identical to those found in regular charts, although you’ll discover that it’s impossible to move certain items, including the plot area, chart title, and axis titles [18].
Note. Every PivotChart requires a PivotTable, which it uses as its data source. You cannot create a PivotChart without adding a PivotTable to your worksheet as well.
To instantly create a PivotChart from an existing PivotTable, first click in the PivotTable, and then click the Chart Wizard button. (You’ll find this button on Excel’s Standard toolbar and on the PivotTable toolbar.) The PivotChart appears on a new chart sheet. If you prefer to start from scratch, start the PivotTable and PivotChart Wizard and choose the PivotChart Report option. Excel docks the field list on the right side of the screen and displays the blank PivotTable chart, as shown in Figure 218.
Figure 218. To change the layout of a PivotChart, drag field buttons from the toolbar and drop them on the appropriate area.
Of course, a chart doesn’t include rows or columns, so the available drop zones on a blank PivotChart page are slightly different from their counterparts on a PivotTable. When you create a PivotChart from a PivotTable, row fields become category fields, and column fields become series fields. To change the arrangement of data in the PivotChart, drag field buttons from the PivotTable toolbar and drop them in one of four areas on the PivotChart. Category fields go below the chart, and series fields appear at the right of the chart. Drop data items directly into the body of the chart. If you want to add a page field, drag it to the region above the chart. Page fields are especially effective on a PivotChart, because they allow you to chart a subset of your data without having to remove or change data series. Click the drop-down arrow to the right of the page field to choose which item you want to display in the chart. The chart shown in Figure 219, for example, allows you to quickly compare data for each company within an industry category. If you used the All option for this chart, the display of more than 2,200 companies would be gibberish.
Figure 219. This PivotTable chart is a tremendous improvement over an ordinary chart, because you can use the page field (Industry Name) to quickly filter and redisplay the information.
Note. There’s no way to hide field buttons on a PivotTable, but you can remove clutter from a PivotChart. If you’re happy with the chart layout, click the PivotChart button on the PivotTable toolbar and choose Hide PivotChart Field Buttons. Use the same menu choice to display the buttons again.
With PivotCharts, you can use the same formatting and editing options as with conventional charts. In particular, use right-click shortcut menus to choose a different chart type; format data series, axes, and the plot area; and add or edit colors and backgrounds to your chart.
Formatting and Printing PivotTables
When you first create a PivotTable, it picks up the generic look of a default worksheet, with plain 10-point Arial formatting for details and headings alike. To make your PivotTable more compelling, use Excel’s formatting features to add emphasis to text and backgrounds or shading to cells, rows, and columns. You can also adjust the number format of data items.
You can format numbers and text in the data area of a PivotTable by selecting cells individually and choosing formatting options as you would in a normal worksheet. However, if you redefine your PivotTable later, you will lose this formatting. That can be exasperating if you’re constantly losing, say, the number of decimal places you want to see in each data item. To apply number formatting that lasts, right-click any cell in the data items area and choose Field Settings from the shortcut menu. Click the Number button and choose a format from the dialog box [18].
Note. When you create a PivotTable or PivotChart on a new worksheet, Excel assigns a generic name to the new sheet. To make your worksheets easier to understand, right-click the tab, choose Rename, and give the sheet a new name that helps identify it. (You can also double-click the existing tab name to make it available for editing.) Right-click the PivotTable itself and choose Table Options to give the PivotTable itself a name, which you can use in dialog boxes and in the PivotTable Wizard.
Sometimes you need to adjust other formatting options as well. For example, you might want to change the alignment of a column of numbers, change to a new font, or add a background shade behind the column. Here, too, you have two options: If you right-click the cells in question and choose Format Cells, you’ll have access to all common cell formatting optionsNumber, Alignment, Font, and so on. But as soon as you rearrange your PivotTable, those custom formats vanish.
To lock cell formatting in place regardless of what you do with your PivotTable, right-click the PivotTable button for the field you want to format and choose Format Cells from the shortcut menu. Adjust desired formatting options and click OK.
To make PivotTables look their best, take advantage of Excel’s AutoFormat capability. After you’ve created a PivotTable, click the Format Report button on the PivotTable toolbar. You see a dialog box containing more than 20 ready-made formats. Select any format and click OK to apply the changes to your PivotTable.
If you don’t like the AutoFormat you’ve applied to a PivotTable, it’s easy to undo the changes. First, right-click any cell in the PivotTable, choose Table Options from the shortcut menu, and clear the check mark from the AutoFormat Table check box. Next, click the Format Report button to open the AutoFormat dialog box again. Scroll to the bottom of the list. Select the None option and click OK.
Extra Credit: Grouping Items in a PivotTable
Excel PivotTables are capable of splitting data into groups, even when you haven’t organized your data in advance. This is a powerful feature that’s useful in a variety of circumstances. When you choose to group data in a PivotTable, Excel analyzes the field you’ve chosen and displays a dialog box with choices that are appropriate for that type of data. For example, if you have a year’s worth of scientific data that records daily weather details for a particular location, you might want to group average temperature, rainfall totals, and other details by week or by month. If you have a list of textbooks and other course material in which each row contains a product name, its category, and a price, you might want to group the list of products by category, and then by price within groups: $1.00$10.00, $10.01$20.00, and so on.
In the example shown here, a worksheet-based list contains data from an automated weather-monitoring station that continuously records temperature, rainfall, relative humidity, barometric pressure, and other details. Each row contains a date and time stamp plus details for that sampling period. In total, the sheet contains two years’ worth of data, with 24 hourly data points for each day. Here’s how to create a report that shows monthly trends for all three years [18]:
1. Create a PivotTable using the Date field in the Row area and the Temp and Rainfall fields in the Data area.
2. Right-click any entry in the Date column and choose Group and Show Detail, Group from the shortcut menu. As Figure 220 shows, Excel correctly determines these are dates and offers to group by month. Because the sample extends over several years, choose Months and Years, and then click OK.
Figure 220. Adding Groups into the PivotTable
3. Drag two more copies of the Temp field into the Data area and format each one to show a different summary: Average, Max, and Min. Adjust the names of each summary as well. This step allows you to see the average temperature as well as the high and low marks for each month during the two-year period. Format the Rainfall field using the Sum function.
4. Use the AutoFormat option to choose one of the Report formats. Adjust column formatting and number formats for each summary cell.
The results, shown in Figure 221, give a month-by-month snapshot of the weather, even though we started with a list that included only daily details.
Figure 221. Results – Excel worksheets with PivotTable
Notice the final frill in this useful table: We hid the taskbar and chose View, Full Screen to make as much information as possible visible on the screen, hiding distracting toolbars and title bars.
