MIS 112 Problem Sets

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

#REF!

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 orange). What is the total widget sales for all regions for the year? A) $9,019 B) $13, 218 C) $16,060 D) $24,914

$24,914

Examine the attached file: PS1_formulas.xlsx What is total widget sales for the West Region for the year? A) $13,282 B) $5,129 C) $4,015 D) None of the above

$5,129

Which of the following is TRUE regarding the HTML files that Excel creates? A) Excel cannot open an HTML file. B) Formulas and charts in an HTML file created an Excel workbook can be edited. C) Changes to graphs and formulas cannot be made to an HTML file created from Excel. D) In the HTML files, formulas can be edited, but the charts appear as static graphic images.

Changes to graphs and formulas cannot be made to an HTML file created from Excel.

What command would you use to import a CSV file? A) Choose Date --> Get External Data --> From Text B) Choose Date --> Get External Data --> From CSV C) Choose Date --> Import Data --> External D) None of the provided options si correct

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

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? A) Click the settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select 'between'. B) 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. C) Click the settings tab (in the Data Validation box). In the Allow box, select 'date'. In the Data box, select 'greater than', and enter the end time. D) Home (tab) -> Editing (group) -> Find & Select -> Data Validation

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.

Ex4.xlsx Please refer to the attached workbook. What does the pivot chart depict? A) Compares the amount in the three branches on the basis of AcctType. B) Compares number of accounts in the three branches by account type. C) Compares the customer type in the three branches. D) None of the answers provided is correct.

Compares the amount in the three branches on the basis of AcctType.

If you want to create a rule to highlight cells that contain the word 'good', which option would you select? A) Conditional Formatting -> Highlight Cells Rules -> Between B) Conditional Formatting -> Highlight Cells Rules -> Less than C) Conditional Formatting -> Top/Bottom Rules -> Top 10 items D) Conditional Formatting -> Highlight Cells Rules -> Text that contains

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

What option in the Table Tools / Design tab removes a table without losing the data in it? A) Remove Duplicates B) Summarize with Pivot Table C) Convert to Range D) Delete Table

Convert to Range

themes_style_formats.xlsx Please see the attached workbook, Sheet1. The Format painter tool functionality is best described by which of the following? A) Copy the format of one cell to another B) Copy the font style properties (only) of one cell to another C) Copy the font color of one cell to another, but not other font properties D) Copy the background of one cell to another

Copy the format of one cell to another

Advanced_aggregation.xlsx Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were longer than the average length of all songs on the list? A) =COUNTIF(D2:D23,"<"&AVERAGE(D2:D23)) B) =COUNTIF(D2:D23,">"&AVG(D2:D23)) C) =COUNTIF(D2:D23,">"&AVERAGE(D2:D23)) D) =COUNTIF(D2:D23,">"AVERAGE(D2:D23))

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

To generate a series of dates 1 year apart in Excel, which of the following formulas is correct? (assuming the first date is entered in cell A1, and you are trying to compute the next year in the series) A) =DATE(YEAR(A1) + 1, DAY(A1)) B) =A + 7 C) =DATE(YEAR(A1) + 1, MONTH(A1), DAY(A1)) D) =DATE(YEAR(A1), MONTH(A1), DAY(A1) + 1)

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

Which of the following formulas returns the number of days in the corresponding month for a date in cell B2? A) =DAY(DATE(YEAR(B2), MONTH(B2)+1,0)) B) =DATE(YEAR(B2)+1, MONTH(B2)+1,0)) C) =DAY(DATE(YEAR(B2)+1, MONTH(B2)+1,0)) D) None of the answers provided is correct

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

dataLookup_eg4.xlsx 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? A) =LOOKUP(B1,H2:H7,K2:K7) B) =LOOKUP(B1,H2:H7,I2:I7) C) =LOOKUP(B1,H2:H7,J2:J7) D) =LOOKUP(B1,L2:L7,K2:K7)

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

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? A) =CASE(A1) B) =PROPER(A1) C) =PROPERCASE(A1) D) =LOWER(A1)

=PROPER(A1)

Which two wildcard characters are supported by the "Find and Replace" dialog box in an excel worksheet? A) ?,* B) *,/ C) ?,/ D) ?,^

?,*

