MICROSOFT EXCEL – Lab 2

LEARNING ABOUT CHARTS - A chart is a visual representation of data in a worksheet.

Types of Charts

· Excel can create 14 different types of charts.

· Each chart contains several accompanying formats, and each represents data in a different manner. For example, a line chart demonstrates change over time. A pie chart displays the relative contribution of each part to the whole. Bar and column charts display comparisons of several values at one point in time.

SELECTING THE DATA TO CHART

Chart Elements

Most charts, except the pie, have the same basic characteristics:

· The bottom boundary of the chart is called the X-axis. It is used to label charted data with category names that are taken from worksheet data and help describe the numbers that are plotted in the chart.

· The Y-axis is the left boundary of the chart. It is a numbered scale whose values are determined by the plotted numbers.

· The chart itself is drawn in the plot area within the X- and Y-axes.

o Each related group of numbers in a column or row is called a data series.

o Each number in a data series is represented by a different color, pattern, and/or symbol, called a data marker. Examples of data markers are lines in a line chart, bars in a bar chart, or slices of a pie chart.

o A legend displays the individual data markers and corresponding labels so a person reading the chart knows how each value is represented.

o Chart gridlines often are displayed to ease the reading of the chart data.

· Charts generally can have as many as three titles to describe the contents of the chart.

o A chart title or main title appears centered at the top of the chart.

o A category-axis title is displayed along the X-axis to describe its labels.

o A value-axis title is displayed along the Y-axis to describe chart values.

· A pie chart does not have axes. Instead, each number is displayed as a slice of a pie, representing a part to whole relationship. Each slice is labeled and has a different color or pattern.

· A 3-D chart has an additional axis--the Z-axis, which is the numbered scale. The X and Y-axes delineate the horizontal surface of the chart, and the Z-axis shows depth.

Charts are drawn from data you select within a worksheet. Selected data includes:

· Numbers used to draw the chart.

· Text entries you want to use in category labels or a legend.

· You can select ranges that are adjacent or non-adjacent. A selection of non-adjacent ranges is called a non-contiguous selection and must be the same size and shape.

To select two non-contiguous ranges:

· Select: The first range (e.g., B5:F5).

· Hold down: [Ctrl] key.

· Select: The second range (e.g., B10:F10).

To insert a chart into the worksheet:

· Click: Chart Wizard

· The ChartWizard - Step 1 of 4 dialog box is displayed.

ChartWizard takes you step-by-step through the process of creating a chart.

· The first step is to define the range that the program will use to draw the chart.

· Choose: to move to the next step.

· The Step 1 dialog box displays the available 14 chart types.

· The column chart is highlighted indicating the chart that the program will create by default. A column chart displays each set of numbers as evenly spaced columns so you can see at a glance how they compare or "measure up" to each other.

· Choose: to accept the selected column chart and move to the next step.

The Step 2 dialog box displays a sample of the chart that will be created.

· The default column chart will print gridlines to line the columns up with the numbers they represent.

· Choose: to accept the selected chart and move to the next step.

· The selected range appears in the Data Range text box.

The two Series In options control boxes control how Excel displays and interprets the data.

Step 3 Chart Options

To enter a chart title:

· Select: Chart Title

· Type: The title (e.g., Downtown Internet Café Sales).

To enter an X-axis title:

· Select: Category (X)

· Type: The X-axis title (e.g., Months).

To enter a Y-axis title:

· Select: Value (Y)

· Type: The Y-axis title (e.g., Total).

The legend and titles will be displayed in the Sample Chart box so you can see the results.

    Choose:

Step 4, the last step, specifies where the chart will be displayed. It may be as a separate sheet or as an object in an existing sheet.

Chart Objects

Chart objects, or more simply objects, are graphic items that can be added to a worksheet and may be sized or moved.

· These objects may be charts, text boxes, or graphics created using the buttons on the Drawing toolbar.

· Drawing objects stack in individual layers as they are added.

When a graphic object is selected, it is surrounded by eight black or white boxes called selection handles. These handles allow for the sizing of an object.

· Pointing and dragging to a new location moves a selected object.

· Choose: when you have completed step 4.

When Step 4 is completed, the chart will be inserted into the worksheet at the specified location and will contain the specified settings.

· Excel assigns a name to each chart as it is inserted into a workbook.

· Charts are named sequentially beginning with Chart 1.

· The chart name is displayed in the name box of the formula bar when the chart is selected.

· The Chart toolbar is automatically opened and displayed in the same location it was when last open. Move the toolbar to the toolbar dock below the Formatting toolbar, if necessary.

· If the chart is not entirely visible, use the scroll bar until it is in view.

MOVING AND SIZING A CHART

· When a chart is initially inserted into a workbook, it is surrounded by selection handles, indicating that it is an object and can be sized, moved, and deleted at this point—like a text box.

· Pointing to the chart object and dragging allows the chart to be moved. The mouse pointer appears as a while dragging to move an object.

· A chart may not display all of the information. For example, the X-axis labels might not be fully displayed. In that case, you will need to size the chart.

