mis bs

Calculate the total yearly sales for the West Region and the South Region in cells "F5" and "F7" (shaded in orange). What is the total widget sales for all regions for the year?

$24,914

Please refer to the figure. Within the "Number" tab, how many categories are available for displaying numbers?

12

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

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)

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

31

What will be the result of this formula =IF(A1<100000,A1*5%,A1*7.5%) , if the cell A1 has a value of 90000?

4500

Please refer to the figure. How many worksheets are contained in the workbook shown?

5

What would you type in the Find and Replace dialog box (Home (tab) -> Editing (command group) -> Find & Select) to find all four-digit entries that begin with 5 and end with 2?

5??2

Which of the following formulae must be entered in cell "C8" (shaded in blue) to get the total widget sales for Quarter 2?

=SUM(C4:C7)

Examine the attached worksheet. Which of the following formulas, when entered in cell B12, will give the total discounted cost of all the items purchased?

=SUM(F2:F8)

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

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.

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

Which of the following statements is true?

A workbook can have many worksheets.

Examine the attached workbook. 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

Examine the attached workbook. To move "Chart 1" in "Sheet1" to a new Excel worksheet named "MyChart", which options will you use?

Click on: (Chart Tools) Design tab --> Move chart (Location) --> New Sheet (option)

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

Ctrl + Shift + :

Which of the following keyboard shortcuts "cuts" the current selection of cells to both the Windows and Office clipboards?

Ctrl + X

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

Ctrl+F

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 is NOT one of the Excel functions to determine whether a cell contains an error value?

ISLOGICAL

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

What is the name of the company that created Excel?

Microsoft

Please examine the attached image. A part of the workbook is boxed in red. Fill in the blanks: The boxed area is called ____. What is displayed in this boxed area when a range of cells is selected? _______

Name box. The number of rows and columns in the selection is displayed here.

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?

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?

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

Please refer to the figure. What Excel element is shown in the red box?

Quick Access toolbar

Please see the attached workbook. Which of the following steps allow you to add a comment to a specific cell? (When you move over the cell with your mouse, the comment will pop up)

Review -> Comments -> New Comment

Please refer to the figure. What Excel element is shown in the red box?

Ribbon

cumulativesum.xlsx Please see the attached workbook. Cell C7 currently has a number entered manually. This number should be replaced with a formula. What is the best formula to use in cell C7?

SUM(B$2:B7)

Please examine the attached workbook. Given the data in Sheet1, which of the following steps should be followed to obtain the data in Sheet2 ?

Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose

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

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

Shift+F9

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

Single-factor without replication

While comparing two sheets using the "View Side by Side" feature, there is an option which lets you scroll the two sheets in sync. What is that option called ?

Synchronous Scrolling

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

If the formula =ROWS(Asset) * COLUMNS(Asset) that is used to calculate the number of cells in a range named Asset doesn't work, what does it imply?

The range Asset consists of non-contiguous cells

Which of the following chart types does NOT support the Data Table chart element?

Treemap

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

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

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

Please refer to the figure. In an Excel worksheet, what column name comes directly after Column Z?

aa

What type of cell reference does the formula in E2 use? (refer to the attached image, the formula is highlighted)

absolute

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

arguments

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

change the chart type, formatting, and data series

Please refer to the figure. The address of the active cell consists of which of the following?

column letter, row number

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

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

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

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

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

range (and) criteria

What is the Skip blanks option (under Excel Paste Special option) used for while copying a range to another area?

to prevent blank cells in the copied range from overwriting existing data

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

y-Test

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

yes

Would you be able to apply the "watch window" option in multiple sheets at the same time ?

yes

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)

Examine the attached Excel file. When you copy the formula in cell "C3" to "F7", the cell in "F7" contains which of the following formulas?

=$B7*F$2

In the attached Excel workbook PS1_Comments_Calculation.xlsx , which of the following formulas can be used to calculate Sophie's total score in both the exams?

=B4+C4

Which formula can be used to find: How many songs were longer than the average length of all songs on the list?

=COUNTIF(D2:D23,">"&AVERAGE(D2:D23))

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

How can you edit the contents of cell (by entering cell-edit mode)? Choose the best answer.

All of the options provided are correct

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

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

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

Calibri, 11 point

There are two charts, Expenses_1 chart represents the data in cells A1:B13 and Expenses_2 chart represents the same data but the missing values are replaced by ZEROS. Which of the following options is the correct set of steps to obtain Expenses_2 chart from Expenses_1 chart?

Chart Tools --> Design --> Data --> Select Data --> Hidden and Empty Cells --> Zero --> OK --> OK

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