e05h2Sociology

CREATE A RECOMMENDED PIVOTTABLE

Because you want to keep the subtotals you created in the Books Subtotal worksheet, you will create a PivotTable from the Books Data worksheet. Refer to Figure 5.18 as you complete Step 1

a. Open e05h1Sociology_LastFirst if you closed it at the end of Hands-On Exercise 1 and save it as e05h2Sociology_LastFirst, changing h1 to h2.

b. Click the Books Data sheet tab. A PivotTable cannot be created using subtotaled data. To preserve the subtotals you created in Hands-On Exercise 1, you will use the dataset in the Books Data worksheet.

c. Click cell A5, click the Insert tab, and then click Recommended PivotTables in the Tables group. (On a Mac, click the Insert tab, click PivotTable and then click OK.) The Recommended PivotTables dialog box opens.

d. Scroll the thumbnails of recommended PivotTables and click the Sum of Total Book Sales by Discipline (+) thumbnail. (Note: Point to each thumbnail to see the full name.) Click OK. You selected this PivotTable to show the overall total book sales for each discipline. The dialog box shows a preview of the selected PivotTable.

e. Rename Sheet1 as PivotTable. Excel inserts a new Sheet1 worksheet, which you renamed as PivotTable, with the PivotTable on the left side and the PivotTable Fields task pane on the right side.

f. Click the PivotTable Name box in the PivotTable group on the Analyze tab, type Total Book Sales, and then press Enter. Save the workbook. You changed the name of the PivotTable from PivotTable2 to Total Book Sales to comply with accessibility standards and to give meaningful names to the PivotTables within your workbook.

ADD ROWS, VALUES, AND COLUMNS You want to compare sales combinations by discipline, copyright year, and edition. The Discipline field is already in the PivotTable, so you will add the Copyright Year and Edition fields. Refer to Figure 5.19 as you complete Step 2.

a. Drag the Edition field in the Choose fields to add to report section to the Columns area in the PivotTable Fields task pane. Excel displays the total book sales by a combination of discipline and edition. This enables you to compare sales of current editions within each discipline. Blanks appear in the PivotTable when a discipline does not have a specific edition. For example, the Family discipline does not have any first-edition books currently being published.

b. Drag the Copyright field in the Choose fields to add to report section between the Discipline and Book Title fields in the Rows area in the PivotTable Fields task pane. The Copyright and Book Titles are not showing in the PivotTable because they are collapsed within the Discipline rows.

c. Click Expand for the Aging/Death discipline on the left side of the PivotTable. Save the workbook. You expanded the Aging/Death discipline to show the copyright years and titles.

REMOVE AND REARRANGE FIELDS Although it is informative to compare sales by edition, you think that the PivotTable contains too much detail, so you will remove the Edition field. In addition, the Rows area contains the Book Titles field, but those data are collapsed; therefore, you will remove it as well. After you remove the fields, you will rearrange other fields to simplify the PivotTable. Refer to Figure 5.20 as you complete Step 3.

a. Click the Edition arrow in the Columns area in the PivotTable Fields task pane. Excel displays a menu of options to apply to this field.

b. Select Remove Field on the menu. You removed the Edition field from the PivotTable. Excel consolidates the sales into one sales column.

c. Click the Book Title check box to deselect it in the Choose fields to add to report section in the PivotTable Fields task pane. You removed the Book Title field from the PivotTable.

d. Drag the Copyright field from the Rows area to the Columns area. Save the workbook. This arrangement consolidates the data. Instead of repeating the copyright years for each discipline, the copyright years are listed only once each at the top of the sales columns.

CHANGE THE VALUES FIELD SETTINGS

After selecting the PivotTable fields, you want to improve the appearance of the sociology textbook PivotTable. You will format the values with the Accounting Number Format and replace the generic Row Labels description with a label that indicates the sociology disciplines. Refer to Figure 5.21 as you complete Step 4.

a. Click cell B5 and click Field Settings in the Active Field group on the Analyze tab. The Value Field Settings dialog box (on a Mac, PivotTable Fields dialog box) opens so that you can format the field.

b. Type Sales by Discipline in the Custom Name box. Leave Sum as the selected calculation type in the Summarize value field by section. (On a Mac, type Sales by Discipline in the Field Name box). You created a customized name for the calculated field and retained the calculation to sum the values. The Value Field Settings dialog box is still open so that you can format the field.

c. Click Number Format. Excel opens a Format Cells dialog box with only the Number tab.

d. Click Accounting in the Category list, change the Decimal places value to 0, click OK in the Format Cells dialog box, and then click OK in the Value Field Settings dialog box. You formatted the values with Accounting Number Format with no decimal places, and the heading Sales by Discipline displays in cell A3.

e. Type Discipline in cell A4 and type Copyright Year in cell B3. You replaced the generic Row Labels heading with Discipline to describe the contents of the first column, and you replaced the Column Labels heading with Copyright Year. Although you can create custom names for values, you cannot create custom names for row and column labels. However, you can edit the labels directly in the cells. f. Select the range B4:E4 and center the labels horizontally. Save the workbook.

 

