Excel LESSON 3 & 4

When you are making filter selections in the rows of your Pivot Table, what does a solid square box in Select All tell you? Select All is not an option in your filter by row. Some, but not all, of the rows have been selected in your filter. You will not be able to select any rows to filter. All of the rows have been selected in your filter.

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. Subtotals will move to the bottom of each product group. The product name in Column A will repeat down the column. Your Pivot Table will not change.

The individual products will move a column to the right.

When you created a Pivot Table in a new worksheet, the table was blank. Why is this? There was a blank row in the data set you used. This is how a Pivot Table in a new worksheet appears. You can only create a Pivot Table in your existing worksheet. You did not click Save when you created the Pivot Table.

This is how a Pivot Table in a new worksheet appears.

In which action will you use a sequence of four keyboard controls to perform the action? --converting a Pivot Table to a cell range --copying a Pivot Table to another worksheet --moving a Pivot Table to another worksheet --clearing the contents of a Pivot Table

converting a Pivot Table to a cell range

When should you always change values in a Pivot Table to the Accounting Number format? when the values are in dollars when the values are over 10,000 dollars when the values are over 1,000 dollars when the values are in numbers

when the values are over 1,000 dollars (This should be done to add the thousands separators with commas.)

The minimum zoom level for a worksheet is _____%; the maximum zoom level is _____%. 10,400 50,200 100,400 30,300

10,400

How can you create a Pivot Chart to display Purchases by Product for 2019? Create a new Pivot Table, then in the Pivot Chart Fields task pane drag Category to Axis, and Total Price to Values. Click any cell in your Excel table, then in the Pivot Chart Fields task pane drag ProductName to Axis, and drag Category to Values. Click any cell in your Excel table, then in the Pivot Chart Fields task pane, drag Category to Axis and drag Total Price to Values. Open a new worksheet, then in the Pivot Chart Fields task pane drag Category to Axis, and drag Total Price to Values.

Click any cell in your Excel table, then in the Pivot Chart Fields task pane, drag Category to Axis and drag Total Price to Values. (This will give you a chart with vertical lines showing the total price for all your product categories.)

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 Field Headers in the Analyze contextual tab on the Ribbon, and you can view the Quarterly columns. Click on Field List in the Analyze contextual tab on the Ribbon, click Tools, and then change the columns to Quarterly. 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. Hide the Pivot Table Fields task pane, and then click on any cell in the columns under Yearly Sales Dollars.

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.

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? In Rows within the Fields Area, enter Bulk in the search box. Click on the Row Labels cell, then enter Bulk in the Search box. Create a search using Does Not Contain and the search term Individual. Click on Sum of Total Price, then enter Bulk in the search box.

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

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. Drag Product Name into the Filters box in the Fields area. Click the filter arrow to the right of Row Labels, then select Sort Z to A. Drag Product Name into the Rows box in the Fields area.

Click the filter arrow to the right of Row Labels, then select Sort A to Z.

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 Filters to Values in the Fields Area. Drag Category from Rows to Filters in the Fields Area. Drag Region from Rows to Columns in the Fields Area. Drag Category from Rows to Columns in the Fields Area.

Drag Category from Rows to Columns in the Fields Area. Feedback

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 Columns in the Fields List area. Drag Date of Purchase into Rows in the Fields List area. Drag Category into Columns in the Fields List area. Drag Months into Rows in the Fields List area.

Drag Date of Purchase into Rows in the Fields List area.

A GETPIVOTDATA formula will always return a valid result even if the cell to which it refers isn't displayed within the PivotTable's current arrangement. T/F?

False

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 Pivot Table. If you add a row or column, the change will be added to the Pivot Table when the Pivot Table refreshes. You can add a Total row by clicking Total Row from the Design contextual tab on the Ribbon. You can see a sum for all the sales amounts formatted in a currency format.

