TOC BACK FORWARD HOME

Microsoft® Excel 97 Quick Reference

- 2 -
Charts and Graphics

Charts enable you to present worksheet data in graphical form. When you create a chart, the worksheet data used to create the chart is linked to it. When the worksheet data changes, the chart is updated to reflect those changes. Excel provides many features for creating and formatting charts. The Chart Wizard leads you step-by-step through the process. You can change chart types, add elements to a chart (such as titles or legends), and format chart elements (such as numbers, fonts, and styles).

Excel also enables you to add graphic elements to your worksheet. You can import graphics from other programs, insert clip art objects, or use the Drawing toolbar to create objects such as arrows, rectangles, and WordArt. After you draw an object in the worksheet, you can edit, rotate, flip, copy or move, size, or add shadows or 3-D effects to the object.

You can use the data map feature to present geographical data from your worksheets in map form. Data mapping enables you to see the relationships between numbers and geographic features.

Adding: Chart Arrows

You can use arrows to identify chart elements that you want people to notice, such as sales data that is higher or lower than expected, or a specific trend that may be occurring in the chart data. Frequently, you may use a text label to include explanatory text, and you can draw the arrow from the label to the chart element. (See also "Adding: Chart Data Labels.")

Steps

  1. Select the chart, and then display the Drawing toolbar by clicking the Drawing button on the Standard toolbar.

  2. Click the Arrow button on the Drawing toolbar. The pointer changes to a crosshair.

  3. Click in the chart where you want the tail of the arrow to appear, hold down the mouse button while you drag across the chart to where you want the head of the arrow, and then release the mouse button.

Move an existing arrow by selecting it and then dragging it to the location you want. To change the length or position of the arrow, select the arrow and then drag the white square at either end of the arrow. To delete an arrow, select the arrow and then press Delete.

TIP: You can format the arrow by adding color, changing the thickness of the line, or selecting a different shape for the arrowhead. Double-click the arrow, then make the selections you want in the Format Autoshape dialog box.

(See also "Drawing an Object" and "AutoShapes.")

Adding: Chart Data Labels

You can attach labels to data points on your chart, which helps the viewer interpret the data in a chart more easily. Data labels can represent the value for that data point or the category axis label associated with the data point. You can attach data labels to individual data points, a single data series, or all data points in a chart.

Steps

  1. Within the chart, select the data point(s) to which you want to add labels: to select an entire data series, click any point in the series; to select an individual point in the series, click the data point twice; to insert labels on all data points for all series, click outside the chart to select the entire chart.

  2. Choose Chart, Chart Options; then click the Data Labels tab in the Chart Options dialog box.

  3. Select the Data Labels option you want to use, such as Show Value, Show Percent, or Show Label. (Depending on the chart type, some options may not be available.) Click OK.

To remove existing data labels from a chart, display the Data Labels tab of the Chart Options dialog box. Then select the None option and click OK.

NOTE: umeric data labels display with the format of the corresponding cell(s) in the worksheet. To change a number's format in the chart, you can format its worksheet cell. If you want to format the data values directly in the chart, select the labels and choose Format, Selected Labels; then click the Number tab and change to the format you want.

TIP: You can add floating text in a chart to label an arrow or other chart elements. Select any nontext object in the chart, type the text, and press Enter. To move the text label, press and hold down Ctrl, then drag the label to the position you want. (See also "Adding: Chart Arrows.")

(See also "Formatting: Chart Data Series.")

Adding: Chart Gridlines

Use gridlines to help viewers compare markers and read values in a chart. If you use the Chart Wizard to create a chart, Excel enables you to add gridlines as you are creating the chart.

You can add gridlines that originate from either the category or value axis, or both. In a 3-D chart, you can also add gridlines for the Z axis (chart depth). You can choose whether to display gridlines for major divisions on an axis or minor divisions (including points between major divisions).

Steps

  1. After selecting the chart, choose Chart, Chart Options; then click the Gridlines tab in the Chart Options dialog box.

  2. Select the type of gridlines you want to add; then click OK.

To remove gridlines from a chart, display the Gridlines tab of the Chart Options dialog box, and then clear the boxes for the gridlines you want to remove.

TIP: Avoid using too many gridlines in a chart because they may make the chart confusing and difficult to read.

Adding: Chart Legends

A legend explains the markers or symbols used in a chart. If you use the Chart Wizard to create a chart, Excel creates a legend by default, based on labels from the shorter side of the worksheet data series. If your chart does not include a legend, you can easily add one. You also can format a legend with border, pattern, and font selections.

