NIU OMIS 259 Excel Chapter 4

How does Excel indicate that a column has been selected in a structured reference? A) A type of colored sticky note is attached to that column. B) There is nothing to indicate a column has been selected. C) A light shading appears in that column. D) A colored border appears around that column.

A colored border appears around that column

If you wanted every cell that has a value over $1,251 to have a purple fill, you would use what feature in Excel? A) AutoComplete formatting B) Aggregate Cell formatting C) AutoFill formatting D) Conditional formatting

Conditional formatting

What cannot be found on the Manage Rules dialog box? A) New Rule B) Edit Rule C) Copy Rule D) Delete Rule

Copy rule

To what does the @ symbol refer in a structured reference? A) Current column B) Current cell C) Current table D) Current row

Current row

The first field in this area creates a filter that appears in cell B1

FILTERS area

A PivotTable is limited to a single table of data.

False

A calculated field must be built in the original data set.

False

Even if panes are frozen pressing Ctrl+Home will still make A1 the active cell.

False

True or False: "John Smith" is a good example of a field.

False

True or False: Before you freeze panes make sure the active cell is one row above and one column to the left of the rows and columns you want to freeze.

False

True or False: Excel uses dashed blue lines to indicate manual page breaks.

False

True or False: Excel uses solid blue lines to indicate automatic page breaks.

False

True or False: Filter arrows are always displayed in data tables.

False

True or False: In Normal view or Page Layout view the data to be printed has a dark blue border.

False

True or False: In Page Break Preview, the area to be printed has a light blue background.

False

True or False: It is impossible to create a data table from random, unorganized data.

False

True or False: Locking is the process of making sure that certain rows or columns are visible at all times.

False

True or False: Records are individual pieces of information.

False

True or False: Selecting a range automatically makes it a data table.

False

True or False: To print row labels on the left side of each page you would select the row(s) that contain the labels in the rows to repeat at left box.

False

True or False: You can manually adjust a page break by pointing to the page break line and waiting for the four-headed arrow to appear, then drag the page break to your desired location.

False

True or False: You cannot sort cells by their fill color.

False

In a data table each column is considered a(n) ________.

Field

When you set up various conditions with the purpose of only showing a subset of data, that process is called ________.

Filtering

Which one of the following filter options is not found on the date filter submenu? A) Between B) First Quarter C) Last Month D) Tomorrow

First Quarter

What is not true about creating a data table? A) Each record should be unique somehow. B) For efficiency, make sure your data table does not exceed 256 rows. C) Delete any blank columns in your table. D) It is best to put individual data tables on separate sheets.

For efficiency, make sure your data table does not exceed 256 rows

What feature in Excel automatically displays a list of field headings when you begin to create a structured reference? A) Formula AutoComplete B) Reference AutoComplete C) AutoComplete D) Nothing automatically displays a list of field headings.

Formula AutoComplete

Which of the following is not a freeze option? A) Freeze Top Row B) Freeze First Column C) Freeze Panes D) Freeze Section

Freeze section

When you include table names in a structure reference it is called a(n) ________ structured reference.

Fully qualified

Which table style option shows the field names when it is checked? A) Banded Columns B) Total Row C) First Column D) Header Row

Header Row

Which conditional formatting option would you choose if you wanted to apply formatting to cells whose value falls between two numbers? A) Top/Bottom Rules B) Highlight Cells Rules C) Color Scales D) Between Values Rules

Highlight cells rules

By default Excel prints page in what order? A) All the even numbered pages, then all the odd numbered pages B) All the odd numbered pages, then all the even numbered pages C) Top-left section, top-right section, bottom-left section, and bottom-right section D) Top-left section, bottom-left section, top-right section, and bottom-right section

Top-left section, bottom-left section, top-right section, and bottom-right section

A PivotChart has live updates as you change the PivotTable

True

On which tab will you find options if you have to print a large dataset? A) Page Layout B) Home C) Print D) Review

Page layout

A(n) ________ is an interactive table which uses calculations to consolidate and summarize information

PivotTable

Using the Print ________ can help you save paper.

Preview

Which wildcard character only replaces one character with one other character? A) Tilde ~ B) Number sign # C) Question Mark ? D) Asterisk *

Question Mark ?

The field(s) used to organize a PivotTable along the left side of the table.

ROWS area

Which category or categories display in the PivotTable, if you see this slicer?

Regular Meal

