e05h3Sociology

ADD FILTERS

The level of success of the first two editions especially determines the likelihood of approving subsequent revisions and editions. To display aggregated sales for these editions, you will set a filter to remove the other editions so they are not included in the calculated sales data. After you review the first-and second-edition data, you will enable additional filters to review books published in the past two years. Refer to Figure 5.33 as you complete Step 1.

a. Open e05h2Sociology_LastFirst if you closed it at the end of Hands-On Exercise 2 and save it as e05h3Sociology_LastFirst, changing h2 to h3.

b. Make sure the PivotTable worksheet tab is active and drag the Edition field from the Choose fields to add to report section to the Filters area. You can now filter the PivotTable based on the Edition field. Cell A1 displays the field name, and cell B1 displays All and the filter arrow.

c. Click the Edition filter arrow in cell B1 and click the Select Multiple Items check box to select it. On a Mac, because Select Multiple Items is not available, skip this step. The list displays a check box for each item.

d. Click the All check box to deselect it.

e. Click the 1 and 2 check boxes to select them and click OK. The summary statistics reflect sales data for only first-and second-edition publications. The filter arrow changes to a funnel icon in cell B1. Cell B1 also changes from All to Multiple Items, indicating that multiple items are included in the filter.

f. Click the Copyright Year filter arrow in cell B3 and click the Select All check box to deselect it.

g. Click the 2020 and 2021 check boxes to select them, click OK, and then save the workbook. Excel filters out data for years that do not meet the condition you set. The filter arrow changes to a funnel icon in cell B3.

INSERT AND CUSTOMIZE A SLICER

You plan to distribute the workbook to colleagues who are not as skilled in Excel as you are. To help them set their own filters, you want to insert slicers. Refer to Figure 5.34 as you complete Step 2.

a. Click Insert Slicer in the Filter group on the Analyze tab. The Insert Slicers dialog box opens, listing each field name.

b. Click the Discipline check box to select it and click OK. Excel inserts the Discipline slicer in the worksheet. Six slicer buttons are blue, indicating that those disciplines are selected. The grayed-out buttons at the bottom of the slicer indicate those disciplines are not applicable based on other engaged filters you set (first and second editions and 2020 and 2021 copyright years, respectively).

c. Press and hold Ctrl as you click Aging/Death in the Discipline slicer. This deselects the Aging/Death discipline.

d. Drag the slicer so that the top-left corner of the slicer is in cell F1. You moved the slicer so that it does not cover up data in the PivotTable.

e. Change the Columns value to 2 in the Buttons group on the Options tab. Change the button Width to 1.5" in the Buttons group. The slicer now displays buttons in two columns. You changed the width of the buttons to 1.5" to display the full discipline names within the buttons.

f. Change the slicer Height to 2 in the Size group. The slicer window is now only 2" tall.

g. Click More in the Slicer Styles group and click Light Blue, Slicer Style Dark 1 (the first style in the Dark group). Save the workbook. Based on the selected workbook theme, Light Blue, Slicer Style Dark 1 applies a dark blue fill color for selected disciplines, dark gray and black font for available but not currently selected disciplines, and light blue fill with medium blue font color for non-applicable disciplines.

CREATE A CALCULATED FIELD

You want to calculate the amount of the sales returned to the authors as royalties. Although the 10% royalty rate is stored in cell J2 in the Books Data worksheet, the value must be used in the calculated field because range names and cell references outside the PivotTable cannot be used. Refer to Figure 5.35 as you complete Step 3.

a. Click within the PivotTable, click the Analyze tab, click Fields, Items, & Sets in the Calculations group, and then select Calculated Field. The Insert Calculated Field dialog box opens.

b. Type Author Royalties in the Name box.

c. Scroll down the Fields list, click Total Book Sales, and then click Insert Field. Excel starts to build the formula, which is currently = 'Total Book Sales'.

d. Type *.1 at the end of the Formula box and click OK. Excel adds Sum of Author Royalties calculated field columns, one for each copyright year category. It calculates the authors’ royalties as 10% of the total sales for each copyright year.

e. Click cell C5, click Field Settings in the Active Field group on the Analyze tab, type Authors’ Royalties in the Custom Name box in the Value Field Settings dialog box, and then click OK.

f. Move the slicer below the PivotTable so that the top-left corner is in cell A13.

g. Select cells C5 and E5, click the Home tab, and then click Center and Wrap Text in the Alignment group. Click Format in the Cells group and select Row Height, type 30, and click OK. Click Format, select Column Width, type 12, and then click OK.

h. Save the workbook

SHOW VALUES AS CALCULATIONS

You want to see which copyright year generated the largest sales for each discipline, which discipline contributes the largest percentage of the total sociology sales, and which introductory book has the largest sales contribution within that discipline. Refer to Figure 5.36 as you complete Step 4.

a. Right-click the PivotTable sheet tab, select Move or Copy, click Books Data in the Before sheet list, click the Create a copy check box to select it, and then click OK. You copied the PivotTable worksheet to maintain the previous tasks you completed as evidence. You will work with the PivotTable (2) worksheet, which is the active worksheet.