Steps

  1. Select the chart.

  2. Choose Chart, Chart Options; then click the Legend tab in the Chart Options dialog box.

  3. Choose Show Legend. Then select the Placement you want for the legend: Bottom, Corner, Top, Right, or Left. Click OK.

You can move the legend by selecting it and then dragging the legend to the location you want. To resize the legend, select it and then drag one of the black handles surrounding the legend. To delete a legend, select it and press Delete.

TIP: To format the legend, right-click the legend and choose Format Legend from the shortcut menu. Make the selections you want from the Format Legend dialog box and then click OK.

Adding: Chart Titles

You can add titles to help explain the data in your chart. Normally, you include a main chart title as well as titles for the category and value axes. If you use the Chart Wizard to create a chart, Excel enables you to add chart titles as you are creating the chart. You also can choose to add chart titles later or modify existing chart titles.

Steps

  1. After selecting the chart, choose Chart, Chart Options; then click the Titles tab in the Chart Options dialog box.

  2. Select the text box for the title you want to add (such as Chart Title) and type the title; then click OK.

You can move the title by selecting it and then dragging an edge of the title to the location you want. To delete a title, select it and press Delete.

TIP: To format a chart title, right-click the title and choose Format Title from the shortcut menu. Make the selections you want from the Format Title dialog box and then click OK.

(See also "Formatting: Chart Titles and Labels.")

Adding: Chart Trendlines

You can add a trendline to a chart to show the direction of the charted data and to make predictions. Regression analysis, a technique that describes relationships among variables, is used to create the trendline from the chart data. You can choose from among five types of regression lines or calculate a line that displays moving averages.

Steps

  1. After selecting the chart, choose Chart, Add Trendline; then select the Type tab in the Add Trendline dialog box.

  2. Select the data series for which you want to create a trendline in the Based On Series list.

  3. Select from among the six Trend/Regression types: Linear, Logarithmic, Polynomial, Power, Exponential, and Moving Average. For more detailed information on these types, click the question mark in the title bar of the dialog box, and then click the option for which you want more information.

  4. Select the Options tab if you want to set any additional options for the trendline, such as the Trendline Name or Forecast options. Click OK.

Adding: Graphic Backgrounds

You can add a graphic background, similar to a watermark, to your worksheets. This feature can dramatically improve the appearance of forms and reports. A background also is a unique way of inserting a company logo in a worksheet without using a prominent graphic.

When Excel adds a graphic background, the graphic is tiled, or repeated across the full width and height of the worksheet. Tiling requires no additional memory for multiple graphics than it does for a single graphic. Excel provides some samples of graphic backgrounds, such as a brick wall or a deco design. You also can use pictures of your own as well as Web page backgrounds.

Steps

  1. Open the worksheet in which you want to add a graphic background; then choose Format, Sheet, Background. The Sheet Background dialog box appears.

  2. Select the graphic file you want to use. Check the \MSOffice\Clipart folder for samples provided with Excel. Then click Open.

To delete a graphic background, choose Format, Sheet, Delete Background.

TIP: Most graphic backgrounds look better if you remove gridlines from the display. Choose Tools, Options; then click the View tab. Clear the Gridlines check box and then click OK.

Adding: New Chart Data

You can add new data to existing charts, whether you created the chart automatically or manually. You can add a new data series to a chart, add new data points to existing data series, or change the entire range of data the chart uses.

If you are working in a separate chart sheet or an embedded chart that has been selected in a worksheet, you can use the Chart, Add Data command. You can use the Chart, Source Data command to edit existing data series or add new series. (See "Creating: Chart Wizard Charts" before you complete this task.)

Steps

  1. To add new data to an embedded chart in a worksheet, select the data you want to add from that worksheet.

  2. Point to an edge of the selected range until the pointer changes to an arrow. Drag the data anywhere onto the chart and release the mouse button.

NOTE: If the chart to which you want to add new data resides on a separate chart sheet (instead of being embedded within a worksheet), the procedure is a bit different. Select the chart sheet; then choose Chart, Add Data. Enter the Range for the new data and click OK. The data is added to the chart.

AutoShapes

AutoShapes are ready-made shapes provided with Excel that you can add to worksheets, chart sheets, or embedded charts in a worksheet. These shapes are arranged in groups such as flowchart symbols, stars and banners, block arrows, and connectors. (See also "Drawing an Object.")

