Recommended chart command

In this project, you will work with multiple worksheets and enter formulas and functions to calculate totals, averages, maximum values, and minimum values. Additionally, you will create a summary sheet, format cells, insert charts, insert sparklines, and create a table in a workbook.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Start Excel. Open the downloaded Excel file named GO_XL_Grader_Vol1_CAP_v2.xlsx.

0

2

On the Net Sales worksheet, calculate totals in the ranges F4:F8 and B9:F9. Apply the Total cell style to the range B9:F9.

5

3

Using absolute cell references as necessary, in cell G4, construct a formula to calculate the percent that the Texas Total is of Total Sales, and then apply Percent Style. Fill the formula down through the range G5:G8.

5

4

In the range H4:H8, insert Line sparklines to represent the trend of each state across the four quarters. Do not include the totals. Add Markers and apply Sparkline Style Colorful 4.

4

5

Select the range A3:E8, and then use the Recommended Charts command to suggest an appropriate chart. Click the first Clustered Column chart that uses the state names as the category axis. Align the upper left corner of the chart inside the upper left corner of cell A11, and then size the chart so that its lower right corner is slightly inside cell H24. Apply chart Style 6. As the chart title, type Quarterly Net Sales by State

10

6

To show the percent that each state contributes to the total sales, select the nonadjacent ranges that represent the state names and state totals. Insert a 3-D Pie chart, and then move the chart to a New sheet. Name the sheet Net Sales Chart.