USE GETPIVOTDATA FUNCTION AND REFRESH A PIVOTTABLE

On the Books Data sheet, you want to insert a function that will display the total from the PivotTable. After consulting with the Accounting Department, you realize that the retail prices are incorrect. The unit retail prices are based on a percentage of the wholesale price. The retail unit price is 30% more than the wholesale unit price, but it should be 25%. You will edit the input cell in the original worksheet and refresh the PivotTable to see the corrected results. Refer to Figure 5.22 as you complete Step 5.

a. Look at cell F14. The current grand total is $120,233.644.

b. Click the Books Data sheet tab and click cell B1.

c. Type =, click the PivotTable sheet tab, click cell F14, and then press Enter. Excel inserts =GETPIVOTDATA("Total Book Sales", PivotTable!$A$3) in cell B1 on the Books Data sheet tab and returns $120,233.644, the grand sum for the Total Book Sales field in cell F14 that you selected in the PivotTable.

d. Make sure cell B2 is active in the Books Data sheet, type =, click the PivotTable sheet tab, click cell F8, and then press Enter. =GETPIVOTDATA("Total Book Sales",PivotTable!$A$3,"Discipline","Introductory") is inserted in cell B2 on the Books Data sheet tab and returns $17,876,251, the total for the Introductory discipline in cell F8 that you selected in the PivotTable.

e. Click cell J1, type 125, and then press Enter. Save the workbook. You changed the retail price rate, which changes the results shown in columns J, K, and L in the Books Data worksheet. However, the PivotTable values are not updated yet.

f. Click the PivotTable sheet tab. Notice that the PivotTable aggregate values did not change. The grand total is still $120,233,644. You must refresh the PivotTable.

g. Click the Analyze tab and click Refresh in the Data group. Excel updates the PivotTable values based on the change you made in the Books Data worksheet. The grand total is now $120,040,028.

h. Click the Books Data sheet tab and look at cell B1. The results of the GETPIVOTDATA function reflect the updated total from the PivotTable. i. Save the workbook. Keep the workbook open if you plan to continue with the next Hands-On Exercise. If not, close the workbook and exit Excel.

 

CREATE A RECOMMENDED PIVOTTABLE

Because you want to keep the subtotals you created in the Books Subtotal worksheet, you will create a PivotTable from the Books Data worksheet. Refer to Figure 5.18 as you complete Step 1

a. Open e05h1Sociology_LastFirst if you closed it at the end of Hands-On Exercise 1 and save it as e05h2Sociology_LastFirst, changing h1 to h2.

b. Click the Books Data sheet tab. A PivotTable cannot be created using subtotaled data. To preserve the subtotals you created in Hands-On Exercise 1, you will use the dataset in the Books Data worksheet.

c. Click cell A5, click the Insert tab, and then click Recommended PivotTables in the Tables group. (On a Mac, click the Insert tab, click PivotTable and then click OK.) The Recommended PivotTables dialog box opens.

d. Scroll the thumbnails of recommended PivotTables and click the Sum of Total Book Sales by Discipline (+) thumbnail. (Note: Point to each thumbnail to see the full name.) Click OK. You selected this PivotTable to show the overall total book sales for each discipline. The dialog box shows a preview of the selected PivotTable.

e. Rename Sheet1 as PivotTable. Excel inserts a new Sheet1 worksheet, which you renamed as PivotTable, with the PivotTable on the left side and the PivotTable Fields task pane on the right side.

f. Click the PivotTable Name box in the PivotTable group on the Analyze tab, type Total Book Sales, and then press Enter. Save the workbook. You changed the name of the PivotTable from PivotTable2 to Total Book Sales to comply with accessibility standards and to give meaningful names to the PivotTables within your workbook.

ADD ROWS, VALUES, AND COLUMNS You want to compare sales combinations by discipline, copyright year, and edition. The Discipline field is already in the PivotTable, so you will add the Copyright Year and Edition fields. Refer to Figure 5.19 as you complete Step 2.

a. Drag the Edition field in the Choose fields to add to report section to the Columns area in the PivotTable Fields task pane. Excel displays the total book sales by a combination of discipline and edition. This enables you to compare sales of current editions within each discipline. Blanks appear in the PivotTable when a discipline does not have a specific edition. For example, the Family discipline does not have any first-edition books currently being published.

b. Drag the Copyright field in the Choose fields to add to report section between the Discipline and Book Title fields in the Rows area in the PivotTable Fields task pane. The Copyright and Book Titles are not showing in the PivotTable because they are collapsed within the Discipline rows.

c. Click Expand for the Aging/Death discipline on the left side of the PivotTable. Save the workbook. You expanded the Aging/Death discipline to show the copyright years and titles.

