Deck 8: Working With Trendlines, pivot Table Reports, pivotchart Reports, and Slicers

Question 1

Ricki wants to apply a particular chart style to a PivotChart she has created.Which of these tabs will help her apply it?

A) Layout tab
B) Format tab
C) Design tab
D) Analyze tab

Answer 1

C

Question 2

Chastity wants to rename her pivot table from PivotTable1 to ProductData.She clicks the Pivot table then goes to PivotTable Tools > Analyze,and clicks the X text box in the PivotTable group.She then types a new name and presses ENTER.What can X be in the procedure?

A) Chart Name
B) Table Name
C) PivotTable Name
D) Pivot Chart Name

Answer 2

C

Question 3

Edwin wants to insert a Pivot Chart to summarize raw data.Which of the following combination of clicks/selections/entries should he use to do this?

A) Select Cell > Insert > PivotChart > OK
B) Select Cell > Insert > Recommended PIvotTables > OK
C) Select Cell > PivotTable Tools > Analyze > PivotChart > Select a chart > OK
D) Select Cell > Insert > Table > Create Table dialog box > Fill data in Where is the data for your table? box > OK

Answer 3

A

Question 4

Nolan wants to group data in a Pivot table to analyze a subset of data.Which of these series of clicks/ selections/ entries should he follow to do so?

A) Ctrl + values > Group
B) Group > Analyze > Field Settings > Change Custom Name > OK
C) Right-click an item in the group > Ungroup
D) Right-click value > Group > Grouping box > Edit values in Starting at and Ending at checkboxes > Under By,select a time period > OK

Question 5

In which of these boxes will you enter the name of the new row or column that you want to show the calculated field in a PivotChart?

A) Name text box
B) Fields lists box
C) Formula text box
D) Calculated Field dialog box

Question 6

How can one filter a PivotTable using a report filter?

A) Click on the drop down arrow for a Report Filter in the pivot table and Click (All).
B) Click the Report Filter's drop-down arrow,then click on an item to select an item in the list and click OK.
C) Right-click a cell in the pivot table,and click Pivot Table Options,then set the 'Display Fields in Report Filter Area' to 'Down,Then Over' On the Layout & Format tab.
D) Right-click a cell in the pivot table,and click Pivot Table Options,then set the 'Display Fields in Report Filter Area' to Over,Then Down' On the Layout & Format tab.

Question 7

Ingrid wants to change the name associated with a Pivot Chart in Excel.To do so,she right-clicks on the pivot table and then selects X from the popup menu.She then enters the new name for the Pivot Chart in the Name field in the window that appears and clicks the OK button.What can X be in this procedure?

A) Hide Field List
B) Number Format
C) PivotTable Options
D) Value Field Settings

Question 8

Horatio wants to hide field headers in a Pivot table to increase the display of the screen.He clicks on a cell in the pivot table,then clicks on ANALYZE in the ribbon.What should he do next to complete the procedure?

A) Click on Clear in the Actions group.
B) Click on +/- Buttons in the Show group.
C) Click on Field Headers in the Show group.
D) Click on Fields,Items & Sets in the Calculations group.

Question 9

Which of these is the default layout for a newly created Pivot table?

A) Compact Form
B) Outline Form
C) Tabular Form
D) Chart Form

Question 10

Alejandro wants to change the color of a Pivot Chart he has created to make it a little more impactful.To do so,he clicks the chart he wants to change,then clicks Chart Styles in the upper right corner next to chart,clicks X and selects the color scheme he wants.What can X be in the series of clicks?

A) Style
B) Color
C) Fonts
D) Effects

Question 11

Izzy wants to move a pivot table to a new worksheet.Which of these should she select after the following clicks to do so: PivotTable > Analyze tab > PivotTable tools > Move PIvotTable > Move PivotTable dialog box > Choose where you want the PivotTable to be placed?

A) New Worksheet
B) Blank Worksheet
C) Fresh Worksheet
D) Create Worksheet

Question 12

Luca wants to insert a pivot table.He clicks on a single cell inside the data set,then proceeds to step X,and finally clicks OK in the dialog box that appears after step 2.What can step 2 be?