o When you point to a side or bottom handle, the mouse pointer turns into and respectively to let you size the chart horizontally and vertically when you drag the mouse.

and pointers size the chart diagonally when you drag a corner chart handle.

o Diagonally sizing the chart lets you maintain the chart’s proportions when you change its size.

o Drag a corner with a sizing mouse pointer (e.g., ) so the chart covers a larger area.

o As you drag the mouse, the mouse pointer turns into a +, and a dotted outline border indicates the size of the chart as you move the mouse pointer.

o Release the mouse button when the chart is the size you want.

CHANGING THE TYPE OF CHART - Once a chart is created, you can easily change its type. For example, you may decide that it is more appropriate to demonstrate change over time with a line chart than it is to show how numbers measure up to each other in a column chart. Since several chart types share the same settings, you can change the chart to suit your purpose with a click of the mouse.

To change the type of a chart:

· Select: The chart.

· Click: Chart Type on the Chart toolbar.

· Select: The chart type (e.g., ) from the displayed palette.

MOVING THE CHART LOCATION -Moving the chart to another location allows you to make a new chart in the same workbook. With a chart selected, the Data menu changes to the Chart menu.

· Choose: Chart/Location.

· Choose: As new sheet.

· Click: .

· The chart is displayed as a separate chart sheet and is linked to the data from which it was created.

CREATING A CHART WITH MULTIPLE DATA SERIES - You can use the ChartWizard button on the Standard toolbar as a shortcut to the Insert/Chart command. This command will embed the chart into the current worksheet.

To embed a chart with the ChartWizard button:

Select: The range that you want to include in the chart (e.g., A9:D13).

    · Click: ChartWizard.

The ChartWizard – Step 1 dialog box is displayed.

· Select: The chart type you want to use (e.g., Line), and choose .

· Make sure that the displayed range is correct, and choose .

· Enter appropriate titles.

· Choose: .

APPLYING A CUSTOM CHART TYPE -An area chart has the characteristics of both a line and a stacked-column chart. It shows trends over time by representing each data series as a proportionally thick "line," which has the effect of showing the relative contribution to the whole over time. To change the stacked-column chart to an area chart:

· Click: Chart Type.

· Click: Area Chart.

In a stacked-column chart, each set of related data (a column or a row) is represented by a column, sub-divided into stacks representing each value in the range. This type of chart is effective for showing a part-to-whole relationship for several data series. To change the type to a stacked-column chart:

· Choose: Chart/Chart Type.

· Click: Column.

· To change the column chart to a stacked-column chart:

· Click: Stacked-Column with 3-D visual effect.

· Click and hold with a 3-D visual effect.

· Release the mouse button.

Open the Custom Types tab to see other types of available charts.

 

ADDING CHART TITLES - When you create a custom chart type, it will not display titles.

To include text in the chart:

· Activate the chart.

· If the chart extends beyond the active window, it will be displayed in its own sheet.

To add a title to the chart:

· Choose: Chart/Chart Options.

· Choose: Titles tab.

A text box (displaying the word Title as a placeholder) is displayed above the chart.

· Click in the box with the I-beam mouse pointer to display the insertion point.

· Delete the placeholder.

· Type: Desired title.

MOVING THE LEGEND

To move a legend from its default position (at the right edge of the chart) to the bottom:

· Click: The legend to select it.

· Click: Format Legend.

· Choose: Placement/Bottom/.

You can drag the legend to a new location and resize it.

FORMATTING CHART ELEMENTS - Charts are made up of separate objects.

Group - A group is two or more objects that are treated as a single object.

The legend is a group object consisting of separate items identifying different data series.

After selecting a group, it can be formatted or edited.

As different objects are selected, the Format menu reflects commands used on the selected object.

 The Format Object button can be used to format the selected object.

To format the title:

    Select: Chart title.

    Click: Format Chart Title.

    Select desired font, size, and color.

    Click: .

To format axis titles:

Select: Desired axis title.

Click Font color on Formatting toolbar.

Double-click on an object to select it and open the Format dialog box.

COPYING A CHART

After formatting the chart, it can be copied so the original can be further modified.

Select the entire chart.

    Click: Copy.

    Click: In a new cell.

    Click: Paste.

CREATING A COMBINATION CHART

You can create a combination chart to convey two types of information in one chart. For example, you can display values for several categories as a column chart so you can see how each category compares to the rest. Then, you can display a total or projected series of values as a line chart to display an upward or downward trend.

    Select: The desired data series.

    Click: Chart Type drop-down menu.

    Select: Line.

CHANGING FILL COLORS

To make the chart easier to read, change line and fill colors by:

Double-click on a line to select it and open the Format dialog box.

Select a new line color and select a new foreground and background color.

Click: .

To change the plot area color:

  1) Select the plot area.

            2) Click: Fill Color.

            3) Select new color.

CHANGING WORKSHEET DATA -  The worksheet data can be changed after charts are made. The worksheet recalculates and charts referencing the cells are redrawn to reflect changes in data.

