follow the directions

EA 9 Instructions – Independent Challenge 3 x
Module 3 Independent Challenge 3

Independent Challenge 3

Advantage Calendars is a Dallas-based printer that prints and assembles calendars. As the finance manager for the company, one of your responsibilities is to analyze the monthly reports from the five district sales offices. Your boss, Joanne Bennington, has just asked you to prepare a quarterly sales report for an upcoming meeting. Because several top executives will be attending this meeting, Joanne reminds you that the report must look professional. In particular, she asks you to highlight the fact that the Northeastern district continues to outpace the other districts.
a
Plan a worksheet that shows the company’s sales during the first quarter. Assume that all calendars are the same price. Make sure you include the following:
· The number of calendars sold (units sold) and the associated revenues (total sales) for each of the five district sales offices. The five sales districts are Northeastern, Midwestern, Southeastern, Southern, and Western.
· Calculations that show month-by-month totals for January, February, and March, and a 3-month cumulative total.
· Calculations that show each district’s share of sales (percent of Total Sales).
· Labels that reflect the month-by-month data as well as the cumulative data.
· Formatting enhancements such as data bars that emphasize the recent month’s sales surge and the Northeastern district’s sales leadership.
b
Ask yourself the following questions about the organization and formatting of the worksheet: What worksheet title and labels do you need, and where should they appear? How can you calculate the totals? What formulas can you copy to save time and keystrokes? Do any of these formulas need to use an absolute reference? How do you show dollar amounts? What information should be shown in bold? Do you need to use more than one font? Should you use more than one point size?
c

Start Excel, then save a new, blank workbook as EX 3-Advantage Calendars to the location where you store your Data Files.
d
Build the worksheet with your own price and sales data. Enter the titles and labels first, then enter the numbers and formulas. You can use the information in Table 3-4 to get started.

Table 3-4

Advantage Calendars

1st Quarter Sales Report

January

February

March

Total

Office

Price

Units Sold

Sales

Units Sold

Sales

Units Sold

Sales

Units Sold

Sales

Total % of Sales

Northeastern

  

  

  

  

  

  

  

  

  

Midwestern

  

  

  

  

  

  

  

  

  

  

Southeastern

  

  

  

  

  

  

  

  

  

  

Southern

  

  

  

  

  

  

  

  

  

  

Western

  

  

  

  

  

  

  

  

  

  

e
Add a row beneath the data containing the totals for each column.
f
Adjust the column widths as necessary.
g
Change the height of row 1 to 33 points.
h
Format labels and values to enhance the look of the worksheet, and change the font styles and alignment if necessary.
i
Resize columns and adjust the formatting as necessary.
j
Add data bars for the monthly Units Sold columns.
k
Add a column that calculates a 25% increase in total sales dollars. Use an absolute cell reference in this calculation. (Hint: Make sure that the current formatting is applied to the new information.)
l
Delete the contents of cells J4:K4 if necessary, then merge and center cell I4 over column I:K.
m
Add a bottom double border to cells I10:L10.
n
Enter your name in an empty cell.
o
Check the spelling in the workbook, change to a landscape orientation, save your work, then compare your work to Figure 3-31.

Figure 3-31

p
Preview the worksheet in Backstage view, then submit your work to your instructor as directed.
q
Close the workbook file, then exit Excel.
2

CS_150_Excel Module 3 Assignment 911 – IC_3 – Instructions x
CS 150

Excel Module 3 Assignment 9 (IC 3) Instructions

1. For this assignment, you do
not
download a data file from Blackboard as in the previous assignments; you start the spreadsheet from scratch, i.e., you start by opening a
blank Excel file.
2. Download the file EA 7 Instructions – Independent Challenge 3 x.
· Follow the steps in the Independent Challenge 3 (IC 3) exercise
· Step a. and b. just explain the overall purpose / structure of this spreadsheet. Therefore, just read them
So the real Excel items you start creating begin in step c.
· Do not type the values from Figure 3-31 in the column where you need to write formulas: Just create the formulas which will calculate those values. (This use of formulas will be the most important point of grading this Assignment; without formulas you will get 0 points)

· When you create the formulas, use
absolute cell reference
[footnoteRef:1] in the formulas for the cells whose value should not change, when you copy the formula. [1: Recall, how was an absolute cell reference written: $H$3. While, a relative cell reference looks like H3.]

3.
After
finishing all steps, upload the resulting Excel file in Blackboard (under the link of this assignment) and submit it for grading by the due date.

CS_150_Excel Module 4 Assignment 1011 Instructions x
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 x
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

To print or preview just a chart, select the chart (or make the chart sheet active), click the File tab, then click Print on the navigation bar. To reposition a chart by changing the page’s margins, click the Show Margins button  in the lower-right corner of the Print tab to display the margins in the preview. You can drag the margin lines to the exact settings you want; as the margins change, the size and placement of the chart on the page change too.

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 x
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 x
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.

Save your work

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 x
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 x
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 x
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

A combo chart presents two or more charts in one; a column chart with a line chart, for example. This type of chart is helpful when charting dissimilar but related data. For example, you can create a combo chart based on home price and home size data, showing home prices in a column chart and related home sizes in a line chart. Here a secondary axis (such as a vertical axis on the right side of the chart) would supply the scale for the home sizes. To create a combo chart, select all the data you want to plot, click the Combo chart button  in the Charts group in the Insert tab, click a suggested type or Create Custom Combo Chart, supply additional series information if necessary, then click OK. To change an existing chart to a combo chart, select the chart, click Change Chart Type in the Type group on the Chart Tools Design tab, then follow the same procedure.

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 x
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 x
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.

Save your work
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