You can use AutoShapes to create an entire flowchart in a worksheet by adding flowchart symbols and using connector symbols or block arrows to connect the flowchart symbols. You can easily add text to most AutoShapes by selecting the shape and typing the text you want to insert.

Steps

  1. Select the worksheet, chart sheet, or embedded chart in a worksheet to which you want to add an AutoShape. Then click the Drawing button on the Standard toolbar to display the Drawing toolbar.

  2. Click the AutoShapes button on the Drawing toolbar. A pop-up menu appears.

  3. Point to a category of AutoShapes, such as Stars and Banners, and then click the shape you want to use.

  4. Click on the area within the worksheet, chart sheet, or embedded chart where you want the AutoShape to appear.

You can move the AutoShape by selecting it and then dragging the shape to the location you want. To resize the shape, select it and then drag one of the handles surrounding the shape. To maintain the original proportions of the shape, hold down the Shift key and then drag one of the corner handles. To delete an AutoShape, select it and press Delete.

TIP: To format the AutoShape by adding colors, changing borders, or adding shadow or 3-D effects, select the shape and use the tools on the Drawing toolbar. For additional formatting options, right-click the AutoShape and choose Format AutoShape from the shortcut menu. Make the selections you want and then click OK.

Changing Chart Types

You can change an Excel chart type to represent another type of data. You can change to any of the chart types that Excel offers--bar charts, line charts, pie charts, or special custom charts like floating bar charts.

When to Change Chart Types

You should use an appropriate chart type for the data you want to chart. The following list illustrates some common Excel chart types and explains their purpose. For more detailed information on all the chart types and examples of their use, search on "chart types, example" in Excel help.

Steps

  1. Right-click in a blank area of the chart and choose Chart Type from the shortcut menu.

  2. In the Chart Type dialog box, click the Standard Types or Custom Types tab.

  3. Select the chart type you want and then click OK. Resize the chart by dragging one of the chart handles, if necessary.

NOTE: In some cases, data may be more effective when presented in a table or text chart. Don't overload your charts with too many data points. Combine data into logical units such as first quarter, second quarter, and so on to make your charts more effective.

Clip Art

Clip art is a collection of graphics and pictures that is available for use in programs such as Excel. Clip art enables you to illustrate ideas with pictures. You can use clip art in place of columns or bars in a chart to add interest to a presentation, for example. You also can use clip art to create a company logo for a company invoice or expense statement. Many of Excel's built-in templates include a placeholder for you to insert a clip art logo. Be careful not to overdo your use of clip art; generally one effective use of clip art in a worksheet is sufficient. (See also "Creating: Picture Charts" and "Template Wizard: Using Excel's Templates" in the section "Data Analysis.")

Where to Find Clip Art

Many clip art collections are sold in categories like business graphics, medical graphics, entertainment graphics, and so on. To locate clip art, look for ads in the back of computer magazines, particularly desktop publishing magazines. You also may find clip art packages available for purchase at your local software store.

Some programs, such as Excel, include free clip art collections. You use the Microsoft Clip Gallery in Excel to easily access these clip art images. You also can find collections of clip art on the Internet and online services such as CompuServe, America Online, and Prodigy. (See also "Importing Graphics.")

Steps

  1. In your worksheet, select the cell where you want the upper-left corner of the clip art to appear.

  2. Choose Insert, Picture, Clip Art. Select the Clip Art tab in the Microsoft Clip Gallery dialog box.

  3. Select a category from the list box on the left side of the dialog box. Then click the clip art symbol you want to use and choose Insert.

NOTE: If you cannot access the Microsoft Clip Gallery, you probably did not install this feature with Excel. For more infor-mation on how to install the Clip Gallery, search on "clip art" in Excel help. Then choose "Insert an imported picture or clip art" and follow the instructions.

Creating: Automatic Charts

If you want to create a chart using the default chart type (normally a column chart) and place this chart on a separate chart sheet in the workbook, you can use the shortcut procedure listed below to quickly create the chart. Otherwise, if you want to use a different chart type or embed the chart in the worksheet that contains the source data, use the Chart Wizard to create the chart. (See also "Creating: Chart Wizard Charts.")

Steps

  1. Select the worksheet data you want to chart.

  2. Press the F11 key. The chart appears automatically in a separate chart sheet.2

Creating: Chart Wizard Charts

In Excel, you most often create charts using the Chart Wizard. Although the Chart Wizard steps you through the entire process of creating a chart, you can create the chart more quickly by accepting some or all of the defaults that Excel suggests. You can add a chart directly to the worksheet or you can create a separate chart sheet in the workbook. (See also "Creating: Automatic Charts.")

