CSI-1200 - Intro Cmptr and Prgmng w/Excel - CH5,4,7
The _____________ wildcard symbol is often used in filters and can represent any number of characters.
, asterisk, star
If you use the Color Scale conditional formatting, you are limited to how many colors (and their gradations?)
2 or 3
By how many levels will Excel allow you to sort?
64
Which of the following is true about creating a blank PivotTable?
A PivotTable may be created on the same worksheet as the source data.
How does Excel indicate that a column has been selected in a structured reference?
A colored border appears around that column.
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
Which is not true about Slicers?
Aggregate values do not respond to slicers.
What is not true about using the AND function in a custom conditional formatting rule?
All the tests must be of the same data type, for example: all numbers or all text.
Which of the following is not a way to bring up the Go To dialog box?
Alt+F7
Which one of the following filter options is not found on the text filter submenu?
Includes
To go to the first unfrozen cell you would press Ctrl+_______ at the same time.
Home
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 valuesE. Formats cells that have the value
B, A, D, E, C
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?
Below the last row in the table
Which one of the following filter options is not found on the number filter submenu?
Bottom 10...
In a structured reference what do you use to indicate a column heading or field name?
Brackets []
Using the data bars conditional formatting, how is the highest value represented?
By the longest bar
Which of the following is not a characteristic of a table?
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.
if you wanted every cell that has a value over $1,251 to have a purple fill, you would use what feature in Excel?
Conditional formatting
What cannot be found on the Manage Rules dialog box?
Copy Rule
To what does the @ symbol refer in a structured reference?
Current row
If you need to perform complex filters on your data you will want to use the ____________ AutoFilter dialog box.
Custom
"John Smith" is a good example of a field.
F
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.
F
Even if panes are frozen pressing Ctrl+Home will still make A1 the active cell.
F
Excel uses dashed blue lines to indicate manual page breaks.
F
Excel uses solid blue lines to indicate automatic page breaks.
F
Filter arrows are always displayed in data tables.
F
In Page Break Preview, the area to be printed has a light blue background.
F
It is impossible to create a data table from random, unorganized data.
F
Locking is the process of making sure that certain rows or columns are visible at all times.
F
Records are individual pieces of information.
F
Selecting a range automatically makes it a data table.
F
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.
F
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.
F
Which one of the following filter options is not found on the date filter submenu?
First Quarter
Use the Sort by __________ Color if you want to sort records by the color of the text.
Font
What is not true about creating a data table?
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?
Formula AutoComplete
Which of the following is not a freeze option?
Freeze Section
The Table Styles _______ shows you all the table styles which are available.
Gallery
Which table style option shows the field names when it is checked?
Header Row
Which conditional formatting option would you choose if you wanted to apply formatting to cells whose value falls between two numbers?
Highlight Cells Rules
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?
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?
Last Column
What is not true about creating a data table?
Make sure to leave one blank row below the data labels.
__________ page breaks can be inserted if the automatic page breaks would occur at undesirable places.
Manual
What is the default data table style?
Medium 2
On which tab will you find options if you have to print a large dataset?
Page Layout
Using the Print _______ can help you save paper.
Preview
Which wildcard character only replaces one character with one other character?
Question Mark ?
What is not true about tables in Excel?
Row headings stay on the screen automatically.
The ________ function is the default summary statistic for PivotTables.
SUM
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.
You have a PivotTable with 2 fields in the ROWS area and you want to see only the top level of categories. How do you hide the entire second level without removing it from the ROWS area?
Select a field in the row headers and click Collapse Field in the Active Field Group on the Analyze tab.
A worksheet of faculty members at your university contains these columns in this sequence: Rank, Last Name, First Name, Department, and Salary. What is the first step to subtotal departmental salaries?
Sort by Department
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
Which is not true of filters in a PivotTable?
Summary statistics do not change to reflect the values selected in the slicer.
After a table has been named, that name can be used in functions instead of cell references.
T
In a data table calculated columns will copy down the table automatically.
T
Rows and/or columns which have been deleted in a data table can be brought back with the Undo command.
T
This could be a valid formula in Excel: =[@Cost]-[@Pre_Paid_Taxes].
T
When you use the Freeze Panes option you freezing both columns and rows.
T
You can add a row to a data table as long as you put in the row immediately below the table.
T
You can freeze as many columns and rows at the same time as you desire.
T
You can select multiple print areas.
T
By default, what is the first table on a worksheet named?
Table 1
Which of the following is not a conditional formatting option?
Table format
The filter "Begins With..." option is found on which type of filter?
Text filter
In the formula =SUBTOTAL(102,[PAID]), what does the 102 represent?
The COUNT function
In conditional formatting what happens if the cell value does not pass the logical test?
The cell is not formatted.
What data does Excel print by default?
The entire dataset on the active sheet
When you have frozen rows and columns, what happens when you press the Ctrl+Home combination?
The first unfrozen cell becomes the active cell.
In the formula =SUBTOTAL(102,[PAID]), what does the word PAID represent?
The range to be included in the calculation
If you use the Icon sets conditional formatting, you are limited to how many categories?
Three, four, or five
By default Excel prints page in what order?
Top-left section, bottom-left section, top-right section, and bottom-right section.
Which conditional formatting option is not found in the Quick Analysis Gallery?
Undo Format
If you create a Recommended PivotTable on a group of data about book sales with theses columns: Discipline (i.e. Business), Area (i.e. Finance and Marketing), Units Sold, Wholesale Price, what column might Excel use as values?
Units Sold
When would you not need to use a fully qualified structured reference?
When referring to a cell within the table
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
What is true about adding a new field to the right side of an existing data table?
You can add it to the right of the data table and Excel will extend the table to include the new field.
A subtotal row must contain at least one ________ function.
aggregate
The print __________ is the range of cells that will be printed.
area
Moving one of two fields from the ROWS area to the COLUMNS area of a PivotTable would ________.
consolidate data and remove redundant labels
If you wanted to sort the days of the week in their natural order, not alphabetically, you would set up a(n) __________ sort.
custom
A PivotTable time line can filter dates by all of these, except ________.
decade.
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
When you include table names in a structure reference it is called a(n) ____________ structured reference.
fully qualified
Grouping is ________.
joining rows or columns of related data into a single entity for analysis
A collection of fields about one person would be considered a(n) ____________
record
In a data table each row is considered a(n) _______
record
You can see how many records meet the conditions of the filter by looking at the ____________ bar.
status
In a data table you can use ________ references in place of cell references inside the formulas.
structured
Table ______ add color and borders to a table.
styles, style
An ______ is a range of organized, related data.
table
In a structured reference, when you use field headings without row references it is called a(n) _________ reference.
unqualified
Excel indicates where page breaks will occur by placing temporary ____________ (such as Page 1, Page 2) on your display.
watermarks
