Exp22_Excel_Ch05_HOE – Sociology 1.1

Exp22 Excel Ch05 HOE Sociology 1.1

Excel Chapter 5 Hands-On Exercise – Sociology

Project Description:

You are the vice president of the Sociology Division at Ivory Halls Publishing Company. Textbooks are classified by an overall discipline. Books are further classified by area. You will use these classifications to see which areas and disciplines have the highest and lowest sales. The worksheet contains wholesale and retail data. You want to analyze sales for books published in the Sociology Division. To do this, you will organize data by discipline and insert subtotal rows. You will also create PivotTables to gain a variety of perspectives of aggregated data. Finally, you will create a PivotChart to depict the aggregated data.

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.

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.

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.

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.

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. 

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.

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.

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.

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. 

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. 

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

The chart needs a meaningful   title.
 

  Change the chart title to Family Discipline Book Sales.

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.

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