Tools and Features of Excel
The VLOOKUP function structure is VLOOKUP.
(lookup_value,table_array,col_index_num,range_lookup)
Complete the steps to describe how to remove duplicate records.
Complete the steps to describe how to remove duplicate records. 1. Click the Data tab. 2. Navigate to the Data Tools group. 3. Click Remove Duplicates. 3. In the dialog box, select the field or fields to check for duplicates. 4. Click OK.
Use the drop-down menus to identify the items that should be included in the example custom function.
Function Profit(sales,cost) Profit=sales-cost End function
1. Select the cells that need a validation rule. 2. Click the Data tab. 3. In the Data Tools group, click Data Validation. 4. On the Settings tab, click the Allow list and select what to allow in the cells. 5. Use the other options on the Settings tab to set the data validation rule. Click the Input Message tab to create the message telling the user how to type the data. 7. Click the Error Alert tab to create the error message. 8. Click OK.
How to set a data validation rule
What step will make it possible to edit a form control button?
Press and hold the Control key while clicking the form control button.
Which tool would be best for selecting people for a survey?
Random Number Generator
What command has options for exporting an Excel file to another file format?
Save As
Which command should you use if you want to export an Excel file to a text file?
Save As
The options for merging scenarios can be found in which dialog box?
Scenario Manager
What field is the best choice for identifying duplicates?
Social Security number (this is because each value should be unique)
Before the Sampling tool can be used, what needs to happen?
The Analysis ToolPak must be installed.
Complete the sentences to define each database term.
The conditions a row must meet to be included in the calculation are referred to as criteria. A range containing the list of related information is called the database. A column to be used by a database function is called a field.
What will happen when changes are made to the cells containing criteria for a database function?
The function results will be updated.
When would it make the most sense to add a macro to a Quick Access toolbar button?
The macro is used frequently.
Which situation is an example of a constraint?
The values in cells A1 through A12 must be less than 100.
What are examples of constraints? Check all that apply.
The values in cells C1 through D5 must be integers. The value in cell B2 must be greater than 10. The value in cell E4 must be less than or equal to 100.
What do newly created PivotTables look like?
They are empty.
What setting is helpful when there are unnecessary blank columns in imported data?
Treat consecutive delimiters as one
Complete the sentences about XML.
XML is useful for exchanging data between applications. The Developer tab is needed for importing XML data. The most common activity when using XML data with Excel is exporting XML data from Excel.
What is a PivotTable?
an interactive table that summarizes and organizes data
How can fields in a PivotTable be moved?
by clicking and dragging fields to new areas in the Field List pane
What is the DAVERAGE function useful for?
calculating the average values in a database that meet certain criteria
Examples of data validation rules include which statements about the data entered? Check all that apply.
can't be greater than the current date must have a specific number of characters must match an option on a drop-down list
What task is the Solver utility most useful for?
determining optimal values for a cell by adjusting multiple cells used in a formula
What are some tasks that data validation can perform? Check all that apply.
display an input message prompting users for correct entries display an error message when incorrect data is entered circle invalid entries on a worksheet
Where can PivotTable styles be applied?
on the PivotTable Tools Design tab
What are examples of common delimiters that the Text Import Wizard uses to separate text into columns? Check all that apply.
tabs commas spaces
What are two examples of common delimiters that the Text Import Wizard uses to separate text into columns?
tabs and spaces
In the function =DSUM(Database,5,A2:A4), what does the 5 refer to?
the column containing the values to be added
In the function =DSUM(Database,8,C5:C10), what does the "8" refer to?
the column containing the values to be added
In the function =DAVERAGE(Item_List,"Prior year",D2:E3), what does Item_List refer to?
the database name
What task would the Goal Seek utility be most helpful for?
working backward to identify the input values for a specific result
What steps will use a Web Query to retrieve data from a web page? Use the drop-down menus to complete the steps.
1. Click the Data tab. 2. Click Get External Data, and then click From Web. 3. In the New Web Query dialog box, enter the web address for the data, and then click Go. 4. Click the arrow to select the desired group of data. 5. Click Import. 6. In the Import Data dialog box, specify where to put the imported data and click OK.
What are the steps for selecting a random sample of data? Use the drop-down menu to complete the steps.
1. Click the Data tab. 2. In the Analysis group, click Data Analysis. 3. In the dialog box, click Sampling and click OK. 4. Select the range of data to use for the sample. 5. Select a sampling method and click OK.
What are the steps to use the Solver utility? Use the drop-down menu to complete the steps.
1. Click the Data tab. 2. In the Analysis group, click Solver. 3. In the Solver dialog box, click Add. 4. Use the dialog box to specify the objective and constraints and click OK.
What steps can remove duplicate records? Use the drop-down menu to complete the steps.
1. Click the Data tab. 2. In the Data Tools group, click Remove Duplicates. 3. In the Remove Duplicates dialog box, select the field or fields to check for duplicates. 4. Click OK.
What are the steps for using the Goal Seek utility? Use the drop-down menus to complete the steps.
1. Click the Data tab. 2. In the Data Tools group, click What-If Analysis, and then click Goal Seek. 3. Specify the cell that contains the formula to solve and enter the desired result. 4. Specify the cell that contains the value to adjust. 5. Click OK.
What steps will create a scenario? Use the drop-down menu to complete the steps.
1. Click the Data tab. 2. In the Data Tools group, click What-If Analysis, and then click Scenario Manager. 3. Click the Add button. 4. Specify the name of the scenario. 5. Specify the cells containing the values to change and click OK. 6. Specify values for the changing cells and click OK.
Complete the steps for creating a scenario.
1. Click the Data tab. 2. In the Data tools group, click What-If Analysis. 3. Click Scenario Manager. 3. Click the Add button. 4. Specify the name of the scenario. 5. Specify the cells containing the values to change, then click OK. 6. Specify values for the changing cells, then click OK.
Complete the steps for beginning the process of adding a macro button to a worksheet.
1. Click the Developer Tab. 2. Navigate to the Controls group. 3. Click Insert.
Complete the steps for running a macro.
1. Click the Developer tab. 2. In the Code group, click Macros. 3. In the dialog box, select the macro and click Run.
What steps will run a macro? Use the drop-down menu to complete the steps.
1. Click the Developer tab. 2. In the Code group, click Macros. 3. In the dialog box, select the macro and click Run.
What steps will create a custom function? Use the drop-down menu to complete the steps.
1. Click the Developer tab. 2. In the Code group, click Visual Basic. 3. In the VBA Editor, click the Insert menu, and then click Module. 4. Type the function. 5. Click Save, and then close the VBA Editor.
What steps can import XML data into Excel? Use the drop-down menu to complete the steps.
1. Click the Developer tab. 2. In the XML group, click Import. 3. Select the XML file containing the data. 4. Click Import.
What steps can be used to merge scenarios? Use the drop-down menu to complete the steps.
1. Click the worksheet where the scenarios should be merged. 2. Open the Excel Scenario Manager dialog box. 3. Click Merge. 4. Select the worksheet with the scenarios to bring in. 5. Click OK.
What steps can split a names column into first name and last name columns?
1. Insert a column next to the names column. 2. Select the names column. 3. Click the Data tab. 4. In the Data Tools group, click Text to Columns. 5. In the wizard, click Next. 6. In the wizard, select Treat consecutive delimiters as one. 7. In the wizard, select spaces as delimiters. 8. Click Next, and then click Finish.
Complete the steps for activating an add-in.
1. Open the Excel Options dialog box 2. In the category list, select Add-ins. 3. From the Manage list, select Excel Add-ins, then click Go. 4. Check the add-ins to activate. 5. Click OK.
What steps will change an existing macro? Use the drop-down menu to complete the steps.
1. Open the Macro dialog box. 2. Select the macro. 3. Click Edit. 4. In the VBA Editor, find and change the code. 5. Save and close the VBA Editor.
What steps will add a group of macros to the Ribbon? Use the drop-down menu to complete the steps.
1. Right-click the Ribbon, and then click Customize the Ribbon. 2. On the Choose commands from list, click Macros. 3. Under Main Tabs, select the Developer tab and click New Group. 4. Click Rename and type a name for the group. 5. From the macro list, select the macros to add and click Add. 6. Click OK.
What steps can create a PivotTable? Use the drop-down menus to complete the steps.
1. Select any cell in the range of data. 2. Click the Insert tab. 3. In the Tables group, click PivotTable. 4. Verify that the range for the data is correct. 5. Select the location for the PivotTable. 6. Click OK.
What steps will add slicers to a PivotTable? Use the drop-down menu to complete the steps.
1. Select the PivotTable. 2. Click the PivotTable Tools Options tab. 3. In the Sort & Filter group, click Insert Slicer, and then click Insert Slicer. 4. In the Insert Slicer dialog box, select the fields to create slicers for. 5. Click OK.
What steps will hide all details in a PivotTable? Use the drop-down menus to complete the steps.
1. Select the cell or range to collapse. 2. Click the PivotTable Tools Options Tab. 3. Click Collapse Entire Field.
Complete the steps for using date criteria in a data validation rule.
1. Select the cells to validate, then click the Data tab. 2. In the Data Tools group, click Data Validation. 4. In the dialog box, on the Settings tab, click the Allow list, then click Date. 5. Click the Data list, then select the operator. 6. Complete the remaining criteria entries. 7. Define input and error messages, then click OK.
What are the steps for using date criteria in a data validation rule? Use the drop-down menus to complete the steps.
1. Select the cells to validate. 2. Click the Data tab. 3. In the Data Tools group, click Data Validation. 4. In the dialog box, on the Settings tab, click the Allow list, and then click Date. 5. Click the Data list, and then select the operator. 6. Complete the remaining criteria entries. 7. Define input and error messages, and then click OK.
What steps will create a drop-down list for data validation? Use the drop-down menus to complete the steps.
1. Select the cells to validate. 2. Click the Data tab. 3. In the Data Tools group, click Data Validation. 4. On the Settings tab, click the Allow drop-down list, and then click List. 5. In the Source field, type the items for the list or select them in the worksheet. 6. Click OK.
Which function will return the average of values in the third column of the database named Products that meet the criteria in cells D2 through E5?
=DAVERAGE(Products,3,D2:E5)
Which function will return the average of values that are in the sixth column of the database named "Profits" and that meet the criteria in cells A3 through B7?
=DAVERAGE(Profits,6,A3:B7)
Which function will return the total sum of values in the "Sales" column of the database named Database that meet the criteria in cells A1 through B3?
=DSUM(Database,"Sales",A1:B3)
A user listed the steps for creating a custom function. Click the Developer tab (then click Visual Basic in the Code Group). In the VBA Editor, click the Insert menu, then click Module. Type the function. Click Save, then close the VBA Editor. Which step is missing?
After clicking the Developer tab, click Visual Basic in the Code group.
What sign indicates that a spreadsheet has data validation enabled?
After data is entered, a message appears stating that the data is invalid and the user needs to retry.
What macro name will cause the macro to automatically run when the file is opened?
Auto_Open
What steps will modify and update PivotTable data?
Change the source data, then select the PivotTable, and on the PivotTable Tools Options tab, click Refresh.
What steps can display data in a PivotTable?
Click and drag a field to one of the four areas in the PivotTable Field List pane.
What are the first two steps for completing either a Microsoft Query or a Web Query?
Click the Data tab, then click Get External Data.
Which tab should you click to access and use the Goal Seek utility?
Data
How to set a Date Criteria
Date Criteria: Select the cells you want to validate Click Data Validation From the allow list, select date From the data list, select the operator Complete the remaining criteria entries Define input and error messages Click OK
What is the structure for database functions?
Dfunction(database,field,criteria)
What code marks the end of a macro?
End Sub
What is the code that marks the end of a macro?
End Sub
What is a feature of the Simplex LP method that makes it different from the GRG Nonlinear method?
It does not allow any of the values to be zero.
How is the TODAY function unusual?
It doesn't require any arguments.
What step will begin the process of adding a macro button inside a worksheet?
On the Developer tab, in the Controls group, click Insert.
Where can the criteria for a database function typically be found?
a separate area in the spreadsheet
What is a scenario?
a set of input values that produce a result
If the Scenario Manager is used frequently, what can be done to make accessing the Scenario Manager more efficient?
adding it as a button to the Quick Access toolbar
What are two tasks that data validation can perform?
displaying an input message prompting users for correct entries or circling invalid entries on a worksheet
What does data validation do?
ensures that entered data matches a specific format
What is XML useful for?
exchanging data between applications
What activity is most common when using XML data with Excel?
exporting XML data from Excel
To use the VLOOKUP function, the lookup value should be in the of the table.
first column
1. Write the number that will be used to generate the table values. 2. Click the Data tab. 3. Navigate to the group. 4. Click What-If Analysis, then click. 4. In the dialog box, type or select the values to use for the row or column input cells. 5. Click OK
formula Data Tools Data Table
What task would Microsoft Query be most helpful for?
gathering data from an Access database and placing it in an Excel worksheet
What feature is likely to be incompatible when saving an Excel spreadsheet as a text file?
graphics
What characters can be included in a macro name? Check all that apply.
letters numbers underscores (they cannot include spaces)
What are two types of characters that can be included in a macro name?
numbers and underscores (only letters, numbers, and underscores can be included in a macro name)
What are slicers useful for?
quickly filtering data
What are three types of information that must be included as arguments in the INDEX function?
range, row number, column number
What is the Web Query feature most useful for?
retrieving data from a web page and placing it in a spreadsheet
What does a function procedure do?
return a value
What are the three main elements of a data validation rule?
rule settings, input message, error alert message
What task would the Random Number Generator tool be most helpful for?
selecting people for a survey
What step will make the PivotTable Field List pane appear on the right?
selecting the PivotTable
What options are available when creating a macro? Check all that apply.
shortcut keystroke comments (for the dialog box)
What are two options available for creating a macro?
shortcut keystroke and comments
In the function =DAVERAGE(Item_List,"Prior year",D2:E3), what does "Prior year" refer to?
the field name
The VLOOKUP function will return the lookup value if it is used to find an approximate match and there is no exact match.
the largest value that is less than
In the function =MATCH(F6,revenue,100), what does "revenue" refer to?
the named range
In the function =DCOUNT(Database,"Current year",A2:C3), what does A2:C3 refer to?
the range containing criteria for the function
What are the three main elements of a data validation rule? Check all that apply.
the rule settings the input message the error alert message
In the function =HLOOKUP(C10,total_revenue,2,FALSE), what does the "2" refer to?
the second row in the table
