MIS112 FINAL EXAM REVIEW

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

Examine the attached worksheet. 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)? Formulas_Test_v2.xlsx

=COUNTIF(E2:E8, 0)

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 Madonna and were three to four minutes long (inclusive)?

=COUNTIFS(B2:B23,"Madonna",D2:D23,"<=4",D2:D23,">=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)

hyperlink_examples.xlsx Please see the attached workbook. Which formula is used to provide a link to cell A4 in the same workbook with link text same as text present in A4?

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

IF_formulas.xlsx 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")

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)

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?

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

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

=NOW()

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 of the following does the search string ~?NULL?~ match when entered in the find and replace dialog box?

?NULL?

cells_example.xlsx 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

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

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

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

A number is divided by zero!

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

Filter data

What is the use of the following data validation formula: =ISTEXT(A1)?

Forces the cell A1 to accept only text

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 if category = "Soups") is only checked/evaluated if the first part (i.e., checking if category = "Pasta") evaluates to FALSE.

Fill in the blanks: A cell in an Excel worksheet is identified by its address, which consists of a column ___ and row ___.

Letter, number

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

Link_location is required, Friendly_name is optional

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.

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.

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

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

To create Sparklines, users do NOT need to enter or specify which of the following: _____.

The count and sum of cells.

The #VALUE! error can occur in the following conditions EXCEPT for:

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

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

range (and) criteria

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)

An Excel formulas may return which of the following errors?

#REF! #DIV/0 #VALUE! (All of the above)

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

0.00388865177938274

Examine the attached worksheet. Using the Correlation Data Analysis Tool (available through the Analysis ToolPak Add-in) on A1:C6, which of the following options is the approximate value of the correlation coefficient for Relaxation Therapy and Music Therapy? DA_Test.xlsx

0.05

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

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?

2

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

2000

PrintScaling_output.xlsx 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,A15%,A17.5%), if the cell A1 has a value of 90000?

4500

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

7

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

Please examine the attached image. Which of the following formulas will calculate the total number of stores across the four states?

=SUM(B2:B5)

A pivot chart is which of the following?

A graphical representation of a data summarized in a pivot table

Which of the following statements are true?

A workbook can have many worksheets.

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

AA

charts_example.xlsx Examine the atteched 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

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?

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

A formula in Excel can consist of which of the following?

All choices provided are valid. (Text, cell references, and mathematical operators)

An Excel formula may return which of the following errors?

