BUS 1350 REVISED EXAM 3 2025

Header row - -the first row of a data range, typically contains field names 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 Freezing panes - -allows rows and/or columns labels or titles to remain stationary on the screen while you scroll Splitting into panes - -makes two panes that you can scroll through either Ascending order - -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 - -sorted from z to a, numeric values are sorted from largest to smallest, and dates and times are sorted from most recent to oldest To sort data in an excel worksheet you can use the ______. - -sort a to z and sort z to a buttons Primary sort field - -to initially sort the data Secondary sort field - -for sorting data within the primary sort field Custom list - -arranges the field values in the sort order you specify You can summarize the data by applying summary functions such as ___, ___, and ___ to the entire data range - -count, sum, average Subtotals - -summary functions that are applied to a part of a data range What is a field? - -cell in a table What is a record? - -a group of related fields What are the three freeze pane options? - -freeze panes, freeze top row, freeze first column When highlighting duplicate values with a conditional format, do the duplicate values have to be adjacent to each other? - -no Before you can add subtotals to a data range, what must you first do with the data? - the data must first be sorted by a field, such as the office or dept field BUS 1350 BUS 1350 Find & select - -can locate cells that match a specified criterion, can also list multiple cells that satisfy the specified criterion To locate cells of a specific type, - -click the find & select button in the editing group on the home tab and then click go to special. To find records based on search criteria that involve multiple fields, you must - -filter the data Filtering data - -hides the rows whose values do not match the search criteria Criteria filters - -for more general criteria, expression involving dates and times, numeric values, and text strings. Text filters - -useful for locating records based on all or part of a specified text string For more advanced text filters, you can use - -wildcard characters, which are symbols that match a character pattern If you want to clear only one filter out of multiple filters, - -click the filter button for that field and then click clear filter on the filter menu. Advanced filtering - -provides a way of writing more complicated filter criteria that involve expression that combine fields using the and and or logical operators Criteria range - -necessary to write an advanced filter, lays out the specifications of the filter The main exception of excel tables is - -that you cannot add subtotals to an excel table because the table structure of field and records must be preserved, which doesn't allow for the insertion of subtotal rows Calculated field - -a field that can contain a formula that references other fields in the table, updates automatically as other field values in the table change Structural reference - -references to fields and elements in an excel table, enclosed within brackets What happens to worksheet rows that do not match the filter criteria? What happens to the data they contain? - -they're hidden What happens to banded rows in an excel table when you insert or delete a row? - -it corrects BUS 1350 BUS 1350 Slicer - -an object containing a button for each unique value from a field, make it clear what filters are being applied to the data at any moment Dashboard - -a page or screen providing informative visuals of data, key performance indicators, and statistics, contain interactive tools to help users explore data Can slicers be used with both data ranges and excel tables? - -slicers can be used in excel tables and pivottables Can slicers be moved to any worksheet or external workbook? - -yes Approximate match lookup - -a lookup in which the lookup value is compared to ranges of values rather than a single, specific value To perform an approximate match lookup with a vertical lookup table, you use - -the same vlookup function used for exact match lookups To retrieve data from a horizontal lookup table - -use the hlookup function Index function - -returns the value from a data range at the intersection of a specific row and column, and has the syntax Match function - -returns the position of a value found within a row or column An index match lookup has two main advantages over the lookup functions - -1. You can do an approximate match lookup based on ranges sorted in descending order rather than ascending order by setting match_type to −1. 2. You can locate values by matching terms from any column or row within the table. Ifs function - -tests for multiple conditions without nesting and has the syntax Or function - -combines multiple conditions, returning a value of true if any of the conditions are true And function - -returns a value of true if all of the conditions are true For conditions that involve multiple criteria ranges, use the - -countifs, sumifs, averageifs, maxifs, and minifs functions Countif function - -to count only those records that match a specified condition, you can create a conditional count Sumif function - -calculates the sum of values that match specified criteria, creating a conditional sum BUS 1350 BUS 1350 Averageif function - -calculates a conditional average by taking the average only of those values that match specified criteria Countifs function - -to apply multiple criteria for a conditional count Minifs/maxifs function - -to calculate the minimum and maximum value in a range under multiple criteria Pivottable - -an interactive table that groups and summarizes data in a concise tabular format Every pivottable includes the following four primary areas - -rows area, columns area, values area, filters area Pivottable cache - -stores information about the pivottable You can change the overall table layout. Pivottables have possible three report layouts - -compact form —(the default layout) places all fields from the rows area in a single worksheet columns and indents values to distinguish nested fields from other fields. Outline form —places each field in the rows area in its own column and includes subtotals above every field category group. Tabular form —places each field in the rows area in its own column and includes subtotals below every group. By default, how does a pivottable arrange the categories in a row or column? - -excel automatically sorts the row and column categories and displays missing combinations within the pivottable as blank cells What is default statistic is used for non-numeric data in the values area of the pivottable? - -text categories are listed alphabetically What happens when you place a date field in a row or column