Steps

  1. Select the worksheet data you want to chart and then click the Chart Wizard button on the Standard toolbar.

  2. Select the type of chart you want in the Chart Type list; then choose Next.

  3. Verify the chart data range and specify whether you want to plot the series in Rows or Columns; then choose Next.

  4. Click the appropriate tab and select the chart options you want, such as titles and data labels; then choose Next.

  5. Select whether you want the chart to appear as a new sheet in the workbook or as a chart object (an embedded chart) in an existing worksheet. Choose Finish.

TIP: To move a chart, select it and drag an edge of the chart to the location you want. To change the size or proportions of the chart, drag one of the black handles along the borders until the chart is sized and proportioned the way you want.

Creating: Picture Charts

Excel charts can use pictures as markers in place of columns, bars, lines, or as backgrounds. You can use this feature to make picture charts that draw attention and then communicate the information. When showing dollar amounts, for example, you can use money bags of varying sizes for each marker.

Steps

  1. In the chart, right-click the data series that you want to replace with a picture, and choose Format Data Series. You can also select the plot or chart area if you want the picture to fill the background area of the chart.

  2. In the Format Data Series dialog box, click the Patterns tab; then choose Fill Effects and click the Picture tab.

  3. Choose Select Picture. In the Look In box, change to the drive and folder where the image is stored. Select the file for the picture you want to insert; then click OK.

  4. Choose other options on the Picture tab if you want. Experiment with various options (Stretch, Stack, or Stack and Scale) to see which effect looks best on your chart. For 3-D charts, select the options you want from the Apply To group. Click OK twice to return to the worksheet.

(See "Clip Art" for sources of picture collections.)

Drawing an Object

Excel enables you to add graphics to your worksheets by drawing objects inside the worksheet itself. The drawing buttons on the Drawing toolbar enable you to create ovals, rectangles, polygons, arrows, and even freehand objects. Excel 97 includes additional drawing tools as well as new tools to assist you with manipulating drawn objects.

By combining drawn objects, you can create all kinds of images. Drawn objects can enhance your worksheet reports, add annotation elements to your charts, and generally spruce up your worksheets.

You can use the AutoShapes tool on the Drawing toolbar to quickly draw more complex shapes such as stars and flowchart symbols. (See also "AutoShapes.")

Steps

  1. To display the Drawing toolbar, click the Drawing button on the Standard toolbar; or choose View, Toolbars, Drawing.

  2. From the Drawing toolbar, select a shape button such as the Arrow, Rectangle, or Oval. If you need more than one copy of a particular shape, double-click the selected button. That button stays selected until you click the same button or click another tool.

  3. Click and drag in the worksheet to draw the object, then release the mouse button. If you just click in the work-sheet, the object appears with the default dimensions.

  4. Change the selected object's location by dragging the object; change the selected object's size by dragging the sizing handles.

  5. Use the Drawing toolbar buttons such as Fill Color, Line Color, and Line Style to format the object's appearance.

To delete an object, select it and then press Delete.

TIP: To create a perfect square or circle, hold down the Shift key while drawing a rectangle or oval. To add a shadow or 3-D appearance to the object, select the object and then click the Shadow or 3-D button on the Drawing toolbar. Choose the effect you want from the palette.

Drawn Objects: Copying and Moving

Excel enables you to easily create exact duplicates of existing objects in your worksheet. You also can move objects to a different position in the worksheet.

Steps

  1. To copy an object, press and hold down the Ctrl key. Then click and drag the object you want to copy. As you drag, a copy of the object moves with the pointer. Release the mouse button.

  2. To move an object, select the object and then drag it to the position you want.

Drawn Objects: Freehand Drawings

Although you can use ready-made AutoShapes to save time when drawing objects, you sometimes may need to manually create an unusually shaped object. The Lines option on the AutoShapes button enables you to create these freehand drawings: curves, freeform polygons, and scribbles. (See "Drawing an Object" and "AutoShapes" before you complete this task.)

Steps

  1. Click the AutoShapes button in the Drawing toolbar and then choose Lines.

  2. Choose the freehand object you want to draw: Curve, Freeform, or Scribble.

  3. To create a curve or freeform object, click in the worksheet to begin the object, then move to a new point and click where you want the line to end. A curve or line appears between those two points. Click as many points as you want the object to include. When you are finished, double-click the last point.

  4. To create a scribble, hold down the mouse button the entire time you are drawing your object in the worksheet. Release the mouse button when you are finished.

  5. To create a closed drawing, right-click the freehand drawing, and choose Close Curve from the shortcut menu.

