PivotTables in Depth

ABC Corp. sells hand tools in bulk at a lower price. How can you filter your Purchases Pivot Table to show the hand tools that are available at the discounted purchase price, listed by the vendor as Bulk?

Click on the Row Labels cell, then enter Bulk in the search box

You are creating a Pivot Table for quarterly sales. Which advice should you keep in mind if you create a Pivot Table from a data list rather than from an Excel table?

If you add a row or a column, the change will not be added to the PivotTable

You would like a slicer filtered by product category, but you would like the caption to read Product Category. How can you do this?

Slicer > Options tab > type Product Category in text box below Slicer Category

Why would you create a custom list to sort products in a Pivot Table?

because it is quicker and safer than manually sorting the products

Which should you always avoid doing when you are defining your Pivot Tables layouts?

merging and centering cells with labels

When you are making filter selections in the rows of your Pivot Table, what does a solid square box in Select All tell you?

some, but not all, of the rows have been selected in your filter

Your Pivot Table shows products you purchase under headings in Column A, and 2019 in Column B. If you have not changed any settings, what happens when you select Show in Outline Form?

the individual products will move a column to the right

When should you always change values in a Pivot Table to the Accounting Number format?

when the values are over 1,000 dollars

When you created a Pivot Table in a new worksheet, the table was blank. Why is this?

when you create a PivotTable in a new worksheet, it will be blank until you makes selections from the Fields Area

Why would you add a Timeline of months to the Pivot Table on a worksheet?

you can show a clickable summary of data without changing your PivotTable

You would like to filter your Pivot Chart from five product rows to three rows. Where do you do this, and are there any changes made to your Pivot Table?

you do this in Categories on the PivotChart, and your PivotTable changes to three product rows as well

Which limitation do you have when you create Pivot Tables from data models?

You cannot define a calculated field If you need to perform calculations you should not use the data model

Column A of your table is Sales Region with product categories for each Region listed below. Column B is Quarterly Sales. How can you change the table to display the products in columns?

Drag Category from Rows to Columns in the Fields area

Your Pivot Table has sales by product in Rows B7 through G7, with 2017 in Row A6. How can you view sales by month?

Drag Date of Purchase into Rows in the Fields List area

You are interested in seeing the contribution of each of your five products to the total yearly sales over the past three years. How can you do this?

by selecting % of Grand Total under Show Values as in Value Fields Settings

How can you create a Pivot Chart to display Purchases by Product for 2019?

click any cell in your Excel table In the Pivot Chart fields task pain drag Category to Axis and drag Total Price to Values

Your Pivot Table shows Yearly Sales Dollars in columns and Products in Row A. You would like to see quarterly sales dollars. How will you do this?

click on +/- buttons in the Analyze contextual tab on the Ribbon, and then you can click the plus sign next to the year to show more detail

The Purchase Order Pivot Table has sales for 2018 and 2019, sorted in order when they were added. How can you change the order to show the products alphabetically?

click the filter arrow to the right of Row Labels then select Sort A to Z

In which action will you use a sequence of four keyboard controls to perform the action?

converting a PivotTable to a cell range Analyze > Actions > Select Ctrl + C and Ctrl + V

The Pivot Table for purchases you created was from an external data source. How can you drill down to see the data values for the individual purchase orders of packing boxes in Quarter 2?

if you double-click on the cell, a new worksheet will open and you will see the data for all your purchase orders in Q2

Your Pivot Table shows purchases by quantity for the five products you bought from ABC Corp. in 2019. How can you see the total dollar purchases for each product?

in Insert Calculated Field, define the formula =Quantity*Unit Price then click Okay

Your story-telling worksheet shows Products and Purchase Price. One of your managers would like to see total units purchased by product. Which path will you have to take?

open Pivot Table Fields drag Total Purchases up to the Fields area drag Quantity down into Columns

You would like to print a Pivot Chart you have moved to its own chart sheet. If you click a Pivot Chart you have moved to its own chart sheet, then press Ctrl+P, which action will you take next?

select Print Active Sheet from the options under Settings