Final (7-12 Problem Sets)

How do you format a valid date serial number as a date ? a.) Home (tab) ->Number (command group) ->Number Format -> $ b.) Home (tab) -> Number (command group) ->Number Format -> Text c.) Home (tab) -> Number (command group) -> Number Format -> Fraction d.) None of the options provided is correct

None of the options provided is correct

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

Which of the following error codes indicates that a formula refers to a cell that is invalid? This type of error can occur if a referenced cell is deleted from the worksheet. a.) #REF! b.) #VALUE! c.) #N/A d.) #NAME?

a.) #REF!

Please refer to the attached workbook. What is the total amount of Sales for the east region of the Arizona (AZ) state in the month of January (Jan)? a.) 1100 b.) 1500 c.) 2100 d.) 8400

a.) 1100

Please refer to the attached screen capture. Which of the following formulas will correctly calculate simple interest? a.) =D5*D6*D7 b.) =D5*D6/D7 c.) =D5+D6/D5*D7 d.) D5+D6

a.) =D5*D6*D7

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? a.) =HYPERLINK("[hyperlink_examples.xlsx]A4",A4) b.) =HYPERLINK("[hyperlink_examples.xlsx]A3",A3) c.) =HYPERLINK("[hyperlink_examples.xlsx]A2",A2) d.) Web saving/pages

a.) =HYPERLINK("[hyperlink_examples.xlsx]A4",A4)

If you place the link target in cell C1 on the same worksheet, how will you use an absolute reference to that cell as the link_location in the HYPERLINK formulas? a.) =HYPERLINK($C$1) b.) =HYPERLINK(C1$) c.) =HYPERLINK(C1$C1*) d.) =HYPERLINK("","C1$C1*")

a.) =HYPERLINK($C$1)

Which of the following formulas can you use to change a text string of military time (e.g., 21:12) in cell Y3 to a standard value of time? a.) =TIMEVALUE(LEFT(Y3,2)&":"&RIGHT(Y3,2)) b.) =TIMEVALUE(RIGHT(Y3,2)&":"LEFT(Y3,2)) c.) =DAYVALUE(LEFT(Y3,2)&":"&RIGHT(Y3,2)) b.) =NONMILITARY(Y3)

a.) =TIMEVALUE(LEFT(Y3,2)&":"&RIGHT(Y3,2))

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

a.) All the other options are correct

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

______ is a feature which allows you to apply a number of preset Photoshop-like effects to an image. a.) Artistic effects b.) Clip-Art c.) Pivot Table d.) High-Low-Close charts

a.) Artistic effects

In Excel, in order to calculate the cumulative principal paid between any two payment periods, what function will you use? a.) CUMPRINC() b.) CUMIPMT() c.) PPMT() d.) PMT()

a.) CUMPRINC()

To view calculated fields and calculated items in a pivot table, which of the following can you do? a.) Choose PivotTable Tools -> Analyze -> Calculations -> Field, Items & Sets -> List Formulas b.) Choose PivotChart Tools -> Analyze -> Calculations -> Field, Items & Sets -> List Formulas c.) Choose Pivot -> Analyze -> Calculations d.) Choose Analyze -> Calculations -> Field, Items & Sets -> List Formulas -> PivotTable Tools

a.) Choose PivotTable Tools -> Analyze -> Calculations -> Field, Items & Sets -> List Formulas

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? a.) Circles appear around cells that contain incorrect entries. b.) squares appear around cells that contain incorrect entries. c.) incorrect entries are italicized. d.) correct entries are italicized.

a.) Circles appear around cells that contain incorrect entries.

Please see the attached workbook, Sheet1. The Format Painter tool functionality is best described by which of the following? a.) Copy the format of one cell to another b.) Copy the font style properties (only) of one cell to another c.) Copy the font color of one cell to another, but not other font properties d.) Copy the background of one cell to another

a.) Copy the format of one cell to another

When entering / editing an Array formula, press _________ to enter the formula (or Excel does not recognize the formula as an Array formula). a.) Ctrl + Shift + Enter b.) Alt + Ctrl + Delete c.) Alt + Shift + Enter d.) None of the options provided is correct

a.) Ctrl + Shift + Enter

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

a.) Data (tab) -> Connections (group) -> Properties