b. Do the following to remove filters, slicer, and Authors’ Royalties field: • Click the Edition filter in cell B1, click the All check box to select it, and then click OK to clear the Edition filter and display all edition numbers again. • Click the Discipline filter in cell A5 and select Clear Filter From "Discipline". • Click the Copyright Year filter in cell B3 and select Clear Filter From "Copyright". • Select the slicer and press Delete. • Click Authors’ Royalties in the Values area of the PivotTable Fields task pane and select Remove Field.

c. Click any value in the PivotTable, click the Analyze tab, and then click Field Settings in the Active Field group. The Value Field Settings dialog box opens.

d. Click the Show Values As tab, click the Show values as arrow, select % of Row Total, and then click OK. Excel displays each copyright year’s values as percentages for that discipline. All disciplines except Introductory and Research/Stats had the highest percentage of sales for the books with a 2021 copyright. These two disciplines had their highest percentage of sales for books with a 2020 copyright.

e. Click the Field Settings in the Active Field group, click the Show Values As tab within the dialog box, click the Show values as arrow, select % of Grand Total, and then click OK. Save the workbook. Refer to Figure 5.36. Each discipline’s yearly value displays as a percentage of the total sales. Which discipline and for what copyright year produces the highest percentage of total sales? Answer: 2021 Race/Class/Gender with 10.30%, followed closely by the 2020 Research/Stats with 10.04%. In general, the Race/Class/Gender discipline contributed the highest percentage of the total sales with 19.12%.

CHANGE THE PIVOTTABLE STYLE To enhance the readability of the sociology textbook PivotTable, you will change the PivotTable style. Refer to Figure 5.37 as you complete Step 5.

a. Make sure the PivotTable (2) sheet tab is active. Click a cell within the PivotTable, click the Design tab, and then click More in the PivotTable Styles group. The PivotTable Style gallery displays styles that you can apply.

b. Click Light Blue, Pivot Style Medium 2 to apply a dark blue style to the PivotTable.

c. Click the Banded Columns check box to select it in the PivotTable Style Options group to add dark blue vertical lines between the columns.

d. Save and close the workbook. You will submit this file to your instructor at the end of the last Hands-On Exercise.

 

 

ADD FILTERS

The level of success of the first two editions especially determines the likelihood of approving subsequent revisions and editions. To display aggregated sales for these editions, you will set a filter to remove the other editions so they are not included in the calculated sales data. After you review the first-and second-edition data, you will enable additional filters to review books published in the past two years. Refer to Figure 5.33 as you complete Step 1.

a. Open e05h2Sociology_LastFirst if you closed it at the end of Hands-On Exercise 2 and save it as e05h3Sociology_LastFirst, changing h2 to h3.

b. Make sure the PivotTable worksheet tab is active and drag the Edition field from the Choose fields to add to report section to the Filters area. You can now filter the PivotTable based on the Edition field. Cell A1 displays the field name, and cell B1 displays All and the filter arrow.

c. Click the Edition filter arrow in cell B1 and click the Select Multiple Items check box to select it. On a Mac, because Select Multiple Items is not available, skip this step. The list displays a check box for each item.

d. Click the All check box to deselect it.

e. Click the 1 and 2 check boxes to select them and click OK. The summary statistics reflect sales data for only first-and second-edition publications. The filter arrow changes to a funnel icon in cell B1. Cell B1 also changes from All to Multiple Items, indicating that multiple items are included in the filter.

f. Click the Copyright Year filter arrow in cell B3 and click the Select All check box to deselect it.

g. Click the 2020 and 2021 check boxes to select them, click OK, and then save the workbook. Excel filters out data for years that do not meet the condition you set. The filter arrow changes to a funnel icon in cell B3.

INSERT AND CUSTOMIZE A SLICER

You plan to distribute the workbook to colleagues who are not as skilled in Excel as you are. To help them set their own filters, you want to insert slicers. Refer to Figure 5.34 as you complete Step 2.

a. Click Insert Slicer in the Filter group on the Analyze tab. The Insert Slicers dialog box opens, listing each field name.

b. Click the Discipline check box to select it and click OK. Excel inserts the Discipline slicer in the worksheet. Six slicer buttons are blue, indicating that those disciplines are selected. The grayed-out buttons at the bottom of the slicer indicate those disciplines are not applicable based on other engaged filters you set (first and second editions and 2020 and 2021 copyright years, respectively).

c. Press and hold Ctrl as you click Aging/Death in the Discipline slicer. This deselects the Aging/Death discipline.

d. Drag the slicer so that the top-left corner of the slicer is in cell F1. You moved the slicer so that it does not cover up data in the PivotTable.

e. Change the Columns value to 2 in the Buttons group on the Options tab. Change the button Width to 1.5" in the Buttons group. The slicer now displays buttons in two columns. You changed the width of the buttons to 1.5" to display the full discipline names within the buttons.

f. Change the slicer Height to 2 in the Size group. The slicer window is now only 2" tall.

