Although Excel worksheets are usually all about numbers, you can enhance them in visual ways to make those numbers easier to interpret. You can also use visual techniques to identify or extract key information. And sometimes it is appropriate to use visual elements to present additional information that has a bearing on the worksheet content.
This chapter guides you in studying the creation of charts, SmartArt business graphics, shapes, and outlines, and the use of conditional formatting and pictures. You also study ways of sorting and filtering data to ensure that you are presenting information in a way that effectively supports your message.
Important Before you can use the practice files in this chapter, you need to install them from the book’s companion CD to their default location. See “Using the Companion CD”
at the beginning of this book for more information.
Tip Graphics and operating system–related instructions in this book reflect the Windows Vista user interface. If your computer is running Windows XP and you experience trouble following the instructions as written, refer to the sidebar “If You Are Running Windows XP”
in “Working in the Microsoft Office Fluent User Interface” at the beginning of this book.
4.1 Create and format charts
Plotting Charts
Charts are an important tool for data analysis and are therefore a common component of certain types of worksheets. You can easily plot selected data as a chart to identify trends and relationships that might not be obvious from the data itself.
Tip You must select only the data you want to appear in the chart. If the data is not in a contiguous range of rows or columns, either rearrange the data or hold down the Ctrl key while you select noncontiguous ranges.
Different types of data are best suited for different types of charts. The following table shows the available chart types and the type of data they are particularly useful for plotting.
Chart type Typically used to show
Column Variations in value over time or comparisons Line Multiple data trends over evenly spaced intervals
Pie Percentages assigned to different components of one item (non-negative, non-zero, no more than seven values)
Bar Variations in value over time or the comparative values of several items at one point in time
Area Multiple data series as cumulative layers showing change over time Scatter (XY) Correlations between independent items
Stock Stock market or similar activity
Surface Trends in values across two different dimensions in a continuous curve, such as a topographic map
Bubble Correlations between three or more independent items
Doughnut Percentages assigned to different components of more than one item Radar Percentages assigned to different components of an item, radiating
from a center point
See Also For more information on chart types see the Excel Help topic “Available chart types.”
To plot selected data as a chart, all you have to do is specify the chart type. If the type of chart you initially select doesn’t adequately depict your data, you can change the type at any time. The 11 chart types each have several two-dimensional and three-dimensional variations, and you can customize each aspect of each variation.
When you plot worksheet data, a row or column of values (called data points in the charting world) constitutes a set of data called a data series. Each data point in a data series is represented graphically in the chart by a data marker and in the chart legend by a unique color or pattern. The data is plotted against an x-axis (or category axis) and a y-axis (or value axis). Three-dimensional charts also have a z-axis (or series axis).
Sometimes a chart does not produce the results you expect because the data series are plotted against the wrong axes; that is, Excel is plotting the data by row when it should be plotting by column, or vice versa. You can quickly switch the rows and columns to see whether that produces the desired effect. To see what Excel is doing behind the scenes, you can display the Select Data Source dialog box, which shows you exactly what is plotted where.
Strategy Practice plotting the same data in different ways. In particular, understand the effects of plotting data by column or by row.
➤To plot selected data as a chart on the data worksheet
Tip Before plotting the data, ensure that it is correctly set up for the type of chart you want to create. For example, a pie chart can display only one data series.
➜ On the Insert tab, in the Charts group, click the button of the chart type you want, and then click a sub-type.
➤To change the type of a selected chart
1. On the Design contextual tab, in the Type group, click the Change Chart Type button.
2. In the Change Chart Type dialog box, click a new type and sub-type, and then click OK.
➤To switch rows and columns in a selected chart
➜ On the Design contextual tab, in the Data group, click the Switch Row/Column button.
Or
1. On the Design contextual tab, in the Data group, click the Select Data button.
2. In the Select Data Source dialog box, click Switch Row/Column, and then click OK.
Layouts and Styles
You can apply predefined combinations of layouts and styles to quickly format a chart. You can also apply a shape style to the chart area to set it off from the rest of the sheet.
➤To change the layout of a selected chart
➜ On the Design contextual tab, in the Chart Layouts gallery, click the layout you want.
➤To apply a style to a selected chart
➜ On the Design contextual tab, in the Chart Styles gallery, click the style you want.
➤To apply a shape style to a selected chart
➜ On the Format contextual tab, in the Shape Styles gallery, click the style you want.
Practice Tasks
The practice fi les for these tasks are located in the Documents\Microsoft Press\
MCAS\Excel2007\Objective04 folder. If you want, save the task results in the same folder with My prepended to the fi le name.
l Open the DataSource workbook, and use the data on the Seattle worksheet to plot a simple pie chart.
l Open the Plotting workbook, and on the Sales worksheet, plot the data as a simple 2-D Clustered Column chart. Then switch the rows and columns.
l Continuing on the Sales worksheet of the Plotting workbook, change the chart to a 3-D Clustered Column chart. Then apply Layout 1, Style 34, and the Subtle Effect – Accent 3 shape style.
Practice Tasks
The practice fi les for these tasks are located in the
The practice fi les for these tasks are located in the Documents\Microsoft Press\Documents\Microsoft Press\
MCAS\Excel2007\Objective04
MCAS\Excel2007\Objective04 folder. If you want, save the task results in the folder. If you want, save the task results in the same folder with
same folder with MyMy prepended to the fi le name.yy prepended to the fi le name.
l Open the Open the DataSourceDataSource workbook, and use the data on the Seattle worksheet workbook, and use the data on the Seattle worksheet to plot a simple pie chart.
to plot a simple pie chart.
l Open the Open the PlottingPlotting workbook, and on the Sales worksheet, plot the data as a workbook, and on the Sales worksheet, plot the data as a simple 2-D Clustered Column chart. Then switch the rows and columns.
simple 2-D Clustered Column chart. Then switch the rows and columns.
l Continuing on the Sales worksheet of the Continuing on the Sales worksheet of the PlottingPlotting workbook, change the workbook, change the chart to a 3-D Clustered Column chart. Then apply Layout 1, Style 34, and chart to a 3-D Clustered Column chart. Then apply Layout 1, Style 34, and the Subtle Effect – Accent 3 shape style.
the Subtle Effect – Accent 3 shape style.
4.2 Modify charts
Moving and Sizing
The charts you create often don’t appear where you want them on a worksheet, and their default size might be too big or too small to adequately show their data. You can move and size a chart by using simple dragging techniques.
Tip You can change the size of only one chart at a time. Simultaneous resizing of multiple charts is not supported by Excel 2007.
If you prefer to display a chart on its own sheet instead of embedded in the worksheet containing its data, you can easily move it. You can also move it to any other existing worksheet in the workbook.
➤To move a selected chart to a chart sheet
1. On the Design tab, in the Location group, click the Move Chart button.
2. In the Move Chart dialog box, click New sheet, and then if you want, type a name for the sheet.
3. Click OK.
➤To move a selected chart to a different sheet in the same workbook
1. Display the Move Chart dialog box, click Object in, and then select the worksheet you want from the list.
2. Click OK.
➤To change the size of a selected chart
➜ Point to a handle (set of dots) on the chart’s frame, and then drag in the direction you want the chart to grow or shrink.
➜ Point to a handle in a corner of the chart’s frame, hold down the Shift key, and then drag in the direction you want the chart to grow or shrink proportionally.
➜ On the Format tab, in the Size group, change the Shape Height and Shape Width settings.
Or
1. On the Format tab, click the Size dialog box launcher.
2. In the Size and Properties dialog box, change the settings in the Size and rotate or Scale area, and then click OK.
Tip Select the Lock Aspect Ratio check box before changing the settings if you want to size the chart proportionally.
Editing Data
A chart is linked to its worksheet data, so any changes you make to the plotted data are immediately reflected in the chart. If you want to add or delete values in a data series or add or remove an entire series, you need to increase or decrease the range of the plotted data in the worksheet.
Range selector
➤To edit the data in a chart
➜ In the linked Excel worksheet, change the plotted values.
➤To change the range of plotted data in a selected chart
➜ In the linked Excel worksheet, drag the range selectors until they enclose the series you want to plot.
Chart Elements
To augment the usefulness or the attractiveness of a chart, you can add elements such as a title, axis labels, data labels, a data table, and gridlines. You can format each element in appropriate ways. You can also format the plot area (the area defined by the axes) and the chart area (the entire chart object).
Strategy You can tailor chart elements in too many ways for us to cover them in detail here.
In addition to choosing options from galleries, you can open a Format dialog box for each type of element. Make sure you are familiar with the chart elements and how to use them to enhance a chart.
➤To add a chart title
1. On the Layout tab, in the Labels group, click the Chart Title button.
2. In the Title gallery, click the option you want.
3. Select the title placeholder, and replace it with the one you want.
➤To add or remove axis titles
1. On the Layout tab, in the Labels group, click the Axis Titles button.
2. In the list, point to Primary Horizontal Axis Title, and then click None or Title Below Axis; or click More Primary Horizontal Axis Title Options, make specific selections in the Format Axis Title dialog box, and then click Close.
Or
In the list, point to Primary Vertical Axis Title, and then click None, Rotated, Horizontal, or Vertical; or click More Primary Vertical Axis Title Options, make specific selections in the Format Axis Title dialog box, and then click Close.
3. Select the placeholder axis title, and enter the text you want to appear as the axis title.
➤To add, remove, or move the legend
1. On the Layout tab, in the Labels group, click the Legend button.
2. In the Legend gallery, click the Show Legend (Right, Top, Left, or Bottom) or Overlay Legend (Right or Left) option you want.
Or
Click More Legend Options, make specific selections in the Format Legend dialog box, and then click Close.
➤To display data labels
1. On the Layout tab, in the Labels group, click the Data Labels button.
2. In the Data Labels gallery, click Show to display the value of each data point on its marker.
Or
Click More Data Label Options, make specific selections (including the number format and decimal places) in the Format Legend dialog box, and then click Close.
Tip Data labels can clutter up all but the simplest charts. If you need to show the data for a chart on a separate chart sheet, consider using a data table instead.
➤To display the chart data under the axis titles
1. On the Layout tab, in the Labels group, click the Data Table button.
2. In the Data Table gallery, click Show Data Table or Show Data Table with Legend Keys.
Or
Click More Data Table Options, make specific selections in the Format Data Table dialog box, and then click Close.
➤To display or hide axes
1. On the Layout tab, in the Axes group, click the Axes button.
2. In the list, point to Primary Horizontal Axis, and then click None, Show Left to Right Axis, Show Axis without labeling, or Show Right to Left Axis; or click More Primary Horizontal Axis Options, make specific selections in the Format Axis dialog box, and then click Close.
Or
In the list, point to Primary Vertical Axis, and then click None, Show Default Axis, Show Axis in Thousands, Show Axis in Millions, Show Axis in Billions, or Show Axis with Log Scale; or click More Primary Vertical Axis Options, make specific selections in the Format Axis dialog box, and then click Close.
➤To display or hide gridlines
1. On the Layout tab, in the Axes group, click the Gridlines button.
2. In the list, point to Primary Horizontal Gridlines, and then click None, Major Gridlines, Minor Gridlines, or Major & Minor Gridlines; or click More Primary Horizontal Gridlines Options, make specific selections in the Format Gridlines dialog box, and then click Close.
Or
In the list, point to Primary Vertical Gridlines, and then click None, Major Gridlines, Minor Gridlines, or Major & Minor Gridlines; or click More Primary Vertical Gridlines Options, make specific selections in the Format Gridlines dialog box, and then click Close.
➤To select a chart element for formatting
➜ On the Layout tab, in the Current Selection area, click the element you want in the Chart Elements list, and then click the Format Selection button to open the corresponding Format dialog box.
Tip An element does not appear in the Chart Elements list unless it is present in the chart.
Practice Tasks
The practice fi les for these tasks are located in the Documents\Microsoft Press\
MCAS\Excel2007\Objective04 folder. If you want, save the task results in the same folder with My prepended to the fi le name.
l Open the SizingMoving workbook, and on the Sales worksheet, increase the size of the chart until it occupies cells A1:L23. Then move it to a new chart sheet named Sales Chart.
l Open the Editing workbook, and change the October sales amount for the Flowers category to 888.25. Then add the November data series to the chart, and change the way the data is plotted so that you can compare sales for the two months.
l Open the ChartElements workbook, and add the title Air Quality Index Report to the chart. Then add data labels that show the percentage relationship of each data marker to the whole, with no decimal places.
Practice Tasks
The practice fi les for these tasks are located in the
The practice fi les for these tasks are located in the Documents\Microsoft Press\Documents\Microsoft Press\
MCAS\Excel2007\Objective04
MCAS\Excel2007\Objective04 folder. If you want, save the task results in the same folder. If you want, save the task results in the same folder with
folder with MyMy prepended to the fi le name.yy prepended to the fi le name.
l Open the Open the SizingMovingSizingMoving workbook, and on the Sales worksheet, increase the workbook, and on the Sales worksheet, increase the size of the chart until it occupies cells A1:L23. Then move it to a new chart size of the chart until it occupies cells A1:L23. Then move it to a new chart sheet named
sheet named Sales ChartSales Chart..
l Open the Open the EditingEditing workbook, and change the October sales amount for the workbook, and change the October sales amount for the Flowers category to
Flowers category to 888.25888.25. Then add the November data series to the chart, . Then add the November data series to the chart, and change the way the data is plotted so that you can compare sales for the and change the way the data is plotted so that you can compare sales for the two months.
two months.
l Open the Open the ChartElementsChartElements workbook, and add the title workbook, and add the title Air Quality Index ReportAir Quality Index Report to the chart. Then add data labels that show the percentage relationship of to the chart. Then add data labels that show the percentage relationship of each data marker to the whole, with no decimal places.
each data marker to the whole, with no decimal places.
4.3 Apply conditional formatting
You can make worksheet data easier to interpret by using conditional formatting to format cells based on their values. If a value meets a particular condition, Excel applies the formatting; if it doesn’t, the formatting is not applied.
You set up conditional formatting by specifying the condition, which is called a rule. You can select from the following types of rules:
l Highlight cells or top/bottom l Data bars, color scales, or icon sets
You can also define a rule from scratch in the New Formatting Rule dialog box.
The options available in the Edit The Rule Description area vary depending on the selection in the Select A Rule Type list. You can define multiple conditions for the same range of cells or table.
Strategy Familiarize yourself with all the types of rules and their variations so that you know how to quickly apply any condition that might be requested on the exam.
All the rules you create are listed in the Conditional Formatting Rules Manager dialog box, where you can do the following:
l Create and delete rules.
l Edit a selected rule.
l Adjust the order in which Excel processes rules.
l Specify whether Excel should stop processing rules after a cell meets the conditions of a specific rule.