Please refer to the attached workbook. Column A has first and last names. The goal is to extract the first name in column B as shown in cell B1. With B2 as the active cell, which of the following is the correct set of steps to achieve that? a.) Data --> Data Tools --> Flash Fill b.) Formulas --> Function Library --> Text --> Search c.) Data --> Data Tools --> Data Validation --> List d.) Formulas --> Function Library --> Lookup & Reference --> Match

a.) Data --> Data Tools --> Flash Fill

If you are trying to display trends with a Sparkline, you may have to work with irregular time periods in the underlying data. To properly do so, you can click on the Group (command group), then click on "Axis", and choose _________ to format the shape of the chart. a.) Date Axis Type b.) Date Value Type c.) Character Axis Type d.) Find and Replace

a.) Date Axis Type

True or false: Excel can only import HTML files from your local computer. a.) False, Excel can import HTML files from the local computer or the web. b.) False, Excel can only import HTML files from the web. c.) True, Excel can only import HTML files from your local computer. d.) False, Excel cannot import HTML files.

a.) False, Excel can import HTML files from the local computer or the web.

How do you change the calculation mode while dealing with a calculation intensive data table? a.) Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Except For Data Tables b.) Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic c.) Formulas (tab) -> Calculation (group) -> Calculation Options -> Manual d.) Press Ctrl+C, followed by: Alt+P

a.) Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Except For Data Tables

Which of the followings steps are to be followed in order to identify the cell that contains an error? a.) Formulas -> Formula Auditing -> Error Checking -> Trace Error b.) Formulas -> Formula Auditing -> Remove Arrows c.) Formulas -> Formula Auditing -> Show Formulas d.) Formulas -> Formula Auditing -> Evaluate Formula

a.) Formulas -> Formula Auditing -> Error Checking -> Trace Error

The HYPERLINK function syntax has two arguments. Which argument has the jump text or numeric value that is displayed in the cell? a.) Friendly_name b.) Link_location c.) Name d.) File

a.) Friendly_name

Please use the attached workbook as an external data source. The workbook has a table called 'Employee'. When you import data, after selecting all the columns from the table using the query wizard (Data(tab) -> Get External Data (group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that have a value greater than 21 for 'Age' column. a.) From the Column to Filter list, select Age. In the right panel, select 'is greater than' from the first drop-down list and then select '21.0' from the second drop-down list b.) From the Column to Filter list, select Age. In the right panel, select 'is lesser than' from the first drop-down list and then select '21.0' from the second drop-down list c.) From the Column to Filter list, select Name. In the right panel, select 'is lesser than' from the first drop-down list and then select '21.0' from the second drop-down list 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

a.) From the Column to Filter list, select Age. In the right panel, select 'is greater than' from the first drop-down list and then select '21.0' from the second drop-down list

Please refer to the attached workbook. In Table 1, the Pivot table shows which of the following: a.) Gender is a Column Label b.) Region is a Column Labe c.) State is used as a Report Filter d.) State is used as a Report Filter, Region as a Column Label

a.) Gender is a Column Label

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()

a.) HYPERLINK()

You can control how a sparkline handles empty cells in a range (and thus how the sparkline is displayed) by using the ______________ dialog box. a.) Hidden & Empty Cell Settings b.) Marker Color Settings c.) Sparkline Empty Style settings d.) Sparkline Color Settings

a.) Hidden & Empty Cell Settings

Please see the attached workbook. Which of the following options can you choose with a Sparkline if you want to highlight the budget for the third year? a.) Highlight the: Low point b.) Highlight the: High point c.) Highlight the: First point d.) Hightlight the: Negative Points

a.) Highlight the: Low point

Please see the image below. Which of the following conditional formatting styles is used in creating rules for the "Defects" column? a.) Icon sets b.) 3-Color Scale c..) Data Bars d.) Defects bars

a.) Icon sets

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

The basic formula for computing simple interest is: a.) Interest = Principal * Rate * Term b.) Interest = Principal * Rate c.) Interest = Principal * Term d.) Interest = Simple * Term * Principal * Normal

a.) Interest = Principal * Rate * Term

When using the MATCH function, the match_type "-1" is best described by which of the following options? a.) It will find the smallest value that is greater than or equal to the "lookup_value" b.) It will find the first value that is equal to "lookup_value" c.) It will find the largest value that is less than or equal to "lookup_value" d.) It returns a random value from within a table or range