Which of the following does the search string ~?NULL?~ match when entered in the find and replace dialog box? A) !NULL# B) ?NULL? C) ~?NULL~? D) ~NULL~

?NULL?

A pivot chart is which of the following? A) A bar that can pivot to a pie chart form with a single click B) A graphical representation of a data summarized in a pivot table C) A chart that is joined to a pivot table in a 4-way link D) A chart that cannot be used with a slicer

A graphical representation of a data summarized in a pivot table

Examine the image in the question. Which of the following is true for the new name being defined? A) A name is being applied to a range B4:E4. This "UA", can be used in formula anywhere in the current workbook. B) A new name is being created for use specifically in the current worksheet (Sheet 1), based on the given range (B4:E4). C) The UA range is being moved from cells B5:E5 to cells B4:E4. D) Three ranges, "UA", "ASU" and "NAU" are being defined as new names in the workbook.

A name is being applied to a range B4:E4. This "UA", can be used in formula anywhere in the current workbook.

What does it mean when Excel displays the following error? #DIV/0! A) A number is divided by zero! B) A number is divided by infinity! C) Unrecognized text D) None of the options provided is correct

A number is divided by zero!

Which of the following statements is true? A) A workbook can have at most 3 worksheets. B) A workbook can have many workbooks. C) A workbook can have many worksheets. D) A worksheet can have at most 3 workbooks.

A workbook can have many worksheets.

The HYPERLINK function syntax has two arguments. Which argument has the hump text or numeric value that is displayed in the cell? A) Friendly_name B) Link_location C) Name D) File

Friendly_name

Which of the following provides correct syntax for the MATCH function? A) MATCH(column-name) B) MATCH(lookup_value,lookup_vector,result_vector) C) MATCH(lookup_value,lookup_array,match_type) D) MATCH(lookup_value,match_type)

MATCH(lookup_value,lookup_array,match_type)

What is the name of the company that created Excel? A) Microsoft B) Google C) Apple D) Dell

Microsoft

cells_image.png 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? _______ A) Formula bar. Any formula in the current cell is displayed here. B) Cell box. The address of a range of cells is displayed here. C) Column box. the address of the topmost cell in a range is displayed here. D) Name box. The number of rows and columns in the selection is displayed here.

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

Does deleting a cell by pressing the Delete key also delete the conditional formatting on the cell? A) No B) Yes, but only if the data is text C) No, it only deletes the cell's style D) Yes

No

Pleas 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? PS2_Chart1 A) Yes B) Yes, but only because it is a stacked Column chart C) No D) No, this feature is only supported for the clustered Column charts

No

Which function can be used to make crude/simple horizontal separators between cells? A) REP() B) CSV() C) REPT() D) CDIV()

REPT()

The ________ function is one solution to account for floating point number errors. It also serves to round off values to the desired number of decimal places. A) DECIMAL() B) ROUND() C) AVERAGE() D) FLOOR()

ROUND()

What happens if you try to store a number greater than 15 digits in Excel? A) Replace the digits exceeding 15 with the number zero B) None of the (other) options provided is correct C) Store as is D) replaces the digits exceeding 15 with '#'

Replace the digits exceeding 15 with the number zero

What does the following formula do? =COUNTIF(Data, TODAY())) A) Returns the number of cells containing the current date in the range named Data. B) Returns the number of cells that were last changed on the current date in the range named Data. C) "Returns the number of cells containing the text "TODAY()"" in the range named Data" D) returns the value of the cells containing the current date in the range named Data.

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

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? A) Review (tab) -> Comments (command group) -> Next B) Review (tab) -> Comments (command group) -> Previous C) Home (tab) -> Navigate (command group) -> Next Comment D) Page Layout (tab) -> Search (command group) -> Jump To Comment

Review (tab) -> Comments (command group) -> Next

Please refer to the figure. What excel element is shown in the red box? A) File button B) Quick Access toolbar C) Scrollbar D) Ribbon

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. A) FIND_AND_REPLACE() B) SUBSTITUTE() C) FIND() D) SEARCH()

SUBSTITUTE()

Please refer to the figure. Which of the following is the title of the chart displayed? A) Sales per month B) Sales (in thousands) C) Month D) Series 1

Sales per month