REMOVE AND REARRANGE FIELDS Although it is informative to compare sales by edition, you think that the PivotTable contains too much detail, so you will remove the Edition field. In addition, the Rows area contains the Book Titles field, but those data are collapsed; therefore, you will remove it as well. After you remove the fields, you will rearrange other fields to simplify the PivotTable. Refer to Figure 5.20 as you complete Step 3.

a. Click the Edition arrow in the Columns area in the PivotTable Fields task pane. Excel displays a menu of options to apply to this field.

b. Select Remove Field on the menu. You removed the Edition field from the PivotTable. Excel consolidates the sales into one sales column.

c. Click the Book Title check box to deselect it in the Choose fields to add to report section in the PivotTable Fields task pane. You removed the Book Title field from the PivotTable.

d. Drag the Copyright field from the Rows area to the Columns area. Save the workbook. This arrangement consolidates the data. Instead of repeating the copyright years for each discipline, the copyright years are listed only once each at the top of the sales columns.

CHANGE THE VALUES FIELD SETTINGS

After selecting the PivotTable fields, you want to improve the appearance of the sociology textbook PivotTable. You will format the values with the Accounting Number Format and replace the generic Row Labels description with a label that indicates the sociology disciplines. Refer to Figure 5.21 as you complete Step 4.

a. Click cell B5 and click Field Settings in the Active Field group on the Analyze tab. The Value Field Settings dialog box (on a Mac, PivotTable Fields dialog box) opens so that you can format the field.

b. Type Sales by Discipline in the Custom Name box. Leave Sum as the selected calculation type in the Summarize value field by section. (On a Mac, type Sales by Discipline in the Field Name box). You created a customized name for the calculated field and retained the calculation to sum the values. The Value Field Settings dialog box is still open so that you can format the field.

c. Click Number Format. Excel opens a Format Cells dialog box with only the Number tab.

d. Click Accounting in the Category list, change the Decimal places value to 0, click OK in the Format Cells dialog box, and then click OK in the Value Field Settings dialog box. You formatted the values with Accounting Number Format with no decimal places, and the heading Sales by Discipline displays in cell A3.

e. Type Discipline in cell A4 and type Copyright Year in cell B3. You replaced the generic Row Labels heading with Discipline to describe the contents of the first column, and you replaced the Column Labels heading with Copyright Year. Although you can create custom names for values, you cannot create custom names for row and column labels. However, you can edit the labels directly in the cells. f. Select the range B4:E4 and center the labels horizontally. Save the workbook.

 

USE GETPIVOTDATA FUNCTION AND REFRESH A PIVOTTABLE

On the Books Data sheet, you want to insert a function that will display the total from the PivotTable. After consulting with the Accounting Department, you realize that the retail prices are incorrect. The unit retail prices are based on a percentage of the wholesale price. The retail unit price is 30% more than the wholesale unit price, but it should be 25%. You will edit the input cell in the original worksheet and refresh the PivotTable to see the corrected results. Refer to Figure 5.22 as you complete Step 5.

a. Look at cell F14. The current grand total is $120,233.644.

b. Click the Books Data sheet tab and click cell B1.

c. Type =, click the PivotTable sheet tab, click cell F14, and then press Enter. Excel inserts =GETPIVOTDATA("Total Book Sales", PivotTable!$A$3) in cell B1 on the Books Data sheet tab and returns $120,233.644, the grand sum for the Total Book Sales field in cell F14 that you selected in the PivotTable.

d. Make sure cell B2 is active in the Books Data sheet, type =, click the PivotTable sheet tab, click cell F8, and then press Enter. =GETPIVOTDATA("Total Book Sales",PivotTable!$A$3,"Discipline","Introductory") is inserted in cell B2 on the Books Data sheet tab and returns $17,876,251, the total for the Introductory discipline in cell F8 that you selected in the PivotTable.

e. Click cell J1, type 125, and then press Enter. Save the workbook. You changed the retail price rate, which changes the results shown in columns J, K, and L in the Books Data worksheet. However, the PivotTable values are not updated yet.

f. Click the PivotTable sheet tab. Notice that the PivotTable aggregate values did not change. The grand total is still $120,233,644. You must refresh the PivotTable.

g. Click the Analyze tab and click Refresh in the Data group. Excel updates the PivotTable values based on the change you made in the Books Data worksheet. The grand total is now $120,040,028.

h. Click the Books Data sheet tab and look at cell B1. The results of the GETPIVOTDATA function reflect the updated total from the PivotTable. i. Save the workbook. Keep the workbook open if you plan to continue with the next Hands-On Exercise. If not, close the workbook and exit Excel.

 

CREATE A RECOMMENDED PIVOTTABLE

Because you want to keep the subtotals you created in the Books Subtotal worksheet, you will create a PivotTable from the Books Data worksheet. Refer to Figure 5.18 as you complete Step 1

