WorksheetsExcel Expert Lesson 3
Total questions: 30
Worksheet time: 15mins
Which of the following is not a way to name a range of cells?
Select the range and type the name in the names box to the left of the formula bar.
Under the Defined Names group click Define Name.
Select the range and right click the selected area. Click Define Name from the shortcut menu.
Under the Defined Names group click the Names Manager and then click New.
What is a big difference between tables and data ranges?
A table can be given a title.
A data range cannot be formatted.
A table does not have rows.
You cannot refer to a table in a formula.
What is it called when you put one formula inside of another?
Nesting
Layering
Grouping
Imbedding
What is a Table_array?
A table formatting.
A table that has an array of formats.
A table of text, numbers or values that you use for a formula.
The way a table is arranged to make it easy to use in a formula.
Which group are the AND, OR and NOT functions in?
Logical
Statistical
Text
Financial
What does the SUMIFS function do?
Adds cells in a range if they are under 500.
Adds cells in a range that meet one criteria.
Adds cells in a range.
Adds cells in a range that meet multiple criteria.
Which is a cell that contains a formula that refers to other cells?
Precedent
Dependent
Iterative
Trace
Which of the following is a function that can help you locate a specific item or the position of an item in a specified range?
FIND
LOOKFOR
SEARCH
INDEX
Which group on the Formulas tab has the option to trace precedents and dependents?
Formula Auditing
Calculation
Function Library
Defined Names
What Excel feature checks for common errors in your formulas?
Calculation Options
Error Checking
Evaluate Formula
Common Errors
What does the Watch Window do?
Monitors all the formulas in a workbook.
Monitors various workbooks.
Monitors various cells.
Monitors various worksheets in a workbook.
How many ways does Excel offer to do a data consolidation?
1
2
3
4
Which of the following is NOT true about the Query Editor?
The actions you take do not affect the original data source.
You cannot combine data from multiple sources.
You can import the data into an Excel table.
You can import the data into a PowerPivot model.
Which of the following about financial functions is NOT true?
Time periods can only be represented in years.
The interest rate and and the number of periods should be in the same time.
An inflow is positive and an outflow is negative.
A loan payment is represented as a negative number.
Which tab is the What-If Analysis tool under?
Insert
Page Layout
Formulas
Data
Which are two of the rules/guidelines for naming ranges?
Range names can be up to 255 characters in length and Range names may not consist solely of the letters “C”, “c”, “R”, or “r”.
Range names may not consist solely of the letters “S”, “s”, “T”, or “t” and you need to put spaces between words
You can only use up to 25 characters and you cannot use "-" or "."
The only symboles you can use are "(" and "]" and you need to put spaces in between each word.
What is the Field Name?
The name of the footer row of the table
An arbitrary name you give to a table
The field name from the header row of the table. The name refers to the set of all cells that comprise the named column in the table.
What refers to the cells that make up the named row in a a table
What is the syntax for VLOOKUP ?
=VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)
=VLOOKUP(VALUE,Table,Number)
=VLOOKUP(Column, Range, Sheetname)
=VLOOKUP{[(Lookup_value,Table_array,Col_index_num,Range_lookup)]}
What is the difference between HLOOKUP and VLOOKUP?
HLOOKUP looks for the proper height of the cell while VLOOKUP looks for the value withtin the cell
HLOOKUP looks for the letter h in a worksheet while HLOOKUPVLOOKUP looks for the letter v
HLOOKUP It looks for the vertical value in the bottom row of a table or an array while VLOOKUP looks for the horizontal value in the leftmost row of a table or an array.
HLOOKUP It looks for the horizontal value in the top row of a table or an array while VLOOKUP looks for the vertical value in the top row of a table or an array
What does the NOT function do ?
The NOT function writes the value entered backwards.
The NOT function reverses the value of its argument
The NOT function makes the argument red
The NOT function one of the arguments has to be true for the function to return a TRUE value.
What is one thing that the SUMIFS, COUNTIFS, and AVERAGEIFS Functions have in common with their formulas?
They all have the word IFS in them
They are all functions
In order for the arguments to be calculated they must meet certain criteria.
They can be calculated with a range
What does goal Seek do?
It mover the cells in a worksheet to a new workbook
It lets you find out how to acomplish what you want in the cell
A tool that see what you want to do and makes it happen in the workbook
it is a tool that lets you move one cell to a specific value based on what its imputs to another cell are.
What does the function PMT do?
The payment of monthly taxes
The payment for a loan based on constant payments and interest rate.
The payment of interest plus the number of days its due
Future value
Which of the following Excel functions returns both the current date and time in a cell?
TODAY
DAY
NOW
MONTH
What is a condition you specify to limit which records are returned when filtering data?
Filter
Criteria
Sort
Find
What command enables you to debug a formula by stepping through each part of a formula individually?
Show Checking
Look through formula
Evaluate Formula
Remove Arrows
What tool allows you to roll up the numbers into one summary worksheet?
Conditional Formatting
Summary
Review Sheet
Consolidate Data
What does the term basis mean in excel?
How many times a day
Number of days in a year for securities calculations.
Number of months in a year
How often in a month
What does the financial function IRR do?
The inconsistant rate of return of cash in a year
The internal rate of return for a series of cash flows (specified in values), occurring at regular intervals
The external rate when cash is spent over a year
The number of times that the cash flow has fluctuated
What is the syntax for AND?
=AND{[Logical1,Logical2,...]}
=ANDs(Logical1,Logical2,...)
=AND(Logical1,Logical2,...)
=-=+AND(Logical1+Logical2,...)=
