Fill in Order Details

  • Submit paper details for free using our simple order form

Make Payment Securely

  • Add funds to your account. There are no upfront payments. The writer will only be paid once you have approved your paper

Writing Process

  • The best qualified expert writer is assigned to work on your order
  • Your paper is written to standard and delivered as per your instructions

Download your paper

  • Download the completed paper from your online account or your email
  • You can request a plagiarism and quality report along with your paper

Assignment 19: Excel 2019 Skill Review

In this excel 2019 skill review project, you will analyze sales data using consolidation, subtotals, outlines, conditional formatting, advanced filters, Pivot Tables, and Pivot Charts.

Skills needed to complete this project:

  • Using GETPIVOTDATA (Skill 8.21)
  • Using Consolidate to Create Subtotals (Skill 8.8)
  • Sorting Data on Multiple Criteria (Skill 8.4)
  • Adding Subtotals (Skill 8.7)
  • Creating an Outline (Skill 8.9)
  • Creating New Conditional Formatting Rules (Skill 8.1)
  • Filtering and Sorting Using Cell Attributes (Skill 8.3)
  • Managing Conditional Formatting Rules (Skill 8.2)
  • Using Advanced Filter (Skill 8.6)
  • Refreshing Data in a PivotTable (Skill 8.16)
  • Adding a Calculated Field to a PivotTable (Skill 8.17)
  • Applying a Quick Style to a PivotTable (Skill 8.22)
  • Changing the Layout of a PivotTable (Skill 8.23)
  • Filtering PivotTable Data (Skill 8.19)
  • Filtering Data in a PivotChart (Skill 8.24)
  • Changing the Look of a PivotChart (Skill 8.25)

Alternate Instruction for Microsoft 365 Apps icon This image appears when a project instruction has changed to accommodate an update to Microsoft 365 Apps. If the instruction does not match your version of Office, try using the alternate instruction instead.

  1. Open the start file EX2019-SkillReview-8-1. The file will be renamed automatically to include your name. Change the project 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 so you can modify the workbook.
  3. Use GETPIVOTDATA to extract data from a PivotTable. In cell D1 on the Analysis worksheet, display the total annual sales for Ambulatory Care of TX, Inc.
    1. If necessary, go to the Analysis worksheet.
    2. Select cell D1.
    3. Type =
    4. Click the PivotTable worksheet tab.
    5. Click cell B4.
    6. Press Enter.
  4. Use consolidate to create a summary of the sales data by region. The summary will be located on the Analysis worksheet.
    1. Remain on the Analysis worksheet and select cell A4.
    2. On the Data tab, in the Data Tools group, click the Consolidate button.
    3. Verify that Sum is selected in the Function box.
    4. If there are any references in the All references box, click each and then click the [Delete] button to remove them.
    5. Click in the Reference box, and then click the Sales Data worksheet tab. Click and drag to select cells C3:E67.
    6. Click both the Top row and Left column check boxes.
    7. Click OK.
    8. On the Analysis worksheet, delete cells B4:B12, allowing the other cells to shift left.
  5. Sort the sales data alphabetically by region and then by last name.
    1. Go to the Sales Data worksheet, and click any cell in the data set.
    2. On the Data tab, in the Sort & Filter group, click the Sort button.
    3. In the Sort dialog, expand the Sort by list, and select Region.
    4. Click the Add Level button.
    5. Expand the Then by list, and select Last Name.
    6. Click OK.
  6. Add subtotals to the data to calculate the total commission earned for each sales associate.
    1. On the Data tab, in the Outline group, click the Subtotal button.
    2. Expand the At each change in list, and select Last Name.
    3. Verify that Sum is selected in the Use function box.
    4. Verify that there is a checkmark next to Commission Earned in the Add subtotal to box.
    5. Click OK.