A(n) __________ is the association between tables where both tables contain a common field

Relationship

Auto Outline creates an outline based on the data structure

True

What data does Excel print by default? A) The entire dataset on the active sheet B) The entire workbook C) The selected range of data D) There is no default setting for this in Excel.

The entire dataset on the active sheet

When you have frozen rows and columns, what happens when you press the Ctrl+Home combination? A) A1 becomes the active cell. B) The first unfrozen cell becomes the active cell. C) Row 1 and whatever column was active when you pressed the combination becomes the active cell. D) Column A and whatever row was active when you pressed the combination becomes the active cell.

The first unfrozen cell becomes the active cell

What dictates the order of hierarchy of fields in the rows of a PivotTable

The order of the fields in the ROWS area.

In the formula =SUBTOTAL(102,[PAID]), what does the word PAID represent? A) The range to be included in the calculation B) The PAID record C) The label for the resultant Total Row D) The Excel function called PAID

The range to be included in the calculation

If you use the Icon sets conditional formatting, you are limited to how many categories? A) Three, four, or five B) One, two, or three C) Four, five, or six D) Two, three, or four

Three, four, or five

True or False: After a table has been named, that name can be used in functions instead of cell references.

True

True or False: In a data table calculated columns will copy down the table automatically.

True

True or False: It is a good practice to print column and row labels on each page.

True

True or False: Rows and/or columns which have been deleted in a data table can be brought back with the Undo command.

True

True or False: This could be a valid formula in Excel: =[@Cost]-[@Pre_Paid_Taxes].

True

True or False: When you enter a structured reference in a formula Excel copies that formula down the rest of the column automatically.

True

True or False: When you use the Freeze Panes option you freezing both columns and rows.

True

True or False: You can add a row to a data table as long as you put in the row immediately below the table.

True

True or False: You can freeze as many columns and rows at the same time as you desire.

True

True or False: You can select multiple print areas.

True

Which conditional formatting option is not found in the Quick Analysis Gallery? A) Duplicate Values B) Unique Values C) Text Contains D) Undo Format

Undo Format

If you create a Recommended PivotTable on a group of data about book sales with these columns: Discipline (i.e. Business), Area (i.e. Finance and Marketing), Units Sold, Wholesale Price, what column might Excel use as values?

Units Sold

In a structured reference, when you use field headings without row references it is called a(n) ________ reference.

Unqualified

Fields added to this area have a calculation of some sort applied

VALUES area

In a data table each row is considered a(n) ________.

record

Table ________ add color and borders to a table.

style

A(n) ________ is a range of organized, related data

table

Rows and/or columns which have been deleted in a data table can be brought back with the Undo command.

true

Match the following terms with their description: I. Color scale II. Icon set III. Filtering IV. Sorting V. Data bars A. Colors are based on the relative values in the cell. B. Arranging records C. Symbols or signs that classify data D. Various widths represent the cell's value. E. Using conditions to limit the visible data

1.a 2.c 3.e 4.b 5.d

Match the following Conditional Formatting Options in the Quick Analysis Gallery with their description: I. Text Contains II. Duplicate Values III. Unique Values IV. Equal To V. Clear Format A. Formats cells that have exactly the same value B. Formats any cell that has content that matches the first content in the first selected cell C. Removes any conditional formatting D. Formats cells that have no matching values E. Formats cells that have the value

1.b 2.a 3.d 4.e 5.c

Match the following terms with their description: I. Print area II. Print order III. Page break IV. Freezing V. Conditional formatting A. Where data will print on the next page B. The range of cells that will be printed C. Rules used to emphasize cells D. Keeps certain rows or columns on the screen at all times E. Sequence of printed pages

1.b 2.e 3.a 4.d 5.c

Match the following Subtotal Function with their Table Numbers: I. AVERAGE II. MAX III. MIN IV. SUM V. COUNTA A. 104 B. 103 C. 101 D. 109 E. 105

1.c 2.a 3.e 4.d 5.b

Match the following terms with their description: I. Unqualified reference II. Fully qualified structured reference III. Structured reference IV. SUBTOTAL function V. Total row A. Uses a field name as the reference in a table B. Contains the table name C. Field headings without row references D. Displays below the last row in a table E. Calculates an aggregate value

1.c 2.b 3.a 4.e 5.d

