MIS 111L FINAL EXAM
The HLOOKUP function works like VLOOKUP function except that the lookup table is arranged ___________ instead of __________.
"horizontally, vertically"
The Excel function: ISERR() returns TRUE if the cell contains any error value except which of the following?
#N/A
In the attached image, examine the formula in cell "E9." What result will this formula produce? =B7:C16
#VALUE!
Please see the attached workbook. The formula: =OFFSET(A1,MATCH("Mary",A2:A5,0),2) returns what value?
22
Please refer to the attached workbook. What is the TOTAL amount of Sales in the west regions of Arizona (AZ) and California (CA)?
2200 and 4100 respectively
For Windows systems, Excel uses the standard 8-bit ANSI character set. This character set consists of ___ characters.
255
In Excel, the DATE function takes _____ arguments.
3
The formula =COUNTIF(range, "???") returns the number of cells in the range containing exactly ______ characters of text
3
What is the maximum number of characters a worksheet name can contain?
31
A single cell in Excel can hold up to approximately ________________ characters.
32000
Please choose the formula that will calculate the future value of a $6,500 investment at 4.55 percent compounded continuously for 4 years.
=6500*EXP(4.55%*4)
Which of the following shows the correct syntax for referencing cell "A2" in another worksheet?
=A1*Sheet2!A2
To calculate the percentage of the total widget sales made by the top 5 employees, which of the following formulae would you enter in cell "C22" (shaded purple)
=C20/F8
Which formula can be used to find: How many songs were sung by singers whose names begin with S?
=COUNTIF(B2:B23,"S*")
If cell "A1" contains JOHN F. SMITH and you want this value to be changed into a proper case like John F. Smith, what Excel function would you use?
=PROPER(A1)
Which of the following formulas rounds a time in cell B2 to the closest minute? (You can assume relevant cells have Excel's Time formatting)
=ROUND(B2*1440,0)/1440
Please see the attached workbook. In the Student Rank table, the points column represents the points awarded to students. If the total points that can awarded in the class cannot exceed 350, what data validation setting would you use for cell I19, to make sure that the entry does not exceed the required total of 350?
=SUM($I$13:$I19)<=350
What changes will you make in the formula =VLOOKUP(D2,PAGES!A2:B10,2,FALSE) so that it checks for an approximate match?
=VLOOKUP(D2,PAGES!A2:B10,2,TRUE)
What is one of the DRAWBACKS of using a pivot table?
A Pivot Table does not update automatically when information changes in source data
A pivot chart is which of the following?
A graphical representation of a data summarized in a pivot table
What will be returned if an Excel LOOKUP() based formula has an entry for the lookup_value and the look_up vector, but no entry for a result_vector parameter?
A value from the lookup_vector
The SUM() function does which of the following in Excel?
Allows you to total up numbers in a specified range of cells
Examine the attached workbook. Fill in the blank: You can add a title to the vertical axis of the chart by using Chart Layouts (Chart Tools "Design" tab) --> Add Chart Element --> __________
Axis Titles
Please see the attached workbook. When you examine the worksheet with the "Manage Rules" (conditional formatting) command, which rule is applied on cells that appear with a yellow background fill?
Below Average
How are some icons hidden in Icon Set conditional formatting?
By choosing "No Cell Icon" in the Edit the Rule Description box.
What command (within Conditional Formatting) is used to delete all the rules relevant to a particular group of cells
Clear Rules
Which of the following is NOT one of the buttons in the Spelling dialog box in Excel?
Conditional Formatting
What option in the Table Tools / Design tab removes a table without losing the data in it?
Convert to Range
A group of cells is referred to as which of the following?
Correct range
Which of the following is the keyboard shortcut to enter the current date into a cell in Excel ?
Ctrl + ; (semicolon)
Which of the following keyboard shortcuts will add the current time to a cell in an Excel worksheet?
Ctrl + Shift + :
Which of the following keyboard shortcuts can be used in Excel 2013 to create a table?
Ctrl+L
If you need to create several different printed reports from the same Excel workbook, setting up the specific settings for each report can be a tedious job. You can simplify the process by using the ______________ feature that enables you to give names to various views of your worksheet, and you can quickly switch among these named views.
Custom Views
Which is one of the ways to refresh a query used to import data from external data source using the query wizard?
Data (tab) -> Connections (group) -> Refresh All
How do you pick the Data Validation option that provides the user with a warning (instead of completely stopping data entry) when a validation check is violated?
Data(tab) -> Data Tools (group)-> Data Validation -> Error Alert -> Style: Warning
Which of the following is a VALID sequence of options for selecting a different chart layout?
Design (from Chart Tools) --> Chart Layouts
When you select a cell in the Total Row, a drop-down arrow appears in the cell. Click the arrow and you can select the "Count" formula. What does this formula do?
Displays the number of entries in the column.
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: &[Tab] ?
Displays the sheet's name
In Excel 2013, when you open multiple workbooks, how does the workbook show?
Each workbook always appears in its own window.
What are the #REF! and #NUM! that may appear in a cell?
Excel error codes
What is the result of the formula =2^3 * (,10*10
Excel tells you that the formula contains an error
What would happen if you used a formula to perform arithmetic operations on dates, but one of the dates was typed in a format that Excel does not recognize as a date?
Excel will display an error value
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 ______________.
External Data Properties Dialog Box
The _____________ command can extract specific pieces of information from cells, as long as the values are stored consistenty.
Flash Fill
Examine the attached workbook. Which ribbon tab provides the option to resize charts? (e.g., change the height & width)
Format
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 has a value lesser than 30000 for 'Salary' column.
From the Column to Filter list, select Salary. In the right panel, select 'is less than' from the first drop-down list and then select '30000.0' from the second drop-down list
To print a worksheet in a Landscape orientation, you:
Go to the Page Layout tab, in the Page Setup group, click Orientation, and then click Landscape
Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet?
HYPERLINK()
After pressing "Alt + H" while in an open Excel worksheet, a number of Key Tips are displayed. Which ribbon tab do these Key Tips relate to?
Home
Which of the following is a VALID method of hiding rows or columns?
Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list)
Please examine any Excel 2013 (Windows) workbook to better answer this question: You can create your own style through which of the following steps?
Home (tab) -> Styles (command group) -> Cell Styles
Which function returns the contents of a cell at the intersection of a specified row and column from a range?
INDEX()
Which of the following statements is true? They all relate to the formula: =IF( OR(Category="Pasta",Category="Soups"), Inventory_Cost,0 )
In this formula, the second statement in the OR (i.e., checking for the category ="Soups") is only checked/evaluated if the first part (i.e., checking if category ="Pasta") evaluates to FALSE
If you need to display lots of text in a worksheet, you can consider using a text box. How will you insert a textbox?
Insert (tab) -> Text (command group) -> Text Box
How does the Excel search/replace function work for dates?
It searches for a date as displayed in a cell, and then does a replace
Which of the following options provides correct syntax for the LOOKUP function?
LOOKUP(lookup_value,lookup_vector,[result_vector])
Fill in the blank: Every chart type has a set of ____ that you can choose from (with settings for chart elements such as a title, data labels, axes, and so on).
Layouts
What statement about the two Hyperlink arguments Hyperlink(link_location,[friendly_name]) is TRUE?
Link_location is required, Friendly_name is optional
At which locations can you find the following formatting features? Increase Decimal, Format Painter and Font Color
Mini Toolbar
How do you remove a filter in the PivotTable Field List?
Move the cursor over the filter icon next to the field name you want to remove the filter from. Click the arrow that appears, and then choose the Clear Filter From option.
Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet?
NOW()
Which function returns the number of payment periods for a loan, given the loan's amount, interest rate, and periodic payment amount?
NPER()
Can you print data in an Excel Form attached to a table?
No, you cannot
Which of the following is FALSE about Pivot Table Slicers?
None of the answers provided is false
Which of the following options would you choose if you want to remove all manual page breaks in the worksheet?
Page Layout (tab) -> Page Setup (group) -> Breaks -> Reset All Page Breaks
In the following formula, Excel sums up the data in which range? =SUMIF(Products!I2:I50,"=Canned Fruit & Vegetables",Products!F2:F50)
Products!F2:F50
What is the syntax for the RATE() function?
RATE(nper,pmt,pv,[fv],[type],[guess])
Which function can be used to make crude/simple vertical separators between cells?
REPT()
With regards to Depreciation Calculation functions in Excel, which of the following are true about the function VDB()?
Refers to the Variable-declining balance function. Computes the depreciation of an asset for any period (including partial periods) using the double-declining balance method or some other method you specify.
Which of the following is NOT a summary function for Pivot tables?
Regression
Suppose your worksheet has a comment in cell A1 and a comment in cell B5. Your active cell is B1. Which command can you use to navigate to the comment in cell B5?
Review (tab) -> Comments (command group) -> Next
Which of the following options can be used to quickly remove data validation for a cell?
Select the cell and open the Data Validation dialog box (Data (tab) -> Data Tools (group)). On the Settings tab, click Clear All.
Please see the attached workbook image. What sequence of steps will allow you to format the data into a table?
Select the data, then click "Table" under the "Insert" tab of the Excel Ribbon
What does selecting the option "Current region" within the: Go To Special Dialog Box (Home (tab) -> Editing (command group) -> Find & Select) do?
Selects a rectangular range of cells around the active cell
Which of the following command sequences will allow you to save a copy of your workbook with a different name?
Shift + S
To create a single file web page, what file format does a user have to select from the Save as Type drop-down list?
Single File Web Page
Which of the following is NOT a type of ANOVA (Analysis of Variance) performed using tools in the Analysis ToolPak?
Single-factor without replication
Microsoft Office Excel can automatically generate a data form (which allows for convenient data entry, among other things) for your range or _______________.
Table
To add rows or columns within the table, right-click and choose Insert from the shortcut menu. Which of the following commands is NOT displayed on this menu?
Table Column Above
When importing a text file into Excel, the _____________ helpsspecifies how the data will be imported.
Text Import Wizard
Examine the attached Excel file. In row 4, we have two different ways of displaying the August 25 date. Which of the following is TRUE about these dates?
The data stored in the two cells A4 and B4 are identical
The "Go to Special" dialog box in Excel allows a user to select all of the following EXCEPT:
The second to last cell in a selected column that contains data or formatting
Which is NOT a feasible option to prevent cell contents from being printed?
Use the custom number format that consists of two colons.
Which of the following is an advantage of using Excel tables?
Using table and column names in formulas provides greater flexibility as extending a table does not require re-writing of formulas
In Excel, a/an ________ is created or run to retrieve text or data from a Web Page.
Web Query
You can use a data Form to add, find, and edit rows in a range or table. Is this correct?
Yes, all these are valid form operations
Which of the following does not hold true for Watch Windows?
You can see all the formulas in a worksheet and print them for later reference
Which of the following is a VALID range name in an Excel workbook?
_2ndyear
Please see the attached workbook. What does the small red triangle on the upper right corner of cell B1 denote?
a comment exists for that cell
When a range of cells is selected, which of the following occurs?
all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded
If there are two or more name / value pairs passed in a URL query string, separate them with the following character: _____________ .
ampersand (&)
Consider the function =COUNTIF(A1:A200, ">200"). The information between the parentheses in a function are known as the ____________ .
arguments
A data form provides a convenient means to enter, display and search for data within a table. It also permits you to do the following: ____________
delete a record/row
In the Color Scales method of Conditional Formatting, color ________ are used to compare values.
gradients
To make Data Bars easier to view, we can ________ the numerical values in the cell.
hide
A chart in an Excel worksheet provides a visual representation of which of the following?
numeric values
You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks?
passwords are case sensitive
The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______.
range (and) criteria
The HLOOKUP function searches for data in ______.
rows
The formula '=NOW()-TODAY()' displays current time as a ___________ without a related date.
serial number
The Find and Replace dialog box cannot find background color formatting in tables that was applied using __________ or formatting that is applied based on ____________
table style, conditional formatting
The real power of data validation becomes apparent when you use data validation formulas. The formula that you specify must be a logical formula that returns either TRUE or FALSE. If the formula evaluates to TRUE, what does it mean?
the data is considered valid and remains in the cell
A _____ data table in Excel shows the result of a single calculation for different values of two input cells
two-way
What best describes the connection between a Pivot Table and a Pivot Chart?
two-way link (updates made in the Pivot Chart are reflected in the PivotTable)
Which of the following methods will keep a heading at the top of a table visible while you scroll through rows lower down in the worksheet?
use Freeze Panes
Which of the following is NOT a method of creating frequency distributions?
use the Review (Proofing) tool of Spelling Error Frequency per Worksheet
Fill in the blanks: Conditional formatting effects help us _______ values in cells making it easier to locate data, find exceptions and spot ______ quickly.
visualize, trends