a. Open e05h1Sociology_LastFirst if you closed it at the end of Hands-On Exercise 1 and save it as e05h2Sociology_LastFirst, changing h1 to h2.

b. Click the Books Data sheet tab. A PivotTable cannot be created using subtotaled data. To preserve the subtotals you created in Hands-On Exercise 1, you will use the dataset in the Books Data worksheet.

c. Click cell A5, click the Insert tab, and then click Recommended PivotTables in the Tables group. (On a Mac, click the Insert tab, click PivotTable and then click OK.) The Recommended PivotTables dialog box opens.

d. Scroll the thumbnails of recommended PivotTables and click the Sum of Total Book Sales by Discipline (+) thumbnail. (Note: Point to each thumbnail to see the full name.) Click OK. You selected this PivotTable to show the overall total book sales for each discipline. The dialog box shows a preview of the selected PivotTable.

e. Rename Sheet1 as PivotTable. Excel inserts a new Sheet1 worksheet, which you renamed as PivotTable, with the PivotTable on the left side and the PivotTable Fields task pane on the right side.

f. Click the PivotTable Name box in the PivotTable group on the Analyze tab, type Total Book Sales, and then press Enter. Save the workbook. You changed the name of the PivotTable from PivotTable2 to Total Book Sales to comply with accessibility standards and to give meaningful names to the PivotTables within your workbook.

ADD ROWS, VALUES, AND COLUMNS You want to compare sales combinations by discipline, copyright year, and edition. The Discipline field is already in the PivotTable, so you will add the Copyright Year and Edition fields. Refer to Figure 5.19 as you complete Step 2.

a. Drag the Edition field in the Choose fields to add to report section to the Columns area in the PivotTable Fields task pane. Excel displays the total book sales by a combination of discipline and edition. This enables you to compare sales of current editions within each discipline. Blanks appear in the PivotTable when a discipline does not have a specific edition. For example, the Family discipline does not have any first-edition books currently being published.

b. Drag the Copyright field in the Choose fields to add to report section between the Discipline and Book Title fields in the Rows area in the PivotTable Fields task pane. The Copyright and Book Titles are not showing in the PivotTable because they are collapsed within the Discipline rows.

c. Click Expand for the Aging/Death discipline on the left side of the PivotTable. Save the workbook. You expanded the Aging/Death discipline to show the copyright years and titles.

REMOVE AND REARRANGE FIELDS Although it is informative to compare sales by edition, you think that the PivotTable contains too much detail, so you will remove the Edition field. In addition, the Rows area contains the Book Titles field, but those data are collapsed; therefore, you will remove it as well. After you remove the fields, you will rearrange other fields to simplify the PivotTable. Refer to Figure 5.20 as you complete Step 3.

a. Click the Edition arrow in the Columns area in the PivotTable Fields task pane. Excel displays a menu of options to apply to this field.

b. Select Remove Field on the menu. You removed the Edition field from the PivotTable. Excel consolidates the sales into one sales column.

c. Click the Book Title check box to deselect it in the Choose fields to add to report section in the PivotTable Fields task pane. You removed the Book Title field from the PivotTable.

d. Drag the Copyright field from the Rows area to the Columns area. Save the workbook. This arrangement consolidates the data. Instead of repeating the copyright years for each discipline, the copyright years are listed only once each at the top of the sales columns.

CHANGE THE VALUES FIELD SETTINGS

After selecting the PivotTable fields, you want to improve the appearance of the sociology textbook PivotTable. You will format the values with the Accounting Number Format and replace the generic Row Labels description with a label that indicates the sociology disciplines. Refer to Figure 5.21 as you complete Step 4.

a. Click cell B5 and click Field Settings in the Active Field group on the Analyze tab. The Value Field Settings dialog box (on a Mac, PivotTable Fields dialog box) opens so that you can format the field.

b. Type Sales by Discipline in the Custom Name box. Leave Sum as the selected calculation type in the Summarize value field by section. (On a Mac, type Sales by Discipline in the Field Name box). You created a customized name for the calculated field and retained the calculation to sum the values. The Value Field Settings dialog box is still open so that you can format the field.

c. Click Number Format. Excel opens a Format Cells dialog box with only the Number tab.

d. Click Accounting in the Category list, change the Decimal places value to 0, click OK in the Format Cells dialog box, and then click OK in the Value Field Settings dialog box. You formatted the values with Accounting Number Format with no decimal places, and the heading Sales by Discipline displays in cell A3.

e. Type Discipline in cell A4 and type Copyright Year in cell B3. You replaced the generic Row Labels heading with Discipline to describe the contents of the first column, and you replaced the Column Labels heading with Copyright Year. Although you can create custom names for values, you cannot create custom names for row and column labels. However, you can edit the labels directly in the cells. f. Select the range B4:E4 and center the labels horizontally. Save the workbook.

 

USE GETPIVOTDATA FUNCTION AND REFRESH A PIVOTTABLE