Or Get a Class Quote

  1. Copy the subtotal data to the Analysis worksheet.
    1. On the Sales Data worksheet, click the outline level 2 button to collapse the list so only the total commission earned for each sales associate is visible. This will make it easier to copy the data.
    2. Select cells A3:G77 and copy them to the Clipboard. Do not include the Grand Total row (row 78).
    3. Go to the Analysis worksheet and paste the copied data beginning in cell A15.
  2. Notice that when you paste the subtotaled data, Excel removes the subtotal grouping, but keeps the subtotal rows and formulas. You can use the Outline command to re-create groups based on the subtotal formulas.
    1. Click any cell in the data range.
    2. On the Data tab, in the Outline group, click the Group button arrow, and select Auto Outline.
    3. Click the outline level 1 button to collapse the list so only the total commission earned for each sales associate is visible.
  3. On the Commissions worksheet, create a new conditional formatting rule to apply an icon set to the values in the Rating column.
    1. Go to the Commissions worksheet and select cells H3:H66.
    2. On the Home tab, in the Styles group, click the Conditional Formatting button. Click New Rule…
    3. In the Select a Rule Type box, verify that Format all cells based on their values is selected.
    4. In the Edit the Rule Description box, expand the Format Style list, and select Icon Sets.
    5. Expand the Icon Style list, and select 3 Stars.
    6. Click the Show Icon Only checkbox to hide the values in the selected cells.
    7. In the Display each icon according to these rules section, expand the first Type list and select Number.
    8. In the first Value box type 9500 to display a completely filled gold star when the cell value is greater than or equal to 9,500.
    9. Expand the second Type list and select Number.
    10. In the second Value box, type 5000 to display a partially filled gold star when the cell value is less than 9,500 and greater than or equal to 5,000.
    11. Click OK.
  4. Sort the sales data so cells with the completely filled gold star icon appear first.
    1. Scroll down and find a cell with a completely filled gold star.
    2. Right-click a cell with a completely filled gold star, point to Sort, and select Put Selected Formatting Icon On Top.
  5. On the Sales Data Filter worksheet, delete the conditional formatting rule that applies a font format to cells where the value is above average.
    1. Go to the Sales Data Filter worksheet.
    2. On the Home tab, in the Styles group, click the Conditional Formatting button. Click Manage Rules… to open the Conditional Formatting Rules Manager.
    3. If necessary, expand the Show formatting rules for list and select This Worksheet.
    4. Select the Above Average rule and click the Delete Rule button.
    5. Click OK.
  6. Use the Advanced Filter feature to find sales greater than $120,000 for George Anderson or Mia Zhu.
    1. Remain on the Sales Data Filter worksheet, and insert four new rows above row 3, so the column label row is now row 7 and there are five blank rows between the title and the column labels. If necessary, clear all formatting from the new rows.
    2. Copy the range A7:G7 (the column labels) and paste in A3:G3.
    3. Enter the filter criteria in A4:G5 as follows:
      1. In A4, type: George
      2. In B4, type: Anderson
      3. In E4, type: >120000
      4. In A5, type: Mia
      5. In B5, type: Zhu
      6. In E5, type: >120000
    4. Click any cell in the data set.
    5. On the Data tab, in the Sort & Filter group, click the Advanced button.
    6. Verify that the Filter the list, in place radio button is selected.
    7. The List range box should display $A$7:$G$71. If it does not, click in the List range box, and then click and drag to select cells A7:G71.
    8. Click in the Criteria range box. Click and drag to select the range A3:G5 as the criteria range.
    9. Click OK.

