Excel Advanced

Locking or unlocking cells in a worksheet does not have any meaningful effect until you ____ the overall worksheet.

• lock

• protect

• hide

• delete

Unlocked cells are NOT editable if a worksheet or workbook is protected.

• TRUE

• FALSE

When using Conditional Formatting, which technique will let you highlight the top three items in a column?

• Use the Top 10 Items rule, but edit the number of items to three instead of 10.

• Create a new rule to highlight the top three items in a column.

• Highlight the top 10, but then manually format the bottom seven of those 10.

• Use Conditional Formatting > Manage Rules

How can you delete conditional formatting from selected cells?

• Use Conditional Formatting > Manage Rules

• Manually format the cells to override any conditional formatting

• Use Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet

• Use Conditional Formatting > Clear Rules > Clear Rules from Selected Cells

Suppose you create a PivotTable and then make changes to the source data for that PivotTable. You return to the PivotTable, and notice that the changes are not showing up. What do you need to do?

• Go to Analyze > Refresh to update the PivotTable

• Go to Data > initialize to update the PivotTable

• Delete the PivotTable, then generate a new one

• Save the workbook to update the PivotTable

When creating an IF function, a ____ separates the logical test from the value if true/false.

• colon

• semicolon

• comma

• dash

Suppose cell B2 in a spreadsheet contains the formula:

=if(A1>100,"bazinga","zippo") If cell A1 contains the value 425, what value appears in cell B2?

• zippo

• 425

• bazinga

• B2 is blank

If you're using the VLOOKUP function for an approximate match, not an exact match, how must data in the left column of the lookup table be arranged?

• in reverse-alphabetical order

• in numerically descending order

• in numerically ascending order

• in alphabetical order

What does data validation allow you to do?

• all of these answers

• restrict or limit data entry to your specifications (i.e. only positive numbers, maximum of 10 characters, etc.)

• check the accuracy of your unique Excel worksheet functions

• lock cells to prevent others from editing them

What does the Goal Seek tool do?

• allows you to see how changing one data item in a formula affects the others

• automatically sorts or totals data

• highlights the major functions and outcomes of your data set

• allows you to add values in a selection of cells