For Kate
The "Go to Special" dialog box in Excel allows a user to select all of the following EXCEPT: 1) Cells that contain comments 2) Blank cells 3) All cells that differ from the active cell in a selected column 4) The second to last cell in a selected column that contains data or formatting
The second to last cell in a selected column that contains data or formatting
While working with Shapes in Excel, which command group contains commands to adjust the "stack order" of shapes, align shapes, group multiple shapes and rotate shapes? 1) Arrange 2) Size 3) Align 4) Alter
Arrange
Which of the following error codes indicates that a problem with a value exists? For example, a negative number was specified by the user where a positive number was expected. 1) #REF! 2) #NUM! 3) #N/A 4) #NAME?
#NUM!
Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet? 1) HYPERLINK() 2) SUM() 3) INDEX() 4) MATCH()
HYPERLINK()
If there are negative values in your data, you can emphasize this by showing a(n) __________ axis in your sparkline. 1) Blank 2) Vertical 3) Diagonal 4) Horizontal
Horizontal
What is the file format (file extension) used when you save an Excel workbook as a single file webpage? 1) .html 2) .mht or .mhtml 3) .xlsx 4) .txt
.mht or .mhtml
dataLOOKUP_Ex4.xlsx Please see the attached workbook. What value does the formula "=VLOOKUP("John",A2:D13,3,FALSE)" return? 1) 2000 2) 1500 3) 1590 4) 1600
2000
The Excel CODE function returns the character code for its argument. What does the following formula return? =CODE("B") 1) 65 2) 66 3) 68 4) 1
66
Country.xlsx Which of the following formulas, when entered in cell B3, will give you the CURRENCY of the country CHAD? 1) =INDEX(I2:I239, MATCH("CHAD", D2:D239, 0)) 2) =(HLOOKUP("CHAD",D1:I239,3)) 3) =MATCH(I2:I239, INDEX("CHAD", D2:D239, 0)) 4)=(VLOOKUP("CHAD",D1:I239,3))
=INDEX(I2:I239, MATCH("CHAD", D2:D239, 0))
datavalidation.xlsx Please see the attached workbook. In the Student Rank table, enter the rank in cell G19 as abcd. What is the data validation formula used in this case? You may look at the Data Validation settings to answer this question. 1) =ISNUMBER(G19) 2) =ISTEXT(G19) 3) =ISNUMBER(H19) 4) =ISTEXT(H19)
=ISNUMBER(G19)
Which of the following formulas calculates the number of payment periods for a $5,000 loan that has a monthly payment amount of $117.43. The loan has a 6 percent annual interest rate. 1) =NPER(6%/12,117.43,-5000) 2) =NPER(8%/12,117.43,-5000) 3) =NPER(6%,117.43,-5000) 4) =NPER(6%/12,117.43,50000)
=NPER(6%/12,117.43,-5000)
What is one of the DRAWBACKS of using a pivot table? 1) A Pivot Table does not update automatically when information changes in source data 2) A Pivot Table updates automatically when information is changed in source data 3) Pivot Table totals do not update automatically when you choose a different field for a Row Label 4) Pivot Table totals update automatically when you choose a different field for a Row Label
A Pivot Table does not update automatically when information changes in source data
o extract a student's first name (e.g., "Herbert") from their full name (e.g., "Herbert Smith"), which of the following functions will you typically use? 1) A combination of DATE(), FIND() and FIRST() 2) A combination of REPT() and CONCATENATE() 3) A combination of FIND() and MID() 4) The function FIRST()
A combination of FIND() and MID()
When using the formula "=WORKDAY(DATE(2014,1,4),10)", what does the output show? 1) A month that is ten months before January 4, 2014 2) A date that is ten working days from (after) January 4, 2014 3) A month that is ten months from (after) January 4, 2014 4) A date that is ten working days before January 4, 2014
A date that is ten working days from (after) January 4, 2014
A pivot chart is which of the following? 1) A bar chart that can pivot to a pie chart form with a single click 2) A graphical representation of a data summarized in a pivot table 3) A chart that is joined to a pivot table in a 4-way link 4) A chart that cannot be used with a slicer
A graphical representation of a data summarized in a pivot table
What does it mean when Excel displays the following error? #DIV/0! 1) A number is divided by zero! 2) A number is divided by infinity! 3) unrecognized text 4) none of the options provided is correct
A number is divided by zero!
If Row 15 needs to be on the first row of a new page and only a horizontal page-break is needed, what cell should be selected to create a page break? 1) B15 2) A14 3) A15 4) B16
A15
Which of the following features for Sparklines is missing in Excel 2016 but can be simulated artificially by transforming the data? 1) Adjusting Sparkline axis scaling 2) Specifying a Date axis 3) Adding Reference line 4) Auto updating Sparklines when the source data changes
Adding Reference line
Which of the following is TRUE about Pivot tables? 1) You cannot add new rows or columns to the Pivot table 2) You cannot change any of the calculated values 3) You cannot enter formulas within the pivot table 4) All of the answers provided are TRUE
All of the answers provided are TRUE
How can you avoid the "#NAME?" error value? 1) If the formula uses a label in a formula, make sure labels are allowed 2) Enclose any text in double quotation marks 3) Make sure all range references in the formula use a colon 4) All of the options provided are correct
All of the options provided are correct
How are some icons hidden in Icon Set conditional formatting? 1) By choosing "No Cell Icon" in the Edit the Rule Description box. 2) By clicking on Conditional Formatting, followed by "icon hide". 3) By clicking on New Rule in Conditional Formatting. 4) By clicking on the "Clear Some Rules" button in Conditional Formatting.
By choosing "No Cell Icon" in the Edit the Rule Description box.
datavalidation.xlsx Please see the attached workbook. In the Student Rank table, in cell F19, type Rachel instead of Dennis. Why do you receive an error? 1) Cell F19 can only take a male name 2) Cell F19 can only take text that begins with the letter 'd'. 3) Cell F19 should only take numbers. 4) Cell F19 has no restrictions.
Cell F19 can only take text that begins with the letter 'd'.
What command would you use to import a CSV file? 1) Choose Data --> Get External Data --> From Text 2) Choose Data --> Get External Data --> From CSV 3) Choose Data --> Import Data --> External 4) None of the provided options is correct
Choose Data --> Get External Data --> From Text
_____________ interest refers to an interest calculation where interest is credited to the investment balance (and the investment then further earns interest on the interest credited). 1) Simple 2) Compound 3) Future value 4) None of the above
Compound
What is the purpose of the function TIMEVALUE()? 1) Returns the serial number of a specified day, hour and minute 2) Provides a $ value for time based on an Excel formula 3) Returns the serial number of the current date and time 4) Converts a time in the form of text string (e.g., "18:45") to a serial number
Converts a time in the form of text string (e.g., "18:45") to a serial number
When entering / editing an Array formula, press _________ to enter the formula (or Excel does not recognize the formula as an Array formula). 1) Ctrl + Shift + Enter 2) Alt + Ctrl + Delete 3) Alt + Shift + Enter 4) None of the options provided is correct
Ctrl + Shift + Enter
datavalidation.xlsx Please see the attached workbook. In the EmpID table, enter the salary in cell H8 as 100. Given the error message, why do you think Excel does not let you input that value? 1) 100 is a whole number 2) Data validation settings require the salary value to be greater than the hours worked value, 120. 3) Data validation settings require the salary value should be less than the hours worked value, 120. 4) The cell is designed to permit text input only
Data validation settings require the salary value to be greater than the hours worked value, 120.
When a header or footer section is activated, you can insert any of several element codes from the Header & Footer Elements group. Each button inserts a code into the selected section. What is the purpose of the following code: &[Path]&[File] ? 1) Displays the workbook s complete path 2) Displays the workbook s complete path and filename 3) Displays the workbook s filename 4) None of the above
Displays the workbook s complete path and filename
Which older version of Excel file can Excel 2016 NO LONGER open? 1) Excel can open all of these files. 2) Excel 4 3) Excel 97 4) Excel 2000
Excel can open all of these files.
When working with data returned from a Query Wizard, you can adjust a variety of properties of an external data range. This can be done using the ______________. 1) External Data Properties Dialog Box 2) Internal Data Properties Dialog Box 3) General Properties Dialog Box 4) You cannot adjust properties of the external data range
External Data Properties Dialog Box
If you want to print a copy of a worksheet, which of the following would you choose? 1) File (tab) -> Print and then choose the PRINT command. 2) Home (tab) -> Print and then press the PRINT button. 3) Page (tab) -> Print and then press the PRINTOUT button. 4) Press Alt+N, and then press the PRINTOUT command
File (tab) -> Print and then choose the PRINT command.
external_ex1.xlsx Please use the attached workbook as an external data source. The workbook has a table called 'Employee'. When you import data, after selecting all the columns from the table using the query wizard (Data(tab) -> Get External Data (group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that have a value that ends with 'm' for 'Name'. 1) From the Column to Filter list, select Name. In the right panel, select 'ends with' from the first drop-down list and then type 'm' in the second drop-down list box 2) From the Column to Filter list, select Name. In the right panel, select 'begins with' from the first drop-down list and then type 'm' in the second drop-down list box 3) From the Column to Filter list, select Name. In the right panel, select 'equals' from the first drop-down list and then type 'm' in the second drop-down list box 4) From the Column to Filter list, select Name. In the right panel, select 'equals' from the first drop-down list and then select 'Sam' from the second drop-down list
From the Column to Filter list, select Name. In the right panel, select 'ends with' from the first drop-down list and then type 'm' in the second drop-down list box
To add one or more Slicers to a worksheet, start by selecting any cell in a pivot table and then choose ______________. 1) Insert (tab) -> Filters (group) -> Slicer. The Insert Slicers dialog box appears. 2) Insert (tab) -> Slicer (group) -> Filters. The Insert Slicers dialog box appears. 3) Right Click on it. The Insert Slicers menu option can be selected. 4) All of the answers provided are correct
Insert (tab) -> Filters (group) -> Slicer. The Insert Slicers dialog box appears.
Please see the attached Excel workbook. Suppose you are on Sheet1 and you have selected cells A5:B5. What happens when you insert a background image through the following sequence of steps? Page Layout (tab) -> Page setup (command group) -> Background? 1) It adds the background image to the selected cells 2) It adds the selected background image to Sheet1, Sheet2, Sheet3 in the current workbook 3) It adds a background image to the currently active worksheet 4) It adds a chart with the background image embedded
It adds a background image to the currently active worksheet
The _________ function is used to return a specific number of letters or characters from the start of a text string. 1) FROMRIGHT() 2) LEFT() 3) MID() 4) SUM()
LEFT()
What statement about the two Hyperlink arguments Hyperlink(link_location,[friendly_name]) is TRUE? 1) Link_location is required, Friendly_name is required 2) Link_location is required, Friendly_name is optional 3) Link_location is optional, Friendly_name is required 4) Link_location is optional, Friendly_name is optional
Link_location is required, Friendly_name is optional
Vector based images are represented by? 1) Mathematical equations 2) Discrete Points 3) Incorrect Response 4) Both Discrete points and well as Mathematical equations 5) Dates and Times
Mathematical equations
A calculated item must reside in the ________ labels, ______ labels or ___________ area of a pivot table. 1) column, row, default 2) column, row, values 3) column, row, filter 4) None of the answers provided is correct
None of the answers provided is correct
When you activate a cell that contains a date, the formula bar usually shows the cell contents formatted using the following date format: 1) a standard IP address 2) TRIM(LEFT(LEN())) 3) toolbar heading 4) None of the answers provided is correct
None of the answers provided is correct
Which of the following entries does Excel automatically recognize as a date (using the U.S. settings)? 1) June 18 2014 2) Jun-18 2014 3) Jun-18/20 4) None of the answers provided is correct
None of the answers provided is correct
How do you format a valid date serial number as a date ? 1) Home (tab) ->Number (command group) ->Number Format -> $ 2) Home (tab) -> Number (command group) ->Number Format -> Text 3) Home (tab) -> Number (command group) -> Number Format -> Fraction 4) None of the options provided is correct
None of the options provided is correct
When you use the HLOOKUP() function, which of the following parameters represents the row number from which the matching value will be returned? 1) range lookup 2) lookup_value 3) row_care_param 4) None of the options provided is correct
None of the options provided is correct
______ cells are by default disregarded when data validation rules are created. These cells can be included in the validation rule by unchecking the: "Ignore blank" checkbox. 1) Unchecked 2) All 3) Checked 4) None of the options provided is valid
None of the options provided is valid
PivotTables_Ex1.xlsx Please refer to the attached workbook. What happens if the following value filter is added to the existing report? Value filter: Sum of Sales is less than 5000. 1) Only one record corresponding to the 'CA' state remains 2) Only one record corresponding to 'AZ' remains. 3) Both the records remain. 4) No records remain as they don't satisfy the condition specified.
Only one record corresponding to 'AZ' remains.
PivotTables_Ex1.xlsx Please refer to the attached workbook. What happens if the following value filter is added to the existing report? Value filter: Sum of Sales is less than 5000. 1) Only one record corresponding to the 'CA' state remains 2) Only one record corresponding to 'AZ' remains. 3) Both the records remain. 4)No records remain as they don't satisfy the condition specified.
Only one record corresponding to 'AZ' remains.
Which financial function returns the principal part of a loan payment for a given period, assuming constant payment amounts and a fixed interest rate? 1) CUMPRINC() 2) IPMT() 3) PMT() 4) PPMT()
PPMT()
Which of the following is TRUE about Web queries? 1) Performing Web queries creates a new HTML file 2) Performing Web queries copies information from an HTML file into the current workbook 3) Web queries are not useful in Excel because an external data can change 4) None of the options provided is true
Performing Web queries copies information from an HTML file into the current workbook
What is one of the DRAWBACKS of using a pivot table? 1) A Pivot Table updates automatically when information is changed in source data 2) Pivot Table totals do not update automatically when you choose a different field for a Row Label 3) Pivot Table totals update automatically when you choose a different field for a Row Label 4) A Pivot Table does not update automatically when information changes in source data
Pivot Table does not update automatically when information changes in source data
By default, Excel prints worksheets in ___________ orientation 1) Landscape 2) Portrait 3) Angular 4) Glossy
Portrait
What is the general formula to calculate compound interest (i.e., the future value of an investment that includes both Principal and interest)? 1) Principal * (1 + periodic rate) ^ number of periods 2) Principal * (1 + periodic rate) ^ 10 3) Principal * (1 + 2) ^ number of periods 4) Principal * (periodic rate) ^ number of periods
Principal * (1 + periodic rate) ^ number of periods
Examine the attached workbook. The data represents the amount of money a customer has spent, in a quarter, to buy a product. Create a PivotTable based on the (entire) table of data in the Sales worksheet. For Row Labels, choose the Product column. Include Columns C, D, E and F in the Values-area. Which quarter saw the highest revenue (sales in dollars) for the product 'TOFU'? Pivot_table_test.xlsx 1) Qtr 1 2) Qtr 2 3) Qtr 3 4) Qtr 4
Qtr 2
Examine the attached workbook. The data represents the amount of money a customer has spent, in a quarter, to buy a product. Create a PivotTable based on the (entire) table of data in the Sales worksheet. For Row Labels, choose the Product column. Include Columns C, D, E and F in the Values-area. Which quarter saw the highest revenue (sales in dollars) for the product 'TOFU'? Pivot_table_test.xlsx 1) Qtr 1 2) Qtr 2 3) Qtr 3 4) Qtr 4
Qtr 2
The first field you add to a PivotTable report that does not contain numbers will automatically be added in the _______ part of the report. 1) Column Labels 2) Report filter 3) Row Labels 4) Slicer Group
Row Labels
Which of the following is NOT one of the worksheet views offered by Excel? 1) Normal 2) Page Layout 3) Page Break Preview 4) Simplified Layout
Simplified Layout
Which of the following is NOT one of the types of Sparkline graphics? 1) Line 2) Column 3) Win/Loss 4) Slicer
Slicer
After you create a Sparkline, changing color is easy. Use the controls in the _______________. 1) Sparkline Tools -> Design (tab) -> Type (group) 2) Sparkline Tools -> Design (tab) -> Style (group) 3) Sparkline Tools -> Design (tab) -> Color (group) 4) Sparkline Tools -> Design (tab) -> Font (group)
Sparkline Tools -> Design (tab) -> Style (group)
After you create a Sparkline or group of them, you can change the type by choosing the Sparkline and clicking one of the three icons in the ________________. 1) Sparkline Tools -> Design (tab) -> Type (group) 2) Sparkline Tools -> Design (tab) -> Names 3) Sparkline Tools -> Design (tab) -> Other Items 4) Design (tab) -> Type (group) -> Sparkline Tools
Sparkline Tools -> Design (tab) -> Type (group)
The __________ function will be used as part of a formula to calculate how many days old you are today. 1) YESTERDAY() 2) TODAY() 3) BIRTHDAY() 4) HOWOLDAMI()
TODAY()
What is the output of the following command sequence: click the Analyze tab under PivotTable tools -> Actions (group) -> Clear -> Clear Filters? 1) This removes all the filters at once 2) This removes only filters on the selected column (or row) 3) This does not remove any filters 4) This is not a valid command sequence for any pivot table
This removes all the filters at once
Which function will you use to calculate depreciation for multiple periods in Excel? 1) VDB() 2) DEPREC() 3) CONCATENATE() 4) MULTIDEPREC()
VDB()
Which of the following BEST applies to the INDEX function? 1) You can use it with the MATCH() function to do reverse lookups when the data is not sorted (e.g., to find the ProductID for a product named "Chocolate Cake", assuming product names are not sorted) 2) It usually returns error codes: #ERR1 - #ERR5 3) It returns the relative position of an item in an array (e.g., "4") when a match is found 4) It cannot be used with the MATCH function
You can use it with the MATCH() function to do reverse lookups when the data is not sorted (e.g., to find the ProductID for a product named "Chocolate Cake", assuming product names are not sorted)
The #VALUE! error can occur in the following conditions EXCEPT for: 1) A function's argument is a range when it should be a single value 2) When a custom worksheet function is not calculated 3) A custom worksheet function tries to perform an operation which is not valid 4) You cut a cell and paste it to a cell that is referenced by other formulas
You cut a cell and paste it to a cell that is referenced by other formulas
The formula: =CHAR(CODE("A")+32) returns which of the following? 1) 97 2) a 3) C 4) B
a
What form of LOOKUP is used when you have a small list of values and the values remain constant over time? 1) dynamic form 2) vector form 3) static form 4) array form
array form
When you create one or more Sparklines in Excel, by default they all use _________ axis scaling. 1) automatic 2) manual 3) batch 4) linear
automatic
While creating a parameterized web query the question mark (?) is used to ______. 1) begin the URL query string 2) delimit the name from the value 3) delimit each name/value pair 4) delimit the parameter from the prompt
begin the URL query string
A calculated item must reside in the ________ labels, ______ labels or ___________ area of a pivot table. 1) column, row, values 2) column, row, default 3) column, row, filter 4) None of the answers provided is correct
column, row, filter
When you enter "FALSE" for the range_lookup parameter in HLOOKUP, you are telling Excel that you want to find a(n) ________ match. 1) exact 2) different 3) dissimilar 4) orderly
exact
When the value of a cell is changed (after conditional formatting is applied), the cell formatting ______ based on the conditions set. 1) remains unchanged 2) is updated 3) needs to be manually reset 4) usually displays an #ERR message
is updated
Worksheet background images are for ______ display only, the images do not appear when the worksheet is ________. 1) offline, printed 2) onscreen, printed 3) offline, saved 4) onscreen, scanned
onscreen, printed
The range_lookup parameter in VLOOKUP and HLOOKUP function is ________. 1) mandatory 2) optional 3) compulsory 4) fixed
optional
A _________ is a graphical data representation of data displayed in a pivot table. 1) pivot chart 2) reverse pivot pie chart 3) slicer 4) sparkline
pivot chart
Step 3 of the Query Wizard lets you specify how you want the ______. 1) records to be filtered 2) record selection criteria named 3) records to be sorted 4) query to be saved
records to be sorted