Drawn Objects: Grouping

When drawing objects, you may find that you want to format or edit many existing objects at once. Instead of having to select each object and format it individually, you can group the objects so they all act as if they are part of the same object. When you group objects, their individual handles disappear and the entire group is surrounded by sizing handles.

Steps

  1. Click the first object in the group to select it.

  2. Hold down the Shift key and click all other objects that you want in the group. All the objects should remain selected.

  3. Choose the Draw button on the Drawing toolbar and choose Group from the pop-up menu. You can now move or format the objects as a group.

To ungroup a set of grouped objects, select the group. Then choose the Draw button on the Drawing toolbar and choose Ungroup from the pop-up menu.

Drawn Objects: Rearranging

As you create a drawing, you sometimes add objects on top of one another in stacked layers. If you need to adjust the order of these objects, you can use the Order options available from the Draw button on the Drawing toolbar. You may choose to move the bottom object to the top, for example, to adjust its order in the stack. To get the arrangement you want, you may need to rearrange several objects in a specific order.

Steps

  1. Select the object that you want to move behind or in front of other objects.

  2. Choose the Draw button on the Drawing toolbar and then choose Order.

  3. Choose the option you want. The Bring to Front and Send to Back options move the object all the way to the front or back of the selected objects in one step. The Bring Forward and Send Backward options move the object forward or backward one object at a time.

Drawn Objects: Rotating and Flipping

You can adjust the placement and position of a drawn object by using the Rotate or Flip option on the Draw button of the Drawing toolbar. You can rotate the object left or right, 90 degrees at a time. You can also flip the object horizontally or vertically to reverse the position of the object. This feature is useful when you want a mirror image of an object.

Steps

  1. Select the object you want to rotate or flip.

  2. Choose the Draw button on the Drawing toolbar and then choose Rotate or Flip.

  3. Choose an option from the Rotate or Flip menu, such as Rotate Left, Rotate Right, Flip Horizontally, or Flip Vertically.

TIP: Click the Free Rotate button on the Drawing toolbar if you need more flexibility when rotating or flipping objects. Drag one of the rotation handles surrounding the object to the position you want. Click the Free Rotate button again to disable the feature.

Drawn Objects: Sizing

After you've drawn an object in the worksheet, you may decide to enlarge or reduce the size of an object. You also may want to change the proportions of the object.

Steps

  1. After selecting the object, click and drag one of the object's handles in the direction you want to resize. Drag a side handle to resize the object horizontally or vertically. Drag a corner handle to resize both sides simultaneously.

  2. Release the mouse button to end resizing.

TIP: Hold down the Shift key while dragging a corner handle to maintain the proportions of the object.

Formatting: Chart Axes

When you create a chart, Excel uses the default settings for the axis style, tick marks, and scaling. Tick marks are the small lines that appear on an axis, similar to measurement lines on a ruler. You can customize the axis, changing the style of the line used for the axis, the tick marks, the positioning of the tick-mark labels, and the scaling of the axis.

Steps

  1. Right-click the axis line you want to customize and choose Format Axis from the shortcut menu.

  2. In the Format Axis dialog box, click the Patterns tab and then select one of the Axis options.

  3. Select the options you want from the Major Tick Mark Type and Minor Tick Mark Type areas.

  4. Select an option in the Tick Mark Labels area to specify where to position the tick-mark labels.

  5. Check to see if the axis line in the Sample box looks the way you want, and make additional changes on the Patterns tab if necessary; then click OK.

Formatting: Chart Data Series

You can enhance the presentation of your data by adding error bars or data labels to a data series. Use error bars to visually display the margin of error in a data series. Data labels are typically used to explain the data points in a chart. You also can change the gap width between the columns in a column chart and make other formatting changes.

Steps

  1. In the worksheet, right-click the data series you want to format and choose Format Data Series from the shortcut menu.

  2. In the Format Data Series dialog box, click the tab containing the options you want to change (such as Error Bars, Data Labels, Series Order, or Options).

  3. Modify the options as you want; then click OK.

Formatting: Chart Fonts and Styles

You can change fonts and styles in charts just as you change them in worksheets. First, select the elements you want to change, and then make the changes.

Steps

  1. Click the text element you want to format. If you want to format only individual characters within a text element, select those characters.

  2. Click a button on the Formatting toolbar for the format you want to change. For example, click the Font Size button to change the point size of the font, or click the Italic button to italicize the text.

