Excel Microsoft assignment

A Skills Approach: Excel 2013 Chapter 5: Adding Charts and Analyzing Data

1 | Page skill review 5.1 Last Updated 6/4/15

skill review 5.1 In this project, you will analyze data for an event planning company.

Skills needed to complete this project:

 Converting Data into Tables

 Adding Total Rows to Tables

 Sorting Data

 Filtering Data

 Analyzing Data with Data Tables

 Using the Recommended Charts Feature

 Changing the Chart Type

 Applying Quick Styles and Colors to Charts

 Showing and Hiding Chart Elements

 Moving a Chart

 Analyzing Data with Goal Seek

 Inserting a Pie Chart

 Exploring Charts

 Creating PivotTables Using Recommended PivotTables

 Creating a PivotChart from a PivotTable

1. Open the start file EX2013-SkillReview-5-1. The file will be renamed automatically to include your

name. Change the file name if directed to do so by your instructor, and save it.

2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top

of the workbook.

3. Format the data on the Table worksheet into a table using one of the table Quick Styles.

a. Select all the data including the headings, being careful not to select any blank rows or columns.

b. On the Home tab, in the Styles group, click the Format as Table button to display the

Table Styles gallery.

c. Click the Table Style Light 2 Quick Style.

d. Verify that the correct cells will be used, and that the My table has headers check box is checked.

Click OK.

4. Add a Total row to the table to count the number of invitations sent.

a. On the Table Tools Design tab, in the Table Style Options group, click the Total Row check box.

b. In the Total row at the bottom of the table, note the total count of guests attending.

c. In the Total row at the bottom of the table, click in the Street column, click the arrow, and select

the Count function to count the invitations sent.

5. Sort the table by last name in alphabetical order:

a. Click anywhere in the Last Name column.

b. On the Data tab, in the Sort & Filter group, click the A-Z button.

Step 1

Download start file

A Skills Approach: Excel 2013 Chapter 5: Adding Charts and Analyzing Data

2 | Page skill review 5.1 Last Updated 6/4/15

6. Filter the data in the table to show only guests from the city of Rocklin.

a. Click the arrow in the City column header.

b. Click the (Select All) check box to remove all of the check marks.

c. Click the check box in front of Rocklin to filter for only guests from Rocklin.

d. Click OK. Excel displays only the rows for invitations sent to Rocklin.

7. Switch to work with the Data Table worksheet and use a data table to estimate costs for different

numbers of attendees. Cell C19 contains a formula that sums the costs in cells C3:C16. The formulas in

those cells all refer to the number of guests in cell C1.

a. Select cells B19:C26 to use as the data table.

b. On the Data tab, in the Data Tools group, click the What-If Analysis button, and click Data Table.

c. In the Column input cell box, type C1. Click OK.

d. Review the data table results computing the party costs for the various numbers of guests.

8. Use the recommended charts feature to create a column chart to compare the cost of various

size parties.

a. Select cells B18:C26.

b. Click the Quick Analysis Tool button at the lower right corner of the selected range.

c. Click the Charts tab.

d. Click the Clustered Column option.

9. Change the chart to a line chart.

a. If necessary, click to select the chart.

b. On the Chart Tools Design tab, in the Type group, click the Change Chart Type button.

c. In the Change Chart Type dialog, click Line, and click the first line chart type.

d. Click OK.

10. Format the chart using a Quick Style. On the Chart Tools Design tab, in the Chart Styles group, click Style 12.

11. Display the chart gridlines and hide the data labels.

a. Click the Chart Elements button that appears near the upper right corner of the chart.

b. Click the Gridlines check box to add a checkmark.

c. Click the Data Labels check box to remove the checkmark.

12. Move the chart to its own sheet in the workbook.

a. On the Chart Tools Design tab, in the Location group, click the Move Chart button.

b. In the Move Chart dialog, click the New sheet radio button.

c. Click OK.

13. Use Goal Seek to determine the most you can afford for dinner per person, on a total $6,000 budget

for 100 guests.

a. Switch back to the Data Table sheet.

b. Select the outcome cell H19 and review the formula.

c. On the Data tab, in the Data Tools group, click the What-If Analysis button and click Goal Seek…

d. Verify that the outcome cell H19 is referenced in the Set cell box.

A Skills Approach: Excel 2013 Chapter 5: Adding Charts and Analyzing Data

3 | Page skill review 5.1 Last Updated 6/4/15

e. Enter the outcome value of 6000 in the To value box.

f. Enter the input cell F4 in the By changing cell box.

g. Click OK to run Goal Seek.

h. Click OK again to accept the Goal Seek solution.

14. Insert a 3-D pie chart to show the breakdown of the per guest costs.

a. Select cells G3:H11.

b. On the Insert tab, in the Charts group, click the Pie button, and click the 3-D Pie option.

c. Move the chart to the right so it does not cover the worksheet data.

d. Change the chart title to: Per Guest Cost Breakdown

15. Create a PivotTable listing the number attending from each city.

a. Return to the Table worksheet.

b. Click anywhere in the table.

c. On the Insert tab, in the Tables group, click the Recommended PivotTables button.

d. Select the option Sum of NumAttending by City.

e. Click OK.

16. Create a PivotChart from the PivotTable.

a. Select any cell in the PivotTable.

b. On the PivotTable Tools Analyze tab, in the Tools group, click the PivotChart button.

c. Verify that the Clustered Column chart type is selected in the Insert Chart dialog.

d. Click OK.

e. Click the title and change it to read:

Number of Guests Attending from Each City

f. Hide the legend by clicking the Chart Elements button that appears near the upper right corner of the

PivotChart and clicking the Legend check box to remove the checkmark.

g. Move the PivotChart to the right so it does not cover the PivotTable.

17. Save and close the workbook.

18. Upload and save your project file.

19. Submit project for grading.

Step 2

Upload & Save

Step 3

Grade my Project