MIS112 Final Exam
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 function(s) can be used to count error values in a range? -all of the provided choices are valid -ISERROR() -ISERR() -ISNA()
all of the provided choices are valid
When a range of cells is selected, which of the following occurs? -the selected cells are all highlighted in the same color -none of the cells is highlighted in any color -a box marks the boundary of the selected cells, but none of the cells are highlighted -all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded
all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded
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? -Format Cells -Quick Access toolbar -Autofill - =SUM() formula
autofill
Which of the following icons/buttons will you click to add or remove gridlines in a chart? -Chart Elements -Chart Styles -Chart filter -None of these
chart elements
What is the shortcut to open the Find and Replace dialog box with the "Replace" tab open? -Ctrl+F -Ctrl+A -Ctrl+H -Ctrl+FIND
ctrl + H
Which of the following is the keyboard shortcut that creates a new blank workbook in Excel? -Ctrl + N -Ctrl + E -Ctrl + W -Ctrl + B
ctrl + N
Which of the following procedures will NOT close your Excel workbook? -Ctrl + S -Ctrl + F4 -Ctrl + W -File -> Close
ctrl + S
Which of the following can be retrieved from a web page using web queries? -.gif image -contents of scripts -.jpeg images -data in a table
data in a table
Which of the following Excel tabs is NOT visible in a worksheet by default? -Developer -Review -Home -Insert
developer
By default, if you hide rows or columns that are used in a Sparkline graphic, the hidden data _____. -appears in the Sparkline -does not appear in the Sparkline -sometimes appears in the Sparkline -is changed to unhidden data
does not appear in the Sparkline
When you enter "FALSE" for the range_lookup parameter in HLOOKUP, you are telling Excel that you want to find a(n) ________ match -exact -different -dissimilar -orderly
exact
Which of the following command sequences will allow you to save a copy of your workbook with a different name? -Ctrl, A -Ribbon, Save -File, Save As -Data, Create Copy
file, save as
What does the Excel keyboard shortcut Ctrl+Alt+F9 do? -highlights error codes in the current workbook -calculates only the formulas in the active worksheet. Other worksheets in the same workbook aren't calculated. -forces a complete recalculation of all formulas in all open workbooks. -Ctrl+Alt+F9 will not do anything in Excel
forces a complete recalculation of all formulas in all open workbooks.
In Excel, what does the MODE function in a range return? -most frequently occurring value in a range -mean of the values in the range -most frequently occurring text entry in a range -middle value of the range
most frequently occurring value in a range
Does deleting a cell by pressing the Delete key also delete the conditional formatting on the cell? -No -Yes, but only if the data is text -Yes -No, it only deletes the cell's style
no
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
Which of the following is an object that contains the actual data in the chart? -Plot area -Pie area -Bar area -Column area
plot area
When do you use the following sequence of commands: PivotTable Tools -> Analyze -> PivotTable -> Options -> Generate GetPivot Data? -prevent Excel from using GETPIVOTDATA function -make use of GETPIVOTDATA function -add GETPIVOTDATA function to the pivot table -enable GETPIVOTDATA function for the pivot table
prevent Excel from using GETPIVOTDATA function
What is the primary purpose of "Protected View?" -Protect you from malware -Prevent your peers from accidentally editing data -Make it impossible for people to change your data unless they have a password -Encrypt your spreadsheet data
protect you from malware
The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______. -range (and) constant -expression (and) criteria -constant (and) expression -range (and) criteria
range (and) criteria
What happens if you try to store a number greater than 15 digits in Excel ? -Replaces the digits exceeding 15 with '#' -Replace the digits exceeding 15 with the number zero -None of the (other) options provided is correct -Stores as is
replace the digits exceeding 15 with the number zero
Which of the following procedures will NOT save (nor give you an option to save) your Excel workbook? -Shift + S -Ctrl + S -(Excel ribbon tab) File -> Save -clicking the Close (X) button in the workbook's title bar
shift + s
A date filter in Pivot Tables is used to ____________. -return the date of the transaction -specify a time period for which you need the data -change the date of a transaction (e.g., a sale) -modify the date in your underlying table
specify a time period for which you need the data
Which icon (in data validation error alerts) is used to prevent users from entering invalid data into a cell? -Stop -Warning -Information -Prevent
stop
Please see the attached workbook. The formula in B1 which is copied down the column is "=TRIM(A1)=A1". The formula returns false for (B3) because the text in that cell contains? -leading space -trailing space -multiple spaces between 2 words -all of the options provided are correct
trailing space
Assume the "range_lookup" parameter in an HLOOKUP function is FALSE. If an exact match is not found, what value is returned? -#REF! -Excel returns a "No value was found" error message -#N/A -An approximate match is returned
#N/A
Which of the following error codes indicates that a problem with a value exists? For example, a negative number was specified by the user where a positive number was expected. -#NUM! -#REF! -#N/A -#NAME?
#NUM!
Please see the attached workbook. If you have to lookup the supplier of the item C43 in the given array of data, which of the following formulas will you use? - =LOOKUP(B1,H2:H7,K2:K7) - =LOOKUP(B1,H2:H7,I2:I7) - "=LOOKUP(B1,H2:H7,J2:J7) - =LOOKUP(B1,L2:L7,K2:K7)
=LOOKUP(B1,H2:H7,I2:I7)
Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet? - = NOW() - = DATE() - = TIME() - = TODAY()
=NOW()
Which of the following formulas rounds a time in cell B2 to the closest minute? (You can assume relevant cells have Excel's Time formatting) - =ROUND(B2*24,0)/24 - =ROUND(B2*60,0)/60 - =ROUND(B2*30,0)/30 - =ROUND(B2*1440,0)/1440
=ROUND(B2*1440,0)/1440
Please see the attached workbook. Why does the #DIV/0! error occur if the formula in cell D2 (also copied to the cells below it) is: "=(C2 - B2) / C2" -Because value of last year and this year is same -Because value in this year is greater than value in last year -Because data in column C is missing or is equal to zero -None of the options provided is correct
Because data in column C is missing or is equal to zero
With regards to Depreciation Calculation functions in Excel, which of the following are true about the function DB()? -Computes depreciation at a fixed rate. -Its arguments are: Cost, Salvage, Life, Period, [Month] -It stores values in a Pivot Table, while other depreciation functions do not -Both A and B
Both A and B
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. -MIN() -MAX() -COUNT() -SUM()
COUNT()
How can you resize the chart title in Chart 1 of Sheet 1? -Drag its corner -Change text alignment -Rotate -Change font size
Change font size
Which of the following is TRUE regarding the HTML files that Excel creates? -Excel cannot open an HTML file. -Formulas and charts in an HTML file created from an Excel workbook can be edited. -Changes to graphs and formulas cannot be made to an HTML file created from Excel. -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.
The Pivot Table Field List shows which of the following? -Columns from the source data only -Columns and Rows from the source data -Columns from the source data and Groups defined in the Pivot Table -Names of available charts
Columns from the source data and Groups defined in the Pivot Table
_____________ interest refers to an interest calculation where interest is credited to the investment balance (and the investment then further earns interest on the interest credited). -Simple -Compound -Future value -None of the above
Compound
You can create Sparklines for rows of data that you add later by using the _________ on an adjacent cell that contains a sparkline. -Format Painter -Fill handle -Spray Can -Smart Art
Fill handle
Assuming you have a report that contains multiple products from XYZ company. What Pivot table feature would you use to see the data of any one product? -Filter -Find & Replace -Conditional Deletion -High Low Close charts
Filter
Which function returns the contents of a cell at the intersection of a specified row and column from a range? -INDEX() -MATCH() -LOOKUP() -IFCELL()
INDEX()
Which of the following is NOT a formatting option offered through the Mini Toolbar? (Hint: for more information on the mini-toolbar, you can right-click on an Excel cell) - Comma Style - Format Painter - Left Align - Center Align
Left Align
The _________ function can return a specified number of characters beginning at any position within a string. -INSIDE() -LEFT() -MID() -EXTRACT()
MID()
Which 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
Which of the following file formats does NOT use a bitmap representation or format? (Bitmap images usually look good at their original size but lose clarity if you increase the size) -SVG -BMP -PNG -TIFF
SVG
Please see the attached workbook. What does '3' in the expression =VLOOKUP("John",A2:D13,3,FALSE) represent? -Third row -Third column -Three values in any order -Three values of data to return
Third column
Please see the attached workbook. Which LOOKUP function would be used in order to get the Tax Rate (in cell B3) for the entered income (in B2)? -HLOOKUP -VLOOKUP -TAXLOOKUP -None of the options provided is valid
VLOOKUP
By default, what is the extension used when you save a workbook in Excel 2016? -.xlsx -.xslx -.xls -.xsl
.xlsx
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? - 0.00388865177938274 - 3.83333333333333 - 2.5 - 0.12
0.00388865177938274
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? -2010 -2011 -2012 -2013
2013
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
A single cell in Excel can hold up to approximately ________________ characters. -5000 -10000 -50000 -32000
32000
What is the result of the following expressions (formulas), respectively? = (10 - 6) * 10 and = 10 - 6 * 10 - 40 and -50 - -50 and 40 - 40 and 50 - 50 and 40
40 and -50
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? - 4500 - 500000 - 4750 - 6750
4500
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? -6750 -4750 -4500 -500000
4500
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? - 5?2 - 5??2 - 52? - *2
5??2
Examine the attached Excel file. When the formula in "D2" is copied to the cells below it, cell "D3" will contain what formula? - =(B3*C3)*$B$7 - =(B3*C3)*B8 - =($B$3*$C$3)*$B$7 - =(B3*C3)*B7
=(B3*C3)*$B$7
The formula to calculate the value of one minute, that can be added to a date serial number is: - =1 / (24 * 60 * 60) - =1 / (60 * 60) - =1 / (24 * 60) - =1 / (24 * 24)
=1 / (24 * 60)
To calculate the percentage of the total widget sales made by the top 5 employees, which of the following formulas would you enter? - C20/F8 - =SUM(C15:C19) - =C20/F8 - =PERCENTSALES(F22)
=C20/F8
Which formula can be used to find: How many songs were sung by singers whose last names contain exactly six letters? - =COUNTIF(B2:B23,"6*?") - =COUNTIF(B2:B23,"{6?}") - =COUNTIF(B2:B23,"??") - =COUNTIF(B2:B23,"??????")
=COUNTIF(B2:B23,"??????")
Which formula can be used to find: How many songs were sung by singers whose names begin with S? - =COUNTIF(B2:B23,"S*") - =COUNTIF(B2:B23,"*S") - =COUNTIF(B2:B23,"S") - =COUNTIF(B2:B23,"S?")
=COUNTIF(B2:B23,"S*")
Which formula can be used to find: How many songs were longer than the average length of all songs on the list? - =COUNTIF(D2:D23,"<"&AVERAGE(D2:D23)) - =COUNTIF(D2:D23,">"&AVG(D2:D23)) - =COUNTIF(D2:D23,">"&AVERAGE(D2:D23)) - =COUNTIF(D2:D23,">"AVERAGE(D2:D23))
=COUNTIF(D2:D23,">"&AVERAGE(D2:D23))
Please refer to the attached screen capture. Which of the following formulas will correctly calculate simple interest? - =D5*D6*D7 - =D5*D6/D7 - =D5+D6/D5*D7 - D5+D6
=D5*D6*D7
Which of the following is the correct formula for converting the hexadecimal argument "3BC" to its decimal equivalent? - =HEX2DEC("3BC") - =HEXTODEC("3BC") - =HEX2DEC(3BC) - =HEXTODEC(3BC)
=HEX2DEC("3BC")
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". - =HYPERLINK("[Example.xlsx]E2", "Quarterly Profit Report") - =HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits", "Quarterly Profit Report") - =HYPERLINK("[Annual Report.docx]QrtlyProfits", "Quarterly Profit Report") - =HYPERLINK("QrtlyProfits", "Quarterly Profit Report")
=HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits", "Quarterly Profit Report")
Please see the attached workbook. Which formula is used to provide a link to cell A4 in the same workbook with link text same as text present in A4? - =HYPERLINK("[hyperlink_examples.xlsx]A4",A4) - =HYPERLINK("[hyperlink_examples.xlsx]A3",A3) - =HYPERLINK("[hyperlink_examples.xlsx]A2",A2) - Web saving/pages
=HYPERLINK("[hyperlink_examples.xlsx]A4",A4)
Which of the following IF() functions evaluate as being TRUE? - =IF("B10>100","B10+10","B10+8") - =IF(B10>200,"true","false") - IF() functions can only be used with Product data - =IF(B10>100,"true","false")
=IF(B10>100,"true","false")
Which of the following formulas can be used to determine the quarter in the calendar for a date in cell P5? (e.g., quarter "1") - =ROUNDUP(YEAR(P5)/4,0) - =DATE(YEAR(P5), MONTH(P5)+1,0) - =ROUNDUP(MONTH(P5)/3,0) - =QUARTER(P5)
=ROUNDUP(MONTH(P5)/3,0)
Which formula can be used to find: How many songs were sung by either Eminem or Spears? - =SUM(COUNTIF(B2:B23,{"Eminem","Spears"})) - =COUNTIF(B2:B23,{"Eminem","Spears"}) - =IF(SUMIF(B2:B23,{"Eminem"}, D2:D23),SUMIF(B2:B23,{"Spears"},D2:D23)) - =COUNTIFS(B2:B23,{"Eminem","Spears"})
=SUM(COUNTIF(B2:B23,{"Eminem","Spears"}))
A pivot chart is which of the following? -A bar chart that can pivot to a pie chart form with a single click -A graphical representation of a data summarized in a pivot table -A chart that is joined to a pivot table in a 4-way link -A chart that cannot be used with a slicer
A graphical representation of a data summarized in a pivot table
In an Excel worksheet, what column name comes directly after Column Z? -AA -AZ -ZA -ZZ
AA
If you copy a formula down a column, and need a cell reference to stay the same (for example, you don't want B2 to change to B3), what type of cell reference do you need? (Example: Use of F4 key) -Absolute -Relative -Frozen -Automated
Absolute
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
Suppose you have a range of cells with numbers and a few blank entries. If you sort the range in ascending order (smallest to largest), where will the blank cells be located after sorting? - At the beginning - At the end - Excel cannot sort ranges with blank values - Excel leaves the blank values in their original location
At the end
Which of the following functions can be used to count specific types of errors? -COUNTA -COUNTIF -COUNTBLANK -COUNT
COUNTIF
Which of the following is the default font type and font size in Excel 2016? - Calibri, 11 point - Arial, 12 point - Times New Roman, 10 point - Cambria, 14 point
Calibri, 11 point
In order to sort the 'Salesperson' column in a descending order, which of the following steps is correct? - 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 - Select the entire Salesperson Column, then Click on the Review tab (in the Excel ribbon), followed by clicking on Group and "Sort" - The Salesperson column cannot be sorted - Convert the data into a Table, right click on the Table header and select the A-to-Z sort option
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
⓶ What command (within Conditional Formatting, Manage Rules) is used to delete a specific rule from a worksheet? -Delete Rule -Clear Rule -Alter Rule -New Rule
Delete Rule
Which of the following is a VALID sequence of options for selecting a different chart layout? -Design (from Chart Tools) --> Type group -Design (from Chart Tools) --> Chart Styles -Insert --> Charts --> Other Charts -Design (from Chart Tools) --> Chart Layouts
Design (from Chart Tools) --> Chart Layouts
In Excel, the set of worksheet functions related to dates can be accessed via: -Formulas (tab) -> Function Library (command group) -> Date & Time -Home (tab) -> Number (command group) -> Number Format -> Date Functions -Pressing: Ctrl + ; (semicolon) -Home (tab) -> Number (command group) -> Number Format -> Special
Formulas (tab) -> Function Library (command group) -> Date & Time
In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram? - Filename (to import Data from Access) - Ignore duplicates vs. Permit duplicates - Simple vs. Compound Interest - Input Range and Bin Range
Input Range and Bin Range
_________ is used to insert a nicely formatted mathematical equation as a graphic object. -Insert (tab) --> Illustrations (group) --> Math -Insert (tab) --> Smart Art --> Text box -Insert (tab) --> Sparklines --> Sparkline type -Insert (tab) --> Symbols (group) --> Equation
Insert (tab) --> Symbols (group) --> Equation
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
To add one or more Slicers to a worksheet, start by selecting any cell in a pivot table and then choose ______________. -Insert (tab) -> Filters (group) -> Slicer. The Insert Slicers dialog box appears. -Insert (tab) -> Slicer (group) -> Filters. The Insert Slicers dialog box appears. -Right Click on it. The Insert Slicers menu option can be selected. -All of the answers provided are correct
Insert (tab) -> Filters (group) -> Slicer. The Insert Slicers dialog box appears.
Which of the following occurs if you select an embedded chart of a sheet and then click on "File" --> "Print"? -It prints only the chart on that sheet -It prints the whole worksheet including the chart -It asks if you want to print all charts on all worksheets in that Excel workbook -It prints all worksheets with data linked to the current chart
It prints only the chart on that sheet
When choosing a page orientation, ____ is one of the two valid options. -Landscape -Narrow -Wide -Tall
Landscape
If cell A1 contained the text MR. JOHN Q. PUBLIC, what would the formula (=PROPER(A1)) return? -Mr. John Q. Public -John Q. Public -Mr. John q. Public -Public, John Q.
Mr. John Q. Public
Which function returns the number of payment periods for a loan, given the loan's amount, interest rate, and periodic payment amount? -CUMIPMT() -NPER() -PMT() -CUMPRINC()
NPER()
Which of the following is NOT one of the four quick margin settings for adjusting margins? -Normal -Wide -Narrow -No margins
No margins
Which of the following entries does Excel automatically recognize as a date (using the U.S. settings)? -June 18 2014 -Jun-18 2014 -Jun-18/20 -None of the answers provided is correct
None of the answers provided is correct
Which of the following is FALSE about Pivot Table Slicers? -Slicers can be moved and resized. -The effects of filtering by a particular slicer can be removed. -To use a Slicer to filter data in a Pivot Table, you can click one of the slicer value buttons. -None of the answers provided is false
None of the answers provided is false
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
Please refer to the attached workbook. Which branch has the maximum amount for 'IRA' AcctType? -Central -North County -Westside -None of the answers provided is correct
North County
How can a Total Row be added to a table? - Once you have a table, you can click the "Table Tools / Design" tab. Then, under "Table Style Options" check the box for "Total Row." - This cannot be done anymore, it is an unsupported feature in Excel 2013 - The most effective way is to add a new row, and manually add total options - Adding a Total Row requires knowledge of Visual Basic which is not covered in this course
Once you have a table, you can click the "Table Tools / Design" tab. Then, under "Table Style Options" check the box for "Total Row."
Which Worksheet View option displays the worksheet, shows where the page breaks occur and lets you adjust the page breaks by dragging page-break indicators? -Normal -Page Layout -Page Break Preview -Simplified Layout
Page Break Preview
The _____________ function is used to return a specified number of letters or characters from the end of a text string. -RIGHT() -FIRST() -STRING() -BEGIN()
RIGHT()
Adding comments to a cell is available through which tab of the Excel ribbon? - Home - Formulas - Data - Review
Review
Suppose your worksheet has a comment in cell A1 and a comment in cell B5. Your active cell is B1. Which command can you use to navigate to the comment in cell B5? -Review (tab) -> Comments (command group) -> Next -Review (tab) -> Comments (command group) -> Previous -Home (tab) -> Navigate (command group) -> Next Comment -Page Layout (tab) -> Search (command group) -> Jump To Comment
Review (tab) -> Comments (command group) -> Next
What does selecting the option "Current region" within the: Go To Special Dialog Box (Home (tab) -> Editing (command group) -> Find & Select) do? -Selects the entire array -Selects all graphic objects on the worksheet -Selects a rectangular range of cells around the active cell -Selects all empty cells
Selects a rectangular range of cells around the active cell
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 ______. - #VALUE! - Sheet1!#REF! - #DELETEDROWS - Sheet1!#INVALIDREF!
Sheet1!#REF!
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
Which of the following is NOT a type of ANOVA (Analysis of Variance) performed using tools in the Analysis ToolPak? -Single-factor -Single-factor without replication -Two-factor With Replication -Two-factor Without Replication
Single-factor without replication
Which of the following is NOT one of the types of Sparkline graphics? -Line -Column -Win/Loss -Slicer
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
Which of the following is TRUE about sorting a table? - Sorting a table is not possible. - Words cannot be sorted, while numbers can be sorted. - Sorting a table re-arranges the rows based on the contents of a particular column. - Sorting the rows based on the background or text color of the data is not possible.
Sorting a table re-arranges the rows based on the contents of a particular column.
Which command helps you to override the automatic behavior and control max and min value for Sparklines? -Sparkline tools-> Design-> Group -> Line -Sparkline tools-> Design-> Group ->Tables -Sparkline tools-> Design-> Group -> Axis -Sparkline tools-> Design-> Group -> Chart
Sparkline tools-> Design-> Group -> Axis
Microsoft Office Excel can automatically generate a data form (which allows for convenient data entry, among other things) for your range or _______________. - Chart - Field - Table - Record
Table
To add rows or columns within the table, right-click and choose Insert from the shortcut menu. Which of the following commands is NOT displayed on this menu? - Table Columns to the Left - Table Columns to the Right - Table Rows Above - Table Column Above
Table Column Above
When importing a text file into Excel, the _____________ helps specify how the data will be imported. -Text Import Wizard -External Data Wizard -CSV Wizard -Text Preview Panel
Text Import Wizard
⓵ What part(s) of a table can be selected? - An entire column, but not an entire table - An entire row, but not an entire column - The entire table or an entire row only - The entire table, an entire row, or an entire column from a table can be selected
The entire table, an entire row, or an entire column from a table can be selected
If 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.
Consider the following formula: =SUMIFS(Inventory_Cost, Category,"=Beverages", Suppliers,"*4*"). Which of the following statements is TRUE? -The formula syntax has an error -The formula looks for a supplier with an ID of: *4*, or a product category of Beverages -The formula is used to generate a Histogram -Two criteria are checked for this formula to include a value in the total: a) the product category must be "Beverages", and b) the supplier must contain a "4"
Two criteria are checked for this formula to include a value in the total: a) the product category must be "Beverages", and b) the supplier must contain a "4"
Which formula would you use if you want to accept only text that begins with a specific character 'a' for a given cell 'A1'? -Use a formula: =LEFT(A1)="a" -Use a formula: =RIGHT(A1)='a' -Use a formula: =UPPER(A1)='A' -None of the options provided is valid
Use a formula: =LEFT(A1)="a"
What is an advantage of using formulas instead of Autofill to create a series of dates? (e.g., for a work-week schedule) -When the first date is changed, the others update automatically -When the first date is changed, the others are not affected -A formula cannot be used to create a series of dates -Autofill uses a formula, so these two options are equivalent
When the first date is changed, the others update automatically
When a row of data is very ____ and requires repeated horizontal scrolling, consider using a data form to add, edit, find, and delete rows - Wide - Narrow - Transformed - Empty
Wide
What characters are NOT allowed in sheet names? - , - ! - All options are correct - [ ]
[ ]
How can you insert a header or footer into the workbook? -Click File > Print > Page Setup and edit the header and footer. -Click Insert (tab) --> Header and Footer (group) --> Edit -In the Page Layout view, click on the placeholder for the header or footer, and add the information. -a and c
a and c
What does the small red triangle on the upper right corner of cell B1 denote? - a comment exists for that cell - the cell has an error - wrong value / formula in the cell - the cell references a missing value
a comment exists for that cell
You enter "(500)" into an unformatted cell in an Excel worksheet (without quotation marks). Which of the following will be generated by your entry? -a negative number -a positive number -a time -a date
a negative number
Which of the following statements is true? -A workbook can have at most 3 worksheets. -A worksheet can have many workbooks. -A workbook can have many worksheets. -A worksheet can have at most 3 workbooks.
a workbook can have many worksheets
The Data(tab) -> Data Tools (group)-> Data Validation drop-down list contains an item named Circle Invalid Data. What will happen if you select this item? -circles appear around cells that contain incorrect entries. -squares appear around cells that contain incorrect entries. -incorrect entries are italicized. -correct entries are italicized.
circles appear around cells that contain incorrect entries.
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 ______. -remove -clear all -shrink -dissolve
clear all
A bar chart is essentially which of the following chart types rotated 90 degrees clockwise? -column -line -area -surface
column
In Excel, the ISNONTEXT() function returns TRUE if its argument refers to which of the following? -content other than text (e.g., a date or a number) or a blank cell -a cell containing data in a numeric or date format -this function is used primarily for blank or empty cells -a cell with alphanumeric characters
content other than text (e.g., a date or a number) or a blank cell
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
What is the shortcut to open the Find and Replace dialog box with the "Find" tab open? -Ctrl+H -Ctrl+F -Ctrl+A -Ctrl+Alt+F
ctrl + F
Which of the following formatting types has been applied to "3419.25041" to make it appear as "$3,419.25" ? -General -Number -Currency -Special
currency
When you specify which rows should be included in your query (selection criteria), you are satisfying which step of the Query Wizard? -choose columns -filter data -sort order -generating lookup
filter data
After pressing "Alt + H" while in an open Excel worksheet, a number of Key Tips are displayed. Which ribbon tab do these Key Tips relate to? -Home -Review -Insert -Formulas
home
While importing data from a web page into Excel, creating a refreshable Web Query is used to ______. -keep data static -keep data dynamic or updateable -convert data into a query -repeat the query
keep data dynamic or updateable
Which keyboard shortcuts can be used to move the active cell one column to the left? -Left arrow or Shift + Tab -Left arrow or Tab -Left arrow or Ctrl + Tab -Left arrow or Alt + Tab
left arrow or shift + tab
Fill in the blanks: A cell in an Excel worksheet is identified by its address, which consists of a column ___ and row ___. -letter, number -number, letter -value, range -range, value
letter, number
What Excel element is shown at the top with all the options to edit? -File button -Quick Access toolbar -Scrollbar -Ribbon
ribbon
In Excel, the Ctrl + Spacebar (keyboard shortcut) is used to ______. -select a row -select a column -select a range -select multiple rows or columns
select a column
Which of the following methods is a VALID way to get into cell edit mode? -Clicking the cell one time enables you to edit the cell contents -Selecting the cell that you want to edit, and then clicking in the Formula bar -Pressing F9, which enables you to edit the cell contents directly in the cell -Pressing F4 allows you to the edit the cell contents
selecting the cell that you want to edit, and then clicking in the formula bar
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? -Synchronous Scrolling -Toggle -None of the (other) options provided is correct -Side View
synchronous scrolling
Which option provided in the View ribbon tab is best to compare two open Excel workbooks simultaneously in Excel 2016? -View Side by Side -Freeze Panes -Arrange All -Split
view side by side
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
Within the "Number" tab, how many categories are available for displaying numbers? -12 -8 -10 -14
12
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? -Add a comment to the sales figure cell, and explain to your assistant what you want him to do -Delete the sales figure for Monday (seeing the the blank cell will make things clear to the assistant) -Set a fill color of dark red for the sales figure cell -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
Add a comment to the sales figure cell, and explain to your assistant what you want him to do
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
Using the Normal view for a workbook, which of the following actions can be performed for Page Breaks? -Insert Page Breaks -Remove Page Breaks -Reset All Page Breaks -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 statements about Trace Precedents are correct? -It can help point out if a formula has been applied to a wrong cell range -You can access Trace Precedents on the Formula Auditing toolbar -By clicking Trace Precedents again, you can identify the next level of cells that provide data to the active cell -All of the options provided are correct
All of the options provided are correct
Data validation on a cell or range can do which of the following? -Calculate what is allowed based on the content of another cell -Use a formula to calculate what is allowed -Restrict entry of time values (to be within a specified time frame) -All of the options provided are valid
All of the options provided are valid
Assume you have a shape (e.g., a rectangle) on your worksheet. Then, you can use the: "Drawing Tools -> Format -> WordArt Styles group" for which of the following? -Convert the shape into a chart -Change the shape from a rectangle to a circle -Alter text formatting within the shape -Insert a Sparkline graphic within a shape
Alter text formatting within the shape
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 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 lesser than 30000 for 'Salary' column. -From the Column to Filter list, select Salary. In the right panel, select 'is less than' from the first drop-down list and then select '30000.0' from the second drop-down list -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 -From the Column to Filter list, select Name. In the right panel, select 'equals' from the first drop-down list and then type 'm' in the second drop-down list box -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
Please see the image below. Which of the following conditional formatting styles is used in creating rules for the "Defects" column? -Icon sets -3-Color Scale -Data Bars -Defects bars
Icon sets
Which of the following statements is true? They all relate to the formula: =IF( OR(Category="Pasta",Category="Soups"), Inventory_Cost,0 ) - This type of IF() functions with an embedded OR() is no longer supported in Excel 2013. - Such IF() statements are too complicated for use in business. They are only used by NASA. - In this formula, the second statement in the OR (i.e., checking for the category ="Soups") is only checked/evaluated if the first part (i.e., checking if category ="Pasta") evaluates to FALSE - Using a nested IF() in a formula is not recommended because the formula result is different based on the IQ level of the user.
In this formula, the second statement in the OR (i.e., checking for the category ="Soups") is only checked/evaluated if the first part (i.e., checking if category ="Pasta") evaluates to FALSE
The formula =COUNTIF(range, "*budget*") will count the number of cells in the specified range containing the word "budget" in certain positions within the text. Which of the following best describes the cells counted? - The word "budget" must appear in the cells surrounded by asterisks (i.e., the * character). - The word "budget" can appear anywhere within the cell. - It looks specifically for cells containing budget multiplied by some number. For example, a cell containing the formula: "=budget*0.25". - 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.
The word "budget" can appear anywhere within the cell.
Please refer to the attached workbook. What would you do if you want to filter records in the report so that you get information only for the state of AZ? -To filter the report, click the arrow next to Row Labels and check the 'AZ' option from the menu that appears. -To filter the report, click the arrow next to Row Labels and check the 'CA' option from the menu that appears. -To filter the report, click the arrow next to Row Labels and check the 'select all' option from the menu that appears. -None of the answers provided is valid
To filter the report, click the arrow next to Row Labels and check the 'AZ' option from the menu that appears.
Examine the attached workbook. What conditional formatting rule is applied in cells A1:A5? -Top/Bottom Rules -> Bottom 10% -Color Scales -Highlight Cell Rules -None of the options provided is correct
Top/Bottom Rules -> Bottom 10%