TIP: To see additional font options that don't appear on the toolbar, right-click the text element you want to format. Choose Format from the shortcut menu and make your selections from the Font tab; then click OK.

Formatting: Chart Numbers

You can format the numbers in a chart just as you format the numbers in a worksheet. For example, you can add dollar signs to the numbers on the value (Y) axis or change the format of numbers used as data labels.

If the numbers in the worksheet you used to create the chart are formatted, the numbers used in the value axis in the chart are formatted the same way. You can override this formatting, however, or add formatting if the numbers in the chart are unformatted.

Steps

  1. Right-click the object whose numbers you want to format and then choose Format from the shortcut menu.

  2. In the Format dialog box, click the Number tab and then select the number format you want from the Category list.

  3. The Format dialog box changes to display options that relate to your category choice. Change any options you want; then click OK.

TIP: To return the formatting to the number formats that appear in the source worksheet, select the Linked to Source option in the Format dialog box.

Formatting: Chart Objects

Excel provides many options and commands that enable you to enhance your chart objects. Chart objects include titles, legends, axes, data series, and so on. You can easily add or change the colors, patterns, and borders of objects in a chart. Excel 97 includes several new patterns, including interesting gradients and textures, that you can use in your charts.

Steps

  1. Right-click the chart object and choose Format from the shortcut menu.

  2. In the Format dialog box, click the Patterns tab.

  3. In the Border group, select the border options you want. In the Area group, select the color you want.

  4. To select a fill effect (pattern), choose Fill Effects. Click one of the tabs and choose the pattern you want; then click OK twice to return to the worksheet.

TIP: If you want to change the color or pattern for the chart background, right-click any edge of the chart and choose Format Chart Area from the shortcut menu. Make your selections and then click OK.

Formatting: Chart Titles and Labels

For some text objects in charts, such as titles, you can change both the horizontal and vertical alignment, as well as the orientation (or rotation) of the text. For other objects, such as the labels on the axes, you can change only the orientation. You can rotate axis titles or text boxes that contain explanations.

Steps

  1. Right-click the text object and choose Format from the shortcut menu.

  2. In the Format dialog box, click the Alignment tab; then select the alignment options you want from the Horizontal and Vertical drop-down lists.

  3. Drag the pointer in the Orientation box up or down to change the orientation of the text, or specify a value in the Degrees spin box between 90 and -90 degrees; then click OK.

TIP: To quickly select a stacked orientation, reading top to bottom, click the box to the left of the Orientation gauge.

Importing Graphics

The procedure for importing graphics (or pictures) into your worksheets is similar to the way you insert clip art images. You can insert a picture into your worksheet without opening the program you used to create the picture. After you import a graphic, you can move, resize, or format the graphic. (See also "Clip Art.")

Steps

  1. Select the cell in the worksheet where you want the upper-left corner of the graphic to appear. (Because the picture is inserted as an object, you can later move it to a different location.)

  2. Choose Insert, Picture, From File. The Insert Picture dialog box appears.

  3. Locate the graphic file you want to import; then choose Insert.

  4. The graphic appears in the worksheet, and the Picture toolbar appears. Use the buttons on the Picture toolbar to modify or format the graphic if you want.

Maps: Adding New Map Data

You can add new data to an existing data map at any time. You can insert data in existing categories or add entirely new categories (columnar data) to display in the map. (See "Maps: Creating a Basic Data Map" before you complete this task.)

Steps

  1. In the worksheet, type the new data in its own column (adjacent to the existing columns of data).

  2. Double-click the map to activate it; then choose Insert, Data.

  3. When the Microsoft Map dialog box appears, drag across the entire data range you want to map (existing data and new data); then click OK.

  4. The Microsoft Map Control now includes additional buttons that correspond to the data you have added. Drag the new column buttons into the working area to add the data to the map.

  5. If necessary, click the Map Refresh button on the Map toolbar to update the map.

NOTE: If you want only to insert data in existing categories, select cells in the middle of the current data range; then, from the Insert menu, choose Cells. Select the Shift Cells Down option and click OK. Enter the new region name and data where you inserted the cells and then click the Map Refresh button.

TIP: Double-click the column buttons at the top of the Microsoft Map Control to see the source for the data.

Maps: Adding Text Labels

You can add text labels with the size and font you want anywhere on your map. You also can edit existing labels that Excel supplied when you created the map. (See "Maps: Creating a Basic Data Map" before you complete this task.)

