Exp22_Excel_Ch05_HOE – Sociology 1.1
Exp22 Excel Ch05 HOE Sociology 1.1
Excel Chapter 5 Hands-On Exercise – Sociology
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.
Why Choose Us
We value our clients. For this reason, we ensure that each paper is written carefully as per the instructions provided by the client. Our editing team also checks all the papers to ensure that they have been completed as per the expectations.
Professional Academic Writers
Over the years, our Acme Homework has managed to secure the most qualified, reliable and experienced team of writers. The company has also ensured continued training and development of the team members to ensure that it keep up with the rising Academic Trends.
Our prices are fairly priced in such a way that ensures affordability. Additionally, you can get a free price quotation by clicking on the "Place Order" button.
We pay strict attention on deadlines. For this reason, we ensure that all papers are submitted earlier, even before the deadline indicated by the customer. For this reason, the client can go through the work and review everything.
At Essay USA, all papers are plagiarism-free as they are written from scratch. We have taken strict measures to ensure that there is no similarity on all papers and that citations are included as per the standards set.
Customer Support 24/7
Our support team is readily available to provide any guidance/help on our platform at any time of the day/night. Feel free to contact us via the Chat window or support email: email@example.com.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
Essay USA has stood as the world’s leading custom essay writing services providers. Once you enter all the details in the order form under the place order button, the rest is up to us.
At Essay USA, we prioritize on all aspects that bring about a good grade such as impeccable grammar, proper structure, zero-plagiarism and conformance to guidelines. Our experienced team of writers will help you completed your essays and other assignments.
Admission and Business Papers
Be assured that you’ll definitely get accepted to the Master’s level program at any university once you enter all the details in the order form. We won’t leave you here; we will also help you secure a good position in your aspired workplace by creating an outstanding resume or portfolio once you place an order.
Editing and Proofreading
Our skilled editing and writing team will help you restructure you paper, paraphrase, correct grammar and replace plagiarized sections on your paper just on time. The service is geared toward eliminating any mistakes and rather enhancing better quality.
We have writers in almost all fields including the most technical fields. You don’t have to worry about the complexity of your paper. Simply enter as much details as possible in the place order section.