On the Books Data sheet, you want to insert a function that will display the total from the PivotTable. After consulting with the Accounting Department, you realize that the retail prices are incorrect. The unit retail prices are based on a percentage of the wholesale price. The retail unit price is 30% more than the wholesale unit price, but it should be 25%. You will edit the input cell in the original worksheet and refresh the PivotTable to see the corrected results. Refer to Figure 5.22 as you complete Step 5.

a. Look at cell F14. The current grand total is $120,233.644.

b. Click the Books Data sheet tab and click cell B1.

c. Type =, click the PivotTable sheet tab, click cell F14, and then press Enter. Excel inserts =GETPIVOTDATA("Total Book Sales", PivotTable!$A$3) in cell B1 on the Books Data sheet tab and returns $120,233.644, the grand sum for the Total Book Sales field in cell F14 that you selected in the PivotTable.

d. Make sure cell B2 is active in the Books Data sheet, type =, click the PivotTable sheet tab, click cell F8, and then press Enter. =GETPIVOTDATA("Total Book Sales",PivotTable!$A$3,"Discipline","Introductory") is inserted in cell B2 on the Books Data sheet tab and returns $17,876,251, the total for the Introductory discipline in cell F8 that you selected in the PivotTable.

e. Click cell J1, type 125, and then press Enter. Save the workbook. You changed the retail price rate, which changes the results shown in columns J, K, and L in the Books Data worksheet. However, the PivotTable values are not updated yet.

f. Click the PivotTable sheet tab. Notice that the PivotTable aggregate values did not change. The grand total is still $120,233,644. You must refresh the PivotTable.

g. Click the Analyze tab and click Refresh in the Data group. Excel updates the PivotTable values based on the change you made in the Books Data worksheet. The grand total is now $120,040,028.

h. Click the Books Data sheet tab and look at cell B1. The results of the GETPIVOTDATA function reflect the updated total from the PivotTable. i. Save the workbook. Keep the workbook open if you plan to continue with the next Hands-On Exercise. If not, close the workbook and exit Excel.

 

CREATE A RECOMMENDED PIVOTTABLE CREATE A RECOMMENDED PIVOTTABLE

Because you want to keep the subtotals you created in the Books Subtotal worksheet, you will create a PivotTable from the Books Data worksheet. Refer to Figure 5.18 as you complete Step 1 Because you want to keep the subtotals you created in the Books Subtotal worksheet, you will create a PivotTable from the Books Data worksheet. Refer to Figure 5.18 as you complete Step 1

a. Open e05h1Sociology_LastFirst if you closed it at the end of Hands-On Exercise 1 and save it as e05h2Sociology_LastFirst, changing h1 to h2. a. Open e05h1Sociology_LastFirst if you closed it at the end of Hands-On Exercise 1 and save it as e05h2Sociology_LastFirst, changing h1 to h2.

b. Click the Books Data sheet tab. A PivotTable cannot be created using subtotaled data. To preserve the subtotals you created in Hands-On Exercise 1, you will use the dataset in the Books Data worksheet. b. Click the Books Data sheet tab. A PivotTable cannot be created using subtotaled data. To preserve the subtotals you created in Hands-On Exercise 1, you will use the dataset in the Books Data worksheet.

c. Click cell A5, click the Insert tab, and then click Recommended PivotTables in the Tables group. (On a Mac, click the Insert tab, click PivotTable and then click OK.) The Recommended PivotTables dialog box opens. c. Click cell A5, click the Insert tab, and then click Recommended PivotTables in the Tables group. (On a Mac, click the Insert tab, click PivotTable and then click OK.) The Recommended PivotTables dialog box opens.

d. Scroll the thumbnails of recommended PivotTables and click the Sum of Total Book Sales by Discipline (+) thumbnail. (Note: Point to each thumbnail to see the full name.) Click OK. You selected this PivotTable to show the overall total book sales for each discipline. The dialog box shows a preview of the selected PivotTable. d. Scroll the thumbnails of recommended PivotTables and click the Sum of Total Book Sales by Discipline (+) thumbnail. (Note: Point to each thumbnail to see the full name.) Click OK. You selected this PivotTable to show the overall total book sales for each discipline. The dialog box shows a preview of the selected PivotTable.

e. Rename Sheet1 as PivotTable. Excel inserts a new Sheet1 worksheet, which you renamed as PivotTable, with the PivotTable on the left side and the PivotTable Fields task pane on the right side. e. Rename Sheet1 as PivotTable. Excel inserts a new Sheet1 worksheet, which you renamed as PivotTable, with the PivotTable on the left side and the PivotTable Fields task pane on the right side.

f. Click the PivotTable Name box in the PivotTable group on the Analyze tab, type Total Book Sales, and then press Enter. Save the workbook. You changed the name of the PivotTable from PivotTable2 to Total Book Sales to comply with accessibility standards and to give meaningful names to the PivotTables within your workbook. f. Click the PivotTable Name box in the PivotTable group on the Analyze tab, type Total Book Sales, and then press Enter. Save the workbook. You changed the name of the PivotTable from PivotTable2 to Total Book Sales to comply with accessibility standards and to give meaningful names to the PivotTables within your workbook.

