MIS Lab Final Review (Practice Test), MIS Lab Final Review (PS 1-12), mis lab my problem sets, MIS 111 Lab Final Questions from Problem Sets, MIS 111 Problem Sets 1-12, MIS111L Final Study Guide Fall 2015, MIS 111L past problem sets, MIS111L Practice...

Lakukan tugas rumah & ujian kamu dengan baik sekarang menggunakan Quizwiz!

Which operator in Excel combines multiple range references into a single reference (e.g., if I want to sum up the values in non-contiguous cell-ranges across the worksheet)?

"," (comma)

The HLOOKUP function works like VLOOKUP function except that the lookup table is arranged ___________ instead of __________.

"horizontally, vertically"

For VLOOKUP() and HLOOKUP(), the lookup_value argument can include wildcard characters if the lookup_value argument is ______ and the range_lookup argument is ________

"text, false"

The lookup value can include wildcard characters if the lookup_value argument is ______ and range_lookup argument is ________

"text, false"

Please see the attached workbook. Using the formula =B2/C2 what error is displayed?

#DIV/0!

What are the Returning Formula Error Codes?

#DIV/0! #N/A #NAME? #NULL! #NUM! #REF! #VALUE

Assume the "range_lookup" parameter in an HLOOKUP function is FALSE. If an exact match is not found, what value is returned?

#N/A

The Excel function: ISERR() returns TRUE if the cell contains any error value except which of the following?

#N/A

------

#NAME? error occurs under which of the following conditions?

consider the phrase this is excel. which sequence of keyboard shortcuts result in the formatting used on the words this is excel in the earlier sentence

(Control + B) + (Control + 5)

Consider the phrase: "This is Excel". Which sequence of keyboard shortcuts result in the formatting used on the words "This is Excel" in the earlier sentence (within Excel, on a Windows PC)?

(Control+B)+(Control+5)

With a single IF() function, how many actions can you ask Excel to carry out?

(i.e., an action if the criteria is true, or an an action if the criteria is false)

Step 3 of the Query Wizard lets you specify how you want the ______.

**** find answer

Which of the following is the file format of the Excel 97-2003 workbook

.xls

What value should be given to the 'match_type' argument in the MATCH function if you want the MATCH function to find the first value exactly equal to lookup_value?

SERIAL #'S OF JANUARY 1ST, 2ND, 3RD,

1,2, &3

How do you create/copy a chart?

1. Make sure that your data is appropriate for a chart. 2. Select the range that contains your data. 3. Select the Insert tab and select a chart type from the Charts group. These icons display drop-down lists that display subtypes. Excel creates the chart and places it in the center of the window. 4. (Optional) Use the various tools and commands to change the look or layout of the chart or add or delete chart elements. To make an exact copy of an embedded chart on the same worksheet, click the chart's bor- der, press and hold the Ctrl key, and drag. Release the mouse button, and a new copy of the chart is created.

What is the correct order of steps to be followed when you use a query wizard?

1. choosing the columns, 2. filtering data, 3. sort order, and 4. finish

What are the minimum and maximum zoom ranges that can be displayed in an open Excel worksheet (respectively)?

10%, 400%

By default, Excel uses __________ font

11 point (pt) Calibri

By default, Excel uses which of the following fonts?

11 point (pt) Calibri

By default, Excel uses ____ font

11 point Calibri

by default excel uses ______ font

11 point calibri

=DATE(LEFT(A1,4), MID(A1, 5, 2), RIGHT(A1,2))

20130315 represents a date March 15, 2013. To convert this string to an actual date, which of the following formulas can be used? (Assume the data 20130315 is in cell A1)

Please see the attached workbook. The formula: =OFFSET(A1,MATCH("Mary",A2:A5,0),2) returns what value?

What is the maximum number of characters a worksheet name can contain?

Worksheet names can have up to ______ characters.

Worksheet names can have up to how many characters?

Worksheet names can have upto ______ characters.

worksheet names can have upto ___ characters

Please see the attached workbook. What tax rate value is returned when you perform a VLOOKUP with an entered income of $45,500?

31%

MAXIMUM # OF CHARACTERS IN A CELL

32,000

A single cell in Excel can hold up to approximately ________________ characters.

32000

What is the result of the following expressions (formulas), respectively? = (10 - 6) * 10 and = 10 - 6 * 10

40 and -50

Please see the attached workbook. The scaling factor used is different from the default. Use Print Preview to see its effect. How much is the scaling factor?

400%

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

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