Match the following Conditional Formatting Options with their description: I. Highlight Cells Rules II. Top/Bottom Rules III. Data bars IV. Color Scales V. Icon Sets A. Formats cells if they fall in the lowest % (% determined by the user) B. Formats cells with gradients of color depending on the cell's value compared to the other values in the range C. Formats cells with colors whose width is proportional to the other values in the range D. Formats cells if a date falls in a certain range E. Uses symbols to represent the comparison of values

1.d 2.a 3.c 4.b 5.e

Match the following table style options with their description: I. Header Row II. Total Row III. First Column IV. Last Column V. Banded Rows A. The formatting of this row/these rows take/takes priority over column formatting. B. Alternate fill colors distinguish one row from another row. C. Makes sure that the row headings stand out D. Displays the field names when checked E. Often used for aggregated data

1.d 2.a 3.c 4.e 5.b

Match the following table style options with their description: I. Header Row II. Total Row III. First Column IV. Last Column V. Filter Button A. Displays a total row when selected B. Makes sure that the row headings stand out C. Displays on the right side of each heading in the header row D. Used to make aggregated data stand out E. Removes field names when not checked

1.e 2.a 3.b 4.d 5.c

Match the following terms with their description: I. Field II. Record III. Table IV. Table style V. Total row A. Includes complimentary fill colors B. Complete set of fields for one entity C. Structured range D. Appears below the last row in a table E. Smallest piece of data collected

1.e 2.b 3.c 4.a 5.d

Match the following Data Type Sort Options with their example: I. Sort Z to A II. Sort Oldest to Newest III. Sort Largest to Smallest IV. Sort A to Z V. Sort Newest to Oldest A. Number; Othniel; Psychology B. 11/20/2015; 8/20/2015; 5/25/91 C. 58; 29; 27 D. 1/16/1957; 12/26/1959; 1/7/87 E. Karen; Doug; Barb

1.e 2.d 3.c 4.a 5.b

If you use the Color Scale conditional formatting, you are limited to how many colors (and their gradations?) A) Unlimited B) 2 or 3 C) 1,012 D) 256

2 or 3

By how many levels will Excel allow you to sort? A) 32 B) 128 C) 64 D) 16

64

Excel will allow you to sort on ________ levels.

64

Which of the following would be an effective calculated field labeled Retail Price to calculate a retail price that is 300% of wholesale price (for example, the wholesale price is $3 and retail price is $9) from a Wholesale Price field?

= Wholesale Price * 3 with a Name of Retail Price in the Insert Calculated Field dialog box

You created a slicer on the field State. What items would you be most likely to see in the slicer

AL, CA, GA, KY, MS, NC, OR, and VA

A subtotal row must contain at least one ______ function

Aggregate

What is not true about using the AND function in a custom conditional formatting rule? A) You may have as many tests as you need in the formula. B) You must have at least two tests in the formula. C) All the tests must be of the same data type, for example: all numbers or all text. D) All the logical tests must be true for the formatting to be applied.

All the tests must be of the same data type, for example: all numbers or all text

If you wanted several cells to be filled with a certain color where the width of the fill color in the cell represents that cell's value compared to the values in the other cells, you would use the Data ________ conditional formatting option.

Bars

Where does Excel display the Total row in a table? A) Below the last row in the table B) After each subtotal in the table C) At the top of the table D) Two rows below the last row in the table

Below the last row in the table

In a structured reference what do you use to indicate a column heading or field name? A) Parenthesis () B) Curly brackets {} C) Brackets [] D) Quotes ""

Brackets []

Using the data bars conditional formatting, how is the highest value represented? A) By the most number of bars B) By the longest bar C) By the darkest bar D) By the tallest bar

By the longest bar

The field(s) used to organize a PivotTable along the top of the table.

COLUMNS area

A(n) ______ is a user-defined field that derives its value by performing calculations on other fields in a PivotTable

Calculated field

Which of the following is not a characteristic of a table? A) Can contain multiple ranges B) Contains related data C) Helps to analyze information D) Helps to manage information

Can contain multiple ranges

You created a PivotTable and later made changes to values in the underlying data. What happens to the PivotTable?

Changes in the dataset do not affect the PivotTable until you refresh the PivotTable.

Which is an effective way to change a SUM of Price field in the VALUES area to an AVERAGE function

Click the field's arrow in the VALUES area and select Value Field Settings

How would you add a date filter to a PivotTable with these fields: Date, Category, Item, Unit Price, Number Sold, and Extended Price?