a.) It will find the smallest value that is greater than or equal to the "lookup_value"

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

How do you clear/remove a Print Area that has already been defined on the worksheet? a.) On the Page Layout tab, in the Page Setup group, click Print Area, and then click Clear Print Area. b.) On the Page Layout tab, in the Page Setup group, click Print Area, and then click Custom Print Area. c.) On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area. d.) None of the above

a.) On the Page Layout tab, in the Page Setup group, click Print Area, and then click Clear Print Area.

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

a.) Principal * (1 + periodic rate) ^ number of periods

______________ replaces part of text string that appears in a specific location within a string. Hint: This function can be used when you know the character location of the text to be replaced. a.) REPLACE() b.) SEARCH_AND_REPLACE() c.) FINDREPLACE() d.) SEARCHREPLACE()

a.) REPLACE()

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

After you create a Sparkline or group of them, you can change the type by choosing the Sparkline and clicking one of the three icons in the ________________. a.) Sparkline Tools -> Design (tab) -> Type (group) b.) Sparkline Tools -> Design (tab) -> Names c.) Sparkline Tools -> Design (tab) -> Other Items d.) Design (tab) -> Type (group) -> Sparkline Tools

a.) Sparkline Tools -> Design (tab) -> Type (group)

Unlike charts on an Excel worksheet, _________ are not objects. Instead these are tiny charts in the background of a cell. a.) Sparklines' b.) Trendlines c.) SmallCharts d.) Inline Charts

a.) Sparklines

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

a.) Text Import Wizard

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.

Which formula would you use if you want to accept only text that begins with a specific character 'a' for a given cell 'A1'? a.) Use a formula: =LEFT(A1)="a" b.) Use a formula: =RIGHT(A1)='a' c.) Use a formula: =UPPER(A1)='A' d.) None of the options provided is valid

a.) Use a formula: =LEFT(A1)="a"

Which function will you use to calculate depreciation for multiple periods in Excel? a.) VDB() b.) DEPREC() c.) CONCATENATE() d.) MULTIDEPREC()

a.) VDB()

From the Settings tab of the Data Validation dialog box, you can specify a wide variety of data validation criteria. Many options are available from the Allow drop-down list. Which of the following options would you select if you want the user to enter a number between the range of 1-250? a.) Whole Number b.) Any value c.) Decimal d.) List

a.) Whole Number

Is it possible to change the direction of bars in DATA BARS conditional formatting? a.) Yes b.) No c.) Feature not available in Excel 2016 d.) Maybe

a.) Yes

Excel has many Shapes to choose from, but sometimes the Shape you need is not in the gallery. In such a case, you may modify one of the existing shapes using a few techniques. One of them is 'Group Multiple Shapes'. What is its purpose / how can it be used? a.) You can create the Shape you need by combining two or more Shapes and then grouping them. b.) When you select a Shape, it displays a small green dot. Click and drag this dot to group Shapes c.) Both A and B are correct d.) None of the above are correct

a.) You can create the Shape you need by combining two or more Shapes and then grouping them.

In Step 2 of the Query Wizard, you specify which data rows should be included in your query output. How is the criteria entered? a.) a criterion for each column b.) you drag and drop the rows you want c.) you use a checkbox to pick criteria d.) criteria are automatically copied from cell $A$1 of your current workbook sheet

a.) a criterion for each column

Please see the attached workbook. The formula in cell C4 is "=B4 + (B4 * $C$1)". Formulas in range C4:C7 use a / an _________ reference to cell C1. a.) absolute b.) columnar c.) mixed d.) none of the options provided is correct

a.) absolute

What is the use of the following data validation formula: =A2>A1 ? a.) accepts values for cell A2 that are greater than the value of cell A1 b.) accepts values for cell A2 that are lesser than the value of cell A1 c.) accepts values for cell A2 that are lesser than the value of cell A5 d.) accepts values for cell A2 that are lesser than the value of cell A8

a.) accepts values for cell A2 that are greater than the value of cell A1

While creating a parameterized web query the question mark (?) is used to ______. a.) begin the URL query string b.) delimit the name from the value c.) delimit each name/value pair d.) delimit the parameter from the prompt

