excel 2

3-D Reference

A reference to the same cell or range in multiple worksheets in the same workbook.

Broken Link

A reference to a file that has been moved since the link was created.

Custom Template

A workbook template you create that is ready to run with the formulas for all calculations included as well as all formatting and labels.

Destination File (Dependent File)

The workbook that receives data from another workbook when two workbooks are linked; sometimes referred to as the dependent file.

External Reference

A reference to cells or ranges in a worksheet from another workbook.

Hyperlink

A link in a file, such as a workbook, to information within that file or another file.

Link

A connection between files that allows data to be transferred from one file to another.

Source File

A workbook that contains data to be used in the destination file when two or more workbooks are linked.

Screen Tip

A tip that appears when the mouse pointer rests on a tool. A basic ScreenTip displays the tool's name and shortcut key (if a shortcut exists for that tool).

Worksheet Group

A collection of two or more selected worksheets.

2-D Reference

Formulas with references to cells on the same sheet can be thought of as 2-D (or 2-dimensional) references because they involve only the row address and the column address.

Named Range/Defined Names

in which range references are replaced with descriptive names. A named range can refer to any cell or cell range within the workbook.

Grouped Worksheets

You can edit several worksheets simultaneously by grouping the worksheets. In a worksheet group, any changes made to one worksheet are automatically applied to all sheets in the group, including entering formulas and data, changing row heights and widths, applying conditional formats, inserting or deleting rows and columns, defining page layouts, and setting view options. Worksheet groups save time and improve consistency because identical actions are performed within several sheets at the same time.

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.

Can a worksheet only have one window?

No; a single workbook can have multiple workbook windows.

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.

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

Monday!C20

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

Monday!$C$20

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

Monday:Friday!C20

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

=MAX(Monday:Friday!C20)

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

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

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

'[C:/Documents/Reports/Weekly Report.xlxs]Sunday'!D10

When would you paste a copied cell using the Paste Link option?

When you want to paste the reference to the cell and not the cells value or format

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.

How does excel indicate that a cell contains a linked text?

the text is displayed in green and underlined

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

It opens an application associated with the type of link

Why is Report-Date not a valid names range?

Contains a dash in the name, Range names can only be separated by underscore or no space.

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

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

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

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

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

EmployeeList1

By default, where does Excel store workbook templates?

In the user's Custom Office Templates folder.

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.

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 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.

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.

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

From the earliest date and time to the latest

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 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.

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

With an AND logical operator

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

to join several fields with an OR logical operator

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?

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

Employees[Income]

What is the formula to calculate the average of the income field from the employees table?

=AVERAGE(Employees[Income])

What is the structural reference to the table header row?

[#Headers]

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 listen within the slicer and when you want to perform the filter on a separate worksheet from the data.

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.

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

No, slicers cannot be used with data ranges

Can slicers be moved to any worksheet or external workbook?

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

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 calculate the average of the filtered values from the Sales Process field in the Sales_Result table.

=SUBTOTAL(1,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)

A school gives out grades in the following ranges: F: 0-<60; D: 60-<70; C: 70-<80; B: 80-<90; A: 90-100. Create a vertical lookup table for this grade scale with the range values in the first column and the letter grades in the second.

0 F

60 D

70 C

80 B

90 A

What is the VLOOKUP function to retrieve the latter grade stored in the second column of the GradeScale table using a lookup value of 83 in an approximate match lookup?

=VLOOKUP(83, GradeScale, 2)

What is the XLOOKUP to perform an approximate match lookup for a test score of 83? assume the column containing the lower end of the range of test score is named TestScores and the range containing the grades is named Grades.

=XLOOKUP(83,TestScores,Grades,,-1)

How does XLOOKUP differ from VLOOKUP in the placement of the column of lookup values?

With VLOOKUP, the lookup column must be the first column in the table. With XLOOKUP, the column can be placed anywhere within the table or even outside the table entirely.

How does XLOOKUP differ from VLOOKUP in how it handles failed matches?

VLOOKUP will always return the error value #N/A in the case of failed matches, XLOOKUP allows you to specify a return vale in the case of a failed match

What is the function to count the number of cells in B1:B50 that equal "B"?

=COUNTIF(B1:B50, "B")

What is the function to calculate the average of the cells in the range C1:C50 for which the adjacent cell in the range B1:B50 equals "B"?

=AVERAGEIF(C1:C50, "B", B1:B50)

What is the function to calculate the sum of the values in range D1:D50 for which in the adjacent value in the range A1:A50 equals "Senior" and the adjacent value in the range B1:B50 equals "B"?

=SUMIF(D1:D50, A1:A50, "Senior", B1:B50, "B")

What is a PivotTable?

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

What are the four primary areas of a PivotTable?

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

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

Count

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

SUM

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

alphabetically for text fields, by date for date fields

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 are two ways of filtering a pivot table?

by adding a field to the filters area of the table or by using the filter buttons for the files in the row and column areas

What are the four areas of a PivotChart?

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

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.

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.

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.

When do you need to refresh a PivotTable?

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

Category Field

A field that groups the values in a PivotTable; appears in a PivotTable as a row label, a column label, and a report filter.

Criteria Filter

Conditions you specify for a filter.

Custom List

The sequence you specify to sort data

Freeze

The process of keeping rows and/or columns you select visible as you scroll the worksheet.

PivotTable

an interactive table used to group and summarize either a range of data or an Excel table into a concise, tabular format for reporting and analysis

Record

A group of related fields, each row in an Excel table or structured range of data represents a record.

Slicer

An object used to filter an Excel table, a PivotTable, or a PivotChart.

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 thee top of each row group. Tabular layout places those fields in separate columns with subtotal rows placed at the bottom of each row group.

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

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

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 data of a field by the item labels within the field

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 a PivotTable field is manually grouped, how does Excel treat the grouped items?

As a new field in the PivotTable field list

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.

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 calculations options.

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 is the PivotTable cache?

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

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.

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 is the difference between a calculated item and a calculated field?

A calculated item is a 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.

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

All calculated fields are summarized using the SUM function

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 files that should not be summed.