Intermediate Excel
Which symbol can you use as an AutoFilter wildcard to represent one (and only one) character?
?
How do the AutoFilter arrow buttons change when you're filtering your table?
A mini filter symbol appears next to the down arrow.
Why is the result of the PMT function negative?
A payment is a cash outflow.
What type of cell addresses should you use to prevent column letters or row numbers from changing when you're copying a formula?
Absolute.
Which PivotTable Tools tab contains the toggle feature that shows or hides the field headers?
Analyze
Which PivotTable-specific tab contains the Refresh command?
Analyze
What does your success with the Solver really depend on your ability to do?
Be flexible and plan ahead.
Which chart type allows you to associate three numbers with each data point?
Bubble
What function is a close sibling to SUMIF, but instead it counts the number of records that meet the criteria?
COUNTIF
Which Statistical function will count the number of records based on multiple criteria?
COUNTIFS
Which shortcut key is acceptable to assign to a macro?
CTRL+SHIFT+C
What does Excel call the element normally found at the top of a chart that is used as an informative header?
Chart Title
On the PivotTable Analyze tab, what is the icon that looks like a little red minus sign?
Collapse Field.
Which text function will combine multiple text fields into a single cell?
Concat
By what rules does the Solver abide when finding a solution?
Constraints.
What chart option allows you to insert labels such as a number, percentage, or category name to each column, line, or pie slice of a chart?
Data Lables
If you want to move your chart location, which tab on the Chart Tools Ribbon is the Move Chart option found on?
Design
Which PivotTable-specific tab on the Ribbon contains the option to apply a colorful style to your report?
Design
When selecting any Sparkline, which contextual Ribbon appears?
Design.
Which type of chart works like a Pie chart but can graph multiple columns of numbers instead of one?
Doughnut
What can you use the "Drop Report Filter Fields Here" rectangle in the PivotTable area for?
Filtering.
Which PivotTable area creates an overall filter for the entire report?
Filters
Which functions can you use to calculate depreciation, rates of return, future values, and loan payment amounts?
Financial
Goal Seek is located in which section of the Excel Ribbon?
Forcast
What additional tab on the Ribbon appears when you create or select a PivotChart as compared to a PivotTable?
Format
What is the first step in recording a macro?
Give it a name.
Which Excel feature would you use if you know the result that you want from a formula but aren't sure what input value the formula needs to get that result?
Goal Seek.
On the Analyze tab, what is the icon that looks like an arrow pointing to the right?
Group Selection.
What combination of nested functions behaves like the VLOOKUP function?
INDEX and MATCH
What function uses an array, row_num, and column_num as function arguments?
INDEX.
You'll find Sparklines on which Ribbon tab?
Insert.
Which chart type fills gaps in knowledge about the collected data?
Line
Which functions give your worksheets decision-making powers?
Logical
In which function category would you find the MATCH function?
Lookup and Reference
What do you call a short program that you can write to automate a task you find yourself performing repeatedly?
Macro
What are the data points along the Sparklines called?
Markers.
In which category will you find the SUMIF function?
Math & Trig
In the Solver parameters, what are the options for setting the target cell?
Max, Min, and Value of.
For constraints, what does the use of an integer represent?
Nondecimal number.
In order to create a meaningful Bar, Line, Pie, or Column chart in Excel, what must your worksheet have present and available for selection before you create the chart?
Numbers and Lables
Where must you store your macro so that it's available in any open workbook?
Personal macro workbook.
Where can you store and quickly process macros?
Quick Access Toolbar.
What is the difference between the SUMIF and SUMIFS functions?
SUMIFS uses multiple criteria
When you give Excel's AutoFilter two conditions, what should you do to ensure both conditions are met?
Select the "And" option.
Which category contains functions that allow you to calculate averages, deviations, distributions, and probabilities?
Statistical.
Which of the following is a component of the SUMIF function?
Sum_range
What does Excel call a worksheet area with an initial row of labels?
Table
Within your workbook area, what should you have in place before you attempt to create a PivotTable report?
Table
Which of the following arguments would you use in the VLOOKUP function?
Table_array.
Which function combination will extract characters from a designated cell?
Text Left
What must you first select before you can format a Sparkline?
The Sparkline.
Why is Solver vastly more powerful than the Goal Seek tool?
The ability to add constraints and manipulate multiple values.
If you change one or more of the numbers in a worksheet from which a chart was derived, how will the chart respond?
The chart will change automatically to reflect the new numbers
Which PivotTable filter option displays the best (or worst) performers?
Top 10
Which chart is ideal for a quick view of a profit and loss financial statement?
Waterfall
Goal Seek is one of three items that comprise which Excel tool set?
What-If Analysis.
Which of the following is a type of Sparkline?
Win/Loss.
Which chart type do scientists, engineers, and statisticians use to create scatter plots?
X Y Scatter
Which symbol can you use as an AutoFilter wildcard to represent any number of characters?
*
How many cells can Goal Seek change in a single application?
1
