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%