a.) begin the URL query string

Please refer to the attached workbook. The ______ , ________ and ________fields are in the Column, Rows and Values sections respectively. a.) branch, date and amount b.) branch, weekday and amount c.) customer, weekday and amount d.) AcctType, date and amount

a.) branch, date and amount

Please see the attached workbook. What is the URL Query String for the extract in the attached worksheet? a.) http://www.imdb.com/ b.) http://www.imdb.com/careers c.) http://www.imdb.com/latest d.) http://blackboard.eller.arizona.edu/

a.) http://www.imdb.com/

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

a.) one-way

The function of value filter in a pivot table is to ___________. a.) read the data and select those rows with cells that match your criteria b.) modify the data c.) write the data with a different value d.) help you insert the correct pivot chart

a.) read the data and select those rows with cells that match your criteria

The formula '=NOW()-TODAY()' displays current time as a ___________ without a related date. a.) serial number b.) military style formatted time c.) combination of hours and minutes d.) None of the answers provided is correct

a.) serial number

Which of the following options provides correct syntax for the VLOOKUP function? a.) VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) b.) VLOOKUP(lookup_value, table_array, row_index_num, range_lookup) c.) Either of the two previous choices will work d.) None of the options provided is valid

a.)VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Please see the attached workbook. What formula (in cell B3) would return the tax rate for the income in cell B2? a.) =VLOOKUP(B2, E1:J3, 3) b.) =HLOOKUP(B2, E1:J3, 3) c.) =VLOOKUP(B3, D2:F7, 3) d.) None of the other options are correct

b.) =HLOOKUP(B2, E1:J3, 3)

When using the formula "=WORKDAY(DATE(2014,1,4),10)", what does the output show? a.) A month that is ten months before January 4, 2014 b.) A date that is ten working days from (after) January 4, 2014 c.) A month that is ten months from (after) January 4, 2014 d.) A date that is ten working days before January 4, 2014

b.) A date that is ten working days from (after) January 4, 2014

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

Please see the attached workbook. In the Student Rank table, in cell F19, type Rachel instead of Dennis. Why do you receive an error? a.) Cell F19 can only take a male name b.) Cell F19 can only take text that begins with the letter 'd'. c.) Cell F19 should only take numbers. d.) Cell F19 has no restrictions.

b.) Cell F19 can only take text that begins with the letter 'd'.

_____________ 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). a.) Simple b.) Compound c.) Future value d.) None of the above

b.) Compound

When a header or footer section is activated, you can insert any of several element codes from the Header & Footer Elements group. Each button inserts a code into the selected section. What is the purpose of the following code: &[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 web page

b.) Displays the total number of pages to be printed

Where do you find the command to add a pivot table on your worksheet? a.) Excel Ribbon: Insert (tab) ->Charts b.) Excel Ribbon: Insert (tab) ->Tables c.) Excel Ribbon: Insert (tab) ->Illustrations d.)Excel Ribbon: Insert (tab) ->Links

b.) Excel Ribbon: Insert (tab) ->Tables

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

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

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

b.) In Tracing Error Values

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()

b.) It usually reduces duplicate data storage

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.

What is the correct syntax for the PMT function, if we are only considering the required parameters/arguments for the function? (Note: "nper" means Number of Payments) a.) PMT(rate,nper) b.) PMT(rate,nper,pv) c.) PPMT(rate,per,nper,pv,fv) d.) PMT(required)

b.) PMT(rate,nper,pv)

________ can be used to check whether all of the columns fit on one page before printing an Excel worksheet. a.) Quick Print b.) Print Preview c.) Page Layout d.) A Pivot Table

b.) Print Preview

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()

b.) SUBSTITUTE()

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

b.) Single File Web Page

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)

The __________ function will be used as part of a formula to calculate how many days old you are today. a.) YESTERDAY() b.) TODAY() c.) BIRTHDAY() d.) HOWOLDAMI()

b.) TODAY()

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.

Please see the attached workbook. What does '3' in the expression =VLOOKUP("John",A2:D13,3,FALSE) represent? a.) Third row b.) Third column c.) Three values in any order d.) Three values of data to return

b.) Third column