g. Click More in the Slicer Styles group and click Light Blue, Slicer Style Dark 1 (the first style in the Dark group). Save the workbook. Based on the selected workbook theme, Light Blue, Slicer Style Dark 1 applies a dark blue fill color for selected disciplines, dark gray and black font for available but not currently selected disciplines, and light blue fill with medium blue font color for non-applicable disciplines.

CREATE A CALCULATED FIELD

You want to calculate the amount of the sales returned to the authors as royalties. Although the 10% royalty rate is stored in cell J2 in the Books Data worksheet, the value must be used in the calculated field because range names and cell references outside the PivotTable cannot be used. Refer to Figure 5.35 as you complete Step 3.

a. Click within the PivotTable, click the Analyze tab, click Fields, Items, & Sets in the Calculations group, and then select Calculated Field. The Insert Calculated Field dialog box opens.

b. Type Author Royalties in the Name box.

c. Scroll down the Fields list, click Total Book Sales, and then click Insert Field. Excel starts to build the formula, which is currently = 'Total Book Sales'.

d. Type *.1 at the end of the Formula box and click OK. Excel adds Sum of Author Royalties calculated field columns, one for each copyright year category. It calculates the authors’ royalties as 10% of the total sales for each copyright year.

e. Click cell C5, click Field Settings in the Active Field group on the Analyze tab, type Authors’ Royalties in the Custom Name box in the Value Field Settings dialog box, and then click OK.

f. Move the slicer below the PivotTable so that the top-left corner is in cell A13.

g. Select cells C5 and E5, click the Home tab, and then click Center and Wrap Text in the Alignment group. Click Format in the Cells group and select Row Height, type 30, and click OK. Click Format, select Column Width, type 12, and then click OK.

h. Save the workbook

SHOW VALUES AS CALCULATIONS

You want to see which copyright year generated the largest sales for each discipline, which discipline contributes the largest percentage of the total sociology sales, and which introductory book has the largest sales contribution within that discipline. Refer to Figure 5.36 as you complete Step 4.

a. Right-click the PivotTable sheet tab, select Move or Copy, click Books Data in the Before sheet list, click the Create a copy check box to select it, and then click OK. You copied the PivotTable worksheet to maintain the previous tasks you completed as evidence. You will work with the PivotTable (2) worksheet, which is the active worksheet.

b. Do the following to remove filters, slicer, and Authors’ Royalties field: • Click the Edition filter in cell B1, click the All check box to select it, and then click OK to clear the Edition filter and display all edition numbers again. • Click the Discipline filter in cell A5 and select Clear Filter From "Discipline". • Click the Copyright Year filter in cell B3 and select Clear Filter From "Copyright". • Select the slicer and press Delete. • Click Authors’ Royalties in the Values area of the PivotTable Fields task pane and select Remove Field.

c. Click any value in the PivotTable, click the Analyze tab, and then click Field Settings in the Active Field group. The Value Field Settings dialog box opens.

d. Click the Show Values As tab, click the Show values as arrow, select % of Row Total, and then click OK. Excel displays each copyright year’s values as percentages for that discipline. All disciplines except Introductory and Research/Stats had the highest percentage of sales for the books with a 2021 copyright. These two disciplines had their highest percentage of sales for books with a 2020 copyright.

e. Click the Field Settings in the Active Field group, click the Show Values As tab within the dialog box, click the Show values as arrow, select % of Grand Total, and then click OK. Save the workbook. Refer to Figure 5.36. Each discipline’s yearly value displays as a percentage of the total sales. Which discipline and for what copyright year produces the highest percentage of total sales? Answer: 2021 Race/Class/Gender with 10.30%, followed closely by the 2020 Research/Stats with 10.04%. In general, the Race/Class/Gender discipline contributed the highest percentage of the total sales with 19.12%.

CHANGE THE PIVOTTABLE STYLE To enhance the readability of the sociology textbook PivotTable, you will change the PivotTable style. Refer to Figure 5.37 as you complete Step 5.

a. Make sure the PivotTable (2) sheet tab is active. Click a cell within the PivotTable, click the Design tab, and then click More in the PivotTable Styles group. The PivotTable Style gallery displays styles that you can apply.

b. Click Light Blue, Pivot Style Medium 2 to apply a dark blue style to the PivotTable.

c. Click the Banded Columns check box to select it in the PivotTable Style Options group to add dark blue vertical lines between the columns.

d. Save and close the workbook. You will submit this file to your instructor at the end of the last Hands-On Exercise.

 

 

ADD FILTERS

The level of success of the first two editions especially determines the likelihood of approving subsequent revisions and editions. To display aggregated sales for these editions, you will set a filter to remove the other editions so they are not included in the calculated sales data. After you review the first-and second-edition data, you will enable additional filters to review books published in the past two years. Refer to Figure 5.33 as you complete Step 1.

a. Open e05h2Sociology_LastFirst if you closed it at the end of Hands-On Exercise 2 and save it as e05h3Sociology_LastFirst, changing h2 to h3.

