Exam 2 excel

What is the external reference to cell C20 of the Final Report worksheet located in the Annual Statement.xlsx workbook?

'[Annual Report.xlsx]Final Report'!C20

What is the external reference to cell D10 of the Sunday worksheet located in the Weekly Report.xslx workbook that is stored in the C:\Documents\Reports folder?

'[C:\Documents\Reports\Weekly Report.xlsx]Sunday'!D10

What is the formula to calculate the average of the Income field from the Employees table?

=AVERAGE(Employees[Income])

Write a formula that uses the MAX function to calculate the maximum value of cell C20 of the Monday through Friday worksheet group?

=MAX(Monday:Friday!C20)

Write a formula to calculate the average of the filtered values from the Sales Price field in the Sales_Result table.

=SUBTOTAL(1, Sales_Result[Sales Price])

Write a formula to calculate the sum of the filtered values from the Sales Price field in the Sales_Result table.

=SUBTOTAL(9, Sales_Result[Sales Price])

Write a formula to retrieve the value from the third field in the Sales_Result table that exactly matches the lookup value in cell B10.

=VLOOKUP(B10, Sales_Result, 3, FALSE)

What is a PivotTable?

A PivotTable is an interactive table that groups and summarizes data in a concise tabular format.

How do you allow a PivotTable field to include both a Date filter and a Value filter?

By modifying the properties of the PivotTable to allow for multiple filters within the same field

When sorting a PivotTable by values in a field, how are the field values sorted by default?

By the grand total of the field across all items, even if the grand total doesn't appear in the PivotTable

How do you ungroup a worksheet group that consists of all the sheets in the workbook?

Click the sheet tab of any worksheet in the group

How does the Compact layout differ from the Tabular layout?

Compact layout places all fields from the Rows area in a single column and with subtotal rows placed at the top of each row group. Tabular layout places those fields in separate columns with subtotal rows placed at the bottom of each row group

If you want to sort employees by the value of the Hire Date field within each value of the Dept field, which field is the primary sort field? Which is the secondary sort field?

Dept is the primary sort field; Hire Date is the secondary sort field.

What are two ways of manually sorting a PivotTable field?

Drag and drop the items in the field or type the item labels in the order you prefer.

What is displayed in the title bar for a workbook created from the EmployeeList.xttx template file?

EmployeeList1

What is the reference to the Income field from the Employees table?

Employees[Income]

If you split the worksheet into panes at cell E3, how many panes are created?

Four panes are created.

What are the three freeze pane options?

Freeze panes for panes placed at the top and left of the workbook window, Freeze Top Row to freeze the worksheet's top, and Freeze First Column to freeze the worksheet's first column.

When an ascending sort order is used, how is a date-time field sorted?

From the earliest date and time to the latest

Why is it not a good idea for a company to treat employee records with duplicate first and last names as duplicate records?

In a large enough company, there is a risk that two different employees will have the same name.

By default, where does excel store workbook templates?

In the user's Custom Office Templates folder

Why is Report-Date not a valid names range?

It contains a dash in the name

What does excel do when a hyperlink is clicked by the user?

It opens an application associated with the type of link

What function do you use to retrieve calculated values from the Data Model without the use of a PivotTable?

The GETPIVOTDATA function

What is the absolute 3-D cell reference to cell C20 in the Monday worksheet?

Monday!$C$20

What is 3-D cell reference to cell C20 in the Monday worksheet?

Monday!C20

What is the 3-D cell reference to cell C20 in the Monday through Friday worksheet group?

Monday:Friday!C20

When highlighting duplicate values with a conditional format, do the duplicate values have to be adjacent to each other?

No, duplicate values located anywhere within the selected range will be highlighted by the conditional format rule.

Can you create a PivotChart without a PivotTable?

No, every PivotChart must be based on a PivotTable somewhere within the workbook.

Can any Excel chart type be turned into a PivotChart?

No, only the following chart types can become PivotCharts: Column, Line, Pie, Bar, Area, Surface, Radar, and Combo charts created from the preceding chart types.

Can slicers be moved to any worksheet or external workbook?

No, slicers can be moved to another worksheet but not to another workbook.

Can slicers be used with both data ranges and Excel tables?

No, slicers cannot be used with data ranges.

Can a timeline slicer be used with Excel tabs as well as PivotTables?

No, they can be used only with PivotTables; they cannot be used with Excel tables.

Can a worksheet have only one window?

No; a single workbook can have multiple workbook windows

How do you check the status of a link within the current workbook to determine whether the link's source file is accessible and up-to-date?

On the Data tab, in the Queries & Connections group, click the Edit Link button to open the Edit Links dialog box, and then view the link's status in the Status column.

