excel study guide
Horatio wants to hide field headers in a Pivot table to increase the display of the screen. He clicks on a cell in the pivot table, then clicks on ANALYZE in the ribbon. What should he do next to complete the procedure?
Click on Field Headers in the Show group
A student is given the task of counting the number of nonblank cells in the range of cells B1 to B20. Which of the following formulas should he use to do so?
=COUNTA(B1:B20)
Ian needs to extract the day from the date 5/31/2017 entered in cell A1. Which formula can he use to do this?
=DAY(A1)
The score of students in a subject is inserted in B2 cell and the passing score for the subject is 60. Which of these functions will you insert in C2 cell to check if the student has passed or failed?
=IF(B2>=60, "Pass", "Fail")
In Excel, to merge cells A1, B1, C1, D1, and E1, _____ is the correct reference for the merged cell.
A1
Which of the following is a Statistical function? _____
AVERAGE
In the formula =B6*$B$2, which of the following describes $B$2?
Absolute cell reference
_______ is a database program.
Access
To align currency symbols and decimal points in a column of numbers, you can apply the ____ number format.
Accounting
Johan wants to create a PivotTable using Power Pivot. During the procedure, to add a Table to PowerPivot, he selects a cell in a Table and clicks Power Pivot tab > Tables group > X. What is X in the procedure?
Add to Data Model
Which of the following is not an Excel function category? ____
Algebra
If you want to create a new line within a cell, press the ____ keys to move the insertion point to the next line within the cell.
Alt + Enter
Which of the following combinations of keys will you press to open the VBA window and run a macro code?
Alt + F11
Marcos wants to refresh the data in a Pivot table manually. He clicks on the PivotTable to show the PivotTable Tools on the ribbon. Which combination of keys should he press next to complete the procedure?
Alt +F5
You select the ____ option button in the Custom AutoFilter dialog box if you want to display rows that meet both of two criteria
And
Which of the following will you click on the Reports list box in Solver Results dialog box to produce an Answer Report?
Answer
How can one filter a PivotTable using a report filter?
Click the Report Filter's drop-down arrow, then click on an item to select an item in the list and click OK.
____provide descriptive text for the individual data markers, such as pie slices
Data labels
A ________ is a highly structured collection of data values organized into separate tables.
Database
Ricki wants to apply a particular chart style to a PivotChart she has created. Which of these tabs will help her apply it?
Design tab
Which of the following lets you edit the contents of a cell?
Double-click the cell or click in the formula bar.
To change a range's conditional formatting from data bars to icon sets, which of the following can you do?
Edit the conditional formatting rule.
To view the code of a macro, you need to open the Visual Basic _____.
Editor
According to the order of operation in Excel:
Excel performs exponentiation, then multiplication and division, then addition, and subtraction.
To add and remove chart elements, you can use the Add Chart Element button in the Charts Layout group on the ____ tab.
CHART TOOLS DESIGN
To select nonadjacent cells or ranges on a worksheet, you can press and hold _____ while selecting each one.
CTRL
The default template contains the default formatting you start out with in every new workbook, including ____ 11-point font.
Calibri
The ____ arrangement option layers the open workbooks on the screen
Cascade
After you filter a column, the ____ Filter command becomes available so you can remove the filter and redisplay all the records.
Clear
In which of these boxes will you list all the possible fields that can be included in your formula in a PivotTable?
Fields lists box
Which of the following tabs lets you set worksheet print options?
File
On which tab and button are document properties located for editing?
File tab, Info Button
To return cell contents to its default font and style, you can use the _____ command.
Clear Formats
Steffie wants to change the margins of a worksheet to Normal to align it better on a printed page. To do this, what can she do after selecting the worksheet for this purpose?
Click Margins in the Page Setup group on the Page Layout tab and then click on Normal.
Luca wants to insert a pivot table. He clicks on a single cell inside the data set, then proceeds to step X, and finally clicks OK in the dialog box that appears after step 2. What can step 2 be?
Click PivotTable on the Insert tab, in the Tables group.
Jin wants to insert a recommended pivot table. He clicks a single cell inside the data set, then proceeds to step X and finally clicks OK in the dialog box that appears after step 2. What can step 2 be?
Click Recommended PivotTables on the Insert tab, in the Tables group.
Which of the following panes in the Power Map window defines where and how data will be used in the map?
Layer Pane
Excel supports all of the following types of sparklines EXCEPT ____.
a true/false sparkline for help making decisions
"AZ" is an example of a ____.
column heading
You double-click the Format Painter button when you want to _____.
copy a cell's format to more than one cell
Once you enter an array formula, Excel will enclose the formula in a set of _______.
curly braces
When you save a workbook that contains external reference formulas Excel stores the most recent results of those formulas in the ____ file.
destination
When you copy a formula that contains an absolute reference to a new location, the reference ____.
does not change
When combination charts have data series with vastly different values, you can create ____ charts.
dual axis
Header and footer elements such as worksheet name, current date, and time are _____ elements.
dynamic
To move one slice of a pie chart away from the pie creates a(n) ____.
exploded pie chart
Which of the following takes precedence over multiplication?
exponentiation
A _______ refers to a collection of characters with a similar, specific design.
font
Italic, bold, bold italic, and underline are examples of _____.
font styles
Which of the following is a built-in formula that helps you perform complex calculations?
function
A worksheet ____ is a collection of two or more selected worksheets
group
You receive a worksheet in which the rows are numbered 1, 2, 3, 5,6. This means that row 4 is _____.
hidden
To _______ a series, the cells between the first and last cells in the series must be blank.
interpolate
A(n) _______ process means that Solver starts with an initial solution and uses that as a basis to calculate a series of solutions.
iterative
Which of the following is NOT an option for arranging your workbooks?
lateral
The header and footer sections include a _____.
left, right, and center section
One of the benefits of Live Preview is that it _____.
lets you see and evaluate different formats as you develop your workbook
A _____ can be destructive and can modify or delete files that may not be recoverable.
macro virus
To avoid splitting a worksheet in awkward places, you can insert a _____ to specify a specific breaking point.
manual page break
A _____ is the space between the page content and the edges of the page.
margin
_____ merges each of the rows in the selected range across the columns in the range.
Merge Across
____ references are seldom used other than when creating tables of calculated values such as a multiplication table in which the values of the formula or function can be found in the initial rows and columns of the table.
Mixed
The ____ dialog box provides options for moving charts between worksheets and chart sheets.
Move Chart
To calculate the number of montly payments required to pay off a loan or meet an investment goal, use the _______ function.
NPER
The _____ dialog box displays all of the names in the workbook.
Name Manager
In the Format Cells dialog box, the _____ tab allows you to format currency by choosing decimal places, comma separators, and regular number displays.
Number
You select the ____ option button in the Custom AutoFilter dialog box if you want to display rows that meet one of the criteria.
Or
Options to rotate cells in Excel are available using the _____ button in the Alignment group on the Home tab.
Orientation
Which of the following should be selected as X in the series of clicks to save a custom theme: X > Themes group > Themes > Custom > Document theme you want to use?
Page Layout
Which worksheet view shows headers, footers, and rulers?
Page Layout view
You can edit a worksheet footer in _____.
Page Layout view
A data structure that stores the information used to create a PivotTable is called the ________________.
PivotTable cache
You want to change all worksheet occurrences of "Radio" to "TV"; to do this, you can use the _____ feature.
Find and Replace
____ enters text based on patterns it finds in the data.
Flash Fill
To enter a range of text based on examples that are already in your worksheet, which of the following can you use?
Flash fill
To change the color of cell content, you use the ________ color list arrow.
Font
To copy a cell's formatting to another cell, which of the following can you use?
Format Painter
Which of the following will you select as X in the following series of clicks to format a range as a table: Home tab > Styles group > X > Table Styles?
Format as Table
In a workbook's theme, _____ colors are designated for text and background, _____ colors are used for accents and highlights, and _____ colors are used for hyperlinks.
Four, six, two
____ rows and columns lets you keep headings on the screen as you work with the data in a large worksheet.
Freezing
By default, numbers appear in the _____ and generally display values exactly as you type them.
General format
To view a worksheet without gridlines showing on the screen, which of the following would you uncheck?
Gridlines View
A ________ is an organization of fields that start with the most general and go down to the most specific.
Hierarchy
To delete records that are incorrect, out of date, or no longer needed, select a cell in each record you want to delete, click the Delete button arrow in the Cells group on the ____ tab, and then click Delete Table Rows.
Home
Which of these series of clicks would you select to remove hyperlink from a cell?
Home tab > Editing group > Clear > Remove Hyperlinks
Which of the following is not a type of trendline?
Horizontal
____ are usually represented by words with colored letters and underlines or images.
Hyperlinks
The _______ function returns the amount of a particular payment that is used to pay the interest on the loan.
IPMT
To calculate the internal rate of return, use the _______ function with the cost of the initial investment as the first cash flow value in a series.
IRR
Which of the following is true about changing worksheet column width?
In Normal view, the Column Width dialog box lets you set the exact number of characters that will fit in the column.
The _____ alert accepts the invalid data but still allows you to cancel the data entry.
Information
Which category of Excel functions returns information about the format, location, or contents of worksheet cells? ____
Information
A chart that compares three set of values in a three-dimensional chart is _____.
Surface
Which of the following keys will you press when the cell cursor is in the last cell of the last record (row) to add new data for the next record?
Tab
Excel assigns the name ____ to the first Excel table created in a workbook.
Table1
The ____ report layout displays one column for each field and leaves space for column headers.
Tabular
In the Allow list box in the Data Validation dialog box, the _____ option means that the cell will accept only text of a specified number of characters.
Text Length
To create a new, blank workbook, which of the following can you use?
The New command in Backstage view. The New command on the File tab.
When you paste formula cells using the Paste button in the Clipboard group, which of the following is true?
The Paste Options button then lets you paste specific elements of the copied cells.
Which of the following formulas totals the cells B6, B7, B8, B9, and B10 most efficiently?
The formula =SUM(B6:B10)
In the following formula: =IF(A1="YES", "DONE", "RESTART"), what happens if A1= "NO"? _____
The formula returns the text "RESTART".
Before using Scenario Manager, you should assign _______ to all the input and result cells you intend to use in your scenarios.
names
Because Excel stores dates and times as _____, you can apply different formats without affecting the date and time value.
numbers
A chart sheet can contain ____.
only one chart
A pie slice is a data marker that represents ____ from a data series.
only one value
PMT(rate, nper, pv [, fv=0] [, type=0]) In the figure above,fvandtypeare ____.
optional arguments
In addition to displaying subtotals, the Subtotal feature "____" your worksheet so you can control the level of detail that is displayed.
outlines
In ____ orientation, a page is taller than it is wide.
portrait
When you borrow money, the money you receive represents a _______ cash flow.
positive
If active cell D12 contains the formula =D10+D11, cells D10 and D11 are _______ cells for D12.
precedent
PMT(rate, nper, pv [, fv=0] [, type=0]) In the figure above,pv stands for the ____.
present value of the loan
Formulas created using the point-and-click method contain ____ references.
relative
When you insert a new column, the existing columns are shifted to the ____ and the new column has the same width as the column directly to its left
right
To resize an embedded chart, ____.
select the chart and drag the corner sizing handle of the selection box
To change the name of a worksheet, you rename the _____.
sheet tab
Keyboard ____ can help you work faster and more efficiently because you can keep your hands on the keyboard.
shortcuts
When you reference cells and ranges in other worksheets, if the worksheet name contains spaces, you must enclose the sheet name in ____.
single quotation marks
If the destination file is closed when you make a change in the ____ file, you can choose whether to update the link to display the current values when you open the destination file.
source
Protecting the _____ prohibits users from renaming, deleting, hiding, or inserting worksheets.
structure
In VBA, macros are called _____ procedures.
sub
To ungroup worksheets, you can click a sheet ____ of a sheet not in the group.
tab
A collection of data stored in rows and columns
table
A ____ is a workbook that you can open with the text, formats, and formulas already built into it.
template
After you delete a worksheet column, _____.
the columns to its right shift left
Which of the following is not a way to move cell contents?
the fill handle in the lower-right corner of an active cell or range
FUNCTION(argument1, argument2, ...) is ____.
the general syntax of all functions
When you nest functions, you should have ____.
the same number of left and right parentheses
Fonts, colors, and cell styles are organized in _____ categories
theme and non-theme
Fonts, colors, and cell styles are organized in _____ categories.
theme and non-theme
A logical function is a function that works with values that are either ____.
true or false
Clicking the Percentage style in the Number Format list applies _____ decimal places by default.
two
In two-variable tables, you must always place a reference to the result cell in the _______ corner of the table at the intersection of the row values and the column values.
upper-left
One way to ensure that correct data is entered into a cell or range is to use the Excel data _____ feature.
validation
In Solver, changing cells are sometimes referred to as _______ cells.
variable
Which of the following expense types can be used in a CVP analysis?
variable
All three expense types can be classified as _______.
variable, fixed, and mixed
The Power Pivot add-in provides access to the _________.
Data model
_____ are an example of conditional formats in Excel.
Data bars
Which formula can you use to extract the month from the date in cell F5: 2016 July 8th?
=MONTH(DATE(2016,7,0))
Which of the following shows the syntax of the NPER function?
=NPER(rate, pmt, pv [, fv=0] [, type=0])
Which of the following is true about adding cell borders?
A cell border extends the width of the cell.
In the formula =IF(A1=B1, C1, C2), the result will be C2 if ____.
A does not equal B
The Format Cells dialog box has six tabs, including _____.
Border
When you reference a cell or range in a different worksheet, the ____ separates the sheet reference from the cell reference
!
When you reference a cell or range in a different worksheet, the ____ separates the sheet reference from the cell reference.
!
If the VLOOKUP function cannot find the lookup value, the _________ error value is displayed in the cell.
#N/A
The Query Editor also includes tools to:
-create new columns -group data values -calculate summary statistics -All of the above
What is the file extension of macro-enabled workbook?
.xlsm
A workbook template has which of the following file extensions?
.xltx
All template files have a(n) ____ extension.
.xltx
Binding constraints always show a slack of _______.
0
To project a trend that decreases rather than increases, use a step value of less than _______ for a linear trend.
0
With the PMT function, the type argument is _______ when the payments are made at the end of each period.
0
How many items in a Pivot table can you drill down at a time?
1
Put the following five steps in the order in which you would perform them to use the Paste Special function. 1. Select and copy a range. 2. Click the Paste button arrow in the Clipboard group on the HOME Tab. 3. Specify exactly what you want to paste. 4. Click Paste Special to open the dialog box. 5. Select the range where you want to paste the Clipboard contents.
1, 5, 2, 4, 3
A jewelry designer creates an excel sheet to list different items he sold on the previous day. Cells A1, B1, C1, and D1 contain headings—Order ID, Unit price, and Quantity, respectively. Cells A2 to A6 list the order ID for items sold—10001,10002,10003,10004, and 10005, respectively. Cells B2 to B6 list their respective unit price—$15, $18.5, $12, $9.5, and $6. Cells C2 to C6 list their respective quantities—20, 32, 16, 15, and 10. If he enters the formula in cell D1 as: =VLOOKUP (10005, A1: H3, 3, FALSE), what will the formula return?
10
How many actions does the Undo button let you reverse?
100
The default worksheet text is _____-point Calibri.
11
Excel functions are organized into ____ categories.
13
An Excel worksheet can have a maximum of ____ columns in a worksheet.
16,384
To move or copy a range of cells, select the correct order:1. Move the pointer over the border of the selection until the pointer changes shape.2. Select the cell or range you want to move or copy.3. To move the range, click the border and drag the selection to a new location, or to copy the range, hold down the Ctrl key and drag the selection to a new location.
2,1,3
What is the result of the expression =50+20/10*5?
60
The formula used to multiply cell A1 by cell C1 is ____.
=A1*C1
Barry creates a table showing ranks and percentage his students obtained in their final exams. He enters the heading "Name" in cell A3 and the names of students in cells A4 to A9, the heading "Rank" in cell B3 and the height of students in cells B4 to B9, the heading "Percentage" in cell C3 and the weight of students in cells C4 to C9. One of the students, Cara's name, rank, and percentage are mentioned in cells A7, B7, and C7, respectively. He types Cara's rank in cell A14. What formula can he use in cell B14 to find the column in which Cara's rank is?
=MATCH (A14,A3:C3,0)
What is the syntax for a SUM formula adding the values of cell F6 to F9?
=SUM(F6:F9)
The components of a structured reference are as follows: Table 1 is Table Name, [Net Amount] and [Commission Amount] are column specifiers and [#Totals] and [#Increase] are item specifiers. Which of these can be the formula for the structured reference?
=SUM(Table1[[#Totals],[Net Amount]],Table1[[#Increase],[Commission Amount]])
You select the ____ option button in the Custom AutoFilter dialog box if you want to display rows that meet both of two criteria.
And
____ order arranges labels in reverse alphabetical order, from A to Z
Ascending
Arlo creates a rectangular shape and inserts the text 'Color Box' in it. Then, he right clicks on the shape and selects a button to assign a macro to it. Which of the following can his selection be?
Assign Macro...
To change a column width, you can click ____ on the Column submenu to make the column(s) as wide as the longest entry of the cells in the column.
AutoFit
When you double-click a cell's right column border, which of the following occurs?
AutoFit resizes the column to the widest cell entry.
What feature inserts a function that totals the cells above or to the left of the selected cell?
AutoSum button
Which of the following inserts a function into a cell?
AutoSum button
Alba wants to use a watermark image as the background of her worksheet. She goes to the Page Setup group on the Page Layout tab. What option should she click to begin choosing the image file to use as a watermark?
Background
A(n) _______ worksheet projects the company's expected assets, liabilities, and equity.
Balance Sheet
The ____ criteria filter requires the records displayed to start with the specified text string.
Begins With
The ____ criteria filter requires the records displayed to be greater than or equal to and less than or equal to the specified numbers.
Between
When you cut or copy a cell, it is cut or copied to which of the following?
Both the Windows and Office Clipboard
If you receive a destination workbook but the source files are not included, click the ____ Link button in the Edit Links dialog box to replace the external references with the existing values.
Break
Which function counts how many cells in a range contain numbers, where value1, value2, and so forth are text, numbers, or cell references?
COUNT
_______ analysis expresses the relationship between a company's expenses, its volume of business, and the resulting profit.
CVP
You already created a line chart, but now decide that you want to change this chart to a pie chart. To do this, you click ____ from the Type group in the Chart Tools Design tab.
Change Chart Type
Your worksheet is too wide to fit on one portrait page. What is the best way to fix this problem?
Change the page orientation to landscape.
To reposition the legend to the left-hand side of a chart, click the _________ button, point to Legend, click the right arrow icon, and click Left.
Chart Elements
Eleanor wants a title row to be printed at the top of every sheet when she prints her data in an MS Excel file. What will she do in step X in the following series of clicks/tasks to do so: Page Layout tab > Print Titles > Page Setup Dialog box > X > OK?
Click on the select button in Rows to repeat at top area and select a title row.
To select a single worksheet cell so you can work with it, which of the following would you do?
Click the cell.
Which of the following will you do to manually turn off autofilter in a table between the steps: Clicking any cell in the filtered range and clicking the Filter button?
Clicking the Data tab on the Excel Ribbon
Which of the following should you select as X in the following series of clicks to change the chart type to a clustered column chart: Chart > Chart Tools > Design tab > Type group > Change Chart Type > All Charts > X > Clustered Column > OK?
Column
The ____ criteria filter requires the records displayed to have the specified text string anywhere.
Contains
To calculate subtotal for a table, the first step is to use the ____ button on the Table Tools Design tab.
Convert to Range
Which of the following would you use to create a duplicate of the selected cell when the CTRL key is pressed?
Copy pointer
Which dialog box opens after selecting a cell in the list of prepared data, clicking the Insert tab on Ribbon and then clicking the Table command in the Tables group?
Create Table
To make A1 the active cell, you should press which of the following keys?
Ctrl + Home
You can move to the previous or next sheet by pressing the ____ keys.
Ctrl + Page Up or Ctrl + Page Down
Whenever you click the Save button on the Quick Access Toolbar or press the ____ keys, the workbook file is updated to reflect the latest content.
Ctrl + S
To calculate multiple cell values, select the range and type the array formula, then press the _______ keys to enter the formula.
Ctrl+Shift+Enter
To format an individual sparkline, select the sparkline you want to format, and then click the Ungroup button in the Group group on the SPARKLINE TOOLS ____ tab.
DESIGN
The ____ CHART TOOLS contextual tabs appear on the Ribbon when you select a chart.
DESIGN and FORMAT
Which of the following is NOT a database function provided in Excel?
DTOTAL
You can display or hide filter arrows for an Excel table by using the Filter button in the Sort & Filter group on the ____ tab.
Data
You can turn off the macro recorder by clicking the Stop Recording button in the Code group on the _____ tab.
Developer
Amelia wants to insert a hyperlink to an e-mail address. Which of these can be X in the following series of clicks she can use: Select text or picture to display as hyperlink > Insert tab > Hyperlink > Link To > X > Subject box (Type subject ) > OK?
E-mail Address
To switch to ____ mode, you double-click the cell.
Edit
You can access the Sort buttons in the ____ group on the Home tab by clicking the Sort & Filter button and clicking a sort option.
Editing
What will you do in step X in the following series of clicks to change the font size of chart title: (Right-click) Chart Title > Font > Font tab > X> OK?
Enter the desired size of the chart title in the Size box.
Johnson wants to load Solver in Microsoft Excel. For this, he clicks the series: File > Options > Add-Ins > Manage Box > X > Go > Add-ins available box > Solver Add-in check box > OK. What can X be in the procedure?
Excel Add-ins
Which of the following will you select as X in the following series of clicks to change the change the color of a data series: (double-click) Data Series > Format Data Series > Series Options > X > Color > Color of your choice?
Fill & Line
The _____ command searches through the current worksheet or workbook for the content or formatting you want to locate.
Find
Which of these tabs will you select to alter the font as desired while changing the slicer style?
Font tab
You can open the Format Cells dialog box by right-clicking a cell or selected range, and then clicking _____ on the shortcut menu.
Format Cells
In which of these dialog boxes will you select the font, border, and fill settings for an element in a PivotTable?
Format Cells dialog box
After you describe a function in the Search for a function box in the Insert Function dialog box, click ____.
Go
Helen wants to resize slicer buttons to exact dimensions. She selects the slicer, then clicks the Slicer Tools under Options tab. What should she select next to reset the slicer buttons to exact dimensions?
Height and Width under Buttons group
To format a cell range so that values between 100 and 500 appear in red, which of the following can you use?
Highlight Cells Rules
To apply a cell style, you would use the Cell Styles command on the _____ tab.
Home
What will you select as X in the following series of clicks/entries/selections to apply a table style: Select cell to format as table > X > Format as table > Desired table style?
Home tab
Which of the following should be selected as X in the series to assign a macro to a button: Developer tab > Controls group > X > Form Controls > Button Form Control > Worksheet location where you want the upper-left corner of the button to appear > Type in the Assign Macro popup window?
Insert
Where can you see a brief description of a selected function?
Insert Function dialog box
Which of the following lets you search for a function or select one from a category
Insert Function dialog box
Which of the following lets you search for a function or select one from a category?
Insert Function dialog box
Which of the following can you use to insert a formula using a function?
Insert Function dialog box AutoSum button on the Ribbon AutoSum list arrow on the Ribbon Copy and paste a cell containing a function
Which of the following will you select as X in the following series of clicks to add a data table to a chart: Data Chart > Chart Tools > X > Data table > (Select check box) Show Data table?
Layout
To have Excel enter the lowest price from a range of prices, which of the following would you use?
MIN function
Kahir wants to delete a macro. To do this, he selects Tools > Macro > X > Select Macro > - sign > Yes on confirmation message dialog box. Which of the following will he select as X in this series of selections?
Macros
Nathan wants to run a macro from the developer tab. He enables the developer tab by clicking File > Options > Customize Ribbon > Developer tab > Code > X > Macro name box > Run. What should he click as X in this procedure?
Macros
To combine multiple cells into one combined cell, which of the following do you use?
Merge and Center button
If content is blocked, the _____ located under the Ribbon appears, notifying you that some content was disabled.
Message Bar
Izzy wants to move a pivot table to a new worksheet. Which of these should she select after the following clicks to do so: PivotTable > Analyze tab > PivotTable tools > Move PIvotTable > Move PivotTable dialog box > Choose where you want the PivotTable to be placed?
New Worksheet
One way to ensure that you are using consistent formats is to copy and paste your formats using the Format _____.
Painter
To see a preview of how a pasted value will look, which of the following would you use?
Paste List arrow
To which of the following chart types can you not add axis titles?
Pie chart
Which of the following methods copies an existing worksheet within the same workbook?
Press and hold CTRL and drag its sheet tab to a new location.
Setting the _____ to A1:L10 will print only data in those cells.
Print Area
Lola wants to install an add-in in Excel. She has to unblock the add-in to allow Excel to open the add-in file automatically. To do this, she right clicks on the add-in file and selects an option from the menu. What could the option be?
Properties
The Format Cells dialog box tab that provides options for locking or hiding cells to prevent other users from modifying their contents is _____.
Protection
You can create conditional cell formats using either the Home tab or the _____.
Quick Analysis tool
The _______ function calculates the interest rate charged or received during each payment period.
RATE
In the formula =A8*$A$1, which of the following describes A8?
Relative cell reference
Which of the following will you select as X in the given series of clicks to remove a data series from a chart: Chart > Chart Tools > Design Tab > Select Data > Data series > X?
Remove
Grace wants to connect a slicer to two PivotCharts. In which of these dialog boxes can she select the pivot tables she wants to link to the slicer and click OK?
Report Connections
Josh teaches sixth grade math. He created a table showing marks that his students scored on an assessment—the last column on the right displays this information. He now wants to add another column to the right showing percentage of marks that each students scored. What should he do after selecting the current last column on the right?
Right click on it and select Insert from the drop-down menu.
Which of these steps will you choose to refresh data in a Pivot Chart?
Right-click on the Pivot Chart and select Refresh
Nolan wants to group data in a Pivot table to analyze a subset of data. Which of these series of clicks/ selections/ entries should he follow to do so?
Right-click value > Group > Grouping box > Edit values in Starting at and Ending at checkboxes > Under By, select a time period > OK
If you orient a one-variable table so that the input values are in the first row, you would use the _______ input cell option.
Row
The _______ function takes only these arguments: cost, salvage, and life.
SLN
One way to interact with a database is with a query language. A popular query language is _______.
SQL
Which of the following functions inserts the total of a range?
SUM
The _______ function returns the sum-of-years' digit depreciation that results in a more accelerated depreciation than straight-line depreciation but less than declining balance depreciation.
SYD
Where do you rename a workbook and adjust its save location?
Save As dialog box
The default ____ for a hyperlink is the folder location and the name of the file to which you will link.
ScreenTip
Edwin wants to insert a Pivot Chart to summarize raw data. Which of the following combination of clicks/selections/entries should he use to do this?
Select Cell > Insert > PivotChart > OK
Michelle finished a 5 kilometer run in 180th position. The organizers shared an Excel spreadsheet with names of all the participants and the time they took to complete the race. The top 15 finishers are listed in rows 2 to 16. Michelle wants to compare her time against theirs. How can she do so?
Select row 17 and click View tab > Window group > Split.
Which of these will you do in step X in the series of clicks/ selections to delete a vertical page break: View tab > Workbook View > Page Break Preview > X > Page layout tab > Page Setup > Breaks > Remove Page Break?
Select the column to the right of the page break you want to delete.
Which of the following tabs in the Data Validation dialog box allows you to set validation criteria?
Settings
Anne has created a chart and wants to apply a different fill color to a chart element. Which of these will she choose after selecting the chart element and clicking on Chart Tools under Format tab?
Shape Fill
Myra wants to hide a worksheet to remove it from view. Which of these series of clicks will help her do so?
Sheet tab > Home tab > Cells group > Format > Visibility > Hide & Unhide > Hide Sheet
The worksheet range ____ references the worksheets, "Sheet1," "Sheet2," "Sheet3," and "Sheet4."
Sheet1:Sheet4
To move the active cell up one row, press ____.
Shift + Enter
You can switch from one scenario to another by clicking the _______ button in the Scenario Manager dialog box.
Show
_______ is a program that searches for the optimal solution of a problem involving several variables.
Solver
In which of the following dialog boxes will you click Load/Save to save a problem model?
Solver Parameters
In which of the following dialog boxes, under Subject to the Constraints, will you click the constraint that you want to change and then click Change?
Solver Parameters
When you extrapolate a series, the _______ value represents the amount that each value is increased or multiplied as the series is extended.
Step
After drilling down, Esteban wants to drill up to analyze summary data. To do so, he follows the following steps: 1. Select the item to drill up on in the PivotTable hierarchy you have drilled into. 2. Click the Quick Explore button that appears in the lower-right corner of the selection. 3. Use the Quick Explore button to reach the data you want. Which step did he miss while following this procedure?
Step 3: Select the item you want to explore in the Explore box and then click Drill Up.
The following are Excel-supported error alert types: _____.
Stop, Warning, and Information
Which of the following options will you select to restrict access to a shared workbook to only co-workers in your company who are logged into Microsoft Office?
The People in <name of your organization>.
If you discover an error immediately after you have confirmed a cell entry, what of the following would you use next?
The Undo button on the Quick Access toolbar
A __________ is a slicer that is applied to date fields. Using the timeline, you can summarize the data for ranges of time intervals from years down to minutes and seconds.
Timeline
Which of the following is true about deleting a worksheet row?
To delete a row, you can select the row, then use the Delete button in the Cells group.
For which of the following would you use the Paste list arrow on the Ribbon when pasting a copied cell?
To paste the value's number formatting only.
The ____ criteria filter requires the records displayed to have the current date.
Today
Which of the following will you select as X in the series of clicks to highlight the top 10 items in a range of cells: (Select) Range of cells > Home tab > Styles group > Conditional formatting > X > Top 10 items > Change value and highlight option in dialog box if required > OK?
Top/Bottom Rules
Which of the following boxes will you check after clicking on a table and then selecting Table Tools > Design, to add a total row to a table?
Total Row
In ____, the ribbon increases in height, the buttons are bigger, and more space appears around each button so you can more easily use your finger or a stylus to tap the button you need.
Touch Mode
The _____ Center is a central location for all the security settings in Office.
Trust
Which of the following will you do in step X in the following series of clicks to change the bounds of a chart axis: Chart > Chart Tools > Format tab > Current Selection > Format Selection > Format Axis > Axis Options > Vertical Axis crosses > At Category number > X?
Type the number that you want in the text box.
Shelley creates a table containing the marks of Language Arts students in her class with these columns: Names of Students and Marks. She now wants to see the names of students who scored exactly 60 marks. What will Shelley do after selecting the column header arrow for the column with heading Marks?
Uncheck (Select All), select the box beside the number 60 and click OK.
To remove a worksheet group, you can right-click the sheet tab of a sheet in the group and then click ____ on the shortcut menu.
Ungroup Sheets
Which of the following would you select on the Developer group to record a relative reference macro?
Use Relative References
PivotTables support all of the following statistical functions to summarize data within each PivotTable field EXCEPT
VSum
Which of these functions will you use to find the variance of a population of all of the values to be summarized?
Varp
Boris wants to remove a workbook window from his workplace. What series of clicks can he use to hide it?
View tab > Window group > Hide
Carol wants to unhide a workbook window she had hidden earlier. Which of these series of clicks can she press to do so?
View tab > Window group > Unhide
When should you use relative cell references?
When you want to preserve the relationship to the formula location
In the Allow list box in the Data Validation dialog box, the _____ option means that the cell will accept only integers.
Whole Number
Mila wants to restrict a cell to accept only whole numbers between 1 and 100. What should she select as X in the following clicks/entries/selections: Select cell. Data > Data Validation > Settings tab > Validation Criteria > Allow > X > Data > (select) between > Minimum > (select) 1 > Maximum > (select) 10 > OK > Enter number between 1 and 100 in cell?
Whole number
Igor wants to set margins for a worksheet he has created per his choice. Which of these will he select as X and Y in the series of clicks to do so: Page Layout tab > Page Setup > X > Margins drop-down list > Y > Specify choice for Top, Header, Left, Right, Bottom and Footer text boxes > OK?
X- Margins, Y- Custom Margins
Kathy wants to rename a table she created to list the chapters from a textbook, and the topics they cover. She selects a cell in the table, then clicks on a Tab X on the Ribbon under the Table Tools tab, clicks on the Table Name box to select the existing name, and then types the new name "Book A" and presses the Y key. What can X and Y be in this series of clicks/selections/entries?
X-Design tab, Y-Enter key
Kylie is a corporate trainer. She creates a table with three columns, with column headings as: Skill Number, Skill Name and Learning Objective. She wants to display numbers in the first column in the range of 400 to 500. What can Kylie select as X and Y in the following series of clicks or entries to do so: Click filter icon on the column heading > X > Y > (Enter value 400 in dialog box on top) > (Enter value 500 in dialog box below it) > OK?
X-Number Filters, Y-Between
The ____ criteria filter requires the records displayed to be since January 1 of the current year to the current date.
Year to Date
Which of the following is true about moving cell contents?
You can move cells using the drag-and-drop feature.
Which of the following is a valid table name?
_Newtable
You need to add another product to your inventory worksheet, which shows product names in row A. Which of the following should you add?
a column
A worksheet named Income (2) is most likely which of the following?
a copy of another worksheet named Income
mm/dd/yyyy is known as ____.
a date format
When you underline cell content, you are using _____.
a font style
Range names must begin with _____.
a letter or an underscore
Aside from Solver, other Excel _______ provide the ability to perform statistical analyses, generate business reports, and retrieve financial data from the Internet.
add-ins
When you copy a selection of cells using drag and drop, which appears before you release the mouse button?
an outline of the new location of the selected range
Solver can create the following three different reports: _______.
answer, limits, sensitivity
In the formula =SUM(A6:A9), which of the following best describes A6?
argument
Use the XNPV and XIRR functions for cash flows that appear _______.
at unevenly spaced intervals
After you select a cell with an error indicator, you can click the Error Checking _______ to display additional information about the possible error and determine if further action is needed.
button
In "SUM(number1 [, number2, number3, ...])", number 1, number 2, and number 3 may be numbers or ____.
cell references
Placing a chart in a ____ displays only the chart and no worksheet cells.
chart sheet
Excel makes reading formulas simpler by ____ each cell reference in the formula and its corresponding cell in the worksheet.
color coding
You relate tables to one another by using _______ fields.
common
To format a range so that all values greater than $500 appear in red, which of the following can you use?
conditional formatting
A(n) _______ is a limit that is placed on a Solver problem's solution.
constraint
The fill handle copies ____.
content and formats
To perform a what-if analysis with more than two input cells, you _______.
create scenarios
Audra is a manager in the Sales Department for Creative Pastry Gadgets. She needs access to up-to-date financial data and sales reports. Some of this information comes from Excel workbooks, but other information is stored in other software programs. In addition, Creative Pastry Gadgets maintains a database with detailed financial information and sales figures. Audra uses Excel to analyze data from external data sources. Audra decides to import data so it will be stored in an external data range. She learns that Excel automatically assigns the data range a defined name based on the filename of the _______.
data source
Collection of data organized to be easily retrieved and used by the end user
database
If an optional argument is not included, Excel assumes a(n) ____ value for it.
default
The process of allocating the original cost of an investment over the years of use is known as _______.
depreciation
When you have more than one sort field, you should use the Sort ____ to specify the sort criteria.
dialog box
Most Excel financial functions require a particular _______ to the cash flow to return the correct value.
direction
An Excel formula always begins with a(n) ____.
equals sign
A formula can include a reference to another workbook (called an ____ reference), which creates a set of linked workbooks.
external
To calculate a series of values from a starting point without a defined ending point, you have to _______ the values.
extrapolate
Stores information about a specific characteristic of a person, place, or thing
field
To set a page break in Excel, select the _____.
first cell below the row where you want to insert a page break
Your worksheet contains confidential information in column C; to prevent others who use your worksheet from seeing the data, you can _____ column C.
hide
To go to column A of the current row, press ____.
home
When you click a(n) ____, the computer switches to the file or portion of the file that it references.
hyperlink
Sometimes when you build a worksheet for other users, you may decide to add a(n) _____ message to a cell even if you do not validate the data in that cell
input
Edward Tufte was concerned with what he termed as "chart ____," in which a proliferation of chart elements—chosen because they look "nice"—confuse and distract the reader.
junk
You can create a(n) ____ reference by deleting the $ from the row reference in the cell reference $B$6.
mixed
To calculate the annual rate, you must _______ the value returned by the RATE function by the number of payments per year.
multiply
Each sub procedure in VBA begins with the keyword "Sub" followed by the name of the sub procedure and a set of _____.
parentheses
To change the order of operations, enclose part of the formula in ____.
parentheses
The ____ is the only one of the following charts to compare relative values from different categories to the whole.
pie
When you _____ a workbook, you control the ability users have to make changes to the file.
protect
A question you ask about the data in a database
query
A(n) _______ might ask something like, "What are the names of all the stocks in the portfolio, and what are their corresponding ticker symbols?"
query
A(n) ____ is a group of cells in a rectangular block.
range
Like cell ____, a worksheet group can contain adjacent or nonadjacent sheets.
ranges
Displays the collection of characteristics of a particular person, place, or thing
record
Databases in which tables can be joined through the use of common fields are known as _______ databases.
relational
With Goal Seek, you specify the _______ values you want.
result
In Excel, dates are _____-aligned in the cell by default, regardless of date format.
right
The default template places the formula results aligned to the ____ of a cell.
right side
In Excel, a table is a range of related data that is managed independently from the data in other ____ in the worksheet.
rows and columns
The range of values or _______, of an axis is based on the values in the data source.
scale
You can ____ the width or the height of the printout so that all of the columns or all of the rows fit on a single page.
scale
Which of the following will not change the location of the active cell?
scrolling
The Insert Function dialog box has a(n) ____ option.
search
The answer report is divided into _______ sections.
six
A _____________________ can be used to filter a PivotTable or PivotChart to a selected group of records.
slicer
Every ____ consists of an object that contains a button for each unique value in that field.
slicer
A _____ indicates a manual page break.
solid blue line
A data _______ is any file that contains the data you want to retrieve.
source
The advantage of an electronic ____ is that the content can be easily edited and updated to reflect changing financial conditions.
spreadsheet
Which of the following can help you quickly format a cell range with labels in the left column and top row, and totals in the bottom row?
table style
To move a worksheet or worksheet group to another location in the same workbook, select the worksheets and then drag and drop them by the selected sheet ____.
tabs
To create a workbook containing text, formulas, macros, and formatting that you use repeatedly, you create a _____.
template
PMT(rate, nper, pv [, fv=0] [, type=0]) In the figure above,nperstands for the ____.
total number of payment periods
A(n) _______ arrow provides a visual clue to the relationship between two cells by pointing from the precedent cell to the dependent cell.
tracer
To paste a data range so that column data appears in rows and row data appears in columns, you can _____ the data using the Paste list arrow.
transpose
When using the Format Painter, double-click the Format Painter button to paste the same format multiple times and click the Format Painter again to _____.
turn it off
You can split the worksheet window into ____ separate panes.
two or four
In the function =CUMIPMT(rate, nper, pv, start, end, type), it is _______ that defines whether the payments are made at the beginning or end of each period.
type
Any blanks or parentheses in a row or column label will be changed to _____ in defined names
underscore characters
The three items that you must specify when using Solver are _______.
variable cell, objective cell, and constraints
One way to perform a _________ is by changing one or more of the input values to see how they affect the calculated results.
what-if analysis
To save a workbook with macros, the default Excel Workbook format needs to change to a macro-enabled workbook, which has the _____ file extension.
xlsm
You need at least ____ complete years of data to project a seasonal trend for the next year.
Two