Steps

  1. Double-click the map to activate it.

  2. Click the Add Text button on the Map toolbar.

  3. Click the insertion point in the map where you want the text to appear and type the text; then press Enter.

  4. Repeat step 3 for each additional label you want to add.

TIP: You can move text labels by dragging them anywhere in the map. To format a text label, right-click the text, choose Format Font from the shortcut menu, then make your formatting selections from the Font dialog box. To delete the text label, right-click it and then choose Clear.

Maps: Changing the Column Source

You can use the Microsoft Map Control to plot different columns of data and watch the map change. (See "Maps: Creating a Basic Data Map" before you complete this task.)

Steps

  1. Double-click the map to activate it.

  2. If the Microsoft Map Control doesn't appear, click the Show/Hide Microsoft Map Control button on the Map toolbar.

  3. Drag a column button from the top of the Microsoft Map Control down on top of a column button in the working area (the white box inside the Microsoft Map Control).

  4. The dropped column button replaces the column button in the working area, and the map redraws to show the new column of data.

  5. If necessary, click the Map Refresh button on the Map toolbar to update the map.

Maps: Creating a Basic Data Map

Excel enables you to easily create a geographical map based on data in your worksheet. The geographic identifiers (such as state abbreviation or country name) should appear in the first column, with data column(s) appearing to the right. Include the headings at the top of the data columns.

Why Use Maps?

You can use maps for many common business applications that involve geographical data. For example, you can create a map that shows how sales and commissions are distributed by region.

Maps Provided with Excel

The following geographical maps are provided in a standard installation of Excel: Australia, Canada, Europe, Mexico, Southern Africa, UK and ROI, United States (with AK & HI Inset), United States in North America, and World Countries.

If you are unable to access maps, a custom installation of Excel 97 that did not include map information may have been performed on your system to conserve disk space. In this case, you need to run Excel Setup again to install the maps.

The Data Map feature is an Excel add-in written by MapInfo Corporation. Although the maps and data that come with Excel may suit your needs, you also can purchase add-in maps, data, and feature extensions from MapInfo. These maps include census data and features such as roads, cities, and airports. For more information on how to contact MapInfo and purchase these items, activate a data map and choose Help, How to Get More Data.

NOTE: When creating or modifying a data map, Excel displays the Microsoft Map menu and toolbar. These appear only when the map is active.

Steps

  1. In the worksheet, select the range containing the geographic identifiers and associated data that you want to map.

  2. Click the Map button on the Standard toolbar, then drag across the area where you want the map to appear (make the map frame as large as possible). Release the mouse button.

  3. If the Data Map feature cannot determine a unique map to use when it analyzes the left column of data, it displays the Multiple Maps Available dialog box. Select the map you want to use; then click OK to display the map.

  4. Use the Microsoft Map Control dialog box to specify the column of data and which type of map you want. The gray buttons at the top of the dialog box represent the columns in your selected data range. The six buttons on the left side of the dialog box enable you to change the map type.

  5. The default map created is a value shaded map using the first column of data. To change the data in the map, drag the column and format buttons you want to use into the white box inside the Microsoft Map Control.

  6. Size and format the map if you want.

TIP: To activate a map, double-click inside the map. A thick border appears around an activated map.

NOTE: The data you plan to map must include geographical data, such as country, state, city, or county names; or postal codes. For more information on the types of data you can map and the abbreviations accepted by Excel, see the file named MAPSTATS.XLS. This worksheet also contains sample population sizes and forecasts you can experiment with. To find MAPSTATS.XLS on your computer, click the Start button, then choose Find, Files or Folders; then type the file name and click Find Now.

(See also "Maps: Entering Map Titles.")

Maps: Customizing Maps

You sometimes may use maps to show data at a certain point in time. You also may use maps as decision resources--they present information and plot data so the viewer can analyze the information and use it to support decisions. Excel provides a variety of symbols you can use to customize maps, so the viewer can better understand the differences in the data points.

You can customize each of the map formats to use different symbols, dots, and so forth. For example, you can change the color and symbols used with a graduated symbol format. Or you can change the dot density by specifying how many units each dot represents. (See "Maps: Creating a Basic Data Map" before you complete this task.)

Steps

  1. Double-click the map to activate it.

  2. From the Map menu, choose the appropriate Options command from the bottom of the menu. Commands for map formats appear on the menu only when that format is in use in the current map. Examples of these options include Category Shading Options, Value Shading Options, and Dot Density Options.

  3. Each map format has a different dialog box and different set of options. Choose the options you want from the dialog box; then click OK.