If you add a row or a column, the change will not be added to the Pivot Table.

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 left-click on the cell, a new worksheet will open and you will see the data for all your purchase orders in Quarter 2. If you click the product you want in the Grand Total column, you will see the data for all your purchase orders in Quarter 2. If you right-click on the cell, a new worksheet will open and you will see the data for all your purchase orders 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 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 Quarter 2.

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? Drag Sum of Total Price from the Pivot Tables Fields to Rows in the Field List Area. In Insert Calculated Field, define the formula = Quantity/Unit Price, then click Okay. Drag Sum of Total Price from the Pivot Table Fields to Columns in the Field List Area. In Insert Calculated Field, define the formula =Quantity*Unit Price, then click Okay.

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

Which of the following pieces of information is NOT contained within a reference to a cell in another workbook? Worksheet Workbook Named range Cell address

Named range

Which type of conditional format rule uses a static value to decide whether or not to apply the format? Data Bar Number Formula Percent

Number

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? You will have to run a new macro to show Quantity in the Pivot Table. You will have to close out of the Pivot Table, then create a new Pivot Table showing Quantity. Open Pivot Table Fields, drag Total Purchases up to the Fields area, then drag Quantity down into Columns. Open Pivot Table Fields and drag Quantity down to Rows beneath Product.

Open Pivot Table Fields, drag Total Purchases up to the Fields area, then 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 Selected Chart, which is the only option displayed under Settings. Select Print Selected Chart or Print Worksheet under Settings. Select Print Active Workbook from the options under Settings. Select Print Active Sheet from the options under Settings.

Select Print Active Sheet from the options under Settings.

You would like a slicer filtered by product category, but you would like the caption to read Product Category. How can you do this? Select the Slicer, go to the Options tab, click on More, then type Product Category in the text box that appears. Select the Slicer, go to the Options tab, then type Product Category in the text box below Slicer Category. Click a cell in the Pivot Table, then click the Design tab to change the name. Click on the Analyze tab, click Insert Slicer, then select Product Category from the drop-down menu.

Select the Slicer, go to the Options tab, then type Product Category in the text box below Slicer Category. (This lets you change the caption from Category to Product Category.)

You can use a _____ filter to display just the highest or lowest values in a data set. Greater than Top 10 Descending Ascending

Top 10

You can include summary formulas as part of an Excel table by displaying the _____ row. Total Aggregate Summary AutoSum

Total

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 Pivot Table. By using a Timeline, you can change the structure of a Pivot Table. Month is not a field that will show in a Pivot Table. You can only filter a Pivot Table by month from the Timeline.

You can show a clickable summary of data without changing your Pivot Table.

Which limitation do you have when you create Pivot Tables from data models? You cannot define a calculated field. Drag-and-drop is not available with data models. You cannot create relationships between tables. You have to combine all of your data together in different tables.

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

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 your Pivot Table, and your Pivot Table changes to three product rows as well. You do this in Categories on your Pivot Chart, and your Pivot Table does not change. You do this in Categories on your Pivot Table, and your Pivot Table does not change. You do this in Categories on the Pivot Chart, and your Pivot Table changes to three product rows as well.

You do this in Categories on the Pivot Chart, and your Pivot Table changes to three product rows as well. (Pivot Charts are connected to Pivot Tables, so the Pivot Table will change as well.)

Why would you create a custom list to sort products in a Pivot Table? because dragging and moving is not available for rows because dragging and moving is not available for columns because there is no other way to sort items in a Pivot Table because it is quicker and safer than manually sorting the products

because it is quicker and safer than manually sorting the products

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 Column Total under Show Values as in Value Fields Settings by selecting % of Grand Total under Show Values as in Value Fields Settings by selecting Count under Summarize value field as in Value Fields Settings by selecting Average under Summarize value field as in Value Fields Settings

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

Which should you always avoid doing when you are defining your Pivot Tables layouts? --having empty cells appear as blank --merging and centering cells with labels --using Show in Compact Form in Report Layout --making changes in the Excel Options box

merging and centering cells with labels