what will be the result of this formula =if(a1*5%

4500

What does the following formula return? =SEARCH("m","Big Mama Thornton",1)

The Excel CODE function returns the character code for its argument. What does the following formula return? =CODE("B")

If you enter 9/19/2014 25:00:00, how will it be interpreted by Excel? (Note: assume a Short Date format)

9/20/2014 1:00:00am

The formula: =CHAR(CODE("A")+32) returns which of the following?

97 Correct Response a C B

When naming an Excel Worksheet you cannot use

: / \ [ ] ? *

Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet?

= NOW()

To display today's date in the format like "Today is Friday April 4, 2014", which formula should you use?

="Today is "&TEXT(TODAY(),"dddd, mmmm d, yyyy")

Entering today's data formula:

="Today is" & TEXT (TODAY(), "dddd,mmmm d, yyyy")

When you copy the formula in cell "C3" to "F7", the cell in "F7" contains which of the following formulas?

=$B7*F$2

The formula to calculate the value of one minute, that can be added to a date serial number is:

=1 / (24 * 60)

Value for one minute

=1/(24*60)=0.00069444

Please choose the formula that will calculate the future value of a $6,500 investment at 4.55 percent compounded continuously for 4 years.

=6500*EXP(4.55%*4)

Combining words from two cells into one cell:

=A1&A2

Which of the following shows the correct syntax for referencing cell "A2" in another worksheet?

=A1*Sheet2!A2

How can you determine the cell address of a value?

=ADDRESS(ROW(Data)+MATCH(Target,Data,0)-1,COLUMN(Data

Which of the following formulas returns the number of available days in the corresponding month for a date in cell B2?

=DAY(DATE(YEAR(B2), MONTH(B2)+1,0))

Examine the image in the question. In "C3," we have the supplier address "Tucson, AZ 85721" appearing in a single cell (the formula combines data from cells "E3," "F3," "G3"). Which of the following formulas will achieve this result?

=E3 &", " &F3 &" " &G3

Suppose you deposit $100 at the beginning of each month (for 12 months) into an account that pays 3.5 percent annual interest compounded monthly. Which formula calculates the future value of your series of deposits?

=FV(3.5%/12,12,-100,,1)

Please see the attached workbook. The result of an HLOOKUP function is "yellow." What is the formula used to obtain this result?

=HLOOKUP("Color",A1:B5,3,FALSE)

What formula (in cell B3) would return the tax rate for the income in cell B2?

=HLOOKUP(B2, E1:J3, 3)

What formula displays the value of B5 stored in a file named "example.xslx" in a different directory called "TEST" in the "C:\" drive?

=HYPERLINK("C:\TEST\example.xslx",B5)

To create hyperlink?

=HYPERLINK([example.com] QrtrlyProfits","Quarterly Profit Report")

You may use the attached spreadsheet, "IF_formulas.xlsx" to answering this question. Which of the following IF() functions evaluate as being TRUE?

=IF(B10>100,"true","false")

"if" true or false

=IFERROR (value, value if error)

Which of the following formulas, when entered in cell B3, will give you the CURRENCY of the country CHAD? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/Country.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq

=INDEX(I2:I239, MATCH("CHAD", D2:D239, 0))

The formula: =CHAR(CODE("A")+32) returns which of the following?

#NULL! errors

A #NULL! error occurs when a formula attempts to use an intersection of two ranges that don't actually intersect

invalid

A #REF! error occurs in Excel when a formula uses a / an ________ cell reference

quick

A -> Z (ascending sort) and Z-> A (descending sort) are both referred to as ________ sorting options. They are both available by clicking a single button in the Excel ribbon (Data tab).

Which of the following is NOT a reason to name a range of cells?

A named range (e.g., 'Order_Total'), works across operating systems (e.g., works on both Macs & Windows), while using the cell references (e.g., H2:H100) does not work on different operating systems

Cell

A single element in a worksheet that can hold a value, some text, or a formula is called a(n) ____?

TASK PANES

APPEAR AUTOMATIC IN RESPONSE TO SEVERAL COMMANDS. SIMILAR TO DIALOG BOXES BUT ARE KEPT VISIBLE FOR A LONG PERIOD OF TIME. EX: INSERTING PICTURES AND FUNCTIONS

Ctrl + Page Up

Activates previous sheet, if one exists

Ctrl + Page Down

Activates the next sheet, if one exists

If you want to remove chart labels, where do you find the options to do so within the Design tab (Chart Tools)?

Add Chart Element > Data Labels

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

What operators can you use in a formula?

Addition Subtraction Multiplication Division Exponentiation Concatenation Logical comparison (equal to) Logical comparison (greater than) Logical comparison (less than) Logical comparison (greater than or equal to) Logical comparison (less than or equal to)Logical comparison (not equal)

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

How do you group Pivot Table items manually?

After creating the pivot table, select the items to be grouped and then choose Options (tab) under PivotTable Tools and select Group (group) -> Group Selection

Data validation on a cell range can do what for the following?

All of the above

If you write a lookup formula to look up the text 'education', the formula considers which of the following a match?

All of the above

If you write lookup formula "education"?

All of the above

Which of the following is true about pivot tables?

All of the above

Which of the following is TRUE about Pivot tables?

All of the above are true

Which of the following is TRUE about pivot tables?

All of the above are true

Which of the following is TRUE about a Watermark in Excel?

All of the above statements are TRUE

Which of the following is TRUE about a Watermark in Excel?

All of the above statements are TRUE.

Which of the following file types can Excel import?

All of the file types

In context of the MATCH() function, which of the following is FALSE?

All of the options are false

Which of the following statements about Trace Precedents are correct?

All of the options provided are correct

In context MATCH() function.... FALSE?

All options are false

With regards to deprecation calculations?

Allocates large deprication

When you save an Excel workbook for viewing on the Web, you have two options. What are they?

An HTML file and a single file web page

Which of the following is TRUE about icon set?

An icon can be used to display a trend graphically.

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

Examine the attached workbook. Fill in the blank: You can add a title to the vertical axis of the chart by using Chart Layouts (Chart Tools "Design" tab) --> Add Chart Element --> __________

Axis Titles

You can add a title to the vertical axis of the chart by using Chart Layouts (Chart Tools "Design" tab) --> Add chart element --> _______

Axis Titles.

A table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS)

Based on the class lecture, which of the following is true about table renaming?

Which of the following is a way of accessing the "Evaluate Formula" dialog box that helps debug a formula?

Choose Formula ->Formula Auditing ->Evaluate Formula

How do you add a comment to a cell? Can you change its shape

Choose Review ➪ Comments ➪ New Comment. Right-click the cell and choose Insert Comment from the shortcut menu. Press Shift+F2. 1. Right-click the Quick Access toolbar and choose Customize Quick Access Toolbar. The Quick Access Toolbar section of the Excel Options dialog box appears. 2. From the Choose Commands From drop-down list, select Drawing Tools | Format Tab. 3. From the list on the left, select Change Shape, and then click Add. 4. Click OK to close the Excel Options dialog bo

All options: 2-Color Scale, 3-Color Scale and Data Bar are available

Choosing: Conditional Formatting -> Color Scales -> More Rules -> Format Styles: allows you to change formatting rules for a color scale. Which of the following formatting options are available?

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.

To gain access to the Table Tools / Design tab in the Excel Ribbon when working with tables, you should do the following:

Click anywhere on the table

Please see the attached Excel workbook image in answering this question. In order to sort the 'Salesperson' column in a descending order, which of the following steps is correct?

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

the Format Painter tool functionality is best described by which of the following?

Copy the format of one cell to another

To save a copy of your workbook with a different name, you can click on ____ followed by _____

Correct File, Save As

Issuing the Print command (in Excel) with a mouse takes three clicks. A slightly more efficient method is to press _____________ and then click the Print button

Ctrl+P

Which key combination can a user press to toggle between formula view and normal view within Excel?

Ctrl+`

If you need to create several different printed reports from the same Excel workbook, setting up the specific settings for each report can be a tedious job. You can simplify the process by using the ______________ feature that enables you to give names to various views of your worksheet, and you can quickly switch among these named views.

Custom Views

What does the following formula return? =CHAR(68)

Which is one of the ways to refresh a query used to import data from external data source using the query wizard?

Data (tab) -> Connections (group) -> Refresh All

Please see the attached workbook. In the EmpID table, enter the salary in cell H8 as 100. Given the error message, why do you think Excel does not let you input that value?

Data validation settings require the salary value to be greater than the hours worked value, 120.

How do you pick the Data Validation option that provides the user with a warning (instead of completely stopping data entry) when a validation check is violated?

Data(tab) -> Data Tools (group)-> Data Validation -> Error Alert -> Style: Warning

To save a copy of your workbook with a different name, you can click on ____ followed by _____

Data, Create Copy Correct Response File, Save As Ribbon, Save Ctrl, A

In the Excel Backstage view, which of the following pieces of information about your workbook are available?

Date the workbook was created

In the Excel backstage view?

Date the workbook was created

Which of the following is a VALID sequence of options for selecting a different chart layout?

Design (from Chart Tools) --> Chart Layouts

Please refer to the figure. Which of the following Excel tabs is NOT visible in a worksheet by default?

Developer

If you write a lookup formula to look up the text 'education', the formula considers which of the following a match?

EDUCATION Education education Correct Response All of the above

In Excel 2013, when you open multiple workbooks, how does the workbook show?

Each workbook always appears in is own window.

In Excel 2013, when you open multiple workbooks, how does the workbook show?

Each workbook always appears in its own window.

If you format a chart element with the font seen in Chart 2, how can you reset it to its original state?

Either of the previous options (resetting through the menu or the right-click options work).

A single element in a worksheet that can hold a value, some text, or a formula is called a(n) ____?

Entity Component Element Correct Response Cell

Where do you find the command to add a pivot table on your worksheet?

Excel Ribbon: Insert (tab) ->Tables

When you apply currency formatting to a Table column and then add a new row to the table, what happens to data entered in the new cell?

Excel applies currency formatting to the new value in that column cell.

Which of the following does the Formula Autocomplete feature in Excel take into account?

Excel built-in functions user-defined functions defined names

Can you open an HTML file in Excel?

Excel can open most HTML files, which can be stored on your local drive or on a web server. Choose File ➪ Open and locate the HTML file. If the file is on a web server, you'll need to copy the URL and paste it into the File Name field in the Open dialog box.

What does XML stand for ?

Extensible Markup Language

What does XML stand for?

Extensible Markup Language

To print a worksheet in a Landscape orientation, you:

Go to the Page Layout tab, in the Page Setup group, click Orientation, and then click Landscape

Argument of HLOOKUP() function?

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

When you save an excel workbook for viewing on the web?

HTML and single file

Which function creates a shortcut that opens a document... internet?

HYPERLINK

Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet?

HYPERLINK()

After pressing "Alt + H" while in an open Excel worksheet, a number of Key Tips are displayed. Which ribbon tab do these Key Tips relate to?

Home

=INDEX(I4:K7,MATCH(B2,I4:I7,0),3)

If the 'amount' for the given 'date' is to be retrieved from the given range of data (I4:K7), which of the following formulas should be used?

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

ampersand (&)

If there are two or more name / value pairs passed in a URL query string, separate them with the following character: _____________ .

Yes, the formula range will be adjusted automatically

If you copy cell "B6" and paste it as a formula in cell "C6," will it produce the correct total for Year 2?

File (tab) -> Print and then choose the PRINT command.

If you want to print a copy of a worksheet, which of the following would you choose?

Correct All of the above

If you write a lookup formula to look up the text 'education', the formula considers which of the following a match?

What is inside the Spelling Dialog Box?

Ignore Once, Ignore All, Add to Dictionary, Change, Change All, Auto Correct

numbers

In Excel, dates are stored as _________.

-------

In context of the MATCH() function, which of the following is FALSE?

Input range and Bin range

In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram?

Change the settings to whole number > less than > 100000

In the EmpID table, enter the empID in cell F8 as 12345. Given the error message, what will you change in the Data Validation dialog box > settings (tab) for 12345 to be a valid entry?

Correct Date the workbook was created

In the Excel Backstage view, which of the following pieces of information about your workbook are available?

How can you insert a header or footer into the workbook?

In the Page Layout view, click on the placeholder for the header or footer, and add the information. (and something else)

Which statement is true. they all relate to the formula: "IF(OR(Category="Pasta", Category="soup") inventory_cost,0)"?

In this formula, the second statement in the OR (i.e , checking for the category = "soups")... FALSE

Which of the following statements is true? They all relate to the formula: =IF( OR(Category="Pasta",Category="Soups"), Inventory_Cost,0 )

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

Which of the following statements is true? They all relate to the formula: =IF(OR(Category="Pasta",Category="Soups"), Inventory_Cost,0 )

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

When a header or footer section is activated?

It is not a valid code

What is the purpose of the Text to Columns command?

It parses strings into their component parts.

what is the purpose of the Text to Columns command?

It parses strings into their component parts.

Which of the following best explains the MATCH function?

It returns the relative position of an item in an array that matches a specified value in a specified order

How does the Excel search/replace function work for dates?

It searches for a date as displayed in a cell, and then does a replace

Which of the following is the MOST compelling motivation for using HLOOKUP()?

It usually reduces duplicate data storage

Fill in the blank: Every chart type has a set of ____ that you can choose from (with settings for chart elements such as a title, data labels, axes, and so on).

Layouts

Which keyboard shortcuts can be used to move the active cell one colunn to the left?

Left arrow or Shift + Tab

The HYPERLINK function syntax has two arguments. Which argument has the path and file name to the document to be opened?

Link_Location

The HYPERLINK function syntax has two arguments. Which argument has the path and file name to the document to be opened?

Link_location

What statement about the two Hyperlink arguments Hyperlink(link_location,[friendly_name]) is TRUE?

Link_location is required, Friendly_name is optional

RIBBON

MAIN LOCATION FOR EXCEL COMMANDS. CLICKING AN ITEM IN THE TAB LIST CHANGES THE RIBBON THAT IS DISPLAYED

The _________ function can return a specified number of characters beginning at any position within a string.

MID()

In order to keep track of all conditional formatting used in a worksheet, we use the ___________ menu command under Conditional Formatting

Manage Rules

What is the name of the company that created Excel?

Microsoft

Which of the following entries does Excel automatically recognize as a date?

None of the answers provided is correct

Which of the following is FALSE about Pivot Table Slicers?

None of the answers provided is false

How do you format a valid date serial number as a date ?

None of the options provided is correct

______ cells are by default disregarded when data validation rules are created. These cells can be included in the validation rule by unchecking the: "Ignore blank" checkbox.

None of the options provided is valid

Please refer to the attached workbook. What happens if the following value filter is added to the existing report? Value filter: Sum of Sales does not equal 5000.

Nothing happens. Both the records remain.

In the following choices, which one is not among the drop-down list of the conditional formatting on the ribbon?

Number bars

In the Excel Backstage view, which of the following pieces of information about your workbook are available?

Number of colors and fonts used Total time (in minutes) spent editing your workbook Correct Response Date the workbook was created Your Windows password

Please refer to the attached image. What is the result upon using the formula =B2-C2? B2: 2/3/2014 C2: 1/11/2014

Number of days between two dates

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)

PMT(rate,nper,pv)

To calculate the present value of an investment, which is the total amount that a series of future payments is worth now, the function used is:

PV()

Which of the following options would you choose if you want to insert a page break in the worksheet?

Page Layout (tab) -> Page Setup (group) -> Breaks -> Insert Page Break

Which of the following options would you choose if you want to remove all manual page breaks in the worksheet?

Page Layout (tab) -> Page Setup (group) -> Breaks -> Reset All Page Breaks

_______ refers to the non-printing areas along the sides, top, and bottom of a printed page. You do not see data from worksheet cells here, but may see a header or footer.

Page Margins

Which of the following is TRUE about Web queries?

Performing Web queries copies information from an HTML file into the current workbook

What Excel element is shown in the red box?

Quick Access toolbar

What is the syntax for the RATE() function?

RATE(nper,pmt,pv,[fv],[type],[guess])

The _____________ function is used to return a specified number of letters or characters from the end of a text string.

RIGHT()

The ________ function is one solution to account for for floating point number errors. It also serves to round off values to the desired number of decimal places.

ROUND()

The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______.

Range (and) criteria

With regards to Depreciation Calculation functions in Excel, which of the following are true about the function VDB()?

Refers to the Variable-declining balance function. Computes the depreciation of an asset for any period (including partial periods) using the double-declining balance method or some other method you specify.

You cannot replace a type of formatting in a cell with another type of formatting.

Regarding the search-and-replace features provided by the "Find and Replace" dialog box, what is NOT true?

Which of the following is NOT a summary function for Pivot tables?

Regression

Which of the following is *NOT* a type of cell reference?

Relative Absolute Mixed Correct Response Dynamic

You may refer to the attached workbook to answer the following question. What command under the Table Tool /Design Tab can you use to add two more rows to the table?

Resize table

What does the following formula do? =COUNTIF(Data, TODAY())

Returns the number of cells containing the current date in the range named Data.

Adding comments to a cell is available through which tab of the Excel ribbon?

Review

The first field you add to a PivotTable report that does not contain numbers will automatically be added in the _______ part of the report.

Row Labels

the HLOOKUP function searches for

Rows

______ locates a substring in another text string and returns the start position of the substring.

SEARCH()

The last step in the Query Wizard, "Finish", lets you do which of the following?

Save a query so that you can reuse it

Examine the attached image. What will the result of the following formula be? ="Section " &mysection

Section B

What will the result of the following formula be? ="Section " &mysection

Section B

Which of the following options can be used to quickly remove data validation for a cell?

Select the cell and open the Data Validation dialog box (Data (tab) -> Data Tools (group)). On the Settings tab, click Clear All.

How do you specify data validation criteria?

Select the cell or range- choose data- data validation- input message, customize setting, and error alert in data validation box

Which of the following is a shortcut key used to calculate the formulas in the active worksheet?

Shift + F9

How do you identify/remove duplicate rows?

Start by moving the cell cursor to any cell within your data range. Choose Data ➪ Data Tools ➪ Remove Duplicates, and the Remove Duplicates dialog box appears. Select the columns that include duplicates in order to delete them. Example for identifying duplicates:Enter this formula into cell G2: =A2&B2&C2&D2&E2&F2 copy these formulas down for each row of your data

It adds a background image to the currently active worksheet

Suppose you are on Sheet1 and you have selected cells A5:B5. What happens when you insert a background image through the following sequence of steps? Page Layout (tab) -> Page setup (command group) -> Background?

Once you have a table you can click the "Table Tools / Design" tab. Then, under "Table Tools / Design" check the box for "Total Row"

The cells highlighted in yellow contain a Total Row to added to a table?

While converting from military time, when would the formula TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2)) return an incorrect result?

The contents of cell A1 do not comprise four digits

In context of the MATCH() function, which of the following is FALSE?

The syntax of match is: MATCH (lookup_value,lookup_array, row_offset) Correct Response All of the options are false A #VAL error value is returned if the MATCH function is unsuccessful in finding a match Match returns the cell contents (e.g., a product name) when it detects two cells with duplicate contents

In Excel when calculating loan information, what does the PV() function return?

The time-adjusted value of the original loan amount

=TRIM()

To eliminate redundant blank space from the start or end of the string

WORKDAY()

To find a working date that falls after a number of days (e.g., 12 business days after some date), you typically use this function:

A tilde followed by an asterisk (~*)

To find an asterisk (i.e., the symbol: *) within a cell, you enter which of the following in an Excel form search criteria?

What is the Formula AutoComplete feature?

To have Excel autocomplete an entry in that list, use the navigation keys to highlight the entry, and then press Tab. Notice that highlighting a function in the list also displays a brief description of the function

=TODAY()

To insert the current date which is updated each time a workbook is opened or recalculated

SEARCH()

To locate a substring in another text string and returns the start position of the substring

Correct Shift+F2

To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________.

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.

Undo

In Excel, a/an ________ is created or run to retrieve text or data from a Web Page.

Web Query

Delete Rule

What command (within Conditional Formatting) is used to delete a specific rule from a worksheet?

Clear Rules

What command (within Conditional Formatting) is used to delete all the rules relevant to a particular group of cells?

Select the range A1:B18 and ensure that cell A1 is the active cell --> Home --> Styles --> Conditional Formatting --> New Rule --> Use a Formula to Determine Which Cells to Format --> Enter the following formula in the Formula box: =ISTEXT(A1) --> Format --> Fill --> Select color Yellow --> OK --> OK

What is the correct set of steps to add conditional formatting to cells A1:B18 so that a yellow background fill color is applied to a cell that is not empty.Note: Cells A1:B18 do not have the conditional formatting and Cells G1:H18 have the conditional formatting.

Link_location is required, Friendly_name is optional

What statement about the two Hyperlink arguments Hyperlink(link_location,[friendly_name]) is TRUE?

-----------

What value of Discount is returned when you use an VLOOKUP function with a quantity entered (i.e., value looked up) of 1001 and a range-lookup parameter of FALSE?

Check 'My table has headers'

What would you do, while creating a table, to display your Table Headers?

Top/Bottom rules

Which rule category does the following conditional formatting rule belong to? "Highlight the top 10 items"

While using cell references in Excel, what key is used to change the type of the references?

Because data in column C is missing or is equal to zero

Why does the #DIV/0! error occur if the formula in cell D2 (also copied to the cells below it) is: "=(C2 - B2) / C2"

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

With regards to Deprecation Calculation functions in Excel, which of the following are true about the function VDB()?

Which of the following file formats can be opened using Excel 2016?

XLS,XLT, and XLA

You ask your customers to provide you with inventory usage data to help you plan your production schedule. Which of the following file formats will you ask them to provide data in (so that data can be easily imported into Excel)?

XML

Which of the following file types can Excel import?

XML Files (.xml) Comma separated values (.csv) Microsoft Access (.accdb)

Can you apply conditional formatting to an excel that already has formatting?

Yes

If you copy the formula in cell "B8" and paste it into cell "D8" (shaded in green), will it produce the correct total sales value for Quarter 3?

Yes

Can you apply conditional formatting to an Excel table that already has its own formatting?

Yes you can

Can you apply conditional formatting to an Excel table, which already has its own formatting?

Yes you can

You can use a data Form to add, find, and edit rows in a range or table. Is this correct?

Yes, all these are valid form operations

Charts are dynamic?

Yes, by default

Charts are dynamic, i.e., they change as the underlying data changes. Is this an accurate statement?

Yes, by default they are dynamic

Charts are dynamic, i.e., they change as the underlying data changes. Is this an accurate statement?

Yes, by default they are dynamic.

In an Excel Form, when finding a row by entering search criteria: Will the question mark symbol "?" find or match any single character?

Yes, the "?" symbol is used to find/match a single character

If you copy cell "B6" and paste it as a formula in cell "C6," will it produce the correct total for Year 2?

Yes, the formula range will be adjusted automatically

Please see the attached workbook. If you copy cell "B6" and paste it as a formula in cell "C6," will it produce the correct total for Year 2? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/problemsets/PS2_files/cells_sales.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq

Yes, the formula range will be adjusted automatically

Add a comment to the sales figure cell, and explain to your assistant what you want him to do

XML

Can you insert hyperlinks in a worksheet?

You can create hyperlinks from cell text or graphics objects, such as shapes and pictures. To create a text hyperlink in a cell, select the cell and choose Insert ➪ Links ➪ Hyperlink (or press Ctrl+K). The Insert Hyperlink dialog box, appears

When you try to import data from MS-Access into Excel, which of the following is TRUE?

You can import a table or a query

Which of the following does not hold true for Watch Windows?

You can see all the formulas in a worksheet and print them for later reference

Which of the following BEST applies to the INDEX function?

You can use it with the MATCH() function to do reverse lookups when the data is not sorted (e.g., to find the ProductID for a product named "Chocolate Cake", assuming product names are not sorted)

Which of the following is TRUE about Pivot tables?

You cannot add new rows or columns to the Pivot table You cannot change any of the calculated values You cannot enter formulas within the pivot table Correct Response All of the above are true

Please see the attached workbook. What does the small red triangle on the upper right corner of cell B1 denote?

a comment exists for that cell

What does the small red triangle on the upper right corner of a cell denote?

a comment exists for that cell

COUNTIF() function only works if

a continguous range argument is present. if data is defined as noncontigous range, formula returns #VALUE!

Which of the following is the difference between a Pie and Doughnut chart in Excel?

a doughnut chart can display more than one series of data

what is the difference between a pie chart and a doughnut chart?

a doughnut chart can display more than one series of data

Pivot Table:

a dynamic summary report generated from a database

A pivot chart is which of the following?

a graphical representation of a data summarized in a pivot table

Loan amortization schedule is:

a table of values that shows various types of information for each payment period of a loan.

In Excel, if one enters a time greater than ten thousand hours, it is interpreted as ________ instead of a time value.

a text string

To find an asterisk.... search?

a tilde followed by *

To find an asterisk (i.e., the symbol: * ) within a cell, you enter which of the following in an Excel form search criteria?

a tilde followed by a question mark Correct Response a tilde followed by an asterisk a tilde by itself this is a type of search you cannot do with Excel Forms

To find an asterisk (i.e., the symbol: * ) within a cell, you enter which of the following in an Excel form search criteria?

a tilde followed by an asterisk

to find an asterisk within a cell, you enter which of the following in an excel form search criteria

a tilde followed by an asterisk

Which of the following statements is true about excel workbooks?

a workbook can have many worksheets

By default, Excel uses ________ font a. 11 point (pt) Calibri b. 12 pt Times New Roman c. 11 pt Comic Sans MS d. 12 pt Arial

a. 11 point (pt) Calibri

Worksheet names can have upto ______ characters. a. 31 b. 34 c. 28 d. 32

a. 31

To gain access to the Table Tools/Design tab in the Excel Ribbon when working with tables, you should do the following: a. Click anywhere on the table b. Such a tab does not exist, the only tab you can get access to is: Table Format/Design c. Click on the Home tab on the Ribbon, and check the appropriate box to make it appear d. The Table Tools/Design tab does not provide any useful commands, so it is pointless trying to access it

a. Click anywhere on the table

What is the purpose of the function TIME()? a. Converts the input hours, minutes & seconds to a serial number b. Returns the current time c. Returns the serial number of the current data and time d. Converts a time in the form of texting string (e.g., "18:45") to a serial number

a. Converts the input hours, minutes & seconds to a serial number

To close an Excel workbook, you can click on ____ followed by ______ a. File, Close b. Ctrl, S c. Ribbon, Formulas d. File, Save

a. File, Close

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

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

a. It is not a valid code

Which of the following is a correct syntax for 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 above

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

Charts are dynamic, i.e. they change as the underlying data changes. Is this an accurate statement? a. No, they are static by default, and you must press "Ctrl-F7" to update them. b. Yes, by default they are dynamic. c. This is untrue, because it would lead to charts with circular references. d. Charts placed in a separate sheet from the data are not dynamic, but if it is placed in the same sheet as the source data--it allows the chart to change if the data changes.

a. Yes, by default they are dynamic.

Which of the following options can be used to quickly remove data validation for a cell? a. select the cell and open the Data Validation dialog box (Data (tab) -> Data Tools (group)). On the Settings tab, click Clear All. b. Right click on the cell and choose delete c. Right click o the cell and choose erase d. select the cell and open the Data Validation dialog box (Data (tab) -> Data Tools (group)). Select Input Message tab and click on OK.

a. select the cell and open the Data Validation dialog box (Data (tab) -> Data Tools (group)). On the Settings tab, click Clear All.

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

Which of the following types (chart types) is a VALID option in the Charts ribbon group on the Insert ribbon tab?

all of the options provided are valid

When a range of cells is selected, which of the following occurs?

all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded

When you create one or more Sparklines, they...?

all use (by default) the automatic axis scaling

In Excel, dates are stored as _________.

alphanumeric characters Correct Response numbers alphabets special characters

A pivot table and a pivot chart are joined in a two-way link, if you make structural or filtering changes to one, the other _______.

also changes

If there are two or more name / value pairs passed in a URL query string, separate them with the following character: _____________ .

ampersand (&)

This error code tells us that a problem with a value exists; for example, you specified a negative number where a positive number was expected. a. #REF! b. #NUM! c. #N/A d. #NAME?

b. #NUM!

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 cell that contain incorrect entries c. incorrect entries are italicized d. correct entries are italicized

b. Circles appear around the cells that contain incorrect entries

Which of the following is NOT a way to add a new worksheet to a workbook? a. Click the insert new worksheet control located to the right of the last sheet tab b. Press Shift + F12 c. Right-click a sheet tab, choose Insert from shortcut menu and select the worksheet icon from the general tab d. Press Shift + F11

b. Press Shift + F12

To find an asterisk (i.e., the symbol: * ) within a cell, you enter which of the following in an Excel form search criteria? a. a tilde followed by a question mark b. a tilde followed by an asterisk c. a tilde by itself d. this is a type of search you cannot do with Excel Forms

b. a tilde followed by an asterisk

Grand Totals in Pivot Tables are used to ______________ a. return the dates of grad (over $1,000) transactions b. display totals for all cells in column (or row) of a pivot table c. recalculate the pivot table after making changes to the source data d. change the totals for different transactions (e.g., a sale)

b. display totals for all cells in a column (or row) of a pivot table

A #REF! error occurs in Excel when a formula uses a/an _____________ cell reference a. valid b. invalid c. multi-cell average d. summation with a

b. invalid

In Excel, dates are stored as __________. a. alphanumeric characters b. numbers c. alphabets d. special characters

b. numbers

The HLOOKUP function seraches for data in _____________. a. columns b. rows c. external databases d. external workbooks

b. rows

While creating a parameterized web query the question mark (?) is used to ______.

begin the URL query string

Please refer to the attached workbook. The ______ , ________ and ________fields are in the Column, Rows and Values sections respectively.

branch, date and amount

Please see the attached workbook. In cell A2, how is the data validation done?

by checking if the number of characters is between 4 and 20

Consider the phrase "This is Excel" (supposed to be bolded with a strike through). Which sequence of keyboard shortcuts result in the formatting used in the words "This is Excel" in the earlier sentence (within Excel, on a Windows PC)? a. (Control + I) + (Control + B) b. (Control + C) + (Control + V) c. (Control + B) + (Control + 5) d. (Control +5) + (Control +F4)

c. (Control + B) + (Control + 5)

What will be the result of this formula =IF(A1<100000,A1*5%,A1*7.5%), if the cell A1 has a value of 90000? a. 6750 b. 4750 c. 4500 d. 500000

c. 4500

What is a keyboard shortcut for creating a table? a. Alt+T b. Ctrl+F2 c. Ctrl+L d. Ctrl+Alt+L

c. Ctrl+L

To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________. a. Ctrl+Alt b. Shift+F2 c. F5 d. Ctrl+A

c. Shift+F2

Can you apply conditional formatting to an Excel table, which already had its own formatting? a. No you cannot b. No you can only apply to a sheet in the workbook c. Yes you can d. You you can apply it to a table, but not to multiple tables on your worksheet

c. Yes you can

A -> Z (ascending sort) and Z-> A (descending sort) are both referred to as ________ sorting options. They are both available by clicking a single button in the Excel ribbon (Data tab). a. conditional b. custom c. quick d. high-low-close

c. quick

A(n) ____________ is an interactive control that makes it easy to filter data in a pivot table? a. easy filter b. reverse pivot table c. slicer d. formula

c. slicer

Data validation on a cell or range can do which of the following?

can do all of the above

data validation on a cell or range can do which of the following

can do all of the above

A single element in a worksheet that can hold a value, some text, or a formula is called a(n) ____?

cell

A single element in a worksheet that can hold a value, some text, or a formula is referred to as which of the following?

cell

a single element in a worksheet that can hold a value, some text, or a formula is called a

cell

Use Ribbon to

change aspects of chart formula

charts_example.xlsx Examine the attached workbook. How can you resize the chart title in Chart 1 of Sheet 1?

change font size

After you create a chart, which of the following actions can you perform on it?

change the chart type, formatting, and data series

Which of the following is an object that contains all other elements in an Excel chart?

chart area

what would you do while creating a table, to display your table headers

check "my table has headers"

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 enries

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

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.

To remove a single individual field from a report, _______ the check box beside the field name in the PivotTable Field List

clear

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

Consider the phrase: this is excel?

control B + control 5

Ctrl+X

copies table so it can be moved to a different worksheet

How can you copy the conditional formatting of a cell to other cells?

copy the cells then use the paste special dialog box and select the formats option; or choose home- clipboard- paste- formatting

in excel in order to avoid incomplete calculation errors and to ensure that formulas are fully calculated, you would press _______

ctrl + alt + shift + f9

In Excel, inorder to avoid incomplete calculation errors?

ctrl+alt+shift+F9

Which of the following does the function =NOW() return?

current date and time

A Web query contains information about the path to the ___________ and can pull the __________ data directly from that source.

data source, updated

in the excel backstage view, which of the following pieces of information about your workbook are avaliable

date the workbook was created

A data form provides a convenient means to enter, display and search for data within a table. It also permits you to do the following: ____________

delete a record/row

When you specify which rows should be included in your query (selection criteria), you are satisfying which step of the Query Wizard?

filter data

In order to delete information using Find and Replace in Excel, enter the text to be deleted in the ________ field and leave the Replace With field _____.

find what and empty

which of the following is not considered a style attribute

formula

the easiest way to enter a function into a formula is to use formula autocomplete or another way to insert a function is to use the ____ on the _____tab

function library group on the formulas tab

In the Color Scales method of Conditional Formatting, color ________ are used to compare values.

gradients

Pivot Chart:

graphical representation of a data summary displayed in a pivot table

GETPIVOTDATA function

helps ensure that formulas will continue to reference intended cells if pivot table layout has changed

Text Import Wizard:

helps specify how the data will be imported when importing a text file into Excel

To make Data Bars easier to view, we can ____ the numerical values in the cell.

hide

To make Data Bars easier to view, we can ________ the numerical values in the cell.

hide

A -> Z (ascending sort) and Z-> A (descending sort) are both referred to as ________ sorting options. They are both available by clicking a single button in the Excel ribbon (Data tab).

high-low-close custom conditional Correct Response quick

To hide rows or columns?

home (ribbon tab), format (cells group) -> hide&unhide (from the drop list)

to hide rows or columns you can click on ____ then ______

home(ribbon tab), format(in the cells group)--> hide and unhide

when you save an excel workbook for viewing on the web you have two options

html file and single file web page

Chart 1: Column, Chart 2 Bar

identify the correct chart types. You can use the data in the attached Excel workbook (charts_SampleData.xlsx) to try different charts.

A #REF! error occurs in Excel when a formula uses a / an ________ cell reference

invalid

a Ref! error occurs in excel when a formula uses a/an ____ cell reference

invalid

.mht or .mhtml

is file format (file extension) used when you save Excel workbook as a Single File Webpage

ROUND() function:

is one solution to account for floating point number errors. Also serves to round off values to the desired number of decimal places

When the value of a cell is changed (after conditional formatting is applied), the cell formatting ______ based on the conditions set.

is updated

DeleteRule (within conditional formatting):

is used to delete a specific rule

#REF! error value:

means the formula contains an invalid cell reference

By pressing the spacebar, the contents of a cell seem to erase because pressing the spacebar inserts a / an _______ space character

non visible

For a range called Data, what does the function =COUNTIF(Data, TODAY()) return?

number of cells containing the current date

For a range called Data, what does the function =COUNTIF(Data, "<>0") return?

number of cells not equal to 0

In Excel, dates are stored as _________.

numbers

In excel, dates are stored as ______.

numbers

in Excel, dates are stored as _________.

numbers

in excel, dates are stored as

numbers

A chart in an Excel worksheet provides a visual representation of which of the following?

numeric values

#REF! errors

occurs when a formula uses an invalid cell reference.

which of the following is not a way to add a new worksheet to a workbook

press shift + F12

When do you use the following sequence of commands: PivotTable Tools -> Analyze -> PivotTable -> Options -> Generate GetPivot Data?

prevent Excel from using GETPIVOTDATA function

A -> Z (ascending sort) and Z-> A (descending sort) are both referred to as ________ sorting options. They are both available by clicking a single button in the Excel ribbon (Data tab).

quick

A group of cells is referred to as which of the following?

range

The function of value filter in a pivot table is to ___________.

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

A pivot table and a pivot chart are joined in a two way link, if you make structural or filtering changes to one,

remains unchanged

Clear Rules

remove only conditional formatting

While using web queries Rich text formatting is typically used if we wish to ______.

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

Data source for pivot table can...

reside in a worksheet or in a/an external data file

calculated field:

reside in values area of pivot table

calculated item:

resides in columns area, rows area, or filters area of pivot table

What command under the Table Tool /Design Tab can you use to add two more rows to a table?

resize table

MID function

returns a specified number of characteristics beginning at a specified position within a string

LEFT function

returns a specified number of characteristics from beginning of a string

RIGHT function

returns specified number of characteristics from end of string

to view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut

shift + f2

Combination Chart

single chart that consists of series that use different chart types

A(n) _________ is an interactive control that makes it easy to filter data in a pivot table?

slicer

an _____ is an interactive control that makes it easy to filter data in a pivot table

slicer

what are icon sets?

symbols that depict the value in each cell, ranges from 3-5

The Find and Replace dialog box cannot find background color formatting in tables that was applied using __________ or formatting that is applied based on ____________

table style, conditional formatting

The lookup value can include wildcard characters if the lookup_value argument is ______ and range_lookup argument is ________

text, true"

friendly_name:

the jump text or numeric value that is displayed in the cell

One-Way data table shows?

the results of any number of calculations for different values of a single input cell

Pivot Table helps

transform endless rows and columns of numbers into meaningful presentations of the data

How do you change the type of summary formula in a table?

use the drop down list in the last summary part of the table

How can you modify the axes of a chart?

use the format axes task pane

A #REF! error occurs in Excel when a formula uses a / an ________ cell reference

valid Correct Response invalid multi-cell average summation within a

what are color scales?

varies the background color of a cell based on the cell's value, relative to other cells in the range.

Fill in the blanks: Conditional formatting effects help us _______ values in cells making it easier to locate data, find exceptions and spot ______ quickly.

visualize, trends

What's the formula bar?

where you enter calculations

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

An Excel workbook can contain one or more of which of the following?

worksheets

The __ command is used to display text on multiple lines in a cell.

wrap text

Which of the following is NOT a tool in Analysis ToolPak for Excel?

y-Test

Can you save a workbook in HTML format?

yes that an a single file web page are the two options when saving a workbook to be viewed on the web

What does Excel return when your MATCH() function uses an exact match option and searches for an item that is not part of the lookup_array or lookup_vector?

#N/A

#N/A errors

#N/A error occurs if any cell referenced by a formula displays #N/A and when a LOOKUP function (HLOOKUP, LOOKUP, MATCH, or VLOOKUP) can't find a match

This error code exists,...number was expected?

#NUM!

This error code tells us that a problem with a value exists; for example, you specified a negative number where a positive number was expected.

#NUM!

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!

the error code tells us that a problem with a value exits; for example, you specified a negative number where a positive number was expected

#NUM!

This error code tells us that a problem with a value exists; for example, you specified a negative number where a positive number was expected.

#REF! Correct Response #NUM! #N/A #NAME?

In the attached image, examine the formula in cell "E9." What result will this formula produce? =B7:C16

#VALUE!

examine the formula in cell "E9." What result will this formula produce?

#VALUE!

Consider the phrase: "This is Excel". Which sequence of keyboard shortcuts result in the formatting used on the words "This is Excel" in the earlier sentence (within Excel, on a Windows PC)?

(Control + 5) + (Control + F4) Correct Response (Control + B) + (Control + 5) (Control + I) + (Control + B) (Control + C) + (Control + V)

"This is Excel". Which sequence of keyboard shortcuts result in the formatting used on the words "This is Excel" in the earlier sentence (within Excel, on a Windows PC)?

(Control + B) + (Control + 5)

Consider the phrase: "This is Excel". Which sequence of keyboard shortcuts result in the formatting used on the words "This is Excel" in the earlier sentence (within Excel, on a Windows PC)?

(Control + B) + (Control + 5)

Examine the attached image. What will the result of the following formula be? ="Section " &mysection

***** find answer

In Excel when calculating loan information, what does the PV() function return?

***** find answer

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

Examine the attached workbook. If you format a chart element with the font seen in Chart 2, how can you reset it to its original state?

*look up*

Which of the following is the file format of the Excel 97-2003 workbook?

.xls

By default, what is the extension to save a workbook in Excel 2016?

.xlsx

By default, what is the extension used when you save a workbook in Excel 2016?

.xlsx

In the attached spreadsheet, please use an appropriate formula to compute the total of the Cells C2 through C11 (surrounded by a red border in the worksheet). What is this total?

14787.45

Please see the attached workbook. How many names does the workbook have defined? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/problemsets/PS3_files/rng_range_names.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq

With a single IF() function, how many actions can you ask Excel to carry out?

2 (i.e., an action if the criteria is true, or an an action if the criteria is false)

With a single IF() function, how many actions can you ask excel to carry out?

2 (if criteria is true, if criteria is false)

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 4100 respectively

For Windows systems, Excel uses the standard 8-bit ANSI character set. This character set consists of ___ characters.

255

In Excel, the maximum length of a Web query URL is _______ characters.

255

Please see the attached workbook. The formula =LOOKUP("Mary", A2:A5, C2:C5) returns what value?

How many sort options/commands do we have in Excel under the 'Data' tab?

In Excel, the DATE function takes _____ arguments.

The formula =COUNTIF(range, "???") returns the number of cells in the range containing exactly ______ characters of text

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

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 Correct Response 4500 500000

What does the following formula return? =FIND("m","Big Mama Thornton",1)

8 6 5 Correct Response 7

Examine the attached worksheet. Which of the following formulas, when entered in cell E6, will give the Discount percentage for Pick Axe? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/Formulas_Test.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq

=(1-(F6/D6))*100

To calculate the percentage of the total widget sales made by the top 5 employees, which of the following formulae would you enter in cell "C22" (shaded purple)

=C20/F8

To calculate the percentage of the total widget sales made by the top 5 employees, which of the following formulae would you enter in cell "C22" (shaded purple) https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/problemsets/PS1_files/PS1_formulas.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq

=C20/F8

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?

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

Which formula can be used to find: How many songs were sung by singers whose last names contain exactly six letters?

=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*")

Which formula can be used to find: How many songs were sung by singers whose names begin with S? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/problemsets/PS4_files/Advanced_aggregation.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq

=COUNTIF(B2:B23,"S*")

Which formula can be used to find: How many songs were sung by Madonna and were three to four minutes long (not inclusive)?

=COUNTIFS(B2:B23,"Madonna",D2:D23,"<=4",D2:D23,">=3")

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)

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

=HYPERLINK("[Example.xlsx]E2", "Quarterly Profit Report") Correct Response =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")

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)

If the 'amount' for the given 'date' is to be retrieved from the given range of data (I4:K7), which of the following formulas should be used?

=INDEX(I4:K7,MATCH(B2,I4:I7,0),3)

Please see the attached workbook. If the 'amount' for the given 'date' is to be retrieved from the given range of data (I4:K7), which of the following formulas should be used?

=INDEX(I4:K7,MATCH(B2,I4:I7,0),3)

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? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/problemsets/PS7_files/dataLookup_eg4.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq

=LOOKUP(B1,H2:H7,I2:I7)

Converting a string (e.g., an email address) to purely lowercase, can be done using which of the following functions?

=LOWER()

Syntax for MATCH function is

=MATCH(lookup_value, lookup_array, match_type)

Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet?

=NOW()

Displaying current time formula:

=NOW()-TODAY()

Which of the following formulas calculates the number of payment periods for a $5,000 loan that has a monthly payment amount of $117.43. The loan has a 6 percent annual interest rate.

=NPER(6%/12,117.43,-5000)

Which Excel function returns the serial number of current date and time?

=Now()

If cell "A1" contains JOHN F. SMITH and you want this value to be changed into a proper case like John F. Smith, what Excel function would you use?

=PROPER(A1)

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*1440,0)/1440

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(MONTH(P5)/3,0)

To get rid of ALL blank spaces in a string (e.g., in "Jo hn Doe "), which Excel function will you use?

=SUBSTITUTE()

Please see the attached workbook. In the Student Rank table, the points column represents the points awarded to students. If the total points that can awarded in the class cannot exceed 350, what data validation setting would you use for cell I19, to make sure that the entry does not exceed the required total of 350?

=SUM($I$13:$I19)<=350

Which of the following formulae must be entered in cell "C8" (shaded in blue) to get the total widget sales for Quarter 2? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/problemsets/PS1_files/PS1_formulas.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq

=SUM(C4:C7)

Which formula can be used to find: How many songs were sung by either Eminem or Spears?

=SUM(COUNTIF(B2:B23,{"Eminem","Spears"}))

Determining date most recent Sunday formula:

=TODAY()-MOD(TODAY()-1,7)

Which of the following formulas is used to determine the date of the Sunday that was most recent?

=TODAY()-MOD(TODAY()-1,7)

To eliminate redundant blank space from the start or end of a string (e.g., " Tucson "), which Excel function will you use?

=TRIM()

What formula (in cell B3) returns the tax rate for the income in cell B2?

=VLOOKUP(B2, D2:F7, 3)

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 salary field in row 2, which of the following formulas would you use?

=VLOOKUP(C2,Students,4,FALSE)

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 salary field in row 2, which of the following formulas would you use?

=VLOOKUP(C2,Students,4,FALSE)

What changes will you make in the formula =VLOOKUP(D2,PAGES!A2:B10,2,FALSE) so that it checks for an approximate match?

=VLOOKUP(D2,PAGES!A2:B10,2,TRUE)

Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the cell entry is a date, and that thedate is a Sunday? Hint: Date format: MM/DD/YYYY

=WEEKDAY(A1)=1

HYPERLINK function Syntax is

=____(link_location, [Friendly_name])

Syntax for VLOOKUP function is

=_____(lookup_value, table_array, col_index_num, range_lookup)

to create a hyperlink to a specific location of word doc it would be

=hyperlink(location, displayed)

Which two wildcard characters are supported by the "Find and Replace" dialog box in an Excel Worksheet?

?, *

Which two wildcard characters are supported by the "Find and Replace" dialog box in an Excel worksheet?

?, *

Which of the following does the search string ~?NULL~? match when entered in the find and replace dialog box?

?NULL?

Correct A table of values that shows various types of information for each payment period of a loan

A Loan amortization schedule in Excel is :

What is one of the DRAWBACKS of using a pivot table?

A Pivot Table does not update automatically when information changes in source data

Which of the following is TRUE for Pivot Tables?

A Pivot table can be sorted by any column selected in the Pivot table field list

one-way

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.

Column

A bar chart is essentially which of the following chart types rotated 90 degrees clockwise?

delete a record/row

A data form provides a convenient means to enter, display and search for data within a table. It also permits you to do the following: ____________

When using the formula "=WORKDAY(DATE(2014,1,4),10)", what does the output show?

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

Balance Sheet

A financial snapshot of a company at a given point in time

The formula returns an error

A formula refers to a value in the Total Row of a table. What will happen to the formula result if the option for "Total Row" (from the Excel ribbon: Table Tools / Design tab) is unchecked?

A pivot chart is which of the following?

A graphical representation of a data summarized in a pivot table

Range

A group of cells is referred to as which of the following?

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

What does it mean when Excel displays the following error? #DIV/0!

A number is divided by zero

Which of the following reviewing features does Excel offer to you?

A proofing tool to check spelling

Based on the class lecture, which of the following is true about table renaming?

A table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS)

A loan amortization schedule?

A table of values that show various types of information for each payment period of a loan

A Loan amortization schedule in Excel is :

A table of values that shows various types of information for each payment period of a loan

What will be returned if an Excel LOOKUP() based formula has an entry for the lookup_value and the look_up vector, but no entry for a result_vector parameter?

A value from the lookup_vector

A cell contains:

A value, formula, or text ONLY

What's a waterfall chart?

A waterfall chart is used to show the cumulative effect of a series of numbers, usually both positive and negative numbers. The result is a staircase-like display.

Which of the following is TRUE about a Watermark in Excel?

A watermark is an image (or text) that appears on each printed page. A watermark can be a faint company logo, or a word such as DRAFT. Excel doesn t have an official command to print a watermark, but you can add a watermark by inserting a picture in the page header or footer. Correct Response All of the above statements are TRUE.

Which of the following is TRUE about a Watermark in Excel?

A watermark is an image (or text) that appears on each printed page. A watermark can be a faint company logo, or a word such as DRAFT. Excel doesn't have an official command to print a watermark, but you can add a watermark by inserting a picture in the page header or footer.

Which of the following statements is true?

A workbook can have many worksheets.

slicer

A(n) _________ is an interactive control that makes it easy to filter data in a pivot table?

If Row 15 needs to be on the first row of a new page and only a horizontal page-break is needed, what cell should be selected to create a page break?

A15

TYPICAL DIALOG BOX

ADDS ADDITIONAL INFORMATION TO A COMMAND. TYPICAL: CANT PARTICIPATE IN SPREADSHEET, STAY ON TOP: CAN CONTINUE TO WORK ON EXCEL

Add a comment to the sales figure cell, and explain to your assistant what you want him to do

Pivot table manually?

After creating the table, select the items and then choose OPTIONS tab under pivot tables

Change the chart type, Formatting, and Data series

After you create a chart, which of the following actions can you perform on it?

Which of the following about watermarks in Excel is true?

All above

Which of the following are TRUE in the context of using VLOOKUP?

All answers provided are correct

In context of the MATCH() function, which of the following is FALSE?

All of the Options are false

Choosing: Conditional Formatting -> Color Scales -> More Rules -> Format Styles: allows you to change formatting rules for a color scale. Which of the following formatting options are available?

All options: 2-Color Scale, 3-Color Scale and Data Bar are available

The SUM() function does which of the following in Excel?

Allows you to total up numbers in a specified range of cells

Which of the following options moves the active cell right one screen?

Alt+PgDn

#VALUE! errors

An argument for a function is of an incorrect data type, or the formula attempts to perform an operation using incorrect data. For example, a formula that adds a value to a text string returns the #VALUE! error. A function's argument is a range when it should be a single value. A custom worksheet function is not calculated. You can press Ctrl+Alt+F9 to force a recalculation. A custom worksheet function attempts to perform an operation that is not valid. For example, custom functions can't modify the Excel environment or make changes to other cells. You forget to press Ctrl+Shift+Enter when entering an Array formula.

There is a type of investment which you make a stream of unfixed payments

Annuity

Consider the function =COUNTIF(A1:A200, ">200"). The information between the parentheses in a function are known as the ____________ .

Arguments

What form of LOOKUP is used when you have a small list of values and the values remain constant over time?

Array Form

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 end

Please see the attached workbook. When you examine the worksheet with the "Manage Rules" (conditional formatting) command, which rule is applied on cells that appear with a yellow background fill?

Below Average

When you examine the worksheet with the "Manage Rules" (conditional formatting) command, which rule is applied on cells that appear with a yellow background fill?

Below Average

With regards to Depreciation Calculation functions in Excel, which of the following are true about the function DB()?

Both A and B Computes depreciation at a fixed rate. Its arguments are: Cost, Salvage, Life, Period, [Month]

With regards to Depreciation Calculation functions in Excel, which of the following are true about the function DDB()?

Both A and B???

How are some icons hidden in Icon Set conditional formatting?

By choosing "No Cell Icon" in the Edit the Rule Description box.

11 point (pt) Calibri

By default, Excel uses __________ font?

How do you change the name of your worksheet?

By double-clicking the sheet-tab

non visible

By pressing the spacebar, the contents of a cell seem to erase because pressing the spacebar inserts a / an _______ space character

Please select the Excel function which allows us to answer the question: "How many days of stock data do I have?"

COUNT()

The ___ function can be used to find the number of characters in a word or text string.

COUNTCHARS() NUMLETTERS() Correct Response LEN() NUMCHARS()

Which of the following functions can be used to count specific types of errors?

COUNTIF

What formula is used to count specific types of errors?

COUNTIF ()

To calculate the cumulative interest paid between any two payment periods, use the ______________ financial function.

CUMIPMT()

In Excel, in order to calculate the cumulative principal paid between any two payment periods, what function will you use?

CUMPRINC()

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) Correct Response can do all of the above

By default, what is Excel's font?

Calibri 11pt

WATCH WINDOW

Cannot print on a different page

A single element in a worksheet that can hold a value, some text, or a formula is called a(n) ____?

Cell

Please see the attached workbook. Currently, some conditional formatting rules are applied to the range A1:A25. Suppose the same formatting is applied to the range B1:B25. Which of the following will be true?

Cell B6 will have a circle shaded white.

Which of the following concepts are useful in understanding relationships between cells and formulas?

Cell Dependents

Examine the attached workbook. How can you resize the chart title in Chart 1 of Sheet 1?

Change font size

What is NOT true about Autocorrect?

Changes made by the Autocorrect feature cannot be undone.

What would you do, while creating a table, to display your Table Headers?

Check 'My table has headers

What would you do, while creating a table, to display your Table Headers?

Check 'My table has headers'

What would you do, while creating a table, to display your table header?

Check 'My table has headers'

What command would you use to import a CSV file?

Choose Data --> Get External Data --> From Text

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 the cells that contain incorrect entries

The data (tab) -> Circle invalid data?

Ciricles appear that have false entitities?

What command (within Conditional Formatting) is used to delete all the rules relevant to a particular group of cells

Clear Rules

What command (within Conditional Formatting) is used to delete all the rules relevant to a particular group of cells?

Clear rules

To gain access to the table tools/design tab in Excel?

Click anywhere

How do you set up a data validation (using the data validation dialog box) that restricts data entry to a text of a specified length?

Click the Settings tab (in the Data Validation box). In the Allow box, select 'Text Length', 'equal to', and specify the length.

How do you set up a data validation (using the data validation dialog box) that restricts data entry to a time less than a specified time?

Click the Settings tab (in the Data Validation box). In the Allow box, select 'Time'. In the Data box, select 'less than' and enter the end time.

How do you set up a data validation (using the data validation dialog box) that restricts data entry to a date greater than a specified date?

Click the Settings tab (in the Data Validation box). In the Allow box, select 'date'. In the Data box, select 'greater than', and specify the start date.

Which of the following is NOT a way to add a new worksheet to a workbook?

Click the insert new worksheet control located to the right of the last sheet tab Correct Response Press Shift + F12 Right-click a sheet tab, choose Insert from shortcut menu and select the worksheet icon from the general tab Press Shift + F11

Which rule category under Conditional formatting can be used if you want to apply graphic bars directly in the cells, proportional to the cell's value?

Color scales Icon sets Correct Response Data Bars Highlight Cells rules

What does the following formula =A1=C12 do?

Compares cell A1 with cell C12. If the cells are identical, the formula returns TRUE; otherwise, it returns FALSE

___ 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).

Compound

What is not inside the Spelling Dialog Box?

Conditional Formatting

Which of the following is NOT one of the buttons in the Spelling dialog box in Excel?

Conditional Formatting

If you want to create a rule to highlight cells that contain the word 'good', which option would you select?

Conditional Formatting -> Highlight Cells Rules -> Text that contains

Two criteria are checked for this formula to include a value in the total: a) the product category myst be "Beverages" and b) suppliers must be "4"

Consider the following formula =SUMIFS(Inventory_Cost, Category, "=Beverages", Suppliers,"*4*"). Which of the following statements is TRUE?

Correct Two criteria are checked for this formula to include a value in the total, the product category must be "Beverages", and the supplier must be "4"

Consider the following formula: =SUMIFS(Inventory_Cost, Category,"=Beverages", Suppliers,"*4*"). Which of the following statements is true?

arguments

Consider the function =COUNTIF(A1:A200,">200"). The information between the parentheses in a function are known as the _______.

What option in the Table Tools / Design tab removes a table without losing the data in it?

Convert to Range

=LOWER()

Converting a string to purely lowercase

What is the purpose of the function TIMEVALUE()?

Converts a time in the form of text string (e.g., "18:45") to a serial number

What is the purpose of the TIME function?

Converts input hours, minutes and seconds in serial numbers

What is the purpose of the function TIME()?

Converts the input hours, minutes & seconds to a serial number

To open an HTML file stored on a Web server using Excel, what needs to be done?

Copy the URL and paste it into the Name Field in the Open dialog box.

By default, Excel uses __________ font

Correct Response 11 point (pt) Calibri 12 pt Times New Roman 11 pt Comic Sans MS 12 pt Arial

With a single IF() function, how many actions can you ask Excel to carry out?

Correct Response 2 (i.e., an action if the criteria is true, or an an action if the criteria is false) As many as you want, this is why IF() functions are so difficult to understand 3 (i.e., an action if the criteria is any one of true, false, unknown) 1 (i.e., an action if the criteria is true)

A Loan amortization schedule in Excel is :

Correct Response A table of values that shows various types of information for each payment period of a loan A table of values that shows various types of information for any one payment period of a loan A table of values that shows only Present Value information for the entire payment period of a loan None of the above

How do you group Pivot Table items manually?

Correct Response After creating the pivot table, select the items to be grouped and then choose Options (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 above

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?

Correct Response Annuity Loan Payment Interest

To gain access to the Table Tools / Design tab in the Excel Ribbon when working with tables, you should do the following:

Correct Response Click anywhere on the table Such a tab does not exist, the only tab you can get access to is: Table Format/Design Click on the Home tab on the Ribbon, and check the appropriate box to make it appear The Table Tools/Design tab does not provide any useful commands, so it is pointless trying to access it

What is the purpose of the function TIME()?

Correct Response Converts the input hours, minutes & seconds to a serial number Returns the current time Returns the serial number of the current date and time Converts a time in the form of text string (e.g., "18:45") to a serial number

Which of the following is NOT considered a style attribute?

Correct Response Formula Pattern Font Alignment

Correct Response Function Library group (on the) Formulas Formulas group (on the) Functions Functions group (on the) Insert Calculations group (on the) Formulas

Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet?

Correct Response HYPERLINK() SUM() INDEX() MATCH()

To hide rows or columns you can click on ____ then ____

Correct Response Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list) Click on the Row/Column header, (then) right-click and choose Delete right-click on the cell, (then) choose "Insert..." Home (ribbon tab), (then) Paste (in Clipboard group)

With regards to Depreciation Calculation functions in Excel, which of the following are true about the function SYD()?

Correct Response It allocates a large depreciation in the earlier years of an asset's life It refers to the Systematic Yield Depreciation method, and shows an asset yield bar chart to display the falling amounts of asset values This method of calculating depreciation uses random numbers to fairly distribute depreciation over the lifetime of an asset None of the above

Which of the following is a correct syntax for VLOOKUP function?

Correct Response VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) VLOOKUP(lookup_value, table_array, row_index_num, range_lookup) Either of the two previous choices will work None of the above

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?

Correct Response circles appear around cells that contain incorrect entries. squares appear around cells that contain incorrect entries. incorrect entries are italicized. correct entries are italicized.

How do you copy a Pivot table to a new worksheet?

Correct Response 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 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 Alt+P followed by Ctrl+C (for Pivot-Copy).

Worksheet names can have upto ______ characters

Correct Response 31 34 28 32

When you save an Excel workbook for viewing on the Web, you have two options. What are they?

Correct Response An HTML file and a single file web page An HTML file and a JPEG file a TEXT file and a JPEG file a .xlsx file and a TEXT file

What is a keyboard shortcut for creating a table? (hint: you can test the options in Excel)

Correct Response Ctrl+L Ctrl+Alt+L Ctrl+F2 Alt+T

To close an Excel workbook, you can click on ____ followed by _____

Correct Response File, Close Ctrl, S Ribbon, Formulas File, Save

A group of cells is referred to as which of the following?

Correct range

Which of the following is the keyboard shortcut to enter the current date into a cell in Excel ?

Ctrl + ; (semicolon)

Which keyboard shortcut is used to select all cells in an open Excel worksheet?

Ctrl + A

In Excel in order to avoid incomplete calculation errors and to ensure that formulas are fully calculated, you would press _________

Ctrl + Alt + Shift + F9

In Excel in order to avoid incomplete calculation errors and to ensure that formulas are fully calculated, you would press _________ .

Ctrl + Alt + Shift + F9

In Excel in order to avoid incomplete calculation errors and to ensure that formulas are fully calculated, you would press _________ .

Ctrl + Alt + Space +F9 Correct Response Ctrl + Alt + Shift + F9 Ctrl + Alt + Fn + F9 Ctrl + Alt + Shift + F10

Which of the following keyboard shortcuts can be used in Excel 2013 to create a table?

Ctrl + L

Which of the following is the keyboard shortcut that creates a new blank workbook in Excel?

Ctrl + N

Which of the following procedures will NOT close your Excel workbook?

Ctrl + S

Which of the following keyboard shortcuts will add the current time to a cell in an Excel worksheet?

Ctrl + Shift + :

When entering / editing an Array formula, press _________ to enter the formula (or Excel does not recognize the formula as an Array formula).

Ctrl + Shift + Enter

which keyboard shortcut "cuts" the current selection of cells to both the Windows and Office clipboards?

Ctrl + X

To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________.

Ctrl+Alt Correct Response Shift+F2 F5 Ctrl+A

Which of the following shortcuts allows you to enter the same data into multiple selected cells?Example: In a new Excel worksheet select cells A1:B4 and type "MIS111". The text is initially entered into cell A1. What could you do to replicate the same text "MIS111" in all the selected cells?

Ctrl+Enter

What is the shortcut to open the Find and Replace dialog box with the "Replace" tab open?

Ctrl+H

What is the shortcut key to create hyperlink when you select an object or a cell?

Ctrl+K

What is a keyboard shortcut for creating a table? (hint: you can test the options in Excel)

Ctrl+L

What is the keyboard short to make a table?

Ctrl+L

Which of the following keyboard shortcuts can be used in Excel 2013 to create a table?

Ctrl+L

Which of the following keyboard shortcuts can be used in Excel 2016 to create a table?

Ctrl+L

THREE WAYS OF EDITING THE CONTENTS OF A CELL

DOUBLE CLICK THE CELL, SELECT THE CELL AN PRESS F2, SELECT THE CELL THAT YOU WANT TO EDIT AND THEN CLICK INSIDE THE FORMULA BAR TO EDIT THE CELL CONTENTS IN THE FORMULA BAR

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

Which rule category under Conditional formatting can be used if you want to apply graphic bars directly in the cells, proportional to the cell's value?

Data Bars

Which rule category under Conditional formatting can be used if you want to apply graphic bars directly in the cells, proportional to the cell's value?

Data bars

Which rule category under conditional formatting can be used if you want to apply graphic bars directly in the cells value?

Data bars

-----

Data validation on a cell or range can do which of the following?

Correct can do all of the above

Data validation on a cell or range can do which of the following?

In the EmpID table, enter the salary in cell H8 as 100. Given the error message, why do you think Excel does not let you input that value?

Data validation settings require the salary value to be greater than the hours worked value, 120.

Grand totals in pivot table are used to...?

Display totals for cells in columns or rows of a pivot table

Displays the current date

When you select a cell in the Total Row, a drop-down arrow appears in the cell. Click the arrow and you can select the "Count" formula. What does this formula do?

Displays the number of entries in the column.

Displays the sheet's name

Displays the total number of pages to be printed

Displays the workbook's complete filepath Correct Response It is not a valid code Displays the workbook name Shows tab characters using the "->tab" code in your printout

#DIV/0! errors

Division by zero is not a valid operation

Which of the following is *NOT* a type of cell reference?

Dynamic

Which of the following is NOT true when it comes to adding new worksheets within an existing Excel workbook?

Excel does not allow you to add additional worksheets to a workbook that has been saved previously

What are the #REF! and #NUM! that may appear in a cell?

Excel error codes

To make the end result attractive and more functional

Excel formatting tools are used to achieve which of the following?

What is the result of the formula =2^3 * (,10*10

Excel tells you that the formula contains an error

What would happen if you used a formula to perform arithmetic operations on dates, but one of the dates was typed in a format that Excel does not recognize as a date?

Excel will display an error value

What does XML stand for?

Extensible makeup language

When working with data returned from a Query Wizard, you can adjust a variety of properties of an external data range. This can be done using the ______________.

External Data Properties Dialog Box

If your formula uses named cells or ranges, you can either type the name in place of the address, or choose the name from a list and have Excel insert the name for you automatically. You can also press the ____ button which displays the Paste Name Dialog box. If no names are defined, pressing this button has no effect.

Examine the image in the question. You can store the results of a formula entered in the formula bar (e.g., =(25*10)/2 ) as an actual number (e.g., 125) in the active cell. What keyboard shortcut do you need to press while editing the cell to do this?

If you want to print a copy of a worksheet, which of the following would you choose?

File (tab) -> Print and then choose the PRINT command.

To close an Excel workbook, you can click on ____ followed by _____

File, Close

To save a copy of your workbook with a different name, you can click on ____ followed by ____

File, Save As

To save a copy of your workbook with a different name, you can click on ____ followed by _____

File, Save As

Which of the following command sequences will allow you to save a copy of your workbook with a different name?

File, Save As

To save a copy of your workbook with a different name, you can click on ____ followed by _____

File, Save as

Which of the following command sequences will allow you to save a copy of your workbook with a different name?

File, Save as

The Analysis Toolpak can be activated through which sequence of steps?

File-->Options-->Add ins -->Manage Excel Add-Ins --> Go

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

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

The _____________ command can extract specific pieces of information from cells, as long as the values are stored consistently.

Flash Fill

The _____________ command can extract specific pieces of information from cells, as long as the values are stored consistenty.

Flash Fill

What is the Flash Fill feature?

Flash Fill uses pattern recognition to extract data (and also concatenate data).

"text, false"

For VLOOKUP() and HLOOKUP(), the lookup_value argument can include wildcard characters if the lookup_value argument is ______ and the range_lookup argument is ________

Number cells containing the current date

For a range called Data, what does the function =COUNTIF(Data, TODAY()) return?

Examine the attached workbook. Which ribbon tab provides the option to resize charts? (e.g., change the height & width)

Format

Which of the following is NOT considered a style attribute?

Formula

Which of the following is not considered a style attribute?

Formula

How do you change the calculation mode while dealing with a calculation intensive data table?

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

Types of data contained in cell Excel Worksheet

Formulas, Text, and Numeric

The HYPERLINK function syntax has two arguments. Which argument has the jump text or numeric value that is displayed in the cell?

Friendly_name

How can you create a drop-down list in a cell?

From data validation box select settings tab- list option- specify range that contains the list using source control (must be in same workbook)

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 (command group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that has a value 'Tucson' for 'City' column.

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

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 has a value that ends with 'm' for 'Name'.

From the Column to Filter list, select Name. In the right panel, select 'ends with' 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 'is less than' from the first drop-down list and then select '30000.0' from the second drop-down list

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 has 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

The HLOOKUP and LOOKUP functions can be found under Formulas (tab) ->_________.

Function Library (command group) -> Lookup & Reference

Function Library group (on the) Formulas

The easiest way to enter a function into a formula?

Function library group (on the formulas)

What function helps ensure that the formula will continue to reference the intended cells if the pivot table layout is changed?

GETPIVOTDATA

Horizontal text alignment options:

General, Left (Indent), Center, Right (Indent), Fill, Justify, Center Across Selection, Distributed (Indent)

which function is used to create a shortcut that opens a document stored on a network server, an intranent, or the internet

HYPERLINK()

To hide rows or columns you can click on ____ then ____

Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list)

Which of the following is a VALID method of hiding rows or columns?

Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list)

How do you find all the cells that have conditional formatting?

Home (tab) -> Editing (command group) -> Find & Select -> Conditional Formatting

Please see the attached workbook, go to Sheet1. How do you find all the cells that have conditional formatting?

Home (tab) -> Editing (command group) -> Find & Select -> Conditional Formatting

The Background color of a cell can be changed by choosing

Home (tab) -> Font (command group) -> Fill color

How will you return a cell formatted as a date or time, back to its default or original format?

Home (tab) -> Number (command group) ->Number Format ->General also Press Ctrl +Shift + ~

Please examine any Excel 2013 (Windows) workbook to better answer this question: You can create your own style through which of the following steps?

Home (tab) -> Styles (command group) -> Cell Styles

Returning a cell formatted as a date or time, back to its default or original format:

Home(tab)-->Number(command group)-->Number Format-->General

How do you specify a Highlight Cell rule?

Home- Styles- Conditional Formatting- Highlighted cell rules- between

How can you create your own formatting rule?

Home- Styles- Conditional Formatting- New Rules-

How do you specify a Top Bottom Rule?

Home- Styles- Conditional Formatting- Top Bottom Rule- Top/Bottom 10, Top/Bottom 10%,, Above/Below average ect.

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

How do you copy a Pivot table to a new worksheet?

Correct After creating the pivot table, select the items to be grouped and then choose Options (tab) under PivotTable Tools and select Group (group) -> Group Selection

How do you group Pivot Table items manually?

Which function returns the contents of a cell at the intersection of a specified row and column from a range?

INDEX()

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?

INFEROR

yes

IS THIS STATEMENT ACCURATE? Different table formatting styles can be previewed by mousing over different options within "Table Styles" (i.e., you can see what the appearance will be like without actually applying a new style).

What functions can be used to count error values?

ISERROR (), ISERR (), ISNA ()

Which of the following function(s) can be used to count error values in a range?

ISERROR() ISERR() ISNA()

Please see the image below. Which of the following conditional formatting styles is used in creating rules for the "Defects" column?

Icon sets

Which of the following statements are true?

If I have a students workbook with their name

If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet

Which of the following statements are true?

If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet

Which of the following statements is true about the COUNTA() function?

If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet

Which of the following statements is true?

If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet

In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram?

Input Range and Bin Range

Which of the following is one of the ways to create a Pivot Chart?

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.

If you need to display lots of text in a worksheet, you can consider using a text box. How will you insert a textbox?

Insert (tab) -> Text (command group) -> Text Box

After you have selected the data to create a chart, which of the following is the correct sequence of options needed to create a clustered column chart?

Insert --> Charts --> Column --> Clustered Column

The basic formula for computing simple interest is:

Interest = Principal * Rate * Term

a #REF! error is?

Invalid

Please see the attached Excel workbook. Suppose you are on Sheet1 and you have selected cells A5:B5. What happens when you insert a background image through the following sequence of steps? Page Layout (tab) -> Page setup (command group) -> Background?

It adds a background image to the currently active worksheet

With regards to Depreciation Calculation functions in Excel, which of the following are true about the function SYD()?

It allocates a large depreciation in the earlier years of an asset's life

It is not a valid code

Which of these dates will Excel store as a text string rather than a serial number?

June 23 2014

Characters in a word or text?

LEN

Function can be used to count word or text string

LEN()

The ___ function can be used to find the number of characters in a word or text string

LEN()

The ___ function can be used to find the number of characters in a word or text string.

LEN()

the ___ function can be used to find the number of characters in a word or text string

LEN()

Which of the following functions looks in a one-row or one-column range (lookup_vector) for a value (lookup_value) and returns a value from the same position in a second one-row or one-column range (result_vector).

LOOKUP

Arguments of LOOKUP() function?

LOOKUP(lookup_value, lookup_vector, [result_vector])

Which of the following options provides correct syntax for the LOOKUP function?

LOOKUP(lookup_value,lookup_vector,[result_vector])

What is the correct syntax for the LOOKUP function?

LOOKUP(lookup_value,lookup_vector,result_vector)

What is the correct syntax for the LOOKUP function?

LOOKUP(lookup_vector,lookup_value,result_vector) Correct Response LOOKUP(lookup_value,lookup_vector,result_vector) LOOKUP(lookup_vector,result_vector) LOOKUP(lookup_value)

Every chart type has a set of ___ that you can choose from (with settings for chart elements such as a title , data labels, axes, and so on).

Layouts

Which function returns the relative position (in a range / table) of a cell corresponding to a specified value?

MATCH()

In order to keep track of all conditional formatting used in a worksheet, we use the ___________ menu command under Conditional Formatting.

Manage Rules

Table

Microsoft Office Excel can automatically generate a data form (which allows for convenient data entry, among other things) for your range or _______________.

At which locations can you find the following formatting features? Increase Decimal, Format Painter and Font Color

Mini Toolbar

How do you remove a filter in the PivotTable Field List?

Move the cursor over the filter icon next to the field name you want to remove the filter from. Click the arrow that appears, and then choose the Clear Filter From option.

Which of the following is NOT an advantage of using defined range names to work with ranges in Excel?

Moving to areas of the worksheet is controlled

If cell A1 contained the text MR. JOHN Q. PUBLIC, what would the formula (=PROPER(A1)) return?

Mr. John Q. Public

No, a chart can be embedded or places in a sheet separate from that of its data source

Must a chart be embedded in the worksheet as its data source?

In Excel, the ___________ function calculates the difference between 2 dates excluding weekend days.

NETWORKDAYS()

------

NOTE: missing problem set 10/ problem set 11 is duplicate of another ps

Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet?

NOW()

Which function returns the number of payment periods for a loan, given the loan's amount, interest rate, and periodic payment amount?

NPER()

DATA TYPES

NUMERIC, TEXT, AND FORMULA

If you want to trace cell relationships for formulas, which of the following commands / options could you use?

Name manager Spelling Both options A and B *None of the options provided is correct*

What command is used to *create your own rule* in conditional formatting?

New Rule

What command is used to *create your own rule* in conditional formatting?

New rule

Does deleting a cell by pressing the Delete key also delete the conditional formatting on the cell?

Please examine the attached workbook. The chart was created based on data in cells A1 through C6. If new data is added in cells A7 through C7, will the chart include the new data automatically? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/PS2_Chart1.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq

When you print the worksheet with graphic background what happens?

No background is printed, even though the background is displayed onscreen.

Which of the following is NOT one of the four quick margin settings for adjusting margins?

No margins

Can you apply conditional formatting to an Excel table, which already has its own formatting?

No you cannot No you can only apply to a sheet in the workbook Correct Response Yes you can Yes you can apply it to a table, but not to multiple tables on your worksheet

Must a chart be embedded in the same worksheet as its data source?

No, a chart an be embedded or placed in a sheet separate from that of its data source.

Convert the data on Sheet1 into a table. Next answer the following question: If you proceed to remove duplicate values using the "Remove Duplicates" feature in "Table Tools", will Excel remove one of the two table rows?

No, these are not considered duplicates by Excel. To remove the duplicate data you must ensure that the date in the second row is formatted the same way as the date in the first row.

Charts are dynamic, i.e., they change as the underlying data changes. Is this an accurate statement?

No, they are static by default, and you must press "Ctrl-F7" to update them. Correct Response Yes, by default they are dynamic. This is untrue, because it would lead to charts with circular references. Charts placed in a separate sheet from the data are not dynamic, but if it is placed in the same sheet as the source data--it allows the chart to change if the data changes.

Can you print data in an Excel Form attached to a table?

No, you cannot

In Excel, dates are stored as _________.

Numbers

In Excel, dates are stored as?

Numbers

TAPPED DIALOG BOX

OFFERS SEVERAL PANELS IN A SINGLE DIALOG BOX

Which of the following are NOT contained in a cell Excel Worksheet: a. Objects b. Formulas C. Text D. Numeric

Objects

Which of the following types of data may NOT be contained in a cell in an Excel worksheet?

Objects

Please see the attached Excel workbook image. The cells highlighted in yellow contain a Total Row. How can such 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."

How can you add a dollar ($) symbol to values in a column as seen in the "Grand Total" column?

One way to do this is by going through the "Format Cells" dialog, and selecting "Accounting" (with a $ symbol and 2 decimal places) as your formatting option from within the Number tab

What happens if the following value filter is added to the existing report? Value filter: Sum of Sales is greater than 5000.

Only one record corresponding to the 'CA' state remains as its total sales amount is greater than 5000.

You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks?

Passwords are case sensitive

Which of the following is true about Web queries?

Performing Web queries copies information from an HTML file into the current workbook

Which of the following is true about Web queries?

Performing Web queries creates a new HTML file Correct Response Performing Web queries copies information from an HTML file into the current workbook Web queries are not useful in Excel because external data can change None of the above

Data retrieved from Web Queries do not include ___ and contents of ______.

Pictures, scripts

Which of following is NOT one of the methods for 'Conditional Formatting' in Excel?

Pie Chart

Which of the following is NOT one of the methods for 'Conditional Formatting' in Excel?

Pie Chart

A _________ is a graphical data representation of data displayed in a pivot table.

Pivot Chart

COUNT()

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.

Which of the following is an object that contains the actual data in the chart?

Plot area

By default, Excel prints worksheets in ___________ orientation

Portrait

Web queries

Preforming workbook

What keys do you need to press to select a newly created table so that it can be copied or moved to a different worksheet?

Press Crtl+A

Which of the following is NOT a way to add a new worksheet to a workbook?

Press Shift + F12

Which of the following is not a way to add a new worksheet to a workbook?

Press Shift+12

When do you use the following sequence of commands: PiovtTable tools --> Analyze-> pivot table--> options--> Generate get pivot data?

Prevent excel from using GETPIVOTDATA function

What is the general formula to calculate compound interest (i.e., the future value of an investment that includes both Principal and interest)?

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

________ can be used to check whether all of the columns fit on one page before printing an Excel worksheet.

Print Preview

In the following formula, Excel sums up the data in which range? =SUMIF(Products!I2:I50,"=Canned Fruit & Vegetables",Products!F2:F50)

Products!F2:F50

What is the primary purpose of "Protected View?"

Protect you from malware

A -> Z (ascending sort) and Z-> A (descending sort) are both referred to as ________ sorting options. They are both available by clicking a single button in the Excel ribbon (Data tab).

Quick

A->Z (ascending sort) and Z->A (descending sort) are both referred to as..?

Quick

Which function can be used to make crude/simple vertical separators between cells?

REPT()

What are relative/absolute/mixed references in a formula?

Relative: The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column. By default, Excel creates relative cell references in formulas. ■ Absolute: The row and column references don't change when you copy the formula because the reference is to an actual cell address. An absolute reference uses two dollar signs in its address: one for the column letter and one for the row number (for example, $A$5). ■ Mixed: Either the row or the column reference is relative, and the other is absolute. Only one of the address parts is absolute (for example, $A4 or A$4).

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

Which of the following options can be used to quickly remove cell data validation for a cell?

Select the cell and open the data validation bdialog box (data (tab) -> data tools)... on the settings tab, click clear all

Please see the attached workbook image. What sequence of steps will allow you to format the data into a table?

Select the data, then click "Table" under the "Insert" tab of the Excel Ribbon

How do you copy a pivot table to a new sheet?

Select the entire table, choose home, paste values

How can you select noncontiguous or multi-sheet ranges?

Select the first range (or cell). Then press and hold Ctrl as you click and drag the mouse to highlight additional cells or ranges. Press Shift and click the Manufacturing sheet tab. This step selects all worksheets between the active worksheet (Totals) and the sheet tab that you click — in essence, a three-dimensional range of cells. When multiple sheets are selected, the workbook window's title bar displays [Group] to remind you that you've selected a group of sheets and that you're in Group mode

In the attached image, how would you modify the pivot table to include only Joseph and Lawrence from the Middle region and nothing else?

Select the required cells and then right-click, point to Filter, and click Keep Only Selected Items.

Which of the following methods is a VALID way to get into cell edit mode?

Selecting the cell that you want to edit, and then clicking in the Formula bar

What does selecting the option "Current region" within the: Go To Special Dialog Box (Home (tab) -> Editing (command group) -> Find & Select) do?

Selects a rectangular range of cells around the active cell

In the Share tab of Backstage View, which of the options is available?

Send using E-mail

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 ______.

Sheet1!#REF!

Which of the following command sequences will allow you to save a copy of your workbook with a different name?

Shift + S

To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________

Shift+F2

To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________.

Shift+F2

To view or edit a comment... keyboard shortcut?

Shift+F2

Which of the following is a shortcut key used to calculate the formulas in the active worksheet?

Shift+F9

What keyboard shortcut can you press to move to the PREVIOUS field in an Excel data Form ?

Shift+Tab

To create a single file web page, what file format does a user have to select from the Save as Type drop-down list?

Single File Web Page

Which of the following is NOT a type of ANOVA (Analysis of Variance) performed using tools in the Analysis ToolPak?

Single-factor without replication

A(n) _________ is an interactive control that makes it easy to filter data in a pivot table?

Slicer

An interactive control that makes it easier to filer a pivot table?

Slicer

______ is an interactive control that makes it easy to filter data in a pivot table.

Slicer

Which of the following is TRUE about sorting a table?

Sorting a table re-arranges the rows based on the contents of a particular column.

To change color in a Sparkline you

Sparkline tools-->Design(tab)-->Style(group)

How can you use formulas for data validation rules?

Specify a logical formula (returns TRUE or FALSE) in the data validation box by selecting the Custom option from the Allow drop-down list of the settings tab. Enter the formula directly into the Formula control, or enter a reference to a cell that contains a formula.

Row 5 is hidden and contains a word in cell A5. What is the result of using the spell checker when the word is hidden?

Spell checker will be able to check the spelling of the word because the contents of hidden rows and columns are checked.

Which of the following statements is true? They all relate to the formula: =IF( OR(Category="Pasta",Category="Soups"), Inventory_Cost,0 )

Such IF() statements are too complicated for use in business. They are only used by research instituteions like NASA. Correct Response 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. This type of IF() functions with an embedded OR() is no longer supported in Excel 2013

What types of error does excel identify?

Syntax, incorrect reference, semantic, circular, array formula entry, logical errors

In Excel 2013, in order to change the date's default format, one needs to change ___________ settings.

System-wide (i.e., all of Windows)

The ____ is a function that will allow you to display a value (after conversion to a string) in a specific format, for example with a thousands comma separator and a currency ($) symbol.

TEXT()

SHORT-CUT MENUS

THE BOX THAT APPEARS WHEN YOU RIGHT CLICK (COPY/PASTE, LOOKUP, ECT.)

What function removes leading and trailing spaces from a cell?

TRIM

Microsoft Office Excel can automatically generate a data form (which allows for convenient data entry, among other things) for your range or _______________.

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 Column Above

When importing a text file into Excel, the _____________ helps specify how the data will be imported.

Text Import Wizard

When importing a text file into Excel, the _____________ helpsspecifies how the data will be imported.

Text Import Wizard

When do you get a "#NAME?" error value?

Text in a formula is not enclosed in quotation marks

Which of the following options is TRUE about the different lookup functions?

The CHOOSE() function returns a specific value, corresponding to the index number, from a supplied list of values.

count a function

The COUNTA function counts the number of cells that are not empty in a range. If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet

Range (and) Criteria

The COUNTIF() function used for single-criterion counting formulas takes two arguments (or parameters) ______ and ____.

Excel formatting tools are available at the following locations:

The Format Cells dialog box, the Home Tab of the Ribbon, the Mini toolbar

How can you combine the MATCH() and INDEX() functions to lookup values?

The MATCH function returns the relative position of a cell in a range that matches a specified value. The syntax for MATCH is MATCH(lookup_value,lookup_array,match_type). The INDEX function returns a cell from a range. The syntax for the INDEX function is INDEX(array,row_num,column_num)

MATCH() function

The Microsoft Excel MATCH function searches for a value in an array and returns the relative position of that item.

LEN()

The ___ function can be used to find the number of characters in a word or text string.

Flash Fill

The _____________ command can extract specific pieces of information from cells, as long as the values are stored consistenty.

Examine the attached Excel file. In row 4, we have two different ways of displaying the August 25 date. Which of the following is TRUE about these dates?

The data stored in the two cells A4 and B4 are identical

A formula bar does not display what from a date formula?

The date's serial number, need to find it by using nondate format

Function Library group (on the) Formulas

What part(s) of a table can be selected?

The entire table, an entire row, or an entire column from a table can be selected

The formula =COUNTIF(range, "???") returns the number of cells in the range containing exactly ______ characters of text.

#NAME? errors

The formula contains an undefined range or cell name. ■The formula contains text that Excel interprets as an undefined name. A misspelled function name, for example, generates a #NAME? error. ■The formula contains text that isn't enclosed in quotation marks. ■The formula contains a range reference that omits the colon between the cell addresses. ■The formula uses a worksheet function that's defined in an add-in, and the add-in is not installed

What is the Formula Evaluator?

The formula evaluator lets you see the various parts of a nested formula evaluated in the order in which the formula is calculated.

Consider the following formula: =SUMIFS(Inventory_Cost, Category,"=Beverages", Suppliers,"*4*"). Which of the following statements is true?

The formula looks for a supplier with an ID of: *4*, or a product category of Beverages Correct Response Two criteria are checked for this formula to include a value in the total, the product category must be "Beverages", and the supplier must be "4" The formula is used to generate a Histogram The formula syntax has an error

A formula refers to a value in the Total Row of a table. What will happen to the formula result if the option for "Total Row" (from the Excel ribbon: Table Tools / Design tab) is unchecked?

The formula returns an error.

Please see the attached workbook. To round off B11 cell to four decimal places what formula would you use?

The formula: =ROUND(B11,4)

The "0th" day of next month represents?

The last day of the current month

Save a query so that you can reuse it

The last step in the Query Wizard, "Finish", lets you do which of the following?

Please look at the attached worksheet. What is the result of the following formula? =COUNTIF(A1:A10, MODE(A1:A10))

The number of cells containing the mode within the cell range A1 - A10

examle_COUNTIF.xlsx Please look at the attached worksheet. What is the result of the following formula? =COUNTIF(A1:A10, MODE(A1:A10))

The number of cells containing the mode within the cell range A1 - A10

Optional

The range_lookup parameter in VLOOKUP and HLOOKUP function is ________

The "Go to Special" dialog box in Excel allows a user to select all of the following EXCEPT:

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

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" can appear anywhere within the cell.

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

How can you display data labels in a chart?

To add labels to data series in a chart, select the series and click the Add Elements button on the right side of the chart. Place a check mark next to Data Labels. Click the arrow next to the Data Labels item to specify the position for the label

hold the "Ctrl" key while dragging the chart to the new location

To create a duplicate copy of a chart (on same Excel worksheet, but different location), you can activate the chart and take what action?

To hide rows or columns you can click on ____ then ____ a. Home (ribbon tab), (then) Format (in the Cells group) -> Hide & Unhide (from the drop down list) b. Click on the Row/Column header, (then) right-click and choose Delete c. right-click on the cell, (then) choose "Insert..." d. Home (ribbon tab), (then) Paste (in Clipboard group)

To hide rows or columns you can click on ____ then ____ a. Home (ribbon tab), (then) Format (in the Cells group) -> Hide & Unhide (from the drop down list)

There are four boxes/areas below the PivotTable Field List. Which of the following is NOT one of these four boxes?

Tools

Vertical text alignment options:

Top, Bottom, Center, Justify, Distributed

Which rule category does the following conditional formatting rule belong to? "Highlight the top 10 items"

Top/Bottom rules

Which of the following reviewing features does Excel offer to you?

Translation into classical (Shakespearean) English Turnitin analysis for plagiarism detection Correct Response A proofing tool to check spelling A grammar and writing-style suggestions tool

Consider the following formula: =SUMIFS(Inventory_Cost, Category,"=Beverages", Suppliers,"*4*"). Which of the following statements is true?

Two criteria are checked for this formula to include a value in the total, the product category must be "Beverages", and the supplier must be "4"

Consider the following formula: =SUMIFS(Inventory_Cost, Category,"=Beverages", Suppliers,"*4*"). Which of the following statements is TRUE?

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 be "4"

Consider the following formula: =SUMIFS(Inventory_Cost,Category,"=Beverages"?

Two criteria are checked.... "4"

Which is NOT a feasible option to prevent cell contents from being printed?

Use the custom number format that consists of two colons.

How do you open the office keyboard?

Use the dialog launcher on the bottom right of the Home ➪ Clipboard group to toggle the Clipboard task pane on and of

Which of the following is an advantage of using Excel tables?

Using table and column names in formulas provides greater flexibility as extending a table does not require re-writing of formulas

How can a manual Page Break be removed from a worksheet?

Using the: Reset All Page Breaks command

Which of the following is a correct syntax for VLOOKUP function?

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Which of the following options provides correct syntax for the VLOOKUP function?

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

correct syntax for VLOOKUP function?

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

which of the following is a correct syntax for vlookup function

VLOOKUP(lookup_value, table_array, col_index_num_range_lookup)

Which of the following is the correct syntax for VLOOKUP function?

VLOOKUP(lookup_value,table array, col_index_num,range_lookup)

Argument of VLOOKUP() function?

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VDM:

Variable Declining Balance

Which of the following option sequences will arrange all open non-minimized Excel workbook windows side-by-side on the screen?

View --> Arrange All

Fill in the blanks: Conditional formatting effects help us _______ values in cells making it easier to locate data, find exceptions and spot ______ quickly.

Visualize, trends

To find a working date that falls after a number of days (e.g., 12 business days after some date), you typically use this function:

WORKDAY()

Paste values and number for formatting

What does the middle button (circle in red) in the row of "Paste Value" do?

A comment exists for that cell

What does the small red triangle on the upper right corner of cell B1 denote?

A value from the lookup_vector

What will be returned if an Excel LOOKUP() based formula has an entry for the lookup_value and the look_up vector, but no entry for a result_vector parameter?

What will be the grade of Adams if the following formula is used in the cell C2? =LOOKUP(B2,{45,50,60,70,80,90},{"F","E","D","C","B","A"})

90,000*.05 =4,500

What will be the result of this formula? =IF(A1<100,000, A1*5%, A1*7.5%). If the cell A1 has a value of 90,000?

"an error because the formatting of the word "South-Africa" is different than the one found in the table

What would you get in E4 if you typed in "South-Africa" in cell E3?

How do you use the chart customization buttons?

When a chart is selected, three buttons appear to the right of the chart (see Figure 20.4). These buttons, when clicked, expand to show various options. The icons are ■ Chart Elements: Use these tools to hide or display specific elements in the chart. Note that each item can be expanded to show additional options. To expand an item in the Chart Elements list, hover your mouse over the item and click the arrow that appears. ■ Chart Styles: Use this icon to select from prebuilt chart styles or change the color scheme of the chart. ■ Chart Filters: Use this icon to hide or display data series and specific points in a data series, or hide and display categories. Some chart types do not display the Chart Filters button.

Displays the sheet's name

The range will contain a gradient between the two specified colors.

When a range is formatted using a 2-color scale, what happens?

Landscape

When choosing a page orientation, ____ is one of the two valid options.

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

Excel applies currency formatting to the new value in that column cell

When you apply currency formatting to a Table column and then add a new row to the table, what happens to data entered in the new cell?

What of the following is FALSE about filtering a table?

When you copy data from a filtered table, both visible data and rows hidden by filtering are copied.

Below Average

When you examine the worksheet with the "Manage Rules" (conditional formatting) command, which rule is applied on cells that appear with a yellow background fill?

Correct An HTML file and a single file web page

When you save an Excel workbook for viewing on the Web, you have two options. What are they?

filter data

When you specify which rows should be included in your query (selection criteria), you are satisfying which step of the Query Wizard?

-----

When you use the HLOOKUP() function, which of the following parameters represents the row number from which the matching value will be returned?

Page Break Preview

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?

PPMT()

Which financial function returns the principal part of a loan payment for a given period, assuming constant payment amounts and a fixed interest rate?

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

Which formula would you use if you want to accept only text that begins with a specific character 'a' for a given cell 'A1'?

HYPERLINK()

Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet?

INDEX()

Which function returns the contents of a cell at the intersection of a specified row and column from a range?

=IF(B10>100,"true","false")

Which of the following IF() functions evaluate as being TRUE?

Charts, Shapes

Which of the following do Theme effects operate on?

?NULL?

Which of the following does the search string ~?NULL~? match when entered in the find and replace dialog box?

#NUM!

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.

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

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?

=D5*D6*D7

Which of the following formulas will correctly calculate simple interest?

=COUNTIF(E2:E8,0)

Which of the following formulas, when entered in cell B12, will give the total number of items for which there was no discount offered (i.e., discount is 0)

=SUM(F2:F8)

Which of the following formulas, when entered in cells B12, will give you the total discounted cost of all the items purchased?

------

Which of the following function(s) can be used to count error values in range?

Dynamic

Which of the following is *NOT* a type of cell reference?

Use the Review (Proofing) tool of spelling Error Frequency per worksheet.

Which of the following is NOT a method of creating frequency distributions?

Press Shift + F12

Which of the following is NOT a way to add a new worksheet to a workbook?

Formula

Which of the following is NOT considered a style attribute?

Simplified Layout

Which of the following is NOT one of the worksheet views offered by Excel?

Performing Web queries copies information from an HTML file into the current workbook

Which of the following is TRUE about Web queries?

Correct All of the above statements are TRUE.

Which of the following is TRUE about a Watermark in Excel?

You must enter it manually

Which of the following is TRUE about the DATEDIF() function?

Changes to graphs and formulas cannot be made to an HTML file created from Excel.

Which of the following is TRUE regarding the HTML files that Excel creates?

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Which of the following is a correct syntax for VLOOKUP function?

Bottom

Which of the following is an INVALID horizontal text alignment option provided in Excel?

15 points

Which of the following is the default row height for Excel 2016?

Correct Performing Web queries copies information from an HTML file into the current workbook

Which of the following is true about Web queries?

A name is being applied to a range B4:E4. This name "UA", can be used in formulas anywhere in the current workbook.

Which of the following is true for the new name being defined?

Ctrl +PageUp

Which of the following keyboard shortcuts can be used to activate a different sheet?

Correct select the cell and open the Data Validation dialog box (Data (tab) -> Data Tools (group)). On the Settings tab, click Clear All.

Which of the following options can be used to quickly remove data validation for a cell?

=A1>=$A$1

Which of the following options, when used as a formula in the Custom Data Validation criteria for cell range A1:A10, enables the user to enter a value in A1:A10 only if it's greater than or equal to the value in A1?

Correct A proofing tool to check spelling

Which of the following reviewing features does Excel offer to you?

"," (comma)

Which operator in Excel combines multiple range references into a single reference ? (e.g., if I want to sum up the values in non-contiguous cell-ranges across the worksheet)

View Side by Side

Which option provided in the View ribbon tab is best to compare two open Excel workbooks simultaneously in Excel 2016?

cells_sales.xlsx Please see the attached workbook. If you copy cell "B6" and paste it as a formula in cell "C6," will it produce the correct total for Year 2?

Yes, the formula range will be adjusted automatically

Different table formatting styles can be previewed by mousing over different options within "Table Styles" (i.e., you can see what the appearance will be like without actually applying a new style). Is this statement accurate?

Yes, the statement is accurate

Which of the following is TRUE about Pivot tables?

You cannot add new rows or columns to the Pivot table You cannot change any of the calculated values You cannot enter formulas within the pivot table *All of the answers provided are TRUE*

Regarding the search-and-replace features provided by the "Find and Replace" dialog box, what is NOT true?

You cannot replace a type of formatting in a cell with another type of formatting.

Which of the following is TRUE about the DATEDIF() function?

You must enter it manually

#NUM! errors

You pass a nonnumeric argument to a function when a numeric argument is expected (for example, $1,000 instead of 1000). You pass an invalid argument to a function, such as attempting to calculate the square root of a negative number. This formula returns #NUM!: =SQRT(-12) A function that uses iteration can't calculate a result. Examples of functions that use iteration are IRR and RATE. A formula returns a value that is too large or too small. Excel supports values between -1E-307 and 1E+3

Which of the following characters is VALID for use in an Excel workbook file name?

_ (underscore)

Which of the following is a VALID range name in an Excel workbook?

_2ndyear

Compound

_____________ 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).

The formula: =CHAR(CODE("A")+32) returns which of the following?

the formula =char(code("A")+32) returns which of the following

A single element in a worksheet that can hold a value, some text, or a formula?

a cell

Which of the following best describes what a Sparkline is?

a mini-chart that is displayed in a single 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

Which of the following reviewing features does excel offer?

a proofing tool to check spelling

which of the following reviewing features does excel offer you

a proofing tool to check spelling

A group of cells is referred to as which of the following?

a range

Examine the 6th argument in the formula below. What kind of argument is it? =SUM(B1, 5, 6, 7, SQRT(4), A1:A5, TRUE)

a range reference

If one attempts to format a serial number that falls outside the range of supported dates, the value displays as __________.

a series of hash (#) marks

A Loam amortization schedule in Excel is: a. A table of values that shows various types of information for each payment period of a loan b. A table of values that shows various types of information for any one payment period of a loan c. A table of values that shows only Present Value information for the entire payment period of a loan d. None of the above

a. A table of values that shows various type of information for each payment period of a loan

How do you group Pivot Table items manually? a. After creating the pivot table, select the items to be grouped and then choose Option (tab) under PivotTable Tools and select Group (group) -> Group selection b. 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 c. select columns in the pivor table and right click on them and choose the option to "Cluster Group d. None of the above

a. After creating the pivot table, select the items to be grouped and then choose Option (tab) under PivotTable Tools and select Group (group) -> Group selection

When you save an Excel workbook for viewing on the Web, you have two options. What are they? a. An HTML file and a single file web page b. An HTML file and a JPEG file c. a TEXT file and a JPEG file d. a .xlsx file and a TEXT file

a. An HTML file and a single file web page

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? a. Annuity b. Loan c. Payment d. Interest

a. Annuity

What would you do, while creating a table, to display your Table Headers? a. Check 'My table had headers' b. Check 'Display headers' c. Uncheck 'Hide table headers' d. Uncheck 'include names'

a. Check 'My table has headers'

The easiest way to enter a function into a formula is to use Formula AutoComplete (the drop-down list that Excel displays while you type a formula). Another way to insert a function is to use the ______ on the _________ tab. a. Function Library group (on the) Formulas b. Formulas group (on the) Functions c. Functions group (on the) Insert d. Calculations group (on the) Formulas

a. Function Library group (on the) Formulas

The easiest way to enter a function into a formula is to use Formula AutoComplete (the drop-down list that Excel displays while you type a formula). Another way to insert a function is to use the ___________________ on the _____________________ tab. a. Function Library group (on the) Formulas b. Formulas group (on the) Function c. Functions group (on the) Insert d. Calculations group (on the) Formulas

a. Function Library group (on the) Formulas

How do you copy a Pivot table to a new worksheet? a. 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 b. select Insert (tab) -> Tables (group) -> Pivot Table c. select Insert (tab) -> Copy Pivot table. Then select a new worksheet and press Ctrl + V d. Select the entier table and choose Alt+P followed by Ctrl+C (for Pivot-Copy)

a. 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

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.

absolute

how do you group pivot table items manually

after creating the pivot table, select the items to be grouped and then choose options under pivottable tools and select group -> group selection

Data validation on a cell or range can do which of the following?

all of the above

If you write a lookup formula to look up the text 'education', the formula considers which of the following a match?

all of the above

In context of the MATCH() function, which of the following is FALSE?

all of the above

Which of the following is TRUE about Pivot tables?

all of the above

if you write a lookup formula to look up the text 'education', the formula considers which of the following a match

all of the above

which of the following is true about pivot tables

all of the above are true

What does the WEEKDAY function return when you provide a date as an argument?

an integer between 1 and 7

there is a type of investment in which you make a stream of fixed payments. what is this type of investment called

annuity

Pivot Chart and Pivot Table

are joined in a two-way link, so when making structural or filtering changes to one the other changes along with it

By default, Page Breaks __________ displayed in the Normal view of worksheet.

are not

Consider the function =COUNTIF(A1:A200, ">200"). The information between the parentheses in a function are known as the ____________ .

arguments

What form of LOOKUP is used when you have a small list of values and the values remain constant over time?

array form

Consider the HLOOKUP function in Excel. If the "range_lookup" argument is set to TRUE, we need to sort the first column of the range in ____________ order

ascending

Using the =NA() function in a cell, makes it clear that data is not _______ and hasn't been ______ accidentally.

available, deleted

In Excel in order to avoid incomplete calculation errors and to ensure that formulas are fully calculated, you would press ________________. a. Ctrl + Alt + Space + F9 b. Ctrl + Alt + Shift + F9 c. Ctrl + Alt + Fn +F9 d. Ctrl + Alt + Shift + F10

b. Ctrl + Alt + Shift + F9

In the Excel Backstage view, which of the following pieces of information about your workbook are available? a. Number of colors and fonts used b. Date the workbook was created c. Total time (in minutes) spent editing your workbook d. Your Windows password

b. Date the workbook was created

What does XML stand for? a. XY Match Lookup b. Extensible Markup Language c. eXcel Macro Lookup d. Excel Math Lookup

b. Extensible Markup Language

Which of the following statements is true? They all relate to the formula: =IF( OR(Category="Pasta",Category="Soups"), Inventory_Cost,0 ) a. Using a nested IF() in a formula is not recommended because the formula result is different based on the IQ level of the user. b. 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 c. This type of IF() functions with an embedded OR() is no longer supported by Excel 2013 d. Such IF() statements are too complicated for use in business. They are only used by research institutions like NASA.

b. 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

Which of the following is true about Web queries? a. Performing Web queries creates a new HTML file b. Performing Web queries copies information from an HTML file into the current workbook c. Web queries are not useful in Excel because external data can change d. None of the above

b. Performing Web queries copies information from an HTML file into the current workbook

To create a hyperlink to a specific location in a Microsoft Word document, you must use a bookmark to define the location you want to jump to in the document. Which of the following is the right option to create a hyperlink to the bookmark 'QrtlyProfits' in the document 'Annual Report.doc' located at http://example.microsoft.com? The link text to be displayed is "Quarterly Profit Report". a. =HYPERLINK("[Example.xlsx]F2", "Quarterly Profit Report") b. =HYPERLINK("[http://example.microsoft.com/Annual Report.doc]QrtlyProfits", "Quarterly Profit Report") c. =HYPERLINK(,"[Annual Report.docx]QtrlyProfits","Quarterly Profit Report") d. =HYPERLINK("QrtlyProfits","Quarterly Profit Report")

c. =HYPERLINK("Annual Report.docx]QrtlyProfits","Quarterly Profit Report")

Which of the following reviewing features does Excel offer to you? a. Turnitin analysis for plagiarism detection b. Translation into classical (Shakespearean) English c. A proofing tool to check spelling d. a grammar and writing-style suggestions tool

c. A proofing tool to check spelling

Which rule category under Conditional formatting can be used if you want to apply graphic bars directly in the cells, proportional to the cell's value? a. Color scales b. Icon sets c. Data Bars d. Highlight Cells rules

c. Data Bars

Which of the following is NOT considered a style attribute? a. Alignment b. Pattern c. Formula d. Font

c. Formula

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

c. LEN()

Grand Totals in Pivot Tables are used to _____________________

change the totals for different transactions (e.g., a sale) Correct Response display totals for all cells in column (or row) of a pivot table return the dates of grand (over $1,000) transactions recalculate the pivot table after making changes to the source data

Which of the following option is NOT available after right clicking on an Excel worksheet Tab?

changing the font color in the worksheet

Which of the following option is NOT available after right clicking on an Excel worksheet tab?

changing the font color in the worksheet

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.

What command (within Conditional Formatting) is used to delete all the rules relevant to a particular group of cells?

clear rules

To gain access to the Table Tools/ Design Tab in the excel ribbon when working with tables, you should do the following:

click anywhere on the table

How do you change Excel's calculation mode?

click formulas then calculation then calculation options

A bar chart is essentially which of the following chart types rotated 90 degrees clockwise?

column

column charts and bar charts

column charts: up and down bar chart: side to side (horizontal)

Please refer to the figure. The address of the active cell consists of which of the following?

column letter, row number

A calculated item must reside in the ________ labels, ______ labels or ___________ area of a pivot table.

column, row, filter

The HLOOKUP function searches for data in ______.

columns Correct Response rows external databases external workbooks

CONCATENATE()

combines its arguments into a single text string

timeline:

conceptually similar to a slicer; controls design to simplify time-based filtering in a pivot table

With a single IF() function, how many actions can you ask Excel to carry out? a. As many as you want, this is why IF() functions are so difficult to understand b. 3 (i.e. an action if the criteria is any one of true, false, unknown) c. 1 (i.e., an action if the criteria is true) d. (i.e., an action if the criteria is true, or an action if the criteria is false)

d. 2 (i.e., an action if the criteria is true, or an action if the criteria is false)

What does the following formula return? =FIND("m",Big Mama Thornton",1) a. 8 b. 6 c. 5 d. 7

d. 7

If you write a lookup formula to look up the text 'education', the formula considers which of the following a match? a. EDUCATION b. Education c. education d. All of the above

d. All of the above

Which of the following is TRUE about Pivot tables? a. You cannot add new rows or columns to the Pivot Table b. You cannot change any of the calculated values c. You cannot enter the formulas within the pivot table d. All of the above are true

d. All of the above are true

Which of the following is TRUE about a Watermark in Excel? a. A watermark is an image (or text) that appears on each printed page b. A watermark can be a faint company logo, or a word such as DRAFT. c. Excel doesn't have an official command to print a watermark, but you can add a watermark by inserting a picture in the page header or footer. d. All of the above statements are TRUE.

d. All of the above statements are TRUE.

In context of the MATCH() function, which of the following is FALSE? a. Match returns the cell contents (e.g., a product name) when it detects two cells with duplicate contents b. A #VAL error value is returned if the MATCH function is unsuccessful in finding a match c. The syntax of match is: MATCH (lookup_value_arrow, row_offset) d. All of the options are false

d. All the options are false

A single element in a worksheet that can hold a value, some text, or a formula is called a(n) _________? a. Entity b. Component c. Element d. Cell

d. Cell

Which of the following is *NOT* a type of cell reference? a. Relative b. Absolute c. Mixed d. Dynamic

d. Dynamic

To save a copy of your workbook with a different name, you can click on ________ followed by ________. a. Data, Create Copy b. Ctrl, A c. Ribbon, Save d. File, Save As

d. File, Save As

Which of the following statements is true? a. The Analysis Toolpak lets you generate random numbers and histograms only, hence it is not very useful b. You can use the FREQUENCY() function in Excel to find Fourier wave frequencies c. The main difference between SUMIF() and COUNTIF() is that the former is used with multiple criteria, but the latter (i.e., COUNTIF() ) only allows a single criteria d. If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet

d. If I have a worksheet with a student names (First, Middle, Last), you can use the COUNTA() function to find how many student names

What is the correct syntax for the LOOKUP function? a. LOOKUP(lookup_value) b. LOOKUP)lookup_vector,result_vector) c. LOOKUP)lookup_vector,lookup_value,result_vector) d. LOOKUP)lookup_value,lookup_vector,result_vector)

d. LOOKUP)lookup_value,lookup_vector,result_vector)

Consider the following formula: =SUMIFS(Inventory_Cost, Category,"=Beverages",Suppliers,"*4*"). Which of the following statements is true? a. The formula looks for a supplier with an ID of: *4*, or a product category of Beverages b. The formula syntax has an error c. The formula is used to generate a Histogram d. Two criteria are checked for this formula to include a value in the total, the product category must be "Beverages", and the supplier must be "4"

d. Two criteria are checked for this formula to include a value in the total, the product category must be "Beverages", and the supplier must be "4"

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. can do all of the above

d. can do all of the above

which rule category formatting can be used if you want to apply graphic bars directly in the cells, proportional to the cells value

data bars

Input messages and error alerts (for data validation) appear only when ___.

data is typed directly into the cells

What command (within Conditional Formatting) is used to delete a specific rule from a worksheet?

delete rule

While creating a parameterized web query the ampersand character (&) is used to ______.

delimit each name/value pair

Grand Totals in Pivot Tables are used to _____________________

display totals for all cells in column (or row) of a pivot table

Grand Totals in Pivot Tables are used to _____________________.

display totals for all cells in column (or row) of a pivot table

grand totals in pivot tables are used to ______

display totals for all cells in column (or row) of a pivot table

The Filter Button does what when a column is sorted?

displays a different graphic, in the header row, to remind you that the table is sorted by that column

What are data bars?

displays of horizontal bars directly in the cell; the length of the bar is based on the data of the cell

DRAWBACK pivot table

does not update automatically when information changes in source data

Which of the following is *NOT* a type of cell reference? -mixed -dynamic -relative -abstract

dynamic

Which of the following is NOT a type of cell reference?

dynamic

which of the following is not a type of cell reference

dynamic

A(n) _________ is an interactive control that makes it easy to filter data in a pivot table?

easy filter reverse pivot table Correct Response slicer formula

How do enable/disable Excel's automatic error checking feature?

enable background error checking check box found on the formulas tab of the excel options dialog box

Where do you find the command to add a pivot table on your worksheet?

excel ribbon: insert(tab)->tables

what does xml stand for

extensible markup language

Excel uses column headers to create labels for each _____ on data entry form.

field

A pivot table calculated field consists of a calculation that involves other _______.

fields

To close an excel workbook?

file, close

to close an excel workbook, you can click on ____ followed by ______

file, close

To save a copy of your workbook under another name?

file, save as

To save a copy of your workbook with a different name, you can click on ____ followed by _____

file, save as

to save a copy of your workbook with a different name, you can click on ______ followed by _______.

file, save as

What does the Excel keyboard shortcut Ctrl+Alt+F9 do?

forces a complete recalculation of all formulas in all open workbooks.

which of the following statements is true? they all relate to the forumla: If( 0r(category="pasta", category=soups

in this formula, the second statement in the or is only checked/evaluated in the first part evaluates to false

What is the Power Utility Pack?

includes tools that help with data cleaning such as text tools

Slicer:

interactive control that makes it easy to filter data in pivot table

With regards to the Depreciation Calculation functions in Excel, which of the following are true about the function SYD()? a. It allocates a large depreciation in the earlier years of an asset's life b. If refers to the Systematic Yield Depreciation method, and shows an asset yield bar chart to display the falling amounts of asset values c. This method of calculating depreciation uses random numbers to fairly distribute

it allocates

with regard to deprecation calculation functions in excel, which of the following are true about SYD()

it allocates a large deprecation in the earlier years of an assets life

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

What is referred to as the "0th" day of next month while working with dates and times in Excel?

last day of current month

Fill in the blank: Every chart type has a set of ____ that you can choose from (with settings for chart elements such as a title, data labels, axes, and so on).

layouts

A Vertical Page Break is always inserted on the _______ of the selected column in a worksheet

left

Sparkline Tools-->Design-->Group-->Axix command?

lets you override automitic behavior and control of minimum and maximum value for each Sparkline or for a group

Fill in the blanks: A cell in an Excel worksheet is identified by its address, which consists of a column ___ and row ___.

letter, number

In order to keep track of all conditional formatting used in a worksheet, we use the ___________ menu command under Conditional Formatting.

manage rules

Middle point is not...

one of the options for highlighting certain data points in a sparkline

one-way

You select a cell with data "120" in an Excel worksheet and press the "Delete" key on your keyboard. Which of the following holds TRUE?

only the contents of the cell are removed

You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks?

passwords are case sensitive

which of the following is true about web queries

performing web queries copies information from an HTML file into the current workbook

Data retrieved from Web Queries do not include __________ and contents of __________.

pictures, scripts

A calculated item, unlike a calculated field, does not appear in the _________ Field List.

pivotable

Which of the following methods will NOT move or copy a worksheet?

press keyboard shortcuts Ctrl + C and Ctrl + V in order

What is the Paste Special dialog box?

provides several pasting options

Fill in the blank: The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______.

range (and) criteria

The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______.

range (and) criteria

MATCH() function:

returns the relative position (in a range/table) of a cell corresponding to a specified value

Adding comments to a cell is available through which tab of the Excel ribbon?

review

Which of the following is NOT one of the VLOOKUP function arguments?

row_index_num

The HLOOKUP function searches for data in ___.

rows

The HLOOKUP function searches for data in ______.

rows

In Excel, the Ctrl + Spacebar (keyboard shortcut) is used to ______.

select a column

How can you select special types of cells?

select a range, and choose Home ➪ Editing ➪ Find & Select ➪ Go to Special to display the Go to Special dialog box,

Adding one or more slicers to worksheet you

select an cell from pivot table,Intersert(tab)-->Filters(group)-->Slicer, Insert slicers dialog box appears

Which of the following options can be used to quickly remove data validation for a cell?

select the cell and open the Data Validation dialog box (Data (tab) -> Data Tools (group)). On the Settings tab, click Clear All.

which of the following options can be used to quickly remove data validation for a cell

select the cell and open the data validation dialog box-> data tools. on the settings tab, click clear all

How do you copy a Pivot table to a new worksheet?

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

How do you copy a pivot table to a new worksheet

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

how do you copy a pivot table to a new worksheet

select the entire table and choose home-> clipboard-> copy. then select a new worksheet and choose home-> clipboard->paste->paste values

How do you name a range of cells?

select the range to name and then click the range box under the formulas tab and press enter to create the name

The formula '=NOW()-TODAY()' displays current time as a ___________ without a related date.

serial number

What is the Format task pane?

sets the priorities of a selected chart element

All manual page breaks are shown as _______ lines instead of ____ lines (when you use the view mode of Page Break Preview).

solid, dashed

Highlight Cell Rules and Top/Bottom Rules use a single type of formatting to highlight ________ cell(s) based on the condition or requirement.

specific

Please refer to the figure. Which of the following View ribbon tab options was used to display the horizontal bar between the nonconsecutive rows containing "Astoria, OR" and "Victoria, TX"?

split

What types of files can Excel open?

spreadsheets. database, text, and HTML files

The real power of data validation becomes apparent when you use data validation formulas. The formula that you specify must be a logical formula that returns either TRUE or FALSE. If the formula evaluates to TRUE, what does it mean?

the data is considered valid and remains in the cell

A _____ data table in Excel shows the result of a single calculation for different values of two input cells

two-way

What best describes the connection between a Pivot Table and a Pivot Chart?

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

How do you use named ranges in a formula?

type the name in place of the address or choose the name from a list and have Excel insert the name for you automatically. Select the name from the drop-down list or press F3

Which of the following methods will keep a heading at the top of a table visible while you scroll through rows lower down in the worksheet?

use Freeze Panes

Which of the following is NOT a method of creating frequency distributions?

use the Review (Proofing) tool of Spelling Error Frequency per Worksheet

can you apply conditional formatting to an excel table, which already has its own formatting

yes you can

When you try to import data from MS-Access into Excel, which of the following is TRUE?

you can import a table or query

Pivot table requires

you to put data in a form of a regular database table

Lihat semua set pelajaran

MIS Lab Final Review (Practice Test), MIS Lab Final Review (PS 1-12), mis lab my problem sets, MIS 111 Lab Final Questions from Problem Sets, MIS 111 Problem Sets 1-12, MIS111L Final Study Guide Fall 2015, MIS 111L past problem sets, MIS111L Practice...

Set pelajaran terkait

Prep-U Cancer& MS

Quiz in Bootstrap

Cellular Respiration: Harvesting Chemical Energy & Photosynthesis: Converting Light Energy to Chemical Energy Test

Exam Part 1

Real Estate Exam - Chapters 1-3

Chapter 8 The Health Care Team

GEOL102L

Quiz

US to 1877 (1)

Chapter 9 MGMT

Famous Names in Psychology

George Washington as President

Statics Exam 2- TH questions

Psych Chapter 6 Prep-U

Indiquez de quelle(s) couleur(s) sont les choses suivantes.

PS102 Midterm

BIO 311D Exam 4

Multiplication Facts for Jakob 2-12

IST 220 Quiz #3 Study Guide

Materials and Processing Exam 2 (Chap. 3 & 6)