Excel formatting tools are used to achieve which of the following? a.) To create programming code in Java/C++ b.) To make the end result attractive and more functional c.) To distract readers from inaccuracies in text and figures d.) The primary use of formatting is to create Excel Pie Charts e.) All of the options listed are valid

b.) To make the end result attractive and more functional

Please see the attached screen capture. What you see is an example of which of the following Excel features: ______________ a.) Pie chart b.) Word Art c.) Smart Art d.) Sparkline Chart

b.) Word Art

The formula: =CHAR(CODE("A")+32) returns which of the following? a.) 97 b.) a c.) C d.) B

b.) a

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

Input messages and error alerts (for data validation) appear only when ___. a.) a user enters data in the cell by copying or filling b.) data is typed directly into the cells c.) a formula in the cell calculates a result that is not valid d.) a macro enters invalid data in the cell

b.) data is typed directly into the cells

If you click outside of the layout area (of a PivotTable report), the PivotTable Field List ________. a.) increases in size b.) goes away c.) decreases in size d.) turns blue

b.) goes away

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

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

The range_lookup parameter in VLOOKUP and HLOOKUP function is ________ a.) mandatory b.) optional c.) compulsory d.) fixed

b.) optional

While using web queries Rich text formatting is typically used if we wish to ______. a.) preserve cell formulas b.) reproduce web page data in a format close to that of the original c.) return none of the formatting d.) return hyperlink formatting

b.) reproduce web page data in a format close to that of the original

A Sparkline is best described as a _____. a.) small table b.) small chart typically displayed in a single cell c.) chart displayed in multiple cells d.) chart that displays multiple series of data

b.) small chart typically displayed in a single cell

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

Please see the attached workbook. The worksheet contains a table of student data (starting in row 13) named as "Students". If you were to use a lookup formula to get the exact value for the 'Last Name' field in row 3, which of the following formulas would you use? a.)=VLOOKUP(C6,Students,2,FALSE) b.)=VLOOKUP(C3,Students,2,FALSE) c.)=VLOOKUP(C3,Students,3,FALSE) d.) =VLOOKUP(C3,Students,3,FALSE)

b.)=VLOOKUP(C3,Students,2,FALSE)

Please refer to the attached workbook. What is the total new deposit amount on Sep - 08 in North County branch? a.) 139196 b.) 62787 c.) 196188 d.) 77674

c.) 196188

Please see the attached workbook. What value of tax rate is returned when the entered income is $22,566? a.) 36% b.) 31% c.) 28% d.) 15%

c.) 28%

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)

c.) =1 / (24 * 60)

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)

c.) =DATE(YEAR(A1) + 1, MONTH(A1), DAY(A1))

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()

c.) A combination of FIND() and MID()

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" a.) Because value of last year and this year is same b.) Because value in this year is greater than value in last year c.) Because data in column C is missing or is equal to zero d.) None of the options provided is correct

c.) Because data in column C is missing or is equal to zero

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

c.) Both a) and b)

This function combines its arguments into a single text string. It is an alternative to using the ampersand operator. a.) COMBINE() b.) SINGLESTRING() c.) CONCATENATE() d.) DAYSADD()

c.) CONCATENATE()

Which of these dates will Excel store as a text string rather than a serial number? a.) June 23, 2014 b.) 23-June-14 c.) June 23 2014 d.) 05/05/14

c.) June 23 2014

The __________ function can be used to find the count of characters in a word or text string. a.) COUNTCHARS() b.) NUMLETTERS() c.) LEN() d.) NUMCHARS()

c.) LEN()

The _________ function can return a specified number of characters beginning at any position within a string. a.) INSIDE() b.) LEFT() c.) MID() d.) EXTRACT()

c.) MID()

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

When do you get a "#NAME?" error value? a.) The wrong argument is used b.) The wrong operand is used c.) Text in a formula is not enclosed in quotation marks d.) A nonnumeric argument is passed to a function when a numeric argument is expected

c.) Text in a formula is not enclosed in quotation marks

When working with data returned from a Query Wizard, refreshing a query occasionally produces undesired results. If this occurs, you can use the _____ button to reverse the change. In other words to "un-refresh" the data. a.) Unrefresh b.) Cycle c.) Undo d.) Cancel

c.) Undo

How can a manual Page Break be removed from a worksheet? a.) By pressing the keyboard shortcut of: Ctrl+1 b.) By inserting a Background Image to cover the Page break c.) Using the: Reset All Page Breaks command d.) All of the above