Examine the attached image. What will the result of the following formula be? ="Section"&mysection A) Section B B) #ERROR C) "Section" & "mysection" D) 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? PS1_Sheets A) Select cells A1 through C4 in Sheet 1 -> Copy -> Transpose -> Go to Sheet2 -> Paste B) Select cells A1 through C4 in Sheet 1 -> Copy -> Go to Sheet2 -> Paste -> Transpose C) Select cells A1 through C4 in Sheet 1 -> Copy -> Go to Sheet2 -> Paste -> Formulas D) It is only possible if done manually

Select cells A1 through C4 in Sheet 1 -> Copy -> Go to Sheet2 -> Paste -> Transpose

In the attached image, how would you modify the pivot table to include only Joseph and Lawrence from the Middle region and nothing else? A) Select the required cells and then right-click, point to Filter, and click Keep Only Selected Items. B) Select the required cells and then right-click, point to Filter, and click conditional formatting. C) Set "date Filter" accordingly. D) Refresh the pivot table data source.

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

What does selecting the option "Current region" within the; Go To Special Dialog Box (Home (tab) -> editing (command group) -> Find & Select) do? A) Selects the entire array B) Selects all graphic objects on the worksheet C) Selects a rectangular range of cells around the active cell D) selects all empty cells

Selects a rectangular range of cells around the active cell

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

Shift+F2

______ is an interactive control that makes it easy to filter data in a pivot table. A) Field B) Slicer C) Row D) None of the answers provided is correct

Slicer

Which of the following is TRUE about Pivot Charts? A) Slicers also work with pivot charts B) By default, pivot charts are embedded in a new worksheet C) Pivot Charts work in Excel 2010 but not in Excel 2016 D) The default pivot chart type is a Pie Chart

Slicers also work with pivot charts

Which of the following is TRUE about sorting a table? A) Sorting a table is not possible. B) Words cannot be sorted, while numbers can be sorted. C) Sorting a table re-arranges the rows based on the contents of a particular column. D) Sorting the rows based on the background or text color of the data is not possible.

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

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"? A) Split B) New Windows C) View Side by Side D) Hide

Split

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? A) None of the (other) options provided is correct B) Side View C) Synchronous Scrolling D) Toggle

Synchronous Scrolling

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. A) TEXT() B) SPECIFIC() C) CUSTOMFORMAT() D) FMT()

TEXT()

What function removes leading and trailing from a cell? A) TRIM B) CUT C) COMPACT D) CLEAN

TRIM

Please examine the attached workbook. Which company has the highest income? ( Income = Sales - Expenses ) PS2_Chart1 A) Fry's B) Amazon C) Wallmart D) Tesco

Tesco

The "Go to Special" dialog box in excel allows a user to select all of the following EXCEPT: A) Cells that contain comments B) Blank cells C) All cells that differ from the active cell in a selected column D) The second to last cell in a selected column that contains data or formatting

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 words "budget" in certain positions within the text. Which of the following best describes the cells counted? A) The word "budget" must appear in the cells surrounded by asterisks (i.e., the * character). B) The word "budget" can appear anywhere within the cell. C) It looks specifically for cells containing budget multiplies by some number. For example, a cell containing the formula: "=budget*0.25". D) Only cells with the text "budget" at the beginning or the end of the cell, are counted. Thus a cell "budget constraint" is counted, but a cell with "Previous budget estimates" is not counted.

The word "budget" can appear anywhere within the cell.

Histogram.xlsx Examine the attached excel data file (Histogram.xlsx) and image (Histogram.png). If you used the Excel data file to generate a histogram (you can use the Analysis Toolpak for that), and compared it with the histogram in the image, which of the following options best describes what you would see? A) The histograms would be identical B) Three of the Histogram bins will have a different count C) The histograms would be identical except for the Horizontal axis labels ( taken from the "Ranges" column) D) Two of the Histogram bins will have a different count

Three of the Histogram bins will have a different count

There are four boxes/area below the PivotTable Field List. Which of the following is NOT one of these four boxes? A) Filters B) Rows C) Columns D) Tools

Tools

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)? A) HLOOKUP B) VLOOKUP C) TAXLOOKUP D) None of the options provided is valid

VLOOKUP