All of the above. (#REF!, #DIV/0!, #VALUE!)

What is the keyboard shortcut to clear the formatting in a cell?

Alt -> h -> e -> f

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

Ampersand (&)

In Excel, a parameter query is a query that prompts for ______ every time it is run.

An input

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

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

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.

Ascending

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

Both A and B ( Computes depreciation at an accelerated rate. Depreciation is highest in the first period and decreases in successive periods. and Its arguments are: Cost, Salvage, Life, Period, [Factor])

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

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

COUNTIF

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

CUMPRINC()

Which of the following is the default font type and font size in excel 2016?

Calibri, 11 point

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

Which of the following is TRUE regarding the HTML files that Excel creates?

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

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

Chart area

What command would you use to import a CSV file?

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

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

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.

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

Column letter, row number

In Step 1 of the Query Wizard, you select database __________ that you want to appear in your query.

Columns

Ex4.xlsx Please refer to the attached workbook. What does the pivot chart depict?

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?

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?

Convert to Range

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

themes_style_formats.xlsx Please see the attached workbook, Sheet1. The Format painter tool functionality is best described by which of the following?

Copy the format of one cell to another

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

Counting

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

Ctrl + N

Which of the following options scrolls the screen so that the active cell is visible?

Ctrl+Backspace

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

Ctrl+L

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

Ctrl+Shift+Enter

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

D

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 is one of th eways to refresh a query used to import data from external data source using the query wizard?

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

Please refer to the attached workbook. Column A has first and last names. The goal is to extract the first name in column B as shown in cell B1. With B2 as the active cell, which of the following is the correct set of steps to achieve that? extractNames.xlsx

Data -> Data Tools -> Flash Fill

A web query contains information about the path to the ________ and can pull the _________ data directly from that source.

Data source, updated

What command (within Conditional Formatting, Manage Rules) is sued to delete a specific rule from a worksheet?

Delete Rule

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

Developer

When a header or footer section is activated, you can insert any of several element codes from the Header & Footer Elements group. Each button inserts a code into the selected section. What is the purpose of the following code: &[Date] ?

Displays the current date

When a header or footer section is activated, you can insert any of several element codes from the Header & Footer Elements group. Each button inserts a code into the selected section. What is the purpose of the following code: &[Time] ?

Displays the current time

By default, if you hide rows or columns that are used in a Sparkline graphic, the hidden data _____.

Does not appear in the Sparkline

Which of these is NOT a Bitmap based file format image?

EPS

Please refer to the figure above. What are the small bars highlighted by the orange ovals known as?

Error Bars

Which of the following is FALSE for Graphic Objects in Excel?

Excel provides a direct way to export a graphic, and you can use it to save the graphic and convert it into a clip-art

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

F4

Excel uses column headers to create labels for each _____ on data entry form.

Field

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.

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

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

You can create Sparklines for rows of data that you add later by using the _____ on an adjacent cell that contains a sparkline.

Fill handle

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

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 that starts with 'T' for 'City' column.

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

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

Which of the following steps are to be followed in order to display the Go to Special dialog box which is an Excel Auditing Tool?

Home -> Editing -> Find & Select -> Go to Special

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

The basic formula for computing simple interest is:

Interest = Principal * Rate * Term

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.

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

It searches for a date as displayed in the Formula bar, and then does a replace

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

It usually reduces duplicate data storage

A Vertical Page Break is always inserted on the _____ of the selected column in a worksheet

Left

What type of chart is shown in the figure?

Line Chart

Which of the following provides correct syntax for the MATCH function?

MATCH(lookup_value,lookup_array,match_type)

To apply specific formatting to a sparkline, use the Sparkline Color or the _____ commands.

Marker Color

Vector based images are represented by?

Mathematical equations

By default, Page Breaks __________ displayed in the Normal view of worksheet.

May or may not be

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

No margins

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

No, you cannot

Which of the following entries does Excel automatically recognize as a date (using the U.S. settings)?

None of the answers provided is correct (June 18 2014, Jun-18 2014, Jun-18/20)

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. (a standard IP address, TRIM(LEFT(LEN())), toolbar heading)

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

Numeric values

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?

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

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

Passwords are case sensitive

Examine the image provided. What does the middle button (circled in red) in the row of "Paste Value" do?

Pastes values and number formatting

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 an object that contains the actual data in the chart

Plot area

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 Ctrl+A

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

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

What is the syntax for the RATE() function?

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

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.

ROUND()

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

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.

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

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

Ribbon

Which of the following is true with regards to row and column titles and headers?

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

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

Save a query so that you can reuse it

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

Select a rectangular range of cells around the active cell

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 C4 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose

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

Send using E-mail

rng_range_names.xlsx Please see the attached workbook. What is the scope of the range name 'months'?

Sheet 1

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 and press the following keyboard shortcut: __________.

Shift+F2

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

Slicer

Which of the following is TRUE about Pivot Charts?

Slicers also work with pivot charts

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.

If you want to use commands to customize Sparklines to highlight certain parts of the data, which of the following options would you select?

Sparkline Tools -> Design (tab) -> Show (group)

Unlike charts on an Excel worksheet, _____ are not objects. Instead these are tiny charts in the background of a cell.

Sparklines

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

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

Table

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

Text Import Wizard

Which of the following is NOT one of the places to adjust print settings?

The Alignment dialog box

Which of the following options is TRUE with respect to the function PROPER() in Excel?

The PROPER() function doesn't handle names with an embedded capital letter, such as McDonald.

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

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

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

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 words "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.

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?

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?

Tools

How can a manual Page Break be removed from a worksheet?

Using the: Reset All Page Breaks command

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

When a row of data is very ____ and requires repeated horizontal scrolling, consider using a data form to ass, edit, find, and delete rows.

Wide

The ____ command is sued to display text on multiple lines in a cell.

Wrap text

Is it possible to get the data back after deleting it using the keyboard?

Yes

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

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

ignored / do not work

While importing data from a web page into Excel, creating a refreshable Web Query is used to ______.

keep data dynamic or updateable

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

Data retrieved from Web Queries do not include __________ and contents of ___________.

pictures, scripts

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

prevent Excel from using GETPIVOTDATA function

What does it mean when excel displays the following error? #####

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?

the data is considered valid and remains in the cell

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

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

y-Test

Please refer to the attached image. The sparkline displayed in cell F6 gets the data from cells A6 through E6 and displays a Win/Loss bar chart that shows only whether the year had a gain or a loss. In which year was there a loss, according to the Win/Loss bar chart?

2013

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

2200 and 4100 respectively

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

255

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

3

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

#N/A

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!

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.

#REF!

Examine the attached file: PS1_formulas.xlsx What is the total widget sales for the West Region for the year?

$5,129

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 (command group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that have 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

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

Whole Number

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