Exp22_excel_ch05_hoe – sociology 1.1 | Computer Science homework help

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.

Calculate your paper price
Pages (550 words)
Approximate price: -

Why Choose Us

Quality Papers

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.

Affordable Prices

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.

On-Time delivery

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.

100% Originality

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: support@acmehomework.com.

Try it now!

Calculate the price of your order

We'll send you the first draft for approval by at
Total price:

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.

Our Services

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.


Essay Writing Services

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.


Technical papers

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.