MIS 112 Problem Sets
Please examine the attached image. Which of the following formulas will calculate the total number of stores across the four states? A. =SUM(B2:B5) B. =COUNT(B2:B5) C. =NUMBER(A1:B5) D. =COUNT(B1)
=SUM(B2:B5)
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
AA
Which of the following procedures will NOT close your Excel workbook? A. Ctrl+S B. Ctrl+F4 C. Ctrl+W D. File -> Close
Ctrl + S
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. Toggle B. Side View C. None of the (other) options provided are correct D. Synchronous Scrolling
Synchronous Scrolling
Data retrieved from Web Queries do not include __________ and contents of __________. A. text, data B. text, pictures C. pictures, scripts D. text, tables
pictures, scripts
dataLookup_eg8.xlsx Please see the attached workbook. What would you get in E4 if you typed in "South-Africa" in cell E3? A. an error because western cape is not a region in South Africa B. "an error because the formatting of the word "South-Africa" is different than the one found in the table C. the value "Western Cape" D. the value "South Africa"
"an error because the formatting of the word "South-Africa" is different than the one found in the table
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
$5,129
What is the correct order of steps to be followed when you use a query wizard? A. 1. choosing the columns, 2. sort order, 3. filtering data, and 4. finish B. 1. filtering data, 2. choosing the columns, 3. sort order, and 4. finish C. 1. choosing the columns, 2. filtering data, 3. sort order, and 4. finish D. None of the options provided is correct
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? A. 12 B. 8 C. 10 D. 14
12
With a single IF() function, how many actions can you ask Excel to carry out? A. 1 (i.e., an action if the criteria is true) B. 2 (i.e., an action if the criteria is any one of the true, or an action if the criteria is false) C. 3 (i.e., an action if the criteria is any one of the true, false, unknown) D. As many as you want, this is why IF() functions are so difficult to understand
2 (i.e., an action if the criteria is true, or an an action if the criteria is false)
In Excel, the maximum length of a Web query URL is _______ characters. A. 124 B. 512 C. 48 D. 255
255
What is the maximum number of characters a worksheet name can contain? A. 31 B. 34 C. 28 D. 32
31
dataLOOKUP_Ex1.xlsx Please see the attached workbook. What tax rate value is returned when you perform a VLOOKUP with an entered income of $45,500? A. 15% B. 28% C. 31% D. 36%
31%
What will be the result of this formula =IF(A1<100000,A1*5%,A1*7.5%) , if the cell A1 has a value of 90000? A. 4500 B. 500000 C. 4750 D. 6750
4500
Please refer to the figure. How many worksheets are contained in the workbook shown? A. 1 B. 5 C. 3 D. A1
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
5??2
formulas_mixed_cell_references.xlsx 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
=$B7*F$2
The formula to calculate the value of one minute, that can be added to a date serial number is: A. =1 / (24 * 60 * 60) B. =1 / (60 * 60) C. =1 / (24 * 60) D. =1 / (24 * 24)
=1 / (24 * 60)
Which of the following options, when used as a formula in the Custom Data Validation criteria for cell range A1:A10, enables the user to enter a value in A1:A10 only if it's greater than or equal to the value in A1? A. =A1>=$A$1 B. =A1>=A1 C. =A2<=$A$1 D. =A2>=$A1
=A1>=$A$1
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}
=B4+C4
Examine the attached file: PS1_formulas.xlsx 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(F2)
=C20/F8
Advanced_aggregation.xlsx Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were sung by singers whose last names contain exactly six letters? A. =COUNTIF(B2:B23,"6*?") B. =COUNTIF(B2:B23, "{6?}") C. =COUNTIF(B2:B23,"??") D. =COUNTIF(B2:B23,"??????")
=COUNTIF(B2:B23,"??????")
Advanced_aggregation.xlsx Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were longer than the average length of all songs on the list? A. =COUNTIF(D2:D23,"<"&AVERAGE(D2:D23)) B. =COUNTIF(D2:D23,">"&AVG(D2:D23)) C. =COUNTIF(D2:D23,">"&AVERAGE(D2:D23)) D. =COUNTIF(D2:D23,">"AVERAGE(D2:D23))
=COUNTIF(D2:D23,">"&AVERAGE(D2:D23))
To generate a series of dates 1 year apart in Excel, which of the following formulas is correct? (assuming the first date is entered in cell A1, and you are trying to compute the next year in the series) A. =DATE(YEAR(A1), MONTH(A1) + 1, DAY(A1)) B. =A1 + 7 C. =DATE(YEAR(A1) + 1, MONTH(A1), DAY(A1)) D. =DATE(YEAR(A1), MONTH(A1), DAY(A1) + 1)
=DATE(YEAR(A1) + 1, MONTH(A1), DAY(A1))
Examine the image in the question. In "C3," we have the supplier address "Tucson, AZ 85721" appearing in a single cell (the formula combines data from cells "E3," "F3," "G3"). Which of the following formulas will achieve this result? A. =E3 + "," + F3 + G3 B. =E3 &", " &F3 &" " &G3 C. =City, State/Province, Zip/Postal Code D. =SUM(E3:G3)+COUNT(C3)
=E3 &", " &F3 &" " &G3
Suppose you deposit $100 at the beginning of each month (for 12 months) into an account that pays 3.5 percent annual interest compounded monthly. Which formula calculates the future value of your series of deposits? A. =FV(3.5%/12,12,-100,,1) B. =FV(3.5%/12,12,100,,1) C. =FV(3.5%,1,-100,,1) D. None of the above
=FV(3.5%/12,12,-100,,1)
What formula displays the value of B5 stored in a file named "example.xslx" in a different directory called "TEST" in the "C:\" drive? A. =HYPERLINK("C:\TEST\example.xslx"B5) B. =HYPERLINK("C:\TEST\example",B5) C. =HYPERLINK("C:\TEST\example.xslx",B5) D. =HYPERLINK("C:\example.xslx",B5)
=HYPERLINK("C:\TEST\example.xslx",B5)
To create a hyperlink to a specific location in a Microsoft Word document, you must use a bookmark to define the location you want to jump to in the document. Which of the following is the right option to create a hyperlink to the bookmark 'QrtlyProfits' in the document 'Annual Report.doc' located at http://example.microsoft.com? The link text to be displayed is "Quarterly Profit Report". A. =HYPERLINK("[Example.xlsx]E2","Quarterly Profit Report") B. =HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits","Quarterly Profit Report") C. =HYPERLINK("[Annual Report.docx]QrtlyProfits","Quarterly Profit Report") D. =HYPERLINK("QrtlyProfits","Quarterly Profit Report")
=HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits","Quarterly Profit Report")
dataLookup_eg7.xlsx Please see the attached workbook. If the 'amount' for the given 'date' is to be retrieved from the given range of data (I4:K7), which of the following formulas should be used? A. =INDEX(I4:K7,MATCH(B2,I4:I7,0),3) B. =INDEX(I4:K7,3) C. =MATCH(B2,I4:I7,0) D .=MATCH(I4:I7,0)
=INDEX(I4:K7,MATCH(B2,I4:I7,0),3)
Which of the following formulas is used to determine the date of the Sunday that was most recent? A. =WEEKDAY(DATE(2011,1,1)) B. =TODAY()-MOD(TODAY()-1,7) C. =TODAY()-MOD(TODAY()-3,7) D. =DATE(YEAR(TODAY()),12,31)-TODAY()
=TODAY()-MOD(TODAY()-1,7)
Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the cell entry is a date, and that thedate is a Sunday? Hint: Date format: MM/DD/YYYY A. =WEEKDAY(A1)=1 B. =WEEKDAY(A1)=SUNDAY C. =ISSUNDAY(A1)=1 D. =ISWEEKDAY(A1)=SUNDAY
=WEEKDAY(A1)=1
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. A combination of DATE(), FIND() and FIRST() B. A combination of REPT() and CONCATENATE() C. A combination of FIND() and MID() D. The function FIRST()
A combination of FIND() and MID()
Suppose you have a column of file names such as: 001-Chapter1.txt, ..., 015-Chapter15.txt, ..., 104-Chapter104.txt, etc. You want to use a formula to consistently remove the prefix numbers, e.g., make the file names into: Chapter1.txt or Chapter12.txt. Which of the following text functions can you use to make this change? A. A combination of: TRIM(), SUBSTITUTE(), REPT() B. A combination of: FIND(), INDEX(), CHAPTER() C. A combination of: FIND(), LEN(), MID() D. A combination of: REPT(), PREFIX(), REMOVE()
A combination of: FIND(), LEN(), MID()
Examine the image in the question. Which of the following is true for the new name being defined? A. A name is being applied to a. range B4:E4. This name "UA", can be used in formulas anywhere in the current workbook. B. A new name is being created for use specifically in the current worksheet (Sheet1), based on the given range (B4:E4) C. The UA range is being moved from cells B5:E5 to cells B4:E4 D. Three ranges, "UA", "ASU" and "NAU" are being defined as new names in the workbook
A name is being applied to a range B4:E4. This name "UA", can be used in formulas anywhere in the current workbook.
Based on the class lecture, which of the following is true about table renaming? A. table renaming is not recommended in Excel as it will cause formulas to stop functioning properly. B. renaming a table is done automatically when a table is extended (e.g., when you add a new row of data) C. a table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS) D. Excel tables are named based on the worksheet it is located in, e.g., a table in "Sheet1" will be named "Sheet1Table1" Or "Sheet1Table2"
A table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS)
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.
A workbook can have many worksheets.
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? A. Arrange B. Size C. Align D. Alter
A. Arrange
How does a missing data point show in a Sparkline graphic? A. Gap B. Zero C. Automatically connected to the next data point D. A Horizontal line
A. Gap
Which of the following is one of the ways to create a Pivot Chart? A. Insert (tab) -> Charts (group) -> PivotChart B. Insert (tab) -> Sparklines (group) -> PivotChart C. Insert (tab) -> Tables (group) -> PivotChart D. Insert (tab) -> Illustrations (group) -> Shapes -> PivotChart
A. Insert (tab) -> Charts (group) -> PivotChart
To add one or more Slicers to a worksheet, start by selecting any cell in a pivot table and then choose ______________. A. Insert (tab) -> Filters (group) -> Slicer. The Insert Slicers dialog box appears. B. Insert (tab) -> Slicer (group) -> Filters. The Insert Slicers dialog box appears. C. Right Click on it. The Insert Slicers menu option can be selected. D. All of the answers provided are correct
A. Insert (tab) -> Filters (group) -> Slicer. The Insert Slicers dialog box appears.
What are the two ways Excel offers to group Pivot table items? A. Manually, Automatically B. Default, Numeric C. Manually, Default D. Chart, Slicer
A. Manually, Automatically
Vector based images are represented by? A. Mathematical equations B. Discrete Points C. Both Discrete points and well as Mathematical equations D. Dates and Times
A. Mathematical equations
What do you use to toggle the display of the field headings in Pivot Tables? A. PivotTable Tools (tab) -> Analyze (tab) -> Show (group) -> Field Headers B. PivotTable Tools (tab) -> Analyze (tab) -> Hide Fields C. PivotTable Tools (tab) -> Design (tab) -> PivotTable Styles D. PivotTable Tools (tab) -> Analyze (tab) -> Headers
A. PivotTable Tools (tab) -> Analyze (tab) -> Show (group) -> Field Headers
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. For Qtr 3, which product was bought by the highest number of customers? Pivot_table_test.xlsx A. Raclette Courdavault B. Tarte au sucre C. Alice Mutton D. Chocolade
A. Raclette Courdavault
Which of the following file formats does NOT use a bitmap representation or format? (Bitmap images usually look good at their orginal size but lose clarity if you increase the size) A. SVG B. BMP C. PNG D. TIFF
A. SVG
How do you create a Sparkline? A. Select an empty cell or group of empty cells in which you want to insert one or more sparklines. On the Insert tab, in the Sparklines group, click the type of sparkline that you want to create. B. Select an empty cell or group of empty cells in which you want to insert one or more sparklines. On the Design tab, in the Sparklines group, click the type of sparkline that you want to create. C. Select an empty cell or group of empty cells in which you want to insert one or more sparklines. On the Formulas tab, in the Sparklines group, click the type of sparkline that you want to create. D. Select an empty cell or group of empty cells in which you want to insert one or more sparklines. On the Analysis Toolpak tab, in the Sparklines group, click the type of sparkline that you want to create.
A. Select an empty cell or group of empty cells in which you want to insert one or more sparklines. On the Insert tab, in the Sparklines group, click the type of sparkline that you want to create.
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? A. Show data markers in the Sparkline B. Show letter markers in the Sparkline C. Show no markers in the Sparkline D. Apply a different color to negative values in the Sparkline
A. Show data markers in the Sparkline
Which of the following is TRUE about Pivot Charts? A. Slicers also work with pivot charts B. By default, pivot charts are embedded in a new worksheet C. Pivot Charts work in Excel 2010 but not Excel 2016 D. The default pivot chart type is a Pie Chart
A. Slicers also work with pivot charts
With respect to Sparklines, what is FALSE? A. The Create Sparklines dialog box does NOT allow users to specify a different sheet for the Data Range. B. A Sparkline's data source can be changed. C. A Sparkline can be deleted. D. A set of Sparklines can be ungrouped.
A. The Create Sparklines dialog box does NOT allow users to specify a different sheet for the Data Range.
What is the output of the following command sequence: click the Analyze tab under PivotTable tools -> Actions (group) -> Clear -> Clear Filters? A. This removes all the filters at once B. This removes only filters on the selected column (or row) C. This does not remove any filters D. This is not a valid command sequence for any pivot table
A. This removes all the filters at once
Please see the attached screen capture. If we add one more month to the table (i.e., September), will the Sparkline update its Data range automatically? A. Yes, the data range is automatically updated B. No, it will not be updated automatically C. We have to make the changes manually to the graph D. None of the options provided is correct
A. Yes, the data range is automatically updated
Which of the following is TRUE about a Watermark in Excel? A. a watermark is an image (or text) that appears on each printed page. B. a watermark can be a faint company logo, or a word such as draft C. Excel doesn't have official command to print a watermark, but you can add a watermark by inserting a picture in the page header or footer. D. All of the above statements are TRUE
All of the above statements are TRUE
In context of the MATCH() function, which of the following is FALSE? A. The syntax of match is: MATCH (lookup_value,lookup_array, row_offset) B. MATCH returns the cell contents (e.g., a product name) when it detects two cells with duplicate contents C. A #VAL error value is returned if the MATCH function is unsuccessful in finding a match D. All of the options are false
All of the options are false
Which of the following statements about Trace Precedents are correct? A. It can help point out if a formula has been applied to a wrong cell range B. You can access Trace Precedents on the Formula Auditing toolbar C. By clicking Trace Precedents again, you can identify the next level of cells that provide data to the active cell D. All of the options provided are correct
All of the options provided are correct
Which of the following function(s) can be used to count error values in a range? A. All of the provided choices are valid B. ISERROR() C. ISERR() D. ISNA()
All of the provided choices are valid
PivotTables_Ex1.xlsx Please refer the attached workbook. What is the total amount of Sales for the Arizona (AZ) state in the month of January (Jan)? A. 4500 B. 2300 C. 1200 D. 2200
B. 2300
A pivot chart is which of the following? A. A bar chart that can pivot to a pie chart form with a single click B. A graphical representation of a data summarized in a pivot table C. A chart that is joined to a pivot table in a 4-way link D. A chart that cannot be used with a slicer
B. A graphical representation of a data summarized in a pivot table
There are a few commands that could be used to customize sparklines to highlight parts of data. One of them is Low Point. What is its purpose? A. Apply a different color to the highest data point in the Sparkline B. Apply a different color to the lowest data point in the Sparkline C. Apply a different color to negative values in the Sparkline D. Change all data points to the lowest data point and give them a single color
B. Apply a different color to the lowest data point in the Sparkline
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. A. Only one record corresponding to the 'CA' state remains B. Only one record corresponding to 'AZ' remains. C. Both the records remain. D. No records remain as they don't satisfy the condition specified.
B. Only one record corresponding to 'AZ' remains.
After you create a Sparkline, changing color is easy. Use the controls in the _______________. A. Sparkline Tools -> Design (tab) -> Type (group) B. Sparkline Tools -> Design (tab) -> Style (group) C. Sparkline Tools -> Design (tab) -> Color (group) D. Sparkline Tools -> Design (tab) -> Font (group)
B. Sparkline Tools -> Design (tab) -> Style (group)
If a pivot chart is created and the underlying table is deleted, what occurs? A. The pivot chart is also deleted. B. The pivot chart remains. C. The data the Chart is based on is invalid since the Pivot Table has vanished. D. The chart is not deleted, but it no longer contains the data from the underlying table
B. The pivot chart remains.
A calculated field must reside in the ________ area of the pivot table. A. Column B. Values C. Rows D. Report Filter
B. Values
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 ______. A. remove B. clear all C. shrink D. dissolve
B. clear all
What happens when you click the icon in a Slicer's upper-right corner? A. it adds new filtering B. it removes the effects of filtering C. it copies the filtering conditions D. the slicer window closes
B. it removes the effects of filtering
Consider a Column Sparkline with three columns. A wide area of blank space between two columns in a sparkline corresponds to which of the following? A. boolean data B. missing data C. a right aligned cell D. a value formatted as currency (not supported by sparklines)
B. missing data
Worksheet background images are for ______ display only, the images do not appear when the worksheet is ________. A. offline, printed B. onscreen, printed C. offline, saved D. onscreen, scanned
B. onscreen, printed
A date filter in Pivot Tables is used to ____________. A. return the date of the transaction B. specify a time period for which you need the data C. change the date of a transaction (e.g., a sale) D. modify the date in your underlying table
B. specify a time period for which you need the data
Ex2.xlsx Please refer to the attached workbook. The ________ and ________fields are in the Rows and values section respectively. A. date, amount B. weekday, amount C. date, branch D. None of the above
B. weekday, amount
How will you return a cell formatted as a date or time, back to its default or original format? A. Home (tab) -> Number (command group) ->Number Format ->General B. Press Ctrl +Shift + ~ C. Both a) and b) D. None of the above
Both a) and b)
How are some icons hidden in Icon Set conditional formatting? A. By choosing "No Cell Icon" in the Edit the Rule Description box. B. By clicking on Conditional Formatting, followed by "icon hide". C. By clicking on New Rule in Conditional Formatting. D. By clicking on the "Clear Some Rules" button in Conditional Formatting.
By choosing "No Cell Icon" in the Edit the Rule Description box.
By default, Microsoft can search for clipart online using which 2 search engines? A. 1) Google Images, 2) Bing B. 1) Office.com Clip Art, 2) Google C. 1) Office.com Clip Art, 2) Bing D. 1) Office.com Clip Art, 2) Getty Images
C. 1) Office.com Clip Art, 2) Bing
To apply specific formatting to a sparkline, use the Sparkline Color or the ___________ commands. A. Sparkline Editor B. Sparkline Style C. Marker Color D. Sparkline Developer
C. Marker Color
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()
COUNT()
To calculate the cumulative interest paid between any two payment periods, use the _____ financial function. A. CUMIPMT() B. PPMT() C. PMT() D. CUMPRINC()
CUMIPMT()
charts_example Examine the attached workbook. How can you resize the chart title in Chart 1 of Sheet 1? A. Drag its corner B. Change text alignment C. Rotate D. Change font size
Change font size
Which of the following is TRUE regarding the HTML files that Excel creates? A. Excel cannot open an HTML file. B. Formulas and charts in an HTML file created from an Excel workbook can be edited. C. Changes to graphs and formulas cannot be made to an HTML file created from Excel. D. In the HTML files, formulas can be edited, but the charts appear as static graphic images.
Changes to graphs and formulas cannot be made to an HTML file created from Excel.
What command (within Conditional Formatting) is used to delete all the rules relevant to a particular group of cells? A. Alter Rulebook / edit B. Alt-F4 C. Clear Rules D. New Rule E. Clear All
Clear Rules
To gain access to the Table Tools / Design tab in the Excel Ribbon when working with tables, you should do the following: A. click anywhere on the table B. such a tab does not exist, the only tab you can get access to is: table format/design C. click on the home tab on the ribbon, and check the appropriate box to make it appear D. The table tools/design tab does not provide any useful commands, so it is pointless trying to access it
Click anywhere on the table
How do you set up a data validation (using the data validation dialog box) that restricts data entry to a text of a specified length? A. Click the Settings tab (in the Data Validation box). In the Allow box, select 'date'. In the Data box, select 'greater than'. B. Click the Settings tab (in the Data Validation box). In the Allow box, select 'Text Length', 'equal to', and specify the length. C. Click the Settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select 'between'. D. Home (tab) -> Editing (group) -> Find & Select -> Data Validation
Click the Settings tab (in the Data Validation box). In the Allow box, select 'Text Length', 'equal to', and specify the length.
What is the purpose of the function TIMEVALUE()? A. Returns the serial number of a specified day, hour and minute B. Provides a $ value for time based on an Excel formula C. Returns the serial number of the current date and time D. 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
In Excel in order to avoid incomplete calculation errors and to ensure that formulas are fully calculated, you would press _________ . A. Ctrl + Alt + Space +F9 B. Ctrl + Alt + Shift + F9 C. Ctrl + Alt + Fn + F9 D. Ctrl + Alt + Shift + F10
Ctrl + Alt + Shift + F9
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
Ctrl + H
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
Ctrl + N
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
Ctrl+L
Please refer to the attached image. The sparkline displayed in cell F6 gets the data from cells A6 through E6 and displays a Win/Loss bar chart that shows only whether the year had a gain or a loss. In which year was there a loss, according to the Win/Loss bar chart? A. 2010 B. 2011 C. 2012 D. 2013
D. 2013
When you right-click a graphic object and choose Size and Properties from shortcut menu, Format Shape pane opens. If you expand the Properties section which of the following choices you get? A. Move and Size with cells B. Move But Don't Size with cells C. Don't Move or Size with Cells D. All of the options provided are correct
D. All of the options provided are correct
Which of the following is a type of Sparkline in Excel? A. Line B. Column C. Win/Loss D. All of the options provided are correct
D. All of the options provided are correct
How can you access the dialog box for Pivot Table options? A. PivotTable Tools (tab) ->Data B. PivotTable Tools-> Analyze (tab) -> Pivot Table (command group) -> Options C. Right Click on a cell within the pivot table and choose "PivotTable Options" D. Both b and c
D. Both b and c
When customizing a sparkline, which of the following is NOT one of the options? A. Change the width or height of a cell. B. Insert a Sparkline into merged cells. C. Change the Sparkline type. D. Convert the Sparkline into a table.
D. Convert the Sparkline into a table.
If there are negative values in your data, you can emphasize this by showing a(n) __________ axis in your sparkline. A. Blank B. Vertical C. Diagonal D. Horizontal
D. Horizontal
Which of the following is NOT one of the options for highlighting certain data points in a Sparkline? A. High point B. Low point C. First point D. Middle point
D. Middle point
Which of the following is NOT a summary function for Pivot tables? A. StdDev B. Varp C. Var D. Regression
D. Regression
In a Pivot Table, the names of the fields for the report come from ________. A. an associated pivot chart B. the worksheet names C. both A & B D. none of the answers provided is correct
D. none of the answers provided is correct
worksheet_file1.xlsx worksheet_file2.xlsx Please refer to the attached workbooks. The worksheet in "worksheet_file1.xlsx" is split into 4 panes (shown in "worksheet_file2.xlsx"). What cell must be selected prior to applying the split function in "worksheet_file1.xlsx" so that it looks exactly like what you see "worksheet_file2.xlsx"? A. C6 B. C7 C. D7 D. D6
D7
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? A. Data (tab) -> Connections (group) -> Properties B. Data (tab) -> Connections C. Data (tab) -> Connections (group) -> Connections D. Data (tab) -> Connections (group) -> Refresh
Data (tab) -> Connections (group) -> Properties
Which is one of the ways to refresh a query used to import data from external data source using the query wizard? A. Data (tab) -> Connections (group) -> Properties B. Data (tab) -> Connections (group) -> Refresh All C. Refresh D. Data (tab) -> Connections (group) -> All
Data (tab) -> Connections (group) -> Refresh All
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? A. 100 is a whole number B. Data validation settings require the salary value to be greater than the hours worked value, 120. C. Data validation settings require the salary value should be less than the hours worked value, 120. D. 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.
Which of the following is a VALID sequence of options for selecting a different chart layout? A. Design (from Chart Tools) --> Type group B. Design (from Chart Tools) --> Chart Styles C. Insert --> Charts --> Other Charts D. Design (from Chart Tools) --> Chart Layouts
Design (from Chart Tools) --> Chart Layouts
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 does the following code do: &[Pages] ? A. Displays the page number B. Displays the total number of pages to be printed C. Displays the link to the first page D. Displays your university's website page
Displays the total number of pages to be printed
charts_example.xlsx 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
Either of the previous options (resetting through the menu or the right-click options work).
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? A. Currency formatting is not applied to the new cell B. Excel applies currency formatting to the new value in that column cell C. A special type of formatting other than currency formatting is applied to the new value D. Currency formatting is temporarily applied to the new value (but only persists until the workbook is closed)
Excel applies currency formatting to the new value in that column cell.
Which older version of Excel file can Excel 2016 NO LONGER open? A. Excel can open all of these files B. Excel 4 C. Excel 97 D. Excel 2000
Excel can open all of these files
Excel uses column headers to create labels for each _____ on data entry form. A. Record B. Data Form C. Row D. Field
Field
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
File, Save As
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 starts with 'T' for 'City' column. A. From the Column to Filter list, select City. In the right panel, select 'equals' from the first drop-down list and then select 'Tucson' from the second drop-down list B. 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 C. From the Column to Filter list, select City. In the right panel, select 'ends with' from the first drop-down list and then type 'T' in the second drop-down list box D. 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 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
external_ex1.xlsx Please 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 lesser than 30000 for 'Salary' column. A. 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. B. 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 '3.0' from the second drop-down list. C. From the Column to Filter list, select Name. In the right panel, select 'equal' from the first drop-down list and type 'm' in the second drop-down list box. D. From the Column to Filter list, select Salary. In the right panel, select 'equals' from the first drop-down list and then select '30000.0' from the second drop-down list.
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.
Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet? A. HYPERLINK() B. SUM() C. INDEX() D. MATCH()
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? A. Home B. Review C. Insert D. Formulas
Home
Which of the following statements is TRUE? A. The Analysis Toolpak lets you generate random numbers and histograms only, hence is not very useful B. You use the FREQUENCY() function in Excel to find Fourier wave frequencies. C. The main difference between SUMIF() and COUNTIF() is that the former is used with multiple criteria, but the latter (i.e., COUNTIF()) only allows a single criteria. D. If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet
If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet
When (or why) does Excel draw arrows to indicate an error source? A. For finding dependencies between two images pasted in different worksheets B. In Tracing Error Values C. Error-checking the sheet Background D. Because Functions Must Have Arrows
In Tracing Error Values
In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram? A. Filename (to import Data from Access) B. Ignore duplicates vs. Permit Duplicates C. Simple vs Compound Interest D. Input Range and Bin Range
Input Range and Bin Range
When a column is sorted within a table, which of the following is true about the drop-down list in the header row? A. the header row background fill color changes to a darker shade B. It displays a different graphic to remind you that the table is sorted by the column C. there is no change to the header for that column before or after sorting D. the header row (for that column) is now disabled and until the sort is cleared, no further actions are permitted.
It displays a different graphic to remind you that the table is sorted by that column
How does the Excel search/replace function work for dates? A. It search a cell for any feasible date format including numbers and text dates, and then does a replace B. It searches for a date as displayed in the Formula bar, and then does a replace C. It can find dates, but not replace them D. It cannot find dates
It searches for a date as displayed in the Formula bar, and then does a replace
Which of the following is the MOST compelling motivation for using HLOOKUP()? A. It improves the formatting of your data and makes style-usage possible B. It usually reduces duplicate data storage C. Pie charts work better if the data is generated using HLOOKUP() formulas D. HLOOKUP() is superior in design to VLOOKUP()
It usually reduces duplicate data storage
Which keyboard shortcuts can be used to move the active cell one column to the left? A. Left arrow or Shift + Tab B. Left arrow or Tab C. Left arrow or Ctrl + Tab D. Left arrow or Alt + Tab
Left arrow or Shift + Tab
The HYPERLINK function syntax has two arguments. Which argument has the jump text or numeric value that is displayed in the cell? A. Link_location B. Friendly_name C. Name D. File
Link_location
To display the Total Row in a Table: choose Table Tools / Design -> Table Style Options (command group) and put a check mark next to Total Row. Selecting a cell in the Total Row and clicking on the drop-down arrow that appears in that cell will NOT allow you to select which of the following summary formulas? A. Average B. Count C. Sum D. Mode
Mode
tables_duplicate_rows.xlsx For this question, please download the attached Excel workbook. 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? A. yes, because excel can see that the dates in the two cells are the same value B. no, because dates that are a duplicate cannot be removed C. no, because excel does not let you remove duplicates when you only have 2 rows in a table (removing a row makes it a 1-row table, which is not really a table) D. yes, because the duplicate values are determined by the value displayed in the cell and not necessarily by the value stored in the cell.
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.
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. A. One-Way B. Two-Way C. Three-Way D. None of the above
One-way
By default, Excel prints worksheets in ___________ orientation A. Landscape B. Portait C. Angular D. 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)? A. Principal * (1 + periodic rate) ^ number of periods B. Principal * (1 + periodic rate) ^ 10 C. Principal * (1 + 2) ^ number of periods D. Principal * (periodic rate) ^ number of periods
Principal * (1 + periodic rate) ^ number of periods
What is the primary purpose of "Protected View?" A. Protect you from malware B. Prevent your peers from accidentally editing data C. Make it impossible for people the change your data unless they have a password D. Encrypt your spreadsheet data
Protect you from malware
The _____________ function is used to return a specified number of letters or characters from the end of a text string. A. RIGHT() B. FIRST() C. STRING() D. BEGIN()
RIGHT()
With regards to Depreciation Calculation functions in Excel, which of the following are true about the function SYD()? A. 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. B. It refers to the Visual Deprecation Barchart Method, and uses a bar chart to display the falling amounts of asset values. C. This method of calculation depreciation uses random numbers to fairly distribute depreciation over the lifetime of an asset. D. None of the above.
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.
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? A. Review (tab) -> Comments (command group) -> Next B. Review (tab) -> Comments (command group) -> Previous C. Home (tab) -> Navigate (command group) -> Next Comment D. Page Layout (tab) -. Search (command group) -> Jump to Comment
Review (tab) -> Comments (command group) -> Next
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
Ribbon
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. A. FIND_AND_REPLACE() B. SUBSTITUTE() C. FIND() D. SEARCH()
SUBSTITUTE()
The last step in the Query Wizard, "Finish", lets you do which of the following? A. Save a query so that you can reuse it B. Determine the order of sorting the output C. Choose hyperlinks that will appear for the query D. Decide whether the query will use relative or absolute cell references
Save a query so that you can reuse it
Please examine the attached workbook. Give the data in Sheet1, which of the following steps should be followed to obtain the data in Sheet2? A. Select cells A1 through C4 in Sheet 1 -> Copy -> Go to Sheet2 -> Paste -> Formulas B. Select cells A1 through C4 in Sheet1 -> Copy -> Transpose -> Go to Sheet2 -> Paste C. It is only possible if done manually D. Select cells A1 through C4 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose
Select cells A1 through C4 in Sheet 1 -> Copy -> Go to Sheet2 -> Paste -> Transpose
To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________. A. Ctrl+Alt B. Shift+F2 C. F5 D. Ctrl+A
Shift+F2
Which of the following is a shortcut key used to calculate only the formulas in the active worksheet? A. Shift + F9 B. all of the choices provided are valid C. F9 D. Ctrl + Alt + F9
Shift+F9
To create a single file web page, what file format does a user have to select from the Save as Type drop-down list? A. XML data B. Single File Web Page C. Web Page D. Excel Binary Workbook
Single File Web Page
Example_6.xlsx Please see the attached workbook. Row 5 is hidden and contains a word in cell A5. What is the result of using the spell checker when the word is hidden? A. The spell check will not find a misspelled word because the cell is hidden. B. Spell checker will be able to check the spelling of the word because the contents of hidden rows and columns are checked. C. The result is that the word is spelled correctly. D. A dialog box will appear, stating that an error occurred while performing spell check
Spell checker will be able to check the spelling of the word because the contents of hidden rows and columns are checked.
What function removes leading and trailing spaces from a cell? A. TRIM B. CUT C. COMPACT D. CLEAN
TRIM
When importing a text file into Excel, the _______ helps specify how the data will be imported. A. Text Import Wizard B. External Data Wizard C. CSV Wizard D. Text Preview Panel
Text Import Wizard
If the formula =ROWS(Asset) * COLUMNS(Asset) that is used to calculate the number of cells in a range named Asset doesn't work, what does it imply? A. The range Asset consists of non-contiguous cells B. The formula syntax is incorrect, e.g., it should read =columns (asset) * Rows (asset) C. The range Asset is a rectangular range of contiguous cells D. Some cells in the range are empty
The range Asset consists of non-contiguous cells
The "Go to Special" dialog box in Excel allows a user to select all of the following EXCEPT: A. Cells that contain comments B. Blank cells C. All cells that differ from the active cell in a selected column D. 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
themes_styles_formats.xlsx 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 A. The theme is applied to Sheet1 only B. The theme is applied to all the sheets in the workbook C. The theme is applied to Sheet 2 only D. The theme is applied to Sheet3 (or the last worksheet in the workbook) only
The theme is applied to all 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? 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 contains budget multiples by some number. For example, a cell contains the formula: "=budget*0.25" D. Only cells with the text "budget constraint" is counted, but a cell with "Previous budget estimates" is not counted
The word "budget" can appear anywhere within the cell.
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
Wide
You can use a data Form to add, find, and edit rows in a range or table. Is this correct? A. yes, all these are valid form operations B. incorrect, as adding rows is not supported. C. false, data forms work only with tables, not with ranges D. no, data forms work only with ranges, not with tables
Yes, all these are valid form operations
cells_sales.xlsx 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 pervious versions of Excel D. Yes, the formula range will be adjusted automatically
Yes, the formula range will be adjusted automatically
Which of the following does not hold true for Watch Windows? A. You select the cells that you want to watch, from the Formulas (tab) --> Formula Auditing --> click Show Watch Window B. You can see all the formulas in a worksheet and print them for later reference C. It helps you to keep an eye on a cell and its formula while you work, even when the cells are out of view D. 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
What characters are NOT allowed in sheet names? A. [] B. , C. All options are correct D. !
[]
Which of the following best describes what a Sparkline is? A. a mini-chart that is displayed in a single cell B. a blinking line to separate two cells C. a highlighted cell D. a dynamic line chart where the lines have metallic color effects
a mini-chart that is displayed in a single cell
Using the Normal view for a workbook, which of the following actions can be performed for Page Breaks? A. insert page breaks B. Remove page breaks C. reset all page breaks D. all of the above
all of the above
Which of the following file formats can be opened using Excel 2016? A. all the other options are correct B. XLS C. XLT D. XLA
all the other options are correct
If there are two or more name / value pairs passed in a URL query string, separate them with the following character: _____________ . A. comma (,) B. semi-colon (;) C. ampersand (&) D. colon (:)
ampersand (&)
While creating a parameterized web query the question mark (?) is used to _______. A. being the URL query string B. delimit the name from the value C. delimit each name/value pair D. delimit the parameter from the prompt
being the URL query string
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 number C. column letter D. row number
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 numeric or date format C. this function is used primarily for blank or empty cells D. a cell with alphanumeric characters
content other than text (e.g., a date or a number) or a blank cell
Which of the following does the function =NOW() return? A. current date B. current time C. current date and time D. nothing, it is not valid Excel function
current date and time
When you enter "FALSE" for the range_lookup parameter in HLOOKUP, you are telling Excel that you want to find a(n) ________ match A. exact B. different C. dissimilar D. orderly
exact
A vertical page break is always inserted on the _____ of the selected column in a worksheet A. left B. right C. top D. bottom
left
A chart in an Excel worksheet provides a visual representation of which of the following? A. numeric values B. forumlae C. text values D. objects
numeric values
You select a cell with data "120" in an Excel worksheet and press the "Delete" key on your keyboard. Which of the following holds TRUE? A. only the contents of the cell are removed B. the contents and formatting of the cell are removed C. only the formatting cell is removed D. no changes occur
only the contents of the cell are removed
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 rang 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
to prevent blank cells in the copied range from overwriting existing data
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? A. use Freeze Panes B. This cannot be done in Excel 2013 C. use the Split or Hide functions D. you must print a copy of the worksheet to do this
use Freeze Panes
What form of LOOKUP is used when you have a large list of values to look up or when the values may change over time? A. array form B. static form C. vector form D. dynamic form
vector form
