MIS Lab Final
The Excel function: ISERR() returns TRUE if the cell contains any error value except which of the following?
#N/A
What does Excel return when your MATCH() function uses an exact match option and searches for an item that is not part of the lookup_array or lookup_vector?
#N/A
In the attached image, examine the formula in cell "E9." What result will this formula produce?
#VALUE!
Examine the attached file: Calculate the total yearly sales for the West Region and the South Region in cells "F5" and "F7" (shaded in orange). What is the total widget sales for all regions for the year?
$24,914
Which of the following is a VALID format for a URL query string in Excel?
---URL---?<Name>=<Val>
By default, what is the extension used when you save a workbook in Excel 2013?
.xlsx
What is the correct order of steps to be followed when you use a query wizard?
1. choosing the columns, 2. filtering data, 3. sort order, and 4. finish
Please refer to the figure. Within the "Number" tab, how many categories are available for displaying numbers?
12
In the attached spreadsheet, please use an appropriate formula to compute the total of the Cells C2 through C11 (surrounded by a red border in the worksheet). What is this total?
14787.45
Please see the attached workbook. How many names does the workbook have defined?
2
With a single IF() function, how many actions can you ask Excel to carry out?
2 (i.e., an action if the criteria is true, or an an action if the criteria is false)
How many sort options/commands do we have in Excel under the 'Data' tab?
3
Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet?
=NOW()
Which formula can be used to find: How many songs were sung by either Eminem or Spears?
=SUM(COUNTIF(B2:B23,{"Eminem","Spears"}))
Please see the attached workbook. What formula (in cell B3) returns the tax rate for the income in cell B2?
=VLOOKUP(B2, D2:F7, 3)
Which two wildcard characters are supported by the "Find and Replace" dialog box in an Excel worksheet?
?, *
To 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?
A combination of FIND() and MID()
When using the formula "=WORKDAY(DATE(2014,1,4),10)", what does the output show?
A date that is ten working days from (after) January 4, 2014
Which of the following statements is FALSE?
A pivot chart is always based on a pivot table, i.e., changes to the pivot chart must be made by changing the underlying table
You are reviewing a Spreadsheet that records daily sales for Burgers, Hotdogs and Chili. Monday of last week shows exceptionally high sales for Burgers. You want to email the spreadsheet back to your assistant so he can look into whether the data was correctly entered.
Add a comment to the sales figure cell, and explain to your assistant what you want him to do
Using the Normal view for a workbook, which of the following actions can be performed for Page Breaks?
All of the above
The SUM() function does which of the following in Excel?
Allows you to total up numbers in a specified range of cells
There is a type of investment in which you make a stream of fixed payments (e.g., as a series of investment deposits into an account). What is this type of investment called?
Annuity
If you want to format cells that have values between 50 and 60, which option under 'Highlight Cells Rules (Conditional Formatting)' category would you select?
Between
How are some icons hidden in Icon Set conditional formatting?
By choosing "No cell Icon" in the Edit the Rule Description box
Please see the attached workbook. What will be the grade of Adams if the following formula is used in the cell C2? =LOOKUP(B2,{45,50,60,70,80,90},{"F","E","D","C","B","A"})
C
The formula: =CHAR(CODE("C")) returns which of the following?
C
In Excel, in order to calculate the cumulative principal paid between any two payment periods, what function will you use?
CUMPRINC()
Please see the attached workbook. In the EmpID table, enter the empID in cell F8 as 12345. Given the error message, what will you change in the Data Validation dialog box > settings (tab) for 12345 to be a valid entry?
Change the settings to whole number > less than > 100000
Which of the following is a way of accessing the "Evaluate Formula" dialog box that helps debug a formula?
Choose Formula ->Formula Auditing ->Evaluate Formula
Examine the attached workbook. To move "Chart 1" in "Sheet1" to a new Excel worksheet named "MyChart", which options will you use?
Click on: (Chart Tools) Design tab --> Move chart (Location) --> New Sheet (option)
The Pivot Table Field List shows which of the following?
Columns from the source data and Groups defined in the Pivot Table
What option in the Table Tools / Design tab removes a table without losing the data in it?
Convert to Range
How do you clear/remove a Print Area that has already been defined on the worksheet?
Correct On the Page Layout tab, in the Page Setup group, click Print Area, and then click Clear Print Area.
What is the shortcut key to create hyperlink when you select an object or a cell?
Ctrl+K
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?
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: &[Time] ?
Displays the current time
In Excel 2013, when you open multiple workbooks, how does the workbook show?
Each workbook always appears in its own window
Where do you find the command to add a pivot table on your worksheet?
Excel Ribbon: Insert (tab) ->Tables
When you apply currency formatting to a Table column and then add a new row to the table, what happens to data entered in the new cell?
Excel applies currency formatting to the new value in that column cell.
What are the #REF! and #NUM! that may appear in a cell?
Excel error codes
Which of the following is NOT a state an Excel workbook can be in?
Expanded
Examine the screen-capture from Excel. Which cell is currently active in the figure?
F4
Examine the image in the question. You can store the results of a formula entered in the formula bar (e.g., =(25*10)/2 ) as an actual number (e.g., 125) in the active cell. What keyboard shortcut do you need to press while editing the cell to do this?
F9
Which of the following command sequences will allow you to save a copy of your workbook with a different name?
File, Save as
Light, Medium and Dark are the categories of styles found under the ______ command [within the Home (tab) -> Styles (command group)].
Format as Table
Which of the following rule types does not apply to color scale formatting?
Format cells differently based on the font styles in each cell
Which of the followings steps are to be followed in order to identify the cell that contains an error?
Formulas -> Formula Auditing -> Error Checking -> Trace Error
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 that starts with 'T' for 'City' column.
From the Column to Filter list, select City. In the right panel, select 'begins with' from the first drop-down list and then type 'T' in the second drop-down list box
Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet?
HYPERLINK()
When specifying what to print, a user is NOT able to choose the following option: ____ .
Hidden sheets
Please see the attached workbook, go to Sheet1. How do you find all the cells that have conditional formatting?
Home (tab) -> Editing (command group) -> Find & Select -> Conditional Formatting
Which of the following functions could be used if you want to display "NOT FOUND" when you lookup for an item that is not part of the lookup_vector?
IFERROR
In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram?
Input Range and Bin range
The basic formula for computing simple interest is:
Interest = Principal * Rate * Term
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?
It adds a background image to the currently active worksheet
What is the purpose of the Text to Columns command?
It parses strings into their component parts.
Examine the attached workbook. Which of the following occurs if you select an embedded chart of a sheet and then click on "File" --> "Print"?
It prints only the chart on that sheet
The _________ function is used to return a specific number of letters or characters from the start of a text string.
LEFT()
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
Which of the following is NOT a formatting option offered through the Mini Toolbar? (Hint: for more information on the mini-toolbar, you can right-click on an Excel cell)
Left Align
Which keyboard shortcuts can be used to move the active cell one colunn to the left?
Left arrow or Shift + Tab
Please examine the attached image. A part of the workbook is boxed in red. Fill in the blanks: The boxed area is called ____. What is displayed in this boxed area when a range of cells is selected? _______
Name box. The number of rows and columns in the selection is displayed here
Which of the following is NOT one of the four quick margin settings for adjusting margins?
No margins
Must a chart be embedded in the same worksheet as its data source?
No, a chart an be embedded or placed in a sheet separate from that of its data source.
Convert the data on Sheet1 into a table. Next answer the following question: If you proceed to remove duplicate values using the "Remove Duplicates" feature in "Table Tools", will Excel remove one of the two table rows?
No, these are not considered duplicates by Excel. To remove the duplicate data you must ensure that the date in the second row is formatted the same way as the date in the first row.
Please see the attached workbook. What value of Discount is returned when you use an VLOOKUP function with a quantity entered (i.e., value looked up) of 1001 and a range-lookup parameter of FALSE?
None of the other answers are correct
Please see the attached Excel workbook image. The cells highlighted in yellow contain a Total Row. How can such a Total Row be added to a table?
Once you have a table, you can click the "Table Tools / Design" tab. Then, under "Table Style Options" check the box for "Total Row."
Please refer to the attached Excel workbook. How can you add a dollar ($) symbol to values in a column as seen in the "Grand Total" column?
One way to do this is by going through the "Format Cells" dialog, and selecting "Accounting" (with a $ symbol and 2 decimal places) as your formatting option from within the Number tab
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 greater than 5000.
Only one record corresponding to the 'CA' state remains as its total sales amount is greater than 5000.
Please refer to the figure. What Excel element is shown in the red box?
Quick access toolbar (where the save button and back arrows are)
The _____________ function is used to return a specified number of letters or characters from the end of a text string.
RIGHT()
Please refer to the figure. What Excel element is shown in the red box?
Ribbon (where the clipboard, font, alignment, etc are)
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
In the Share tab of Backstage View, which of the options is available?
Send using e-mail
To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________.
Shift+F2
What keyboard shortcut can you press to move to the PREVIOUS field in an Excel data Form ?
Shift+Tab
______ is an interactive control that makes it easy to filter data in a pivot table.
Slicer
When importing a text file into Excel, the _____________ helpsspecifies how the data will be imported.
Text Import Wizard
While converting from military time, when would the formula TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2)) return an incorrect result?
The contents of cell A1 do not comprise four digits
A formula refers to a value in the Total Row of a table. What will happen to the formula result if the option for "Total Row" (from the Excel ribbon: Table Tools / Design tab) is unchecked?
The formula returns an error
Please look at the attached worksheet. What is the result of the following formula? =COUNTIF(A1:A10, MODE(A1:A10))
The number of cells containing the mode within the cell range A1 - A10
If a pivot chart is created and the underlying table is deleted, what occurs?
The pivot chart remains
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
Please see the attached workbook, Sheet1. What happens when you apply a theme on Sheet1 of the Excel sheet by going to Page -> Layout -> Themes
The theme is applied to all of the sheets in the workbook
The formula =COUNTIF(range, "*budget*") will count the number of cells in the specified range containing the word "budget" in certain positions within the text. Which of the following best describes the cells counted?
The word "budget" can appear anywhere within the cell.
Please see the attached workbook. What does '3' in the expression =VLOOKUP("John",A2:D13,3,FALSE) represent?
Third column
Examine the attached Excel data file (Histogram.xlsx) and image (Histogram.png). If you used the Excel data file to generate a histogram, and compared it with the histogram in the image, which of the following options best describes what would you see?
Three of the Histogram bins will have a different count
Excel formatting tools are used to achieve which of the following?
To make the end result attractive and more functional
Please see the attached workbook. The highest 5 salaries for employees can be viewed using which of the following options under Conditional Formatting?
Top 10 Items under Top/Bottom Rules
Which function will you use to calculate depreciation for multiple periods in Excel?
VDB()
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
Please see the attached workbook. If you copy cell "B6" and paste it as a formula in cell "C6," will it produce the correct total for Year 2?
Yes, the formula range will be adjusted automatically
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 BEST applies to the INDEX 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)
Which of the following characters is VALID for use in an Excel workbook file name?
_
Which of the following is the difference between a Pie and Doughnut chart in Excel?
a doughnut chart can display more than one series of data
If one attempts to format a serial number that falls outside the range of supported dates, the value displays as __________.
a series of hash(#) marks
Please see the attached workbook. The formula in cell C4 is "=B4 + (B4 * $C$1)". Formulas in range C4:C7 use a / an _________ reference to cell C1.
absolute
What does the WEEKDAY function return when you provide a date as an argument?
an integer between 1 and 7
What form of LOOKUP is used when you have a small list of values and the values remain constant over time?
array form
Please see the attached workbook. In cell A2, how is the data validation done?
by checking if the number of characters is between 4 and 20
When working with Pivot Tables you can remove all the fields from the report so that you can start over. To do this, from the Excel Ribbon, on the Analyze tab, go to the Actions group. Then click the arrow on the Clear button, and select ______.
clear all
A calculated item must reside in the ________ labels, ______ labels or ___________ area of a pivot table.
column, row, filter
In Excel, the ISNONTEXT() function returns TRUE if its argument refers to which of the following?
content other than text (e.g., a date or a number) or a blank cell
Which of the following does the function =NOW() return?
current date and time
A Web query contains information about the path to the ___________ and can pull the __________ data directly from that source.
data source, updated
Which of the following is NOT a type of cell reference?
dynamic
When you specify which rows should be included in your query (selection criteria), you are satisfying which step of the Query Wizard?
filter data
What does the Excel keyboard shortcut Ctrl+Alt+F9 do?
forces a complete recalculation of all formulas in all open workbooks.
If the user copies a cell that does not use data validation and pastes it to a cell that uses data validation, the data validation rules are _____
ignored/do not work
What is referred to as the "0th" day of next month while working with dates and times in Excel?
last day of current month
What is the value to be found in the first row of the table while using the HLOOKUP function?
lookup value
A _____________ data table shows the results of any number of calculations for different values of a single input cell. For example, using a single loan amount, you can calculate a table of monthly payments each for a different interest rate.
one-way
Which of the following options best describe what the following formula would do? =VLOOKUP(D2,PAGES!A2:B10,2,TRUE)
perform a VLOOKUP in a different worksheet which does not have an exact match
The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______.
range (and) criteria
A date filter in Pivot Tables is used to ____________.
specify a time period for which you need the data
The =TODAY() function is used:
to insert the current date which is updated each time a workbook is opened or recalculated
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
Please refer to the attached workbook. The ________ and ________fields are in the Rows and values section respectively.
weekday, amount