ADDING DATA LABELS - Data labels are the actual plotted numbers that can be included in the chart in corresponding locations to clarify the information that the chart is trying to convey.

To include data labels in a chart:

    1) Double-click a data series.

    2) Open: The Data Labels tab.

    3) Select: Show value.

    4) Click: .

ADDING A TEXT BOX - A text box is a rectangular object in which text is typed.  The chart must be selected for the text box to be added to it.  The text box will move with the chart when it is moved.

The procedure for adding a text box to a chart is the same as adding a text box to a worksheet.

    1) Click: Drawing from the Standard toolbar.

    2) Click: Text Box.

    3) Use the + mouse pointer to draw the box.

    4) Type: The text in the box.

Once created, a text box can be selected, moved (with the mouse pointer), and sized (with the , , or mouse pointers).

Items in text boxes may be formatted once selected.

The hatched border surrounding the text box indicates that text can be entered, deleted, selected, and formatted.

Click the border of the hatched text box to turn off text editing.

Use the , Font Color button, or , Fill Color button, to add color effects to the text box.

ADDING ARROWS -  You can add emphasis to a chart by drawing an arrow from a text box to a portion of the chart, such as a data series.

To create an arrow:

1) Click: Arrow from the Drawing toolbar.

2) The mouse pointer turns into a +.

3) Point to the edge of the object (e.g., the left edge of the text box).

4) Drag: To the item you want to point the arrow to (e.g., the Internet line).

5) Release the mouse button.

6) The arrow is a selected object with handles at both ends so you can size or move it, if necessary.

7) To change line color, click and select a new color.

8) To change line style, click and select new line weight.

Hide the Drawing toolbar when you are finished.

CREATING A PIE CHART -  A pie chart compares parts to the whole displaying each value in the data series as a slice of the pie.

1) The X-axis labels each slice.

2) Since only one data series can be specified, the pie chart will usually have different settings from the other chart types. In this case, you will have to create a new chart.

To create a pie chart using the Chart Type button:

1) Select: The range.

2) Display the Chart toolbar.

3) Click: Chart Type.

Choose: Pie Chart.

Move and size the chart, if necessary.

FORMATTING THE PIE CHART - A pie chart does not contain axis lines so it has only a main title.

1) Choose: Chart/Chart Options.

2) Enter: The title in the Chart Title text box.

Remove the X from the Show legend box in the Legend tab.

Use the Data Labels tab to Show Label and Percent.

EXPLODING AND ROTATING THE PIE -  To make a slice stand out, explode or separate it.

1) Select: The slice that will be exploded.

2) Drag: The slice away from the pie.

If all slices are selected, dragging one explodes all slices at the same time.

The chart can be rotated to change the order in which slices are displayed.

1) Double-click the desired data series.

2) Open the Options tab.

3) Change the angle of a slice setting and click .

APPLYING PATTERNS AND COLOR - A color monitor will display data markers (each line, each column, etc.) in different colors. If you have a monochrome monitor, the markers will be displayed in varying shades of gray. If you do not have a color printer, the markers will print in varying shades of gray as well. This makes it difficult to distinguish each area of the chart.

If you have a monochrome monitor and/or a black and white printer, you might want to display black and white patterns instead of colors. Make sure the chart is activated. For each data series:

1) Select: The data series and display its shortcut menu by right-clicking.

2) Select: The Patterns tab, if necessary.

3) Click Fill Effects.

4) Select: The Pattern tab.

5) Change gradients, textures, patterns, and pictures from the Fill Effects dialog box.

6) Select desired pattern from the Pattern Palette.

7) Choose: twice.

DOCUMENTING A WORKBOOK - A workbook includes summary information that is associated with the file.

1) Choose: File/Properties.

2) Select each tab and look at the recorded information.

3) Open: The Summary tab.

Information such as title, subject, author, keywords, and comments helps to document the workbook and is saved with the workbook.

PREVIEWING THE WORKBOOK -  It is important to preview how charts will print before printing.

To preview the entire workbook, change the print setting:

    1) Choose: File/Print/Entire workbook.

    2) Click: button.

To enhance a chart prior to printing:

1) Click: The Setup button.

2) Open: The Chart tab.

3) Size the chart to fill the page.

4) Select appropriate options for color or black-and-white printer.

5) Click: .

Click to view the next sheets in the workbook.

SIZING THE WORKSHEET

To reduce a worksheet and charts to fit on one page:

1) Click: The Setup button.

2) Open: The Page tab.

3) Select: Fit to.

ALIGNING A SHEET ON A PAGE

To center a worksheet horizontally on a page:

    Open: The Margins tab.

    Select: Horizontally.

ADDING PREDEFINED HEADERS AND FOOTERS -  A header is a line of text that appears at the top of each page just below the top margin. A footer is a line of text that appears at the bottom of each page just above the bottom margin.

To insert a header:

    1) Open: The Header/Footer tab.

    2) Select: The desired Header from the Header drop-down list box.

    3) Click: .

PRINTING THE WORKBOOK

To print a workbook:

    1) Click: .

    2) Choose the appropriate printer.

    3) Click: .