A calculated field must reside in the ________ area of the pivot table. A) Column B) Values C) Rows D) Report Filter

Values

When a row of data is very ____ and requires repeated horizontal scrolling, consider using a data form to ass, edit, find, and delete rows. A) Wide B) Narrow C) Transformed D) Empty

Wide

Would you be able to apply the "watch window" option in multiple sheets at the same time? A) This option was available in Excel 2013 but not in excel 2016 B) Yes C) No D) Yes, but only if there are no more than 3 sheets in the workbook

Yes

cells_sales.xlsx Please see the attached workbook. If the you copy cell "B6" and paste it as a formula in cell "C6," will it produce the correct total for Year 2? A) No, because the result will be the total for Quarter 1. B) No, because you must directly enter the formula for each quarter in cell C6. C) Yes for Excel 2013, but not for previous versions of Excel. D) Yes, the formula range will be adjusted automatically

Yes, the formula range will be adjusted automatically

What characters are NOT allowed in sheet names? A) [] B) , C) ! D) All options are correct

[]

Which of the following characters is VALID for use in an Excel workbook file name? A) _ B) : C) " D) \

_

cells_example.xlsx Please see the attached workbook. What does the small red triangle on the upper right corner of cell B1 denote? A) a comment exists for that cell B) the cell has an error C) wrong value / formula in the cell D) the cell references a missing value

a comment exists for that cell

Which of the following types 9chart types) is a VALID option in the Charts ribbon group on the Insert ribbon tab? A) all of the options provided are valid B) area C) scatter d) column

all of the options provided are valid

Which of the following does the Formula Autocomplete feature in excel take into account? A) all of the options provided for this answer are valid B) excel built-in functions C) user-defined functions D) defined names

all of the options provided for this answer are valid

Which of the following function(s) can be used to count error values in a range? A) all of the provided choices are valid B) ISERROR() C) ISERR() D) ISNA()

all of the provided choices are valid

When a range of cells is selected, which of the following occurs? A) the selected cells are all highlighted in the same color B) none of the cells is highlighted in any color C) a box marks the boundary of the selected cells, but none pf the cells are highlighted D) all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded

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

What does the WEEKDAY function return when you provide a date as an argument? A) an integer between 1 and 7 B) an alphabetical representation of the day of the week, e.g., "MON" / "TUE" C) an integer between 0 and 7 D) either 0 or 1

an integer between 1 and 7

Consider the function =COUNTIF(A1:A200, ">200"). The information between the parentheses in a function are known as the ___________. A) function signature B) dependent variables C) cell reference D) arguments

arguments

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. A) descending B) ascending C) color coded D) ranged

ascending

Data retrieved from Web Queries do not include __________ and contents of ___________. A) text, data B) text, pictures C) pictures, scripts D) text, tables

pictures, scripts

When do you use the following sequence of commands: PivotTable Tools -> Analyze -> PivotTable -> Options -> Generate GetPivot Data? A) prevent Excel from using GETPIVOTDATA function B) make use of GETPIVOTDATA function C) add GETPIVOTDATA function to the pivot table D) enable GETPIVOTDATA function for the pivot table

prevent Excel from using GETPIVOTDATA function

In Excel, the Ctrl + Spacebar (keyboard shortcut) is used to ______. A) select a row B) select as column C) select a range D) select multiple rows or columns

select a column

What does it mean when excel displays the following error? ##### A) a number is divided by a very large number that is #'d out for privacy B) the column is not wide enough to display contained data or a negative date or time is used C) unrecognized text not matching a date validation rule D) none of the options provided is valid

the column is not wide enough to display contained data or a negative date or time is used

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? A) a message box appears that displays an error message B) the data is considered valid and remains in the cell C) nothing happens D) ovals pr squares appear around cells that contain incorrect entries

the data is considered valid and remains in the cell

What is the Skip blanks option (under Excel Paste Special option) used for while copying a range to another area? A) to prevent blank cells in the copied range from overwriting existing data B) to skip pasting content into the blank cells C) to copy the blank cells from the copied range into the paste area D) None of the above

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

Which of the following is NOT a method of creating frequency distributions? A) use the Review (Proofing) tool of Spelling Error Frequency per Worksheet B) Write your own formula or function C) Use the Pivot table feature D) Use the Analysis ToolPak add-in