b. Make sure the PivotTable worksheet tab is active and drag the Edition field from the Choose fields to add to report section to the Filters area. You can now filter the PivotTable based on the Edition field. Cell A1 displays the field name, and cell B1 displays All and the filter arrow.

c. Click the Edition filter arrow in cell B1 and click the Select Multiple Items check box to select it. On a Mac, because Select Multiple Items is not available, skip this step. The list displays a check box for each item.

d. Click the All check box to deselect it.

e. Click the 1 and 2 check boxes to select them and click OK. The summary statistics reflect sales data for only first-and second-edition publications. The filter arrow changes to a funnel icon in cell B1. Cell B1 also changes from All to Multiple Items, indicating that multiple items are included in the filter.

f. Click the Copyright Year filter arrow in cell B3 and click the Select All check box to deselect it.

g. Click the 2020 and 2021 check boxes to select them, click OK, and then save the workbook. Excel filters out data for years that do not meet the condition you set. The filter arrow changes to a funnel icon in cell B3.

INSERT AND CUSTOMIZE A SLICER

You plan to distribute the workbook to colleagues who are not as skilled in Excel as you are. To help them set their own filters, you want to insert slicers. Refer to Figure 5.34 as you complete Step 2.

a. Click Insert Slicer in the Filter group on the Analyze tab. The Insert Slicers dialog box opens, listing each field name.

b. Click the Discipline check box to select it and click OK. Excel inserts the Discipline slicer in the worksheet. Six slicer buttons are blue, indicating that those disciplines are selected. The grayed-out buttons at the bottom of the slicer indicate those disciplines are not applicable based on other engaged filters you set (first and second editions and 2020 and 2021 copyright years, respectively).

c. Press and hold Ctrl as you click Aging/Death in the Discipline slicer. This deselects the Aging/Death discipline.

d. Drag the slicer so that the top-left corner of the slicer is in cell F1. You moved the slicer so that it does not cover up data in the PivotTable.

e. Change the Columns value to 2 in the Buttons group on the Options tab. Change the button Width to 1.5" in the Buttons group. The slicer now displays buttons in two columns. You changed the width of the buttons to 1.5" to display the full discipline names within the buttons.

f. Change the slicer Height to 2 in the Size group. The slicer window is now only 2" tall.

g. Click More in the Slicer Styles group and click Light Blue, Slicer Style Dark 1 (the first style in the Dark group). Save the workbook. Based on the selected workbook theme, Light Blue, Slicer Style Dark 1 applies a dark blue fill color for selected disciplines, dark gray and black font for available but not currently selected disciplines, and light blue fill with medium blue font color for non-applicable disciplines.

CREATE A CALCULATED FIELD

You want to calculate the amount of the sales returned to the authors as royalties. Although the 10% royalty rate is stored in cell J2 in the Books Data worksheet, the value must be used in the calculated field because range names and cell references outside the PivotTable cannot be used. Refer to Figure 5.35 as you complete Step 3.

a. Click within the PivotTable, click the Analyze tab, click Fields, Items, & Sets in the Calculations group, and then select Calculated Field. The Insert Calculated Field dialog box opens.

b. Type Author Royalties in the Name box.

c. Scroll down the Fields list, click Total Book Sales, and then click Insert Field. Excel starts to build the formula, which is currently = 'Total Book Sales'.

d. Type *.1 at the end of the Formula box and click OK. Excel adds Sum of Author Royalties calculated field columns, one for each copyright year category. It calculates the authors’ royalties as 10% of the total sales for each copyright year.

e. Click cell C5, click Field Settings in the Active Field group on the Analyze tab, type Authors’ Royalties in the Custom Name box in the Value Field Settings dialog box, and then click OK.

f. Move the slicer below the PivotTable so that the top-left corner is in cell A13.

g. Select cells C5 and E5, click the Home tab, and then click Center and Wrap Text in the Alignment group. Click Format in the Cells group and select Row Height, type 30, and click OK. Click Format, select Column Width, type 12, and then click OK.

h. Save the workbook

SHOW VALUES AS CALCULATIONS

You want to see which copyright year generated the largest sales for each discipline, which discipline contributes the largest percentage of the total sociology sales, and which introductory book has the largest sales contribution within that discipline. Refer to Figure 5.36 as you complete Step 4.

a. Right-click the PivotTable sheet tab, select Move or Copy, click Books Data in the Before sheet list, click the Create a copy check box to select it, and then click OK. You copied the PivotTable worksheet to maintain the previous tasks you completed as evidence. You will work with the PivotTable (2) worksheet, which is the active worksheet.

b. Do the following to remove filters, slicer, and Authors’ Royalties field: • Click the Edition filter in cell B1, click the All check box to select it, and then click OK to clear the Edition filter and display all edition numbers again. • Click the Discipline filter in cell A5 and select Clear Filter From "Discipline". • Click the Copyright Year filter in cell B3 and select Clear Filter From "Copyright". • Select the slicer and press Delete. • Click Authors’ Royalties in the Values area of the PivotTable Fields task pane and select Remove Field.

