mis 112
Examine the attached workbook. The data represents the amount of money a customer has spent, in a quarter, to buy a product. Create a PivotTable based on the (entire) table of data in the Sales worksheet. For Row Labels, choose the Product column. Include Columns C, D, E and F in the Values-area. What is the total revenue (sales in dollars) generated in Qtr 4 for the Products 'Aniseed Syrup', 'Boston Crab Meat' and 'Camembert Pierrot'? Pivot_table_test.xlsx
5156
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 formulas will display both the current date and time when entered into a cell in an Excel worksheet?
= NOW()
formulas_cell_references.xlsx Examine the attached Excel file. When the formula in "D2" is copied to the cells below it, cell "D3" will contain what formula?
=(B3*C3)*$B$7
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?
=A1>=$A$1
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
datavalidation.xlsx Please see the attached workbook. In the Student Rank table, notice that H15 and H19 have the same value. Since two students cannot have the same student ID, what formula would you use in the Data validation settings in this case to make sure duplicate entries are not allowed?
=COUNTIF($H$13:$H$19,H19)=1
Advanced_aggregation.xlsx Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were sung by singers whose names begin with S?
=COUNTIF(B2:B23,"S*")
dataLOOKUP_Ex2.xlsx Please see the attached workbook. What formula (in cell B3) would return the tax rate for the income in cell B2?
=HLOOKUP(B2, E1:J3, 3)
If you place the link target in cell C1 on the same worksheet, how will you use an absolute reference to that cell as the link_location in the HYPERLINK formulas?
=HYPERLINK($C$1)
IF_formulas.xlsxYou 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")
Examine the attached worksheet. We want to display in cells H2 to H8 the names of the items whose quantity purchased is greater than the Average quantity reported in cell B10. If the quantity purchased is equal or lower, we want to display 0. Which formula, entered in H2 and extended to H8, will achieve this? Formulas_Test_v2.xlsx
=IF(B2>$B$10, A2,0)
Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the entry in cell A1 is an odd number?
=ISODD(A1)
To get rid of ALL blank spaces in a string (e.g., in "Jo hn Doe "), which Excel function will you use?
=SUBSTITUTE()
Examine the attached file: PS1_formulas.xlsx 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)
Advanced_aggregation.xlsx Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were sung by either Eminem or Spears?
=SUM(COUNTIF(B2:B23,{"Eminem","Spears"}))
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?
=TIMEVALUE(LEFT(Y3,2)&":"&RIGHT(Y3,2))
To eliminate redundant blank space from the start or end of a string (e.g., " Tucson "), which Excel function will you use?
=TRIM()
dataLookup_eg1.xlsx 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)
Which of the following does the search string ~?NULL~? match when entered in the find and replace dialog box?
?NULL?
What does it mean when Excel displays the following error? #DIV/0!
A number is divided by zero!
Which of the following statements is true?
A workbook can have many worksheets.
Ex3.xlsx Please refer to the attached workbook. The ______ , ________ and ________fields are in the Column, Rows and Values section respectively.
AcctType, branch, count of amount
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 is the purpose of the function TIME()?
Converts the input hours, minutes & seconds to a serial number
Which of the following is the keyboard shortcut to enter the current date into a cell in Excel ?
Ctrl + ; (semicolon)
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
Examine the attached Excel file PS1_formatting.xlsx In Row 2 (Col B), which of the following formatting types has been applied to "3419.25041" to make it appear as "$3,419.25" ? formatting.xlsx
Currency
What does the following formula return? =CHAR(68)
D
What is metadata?
Descriptive information about a workbook
Please refer to the figure. Which of the following Excel tabs is NOT visible in a worksheet by default?
Developer
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 command sequences will allow you to save a copy of your workbook with a different name?
File, Save As
Which of the following is one of the ways to create a Pivot Chart?
Insert (tab) -> Charts (group) -> PivotChart
When a column is sorted within a table, which of the following is true about the drop-down list in the header row?
It displays a different graphic to remind you that the table is sorted by that column
What is the purpose of the Text to Columns command?
It parses strings into their component parts.
The __________ function can be used to find the count of characters in a word or text string.
LEN()
Which of the following options provides correct syntax for the LOOKUP function?
LOOKUP(lookup_value,lookup_vector,[result_vector])
Which of the following is NOT a formatting option offered through the Mini Toolbar? (Hint: for more information on the mini-toolbar, you can right-click on an Excel cell)
Left Align
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 _________ function can return a specified number of characters beginning at any position within a string.
MID()
What is Not the purpose of using Slicers?
Make table more beautiful
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.
If cell A1 contained the text MR. JOHN Q. PUBLIC, what would the formula (=PROPER(A1)) return?
Mr. John Q. Public
When you activate a cell that contains a date, the formula bar usually shows the cell contents formatted using the following date format:
None of the answers provided is correct
Which of the following entries does Excel automatically recognize as a date (using the U.S. settings)?
None of the answers provided is correct
In the following choices, which one is not among the drop-down list of the conditional formatting on the ribbon?
Number bars
Please refer to the attached image. What is the result upon using the formula =B2-C2?
Number of days between two dates
Reading_Symbol Reference.xlsx Please refer to the attached Excel workbook. 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
Which of the following is NOT one of the methods for 'Conditional Formatting' in Excel?
Pie Chart
tables_moving_tables.xlsx For this question, please download the attached Excel workbook. Convert the data on Sheet1 into a table. Next answer the following question: What keys do you need to press to select the newly created table so that it can be copied or moved to a different worksheet?
Press Crtl+A
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
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()
Adding comments to a cell is available through which tab of the Excel ribbon?
Review
Please refer to the figure. What Excel element is shown in the red box?
Ribbon
The function ______________ updates specific characters in a string with a new value (i.e., with new text). Hint: This function is used when you know the text to be replaced but not the specific location.
SUBSTITUTE()
Examine the attached image. What will the result of the following formula be? ="Section " &mysection
Section B
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 examine the attached workbook. Given the data in Sheet1, which of the following steps should be followed to obtain the data in Sheet2 ? PS1_Sheets
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
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 procedures will NOT save (nor give you an option to save) your Excel workbook?
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
Which of the following is a shortcut key used to calculate only the formulas in the active worksheet?
Shift+F9
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
Example_6.xlsx Please see the attached workbook. 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.
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 function removes leading and trailing spaces from a cell?
TRIM
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
Please examine the attached workbook. Which company has the highest income? ( Income = Sales - Expenses ) PS2_Chart1
Tesco
When do you get a "#NAME?" error value?
Text in a formula is not enclosed in quotation marks
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? formatting.xlsx
The data stored in the two cells A4 and B4 are identical
Which of the following is TRUE about the DATEDIF() function?
You must enter it manually
What characters are NOT allowed in sheet names ?
[]
Which of the following characters is VALID for use in an Excel workbook file name?
_
The formula in the attached image shows an example of which of the following?
a circular reference warning
cells_example.xlsxPlease 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
In Step 2 of the Query Wizard, you specify which data rows should be included in your query output. How is the criteria entered?
a criterion for each column
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
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
In Excel, if one enters a time greater than ten thousand hours, it is interpreted as ________ instead of a time value.
a text string
Example_3.xlsx 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
What type of cell reference does the formula in E2 use? (refer to the attached image, the formula is highlighted)
absolute
A formula in Excel can consist of which of the following?
all of the choices provided are valid
Which of the following function(s) can be used to count error values in a range?
all of the provided choices are valid
Which of the following does the function =NOW() return?
current date and time
charts_example.xlsx Examine the attached workbook. To create a duplicate copy of a chart (on same Excel worksheet, but different location), you can activate the chart and take what action?
hold the "Ctrl" key while dragging the chart to the new location
When the value of a cell is changed (after conditional formatting is applied), the cell formatting ______ based on the conditions set.
is updated
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 Excel, what does the MODE function in a range return?
most frequently occurring value in a range
For a range called Data, what does the function =COUNTIF(Data, TODAY()) return?
number of cells containing the current date
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
When do you use the following sequence of commands: PivotTable Tools -> Analyze -> PivotTable -> Options -> Generate GetPivot Data?
prevent Excel from using GETPIVOTDATA function
A group of cells is referred to as which of the following?
range
Fill in the blank: The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______.
range (and) criteria
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
rng_range_names.xlsx Please see the attached workbook. What is the scope of the range name 'months'?
sheet 1
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
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)
In Excel, the DATE function takes _____ arguments.
3
The formula =COUNTIF(range, "???") returns the number of cells in the range containing exactly ______ characters of text
3
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
Which of the following functions can be used to count specific types of errors?
COUNTIF
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 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)
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
Examine the attached file: PS1_formulas.xlsx 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
Which of the following is the file format of the Excel 97-2003 workbook?
.xls
Examine the attached worksheet. Using the Covariance Data Analysis Tool (available through the Analysis ToolPak Add-in) on A1:C6, which of the following options is the approximate value of the covariance coefficient for Control and Relaxation Therapy? DA_Test.xlsx
1
What are the minimum and maximum zoom ranges that can be displayed in an open Excel worksheet (respectively)?
10%, 400%
Please refer to the figure. Within the "Number" tab, how many categories are available for displaying numbers?
12
Which of the following is TRUE with respect to charts?
After you create a chart, you have great deal of flexibility in customizing it.
In context of the MATCH() function, which of the following is FALSE?
All of the options are false
If you want to apply a date format to serial numbers, which of the following choices will work for you? Assume you have already done the following steps: selected a cell with the serial number, gone to the Home tab, within that the Number group, clicked the drop-down arrow in the Number format box)
All of the options provided will work
Which of the following options will return Year2016?
All the options are correct
The =SUM() function does which of the following in Excel?
Allows you to total up numbers in a specified range of cells
What is the keyboard shortcut to clear the formatting in a cell ?
Alt -> h -> e -> f
Examine the attached file: PS1_formulas.xlsx In the Top Salespersons list, which feature can be used to populate the numbers "3," "4," and "5" in cells "A17," "A18," and "A19," respectively?
Autofill
charts_example.xlsx 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
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.
COUNT()
external_ex1.xlsx Please use the attached workbook as an external data source. The workbook has a table called 'Employee'. When you import data, after selecting all the columns from the table using the query wizard (Data(tab) -> Get External Data (group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that have a value greater than 21 for 'Age' column.
From the Column to Filter list, select Age. In the right panel, select 'is greater than' from the first drop-down list and then select '21.0' from the second drop-down list
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.
Function Library group (on the) Formulas
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
Which of the followings steps are to be followed in order to display the Go to Special dialog box which is an Excel Auditing Tool? Home -> Editing -> AutoSum Home -> Cells -> Format Home -> Styles -> Conditional Formatting Home -> Editing -> Find & Select -> Go to Special
Home -> Editing -> Find & Select -> Go to Special
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 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
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
If a pivot chart is created and the underlying table is deleted, what occurs?
The pivot chart remains.
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 contain a "4"
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
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
dataLOOKUP_Ex1.xlsx Please see the attached workbook. Which LOOKUP function would be used in order to get the Tax Rate (in cell B3) for the entered income (in B2)?
VLOOKUP
A calculated field must reside in the ________ area of the pivot table.
Values
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()
Examine the attached file: PS1_formulas.xlsx 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
Is it possible to convert a cell with text data into a .png format ?
Yes
Would you be able to apply the "watch window" option in multiple sheets at the same time ?
Yes
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
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
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
To remove a single individual field from a report, _______ the check box beside the field name in the PivotTable Field List.
clear
While creating a parameterized web query the ampersand character (&) is used to ______.
delimit each name/value pair
Which of the following is NOT a type of cell reference?
dynamic
Which of the following is NOT a state an Excel workbook can be in?
expanded
When you create a PivotTable report, each column of your source data becomes a _____ that you can use in the report.
field
What does the Excel keyboard shortcut Ctrl+Alt+F9 do?
forces a complete recalculation of all formulas in all open workbooks.
