EL2 C5 C6 Flashcards
Excel’s built-in auditing features help you make sure that
a worksheet is functioning accurately
Use Excel’s _____ feature to test data that was entered before validation rules were set up.
Circle Invalid Data
At the Scenario Summary dialog box, enter in the Result cells text box
the formula cell or cells that change when the data is applied in various scenarios
To test the accuracy of key figures in a worksheet, enter a _____ outside the main worksheet area.
proof formula
You can do all the following at the Paste Special dialog box except
copy the active cell, plus all others with the same value
To use the Scenario Manager, start by clicking the Data tab and then the_____ button.
What-If Analysis
Which of the following is not one of the Excel error codes mentioned in Chapter 5?
a red diagonal triangle
Which types of data tables does Excel offer?
one-variable and two-variable
A range of cells in a copied source range can be multiplied by the cells in the destination range by
using Paste Special and clicking Multiply
To create a data table, begin by clicking the _____ button.
What-If Analysis
What advice does Chapter 5 give regarding creating scenarios?
create the first scenario using the original values
Which of the following buttons is not available at the Scenario Manager dialog box?
OK
When you select Formats at the Paste Special dialog box, Excel will paste
only formatting options from the source
Cells that contain formulas that reference other cells are called _____ cells.
Dependent
Which of the following options is not provided at the What-If Analysis button drop-down list
Evaluate Formula
You can convert data from a column to a row arrangement by clicking the Transpose button at the Paste button drop-down gallery or by clicking the Transpose option
at the Paste Special dialog box
After data has been copied to the Clipboard, use options at the _____ dialog box to perform a mathematical operation in the destination range based on values in the source range.
Paste Special
With a large worksheet, you can keep track of dependent cells that may not be visible while other cells are being changed by
opening a Watch Window
When using the Goal Seek command, enter the target value into the _____ box at the Goal Seek dialog box.
To value
To clear cell tracer arrows, click the _____ button.
Remove Arrows
The _____ error code indicates that the formula contains an unrecognized entry.
NAME?
When viewing the results of a scenario at the Scenario Manager dialog box, click the _____ button to apply the current values.
Show
The cell in which Excel is to calculate the target value using Goal Seek must be referenced by a formula in the _____ text box.
Set cell
Which of the following is not one of the options in the Operation section of the Paste Special dialog box?
Count
How many models can you create using the Scenario Manager dialog box?
As many as you want
Convert data in columns to rows and vice versa using the _____ button in the Paste button drop-down gallery or using the _____ option at the Paste Special dialog box.
Transpose; Transpose
Use the _____ button to work through a formula value by value to determine where an error exists.
Evaluate Formula
To test three different what-if conditions using the Scenario Manager, you must
create three scenarios
What can you do to see a descriptive reference next to the input text box, rather than the cell address when adding a scenario?
Create a range name for each changing cell
Use the _____ option at the Paste Special dialog box to convert data arranged in rows to data arranged in columns.
Transpose
To circle invalid data, click the _____ button arrow and then click the Circle Invalid Data option.
Data Validation
To calculate a value, the Goal Seek feature requires that cells have a(n) _____ relationship.
dependent
With a Watch Window, you can keep track of multiple
dependent cells
How is the Scenario Manager different from the Goal Seek feature?
It allows you to examine several different answers to your question
If you hear a beep while tracing cell relationships, it means that
no more relationships exist
Use Excel’s _____ feature when you know the result but don’t know what input value will achieve that result.
Goal Seek
Enter _____ in the Changing cells text box at the Add Scenario dialog box.
the range cells that will change when the scenario is applied
To compare scenarios side by side, display the Scenario Manager dialog box and click the _____ button.
Summary
By default, Excel stores _____ in the Comment text box at the Add Scenario dialog box.
the user name and date the scenario was created
Cells that provide data to formula cells are called _____ cells.
precedent
Using data tables provides a means of analyzing various outcomes in a calculation without
creating multiple formulas
In a two-variable data table, where is the source formula placed?
in the top left cell of the table
The _____ group on the Formulas tab contains buttons that are useful for viewing relationships between cells in formulas.
Formula Auditing
Which of these is not a file extension for a text file?
.tab
Use the _____ function to join the content of two or more cells, including text, numbers, and cell references.
CONCAT
The text function PROPER
capitalizes the first letter of each word
What does it mean if a yellow triangle with an exclamation mark appears next to a query in the Queries & Connections group?
The source file cannot be found
What will be returned by the formula =LEFT(“1st Quarter Sales”,3)?
1st
What is the keyboard shortcut for using the Flash Fill feature?
Ctrl + E
If cell A3 holds the text JD Enterprises, the formula =LEN(A3) will return which of the following?
14
To exchange Excel data with someone who does not have Excel, save the workbook as a
text file
To start the process of exporting data from Excel to Access, open the Excel workbook, select the cells to export, _____, and then start Access.
click the Copy button
When data is imported into Excel, the data is imported in a table format and a(n) ____ is created.
query
If the Excel data being exported to Word will often change and you want to keep the Word document up to date, copy the data to the Clipboard in Excel, open Word, place the insertion point in the desired location,
click the Paste button arrow, click Paste Special, click Microsoft Office Excel Worksheet Object, and then click Paste link
Before transferring data from Excel to an existing table datasheet in Access, make sure that
the column structures in the two programs matc
To redisplay the Query & Connections task pane,
click the Queries & Connections button
UPPER and RIGHT are both _____ functions.
Text
You can import data into Excel from a variety of sources. Which of the following is not one of the sources discussed in Chapter 6?
PowerPoint
The formula =RIGHT(“1st Quarter Sales”,5) will return _____ in formula cell.
Sales
Flash fill can be used to
join parts of the contents of cells
In a tab delimited text file, Excel _____ at each tab.
begins a new column
To use Excel’s editing features to edit data embedded in Word, copy cells to the Clipboard in Excel, open Word, place the insertion point in the desired location,
click the Paste button arrow, click Paste Special, and then click Microsoft Office Excel Worksheet Object
How do you make the Edit button appear in a comment?
Click in the cell and then position the mouse pointer inside the comment box
When you edit an Excel chart embedded in a PowerPoint presentation,
the groups and buttons available on the Excel chart tabs become active
The formula =MID(“JD Enterprises Inc.”4,11) will return _____ in the formula cell.
Enterprises
Use _____ functions to extract the data when only some of the characters in a cell need to be copied.
Text
The TEXTJOIN function uses a ____ delimiter.
constant
One of the options at the _____ dialog box is to have Excel refresh the data when you open the file.
Query Properties
To separate a field into two fields during an import process, click the Edit button, select the column to be split, and then
click the Split Column button in the Transform group
To open a non-native file directly in Excel, use the _____ option to navigate to a specific folder and then click the File Type option box to display a drop-down list of all the files types that Excel can open.
Browse
Use the Power Query Editor window to
transform data before it is imported into Excel
What should you do to refresh a query?
Click the Refresh All button in the Queries & Connections group
Once Flash Fill has recognized that the first word of the adjacent column is to be extracted and suggests doing the same for the remaining cells, you should _____ to accept the suggestion or continue typing to reject the suggestion.
press the Enter key
The formula =CONCAT(“-“,519,555,1234) will return which of the following?
-5195551234
At the Import Data dialog box, which of the following is not one of the options for viewing imported data in a workbook?
Excel Chart
At the Query Properties dialog box, click the Refresh every check box to insert a check mark and then adjust the number of minutes in the measurement box if you want to Excel to
refresh the query at a specific time period
What does the text function SUBSTITUTE do?
inserts new text in place of old text
From where do you delete a scenario?
the Scenario Manager dialog box
Which of these options is not found in the Paste section of the Paste Special dialog box?
As hyperlink
If you see a green diagonal triangle in the upper left corner of a cell, you should activate the cell and then
(?)
A link to Excel data from a Word document will no longer work if you _____ the source workbook.
move or rename
How many Access tables can be imported into Excel at a time?
one
One of the methods for deleting a query is to right-click the query at the _____ and then click the Delete option at the pop-up menu.
Queries & Connections task pane
The formula =TEXTJOIN(“-“,TRUE,519,555,1234) will return which of the following?
519-555,1234
Why are text files useful for sharing data with others?
The text file format is supported by almost all applications and computers
If you have linked an Excel object to a Word document and decide to keep the data but not maintain the link,
open the Word document, right-click the linked object, point to Linked Worksheet Object, click Links, and then click the Break Link button
To export Excel data to an Access database that does not have an existing table in which to receive the data, you should
perform an import routine from Access
Which of these is not a text file format supported by Excel?
.bin
To import a text file into Excel, use the _____ button in the _____ group.
From Text/CSV; Get & Transform
To import an Access table into a new worksheet after selecting the table at the Navigator dialog box, begin by clicking the _____ button.
OK
To finish the process of exporting data from Excel to Access, open the Access database in Datasheet view,
click the Paste button arrow, click Paste Append, and then click Yes
Import data into Excel using buttons in the _____ group on the _____ tab.
Get & Transform Data; Data
To test the various inputs on the worksheet model in the Scenario Manager, you can
switch scenarios
Removing the connection between the source file and the destination file allows which of the following?
capturing the data at a specific time
When tracing relationships between cells and formulas, Excel draws _____ if an error is detected.
red tracer arrows
You must provide three pieces of information via a dialog box when using the Goal Seek command. Which of the following is not one of them?
confidence level
A range of cells containing a series of input values is called a
data table
If the data being copied from Excel to Word is not likely to need editing or updating, copy it to the Clipboard in Excel, open Word, place the insertion point in the desired location,
and then click the Paste button