Or Get a Class Quote

  1. The data in the Sales Data worksheet has changed since the PivotTable was created. Refresh the PivotTable to reflect the changes.
    1. Go to the PivotTable worksheet and click anywhere in the PivotTable.
    2. On the PivotTable Tools Analyze tab, in the Data group, click the Refresh button.
      Alternate Instruction for Microsoft 365 Apps icon On the PivotTable Analyze tab, in the Data group, click the Refresh button.
    3. In the PivotTable Fields pane, click Last Name to add the Last Name field to the Rows box.
    4. In the Rows box, click Last Name and move it above Client.
  2. Add a calculated field to determine the average commission rate for each row in the PivotTable.
    1. On the PivotTable Tools Analyze tab, in the Calculations group, click the Fields, Items, & Sets button.
      Alternate Instruction for Microsoft 365 Apps icon On the PivotTable Analyze tab, in the Calculations group, click the Fields, Items, & Sets button.
    2. Click Calculated Field…
    3. In the Name box, type: Average Commission Calculated
    4. In the Fields box, click Commission Earned, and then click the Insert Field button.
    5. Type: /
    6. In the Fields box, double-click Annual Sales.
    7. The final formula in the Formula box should look like this:
      ='Commission Earned'/'Annual Sales'
    8. Click OK to add the calculated field to the PivotTable.
    9. Click any cell in the new calculated column.
    10. On the PivotTable Tools Analyze tab, in the Active Field group, click the Field Settings button to open the Value Field Settings dialog.
      Alternate Instruction for Microsoft 365 Apps icon On the PivotTable Analyze tab, in the Active Field group, click the Field Settings button to open the Value Field Settings dialog.
    11. In the Custom Name box, type: Average Commission
    12. Click the Number Format button at the bottom left corner of the dialog. In the Category list, click Percentage. If necessary, type 2 in the Decimal places box.
    13. Click OK.
    14. Click OK again to close the Value Field Settings dialog.
  3. Apply a Quick Style to the PivotTable.
    1. Click anywhere in the PivotTable.
    2. On the PivotTable Tools Design tab, in the PivotTable Styles group, click the More button to expand the gallery.
      Alternate Instruction for Microsoft 365 Apps icon On the Design tab, in the PivotTable Styles group, click the More button to expand the gallery.
    3. Click Light Blue, Pivot Style Medium 2.
  4. Change the layout of the PivotTable to Tabular and repeat the item labels on every row.
    1. On the PivotTable Tools Design tab, in the Layout group, click the Report Layout button and select Show in Tabular Form.
      Alternate Instruction for Microsoft 365 Apps icon On the Design tab, in the Layout group, click the Report Layout button and select Show in Tabular Form.
    2. On the PivotTable Tools Design tab, in the Layout group, click the Report Layout button and select Repeat All Item Labels.
      Alternate Instruction for Microsoft 365 Apps icon On the Design tab, in the Layout group, click the Report Layout button and select Repeat All Item Labels.
  5. Use slicers to filter the PivotTable by region to show only the SW2 region.
    1. On the PivotTable Tools Analyze tab, in the Filter group, click the Insert Slicer button.
      Alternate Instruction for Microsoft 365 Apps icon On the PivotTable Analyze tab, in the Filter group, click the Insert Slicer button.
    2. In the Insert Slicers dialog, click the Region check box. Click OK.
    3. In the Region slicer, click the SW2 button.
  6. Filter the PivotChart data to show only the NW1 and NW2 regions.
    1. Go to the PivotChart worksheet and select the chart.
    2. On the PivotChart Tools Analyze tab, in the Filter group, click the Insert Slicer button.
      Alternate Instruction for Microsoft 365 Apps icon On the PivotChart Analyze tab, in the Filter group, click the Insert Slicer button.
    3. In the Insert Slicers dialog, click the Region check box. Click OK.
    4. In the Region slicer, click the NW1 button. Press the [Ctrl] key and click the NW2 button.
  7. Apply a Quick Style to the PivotChart.
    1. Select the PivotChart.
    2. On the PivotChart Tools Design tab, in the Chart Styles group, click the More button to expand the gallery.
      Alternate Instruction for Microsoft 365 Apps icon On the Design tab, in the Chart Styles group, click the More button to expand the gallery.
    3. Click Style 8.
  8. Save and close the workbook.
  9. Submit project for grading.

Reviews


Anonymous

Excellent job, received an A+ (96) on the paper. Thank you so much.

Anonymous

On time and beyond the work I expected. Communication is fast and amazing, this is the only writing service I will be using.

Consider Your Assignments Done

“All my friends and I are getting help from eliteacademicresearch. It’s every college student’s best kept secret!”

Jermaine Byrant
BSN

“I was apprehensive at first. But I must say it was a great experience and well worth the price. I got an A!”

Nicole Johnson
Finance & Economics

See Why Our Clients Hire Us Again And Again!

OVER
10.3k
Reviews

RATING
4.89/5
Avg Rating

YEARS
12
Experience

Elite Academic Research Promises You:

Always on Time

If we are a minute late, the work is on us – it’s free!

Plagiarism-free

If the work we produce contains plagiarism we’ll pay out a £5,000 guarantee.

Quality

Providing quality work is core to our beliefs, which is why we will strive to give you exactly that, and more!

Written to Standard

All of our assignments go through a stringent quality checking process from start to finish.

Success Guarantee

When you order form the best, some of your greatest problems as a student are solved!

Reliable

Professional

Affordable

Quick

What our customers say
_____

Writer provided excellent layout and research assistance I needed in order to create my own essay.
Great service fast and friendly. Have used the team more than once for assistance with work of a high standard. Communications have been faultless and everything arriving by promised time line. Will definitely use again ! 5 star