BITM Ch.4 Excel

What should you do to ensure that records in a table are unique?

b. Use the Remove Duplicates command

Which statement is not a recommended guideline for designing and creating an Excel table?

c.Leave one blank row between records in the table

A structured reference is a tag, or the use of a table element such as a field heading, as a reference in a formula. Ex:Purchase Price*Quanitty instead of B6*E5

A fully qualified structured reference references the name of the object such as the Totals table and the corresponding field name such as Owed (column L). Better if need to use outside of the table. EX:=Totals[@[Purchase_Price]*Totals[@Quantity]-Totals[@Rebate] The use of field headings without the name of the table in a structured formula is called an unqualified reference. Ex:=[@[Purchase_Price]*[@Quantity]-[@Rebate]

Can create a table with quick analysis (can add rows, remove duplicates, etc.)

A table style controls the fill color of the header row (the row containing field names) and rows of records.

Excel enables you to create and maintain data structure. Data structure is the organization method used to manage multiple data points within a dataset.

An Excel table is a group of related data organized in a series of rows and columns that is managed independently from any other data on the worksheet. Once a data range is converted into a table, each column represents a field, which is an individual piece of data, such as last names or quantities sold. Sepaeerate the smalled possible unit of data like name to first name and last name.

Data bars apply a gradient or solid fill bar in which the width of the bar represents the current cell's value compared relatively to other cells' values

Color scales format cells with different colors based on the relative value of a cell compared to other selected cells. You can apply a two- or three-color scale.

Sorting is the process of arranging records by the value of one or more fields within a table.(can create custom sorts)

Filtering is the process of displaying only those records that meet certain conditions. (can apply date, color, custom, etc. filters)

You can further restrict what is printed by setting the print area, which is the range of cells that will print. its helpful to print descriptive stuff like titles

Print order is the sequence in which the pages are printed. By default, the pages print in the following order: top-left section, bottom-left section, top-right section, and bottom-right section.

Each row in a table represents a record, which is a collection of related data about one entity.

Structure ur table before u create it with these in mind... Enter field (column) names on the top row of the table. Keep field names short, descriptive, and unique. No two field names should be identical. Format the field names so that they stand out from the data. Enter data for each record on a row below the field names. Do not leave blank rows between records or between the field names and the first record. Delete any blank columns between fields in the dataset. Make sure each record has something unique, such as a transaction number or ID. Insert at least one blank row and one blank column between the table and other data, such as the main titles. When you want multiple tables in one workbook, a best practice is to place each table on a separate worksheet.

An Excel total row displays below the last row of records in an Excel table and enables you to display summary statistics, such as a sum of values displayed in a column.

The SUBTOTAL function calculates an aggregate value, such as totals or averages, for displayed values in a range, table, or database. =SUBTOTAL(function_num,ref1)

Freezing is the process of keeping rows and/or columns visible onscreen at all times even when you scroll through a large dataset.

a page break, indicates where data will start on another printed page.

Which of the following characters is a wildcard in Excel?

a. * or ?

Which of the following is an unqualified structured reference?

a. =[Purchase_Price] - [Rebate]

You are working with a large worksheet. Your row headings are in column A. Which command(s) should be used to see the row headings and the distant information in columns X, Y, and Z?

a. Freeze Panes command

Which date filter option enables you to restrict the view to only dates between June 16, 2024, and June 30, 2024?

d. Between

You have a large dataset that will print on several pages. You want to ensure that related records print on the same page with column and row labels visible and that confidential information is not printed. You should apply all of the following page setup options except which one to accomplish this task?

d. Change the print page order

Which conditional formatting rule is best suited to highlight sales value less than $300?

d. Less Than

Which of the following is not an aggregate function that can be applied in a total row?

d. VLOOKUP

Which of the following conditional formatting rules cannot be applied using Quick Analysis?

d. less than

Conditional formatting applies formatting to any set of data to highlight or emphasize cells that meet specific conditions.

like IF function Highlight Cells Rules category enables you to apply a highlight to cells that meet a condition, such as cells containing values greater than a particular value. the Top/Bottom Rules category enables you to specify the top or bottom number, top or bottom percentage, or values that are above or below the average value in a specified range.