use the Review (Proofing) tool of Spelling Error Frequency per Worksheet

Reading_Symbol Reference.xlsx Please refer to the attached excel workbook. How can you add a dollar ($) symbol to the values in a column as seen in the "Grand Total" column? A) The simplest way is to click on the "Wrap text" button in Home tab. From the dialog box that pops up, pick the default $-symbol formatting. B) This is automatically provided to you when you make an Excel Table. C) 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 D) the correct way to do this is by going through the "Format table" menu, and selecting the option of "Number" (with 2 decimal places and a $ symbol)

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

charts_sampleData Examine the survey results in the workbook (in Sheet2). What type of chart would be most suitable to represent the survey response data? A) Pie B) Scatter C) High-Low-Close D) area (2D or 3D)

Pie

Which of the following is an object that contains the actual data in the chart? A) Plot area B) Pie area C) Bar area D) Column area

Plot area

When you specify which rows should be included in your query (selection criteria), you are satisfying which step of the Query Wizard? A) choose columns B) filter data C) sort order D) generating lookup

filter data

If the user copies a cell that does not use data validation and pastes it to a cell that uses data validation, the data validation rules are _____. A) inserted B) ignored / do not work C) shifted to the next cell in the row D) changed from a "stop" to a "warning" type of rule (in Error Alert)

ignored / do not work

While importing data from a web page into Excel, creating a refreshable Web Query is used to ______. A) keep data static B) keep data dynamic or updateable C) convert data a query D) repeat the query

keep data dynamic or updateable

Examine the attached file: PS1_formulas.xlsx 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) A) C20/F8 B) =SUM(C15:C19) C) =C20/F8 D) =PERCENTSALES(F22)

=C20/F8

Examine the attached worksheet. Using the ANOVA: Single Factor Data Analysis Tool (available through the Analysis ToolPak Add-in) on A1:C6, which of the following options is the correct value for the p-value of the data in the worksheet? DA_Test.xlsx A) 0.00388865177938274 B) 3.8333333333333 C) 2.5 D) 0.12

0.00388865177938274

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 A) 1 B) 2 C) 4 D) 3.2

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 last names contain exactly six letters? A) =COUNTIF(B2:B23,"6*?") B) =COUNTIF(B2:B23,"{6?}") C) =COUNTIF(B2:B23,"??") D) =COUNTIF(B2:B23,"??????")

=COUNTIF(B2:B23,"??????")

What are the minimum and maximum zoom ranges that can be displayed in an open excel worksheet (respectively)? A) 10%, 400% B) 10%, 500% C) 10%, 300% D) 25%, 200%

10%, 400%

Please refer to the figure. Within the "Number" tab, how many categories are available for displaying numbers? A) 12 B) 8 C) 10 D) 14

12

formulas_sum_reference.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? A) 14537.45 B) 14875.5 C) 14787.45 D) 9787.45

14787.45

dataLookup_eg7.xlsx Please see the attached workbook. If the formulas =MATCH(B2,I4:I7,0) was used in cell B#, what would be the result? A) 2 B) 3 C) 1 D) 0

2

With a single IF() function, how many actions can you ask excel to carry out? A) 1 (i.e., an action if the criteria is true) B) 2 (i.e., an action if the criteria is true, or an action if the criteria is false) C) 3 (i.e., an action if the criteria is any one of true, false, unknown) D) as many as you want, this is why IF() functions are so difficult to under stand

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

dataLOOKUP_Ex4.xlsx Please see the attached workbook. What value does the formula "=VLOOKUP("John",A2:D13,3,FALSE)" return? A) 2000 B) 1500 C) 1590 D) 1600

2000

PivotTables_Ex1.xlsx Please refer to the attached workbook. What is the TOTAL amount of Sales in the west regions of Arizona (AZ) and California (CA)? A) 2200 and 4300 respectively B) 2300 and 4300 respectively C) 2200 and 4100 respectively D) 2300 and 2200 respectively

2200 and 4100 respectively

In excel, the DATE function takes _____ arguments. A) 3 B) 2 C) 4 D) 5 (the 6th is optional)

3

