Excel Lab 3 – Pivot Tables and ChartsDue on 04/01/2016

Name:______

Turn in this sheet with your answers to the questions in this Assignment Sheet.

BE CAREFUL TO FOLLOW EACH OF THE STEPS IN THE DIRECTIONS.

Reference if Needed

  • Material presented in the associated Tour
  • Gcflearnfree.org

Assignment– Pivot Tables

Skills

Use of the Excel Pivot Table and Pivot Chart for data analysis

Assignment

Part 1 – Prepare the Spreadsheet for Analysis

  1. Locate the workbook named WHP_recordings_3.xlsxonthe course web page folder.
  2. Download and Save the workbook either in the Documents folder or on your thumb drive with the name WHP_recordings_Lab_3_lastname

Note: Pivot Table tool will perform any required sorts

Part 2 – Genre Popularity

  1. Select range A1-J17
  2. Insert, Pivot Table (Leftmost selection on the Insert Ribbon)

  1. The range should be filled in, if not select the range A1 – J17. Select New Worksheet for the Pivot table and hit OK.
  2. Drag Genre to Row Labels box
  3. Drag each of the Regions to Values box. Do this in the order North, South, Midwest, West.

  1. Select spreadsheet range A3-E6
  2. Insert a 2D Column Chart and select the char layout 1.
  3. Close the Pivot Table Field List by clicking the x in the upper right corner
  4. The spreadsheet should look as follows

  1. Rename the tab to GenreRegion
  2. Save the workbook

QUESTION

For each of the genres, which region are they the most popular in?

Blues:

Country:

Indie:

Part 3 – Best Selling Release Year

  1. Go to the RecordingData tab
  2. Select range A1-J17
  3. Insert, Pivot Table (Leftmost selection on the Insert Ribbon)
  1. The range should be filled in. Select New Worksheet for the Pivot table and hit OK.
  2. Drag Release to Row Labels box
  3. Drag CDs to Values box.
  4. Eliminate 1974 by selecting the filter symbol next to the Row Labels heading (at about cell A3) and clicking on 1974 in the years list. Then click OK.
  1. Select range A4-B11 and insert a 2D Pie chart, Chart Layout 1. Change the chart title to Total Sales.
  2. Reposition the chart so it doesn’t overlap the data
  3. The spreadsheet should look as follows

  1. Rename the tab to ReleaseYear
  2. Save the workbook

QUESTION

Which Release year generated the greatest CD sales and what percent of total company sales did it generate?

What would you say about the company’s progress as shown in the latest years of the analysis?

Part 4– Artist Popularity by Region

(Using Pivot Chart rather than Pivot table)

  1. Go to the RecordingData tab
  2. Select range A1-J17
  3. InsertPivot Chart
  1. The range should be filled in. Select New Worksheet from the Pivot table and hit OK.
  2. Drag Genre and then Artist to the Axis Fields
  3. Drag each of the Regions to Values box. Do this in the order North, South, Midwest, West.
  4. The spreadsheet should look as follows (a mess)

  1. Move the chart to a new tab and name it Regional Popularity (Move Chart option on the right end of the Design tab). Close the Pivot Table Field list as you did above.
  1. Your Chart should look as follows

  1. Save the workbook
  1. Submit the Excel workbook and this word document to the D2L folder Excel Lab 3

Page 1