TIP: A quick way to display the customizing options for a map format is to double-click the Map Format button that appears in the work area of the Microsoft Map Control.

Maps: Deleting a Map

You can delete an existing map if you no longer need the map or if you want to create an entirely new map in its place.

NOTE: You cannot delete a map when it is active. If the map is active (an active map has a thick border around it), select a cell in the worksheet to deactivate the map and then proceed with the following steps.

Steps

  1. Click the map once to select it. Handles appear around the map.

  2. Press Delete.

Maps: Entering Map Titles

Your data map already includes a main title as soon as you create the map. This title appears in a bordered box at the top of the map. You also can add a subtitle to your map. (See "Maps: Creating a Basic Data Map" before you complete this task.)

Steps

  1. Double-click the map to activate it.

  2. Choose View, Subtitle. (If your map isn't showing a main title, choose View, Title if you want to display it.)

  3. To edit the text in the subtitle (or the main title), select the title and then click where you want the insertion point.

  4. Use standard Windows editing techniques to modify the text in the title. Press Enter to enter the title.

Maps: Formatting Map Titles

You can enhance the appearance of your map titles by changing the font, font styles, and font color used for the titles. (See "Maps: Creating a Basic Data Map" and "Maps: Entering Map Titles" before you complete this task.)

Steps

  1. Double-click the map to activate it.

  2. Right-click the title and choose Format Font from the shortcut menu.

  3. Select the options you want to change in the Font dialog box, such as Font, Font Style, Size, and Color; then click OK.

TIP: You can drag the title (or subtitle) to a new location by clicking the title one time and then dragging it by the border. To remove a title or subtitle, select it and then press the Delete key.

Maps: Inserting Data in an Existing Map

The Data Map feature enables you to easily add new data to your map. (See "Maps: Creating a Basic Data Map" before you complete this task.)

Steps

  1. Select cells in the middle of the current data range; then, from the Insert menu, choose Cells.

  2. Choose the Shift Cells Down option; then click OK.

  3. Enter the new region name and data where you inserted the cells.

  4. Double-click the map to reactivate it; then click the Map Refresh button on the toolbar to update the map.

Maps: Refreshing a Map

When you make changes to worksheet data that your map uses, you need to refresh the map so it reflects these changes. Excel may not automatically update the map as you edit worksheet data. When you need to update the map, the Map Refresh button on the Map toolbar becomes available.

Steps

  1. Double-click the map to activate it.

  2. Click the Map Refresh button on the Map toolbar. If this button appears gray, the map has already been refreshed.

TIP: To change the map refresh from manual to automatic, activate the map and choose Tools, Options. Then choose Automatic and click OK.

Maps: Zooming in a Data Map

Data maps cover a great deal of geographic area, but you may be interested only in a few states or countries. You can magnify maps and reposition them so that they show only the area in which you are interested. (See "Maps: Creating a Basic Data Map" before you complete this task.)

Steps

  1. Double-click the map to activate it.

  2. Click the Zoom Percentage of Map drop-down list on the Map toolbar; then select or type a magnification.

  3. Press Enter or click the map to see the map magnify. To return to the default size, click the Display Entire button on the Map toolbar.

  4. If you want to move the magnified map within the map window, click the Grabber button on the Map toolbar and drag the map. To return to the previous view of your map, choose View, Previous.

TIP: If you prefer a magnification different from the values in the list, type a magnification. To increase magnification greater than the default, enter a number greater than 100 percent. To decrease magnification, enter a number less than 100 percent.

TROUBLESHOOTING: After zooming in on a data map, the map seems to have disappeared. How do I get it back? You probably zoomed in on a blank area of the map. Click the Display Entire button on the Map toolbar to show the entire map. Then try the zoom procedure again.

Saving Charts

To save a chart that is embedded in a worksheet or that appears as a separate sheet in the workbook, you save the workbook that contains the worksheet.

Steps

  1. Modify the chart as you want.

  2. Click the Save button on the Standard toolbar.

  3. If the Save As dialog box appears, type a name for the workbook that contains the chart, then click Save.

NOTE: When you format and enhance a chart in Excel 97 and save it as an earlier version file, you may lose some of the formatting or enhancements. This is due to incompatibilities between Excel 97 and earlier versions of Excel.

TOCBACKFORWARDHOME


©Copyright, Macmillan Computer Publishing. All rights reserved.