A) Select a pivot table under Pivot Chart & Pivot Tables in the Charts group on the Charts tab.
B) Click PivotTable on the Insert tab,in the Tables group.
C) Click Recommended PivotTables on the Insert tab,in the Tables group.
D) Select a pivot table under Pivot Chart in the Charts group on the Charts tab.

Question 13

Which of these steps will you choose to refresh data in a Pivot Chart?

A) Right-click on the Pivot Chart and select Refresh
B) Click on the Pivot Chart > PivotTable Tools > Analyze > Refresh arrow > Refresh Status
C) Click on the Pivot Chart > PivotTable Tools > Analyze > Refresh arrow > Cancel Refresh
D) Click Analyze > Options > Layout & Format tab > Check Autofit column widths on update and Preserve cell formatting on update boxes

Question 14

Anne has created a chart and wants to apply a different fill color to a chart element.Which of these will she choose after selecting the chart element and clicking on Chart Tools under Format tab?

A) Shape Fill
B) Shape Styles
C) Shape Effects
D) Shape Outline

Question 15

Melanie wants to change the default PivotTable Style for a workbook.She selects a cell in a Pivot Table,then clicks a certain tab on the Ribbon,under the PivotTable Tools tab.Then she right-clicks the style she wants to set as the default In the PivotTable Style gallery and finally clicks on Set As Default in the context menu.What is the tab Melanie clicks during this procedure?

A) Data tab
B) Home tab
C) Insert tab
D) Design tab

Question 16

Jin wants to insert a recommended pivot table.He clicks a single cell inside the data set,then proceeds to step X and finally clicks OK in the dialog box that appears after step 2.What can step 2 be?

A) Select a pivot table under Pivot Chart & Pivot Tables in the Charts group on the Charts tab.
B) Click PivotTable on the Insert tab,in the Tables group.
C) Click Recommended PivotTables on the Insert tab,in the Tables group.
D) Select a pivot table under Pivot Chart in the Charts group on the Charts tab.

Question 17

In which of these dialog boxes will you select the font,border,and fill settings for an element in a PivotTable?

A) Quick Style dialog box
B) Format Cells dialog box
C) Field Settings dialog box
D) PivotTable Options dialog box

Question 18

After drilling down,Esteban wants to drill up to analyze summary data.To do so,he follows the following steps: 1.Select the item to drill up on in the PivotTable hierarchy you have drilled into.2.Click the Quick Explore button that appears in the lower-right corner of the selection.3.Use the Quick Explore button to reach the data you want.Which step did he miss while following this procedure?

A) Step 3: Select the item you want to explore in the Explore box and then click Drill Down.
B) Step 3: Select the item you want to explore in the Explore box and then click Drill Up.
C) Step 2: Select the item you want to drill up on and then click Drill Up.
D) Step 4: Click OK to complete the procedure.

Question 19

Helen wants to resize slicer buttons to exact dimensions.She selects the slicer,then clicks the Slicer Tools under Options tab.What should she select next to reset the slicer buttons to exact dimensions?

A) Height and Width under Size group
B) Height and Width under Buttons group
C) Format cells under Format in Cells group
D) Filter under Sort & Filter in Editing group

Question 20

Che wants to change the chart type of the whole chart to give it a different look.To do this,he follows the following steps: 1.X 2.He clicks Change Chart Type On the Design tab in the Type group.3.Che clicks the chart type that he wants to use in the Change Chart Type dialog box.What was step 1 in his procedure?

A) He clicks the chart area of the chart.
B) He selects all the labels of the chart.
C) He clicks one data series among the ones she wants to change the chart type for.
D) He selects all the data series of the chart.

Question 21

To see the data source for the selected chart,click the Design tab,then under PivotChart Tools click Select Data to open the Select Data Source window.

Question 22

To remove a field,in the PivotTable Field List clear the check box of the field you want to remove In the Choose fields to add to report box.

Question 23