c.) Using the: Reset All Page Breaks command

If one attempts to enter a date that falls outside of the range of supported dates, Excel will interpret it as _______ a.) a numeric value b.) a special character c.) a text entry d.) a currency value

c.) a text entry

What is referred to as the "0th" day of next month while working with dates and times in Excel? a.) first day of next month b.) last day of next month c.) last day of current month d.) first day of current month

c.) last day of current month

What best describes the connection between a Pivot Table and a Pivot Chart? a.) one-way link (updates from the Pivot Table are reflected in the chart, but not vice versa) b.) one-way link (updates from the Pivot Chart are reflected in the Pivot Table, but not vice versa) c.) two-way link (updates made in the Pivot Chart are reflected in the PivotTable) d.)broken link

c.) two-way link (updates made in the Pivot Chart are reflected in the PivotTable)

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

c.) vector form

Which of the following provides correct syntax for the MATCH function? a.) MATCH(column-name) b.)MATCH(lookup_value,lookup_vector,result_vector) c.)MATCH(lookup_value,lookup_array,match_type) d.)MATCH(lookup_value,match_type)

c.)MATCH(lookup_value,lookup_array,match_type)

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

In Excel, the maximum length of a Web query URL is _______ characters. a.) 124 b.) 512 c.) 48 d.) 255

d.) 255

Please see the attached workbook. If you have to use a formula to retrieve the age of the student from the given array of data, which of the following formulas will you use? a.) =INDEX(J4:L8,3,2) b.) =INDEX(J4:L8,2,3) c.) =INDEX(J4:K8,1,3) d.) =INDEX(J4:L8,3,3)

d.) =INDEX(J4:L8,3,3)

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) a.) =ROUND(B2*24,0)/24 b.) =ROUND(B2*60,0)/60 c.) =ROUND(B2*30,0)/30 d.) =ROUND(B2*1440,0)/1440

d.) =ROUND(B2*1440,0)/1440

To eliminate redundant blank space from the start or end of a string (e.g., " Tucson "), which Excel function will you use? a.) =NOBLANKS() b.) =SPLICE() c.) =PROPER() d.) =TRIM()

d.) =TRIM()

Which of the following statements is FALSE? a.)You can create a pivot table and a pivot chart using the same data b.) Slicers can be used with Pivot Charts c.) All Excel charting features are available in a pivot chart d.) A pivot chart is always based on a pivot table, i.e., changes to the pivot chart must be made by changing the underlying table

d.) A pivot chart is always based on a pivot table, i.e., changes to the pivot chart must be made by changing the underlying table

#NAME? error occurs under which of the following conditions? a.) Formula contains an undefined range or cell name b.) Formula contains text that Excel interprets as an undefined name c.) Formula uses a worksheet function that's defined in an add-in or the add-in is not installed d.) All of the options provided are correct

d.) All of the options provided are correct

Data validation on a cell or range can do which of the following? a.) Calculate what is allowed based on the content of another cell b.) Use a formula to calculate what is allowed c.) Restrict entry of time values (to be within a specified time frame) d.) All of the options provided are valid

d.) All of the options provided are valid

With regards to Depreciation Calculation functions in Excel, which of the following are true about the function DB()? a.) Computes depreciation at a fixed rate. b.) Its arguments are: Cost, Salvage, Life, Period, [Month] c.) It stores values in a Pivot Table, while other depreciation functions do not d.) Both A and B

d.) Both A and B

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

Please see the attached workbook, Sheet2. Which of the following do Theme effects operate on? a.) SmartArt, but not Shapes b.) Shapes, but not Charts c.) Charts, but not SmartArt d.) Charts, Shapes

d.) Charts, Shapes

When a header or footer section is activated, you can insert any of several element codes from the Header & Footer Elements group. Each button inserts a code into the selected section. What is the purpose of the following code: &[Tab] ? a.) Shows tab characters using the "->tab" code in your printout b.) Displays the workbook name c.) Displays the workbook's complete filepath d.) Displays the sheet's name

d.) Displays the sheet's name

Microsoft Query provides you with a number of features while importing data. Which of the following is NOT an option you can specify or customize through Microsoft Query? a.) Tables to select from an external database b.) Criteria to select specific records from a table c.) Sorting of results from a table d.) Graphing of data from tables