The formula =COUNTIF(range,"???") returns the number of cells in the range containing exactly ______ characters of text. A) 3 B) 5 C) 4 D) 2

3

If the date serial number of March 15, 2014 is represented as 41713 then how will noon (half way through the day) be represented? A) 41713.75 B) 41713.25 C) 41713.5 D) 41713

41713.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) 45000 B) 500000 C) 4750 D) 6750

4500

Please refer to the figure. How many worksheets are contained in the workbook shown? A) 1 B) 5 C) 3 D) A1

5

Please refer to the figure. In an excel worksheet, what column name comes directly after Column Z? A) AA B) AZ C) ZA D) ZZ

AA

If you copy a formula down a column, and need a cell reference to stay the same (for example, you don't want B2 to change to B3), what type of cell reference do you need? (Example: Use of F4 key) A) Absolute B) Relative C) Frozen D) Automated

Absolute

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 assitant? A) Add a comment to the sales figure cell, and explain to your assistant what you want him to do B) Delete the sales figure for Monday (seeing the blank cell will make things clear to the assistant) C) Set a fill color of dark red for the sales figure cell D) Make a pie-chart to explain the ratio of sales of Monday to other days in the week so the assistant sees which day has the highest sale

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

An Excel formulas may return which of the following errors? A) #REF! B) #DIV/0 C) #VALUE! D) All of the above

All of the above

Which of the following functions can be used to count specific types of errors? A) COUNTA B) COUNTIF C) COUNTBLANK D) COUNT

COUNTIF

Which of the following is the default font type and font size in excel 2016? A) Calibri, 11 point B) Arial, 12 point C) Times New Roman, 10 point D) Cambria, 14 point

Calibri, 11 point

Which of the following keyboard shortcuts can be used to activate a different sheet? A) Alt + PageUp B) Alt + PageDown C) Ctrl + PageUp D) Alt + F4

Ctrl + PageUp

Which of the following procedures will NOT close your Excel workbook? A) Ctrl + S B) Ctrl + F4 C) Ctrl + W D) File -> Close

Ctrl + S

Which of the following keyboard shortcuts will add the current time to a cell in an excel worksheet? A) Ctrl + Shift + : B) Ctrl + ; C) Ctrl + ' D) Ctrl + T

Ctrl + Shift + :

When entering / editing an Array formula, press _________ to enter the formulas (or excel does not recognize the formula as an Array formula). A) Ctrl + Shift + Enter B) Alt + Ctrl + Delete C) Alt + Shift + Enter D) None of the options provided is correct

Ctrl + Shift + Enter

Please examine the attached workbook. Which conditional formatting rule os applied? PS2_Chart3 A) Custom bars B) Color scales C) Icon sets D) Data bars

Data bars

What command (within Conditional Formatting, Manage Rules) is sued to delete a specific rule from a worksheet? A) Delete Rule B) Clear Rule C) Alter Rule D) New Rule

Delete Rule

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? A) F3 B) F6 C) F8 D) F9

F9

Excel uses column headers to create labels for each _____ on data entry form. A) Record B) Data Form C) Row D) Field

Field

The Analysis Toolpak can be activated through which sequence of steps? A) File-->Options-->Add ins-->Manage Excel Add-Ins-->Go B) Data-->date Validation-->Manage Excel Add-Ins-->OK C) Tools-->Options-->Data-->What-if Analysis D) Home-->Ribbon-->Analyze-->Analysis Toolpak

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

externel_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 that starts with 'T' for 'City' column. A) From the Column to Filter list, select City. In the right panel, select 'equals' from the first drop-down list and then select 'Tucson' from the second drop-down list B) From the Column to Filter list, select City. In the right panel, select 'begins with' from the first drop-down list and then select 'T' from the second drop-down list box C) From the Column to Filter list, select City. In the right panel, select 'ends with' from the first drop-down list and then select 'T' from the second drop-down list box D) From the Column to Filter list, select Name. In the right panel, select 'equals' from the first drop-down list and then select 'Sam' from the second drop-down list

From the Column to Filter list, select City. In the right panel, select 'begins with' from the first drop-down list and then select 'T' from the second drop-down list box

The easiest way to enter a function into a formula is to use Formula Auto Complete (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

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? A) Home B) Review C) Insert D) Formulas

Home