Drag the Date field to the FILTERS area

Which one of the following filter options is not found on the text filter submenu? A) Custom Filter B) Includes C) Contains D) Begins With

Includes

What would be the result if you sorted the following dates in ascending order: December 25, 2000; January 1, 2015; January 16, 1957; December 26, 1959? A) January 16, 1957; December 26, 1959; December 25, 2000; January 1, 2015 B) January 1, 2015; January 16, 1959, December 25, 2000; December 26, 1959 C) December 25, 2000; December 26, 1959; January 1, 2015; January 16, 1959 D) December 25, 2000; January 1, 2015; January 16, 1957; December 26, 1959

January 16, 1957; December 26, 1959; December 25, 2000; January 1, 2015

Which table style option is useful to display aggregated data when it is checked? A) Last Column B) Header Row C) Filter Button D) Total Row

Last column

What is not true about creating a data table? A) Make sure to leave one blank row below the data labels. B) Format the field names so that they stand out visually. C) Keep field names short. D) Make sure no column labels are the same.

Make sure to leave one blank row below the data labels

What is the default data table style? A) Medium 1 B) Medium 2 C) Normal D) None

Medium 2

What is not true about tables in Excel? A) Column headings stay on the screen automatically. B) You can export the data to a SharePoint list. C) Filter arrows make it quick and easy to filter the data in the table. D) Row headings stay on the screen automatically.

Row heading stay on the screen automatically

What is the best option for a field to build a relationship between the tables Sales Personnel and Orders with the following fields?

Sales person ID

What would you do to format all values as Currency in the Sum of Extended price field no matter how you pivot the data

Select Number Format and choose Currency in the list of options and then click OK.

A small window containing one button for each unique item in a field is:

Sliver

You can see how many records meet the conditions of the filter by looking at the ________ bar.

Status

Data ________ is the method used to organize data points in a dataset.

Structure

________ references help to clarify what type of data is being used in any calculations.

Structured

The ______ function is the default summary statistic for PivotTables

Sum

By default, what is the first table on a worksheet named? A) Table B) Table A C) Table 1 D) Data tables do not have names.

Table 1

Which of the following is not a conditional formatting option? A) Data bars B) Table format C) Color Scales D) Icon Sets

Table format

The filter "Begins With..." option is found on which type of filter? A) Custom filter B) Text filter C) Number filter D) Date filter

Text Filter

In the formula =SUBTOTAL(102,[PAID]), what does the 102 represent? A) The number of rows to subtotal B) The row number in which to place the subtotal C) The COUNT function D) The SUM function

The COUNT function

In conditional formatting what happens if the cell value does not pass the logical test? A) The cell is formatted however you have determined for the FALSE result. B) The cell is formatted with Excel's default FALSE format. C) A small green triangle displays in the top-left corner of the cell. D) The cell is not formatted.

The cell is not formatted

When would you not need to use a fully qualified structured reference? A) When referring to a cell on a different worksheet B) When referring to a cell on a different workbook C) When referring to a cell within the table D) When referring to a cell outside the table

When referring to a cell within the table

What is true about adding a new field to the right side of an existing data table? A) You must insert the new field somewhere inside the existing table, then move the columns to the order you desire. B) You cannot add a new field to an existing data table. C) You can add it to the right of the data table and Excel will extend the table to include the new field. D) You must convert the table back to a range, add the field, then recreate the table.

You can add it to the right of the data table and Excel will extend the table to include the new field.

A collection of fields about one person would be considered a(n) ________.

record

Which of the following is not a way to bring up the Go To dialog box? A) F5 B) Ctrl+G C) Alt+F7 D) Name Box

alt+f7

The print ________ is the range of cells that will be printed.

area

Which one of the following filter options is not found on the number filter submenu? A) Below Average B) Bottom 10... C) Greater Than... D) Between...

bottom 10...

If you wanted to sort the days of the week in their natural order, not alphabetically, you would set up a(n) ________ sort.

custom

In Normal view or Page Layout view the data to be printed has a dark blue border.

false

You cannot sort cells by their fill color.

false

Use the Sort by ________ Color if you want to sort records by the color of the text.

font

The Table Styles ________ shows you all the table styles which are available.

gallery

To go to the first unfrozen cell you would press Ctrl+________ at the same time.

home

________ page breaks can be inserted if the automatic page breaks would occur at undesirable places.

manual