Exp22_Excel_Ch05_HOE – Sociology 1.1

Step

Instructions

Points Possible

1

Start Excel. Download and open the file named Exp22_Excel_Ch05_HOE_Sociology.xlsx. Grader has automatically added your last name to the beginning of the filename.

0

2

Your first task is to sort the dataset on the Books Subtotal worksheet. Ensure the Books Subtotal worksheet is active. Sort the data by Discipline in alphabetical order and further sort it by Area in alphabetical order.

3

3

You want to subtotal the dataset by Discipline and then add a second set of subtotals by Area. Use the Subtotal feature to insert subtotal rows by Discipline to calculate the totals for the Sales Wholesale, Sales Retail, and Total Book Sales columns. Without removing the first subtotals, insert subtotals by Area to calculate totals for the same columns.

5

4

Next, you want to apply an outline to the columns so that you can collapse or expand the Units Sold and Unit Price columns. Create an automatic outline. Collapse the outline above columns H and K.

3

5

Next, you want to create a recommended PivotTable and then name it. Display the Books Data worksheet and create a recommended PivotTable using the Sum of Units Sold thumbnail. Change the name of the worksheet to PivotTable. Name the PivotTable Total Book Sales.

5

6

You want to compare total book sales by discipline and copyright year. Make sure these fields are in the respective areas. Remove extra fields. Place the Discipline field in rows, Total Book Sales field as values, and Copyright field in columns.

6

7

You will format the values in the PivotTable to look more professional and change the custom names that display as column headings. Click or select cell B5, display the Value Field Settings dialog box, and type Sales by Discipline as the custom name. Apply Accounting Number Format with zero decimal places.

2

8

You want to replace the generic Row Labels and Column Labels headings with meaningful headings. Type Discipline in cell A4 and Copyright Year in cell B3. Select the range B4:E4 and center the labels horizontally.

4

9

On the Books Data sheet, you want to insert functions that will display the total sales and the total Introductory discipline sales data from the PivotTable. You will change the retail unit price rate from 30% to 25% and then refresh the PivotTable. Display the Books Data worksheet. In cell B1, enter the GETPIVOTDATA function to get the value from cell F10 in the PivotTable worksheet. In cell B2, enter the GETPIVOTDATA function to get the value from cell F7 in the PivotTable. Change the value in cell J1 to 125 in the Books Data worksheet, and then refresh the PivotTable.

5

10

You will add a field to the Filters area so that you can filter the list by Edition. Add the Edition field to the Filters area.

4

11

Because you plan to distribute the workbook to colleagues, you will insert a slicer to help them set filters. Insert a slicer for Discipline. Move the slicer so that the top-left corner is just inside the corner of cell A13.

6

12

After inserting the slicer, you want to change the dimensions and appearance of it. Change the slicer to display 2 columns. Change the button width to 1.5 inches. Change the slicer height to 2 inches. Apply Light Blue, Slicer Style Dark 1.

5

13

You want to calculate the amount of the sales returned to the authors as royalties. Create a calculated field with the name Author Royalties. Multiply Total Book Sales by *.1. In cell C5, use the Field Settings to type the custom name Authors’ Royalties.

5

14

Format cells C5, E5, G5, and I5 with Center horizontal alignment and wrap text. Change the height of row 5 to 30. Change the width of columns C, E, G, and I to 12.

4

15

You want to display the Total Book Sales as a percentage of the grand total. Select the Total Book Sales and show the values as a percentage of the grand total.

6

16

To enhance the readability of the sociology textbook PivotTable, you will change the PivotTable style. Apply Light Blue, Pivot Style Medium 2 to the PivotTable. Display banded columns.

4

17

The Books, Disciplines, and Editors worksheets contain tables You will create relationships between the Books table and the other two tables. Display the Books worksheet. Create a relationship between the BOOKS table using the Discipline Code field and the DISCIPLINE table using the Disc Code. Create a relationship between the BOOKS table using the Editor ID field and the EDITOR table using the Editor ID field.

5

18

Now that you built a relationship between the tables, you can create a PivotTable using fields from all three tables. Create a blank PivotTable from within the Books worksheet and add the data to the data model. Name the worksheet as Relationships.

5

19

You are ready to add fields from the three tables to the PivotTable. Display all tables within the PivotTable Fields pane. Place the Total Book Sales field from the BOOKS table in the Values area. Place the Discipline field from the DISCIPLINE table in the Rows area. Place the Editor Last field from the EDITOR table below the Discipline field in the Rows area.

5

20

Format the Total Book Sales field with Accounting Number Format with zero decimal places.

2

21

You want to create a PivotChart to display percentages in a pie chart. Create a PivotChart from the PivotTable you just created. Change the chart type to a pie chart. Cut the chart and paste it just inside the top-left corner of cell C1.

5

22

The PivotChart has too much detail. You will filter the chart to display only one discipline. Filter the PivotChart by displaying only the Family discipline. Sort the PivotTable by the Total Book Sales from largest to smallest.

5

23

The chart needs a meaningful title. Change the chart title to Family Discipline Book Sales.

2

24

You want to add data labels to provide more details to the chart. Display data labels in the best fit position with only the Percentage labels. Deselect the Values data labels.

4

25

Save and close Exp22_Excel_Ch03_ HOE_Sociology.xlsx. Exit Excel. Submit the file as directed.

0

Total Points

100