Which of the following is a VALID method of hiding rows or columns? 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)

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

The HYPERLINK function syntax has two arguments. Which argument has the path and file name to the document to be opened? A) Link_location B) Friendly_name C) Name D) File

Link_location

Which of the following statements is true? They all relate to the formula: =IF(OR(Category="pasta",Category="Soups"), Inventory_Cost,0) A) this type of IF() functions with an embedded OR() is no longer supported in Excel 2013. B) Such IF() statements are too complicated for use in business. they are only used by NASA. C) In this formula, the second statement in the OR (i.e., checking if category = "Soups") is only checked/evaluated if the first part (i.e., checking if category = "Pasta") evaluates to FALSE. D) using a nested IF() in a formulas is not recommended because the formulas result is different based on the IQ level of the user.

In this formula, the second statement in the OR (i.e., checking if category = "Soups") is only checked/evaluated if the first part (i.e., checking if category = "Pasta") evaluates to FALSE.

When a column is sorted within a table, which of the following is true about the drop-down list in the header row? A) The header row background fill color changes to a darker shade B) It displays a different graphic to remind you that the table is sorted by that column C) There is no change to the header for that column before or after sorting D) The header row 9 (for that column) is now disabled and until the sort is cleared, no further actions are permitted

It displays a different graphic to remind you that the table is sorted by that column

charts_example.xlsx Examine the attached workbook. Which of the following occurs if you select an embedded chart of a sheet and then click on "File" --> "Print"? A) It prints only the chart on that sheet B) It prints the whole worksheet including the chart C) It asks if you wants to print all charts on all worksheets in that excel workbook D) It prints all worksheets with data linked to the current chart

It prints only the chart on that sheet

How does the Excel search/replace function work for dates? A) It searched a cell for any feasible date format including numbers and text dates, and then does a replace B) It searches for a sate as displayed in the Formulas bar, and then does a replace C) It can find dates, but not replace them D) It cannot find dates

It searches for a sate as displayed in the Formulas bar, and then does a replace

Which of the following is the MOST compelling motivation for using HLOOKUP()? A) It improves the formatting of your data and make style-usage possible B) It usually reduces duplicate data storage C) pie charts work better if the data is generated using HLOOKUP() formulas D) HLOOKUP() is superior in design to VLOOKUP()

It usually reduces duplicate data storage

The __________ function can be used to find the count of characters in a word or text string. A) COUNTCHARS() B) NUMLETTERS() C) LEN() D) NUMCHARS()

LEN()

tables_duplicate_rows.xlsx For this question, please download the attachedExcel workbook. 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? A) Yes, because Excel can see that the dates in the two cells are the same value. B) No, because dates that are a duplicate cannot be removed. C) No, because excel does not let you remove duplicates when you only have 2 rows in a table (removing a row makes it a 1 -row, which is not really a table). D) Yes, because the duplicate values are determined by the value displayed in the cell and not necessarily by the value stored in the cell. E) No, these are not considered duplicates by excel. To remove the duplicate data you muse ensure that the date the date in the second row is formatted the same way as the date in the first row.

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

Can you print data in an Excel Form attached to a table? A) No, you cannot B) Yes, you can always perform an action like Printing in Excel C) You can print forms with the "Print" button on the Form only D) Printing is disabled unless you buy the "Print Form" excel Professional feature app from Microsoft

No, you cannot

Which type of chart does not allow format changes to the Plot area? A) None of the (other) options provided is correct B) Treemap C) Bar chart D) Thermometer chart

None of the (other) options provided is correct

A bar chart is essentially which of the following chart types rotated 90 degrees clockwise? A) column B) line C) area D) surface

column

In Step 1 of the Query Wizard, you select database __________ that you want to appear in your query. A) columns B) records C) rows D) sort order

columns

Most pivot tables are created from numeric data, but pivot tables are also useful with some types of non-numeric data. because you cannot sum non-numbers, this technique involves ____________. A) rounding-off B) counting C) subtracting D) division

counting

Which of the following is NOT a state an excel workbook can be in? A) expanded B) restored C) maximized D) minimized

expanded

The ____ command is sued to display text on multiple lines in a cell. A) shrink to fit B) wrap text C) merge cells D) icon formatting

wrap text