c. Click any value in the PivotTable, click the Analyze tab, and then click Field Settings in the Active Field group. The Value Field Settings dialog box opens.

d. Click the Show Values As tab, click the Show values as arrow, select % of Row Total, and then click OK. Excel displays each copyright year’s values as percentages for that discipline. All disciplines except Introductory and Research/Stats had the highest percentage of sales for the books with a 2021 copyright. These two disciplines had their highest percentage of sales for books with a 2020 copyright.

e. Click the Field Settings in the Active Field group, click the Show Values As tab within the dialog box, click the Show values as arrow, select % of Grand Total, and then click OK. Save the workbook. Refer to Figure 5.36. Each discipline’s yearly value displays as a percentage of the total sales. Which discipline and for what copyright year produces the highest percentage of total sales? Answer: 2021 Race/Class/Gender with 10.30%, followed closely by the 2020 Research/Stats with 10.04%. In general, the Race/Class/Gender discipline contributed the highest percentage of the total sales with 19.12%.

CHANGE THE PIVOTTABLE STYLE To enhance the readability of the sociology textbook PivotTable, you will change the PivotTable style. Refer to Figure 5.37 as you complete Step 5.

a. Make sure the PivotTable (2) sheet tab is active. Click a cell within the PivotTable, click the Design tab, and then click More in the PivotTable Styles group. The PivotTable Style gallery displays styles that you can apply.

b. Click Light Blue, Pivot Style Medium 2 to apply a dark blue style to the PivotTable.

c. Click the Banded Columns check box to select it in the PivotTable Style Options group to add dark blue vertical lines between the columns.

d. Save and close the workbook. You will submit this file to your instructor at the end of the last Hands-On Exercise.

 

 

ADD FILTERS ADD FILTERS

The level of success of the first two editions especially determines the likelihood of approving subsequent revisions and editions. To display aggregated sales for these editions, you will set a filter to remove the other editions so they are not included in the calculated sales data. After you review the first-and second-edition data, you will enable additional filters to review books published in the past two years. Refer to Figure 5.33 as you complete Step 1. The level of success of the first two editions especially determines the likelihood of approving subsequent revisions and editions. To display aggregated sales for these editions, you will set a filter to remove the other editions so they are not included in the calculated sales data. After you review the first-and second-edition data, you will enable additional filters to review books published in the past two years. Refer to Figure 5.33 as you complete Step 1.

a. Open e05h2Sociology_LastFirst if you closed it at the end of Hands-On Exercise 2 and save it as e05h3Sociology_LastFirst, changing h2 to h3. a. Open e05h2Sociology_LastFirst if you closed it at the end of Hands-On Exercise 2 and save it as e05h3Sociology_LastFirst, changing h2 to h3.

b. Make sure the PivotTable worksheet tab is active and drag the Edition field from the Choose fields to add to report section to the Filters area. You can now filter the PivotTable based on the Edition field. Cell A1 displays the field name, and cell B1 displays All and the filter arrow. b. Make sure the PivotTable worksheet tab is active and drag the Edition field from the Choose fields to add to report section to the Filters area. You can now filter the PivotTable based on the Edition field. Cell A1 displays the field name, and cell B1 displays All and the filter arrow.

c. Click the Edition filter arrow in cell B1 and click the Select Multiple Items check box to select it. On a Mac, because Select Multiple Items is not available, skip this step. The list displays a check box for each item. c. Click the Edition filter arrow in cell B1 and click the Select Multiple Items check box to select it. On a Mac, because Select Multiple Items is not available, skip this step. The list displays a check box for each item.

d. Click the All check box to deselect it. d. Click the All check box to deselect it.

e. Click the 1 and 2 check boxes to select them and click OK. The summary statistics reflect sales data for only first-and second-edition publications. The filter arrow changes to a funnel icon in cell B1. Cell B1 also changes from All to Multiple Items, indicating that multiple items are included in the filter. e. Click the 1 and 2 check boxes to select them and click OK. The summary statistics reflect sales data for only first-and second-edition publications. The filter arrow changes to a funnel icon in cell B1. Cell B1 also changes from All to Multiple Items, indicating that multiple items are included in the filter.

f. Click the Copyright Year filter arrow in cell B3 and click the Select All check box to deselect it. f. Click the Copyright Year filter arrow in cell B3 and click the Select All check box to deselect it.

g. Click the 2020 and 2021 check boxes to select them, click OK, and then save the workbook. Excel filters out data for years that do not meet the condition you set. The filter arrow changes to a funnel icon in cell B3. g. Click the 2020 and 2021 check boxes to select them, click OK, and then save the workbook. Excel filters out data for years that do not meet the condition you set. The filter arrow changes to a funnel icon in cell B3.

INSERT AND CUSTOMIZE A SLICER INSERT AND CUSTOMIZE A SLICER

You plan to distribute the workbook to colleagues who are not as skilled in Excel as you are. To help them set their own filters, you want to insert slicers. Refer to Figure 5.34 as you complete Step 2. You plan to distribute the workbook to colleagues who are not as skilled in Excel as you are. To help them set their own filters, you want to insert slicers. Refer to Figure 5.34 as you complete Step 2.

