MIS 112 final
Please see the attached workbook. The result of an HLOOKUP function is "yellow." What is the formula used to obtain this result? =HLOOKUP("FRUIT",A1:B5,3,FALSE) =HLOOKUP("Color", A1:B2,3,FALSE) =HLOOKUP("Color",A1:B5,3,TRUE) =HLOOKUP("Color",A1:B5,3,FALSE)
=HLOOKUP("Color",A1:B5,3,FALSE)
What formula displays the value of B5 stored in a file named "example.xslx" in a different directory called "TEST" in the "C:\" drive? =HYPERLINK("C:\TEST\example.xslx"B5) =HYPERLINK("C:\TEST\example",B5) =HYPERLINK("C:\TEST\example.xslx",B5) =HYPERLINK("C:\example.xslx",B5)
=HYPERLINK("C:\TEST\example.xslx",B5)
Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the entry in cell A1 is an odd number? =ISODD(A1) =ISNOTEVEN(A1) =ODD(A1) All the other options are correct
=ISODD(A1)
Which Excel function returns the serial number of current date and time? =TODAY() =NOW() =DAY() =DATE()
=NOW()
Which of the following formulas can you use to change a text string of military time (e.g., 21:12) in cell Y3 to a standard value of time? =TIMEVALUE(LEFT(Y3,2)&":"&RIGHT(Y3,2)) =TIMEVALUE(RIGHT(Y3,2)&":"LEFT(Y3,2)) =DAYVALUE(LEFT(Y3,2)&":"&RIGHT(Y3,2)) =NONMILITARY(Y3)
=TIMEVALUE(LEFT(Y3,2)&":"&RIGHT(Y3,2))
Which of the following is TRUE for Pivot Tables? A Pivot table can be sorted by any column selected in the Pivot table field list The main drawback of Pivot tables is that you cannot sum up values (e.g., Sales for a region) The main difference between Pivot tables and regular Tables is that Pivot tables allow more columns Pivot tables are a new feature in Excel 2016
A Pivot table can be sorted by any column selected in the Pivot table field list
When customizing a sparkline, which of the following is NOT one of the options? Change the width or height of a cell. Insert a Sparkline into merged cells. Change the Sparkline type. Convert the Sparkline into a table.
Convert the Sparkline into a table.
To open an HTML file stored on a Web server using Excel, what needs to be done? Copy the URL and paste it into the Name Field in the Open dialog box. Excel cannot open an HTML file stored on a Web server. Type the name of the HTML file in the Name Field in the Open dialog box. Choose File -> Open -> Get External Data -> From Web
Copy the URL and paste it into the Name Field in the Open dialog box.
From the Settings tab of the Data Validation dialog box, you can specify a wide variety of data validation criteria. Many options are available from the Allow drop-down list. Which of the following options would you select if you want the user to enter a date such that it falls on a Sunday? Custom Date Time Text length
Custom
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. Design Views Tailor Views Custom Views Redesign Views
Custom Views
When you have imported data from an external data source, you can change the properties of the 'external data range' by using the External Data Properties dialog box. How will you open this dialog box? Data (tab) -> Connections (group) -> Properties Data (tab) -> Connections Data (tab) -> Connections (group) -> Connections Data (tab) -> Connections (group) -> Refresh
Data (tab) -> Connections (group) -> Properties
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? 100 is a whole number Data validation settings require the salary value to be greater than the hours worked value, 120. Data validation settings require the salary value should be less than the hours worked value, 120. 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: &[Date] ? Displays the current date Displays the date when the workbook was created Displays the date when the workbook was last modified None of the above
Displays the current date
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 Internal Data Properties Dialog Box General Properties Dialog Box You cannot adjust properties of the external data range
External Data Properties Dialog Box
True or false: Excel can only import HTML files from your local computer. False, Excel can import HTML files from the local computer or the web. False, Excel can only import HTML files from the web. True, Excel can only import HTML files from your local computer. False, Excel cannot import HTML files.
False, Excel can import HTML files from the local computer or the web.
How do you change the calculation mode while dealing with a calculation intensive data table? Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Except For Data Tables Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Formulas (tab) -> Calculation (group) -> Calculation Options -> Manual Press Ctrl+C, followed by: Alt+P
Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Except For Data Tables
Which of the following is one of the ways to create a Pivot Chart? Insert (tab) -> Charts (group) -> PivotChart Insert (tab) -> Sparklines (group) -> PivotChart Insert (tab) -> Tables (group) -> PivotChart Insert (tab) -> Illustrations (group) -> Shapes -> PivotChart
Insert (tab) -> Charts (group) -> PivotChart
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) -> Links (command group) -> Text Box Insert (tab) -> Symbols (command group) -> Text Box Home (tab) -> Text(command group) -> Text Box Insert (tab) -> Text (command group) -> Text Box
Insert (tab) -> Text (command group) -> Text Box
How does the Excel search/replace function work for dates? It search a cell for any feasible date format including numbers and text dates, and then does a replace It searches for a date as displayed in the Formula bar, and then does a replace It can find dates, but not replace them It cannot find dates
It searches for a date as displayed in the Formula bar, and then does a replace
When using the MATCH function, the match_type "-1" is best described by which of the following options? It will find the smallest value that is greater than or equal to the "lookup_value" It will find the first value that is equal to "lookup_value" It will find the largest value that is less than or equal to "lookup_value" It returns a random value from within a table or range
It will find the smallest value that is greater than or equal to the "lookup_value"
Which of the following functions looks in a one-row or one-column range (lookup_vector) for a value (lookup_value) and returns a value from the same position in a second one-row or one-column range (result_vector). IF INDEX MATCH LOOKUP
LOOKUP
What statement about the two Hyperlink arguments Hyperlink(link_location,[friendly_name]) is TRUE? Link_location is required, Friendly_name is required Link_location is required, Friendly_name is optional Link_location is optional, Friendly_name is required Link_location is optional, Friendly_name is optional
Link_location is required, Friendly_name is optional
hich of the following is NOT one of the options for highlighting certain data points in a Sparkline? High point Low point First point Middle Point
Middle Point
Please see the attached workbook. Is =VLOOKUP(E3,TABLE1,3,FALSE) a valid lookup function when used in cell E4? Yes No: because TABLE1 does not exist No: because the table has only two columns but the function refers to column 3 None of the other answers are correct
No: because the table has only two columns but the function refers to column 3
Please see the attached workbook. Which LOOKUP function can be used in order to get the minimum Quantity necessary (in cell E2) for the entered Discount (in cell E3)? VLOOKUP HLOOKUP Both of the above None of the above
None of the Above
How do you copy a Pivot table (including its design format) to a new worksheet? None of the answers provided is valid select Insert (tab) -> Tables (group) -> Pivot Table select Insert (tab) -> Copy Pivot table. Then select a new worksheet and press Ctrl +V select the entire table and choose Home (tab) -> Clipboard (group) -> Copy (or, press Ctrl+C). Then select a new worksheet and choose Home (tab) -> Clipboard (group) -> Paste -> Paste Values
None of the answers provided is valid
If you want to trace cell relationships for formulas, which of the following commands / options could you use? Name manager Spelling Both options A and B None of the options provided is correct
None of the options provided is correct
How can you define a Print Area after selecting the required cells on the worksheet? On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area. On the Page Layout tab, in the Page Setup group, click Print Area, and then click Custom Print Area. On the Page Layout tab, in the Page Setup group, click Print Area, and then click Clear Print Area. Press the keyboard shortcut of: Ctrl+!
On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area.
_______ refers to the non-printing areas along the sides, top, and bottom of a printed page. You do not see data from worksheet cells here, but may see a header or footer. Watermarks Page Margins A Background Image Hidden text
Page Margins
Which of the following is TRUE about Web queries? Performing Web queries creates a new HTML file Performing Web queries copies information from an HTML file into the current workbook Web queries are not useful in Excel because an external data can change None of the options provided is true
Performing Web queries copies information from an HTML file into the current workbook
By default, Excel prints worksheets in ___________ orientation Landscape Portrait Angular Glossy
Portrait
Which function can be used to make crude/simple horizontal separators between cells? REP() CSV() REPT() CDIV()
REPT()
The function ______________ updates specific characters in a string with a new value (i.e., with new text). Hint: This function is used when you know the text to be replaced but not the specific location. FIND_AND_REPLACE() SUBSTITUTE() FIND() SEARCH()
SUBSTITUTE()
In the attached image, how would you modify the pivot table to include only Joseph and Lawrence from the Middle region and nothing else? Select the required cells and then right-click, point to Filter, and click Keep Only Selected Items. Select the required cells and then right-click, point to Filter, and click conditional formatting. Set "Date Filter" accordingly. Refresh the pivot table data source.
Select the required cells and then right-click, point to Filter, and click Keep Only Selected Items.
There are a few commands that could be used to customize sparklines to highlight parts of data. One of them is Markers. What is its purpose? Show data markers in the Sparkline Show letter markers in the Sparkline Show no markers in the Sparkline Apply a different color to negative values in the Sparkline
Show data markers in the Sparkline
_____ is an interactive control that makes it easy to filter data in a pivot table. Field Slicer Row None of the answers provided is correct
Slicer
Which of the following is TRUE about Pivot Charts? Slicers also work with pivot charts By default, pivot charts are embedded in a new worksheet Pivot Charts work in Excel 2010 but not Excel 2016 The default pivot chart type is a Pie Chart
Slicers also work with pivot charts
What function removes leading and trailing spaces from a cell? TRIM CUT COMPACT CLEAN
TRIM
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 An error is returned in all cases Presence of a symbol such as . Instead of a : in the time, e.g., 16.24 (instead of 16:24) The presence of the character ':' (colon) in the time, e.g., 15:25
The contents of cell A1 do not comprise four digits
After you have selected the data to create a chart, which of the following is the correct sequence of options needed to create a clustered column chart? a. Insert --> Charts --> Column --> Clustered Column b. Insert --> Charts --> Bar --> Clustered Column c. Insert --> Charts --> Clustered Column d. Insert --> Charts --> Other Charts
a. Insert --> Charts --> Column --> Clustered Column
What type of chart is shown in the figure? a. Line chart b. Bar chart c. Column chart d. Radar chart
a. Line chart
What is the name of the company that created Excel? a. Microsoft b. Google c. Apple d. Dell
a. Microsoft
Does deleting a cell by pressing the Delete key also delete the conditional formatting on the cell? a. No b. Yes, but only if the data is text c. No, it only deletes the cell's style d. Yes
a. No
Would you be able to apply the "watch window" option in multiple sheets at the same time? a. No b. Yes, but only if there are no more than 3 sheets in the workbook c. Yes d. This option was available in Excel 2013 but not in Excel 2016
a. No
Can you print data in an Excel Form attached to a table? a. No, you cannot b. Yes, you can always perform an action like Printing in Excel c. You can print forms with the "Print" button on the Form only d. Printing is disabled unless you buy the "Print Form" Excel Professional feature app from Microsoft
a. No, you cannot
Examine the survey results in the attached workbook (in Sheet2). What type of chart would be most suitable to represent the survey response data? a. Pie b. Scatter c. High-Low-Close d. Area (2D or 3D)
a. Pie
Which of the following is an object that contains the actual data in the chart? a. Plot area b. Pie area c. Bar area d. Column area
a. Plot area
Please refer to the figure. Which of the following is the title of the chart displayed? a. Sales per month b. Sales (in thousands) c. Month d. Series 1
a. Sales per month
Which of the following does not hold true for Watch Windows? You select the cells that you want to watch, from the Formulas (tab) --> Formula Auditing --> click Show Watch Window You can see all the formulas in a worksheet and print them for later reference It helps you to keep an eye on a cell and its formula while you work, even when the cells are out of view You can move the Watch window toolbar around your open window
You can see all the formulas in a worksheet and print them for later reference
Which of the following is TRUE about the DATEDIF() function? It can be found under the dropdown function for the Date & Time category It appears in the Insert function dialog box You must enter it manually The function has four arguments
You must enter it manually
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 a #VALUE! Error a series of question (?) marks a #REF! error
a series of hash (#) marks
Please examine the attached workbook. Given the data in Sheet1, which of the following steps should be followed to obtain the data in Sheet2 ? a. Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose b. It is only possible if done manually c. Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Formulas d. Select cells A1 through D3 in Sheet1 -> Copy -> Transpose -> Go to Sheet2 -> Paste
a. Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose
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? a. The data stored in the two cells A4 and B4 are identical b. Since we have the month name spelled out in cell B4, the underlying data stored is different c. Date format options are available in the "Format Cells" categories of "Date", "Special" and "Custom" d. Excel requires non-US date formats to be stored as text, not true dates
a. The data stored in the two cells A4 and B4 are identical
Which of the following option sequences will arrange all open non-minimized Excel workbook windows side-by-side on the screen? a. View --> Arrange All b. View --> Freeze Panes c. Data --> Text to Columns d. Page Layout --> Breaks
a. View --> Arrange All
When a row of data is very ____ and requires repeated horizontal scrolling, consider using a data form to add, edit, find, and delete rows a. Wide b. Narrow c. Transformed d. Empty
a. Wide
Please refer to the attached images. Go to the "View" tab on your Excel ribbon. Which command changes your view from what's seen in "worksheet_image1.png" to "worksheet_image2.png"? a. Zoom to Selection b. View Side by Side c. Freeze Panes d. None of the options described will work
a. Zoom to Selection
Examine the 6th argument in the formula below. What kind of argument is it?=SUM(B1, 5, 6, 7, SQRT(4), A1:A5, TRUE) a. a range reference b. an empty argument c. a literal value d. a logical value
a. a range reference
Please refer to the figure. The address of the active cell consists of which of the following? a. column letter, row number b. column height, row width c. column letter d. row number
a. column letter, row number
In Excel, the ISNONTEXT() function returns TRUE if its argument refers to which of the following? a. content other than text (e.g., a date or a number) or a blank cell b. a cell containing data in a numeric or date format c. this function is used primarily for blank or empty cells d. a cell with alphanumeric characters
a. content other than text (e.g., a date or a number) or a blank cell
Which of the following types of data may NOT be contained in a cell in an Excel worksheet? a. objects b. formulas c. text d. numeric
a. objects
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 a. range A1 - A10 b. 10 (the value of the mode in cells A1 - A10) c. there is insufficient information to answer this question d. 10 (the number of cells in the range A1 - A10)
a. range A1 - A10
What is the Skip blanks option (under Excel Paste Special option) used for while copying a range to another area? a. to prevent blank cells in the copied range from overwriting existing data b. to skip pasting content into the blank cells c. to copy the blank cells from the copied range into the paste area d. None of the above
a. to prevent blank cells in the copied range from overwriting existing data
An Excel workbook can contain one or more of which of the following? a. worksheets b. files c. notebooks d. filenames
a. worksheets
Most pivot tables are created from numeric data, but pivot tables are also useful with some types of non-numeric data. Because you cannot sum non-numbers, this technique involves ____________. rounding-off counting subtracting division
counting
Please see the attached workbook. What is the URL Query String for the extract in the attached worksheet? http://www.imdb.com/ http://www.imdb.com/careers http://www.imdb.com/latest http://blackboard.eller.arizona.edu/
http://www.imdb.com/
What happens when you click the icon in a Slicer's upper-right corner? it adds new filtering it removes the effects of filtering it copies the filtering conditions the slicer window closes
it removes the effects of filtering
In a Pivot Table, the names of the fields for the report come from ________. an associated pivot chart the worksheet names both A & B none of the answers provided is correct
none of the answers provided is correct
Worksheet background images are for ______ display only, the images do not appear when the worksheet is ________. offline, printed onscreen, printed offline, saved onscreen, scanned
onscreen, printed
A _________ is a graphical data representation of data displayed in a pivot table. pivot chart reverse pivot pie chart slicer sparkline
pivot chart
The function of value filter in a pivot table is to ___________. read the data and select those rows with cells that match your criteria modify the data write the data with a different value help you insert the correct pivot chart
read the data and select those rows with cells that match your criteria
Please refer to the attached workbook. The ________ and ________fields are in the Rows and values section respectively. date, amount weekday, amount date, branch None of the above
weekday, amount
Please see the attached workbook. The formula:=OFFSET(A1,MATCH("Mary",A2:A5,0),2) returns what value? 101 Name 28 22
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 4300 respectively 2300 and 4300 respectively 2200 and 4100 respectively 2300 and 2200 respectively
2200 and 4100 respectively
Please refer the attached workbook. What is the total amount of Sales for the Arizona (AZ) state in the month of January (Jan)? 4500 2300 1200 2200
2300
Which of the following is NOT one of the buttons in the Spelling dialog box in Excel? Ignore All Add to Dictionary Autocorrect Conditional Formatting
Conditional Formatting
In Excel, in order to calculate the cumulative principal paid between any two payment periods, what function will you use? CUMPRINC() CUMIPMT() PPMT() PMT()
CUMPRINC()
Which of the following error codes indicates that a formula refers to a cell that is invalid? This type of error can occur if a referenced cell is deleted from the worksheet. #REF! #VALUE! #N/A #NAME?
#REF!
What result does the following formula display? =DOLLAR(5742.999,2) $5,742.99 $5742.99 $2,999,2475 $5,743.00
$5,743.00
What steps would you take if you wanted to adjust the outline of a rectangle shape that has already been inserted into your worksheet? 1) Click the shape. 2) Click (Drawing Tools) Format. 3) In the Shape Outline group, change the settings. 1) Click the shape. 2) Click Home (tab) --> Font (group) --> Fill Color 1) Click the shape. 2) Click (Drawing Tools) Format. 3) In the Shape Styles group, select a different outline. 1) Click the shape. 2) Hold shift while pressing the up/down arrow keys to increase/decrease the size of the outline.
1) Click the shape. 2) Click (Drawing Tools) Format. 3) In the Shape Styles group, select a different outline.
Please refer to the attached workbook. What is the total new deposit amount on Sep - 08 in North County branch? 139196 62787 196188 77674
196188
How do you group Pivot Table items manually? After creating the pivot table, select the items to be grouped and then choose Analyze (tab) under PivotTable Tools and select Group (group) -> Group Selection After creating the pivot table, select the items to be grouped and then choose Design (tab) under PivotTable Tools and select Group (group) -> Group Selection Select columns in the pivot table and right click on them and choose the option to "Cluster Group" None of the answers provided is valid
After creating the pivot table, select the items to be grouped and then choose Analyze (tab) under PivotTable Tools and select Group (group) -> Group Selection
An Excel formula may return which of the following errors? #REF! #DIV/0! #VALUE! All of the above
All of the above
Automatic grouping of Pivot Table items is possible when a field contains which of the following types of values? Numbers Dates Times All of the answers provided are correct
All of the answers provided are correct
How can you avoid the "#NAME?" error value? If the formula uses a label in a formula, make sure labels are allowed Enclose any text in double quotation marks Make sure all range references in the formula use a colon All of the options provided are correct
All of the options provided are correct
Which of the following actions can be performed on objects placed on the drawing layer with no effect on any other elements in the worksheet? move resize delete All of the options provided are correct
All of the options provided are correct
Which of the following is a type of Sparkline in Excel? Line Column Win/Loss All of the options provided are correct
All of the options provided are correct
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 Loan Payment Interest
Annuity
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? Top 5 Below Average Top 7 Icon set
Below Average
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 greater than 21 for 'Age' column. From the Column to Filter list, select Age. In the right panel, select 'is greater than' from the first drop-down list and then select '21.0' from the second drop-down list From the Column to Filter list, select Age. In the right panel, select 'is lesser than' from the first drop-down list and then select '21.0' from the second drop-down list From the Column to Filter list, select Name. In the right panel, select 'is lesser than' from the first drop-down list and then select '21.0' from the second drop-down list 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 Age. In the right panel, select 'is greater than' from the first drop-down list and then select '21.0' from the second drop-down list
The HLOOKUP and LOOKUP functions can be found under Formulas (tab) ->_________. Function Library (command group) -> Lookup & Reference Function Library (command group) -> Text Function Library (command group) -> Financial Function Library (command group) -> Logical
Function Library (command group) -> Lookup & Reference
What function helps ensure that the formula will continue to reference the intended cells if the pivot table layout is changed? GETPIVOTDATA PUTPIVOTDATA EXTENDPIVOTDATA REFPIVOTDATA
GETPIVOTDATA
You can control how a sparkline handles empty cells in a range (and thus how the sparkline is displayed) by using the ______________ dialog box. Hidden & Empty Cell Settings Marker Color Settings Sparkline Empty Style settings Sparkline Color Settings
Hidden & Empty Cell Settings
If there are negative values in your data, you can emphasize this by showing a(n) __________ axis in your sparkline. Blank Vertical Diagonal Horizontal
Horizontal
Please see the attached image. What is the difference between two Sparkline groups (top and bottom)? The group at top uses the "Same for All Sparklines" axis minimum & maximum settings, but the group at the bottom uses the default (automatic) axis settings for each sparkline in that group The group at top uses the default (automatic) axis setting for each sparkline, but the group at bottom uses the vertical axis setting of "Same for All Sparklines" Both the groups use the default (automatic) minimum and maximum value settings for the vertical axis Both the groups use the "Same for All Sparklines" setting (i.e., for axis minimum and maximum values)
The group at top uses the default (automatic) axis setting for each sparkline, but the group at bottom uses the vertical axis setting of "Same for All Sparklines"
f a pivot chart is created and the underlying table is deleted, what occurs? The pivot chart is also deleted. The pivot chart remains. The data the Chart is based on is invalid since the Pivot Table has vanished. The chart is not deleted, but it no longer contains the data from the underlying table.
The pivot chart remains.
Excel formatting tools are used to achieve which of the following? To create programming code in Java/C++ To make the end result attractive and more functional To distract readers from inaccuracies in text and figures The primary use of formatting is to create Excel Pie Charts All of the options listed are valid
To make the end result attractive and more functional
Which function will you use to calculate depreciation for multiple periods in Excel? VDB() DEPREC() CONCATENATE() MULTIDEPREC()
VDB()
Which of the following is the file format of the Excel 97-2003 workbook? a. .xls b. .xlsx c. .xlsb d. .xltm
a. .xls
By default, what is the extension used when you save a workbook in Excel 2016? a. .xlsx b. .xslx c. .xls d. .xsl
a. .xlsx
Examine the attached worksheet. Using the ANOVA: Single Factor Data Analysis Tool (available through the Analysis ToolPak Add-in) on A1:C6, which of the following options is the correct value for the p-value of the data in the worksheet? a. 0.00388865177938274 b. 3.83333333333333 c. 2.5 d. 0.12
a. 0.00388865177938274
Examine the attached worksheet. Using the Correlation Data Analysis Tool (available through the Analysis ToolPak Add-in) on A1:C6, which of the following options is the approximate value of the correlation coefficient for Relaxation Therapy and Music Therapy? a. 0.56 b. 0.05 c. 0.39 d. 1
a. 0.56
Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet? a. = NOW() b. = DATE() c. = TIME() d. = TODAY()
a. = NOW()
In the attached Excel workbook PS1_Comments_Calculation.xlsx , which of the following formulas can be used to calculate Sophie's total score in both the exams? a. =B4+C4 b. =B3+C3 c. =B4*C4 d. ={B4+C4}
a. =B4+C4
Examine the attached worksheet. Which of the following formulas, when entered in cell B12, will give the total discounted cost of all the items purchased? Formulas_Test.xlsx a. =SUM(F2:F8) b. =TOTAL(F2:F8) c. =SUMIF(F2:F8) d. =AUTOSUM(F2:F8)
a. =SUM(F2:F8)
Please refer to the figure. In an Excel worksheet, what column name comes directly after Column Z? a. AA b. AZ c. ZA d. ZZ
a. AA
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. Which of the following methods should you use to explain your request to your assistant? a. Add a comment to the sales figure cell, and explain to your assistant what you want him to do b. Delete the sales figure for Monday (seeing the the blank cell will make things clear to the assistant) c. Set a fill color of dark red for the sales figure cell d. Make a pie-chart to explain the ratio of sales of Monday to other days in the week so the assistant sees which day has the highest sale
a. Add a comment to the sales figure cell, and explain to your assistant what you want him to do
Which of the following options moves the active cell right one screen? a. Alt+PgDn b. Alt+PgUp c. PgDn d. PgUp
a. Alt+PgDn
Which of the following is an INVALID horizontal text alignment option provided in Excel? a. Bottom b. Justify c. Distributed d. Fill
a. Bottom
Please see the attached Excel workbook image in answering this question. In order to sort the 'Salesperson' column in a descending order, which of the following steps is correct? a. Click on any cell containing Salespersons data, from the Ribbon pick the Data tab, and then click on the "Sort Z to A" command button b. Select the entire Salesperson Column, then Click on the Review tab (in the Excel ribbon), followed by clicking on Group and "Sort" c. The Salesperson column cannot be sorted d. Convert the data into a Table, right click on the Table header and select the A-to-Z sort option
a. Click on any cell containing Salespersons data, from the Ribbon pick the Data tab, and then click on the "Sort Z to A" command button
Which keyboard shortcut is used to select all cells in an open Excel worksheet? a. Ctrl + A b. Ctrl + X c. Ctrl + V d. Ctrl + C
a. Ctrl + A
Which of the following is the keyboard shortcut that creates a new blank workbook in Excel? a. Ctrl + N b. Ctrl + E c. Ctrl + W d. Ctrl + B
a. Ctrl + N
Which of the following procedures will NOT close your Excel workbook? a. Ctrl + S b. Ctrl + F4 c. Ctrl + W d. File -> Close
a. Ctrl + S
Which of the following keyboard shortcuts will add the current time to a cell in an Excel worksheet? a. Ctrl + Shift + : b. Ctrl + ; c. Ctrl + ' d. Ctrl + T
a. Ctrl + Shift + :
Which of the following keyboard shortcuts "cuts" the current selection of cells to both the Windows and Office clipboards? a. Ctrl + X b. Ctrl + V c. Ctrl + C s. Ctrl + Z
a. Ctrl + X
Please refer to the figure. Which of the following Excel tabs is NOT visible in a worksheet by default? a. Developer b. Review c. Home d. insert
a. Developer
Which of the following is a VALID method of hiding rows or columns? a. Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list) b. Click on the Row/Column header, (then) right-click and choose Delete c. Right-click on the cell, (then) choose "Insert" d. Home (ribbon tab), (then) Paste (in Clipboard group)
a. Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list)
To make Data Bars easier to view, we can ________ the numerical values in the cell. query hide automatically shrink use bold fonts for
hide
Examine the attached file: PS1_formulas.xlsx What is total widget sales for the West Region for the year? a. $13,282 b. $5,129 c. $4,015 d. None of the above
b. $5,129
Please refer to the figure. How many worksheets are contained in the workbook shown? a. 1 b. 5 c. 3 d. A1
b. 5
What would you type in the Find and Replace dialog box (Home (tab) -> Editing (command group) -> Find & Select) to find all four-digit entries that begin with 5 and end with 2? a. 5?2 b. 5??2 c. 52? d. *2
b. 5??2
Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were sung by Madonna and were three to four minutes long (inclusive)? a. =COUNTIFS(B2:B23,"Madonna",D2:D23,"<4",D2:D23,">3") b. =COUNTIFS(B2:B23,"Madonna",D2:D23,"<=4",D2:D23,">=3") c. =COUNTIFS(B2:B23,'Madonna',D2:D23,'<=4',D2:D23,'>=3') d. =COUNTIFS(B2:B23,"Madonna",D2:D23,<=5,D2:D23,>=3)
b. =COUNTIFS(B2:B23,"Madonna",D2:D23,"<=4",D2:D23,">=3")
Which of the following does the search string ~?NULL~? match when entered in the find and replace dialog box? a. !NULL# b. ?NULL? c. ~?NULL~? d. ~NULL~
b. ?NULL?
In the image shown below, identify the correct chart types. You can use the data in the attached Excel workbook (charts_SampleData.xlsx) to try different charts. a. Chart 1: Bar, Chart 2: Column b. Chart 1: Column, Chart 2: Bar c. Chart 1: Line, Chart 2: Bar d. Chart 1: Column, Chart 2: Line
b. Chart 1: Column, Chart 2: Bar
What is the shortcut to open the Find and Replace dialog box with the "Find" tab open? a. Ctrl+H b. Ctrl+F c. Ctrl+A d. Ctrl+Alt+F
b. Ctrl+F
Which of the following keyboard shortcuts can be used in Excel 2016 to create a table? a. Ctrl+F2 b. Ctrl+L c. Alt+T d. Ctrl+Alt+L
b. Ctrl+L
Please refer to the figure. What Excel element is shown in the red box? a. File button b. Quick Access toolbar c. Scrollbar d. Ribbon
b. Quick Access toolbar
Suppose you have a named range called "myrange" on Sheet1 of your workbook. If you delete the rows or columns that contain the range "myrange", the name (in Name Manager) contains an invalid reference. If you open name manager and examine what "myrange" refers to, you will see the missing cells denoted by ______. a. #VALUE! b. Sheet1!#REF! c. #DELETEDROWS d. Sheet1!#INVALIDREF!
b. Sheet1!#REF!
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? a. The word "budget" must appear in the cells surrounded by asterisks (i.e., the * character). b. The word "budget" can appear anywhere within the cell. c. It looks specifically for cells containing budget multiplied by some number. For example, a cell containing the formula: "=budget*0.25". d. Only cells with the text "budget" at the beginning or the end of the cell, are counted. Thus a cell with "budget constraint" is counted, but a cell with "Previous budget estimates" is not counted.
b. The word "budget" can appear anywhere within the cell.
Which of the following chart types does NOT support the Data Table chart element? a. Stacked Column Chart b. Treemap c. Clustered Column Chart d. Line Chart
b. Treemap
Is it possible to convert a cell with text data into a .png format ? a. Yes, but only if the cell width is no larger than 72 pixels b. Yes c. This option was available in Excel 2013 but not in Excel 2016 d. No
b. Yes
The ____ command is used to display text on multiple lines in a cell. a. shrink to fit b. wrap text c. merge cells d. icon formatting
b. wrap text
You can protect your Excel workbook with a password. Which of the following is TRUE about password-protected workbooks? a. passwords are NOT case sensitive b.passwords are case sensitive c. if you forget your workbook password, you can email the TA to retrieve it for you d. workbook passwords automatically expire in 360 days
b.passwords are case sensitive
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) a. C20/F8 b. =SUM(C15:C19) c. =C20/F8 d. =PERCENTSALES(F22)
c. =C20/F8
Which of the following statements is true? a. A workbook can have at most 3 worksheets. b. A worksheet can have many workbooks. c. A workbook can have many worksheets. d. A worksheet can have at most 3 workbooks.
c. A workbook can have many worksheets.
In the Top Salespersons list, which feature can be used to populate the numbers "3," "4," and "5" in cells "A17," "A18," and "A19," respectively? a. Format Cells b. Quick Access toolbar c. Autofill d. =SUM() formula
c. Autofill
Please select the Excel function which allows us to answer the question: "How many days of stock data do I have?" You may refer to the image in the question. a. MIN() b. MAX() c. COUNT() d. SUM()
c. COUNT()
What option in the Table Tools / Design tab removes a table without losing the data in it? a. Remove Duplicates b. Summarize with PivotTable c. Convert to Range d. Delete Table
c. Convert to Range
What is the shortcut to open the Find and Replace dialog box with the "Replace" tab open? a. Ctrl+F b. Ctrl+A c. Ctrl+H d. Ctrl+FIND
c. Ctrl+H
Examine the attached workbook. If you format a chart element with the font seen in Chart 2, how can you reset it to its original state? a. Format tab (chart tools) --> Reset to Match Style (under the "Current Selection" group) b. Right-click on the chart --> Reset to Match Style c. Either of the previous options (resetting through the menu or the right-click options work) d. Home tab --> Wrap Text, followed by, Paste
c. Either of the previous options (resetting through the menu or the right-click options work)
Which of the following command sequences will allow you to save a copy of your workbook with a different name? a. Ctrl, A b. Ribbon, Save c. File, Save As d. Data, Create Copy
c. File, Save As
Which of the following is NOT one of the Excel functions to determine whether a cell contains an error value? a. ISERROR b. ISNA c. ISLOGICAL d. ISERR
c. ISLOGICAL
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). a. Shapes b. Categories c. Layouts d. Attributes
c. Layouts
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? a. The simplest way is to click on the "Wrap text" button in Home tab. From the dialog box that pops up, pick the default $-symbol formatting. b. This is automatically provided to you when you make an Excel Table c. 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 d. The correct way to do this is by going through the "Format Table" menu, and selecting the option of "Number" (with 2 decimal places and a $ symbol)
c. 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
Microsoft Office Excel can automatically generate a data form (which allows for convenient data entry, among other things) for your range or _______________. a. Chart b. Field c. Table d. Record
c. Table
What does the Excel keyboard shortcut Ctrl+Alt+F9 do? highlights error codes in the current workbook a. calculates only the formulas in the active worksheet. Other b. worksheets in the same workbook aren't calculated. c. forces a complete recalculation of all formulas in all open workbooks. d. Ctrl+Alt+F9 will not do anything in Excel
c. forces a complete recalculation of all formulas in all open workbooks.
In the attached image, examine the formula in cell "E9." What result will this formula produce? a. 20 b. 882 c. 2 d. #VALUE!
d. #VALUE!
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? a. $9,019 b. $13,218 c. $16,060 d. $24,914
d. $24,914
Examine the attached Excel file. When you copy the formula in cell "C3" to "F7", the cell in "F7" contains which of the following formulas? a. =$B$7*$F$2 b. =B$7*$F2 c. =B7*F2 d. =$B7*F$2
d. =$B7*F$2
Must a chart be embedded in the same worksheet as its data source? a. Yes, this is a limitation of Microsoft Excel but leads to easier linking between charts and the data. b. Yes, and this holds true for graphs as well. c. No, unless the chart is a histogram. d. No, a chart an be embedded or placed in a sheet separate from that of its data source.
d. No, a chart an be embedded or placed in a sheet separate from that of its data source.
Adding comments to a cell is available through which tab of the Excel ribbon? a. Home b. Formulas c. Data d. Review
d. Review
Please refer to the figure. What Excel element is shown in the red box? a. File button b. Quick Access toolbar c. Scrollbar d. Ribbon
d. Ribbon
While comparing two sheets using the "View Side by Side" feature, there is an option which lets you scroll the two sheets in sync. What is that option called? a. None of the (other) options provided is correct b. Side View c. Toggle d. Synchronous Scrolling
d. Synchronous Scrolling
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? a. Table Columns to the Left b. Table Columns to the Right c. Table Rows Above d. Table Column Above
d. Table Column Above
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? a. No, because the result will be the total for Quarter 1. b. No, because you must directly enter the formula for each quarter in cell C6. c. Yes for Excel 2013, but not for previous versions of Excel. d. Yes, the formula range will be adjusted automatically
d. Yes, the formula range will be adjusted automatically
Consider the function =COUNTIF(A1:A200, ">200"). The information between the parentheses in a function are known as the ____________ . a. function signature b. dependent variables c. cell references d. arguments
d. arguments
A single element in a worksheet that can hold a value, some text, or a formula is referred to as which of the following? a. entity b. component c. element d. cell
d. cell
Fill in the blank: The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______. a. range (and) constant b. expression (and) criteria c. constant (and) expression d. range (and) criteria
d. range (and) criteria
When you create a PivotTable report, each column of your source data becomes a _____ that you can use in the report. row record field sheet
field
What is the use of the following data validation formula: =ISTEXT(A1) ? forces the cell A1 to accept only text forces the cell A1 to accept only numbers forces the cell A1 to accept only ' * '
forces the cell A1 to accept only text
