TEACHING EXCEL FOR DATA ANALYSIS: HANDS ON 19 Modifying tables

 

Modifying tables

You would like to analyze the top sales associate data in the Weekly Sales & Bonus Payout worksheet that you have been working on.

1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.

https://github.com/alcadelina/Excel-

2. To begin your analysis, you want to add a total row to your table. Click anywhere within the table then click Table Design → Total Row:

3. A Total Row is added to your table, with a total amount in the Weekly Goal column:

4. First, you want to add a count for the number of sales representatives. Select cell C16, then click the total row drop-down arrow and select Count:

5. You see that the count of sales representatives is 11, but you know that there are only 10. You realize there must be duplicate data:

6. To remove the duplicate, click Table Design → Remove Duplicates:

7. The Remove Duplicates dialog box opens. Leave all of the columns selected and click OK:

8. An information window appears, indicating that one duplicate has been removed and there 10 unique values remaining. Click OK:

9. With the duplicate now removed you can now add a total to the Weekly Sales column. Select Cell E15, click the dropdown arrow, and select Sum:

10. You can now add a column to your table by selecting cell G4 and typing “% of Total Sales”, then pressing Enter:

(Adjust your column width as required to view the text in the header.)

11. In cell G5, type the formula “=E5/$E$15” and press Enter, then set the format of the column to percentage, and center the content:

12. You can now add sum of the % of Total Sales column by selecting cell G15, clicking the drop-down arrow, then selecting Sum:

13. You can now filter the table to show only the sales representatives who reached their weekly goal by clicking the header row drop-down arrow of the Weekly Sales column, then selecting Number Filters, then Greater Than:

14. In the Custom AutoFilter dialog box, type “15000” next to the “is greater than” selection, then click OK:

15. You will now see the three sales representatives who reached their weekly goal, but the % of Total Sales column is showing percentages only for the total sales of the three. You want to see the percentages based on the total. To do this, select cell E15, then select the Subtotal formula in the formula bar and type “=SUM([[Weekly Sales]])” to replace it:

16. Press Enter to apply the formula. You will see that the value in the Total Row of the Weekly Sales column is now the sum of all of the sales, not just the ones that are visible. You will also see that the values in the % of Total Sales column are now displaying correctly:

17. Save the current workbook as Activity 3-2 Complete and then close Microsoft 365 Excel to complete this exercise.

18. Now, you can check out an example of a completed document in the link below:

https://github.com/alcadelina/Excel-