a. Click Insert Slicer in the Filter group on the Analyze tab. The Insert Slicers dialog box opens, listing each field name. a. Click Insert Slicer in the Filter group on the Analyze tab. The Insert Slicers dialog box opens, listing each field name.

b. Click the Discipline check box to select it and click OK. Excel inserts the Discipline slicer in the worksheet. Six slicer buttons are blue, indicating that those disciplines are selected. The grayed-out buttons at the bottom of the slicer indicate those disciplines are not applicable based on other engaged filters you set (first and second editions and 2020 and 2021 copyright years, respectively). b. Click the Discipline check box to select it and click OK. Excel inserts the Discipline slicer in the worksheet. Six slicer buttons are blue, indicating that those disciplines are selected. The grayed-out buttons at the bottom of the slicer indicate those disciplines are not applicable based on other engaged filters you set (first and second editions and 2020 and 2021 copyright years, respectively).

c. Press and hold Ctrl as you click Aging/Death in the Discipline slicer. This deselects the Aging/Death discipline. c. Press and hold Ctrl as you click Aging/Death in the Discipline slicer. This deselects the Aging/Death discipline.

d. Drag the slicer so that the top-left corner of the slicer is in cell F1. You moved the slicer so that it does not cover up data in the PivotTable. d. Drag the slicer so that the top-left corner of the slicer is in cell F1. You moved the slicer so that it does not cover up data in the PivotTable.

e. Change the Columns value to 2 in the Buttons group on the Options tab. Change the button Width to 1.5" in the Buttons group. The slicer now displays buttons in two columns. You changed the width of the buttons to 1.5" to display the full discipline names within the buttons. e. Change the Columns value to 2 in the Buttons group on the Options tab. Change the button Width to 1.5" in the Buttons group. The slicer now displays buttons in two columns. You changed the width of the buttons to 1.5" to display the full discipline names within the buttons.

f. Change the slicer Height to 2 in the Size group. The slicer window is now only 2" tall. f. Change the slicer Height to 2 in the Size group. The slicer window is now only 2" tall.

g. Click More in the Slicer Styles group and click Light Blue, Slicer Style Dark 1 (the first style in the Dark group). Save the workbook. Based on the selected workbook theme, Light Blue, Slicer Style Dark 1 applies a dark blue fill color for selected disciplines, dark gray and black font for available but not currently selected disciplines, and light blue fill with medium blue font color for non-applicable disciplines. g. Click More in the Slicer Styles group and click Light Blue, Slicer Style Dark 1 (the first style in the Dark group). Save the workbook. Based on the selected workbook theme, Light Blue, Slicer Style Dark 1 applies a dark blue fill color for selected disciplines, dark gray and black font for available but not currently selected disciplines, and light blue fill with medium blue font color for non-applicable disciplines.

CREATE A CALCULATED FIELD CREATE A CALCULATED FIELD

You want to calculate the amount of the sales returned to the authors as royalties. Although the 10% royalty rate is stored in cell J2 in the Books Data worksheet, the value must be used in the calculated field because range names and cell references outside the PivotTable cannot be used. Refer to Figure 5.35 as you complete Step 3. You want to calculate the amount of the sales returned to the authors as royalties. Although the 10% royalty rate is stored in cell J2 in the Books Data worksheet, the value must be used in the calculated field because range names and cell references outside the PivotTable cannot be used. Refer to Figure 5.35 as you complete Step 3.

a. Click within the PivotTable, click the Analyze tab, click Fields, Items, & Sets in the Calculations group, and then select Calculated Field. The Insert Calculated Field dialog box opens. a. Click within the PivotTable, click the Analyze tab, click Fields, Items, & Sets in the Calculations group, and then select Calculated Field. The Insert Calculated Field dialog box opens.

b. Type Author Royalties in the Name box. b. Type Author Royalties in the Name box.

c. Scroll down the Fields list, click Total Book Sales, and then click Insert Field. Excel starts to build the formula, which is currently = 'Total Book Sales'. c. Scroll down the Fields list, click Total Book Sales, and then click Insert Field. Excel starts to build the formula, which is currently = 'Total Book Sales'.

d. Type *.1 at the end of the Formula box and click OK. Excel adds Sum of Author Royalties calculated field columns, one for each copyright year category. It calculates the authors’ royalties as 10% of the total sales for each copyright year. d. Type *.1 at the end of the Formula box and click OK. Excel adds Sum of Author Royalties calculated field columns, one for each copyright year category. It calculates the authors’ royalties as 10% of the total sales for each copyright year.

e. Click cell C5, click Field Settings in the Active Field group on the Analyze tab, type Authors’ Royalties in the Custom Name box in the Value Field Settings dialog box, and then click OK. e. Click cell C5, click Field Settings in the Active Field group on the Analyze tab, type Authors’ Royalties in the Custom Name box in the Value Field Settings dialog box, and then click OK.