ADD ROWS, VALUES, AND COLUMNS You want to compare sales combinations by discipline, copyright year, and edition. The Discipline field is already in the PivotTable, so you will add the Copyright Year and Edition fields. Refer to Figure 5.19 as you complete Step 2. ADD ROWS, VALUES, AND COLUMNS You want to compare sales combinations by discipline, copyright year, and edition. The Discipline field is already in the PivotTable, so you will add the Copyright Year and Edition fields. Refer to Figure 5.19 as you complete Step 2.

a. Drag the Edition field in the Choose fields to add to report section to the Columns area in the PivotTable Fields task pane. Excel displays the total book sales by a combination of discipline and edition. This enables you to compare sales of current editions within each discipline. Blanks appear in the PivotTable when a discipline does not have a specific edition. For example, the Family discipline does not have any first-edition books currently being published. a. Drag the Edition field in the Choose fields to add to report section to the Columns area in the PivotTable Fields task pane. Excel displays the total book sales by a combination of discipline and edition. This enables you to compare sales of current editions within each discipline. Blanks appear in the PivotTable when a discipline does not have a specific edition. For example, the Family discipline does not have any first-edition books currently being published.

b. Drag the Copyright field in the Choose fields to add to report section between the Discipline and Book Title fields in the Rows area in the PivotTable Fields task pane. The Copyright and Book Titles are not showing in the PivotTable because they are collapsed within the Discipline rows. b. Drag the Copyright field in the Choose fields to add to report section between the Discipline and Book Title fields in the Rows area in the PivotTable Fields task pane. The Copyright and Book Titles are not showing in the PivotTable because they are collapsed within the Discipline rows.

c. Click Expand for the Aging/Death discipline on the left side of the PivotTable. Save the workbook. You expanded the Aging/Death discipline to show the copyright years and titles. c. Click Expand for the Aging/Death discipline on the left side of the PivotTable. Save the workbook. You expanded the Aging/Death discipline to show the copyright years and titles.

REMOVE AND REARRANGE FIELDS Although it is informative to compare sales by edition, you think that the PivotTable contains too much detail, so you will remove the Edition field. In addition, the Rows area contains the Book Titles field, but those data are collapsed; therefore, you will remove it as well. After you remove the fields, you will rearrange other fields to simplify the PivotTable. Refer to Figure 5.20 as you complete Step 3. REMOVE AND REARRANGE FIELDS Although it is informative to compare sales by edition, you think that the PivotTable contains too much detail, so you will remove the Edition field. In addition, the Rows area contains the Book Titles field, but those data are collapsed; therefore, you will remove it as well. After you remove the fields, you will rearrange other fields to simplify the PivotTable. Refer to Figure 5.20 as you complete Step 3.

a. Click the Edition arrow in the Columns area in the PivotTable Fields task pane. Excel displays a menu of options to apply to this field. a. Click the Edition arrow in the Columns area in the PivotTable Fields task pane. Excel displays a menu of options to apply to this field.

b. Select Remove Field on the menu. You removed the Edition field from the PivotTable. Excel consolidates the sales into one sales column. b. Select Remove Field on the menu. You removed the Edition field from the PivotTable. Excel consolidates the sales into one sales column.

c. Click the Book Title check box to deselect it in the Choose fields to add to report section in the PivotTable Fields task pane. You removed the Book Title field from the PivotTable. c. Click the Book Title check box to deselect it in the Choose fields to add to report section in the PivotTable Fields task pane. You removed the Book Title field from the PivotTable.

d. Drag the Copyright field from the Rows area to the Columns area. Save the workbook. This arrangement consolidates the data. Instead of repeating the copyright years for each discipline, the copyright years are listed only once each at the top of the sales columns. d. Drag the Copyright field from the Rows area to the Columns area. Save the workbook. This arrangement consolidates the data. Instead of repeating the copyright years for each discipline, the copyright years are listed only once each at the top of the sales columns.

CHANGE THE VALUES FIELD SETTINGS CHANGE THE VALUES FIELD SETTINGS

After selecting the PivotTable fields, you want to improve the appearance of the sociology textbook PivotTable. You will format the values with the Accounting Number Format and replace the generic Row Labels description with a label that indicates the sociology disciplines. Refer to Figure 5.21 as you complete Step 4. After selecting the PivotTable fields, you want to improve the appearance of the sociology textbook PivotTable. You will format the values with the Accounting Number Format and replace the generic Row Labels description with a label that indicates the sociology disciplines. Refer to Figure 5.21 as you complete Step 4.

