excel model assigment
CS_150_Excel Module 4 Assignment 1011 Instructions.docx
CS 150
CS_150_Excel Module 4 Assignment 10 Instructions
IMPORTANT NOTE : Take your time to do this assignment; it introduces you in many detailed steps how to work with charts in Excel.
1. Download the data files for this assignment (EX 4-1.xlsx).
· Download it by right clicking on the file name and then save it in your flash drive.
· You will start the assignment by opening this file and saving it with the name listed in one of the first steps.
2. Download the zipped (compressed) file EA 10 Instructions 1-8.zip. Extract all the 8 files from it. Read and study the material in the 1st file EA 10 Instructions 1. Follow the steps in each of the extracted files, starting with the steps in EA 10 Instructions 1 file and follow the steps in each of the following files until you finish them with the steps in the last file EA 10 Instructions 8.
3. After finishing all steps, upload and submit for grading the resulting Excel file in Blackboard by clicking the link (EA 10) to this assignment.
EA 10 Instructions 6.docx
4-6 Format a Chart
Learning Outcomes
· Change the fill of a data series
· Use Live Preview to see a new data series color
· Apply a style to a data series
Formatting a chart can make it easier to read and understand. Many formatting enhancements can be made using the Chart Tools Format tab. You can change the fill color for a specific data series, or you can apply a shape style to a title or a data series using the Shape Styles group. Shape styles make it possible to apply multiple formats, such as an outline, fill color, and text color, all with a single click. You can also apply different fill colors, outlines, and effects to chart objects using arrows and buttons in the Shape Styles group. You want to use a different color for one data series in the chart and apply a shape style to another, to enhance the look of the chart.
Steps
Quick Tip
You can change the chart type of a selected data series by clicking the Chart Tools Design tab on the Ribbon, clicking the Change Chart Type button in the Type group, selecting a chart type for that data series, then clicking OK.
1.
With the chart selected, click the Chart Tools Format tab on the Ribbon, then click any column in the Quarter 4 data series
Handles appear on each column in the Quarter 4 data series, indicating that the entire series is selected.
2.
Click the Shape Fill list arrow in the Shape Styles group on the Chart Tools Format tab
3.
Click Orange, Accent 6 (first row, 10th from the left) as shown in Figure 4-15
Figure 4-15 New Shape Fill Applied to Data Series
All the columns for the series become orange, and the legend changes to match the new color. You can also change the color of selected objects by applying a shape style.
4.
Click any column in the Quarter 3 data series
Handles appear on each column in the Quarter 3 data series.
5.
Click the More button on the Shape Styles gallery, then hover the pointer over the Moderate Effect – Olive Green, Accent 3 shape style (fifth row, fourth from the left) in the gallery, as shown in Figure 4-16
Figure 4-16 Live Preview of New Style Applied to Data Series
Live Preview shows the data series in the chart with the shape style applied.
Quick Tip
To apply a WordArt style to a text object (such as the chart title), select the object, then click a style in the WordArt Styles group on the Chart Tools Format tab.
6.
Click the Subtle Effect – Olive Green, Accent 3 shape style
The style for the data series changes, as shown in Figure 4-17 .
Figure 4-17 Style of Data Series Changed
7.
Save your work
Previewing a Chart
Changing Alignment and Angle in Axis Labels and Titles
The buttons on the Chart Tools Design tab provide a few options for positioning axis labels and titles, but you can customize their position and rotation to exact specifications using the Format Axis pane or Format Axis Title pane. With a chart selected, right-click the axis text you want to modify, then click Format Axis or Format Axis Title on the shortcut menu. In the pane that opens, click the Size & Properties button, then select the appropriate option. You can also create a custom angle by clicking the Custom angle up and down arrows. When you have made the desired changes, close the pane.
2
EA 10 Instructions 3.docx
4-3 Move and Resize a Chart
Learning Outcomes
· Reposition a chart
· Resize a chart
· Modify a legend
· Modify chart data
A chart is an object, or an independent element on a worksheet, and is not located in a specific cell or range. You can select an object by clicking it; sizing handles around the object indicate it is selected. (When a chart is selected in Excel, the Name box, which normally tells you the address of the active cell, tells you the chart number.) You can move a selected chart anywhere on a worksheet without affecting formulas or data in the worksheet. Any data changed in the worksheet is automatically updated in the chart. You can even move a chart to a different sheet in the workbook, and it will still reflect the original data. You can resize a chart to improve its appearance by dragging its sizing handles. You can reposition chart objects (such as a title or legend) to predefined locations using commands using the Chart Elements button or the Add Chart Element button on the Chart Tools Design tab, or you can freely move any chart object by dragging it or by cutting and pasting it to a new location. When you point to a chart object, the name of the object appears as a ScreenTip. You want to resize the chart, position it below the worksheet data, and move the legend.
Steps
Quick Tip
To delete a selected chart, press [Delete].
1.
Make sure the chart is still selected, then position the pointer over the chart
The pointer shape indicates that you can move the chart. For a table of commonly used object pointers, refer to Table 4-2.
Table 4-2
Common Object Pointers
|
name |
pointer |
use |
name |
pointer |
use |
|
Diagonal resizing |
or |
Change chart shape from corners |
I-beam |
|
Edit object text |
|
Draw |
|
Draw an object |
Move |
|
Move object |
|
Horizontal resizing |
|
Change object width |
Vertical resizing |
|
Change object height |
Trouble
Dragging a chart element instead of a blank area moves the element instead of the chart; if this happens, undo the action and try again.
2.
Position on a blank area near the upper-left edge of the chart, press and hold the left mouse button, drag the chart until its upper-left corner is at the upper-left corner of cell A16, then release the mouse button
When you release the mouse button, the chart appears in the new location.
3.
Scroll down so you can see the whole chart, position the pointer on the right-middle sizing handle until it changes to , then drag the right border of the chart to the right edge of column G
The chart is widened. See Figure 4-7.
Figure 4-7 Moved and Resized Chart
Quick Tip
To resize a selected chart to an exact size, click the Chart Tools Format tab, then enter the desired height and width in the Size group.
4.
Position the pointer over the upper-middle sizing handle until it changes to , then drag the top border of the chart to the top edge of row 15
5.
Position the pointer over the lower-middle sizing handle until it changes to , then drag the bottom border of the chart to the bottom border of row 26
You can move any object on a chart. You want to align the top of the legend with the top of the plot area.
Quick Tip
You can move a legend to the right, top, left, or bottom of a chart by clicking Legend in the Add Chart Element button in the Chart Layouts group on the Chart Tools Design tab, then clicking a location option.
6.
Click the Quick Layout button in the Chart Layouts group of the Chart Tools Design tab, click Layout 1 (in the upper-left corner of the palette), click the legend to select it, press and hold [Shift], drag the legend up using so the dotted outline is approximately 1/4” above the top of the plot area, then release [Shift]
When you click the legend, sizing handles appear around it and “Legend” appears as a ScreenTip when the pointer hovers over the object. As you drag, a dotted outline of the legend border appears. Pressing and holding the [Shift] key holds the horizontal position of the legend as you move it vertically. Although the sizing handles on objects within a chart look different from the sizing handles that surround a chart, they function the same way.
7.
Click cell A12, type United States, click the Enter button on the formula bar, use AutoFit to resize column A, then save your work
The axis label changes to reflect the updated cell contents, as shown in Figure 4-8. Changing any data in the worksheet modifies corresponding text or values in the chart. Because the chart is no longer selected, the Chart Tools tabs no longer appear on the Ribbon.
Figure 4-8 Worksheet With Modified Legend and Label
Moving an Embedded Chart to a Sheet
Suppose you have created an embedded chart that you decide would look better on a chart sheet or in a different worksheet. You can make this change without recreating the entire chart. To do so, first select the chart, click the Chart Tools Design tab, then click the Move Chart button in the Location group. The Move Chart dialog box opens. To move the chart to its own chart sheet, click the New sheet option button, type a name for the new sheet if desired, then click OK. If the chart is already on its own sheet or you want to move it to a different existing sheet, click the Object in option button, click the desired worksheet, then click OK.
2
EX 4-1.xlsx
Sheet1
| Reason2Go | |||||
| FY 2017 Quarterly Tour Expenses | |||||
| Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | Total | |
| Australia | 6,076.20 | 6,173.82 | 7,207.66 | 6,624.19 | $ 26,081.87 |
| Brazil | 3,610.99 | 4,021.46 | 4,437.40 | 4,658.11 | $ 16,727.96 |
| Britain | 4,387.78 | 4,471.98 | 4,670.21 | 4,200.04 | $ 17,730.01 |
| France | 4,132.10 | 4,589.74 | 4,679.06 | 4,793.72 | $ 18,194.62 |
| Germany | 5,182.77 | 3,094.56 | 3,661.12 | 3,812.50 | $ 15,750.95 |
| India | 1,568.25 | 2,610.30 | 2,765.04 | 2,990.95 | $ 9,934.54 |
| Malaysia | 3,371.50 | 3,665.14 | 8,340.35 | 3,821.89 | $ 19,198.88 |
| U.S.A. | 7,295.06 | 6,642.76 | 8,340.46 | 7,118.91 | $ 29,397.19 |
| Total | $ 35,624.65 | $ 35,269.76 | $ 44,101.30 | $ 38,020.31 | $ 153,016.02 |
EA 10 Instructions 2.docx
4-2 Create a Chart
Learning Outcomes
· Create a chart
· Switch a chart’s columns/rows
· Add a chart title
To create a chart in Excel, you first select the range in a worksheet containing the data you want to chart. Once you’ve selected a range, you can use The Quick Analysis tool or the Insert tab on the Ribbon to create a chart based on the data in the range. Using the worksheet containing the quarterly expense data, you create a chart that shows how the expenses in each country varied across the quarters.
Steps
Quick Tip
When charting data for a particular time period, make sure that all series are for the same time period.
1.
Start Excel, open the file EX 4-1.xlsx from the location where you store your Data Files, then save it as EX 4-R2G Quarterly Tour Expenses
You want the chart to include the quarterly tour expenses values, as well as quarter and country labels. You don’t include the Total column and row because the figures in these cells would skew the chart.
2.
Select the range A4:E12, click the Quick Analysis tool in the lower-right corner of the range, then click Charts
The Charts tab on the Quick Analysis tool recommends commonly used chart types based on the range you have selected. The Charts tab also includes a More Charts button for additional chart types, such as stock charts for charting stock market data.
Quick Tip
To base a chart on data in nonadjacent ranges, press and hold [Ctrl] while selecting each range, then use the Insert tab to create the chart.
3.
On the Charts tab, verify that Clustered Column is selected, as shown in Figure 4-3 , then click Clustered Column
Figure 4-3 Charts Tab in Quick Analysis Tool
The chart is inserted in the center of the worksheet, and two contextual Chart Tools tabs appear on the Ribbon: Design and Format. On the Design tab, which is currently active, you can quickly change the chart type, chart layout, and chart style, and you can swap how the columns and rows of data in the worksheet are represented in the chart. When seen in the Normal view, three tools display to the right of the chart: these enable you to add, remove, or change chart elements , set a style and color scheme , and filter the results shown in a chart . Currently, the countries are charted along the horizontal x-axis, with the quarterly expense dollar amounts charted along the y-axis. This lets you easily compare the quarterly expenses for each country.
4.
Click the Switch Row/Column button in the Data group on the Chart Tools Design tab
The quarters are now charted along the x-axis. The expense amounts per country are charted along the y-axis, as indicated by the updated legend. See Figure 4-4 .
Figure 4-4 Clustered Column Chart With Different Configuration of Rows and Columns
5.
Click the Undo button on the Quick Access Toolbar
The chart returns to its original design.
Quick Tip
You can also triple-click to select the chart title text.
6.
Click the Chart Title placeholder to show the text box, click anywhere in the Chart Title text box, press [Ctrl][A] to select the text, type R2G Quarterly Tour Expenses, then click anywhere in the chart to deselect the title
Adding a title helps identify the chart. The border around the chart and the sizing handles , the small series of dots at the corners and sides of the chart’s border, indicate that the chart is selected. See Figure 4-5 . Your chart might be in a different location on the worksheet and may look slightly different; you will move and resize it in the next lesson. Any time a chart is selected, as it is now, a blue border surrounds the worksheet data range on which the chart is based, a purple border surrounds the cells containing the category axis labels, and a red border surrounds the cells containing the data series labels. This chart is known as an embedded chart because it is inserted directly in the current worksheet and doesn’t exist in a separate file. Embedding a chart in the current sheet is the default selection when creating a chart, but you can also embed a chart on a different sheet in the workbook, or on a newly created chart sheet. A chart sheet is a sheet in a workbook that contains only a chart that is linked to the workbook data.
Figure 4-5 Chart With Original Configuration Restored and Title Added
7.
Creating Sparklines
You can quickly create a miniature chart called a sparkline that serves as a visual indicator of data trends. You can create a sparkline by selecting a range of data, clicking the Quick Analysis tool, clicking the Sparklines tab, then clicking the type of sparkline you want. (The sparkline appears in the cell immediately adjacent to the selected range.) You can also select a range, click the Insert tab, then click the Line, Column, or Win/Loss button in the Sparklines group. In the Create Sparklines dialog box that opens, enter the cell in which you want the sparkline to appear, then click OK. Figure 4-6 shows a sparkline created in a cell. Any changes to data in the range are reflected in the sparkline. To delete a selected sparkline from a cell, click the Clear button in the Group group on the Sparkline Tools Design tab.
Figure 4-6 Sparklines in a Cell
2
EA 10 Instructions 5.docx
4-5 Change the Chart Format
Learning Outcomes
· Change the gridlines display
· Add axis titles
· Change the border color
· Add a shadow to an object
While the Chart Tools Design tab contains preconfigured chart layouts you can apply to a chart, the Chart Elements button makes it easy to add, remove, and modify individual chart objects such as a chart title or legend. Using options on this shortcut menu (or using the Add Chart Element button on the Chart Tools Design tab), you can also add text to a chart, add and modify labels, change the display of axes, modify the fill behind the plot area, create titles for the horizontal and vertical axes, and eliminate or change the look of gridlines. You can format the text in a chart object using the Home tab or the Mini toolbar, just as you would the text in a worksheet. You want to change the layout of the chart by creating titles for the horizontal and vertical axes. To improve the chart’s appearance, you’ll add a drop shadow to the chart title.
Steps
1.
With the chart still selected, click the Add Chart Element button in the Chart Layouts group on the Chart Tools Design tab, point to Gridlines, then click Primary Major Horizontal to deselect it
The gridlines that extend from the value axis tick marks across the chart’s plot area are removed as shown in Figure 4-12.
Figure 4-12 Gridlines Removed From Chart
2.
Click the Chart Elements button in the upper-right corner outside the chart border, click the Gridlines arrow, click Primary Major Horizontal, click Primary Minor Horizontal, then click to close the Chart Elements fly-out menu
Both major and minor gridlines now appear in the chart. Major gridlines represent the values at the value axis tick marks, and minor gridlines represent the values between the tick marks.
Quick Tip
You can move any title to a new position by clicking one of its edges, then dragging it.
3.
Click , click the Axis Titles checkbox to select all the axis titles options, triple-click the vertical axis title on the chart, then type Expenses (in $)
Descriptive text on the category axis helps readers understand the chart.
4.
Triple-click the horizontal axis title on the chart, then type Tour Countries
The text “Tour Countries” appears on the horizontal axis, as shown in Figure 4-13.
Figure 4-13 Axis Titles Added to Chart
Quick Tip
You can also edit text in a chart or axis title by positioning the pointer over the selected title until it changes to , clicking the title, then editing the text.
5.
Right-click the horizontal axis labels (“Australia”, “Brazil”, etc.), click Font on the shortcut menu, click the Latin text font list arrow in the Font dialog box, click Times New Roman, click the Size down arrow until 8 is displayed, then click OK
The font of the horizontal axis labels changes to Times New Roman, and the font size decreases, making more of the plot area visible.
6.
Right-click the vertical axis labels, then click Reset to Match
Quick Tip
You can also apply a border to a selected chart object by clicking the Shape Outline list arrow on the Chart Tools Format tab, and then selecting from the available options.
7.
Right-click the Chart Title (“R2G Quarterly Tour Expenses”), click Format Chart Title on the shortcut menu, click the Border arrow in the Format Chart Title pane to display the options if necessary, then click the Solid line option button in the pane
A solid border appears around the chart title with the default blue color.
Quick Tip
You can also apply a shadow to a selected chart object by clicking the Shadow arrow, then clicking a shadow effect.
8.
Click the Effects button in the Format Chart Title pane, click Shadow, click the Presets list arrow, click Offset Diagonal Bottom Right in the Outer group (first row, first from the left), click the Format Chart Title pane Close button , then save your work
A blue border with a drop shadow surrounds the title. Compare your work to Figure 4-14.
Figure 4-14 Enhanced Chart
Adding Data Labels to a Chart
There are times when your audience might benefit by seeing data labels on a chart. These labels appear next to the data markers in the chart and can indicate the series name, category name, and/or the value of one or more data points. Once your chart is selected, you can add this information to your chart by clicking the Chart Elements button in the upper-right corner outside the selected chart, clicking the Data Labels arrow, and then clicking a display option for the data labels. Once you have added the data labels, you can format them or delete individual data labels. To delete a data label, select it and then press [Delete].
2
EA 10 Instructions 8.docx
4-8 Create a Pie Chart
Learning Outcomes
· Create a pie chart
· Explode a pie chart slice
You can create multiple charts based on the same worksheet data. While a column chart may illustrate certain important aspects of your worksheet data, you may find that you want to create an additional chart to emphasize a different point. Depending on the type of chart you create, you have additional options for calling attention to trends and patterns. For example, if you create a pie chart, you can emphasize one data point by exploding , or pulling that slice away from, the pie chart. When you’re ready to print a chart, you can preview it just as you do a worksheet to check the output before committing it to paper. You can print a chart by itself or as part of the worksheet. At an upcoming meeting, Yolanda plans to discuss the total tour expenses and which countries need improvement. You want to create a pie chart she can use to illustrate total expenses. Finally, you want to fit the worksheet and the charts onto one worksheet page.
Steps
1.
Select the range A5:A12, press and hold [Ctrl], select the range F5:F12, click the Insert tab, click the Insert Pie or Doughnut Chart button in the Charts group, then click 3-D Pie in the chart gallery
The new chart appears in the center of the worksheet. You can move the chart and quickly format it using a chart layout.
2.
Drag the chart so its upper-left corner is at the upper-left corner of cell G1, click the Quick Layout button in the Chart Layouts group of the Chart Tools Design tab, then click Layout 2
The chart is repositioned on the page, and its layout changes so that a chart title is added, the percentages display on each slice, and the legend appears just below the chart title.
3.
Select the Chart Title text, then type R2G Total Expenses, by Country
Trouble
If the Format Data Series command appears on the shortcut menu instead of Format Data Point, doubleclick the slice you want to explode to make sure it is selected by itself, then right-click it again.
4.
Click the slice for the India data point, click it again so it is the only slice selected, right-click it, then click Format Data Point
The Format Data Point pane opens, as shown in Figure 4-22 . You can use the Point Explosion slider to control the distance a pie slice moves away from the pie, or you can type a value in the Point Explosion text box.
Figure 4-22 Format Data Point Pane
5.
Double-click 0 in the Point Explosion text box, type 40, then click the Close button
Compare your chart to Figure 4-23 . You decide to preview the chart and data before you print.
Figure 4-23 Exploded Pie Slice
6.
Click cell A1, switch to Page Layout view, type your name in the left header text box, then click cell A1
You decide the chart and data would fit better on the page if they were printed in landscape orientation.
7.
Click the Page Layout tab, click the Orientation button in the Page Setup group, then click Landscape
8.
Click the File tab, click Print on the navigation bar, verify that the correct printer is selected, click the No Scaling setting in the Settings section on the Print tab, then click Fit Sheet on One Page
The data and chart are positioned horizontally on a single page, as shown in Figure 4-24 . The printer you have selected may affect the appearance of your preview screen.
Figure 4-24 Preview of Worksheet With Charts in Backstage View
9.
Save and close the workbook, submit your work to your instructor as directed, then exit Excel
Using the Insert Chart Dialog Box to Discover New Chart Types
Excel 2016 includes five new chart types. You can explore these charts by clicking the Insert tab on the Ribbon, clicking Recommended Charts, then clicking the All Charts tab in the Insert Chart dialog box. Near the bottom of the list in the left panel are the new chart types: Treemap (which has nine variations), Sunburst, Histogram, Box & Whisker, and Waterfall. If cells are selected prior to opening the Insert Chart dialog box, you will see a sample of the chart type when you click each chart type; the sample will be magnified when you hover the mouse over the sample. The Treemap and Sunburst charts both offer visual comparisons of relative sizes. The Histogram looks like a column chart, but each column (or bin) represents a range of values. The Box & Whisker chart shows distribution details as well as the mean, quartiles, and outliers. The Waterfall chart shows results above and below an imaginary line.
2
EA 10 Instructions 4.docx
4-4 Change the Chart Design
Learning Outcomes
· Change the chart design
· Change the chart type
· Apply a chart style
Once you’ve created a chart, you can change the chart type, modify the data range and column/row configuration, apply a different chart style, and change the layout of objects in the chart. The layouts in the Chart Layouts group on the Chart Tools Design tab offer arrangements of objects in your chart, such as its legend, title, or gridlines; choosing one of these layouts is an alternative to manually changing how objects are arranged in a chart. You discovered that the data for Malaysia and the United States in Quarter 3 is incorrect. After the correction, you want to see how the data looks using different chart layouts and types.
Steps
1.
Click cell D11, type 5568.92, press [Enter], type 7107.09, then press [Enter]
In the chart, the Quarter 3 data markers for Malaysia and the United States reflect the adjusted expense figures. See Figure 4-9 .
Figure 4-9Worksheet With Modified Data
Quick Tip
You can see more layout choices by clicking the More button in the Chart Styles group.
2.
Select the chart by clicking a blank area within the chart border, click the Chart Tools Design tab on the Ribbon, click the Quick Layout button in the Chart Layouts group, then click Layout 3
The legend moves to the bottom of the chart. You prefer the original layout.
3.
Click the Undo button on the Quick Access Toolbar, then click the Change Chart Type button in the Type group
The Change Chart Type dialog box opens, as shown in Figure 4-10 . The left pane of the dialog box lists the available categories, and the right pane shows the individual chart types. A pale gray border surrounds the currently selected chart type.
Figure 4-10 Change Chart Type Dialog Box
4.
Click Bar in the left pane of the Change Chart Type dialog box, confirm that the first Clustered Bar chart type is selected in the right pane, then click OK
The column chart changes to a clustered bar chart. See Figure 4-11 . You decide to see how the data looks in a three-dimensional column chart.
Figure 4-11 Column Chart Changed to Bar Chart
5.
Click the Change Chart Type button in the Type group, click Column in the left pane of the Change Chart Type dialog box, click 3-D Clustered Column (fourth from the left in the top row) in the right pane, verify that the left-most 3-D chart is selected, then click OK
A three-dimensional column chart appears. You notice that the three-dimensional column format gives you a sense of volume, but it is more crowded than the two-dimensional column format.
6.
Click the Change Chart Type button in the Type group, click Clustered Column (first from the left in the top row) in the right pane of the Change Chart Type dialog box, then click OK
Quick Tip
If you plan to print a chart on a black-and-white printer, you may wish to apply a black-and-white chart style to your chart so you can see how the output will look as you work.
7.
Click the Style 3 chart style in the Chart Styles group
The columns change to lighter shades of color. You prefer the previous chart style’s color scheme.
8.
Click on the Quick Access Toolbar, then save your work
Creating a Combo Chart
Working With a 3-D Chart
Excel includes two kinds of 3-D chart types. In a true 3-D chart, a third axis, called the z-axis, lets you compare data points across both categories and values. The z-axis runs along the depth of the chart, so it appears to advance from the back of the chart. To create a true 3-D chart, look for chart types that begin with “3-D,” such as 3-D Column. In a 3-D chart, data series can sometimes obscure other columns or bars in the same chart, but you can rotate the chart to obtain a better view. Right-click the chart, then click 3-D Rotation. The Format Chart Area pane opens with the 3-D Rotation category active. The 3-D Rotation options let you change the orientation and perspective of the chart area, plot area, walls, and floor. The 3-D Format category lets you apply three-dimensional effects to selected chart objects. (Not all 3-D Rotation and 3-D Format options are available on all charts.)
2
EA 10 Instructions 1.docx
4-1 Plan a Chart
Learning Outcomes
· Prepare to create a chart
· Identify chart elements
· Explore common chart types
Before creating a chart, you need to plan the information you want your chart to show and how you want it to look. Planning ahead helps you decide what type of chart to create and how to organize the data. Understanding the parts of a chart makes it easier to format and change specific elements so that the chart best illustrates your data. In preparation for creating the chart for Yolanda’s presentation, you identify your goals for the chart and plan its layout.
Details
Use the following guidelines to plan the chart:
· Determine the purpose of the chart, and identify the data relationships you want to communicate graphically
You want to create a chart that shows quarterly tour expenses for each country where Reason2Go provides tours. This worksheet data is shown in Figure 4-1. You also want the chart to illustrate whether the quarterly expenses for each country increased or decreased from quarter to quarter.
Figure 4-1 Worksheet Containing Expense Data
· Quick Tip
The Quick Analysis tool recommends charts based on the selected data.
Determine the results you want to see, and decide which chart type is most appropriate
Different chart types display data in distinctive ways. For example, a pie chart compares parts to the whole, so it’s useful for showing what proportion of a budget amount was spent on tours in one country relative to what was spent on tours in other countries. A line chart, in contrast, is best for showing trends over time. To choose the best chart type for your data, you should first decide how you want your data displayed and interpreted. Table 4-1 describes several different types of charts you can create in Excel and their corresponding buttons on the Insert tab on the Ribbon. Because you want to compare R2G tour expenses in multiple countries over a period of four quarters, you decide to use a column chart.
Table 4-1
Common Chart Types
|
type |
button |
description |
|
Column |
|
Compares data using columns; the Excel default; sometimes referred to as a bar chart in other spreadsheet programs |
|
Line |
|
Compares trends over even time intervals; looks similar to an area chart, but does not emphasize total |
|
Pie |
|
Compares sizes of pieces as part of a whole; used for a single series of numbers |
|
Bar |
|
Compares data using horizontal bars; sometimes referred to as a horizontal bar chart in other spreadsheet programs |
|
Area |
|
Shows how individual volume changes over time in relation to total volume |
|
Scatter |
|
Compares trends over uneven time or measurement intervals; used in scientific and engineering disciplines for trend spotting and extrapolation |
|
Combo |
|
Displays two or more types of data using different chart types; illustrates mixed or widely varying types of data |
· Identify the worksheet data you want the chart to illustrate
Sometimes you use all the data in a worksheet to create a chart, while at other times you may need to select a range within the sheet. The worksheet from which you are creating your chart contains expense data for each of the past four quarters and the totals for the past year. You will need to use all the quarterly data except the quarterly totals.
· Understand the elements of a chart
The chart shown in Figure 4-2 contains basic elements of a chart. In the figure, R2G tour countries are on the horizontal axis (also called the x-axis ) and expense dollar amounts are on the vertical axis (also called the y-axis ). The horizontal axis is also called the category axis because it often contains the names of data groups, such as locations, months, or years. The vertical axis is also called the value axis because it often contains numerical values that help you interpret the size of chart elements. (3-D charts also contain a z-axis , for comparing data across both categories and values.) The area inside the horizontal and vertical axes is the plot area . The tick marks , on the vertical axis, and gridlines (extending across the plot area) create a scale of measure for each value. Each value in a cell you select for your chart is a data point . In any chart, a data marker visually represents each data point, which in this case is a column. A collection of related data points is a data series . In this chart, there are four data series (Quarter 1, Quarter 2, Quarter 3, and Quarter 4). Each is made up of column data markers of a different color, so a legend is included to make it easy to identify them.
Figure 4-2 Chart Elements
2
EA 10 Instructions 7.docx
4-7 Annotate and Draw on a Chart
Learning Outcomes
· Type text in a text box
· Draw an arrow on a chart
· Modify a drawn object
You can use text annotations and graphics to point out critical information in a chart. Text annotations are labels that further describe your data. You can also draw lines and arrows that point to the exact locations you want to emphasize. Shapes such as arrows and boxes can be added from the Illustrations group on the Insert tab or from the Insert Shapes group on the Chart Tools Format tab on the Ribbon. The Insert group is also used to insert pictures into worksheets and charts. You want to call attention to the Germany tour expense decrease, so you decide to add a text annotation and an arrow to this information in the chart.
Steps
1.
With the chart selected and the Chart Tools Format tab active, click the Text Box button in the Insert Shapes group, then move the pointer over the worksheet
The pointer changes to , indicating that you will insert a text box where you next click.
Quick Tip
You can also insert a text box by clicking the Text Box button in the Text group in the Insert tab, then clicking in the worksheet.
2.
Click to the right of the chart (anywhere outside the chart boundary)
A text box is added to the worksheet, and the Drawing Tools Format tab appears on the Ribbon so that you can format the new object. First you need to type the text.
3.
Type Great Improvement
The text appears in a selected text box on the worksheet, and the chart is no longer selected, as shown in Figure 4-18. Your text box may be in a different location; this is not important because you’ll move the annotation in the next step.
Figure 4-18 Text Box Added
4.
Point to an edge of the text box so that the pointer changes to , drag the text box into the chart to the left of the chart title, as shown in Figure 4-19, then release the mouse button
Figure 4-19 Text Annotation on the Chart
The text box is a text annotation for the chart. You also want to add a simple arrow shape in the chart.
Quick Tip
To annotate a chart using a callout, click the Shapes button in the Illustrations group on the Insert tab or the More button on the Insert Shapes group on the Chart Tools Format tab, then click a shape in the Callouts category of the Shapes gallery.
5.
Click the chart to select it, click the Chart Tools Format tab, click the Arrow button in the Insert Shapes group, then move the pointer over the text box on the chart
The pointer changes to , and the status bar displays “Click and drag to insert an AutoShape.” When is over the text box, black handles appear around the text in the text box. A black handle can act as an anchor for the arrow.
6.
Position on the black handle to the right of the “t” in the word “improvement” (in the text box), press and hold the left mouse button, drag the line to the Quarter 2 column for the Germany category in the chart, then release the mouse button
An arrow points to the Quarter 2 expense for Germany, and the Drawing Tools Format tab displays options for working with the new arrow object. You can resize, format, or delete it just like any other object in a chart.
7.
Click the Shape Outline list arrow in the Shape Styles group, click the Automatic color, click the Shape Outline list arrow again, point to Weight, then click 1½ pt
Compare your finished chart to Figure 4-20.
Figure 4-20 Arrow Shape Added to Chart
8.
Adding SmartArt Graphics
In addition to charts, annotations, and drawn objects, you can create a variety of diagrams using SmartArt graphics. SmartArt graphics are available in List, Process, Cycle, Hierarchy, Relationship, Matrix, Pyramid, Picture, and Office.com categories. To insert SmartArt, click the Insert a SmartArt Graphic button in the Illustrations group on the Insert tab to open the Choose a SmartArt Graphic dialog box. Click a SmartArt category in the left pane, then click a layout for the graphic in the right pane. The right pane shows sample layouts for the selected SmartArt, as shown in Figure 4-21. The SmartArt graphic appears in the worksheet as an embedded object with sizing handles. Depending on the type of SmartArt graphic you selected, a text pane opens next to the graphic; you can enter text into the graphic using the text pane or by typing directly in the shapes in the diagram.
Figure 4-21 Choose a SmartArt Graphic Dialog Box
2