Excel Test Module 6

Custom sort lists (2)

Day of the week and month of the year

First Column

Leftmost column of the table

When sorting multiple fields, what is the first sort field called?

Primary field

Subtotals

Summary functions applied to a part of a data range

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

With an AND logical operator

Sorting by a Single Field

You can sort any data range by any field inascending or descending order

Find and Select

can locate cells that match a specified criterion. Find &Select is a quick and easy way of locating cells of a specific type within a worksheet and across worksheets.

What are custom sort lists used for?

creates custom lists to sort records in a sequence you define

How many sort fields are possible?

64

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

=AVERAGE(Employees[Income])

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

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

Calculated field

A field that also contains a formula that references other fields in the table

Criteria Range

A location separate from the table used to list specific search specifications.

Total row

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

Custom List

A sequence you specify to sort data.

Ascending order

A to Z, numbers from smallest to largest, and dates from oldest to newest

Slicer

An object containing a button for each unique value from a field

Calculating Subtotals

Analyzing a large data range usually includes making calculations on the data. You can summarize the data by applying summary functions such as COUNT, SUM, and AVERAGE to the entire data range.

Filter buttons

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

Criteria Filters

Conditions you specify for a filter. ( study figure 6-20)

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

Data definition table

Documentation that lists the fields to be maintained for each record, a description of the information each field will include, and the type of data stored in each field

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

Earliest date and time to latest

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

Employees[Income]

Pro Skills Planning for Data entry

Excel can split the workbook window in up to four sections called panes with each pane offering a separate view into the worksheet.

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

Four panes

What are the three freeze pane options?

Freeze panes , Freeze Top Row, and Freeze First Column

Secondary Sort Field

In Excel, a second field that determines the order of records in an object that are already sorted by a primary sort field.

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

How are slicers arranged as a default?

In a single column

Advanced Filtering

In an Excel table or data range, a way of writing more complicated filter criteria that involves expression that combine fields using the AND and OR logical operators.

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

Excel Table

Range of data that is treated like a single object.

Structural references

References to fields and elements in an Excel table that are enclosed within square brackets, [ ]

Structural References

Refers to a structural element of a table. Enclosed with square brackets.

Last column

Rightmost column of the table

When sorting multiple fields, what is the second sort field called?

Secondary sort field

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.

Header row

The first row of the table containing the field names

Primary Sort Field

The first sort field specified that determines the orderof records in an object.

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

Banded columns

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

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 would you use an advanced filter in place of the filter buttons?

To join several fields with an OR logical operator

Freeze

To keep rows and/or columns you select visible as you scroll a worksheet.

Descending order

Z to A, numbers from largest to smallest, and dates from newest to oldest

What is the structural reference to the table header row?

[#Headers]

A dashboard

a page or screen providing informative visuals of data, key performance indicators, and statistics

Dashboard

a page or screen providing informative visuals of data, key performance indicators, and statistics. Most dashboards contain interactive tools to help users explore data under different conditions and assumptions. (like an automobile dashboard)

Filtering data

hides the rows whose values do not match the search criteria

Data definition table

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

Conditional formatting can...

locate a duplicate record byhighlighting duplicate values within a selected range.

Banded rows

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

What does the "@" symbol mean in a structural reference ?

refers to the current record or row within the table

Panes

splits the workbook window in up to four sections called panes, with each pane offering a separate view into the worksheet

Subtotal row

summary functions that are applied to apart of a data range

What is one limit of using conditional formatting to locate duplicate records?

the process findsduplicate values only in a single field. It cannot highlight records thathave several duplicated fields.

Why are slicers often used in dashboards?

they provide a simple and intuitive wayto filter data.

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

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

Remove Duplicates Tool

to locate and delete records that are duplicatedacross multiple fields.

Dynamic charts

update automatically as the source data is filtered so that hidden records do not contribute to the chart's appearance (ideal for dashboards)