a. Click cell B5 and click Field Settings in the Active Field group on the Analyze tab. The Value Field Settings dialog box (on a Mac, PivotTable Fields dialog box) opens so that you can format the field. a. Click cell B5 and click Field Settings in the Active Field group on the Analyze tab. The Value Field Settings dialog box (on a Mac, PivotTable Fields dialog box) opens so that you can format the field.

b. Type Sales by Discipline in the Custom Name box. Leave Sum as the selected calculation type in the Summarize value field by section. (On a Mac, type Sales by Discipline in the Field Name box). You created a customized name for the calculated field and retained the calculation to sum the values. The Value Field Settings dialog box is still open so that you can format the field. b. Type Sales by Discipline in the Custom Name box. Leave Sum as the selected calculation type in the Summarize value field by section. (On a Mac, type Sales by Discipline in the Field Name box). You created a customized name for the calculated field and retained the calculation to sum the values. The Value Field Settings dialog box is still open so that you can format the field.

c. Click Number Format. Excel opens a Format Cells dialog box with only the Number tab. c. Click Number Format. Excel opens a Format Cells dialog box with only the Number tab.

d. Click Accounting in the Category list, change the Decimal places value to 0, click OK in the Format Cells dialog box, and then click OK in the Value Field Settings dialog box. You formatted the values with Accounting Number Format with no decimal places, and the heading Sales by Discipline displays in cell A3. d. Click Accounting in the Category list, change the Decimal places value to 0, click OK in the Format Cells dialog box, and then click OK in the Value Field Settings dialog box. You formatted the values with Accounting Number Format with no decimal places, and the heading Sales by Discipline displays in cell A3.

e. Type Discipline in cell A4 and type Copyright Year in cell B3. You replaced the generic Row Labels heading with Discipline to describe the contents of the first column, and you replaced the Column Labels heading with Copyright Year. Although you can create custom names for values, you cannot create custom names for row and column labels. However, you can edit the labels directly in the cells. f. Select the range B4:E4 and center the labels horizontally. Save the workbook. e. Type Discipline in cell A4 and type Copyright Year in cell B3. You replaced the generic Row Labels heading with Discipline to describe the contents of the first column, and you replaced the Column Labels heading with Copyright Year. Although you can create custom names for values, you cannot create custom names for row and column labels. However, you can edit the labels directly in the cells. f. Select the range B4:E4 and center the labels horizontally. Save the workbook.

 

USE GETPIVOTDATA FUNCTION AND REFRESH A PIVOTTABLE USE GETPIVOTDATA FUNCTION AND REFRESH A PIVOTTABLE

On the Books Data sheet, you want to insert a function that will display the total from the PivotTable. After consulting with the Accounting Department, you realize that the retail prices are incorrect. The unit retail prices are based on a percentage of the wholesale price. The retail unit price is 30% more than the wholesale unit price, but it should be 25%. You will edit the input cell in the original worksheet and refresh the PivotTable to see the corrected results. Refer to Figure 5.22 as you complete Step 5. On the Books Data sheet, you want to insert a function that will display the total from the PivotTable. After consulting with the Accounting Department, you realize that the retail prices are incorrect. The unit retail prices are based on a percentage of the wholesale price. The retail unit price is 30% more than the wholesale unit price, but it should be 25%. You will edit the input cell in the original worksheet and refresh the PivotTable to see the corrected results. Refer to Figure 5.22 as you complete Step 5.

a. Look at cell F14. The current grand total is $120,233.644. a. Look at cell F14. The current grand total is $120,233.644.

b. Click the Books Data sheet tab and click cell B1. b. Click the Books Data sheet tab and click cell B1.

c. Type =, click the PivotTable sheet tab, click cell F14, and then press Enter. Excel inserts =GETPIVOTDATA("Total Book Sales", PivotTable!$A$3) in cell B1 on the Books Data sheet tab and returns $120,233.644, the grand sum for the Total Book Sales field in cell F14 that you selected in the PivotTable. c. Type =, click the PivotTable sheet tab, click cell F14, and then press Enter. Excel inserts =GETPIVOTDATA("Total Book Sales", PivotTable!$A$3) in cell B1 on the Books Data sheet tab and returns $120,233.644, the grand sum for the Total Book Sales field in cell F14 that you selected in the PivotTable.

d. Make sure cell B2 is active in the Books Data sheet, type =, click the PivotTable sheet tab, click cell F8, and then press Enter. =GETPIVOTDATA("Total Book Sales",PivotTable!$A$3,"Discipline","Introductory") is inserted in cell B2 on the Books Data sheet tab and returns $17,876,251, the total for the Introductory discipline in cell F8 that you selected in the PivotTable. d. Make sure cell B2 is active in the Books Data sheet, type =, click the PivotTable sheet tab, click cell F8, and then press Enter. =GETPIVOTDATA("Total Book Sales",PivotTable!$A$3,"Discipline","Introductory") is inserted in cell B2 on the Books Data sheet tab and returns $17,876,251, the total for the Introductory discipline in cell F8 that you selected in the PivotTable.