Hari wants to connect a slicer to three pivot tables so that he can control the entire dashboard with a single slicer.To do so,he selects the pivot tables to connect a slicer,then selects a cell in any of them,then clicks on three group/tab/button in a series.Next from the "Insert Slicer" dialog box,he selects the column to use as a filter in the slicer and clicks OK.What can be the three clicks he made during the procedure?

A) Analyze tab → Filter → Refresh
B) Analyze tab → Filter → Insert Slicer
C) Analyze tab → Show/Hide → Field List
D) Analyze tab → Show/Hide → Field Button

Question 24

Leigh-Ann wants to filter data in a PIvotChart using a slicer.She clicks on the table and then selects Slicer in a particular group on the insert tab.She then ticks the check boxes for the columns that she wants to filter in the Insert Slicers dialog box and clicks OK.What tab did click during the procedure?

A) Links
B) Filters
C) Symbols
D) Sparklines

Question 25

To display the PivotTable Field list,right-click any cell in the pivot table and select Show Field List from the menu.

Question 26

To clear all the data from a PivotChart,click the PivotChart.Then on the Analyze tab,in the Data group,click Clear,and then click Clear All.

Question 27

To select the Row Labels in a PivotChart,point to the bottom border of a Row Label heading and click to select the row labels for that field when the pointer changes to a thick black arrow.

Question 28

Which of these keys will you hold to select more than one item while inserting a slicer into a PivotTable?

A) Tab
B) Alt
C) Ctrl
D) Shift

Question 29

Which of these tabs will you select to alter the font as desired while changing the slicer style?

A) Font tab
B) Analyze tab
C) Border tab
D) Slicer Contextual tool tab

Question 30

Right-click the pivot item,then click Collapse to collapse a field in a PivotTable.

Question 31

To filter data in a PivotTable,follow the series of selections: A cell in the PivotTable > Analyze > Insert Slicer > Select the fields you want to create slicers for > OK > Select the items you want to show in the PivotTable.

Question 32

Grace wants to connect a slicer to two PivotCharts.In which of these dialog boxes can she select the pivot tables she wants to link to the slicer and click OK?

A) Insert Slicers
B) Remove "Product"
C) Report Connections
D) Multi-select "Product"

Question 33

One can use the areas section (at the bottom)of the Field List to rearrange fields the way they want by dragging them between the four areas.

Question 34

Salena has created a chart to show the sale of products of her firm in different cities during different years.The chart has only one series with all the columns of the same color.The Year and Location fields are both in the Rows area.Now,she wants to create columns with different colors,for which she will have to move one of the fields into the Column area in the PivotTable Field List.To do so,Salena can follow the following series of clicks/ entries/ selections to do so: Pivot Chart > Pivot Chart Field window > Drag the Year field from the Axis box (Categories)to the Legend (Series)box.This will create a series for each city,and show the city's columns in a different color.

Question 35

Which of these functions will you use to find the variance of a population of all of the values to be summarized?

A) Var
B) Varp
C) StDev
D) StDevp

Question 36

Marcos wants to refresh the data in a Pivot table manually.He clicks on the PivotTable to show the PivotTable Tools on the ribbon.Which combination of keys should he press next to complete the procedure?

A) Alt + F9
B) Alt +F5
C) Ctrl + K
D) Shift +Tab

Question 37

To hide the PivotTable Field list,right click on any cell of the pivot table,and select Hide Field List option from the context menu.

Question 38

Katrin has created a new table with only data for sale of products in the East region of her company.She wants to change the pivot table showing the sale of products in both East and West regions of the country to use that source,so that it can be sent to a manager in the West region,without revealing the East region's data.To do so,she can follow the following series of clicks/ entries/ selections: Cell > PivotTable Tools tab > Data group > Top section of the Change Data Source command > Change PivotTable Data Source dialog box > Select a new data source range > OK.

Question 39

Right-click the pivot item,then click Expand to expand a field in a PivotTable.

Question 40

One can rearrange fields in the PivotTable by clicking the field name in one of the areas,and then selecting Move Up command to move the field up one position in the area.

Question 41

