Chapter 4.1

4 key steps before constructing an excel chart

1, define your message 2. identif the data you need 3. select a chart type 4. select what will go on the X and Y axes

skill refresher: Charts: using data in a noncontiguous Range

1. Click a blank cell location that is not adjacent to any data on the worksheet. 2. Click the Insert tab of the Ribbon. 3. Select a chart type and format in the Charts group of commands. 4. Click the Select Data button in the Design tab of the Chart Tools section of the Ribbon. 5. Click the Add button on the 6. Select Data Source dialog box. 6. In the Edit Series dialog box, type a name in the Series name input box or highlight a cell location on the worksheet that contains a description for the data series. 7. Press the TAB key on your keyboard to advance to the Series values input box. 8. Highlight the range of cells on the worksheet that contain the data that will appear on the Y axis for the series identified in step 6. 9. Click the OK button on the Edit Series dialog box. Repeat steps 5 through 9 for each data series that you need to add to the chart. 10. Click the Edit button on the right side of the Select Data Source dialog box. 11. Highlight the range of cells that contain the descriptions for the X axis. 12. Click the OK button on the Axis Labels dialog box. 13. Click the OK button on the Select Data Source dialog box.

skill refresher adjusting the Y axis s

1. Click anywhere along the Y axis to activate it. 2. Click the Format tab in the Chart Tools section of the Ribbon. 3. Click the Format Selection button in the Current Selection group of commands. 4. In the Format Axis dialog box, click the Fixed option next to any axis option where you wish to change the value. 5. Click in the input box next to the desired axis option and then type the new scale value. 6. Click the Close button at the bottom of the Format Axis dialog box.

inserting a stacked column chart skill refresher

1. Highlight a range of cells that contain data that will be used to create the chart. 2. Click the Insert tab of the Ribbon. 3. Click the Column button in the Charts group. 4. Select the Stacked Column format option from the Column Chart drop-down menu to show the values of each category on the Y axis. Select the 100% Stacked Column option to show the percent of total for each category on the Y axis.

skill refresher: inserting a pie chart

1. Highlight a range of cells that contain the data you will use to create the chart. 2. Click the Insert tab of the Ribbon. 3. Click the Pie button in the Charts group. 4. Select a format option from the Pie Chart drop-down menu.

3 key points before creating any chart in excel

1. idetnifying your idea or message 2. electing the right chart type 3. idenitfying the values taht should appear on X and Y axes

skill refresher intserting a line chart

1.Highlight a range of cells that contain data that will be used to create the chart. 2.Click the Insert tab of the Ribbon. 3.Click the Line button in the Charts group. 4.Select a format option from the Line Chart drop-down menu.

4. idetnify the X and Y axes

After you have selected a chart type, you may find that drawing a sketch is helpful in identifying which values should be on the X and Y axes. (The X axis is horizontal, and the Y axis is vertical.)

too many bars on a column chart?

Although there is no specific limit for the number of bars you should use on a column chart, a general rule of thumb is twenty bars or less.

adjusting the chart title

Click the chart title once. Then click in front of the letter S. You should see a blinking cursor in front of the letter S. This allows you to modify the title of the chart.

removing the legend

Click the legend once and press the DELETE key on your keyboard. This removes the legend from the chart. Since the chart contains only one data series, the legend is not necessary. Once you remove the legend, the plot area automatically expands.

1. define your message

Identify the main idea you are trying to communicate to an audience. If there is no main point or important message that can be revealed by a chart, you might want to question the necessity of creating a chart.

2. identify the data you need

Once you have a clear message, identify the data on a worksheet that you will need to construct a chart. In some cases, you may need to create formulas or consolidate items into broader categories.

chart sheet

an excel worksheet that is designated for a chart

embedded chart

any chart that is created and placed within a worksheet

column chart begins to be diffucult to read

as the number of bars exceeds 20

line chart vs. column chart

can use both to illustrate a trend over time. however, a line chart is far more effecctive when there are many periods of time being measured

moving a chart

click and drag the upper left corner of the chart

when using line charts in excel, keep in mind that anything placed on the X axis is considered a

descriptive label, not a numeric value his is important because there will never be a change in the spacing of any items placed on the X axis of a line chart. If you need to create a line chart using numeric data on the X axis, you must use a scatter chart type.

When creating a chart to compare the trends of two or more data series, the values for each data

eries must be compatible. In other words, the values for each data series must be within a reasonable range in order for an effective comparison to be made If the variance between the values in your data series is never less than a multiple of 2 (i.e., 500 × 2 = 1000 or 1000 ÷ 2 = 500), calculate the percent change for each point in time on your worksheet. The percent change must be calculated with respect to the first data point for each series. Then create your chart using the percentages instead of the actual values for each data series.

contextual tabs

hidden tabs on the ribbon that contain commands related to a specific object.

X axis

horizontal

chart sheets are useful when

if you need to create several charts using the data in a single worksheet

frequenc distrubtions

number of occurences for an established set of categories

resizing a chart

place the mouse pointer over the left middle sizing handle, hold down the alt ke on your keyboard, and click and drage the chart so it snaps to the left side of the column

column chart vs. bar chart

the difference between a column chart and a bar chart is really a matter of preference. Both are very effective in showing frequency distributions. However, if you are showing a trend over a period of time, a column chart is preferred over a bar chart. This is because a period of time is typically shown horizontally, with the oldest date on the far left and the newest date on the far right. Therefore, the descriptive categories for the chart would have to fall on the X axis, which is the configuration of a column chart. On a bar chart, the descriptive categories are displayed vertically along the Y axis.

when presenting the trend for an data the most commonly used chart is

the line chart

contextual tabs become visible hwne

the related object is aded or activated

3. select a chart type

the type of chart you select will depend on the message you are communicating and the data you are using

column chart is commonly used

to show trends over time so long as the data are limited to approximately twenty points or less

stacked column chart

used to show how a percent of total changes over time or between two or more entities

pie chart

used to show the percent of total for each component of a data set

Y axis

vertical

carefully select data when creating a chart

when creating a charrt it is common for only specific data points to be used

with the column chart

you are limited on a certain number of bars or data points