d.) Graphing of data from tables

Which of the following functions could be used if you want to display "NOT FOUND" when you lookup for an item that is not part of the lookup_vector? a.) SUM b.) MATCH c.) INDEX d.) IFERROR

d.) IFERROR

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 one of the four quick margin settings for adjusting margins? a.) Normal b.) Wide c.) Narrow d.) No margins

d.) No margins

Which of the following is FALSE about Pivot Table Slicers? a.) Slicers can be moved and resized. b.) The effects of filtering by a particular slicer can be removed. c.) To use a Slicer to filter data in a Pivot Table, you can click one of the slicer value buttons. d.) None of the answers provided is false

d.) None of the answers provided is false

When you use the HLOOKUP() function, which of the following parameters represents the row number from which the matching value will be returned? a.) range lookup b.) lookup_value c.) row_care_param d.) None of the options provided is correct

d.) None of the options provided is correct

Which of the following is NOT one of the methods for 'Conditional Formatting' in Excel? a.) Data Bars b.) Color Scales c.) Icon Sets d.) Pie Chart

d.) Pie Chart

Which of the following is true with regards to row and column titles and headers? a.) They are the same. b.) At the top of each page, row and column titles contain information, such as the worksheet name, date, or page number. c.) Headers describe the field names in a table or list. d.) Row and column titles describe field names in a table or list while headers appear at the top of each page.

d.) Row and column titles describe field names in a table or list while headers appear at the top of each page.

In the Share tab of Backstage View, which of the options is available? a.) Enable Google Indexing b.) Collaborate in Real-time c.) Publish to Word Services d.) Send using E-mail

d.) Send using E-mail

While formatting Shapes in Excel, which command is used to change the overall style of a Shape? a.) Change style b.) WordArt Styles group c.) Stylesheet d.) Shape Styles

d.) Shape Styles

Which of the following is NOT one of the worksheet views offered by Excel? a.) Normal b.) Page Layout c.) Page Break Preview d.) Simplified Layout

d.) Simplified Layout

Which of the following is NOT one of the places to adjust print settings? a.) The Print screen in Backstage View, displayed when you choose File>Print. b.) The Page Layout tab of the Ribbon. c.) The Page Setup dialog box d.) The Alignment dialog box

d.) The Alignment dialog box

To create Sparklines, users do NOT need to enter or specify which of the following: _____. a.) Data to be depicted in the Sparkline b.) Location for the Sparkline to be displayed c.) Select one of the Sparkline types d.) The count and sum of cells

d.) The count and sum of 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

d.) The second to last cell in a selected column that contains data or formatting

There are four boxes/areas below the PivotTable Field List. Which of the following is NOT one of these four boxes? a.) Filters b.) Rows c.) Columns d.) Tools

d.) Tools

Which is NOT a feasible option to prevent cell contents from being printed? a.) Hide rows or columns. b.) Hide cells or ranges. c.) Mask an area of a worksheet by covering it with a rectangle Shape. d.) Use the custom number format that consists of two colons.

d.) Use the custom number format that consists of two colons.

The #VALUE! error can occur in the following conditions EXCEPT for: a.) A function's argument is a range when it should be a single value b.) When a custom worksheet function is not calculated c.) A custom worksheet function tries to perform an operation which is not valid d.) You cut a cell and paste it to a cell that is referenced by other formulas

d.) You cut a cell and paste it to a cell that is referenced by other formulas

Please see the attached workbook. In the Student Rank table, notice that H15 and H19 have the same value. Since two students cannot have the same student ID, what formula would you use in the Data validation settings in this case to make sure duplicate entries are not allowed? a.)=COUNTIF(H13:H19)=1 b.)=COUNT($H$13:$H$19,H19) c.)=COUNTIF($H$13:$H$19)=1 d.)=COUNTIF($H$13:$H$19,H19)=1

d.)=COUNTIF($H$13:$H$19,H19)=1

How can you avoid the "#NAME?" error value? a.) If the formula uses a label in a formula, make sure labels are allowed b.) Enclose any text in double quotation marks c.) Make sure all range references in the formula use a colon d.)All of the options provided are correct

d.)All of the options provided are correct