dfgjkjshgksdf

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 Reports]Sunday'!D10

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

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

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

1 few distinct values are being used 2when you are using data from a table in a diff worksh

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

2

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

=AVERAGE(Employee[income])

7. 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(AVERAGE,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(SUM,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)

drilling down

A PivotTable summarizes data, but sometimes you will want to examine the data records themselves. You can do this by ________________ the PivotTable to display the records responsible for the PivotTable calculations. To _____ a PivotTable, double-click any cell within the table. Excel then opens a new sheet displaying the records used to calculate that PivotTable value.

calculated field,

A field can also contain a formula that references other fields in the table. it updates automatically as other field values in the table change.

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

Book#

Filter buttons

Buttons next to each field name for filtering and sorting the table data

By default, where does Excel store workbook templates?

Custom Office Templates folder

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 sec-ondary sort field?

Dept field primary hire date secondary

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

Employees[Income]

What is the expression to reference the local scope defined name TotalSales from the Final Report worksheet?

Final Report!TotalSales

Type.

Identifies the type of source file, such as an Excel worksheet, a Word docu-ment, or a PowerPoint slideshow

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?

In the Edit links dialogue box, "OK" means the link updated

Source.

Indicates the source file for a given link

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

It takes you to the range of data the defined name is applied to

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 worksheet?

Monday!C20

6. What is the 3-D cell reference to cell C20 in the Monday through Friday work-sheet group?

Monday:Friday!C20

structural references

References to fields and elements in an Excel table enclosed with square bracketts

Status.

Shows whether the data source has been accessed during the current session and if so, whether the link has been updated

Update.

Specifies how values are updated from the linked data source, where the let-ter A indicates the link is updated automatically upon opening the workbook and M indicates that the link is updated manually in response to your request

header row,

The first row of a data range, known as the ___________ typically contains the field names

Banded columns—

The odd-and even-numbered columns of the table formatted differently to make fields easier to distinguish

Banded rows—

The odd-and even-numbered rows of the table formatted differently to make records easier to distinguish

COUNTIFS function, SUMIFS function and AVERAGEIFS function

To apply multiple criteria for a conditional count, COUNTIFS(range1, criteria1, [range2], [criteria2] ...) where range1 is the range in which to count cells indicated in criteria1; the optional arguments range2, criteria2, and so on are additional ranges and criteria for choosing which cells to count. You can include up to 127 range/criteria pairs. SUMIFS(sum_range, range1, criteria1, [range2], [criteria2] ...) AVERAGEIFS(avg_range, range1, criteria1, [range2], [criteria2] ...) where sum_range is a reference to cells to sum and avg_range is a reference to the cells to average.

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

When you are copying a range from another workbook and including it in an external reference

primary sort field

With sorts that involve multiple fields or columns, you identify one field as the ____________ by which to initially sort the data

What is the structural reference to the table header row?

[#Header]

custom list

arranges the field values in the order you specify.

Why is Report-Date not a valid named range?

because of the dash

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

because they could have the same first and last name and you don't want to delete an employee

conditional sum.

calculates the sum of values that match specified criteria, The syntax of the SUMIF function is SUMIF(range, criteria, [sum_range]) where range is the range of cells to be evaluated by the criteria specified in the criteria argument, and sum_range is an optional argument that specifies the values to sum.

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

can only be used with excel tables

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

combined using the AND logical operator and in advanced filter the OR logical operator

OR function

combines multiple conditions, returning a value of TRUE if any of the conditions are true. OR(logical1, [logical2], [logical3], ...)

conditional count

count only those records that match a specified condition, using the COUNTIF function COUNTIF(range, criteria) where range references the range of cells or table field to be counted and criteria is a value or a condition that defines which cells to include in the count.

wildcard

d is a symbol that represents any character, much as wildcards in poker can take on any card value. (?) _____ , which represents any single charac-ter and the asterisk (*) ______ which represents any string of characters.

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

data/row is hidden

Rows area—

displays category values from one or more fields arranged in separate rows

What is a field? What is a record?

field column row record

profit and loss (P&L) statement, also called an income statement,

financial statement that summarizes the income and expenses incurred during a specified interval. _________ are usually released monthly, quarterly, and annually.

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

for accuracy and efficiency- they can just enter the data and it will be the same as everyone elses

secondary sort field

for sorting data within the primary sort field.

What are the three freeze pane options?

freeze top and left of selected cell freeze top row freeze first column

What is the difference between a defined name with global scope and one with local scope?

global scope is recognzed through the whole workbook local scope recognized only in worksheet it is named in

indirect referencing

in which the reference itself is a calculated value. Indirect references are created with the INDIRECT function

freeze

its contents are always visible though you cannot scroll within it.

criteria range

lays out the specifications of the filter. ________ are placed in a table with the following structure: 1. Field names are listed in the first row of the table and must exactly match the field names from the data range. A field name can be repeated in the table. 2. Criterion for each field are listed in subsequent rows of the table. 3. Criteria within the same row are combined using the AND logical operator. 4. Criteria in different rows are combined using the OR logical operator.

partial lookup

matches a character pattern rather than a specific value

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

no

Can a workbook have only one window?

no it can have multiple

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

oldest to most recent

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

opens the source using the application related to the link

Advanced filtering

provides a way of writing more complicated filter criteria that involve expression that combine fields using the AND and OR logical operators.

AND function

returns a value of TRUE if all of the conditions are true. The two functions have a similar syntax: OR(logical1, [logical2], [logical3], ...) AND(logical1, [logical2], [logical3], ...)

MATCH function

returns the position of a value found within a row or column. The syntax of the MATCH function is MATCH(lookup_value, lookup_array, [match_type=1])

INDEX function

returns the value from a data range at the intersection of a specific row and column, and has the syntax: INDEX(array, row_num, col_num)

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

right-click any worksheet tab in the group and click ungroup sheets. or click any worksheet tab outside the group.

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

sort the datadata/row

PivotTable cache,

stores information about the PivotTable.

subtotals,

summary functions that are applied to a part of a data range.

conditional average

taking the average only of those values that match specified criteria The syntax of the AVERAGEIF function is AVERAGEIF(range, criteria, [average_range]) where range is the range of cells to be evaluated by the criteria specified in the criteria argument, and average_range is an optional argument that specifies the values to be averaged. T

IFS function

tests for multiple conditions without nesting and has the syntax IF(logical_test1, value_if_true1, logical_test2, value_if_true2, ...)

ascending order

text entries are arranged alphabetically from A to Z, numeric values are sorted from smallest to largest, and date and time values are sorted from oldest to most recent

descending order

text entries are sorted from Z to A, numeric values are sorted from largest to smallest, and dates and times are sorted from most recent to oldest.

path

the exact location of the workbook file using the expression

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

they automatically update to make sure the tables are easy to read

MINIFS function and the MAXIFS function

to calculate the minimum and maximum value in a range under multiple criteria, MINIFS(min_range, range1, criteria1, [range2], [criteria2] ...) MAXIFS(max_range, range1, criteria1, [range2], [criteria2] ...) where min_range and max_range reference the cells in which to find the minimum and maximum value, subject to constraints specified in the range/criteria pairs.

How do you create a worksheet group consisting of sheets that are not adja-cent in a workbook

to select a nin-adjacent worksheet group, click the sheet tab of one worksheet in the group, press and hold the CTRL key, click the sheet tabs of the remaining worksheets in the group, and then release the CTRLnkey.

How does Excel indicate that a cell contains linked text?

underlined green font

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

when you need to use the AND and OR logical operators

criteria filters,

which are expression involving dates and times, numeric values, and text strings

data definition table,

which lists the fields included with each record, the type of data stored in each field (such as numbers, text, or dates), and a short description of each field.

Can slicers be moved to any worksheet or external workbook?

yes

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

you need to use criteria filter such as text, numeric value, date

Total row—

—A row at the bottom of the table containing summary statistics for selected fields

Filters area—

—contains a filter button that limits the PivotTable to only those values matching specified criteria

Columns area

—displays categories from one or more fields arranged in separate columns

Values area—

—displays summary statistics for one or more fields at each intersection of each row and column category