Conditional formats can be applied to which parts of a PivotTable?

To selected cells, to all cells showing the value of a particular field, and to all cells at the intersection of specified fields

What are the four areas of a PivotChart?

the Axis (Category) area, the Legend (Series) area, the Values area, and the Filters area

What are the four primary areas of a PivotTable?

the Rows area, the Columns area, the Values area, and the Filters area

When you do you need to refresh a PivotTable?

whenever the data source is changed by modifying a data value or adding new data records

What is the difference between a calculated item and a calculated field?

A calculated item is user-defined formula that is applied to items with a field and appears in the PivotTable as just another field item. A calculated field is a user-defined formula that is applied across fields and appears as a new field in the PivotTable.

What is the PivotTable cache?

A data structure that stores the information used to build a PivotTable

What is a field? What is a record?

A field is a single value that describes a person, place, or thing. A record is a group of related fields.

What is a standard PivotTable?

A standard PivotTable is a PivotTable that is based on an Excel table or data range and not the Power Pivot Data model.

How is the value of a calculated field summarized within a PivotTable?

All calculated fields are summarized using the SUM function

When a PivotTable field is manually grouped, how does Excel treat the grouped items?

As a new field in the PivotTable field list

How do you create a PivotTable with its own cache?

Begin creating the PivotTable by pressing ALT+D,P to open the PivotTable Wizard and when prompted to share an already existing cache, click No

What does Excel do to date fields that are added to a PivotTable?

Excel automatically groups the date values into quarters, months, and years if the dates span more than one year.

What happens when you place a date field in a row or column area?

Excel automatically groups the dates, creating new fields for month, quarter, and year.

What happens when you select a defined name in the Name box?

Excel selects the call or cell range referenced by the defined name

How do you display the rank (smallest to largest) of a PivotTable field?

Open the Value Field Settings dialog box for the field, go to the Show Values As tab, and then select Rank Smallest to Largest from the calculation options.

What is a potential mistake you could make with calculated fields?

Performing a nonsensical calculation in the PivotTable's grand total row or subtotal row by adding up fields that should not be summed

How do you create a worksheet group consisting of sheets that are not adjacent in a workbook?

Press and hold CTRL as you click each sheet tab that you want to add to the worksheet group

What default statistic is used for numeric data in the Values area of the PivotTable?

SUM

How do you apply the same slicer to multiple PivotTables?

Select the slicer, click the Report Connections button in Slicer group on the Slicer Tools Options tab, and then select the PivotTables that the slicer is applied to.

Before you can add subtotals to a data range, what must you first do with the data?

Sort the data range by order the field for which you want the subtotals calculated.

What function do you use to retrieve values from a PivotTable?

The CUBEVALUE function

What are the three arguments of an Excel Database function?

The database argument that references the range containing the data, the field argument that specifies which field to retrieve data on, and the criteria argument that specifies a range containing filter criteria to be applied to the data

What happens to banded rows in an Excel table when you insert or delete a row?

The formatting adjusts to retain the banded row effect.

How does Excel indicate that a cell contains linked text?

The text is displayed in green and underlined

What happens to worksheet rows that do not match the filter criteria? What happens to the data they contain?

They are hidden in the worksheet, but the data is not removed.

If motels are grouped by size in one PivotTable, how are they displayed in other PivotTables sharing the same cache?

They will also be grouped by size

When would you use a Label filter in a PivotTable?

To filter the values of a field by the item labels within the field

When would you use a Value filter in a PivotTable?

To filter the values of a field by the item labels within the field

When would you use an advanced filter in place of the filter buttons?

To join several fields with an OR logical operator

What are two reasons for using filter buttons rather than slicers to filter data?

Use filter buttons when you want to apply text filter, date filters, and numeric filters, and when you want to view all of the record in place as you filter the data.

What are two reasons for using slicers rather than filter buttons to filter data?

Use slicers when you want to filter the data into a few distinct categories that can be easily listed within the slicer and when you want to perform the filter on a separate worksheet from the data.

When would you create a template rather than just providing a coworker with the copy of your workbook?

When you don't want the coworker modifying the workbook itself, but only when you want the coworker to create a new workbook based on the workbook design

When multiple filter buttons are used with a data range, how are criteria in different fields combined?

With an AND logical operator

What is the structural reference to the table header row?

[#Headers]

By default, how does a PivotTable arrange the categories in a row or column?

alphabetically for text fields; by date for date fields

What are two ways of filtering a PivotTable?

by adding a field to the Filters area of the table or by using the filter buttons for the fields in the row and column areas.

What is default statistic is used for non-numeric data in the Values area of the PivotTable?

count