e. Click cell J1, type 125, and then press Enter. Save the workbook. You changed the retail price rate, which changes the results shown in columns J, K, and L in the Books Data worksheet. However, the PivotTable values are not updated yet. e. Click cell J1, type 125, and then press Enter. Save the workbook. You changed the retail price rate, which changes the results shown in columns J, K, and L in the Books Data worksheet. However, the PivotTable values are not updated yet.

f. Click the PivotTable sheet tab. Notice that the PivotTable aggregate values did not change. The grand total is still $120,233,644. You must refresh the PivotTable. f. Click the PivotTable sheet tab. Notice that the PivotTable aggregate values did not change. The grand total is still $120,233,644. You must refresh the PivotTable.

g. Click the Analyze tab and click Refresh in the Data group. Excel updates the PivotTable values based on the change you made in the Books Data worksheet. The grand total is now $120,040,028. g. Click the Analyze tab and click Refresh in the Data group. Excel updates the PivotTable values based on the change you made in the Books Data worksheet. The grand total is now $120,040,028.

h. Click the Books Data sheet tab and look at cell B1. The results of the GETPIVOTDATA function reflect the updated total from the PivotTable. i. Save the workbook. Keep the workbook open if you plan to continue with the next Hands-On Exercise. If not, close the workbook and exit Excel. h. Click the Books Data sheet tab and look at cell B1. The results of the GETPIVOTDATA function reflect the updated total from the PivotTable. i. Save the workbook. Keep the workbook open if you plan to continue with the next Hands-On Exercise. If not, close the workbook and exit Excel.

 

Answers

Related Questions

you...

There will be a final paper of approximately 2000 words (about 6-8 pages), double spaced pages to be submitted on the day the paper is due on a topic...

discussion...

There was once an outlaw named Fred who had robbed 24 banks successfully.On the way to rob his 25th bank, Fred said to himself: “I have robbed 24 ba...

strategic...

Read the Strategic Management Project Background.Familiarize yourself with the following terms and concepts discussed this week in preparation for com...

module...

Think of your analysis as a story that must have a story structure when presented. All data has a story to tell. Many people present just the facts. T...

lessons...

Lessons for the FutureThe final chapter of Essentials of Physical Anthropology (Lessons From the Past, Lessons for the Future) suggests that humans ar...

process...

Assume you drank a cup of coffee or tea this morning. Did you make it yourself or did you buy it? Draw a detailed process map illustrating the activit...

ceo...

Fuego Phones International is a corporation that recently kicked off the sale of its new smartphone, the Rapid Fire 6. After three months of sales, th...

the...

See the attached model for the example – model 1 “client” For this assignment, imagine you are a counselor working in a psychological model trea...

do...

For those of you who are unfamiliar with how to write a research paper, this detailed guide is for you. For those of you who think you are already exp...

complete...

Consider your experience both within your own family of origin and when interacting with other families. What is different between these examples of f...

in...

Guidelines for Submission: Short papers should use double spacing, 12-point Times New Roman font, and one-inch margins. Sources should be cited accord...

ufos...

Writing a response to the attached reading Lepselter, Susan’s “The Resonance of Unseen Things: Poetics, Power, Captivity, and UFOs in the American...

unit...

****There are two responses. Write a 100 word response for each response. Response 1) According to Salkind (2017), “A theory, such as a theory of mo...

fda...

Using the FDA Website, select a Warning Letter issued to a Medical Device Company. In your own words, briefly summarize what you feel is the biggest i...

social...

3-5 page essay should focus on the readings. The essay should be in 12 point font, double spaced; using appropriate MLA citations in your text (do not...

what...

Directions: Of the following questions below, choose only 1 to answer. Your answer is to be submitted on Canvas by the due date. Please write 1.5 to 2...

assignment...

Business Plan Instructions: Below you will find specific requirements to include in your final Business Plan for opening a Childcare Center in the cit...

5...

Write an expository 5 paragraph essay comparing and contrasting the dystopians elements, themes, and symbolism used within the novel 1984 and the movi...

3...

4 page essayAPA Format – no title page, abstract, or references neededPlease reference these videosPart 2: Start at 23 minutesPart 3: Start at 47 mi...

can...

hey, im taking geography class and i need you to do this activity. this assignment has to be two pages, here is the guideline below: For this activit...

sociology...

C. Wright Mills defined the sociological imagination as the capacity for individuals to understand the relationship between their individual lives and...

pjm380...

RequiredRecommendedImportant! Read FirstChoose one of the following two assignments to complete this week. Do not do both assignments. Identify your a...

deliverable...

CompetencyExamine team member communication, collaboration, and strategies for developing team intelligence.Scenario InformationAs a leader, you have...

1...

2. How would you implement the updated appraisal instrument?3. How would you ensure that the new updated appraisal instrument is part of an effective...

If you didn't find the right answer

Ask Your Questions, We'll notify you once someone answers it