f. Move the slicer below the PivotTable so that the top-left corner is in cell A13. f. Move the slicer below the PivotTable so that the top-left corner is in cell A13.

g. Select cells C5 and E5, click the Home tab, and then click Center and Wrap Text in the Alignment group. Click Format in the Cells group and select Row Height, type 30, and click OK. Click Format, select Column Width, type 12, and then click OK. g. Select cells C5 and E5, click the Home tab, and then click Center and Wrap Text in the Alignment group. Click Format in the Cells group and select Row Height, type 30, and click OK. Click Format, select Column Width, type 12, and then click OK.

h. Save the workbook h. Save the workbook

SHOW VALUES AS CALCULATIONS SHOW VALUES AS CALCULATIONS

You want to see which copyright year generated the largest sales for each discipline, which discipline contributes the largest percentage of the total sociology sales, and which introductory book has the largest sales contribution within that discipline. Refer to Figure 5.36 as you complete Step 4. You want to see which copyright year generated the largest sales for each discipline, which discipline contributes the largest percentage of the total sociology sales, and which introductory book has the largest sales contribution within that discipline. Refer to Figure 5.36 as you complete Step 4.

a. Right-click the PivotTable sheet tab, select Move or Copy, click Books Data in the Before sheet list, click the Create a copy check box to select it, and then click OK. You copied the PivotTable worksheet to maintain the previous tasks you completed as evidence. You will work with the PivotTable (2) worksheet, which is the active worksheet. a. Right-click the PivotTable sheet tab, select Move or Copy, click Books Data in the Before sheet list, click the Create a copy check box to select it, and then click OK. You copied the PivotTable worksheet to maintain the previous tasks you completed as evidence. You will work with the PivotTable (2) worksheet, which is the active worksheet.

b. Do the following to remove filters, slicer, and Authors’ Royalties field: • Click the Edition filter in cell B1, click the All check box to select it, and then click OK to clear the Edition filter and display all edition numbers again. • Click the Discipline filter in cell A5 and select Clear Filter From "Discipline". • Click the Copyright Year filter in cell B3 and select Clear Filter From "Copyright". • Select the slicer and press Delete. • Click Authors’ Royalties in the Values area of the PivotTable Fields task pane and select Remove Field. b. Do the following to remove filters, slicer, and Authors’ Royalties field: • Click the Edition filter in cell B1, click the All check box to select it, and then click OK to clear the Edition filter and display all edition numbers again. • Click the Discipline filter in cell A5 and select Clear Filter From "Discipline". • Click the Copyright Year filter in cell B3 and select Clear Filter From "Copyright". • Select the slicer and press Delete. • Click Authors’ Royalties in the Values area of the PivotTable Fields task pane and select Remove Field.

c. Click any value in the PivotTable, click the Analyze tab, and then click Field Settings in the Active Field group. The Value Field Settings dialog box opens. c. Click any value in the PivotTable, click the Analyze tab, and then click Field Settings in the Active Field group. The Value Field Settings dialog box opens.

d. Click the Show Values As tab, click the Show values as arrow, select % of Row Total, and then click OK. Excel displays each copyright year’s values as percentages for that discipline. All disciplines except Introductory and Research/Stats had the highest percentage of sales for the books with a 2021 copyright. These two disciplines had their highest percentage of sales for books with a 2020 copyright. d. Click the Show Values As tab, click the Show values as arrow, select % of Row Total, and then click OK. Excel displays each copyright year’s values as percentages for that discipline. All disciplines except Introductory and Research/Stats had the highest percentage of sales for the books with a 2021 copyright. These two disciplines had their highest percentage of sales for books with a 2020 copyright.

e. Click the Field Settings in the Active Field group, click the Show Values As tab within the dialog box, click the Show values as arrow, select % of Grand Total, and then click OK. Save the workbook. Refer to Figure 5.36. Each discipline’s yearly value displays as a percentage of the total sales. Which discipline and for what copyright year produces the highest percentage of total sales? Answer: 2021 Race/Class/Gender with 10.30%, followed closely by the 2020 Research/Stats with 10.04%. In general, the Race/Class/Gender discipline contributed the highest percentage of the total sales with 19.12%. e. Click the Field Settings in the Active Field group, click the Show Values As tab within the dialog box, click the Show values as arrow, select % of Grand Total, and then click OK. Save the workbook. Refer to Figure 5.36. Each discipline’s yearly value displays as a percentage of the total sales. Which discipline and for what copyright year produces the highest percentage of total sales? Answer: 2021 Race/Class/Gender with 10.30%, followed closely by the 2020 Research/Stats with 10.04%. In general, the Race/Class/Gender discipline contributed the highest percentage of the total sales with 19.12%.

CHANGE THE PIVOTTABLE STYLE To enhance the readability of the sociology textbook PivotTable, you will change the PivotTable style. Refer to Figure 5.37 as you complete Step 5. CHANGE THE PIVOTTABLE STYLE To enhance the readability of the sociology textbook PivotTable, you will change the PivotTable style. Refer to Figure 5.37 as you complete Step 5.