What will you select as X and Y in the following series of clicks/entries to insert a Clustered Column - Line Combination chart: Range of cells > Insert tab > Charts group > Insert Combo Chart symbol > Create Custom Combo Chart > Insert Chart dialog box > All Charts > Series 1 > X (Chart type)> Series 2 > Y (Chart type)> OK?

A) X - Scatter,Y - Stacked Bar
B) X - Clustered Column,Y - Line
C) X - Radar with Markers,Y - Area
D) X- Clustered Bar,Y- Stacked Line

Question 42

To insert a Sunburst chart for a data set,we can click on the following series: Range of cells > Data tab > Charts group > Insert Hierarchy Chart > Sunburst.

Question 43

Xan wants to change the number format in a PivotTable as well a PivotChart.To do so,she can right-click on a cell in the value field in the pivot table,then click Value Field Settings,and then the Number Format button in the popup menu,then select the formatting that she wants in the Format Cells dialog box and finally click OK.

Question 44

When we add fields to the Column Labels area,they appear as headings on the left of the table.

Question 45

To insert a trendline that forecasts future trends into a chart,we need to follow this series: Chart > Data series > Chart > Add Trendline > Options tab > Forecast section > Up arrow in the Forecast box.

Question 46

Which of the following will you select as X in the following series of clicks to copy a chart from Excel to Word so that it is no longer linked to Excel: In Excel: Chart > Home tab > Clipboard group > Copy button > In Word: location to paste chart > Home tab > Clipboard group > Paste > Paste Options button > X?

A) Link
B) Embed
C) Source formatting
D) Destination theme

Question 47

Which of the following keys will you press after clicking on a chart to remove it from an Excel sheet but keep the data intact?

A) Ctrl + Q
B) Alt + F
C) Esc
D) Delete

Question 48

To add fields to a PivotTable,one can use the field section of the Field List and tick the box next to field names to place them in the default area of the Field List.

Question 49

Which of the following will you do in step X in the following series of clicks to change the bounds of a chart axis: Chart > Chart Tools > Format tab > Current Selection > Format Selection > Format Axis > Axis Options > Vertical Axis crosses > At Category number > X?

A) Expand Tick Marks,and then select the options that you want in the Major type and Minor type boxes.
B) Type the number that you want in the text box.
C) Expand Tick Marks,and then in the Interval between tick marks box,and type the number that you want.
D) Expand Labels,then under Interval between labels,select Specify interval unit and type the number you want in the text box.

Question 50

How many items in a Pivot table can you drill down at a time?

A) 1
B) 2
C) 5
D) 8

Question 51

To insert a Scatter chart for a data set,we can click on the following series: Range of cells > Insert tab > Charts group > Insert Scatter (X,Y)or Bubble Charts > Scatter > Chart of choice > OK.

Question 52

Which of the following will you select as X in the given series of clicks to add a legend to the bottom of a chart: Chart > + sign > X > Bottom?

A) Style
B) Legend
C) Chart Titles
D) Data labels

Question 53

Which option will you click after selecting a chart and clicking on + at its top to add a trendline to a chart?

A) Axes
B) Legend
C) Gridlines
D) Trendline

Question 54

What will you select as X in the following series of clicks/entries to insert a Histogram chart: Range of cells > Insert tab > Charts group > X > Histogram group > Histogram chart icon > OK?

A) Insert Bar Chart
B) Insert line Chart
C) Insert Area Chart
D) Insert Static Chart

Question 55

Rainn wants to switch rows to column in his chart.To do so,he can click on the following series: chart with data series that he wants to plot on different axes > Chart Tools > Design tab > Data group > Switch Row/Column.

Question 56

To use more than one filters per field,one can right-click a cell in the pivot table,and click PivotTable Options,then click the Layout & Format tab.Finally,under Filters,add a check mark to "Allow multiple filters per field" and click OK.

Question 57

To add a calculated field to a PIvotTable,select a cell in the pivot table,then under the PivotTable Tools tab,click the Analyze tab.In the Calculations group,click Fields,Items,& Sets,and then click Calculated Field.Enter a name for the calculated field,type a formula in the Formula box,then click Add to save the calculated field,and click Close.