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)