a. Make sure the PivotTable (2) sheet tab is active. Click a cell within the PivotTable, click the Design tab, and then click More in the PivotTable Styles group. The PivotTable Style gallery displays styles that you can apply. a. Make sure the PivotTable (2) sheet tab is active. Click a cell within the PivotTable, click the Design tab, and then click More in the PivotTable Styles group. The PivotTable Style gallery displays styles that you can apply.

b. Click Light Blue, Pivot Style Medium 2 to apply a dark blue style to the PivotTable. b. Click Light Blue, Pivot Style Medium 2 to apply a dark blue style to the PivotTable.

c. Click the Banded Columns check box to select it in the PivotTable Style Options group to add dark blue vertical lines between the columns. c. Click the Banded Columns check box to select it in the PivotTable Style Options group to add dark blue vertical lines between the columns.

d. Save and close the workbook. You will submit this file to your instructor at the end of the last Hands-On Exercise. d. Save and close the workbook. You will submit this file to your instructor at the end of the last Hands-On Exercise.

 

 

Answers

Related Questions

Psychology : Discussion responses...

Can you please respond to this discussion...1) The Brain and ConsciousnessI've always felt that consciousness is a realm that lies outside the bra...

Social Science : Project 1 Scenario Characters:...

Project 1Scenario Characters:You: Information Security Specialist, Greenwood CompanyHubert Jenkins: Human Resources Director, Greenwood CompanyMike Mc...

English : visual analysis...

Find an advertisement, cartoon, or image on the Internet that depends predominantly (if not entirely) on visual appeals to market a product or make a...

Develop a final literature review...

For this assignment you will Develop a final literature review. My topic is Educational leadership.Please Include the revisions in response to feedbac...

English : visual analysis...

Find an advertisement, cartoon, or image on the Internet that depends predominantly (if not entirely) on visual appeals to market a product or make a...

Leadership Trends Within a Changing Healthcare E...

This week, assigned Presenters should post a PowerPoint presentation with detailed notes section that contains the following:Incorporation and analysi...

Strayer FIN534 assignment 6 financial management...

Homework Set #3: Chapters 6, 7, & 8Due Week 6 and worth 100 pointsDirections: Answer the following questions on a separate document. Explain how you r...

Management : Financial management assignment, week...

Homework Set #3: Chapters 6, 7, & 8Due Week 6 and worth 100 pointsDirections: Answer the following questions on a separate document. Explain how you r...

Sociology : 300 words, MLA...

Module 4: Life Span Social Work Discussion Board (15 points):This discussion board relates to this week's learning objective: Examining Child welf...

Image Classification...

Simplified Project (One Output Only) Main Question: The application should be using Python programming language, input data as images of crowd people....

Management : Directions: Answer the following ques...

here is assignment 1: A stock exchange is an organized financial market where financial instruments such as bonds; stocks, options, notes etc. are tra...

Computer Science : literature review...

Write a literature review for your study. See pages 70-73 in your textbook for an example of a literature review (Chapter 3). Your literature review s...

Computer Science : literature review...

Write a literature review for your study. See pages 70-73 in your textbook for an example of a literature review (Chapter 3). Your literature review s...

5 pages double space (MLA style)...

new essay only need five paragraph, 1introduction, 3 body paragraphs [ each body paragraphneed 2 quote] and 1 conclusion, like:1paragraph: introductio...

Political Science : Write an essay 1-2 pages singl...

Introduction: The purpose of voting in a democracy.Explain the specific steps to determine if you personally are currently registered to vote in your...

Other : Read article title "Ethics and Criminal Ju...

Criminal Justice and the Study of Morality  Please respond to the following:Read article title "Ethics and Criminal Justice: Some Observations on Pol...

Management : Serious Tutors; Second portion in gro...

Your supervisor has been asked to give a presentation on the effects of the Americans with Disabilities Act (ADA) on employer discrimination at an up...

Other : , write a well-organized and well-supporte...

Note: Prepare an outline for the essay assigned using the Formal Essay Outline and then proceed to the Module 2 Case.Reflecting on the four gende...

Other : Construction Law Assignment - Law homework...

I`v an assingment about reading a Case law and answering the questions attached  I need two version of answers for two person  use this website as...

Other : project - Computer Science homework help...

 Definition of a Problem: A situation, issue, policy, or practice this is difficult and perplexing but can be improved or solved. ·      What...

English : Considering the Color-Line...

W.E.B. DuBois invites all readers, regardless of ethnic background, to consider his proposition that the color-line will continue to be America's...

Accounting : Accounting TAXATION - 0% Plagiari...

I have an assignment for my TAX class. You have to read 2 things code section plus a case, and you are required to write a one page length single spac...

Business : Connie is starting a new busin...

Connie is starting a new business. She contacts Ellen by phone and orders a month's worth of stock. Ellen has a standard contract she faxed to Con...

Write a profile of a leader you know. Preferably...

Write a profile of a leader you know. Preferably, this is someone in management where you work - or where you once worked. If you don't know such...

If you didn't find the right answer

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