Excel 2016

Toby has been asked to audit several workbooks for the finance department. As he reviews, he uses the Find feature to search for "{". What is Toby searching for?

He is looking for linkage formulas to other workbooks.

Miguel is trying to copy a worksheet to another workbook. Unfortunately, he keeps copying it to the end of his active workbook. What is the likely cause of his mistake?

He is not designating the copy to go to another workbook.

You just entered New Comp. into cell J1 of this employee compensation table. What will happen when you select Enter?

The New Comp. column becomes part of the table.

What happens if you double-click the right side of a column's header?

The column width adjusts to fit the largest entry in that column.

How would you know whether Track Changes is set on a file you are viewing?

The file name will have (Shared) at the end of it.

The solid square button on the Quick Access toolbar is a macro. What does its location on the toolbar mean?

The macro will be available with any workbook. Macros are available on any workbook where the solid square button is located.

Kathy needs to edit the chart she just made. She clicks on the title and enters =A1 from her worksheet. What is the result?

The title from the worksheet in cell A1 is added to the chart title text box.

In the image shown, what is the Formula Bar telling you?

The value in cell B6 is the result of a formula. When the focus is on a cell, the values are displayed within the formula bar.

You have received a worksheet from a coworker who told you there are a number of comments you need to review. How do you first know there are comments in the worksheet?

There is a little triangle in the upper right-hand corner of the cell.

What does this formula tell you?

This is a workbook with a destination linkage formula. Destination linkage formulas use the link name in the formula, if available.

Ajaypal has added a couple of shapes and a logo to his worksheet. He holds the shift key down as he places these items in the location he wants. What does this do?

This makes the item larger or smaller while maintaining that ratio of height to width.

The formula you are using to link worksheets is extremely long. How can you make the formula read more easily?

Use a forced line break with Alt+Enter where you would like to break up the formula.

Darren needs his team to use a standard template for reporting expenses. What advice would you give him?

Use one of the Microsoft Excel templates that is already built.

Rick is a human resources representative who is sorting through 1,000 employee records. What advice would you provide to help him be more efficient?

Use the Filter feature. This feature is a part of the Sort & Filter process that automatically adds drop-down arrows to column headers with options on sorting.

Is splitting a Name column into First Name and Last Name columns, in the manner shown, the way you should sort your customers by last name?

Yes, because when you split the columns to sort by last name in this way, Excel will bring the associated data along with it. When you split data, you can break it up into different words, and use a space to denote the break.

While Undo is a valuable feature to avoid losing important data, what limitation does it have?

You can only undo actions consecutively and cannot select an action, for example, from 10 entries earlier.

Why should macros be part of your everyday use with Excel?

You can reduce typing a sequence of steps to just a single keystroke shortcut. Macros can record repetitive clicks from a user, thus increasing performance.

If you are satisfied with the print preview of a worksheet you set up in Landscape, why might you not be as satisfied if you change it to Portrait?

You might find the column or columns to the right going onto a second page if you use Portrait.

If you highlight a column and then click the button to Sort Alphabetically (A-Z), what happens?

You will be prompted with a dialog box asking how you would like to sort.

Jane is typing a description of a product in a cell. After she types a line, she presses Alt+Enter (Shift+Alt/Command+Return). What does this action do?

adds a manual line break to the data within a cell

Suppose cell B2 in a spreadsheet contains the formula: =if(A1>100,'bazinga','zippo") If cell A1 contains the value 425, what value appears in cell B2?

bazinga

Where can you find the command for Excel to calculate the MAX value into cell G2?

by selecting the arrow under the AutoSum button (∑) at the top left of the ribbon

The worksheet contains employee data, such as departments, status, years of service, salary, and other information. If you use the sort shown, what sorting will be returned?

by status, then years of service, and finally employee name within a Sort by department, sorted A to Z alphabetically

You have created a small worksheet and need to calculate the amount of inventory you have left from Widget A month after month. When creating your formulas, what do you need to consider?

cell references

When creating an IF function, a _____ separates the logical test from the value if true/false.

comma

Which of the following is NOT a typical use for Excel? (designing a website; creating a budget spreadsheet; tracking customer information; analyzing sales data)

designing a website

When you apply a filter, what are you actually doing?

hiding rows of data you do not want to see

Your worksheet has a column labeled Profit. When you set up the column in dollar sign and comma format, you notice there is some space to the right of the numbers. Why is this?

to allow for parentheses if the number is negative

When you are working with PivotTable, why would you group data?

to create new data categories comprised of data from multiple fields. PivotTables allow the grouping of data, thus creating new categories.

How can you access font styles and effects other than what is presented by default on the Home ribbon tab?

Click the dialog box launcher in the bottom right-hand corner of the Font section.

You apply a macro to cell E4 after turning cell D3 bold. You realize you do not want the macro applied in cell E4. What can you do to remove the macro?

Copy and paste the formatting from another cell in the column into cell E4. By copying and pasting the contents of the cell in the column, you can undo the changes.

There is a formula in cell B4, =B2-B3. If you drag the fill handle to the right, cell C4, what is Excel actually doing behind the scenes?

Excel is adjusting the cell references in the formula.

Kumar just received a lengthy worksheet with the work status of all employees. He is interested in a particular employee named James. What should he do to narrow the list down?

He needs to set the Match case when doing a Find.

Katrina has a list of products and their costs in her worksheet. She has set the more costly products to be highlighted with a color. How has she done this?

The format was applied based on some conditional formatting.

This worksheet has a formula in cell E2, =DATEDIF(D2,TODAY(),"Y"). What will happen if you insert a new column D?

The formula will adjust to =DATEDIF(E2,TODAY(),"Y").

The shape at the upper right, "Great job, Jan !!!" is smaller than you would like it to be. How can you ensure you are not changing the height/width ratio as you enlarge the shape?

Drag the text box from the corner handle, while you select and hold the Shift key.

What is the basis of using formulas in Excel?

Formulas are usually based on location.

What is the maximum number of worksheets that can be undeleted using the Undo command?

0

Which tool will allow you to come up with a new total based on one or more cells changing?

Solver

Why should you avoid using this chart type if you are printing this as part of a report?

It will use a lot of ink from your printer.

If there are negative values in your data, why would you not want to use this chart type?

Negative values are not shown on a pie chart.

Your chart has the headings Product and Sales by Region. You would like to change Axis Title to the left of this chart to Sales in Thousands. How will you do this?

Select Axis Title to the left, and then enter Sales in Thousands in the text box.

The inner area of a chart is known as the _____.

plot area

When would you save a file and not have to use Save As?

when you are just updating a file

What is the main purpose of the Freeze Panes command?

to keep information from scrolling off the screen

The most effective way to add up five different cells in a row is to _____.

use the SUM function

When would you use a line chart to display data?

when you are comparing data that involves a time period

Johanna wants to copy a formula into an adjacent cell. Which action should she take?

Hold down the left mouse button on the fill handle and drag to the right.

How can you use AutoFill to provide all 12 months in a year across the columns in row 1 of a data set, starting at column B?

Enter Jan in cell B1, and then drag the fill handle to cell M1.

If you want to set up Data Bars, Color Scales, or Icon Sets, where should you go?

Home > Conditional Formatting

If you're trying to use VLOOKUP to find an exact match, what value should replace the question mark? (=VLOOKUP(B2,D:F,3,?)

0 or False

You intended to enter 110 in a series of 10 values, but you inadvertently typed 11O. How will Excel tell you that you made a data entry error?

11O will be left-aligned, while the other values will be right-aligned.

What is Excel?

A program used for maintaining large lists of data, a spreadsheet package, and an application where data can be illustrated with a chart.

Akio likes most of his worksheet preview, but needs to make it less than 15 pages. Which action is the best choice for him?

Adjust the Scaling to "Fit to: 1 page(s) wide by 15 tall."

Karl is trying to figure out what he should name his Excel file before saving it. Which should he avoid when creating a file name?

Avoid using an asterisk.

Three of these choices are required to begin creating a PivotTable. Which is NOT required? (Headings should be in a single row at the top of the source data; Begin with an equal sign and open parenthesis; Source data must be organized as a list or table; Source data must not have empty rows or columns)

Begin with an equal sign and open parenthesis.

Suppose you create a PivotTable and then make changes to the source data for that PivotTable. You return to the PivotTable, and notice that the changes are not showing up. What do you need to do?

Go to Analyze > Refresh to update the PivotTable.

Karl is working with several workbooks and wants to see the contents from each one. Which action would be most efficient for him to do so?

He should use the Arrange All feature under View.

Jason is reviewing his employee's worksheet. He wants to see the details behind some of the numbers, especially the totals. If he clicks on a cell with a total, where would he see the details?

In the formula bar

What is the Mini-Toolbar?

It is a limited-feature bar that contains many of the features contained on the Home tab of the toolbar.

You work for ABC, which is a company that distributes meat nationwide. ABC is changing the name of its ham products to Virginia Ham. When you update your customer list of 5,000 names, is entering the word ham in Replace All the way to do it?

No, because you will also inadvertently replace customer names, cities, and states that have the word ham in them.

You have created a short summary of your department's budget; however, you noticed that certain entries are not aligned correctly. What is the general rule you should follow?

Numbers are always aligned on the right, and text is on the left.

Thomas wants to set up his worksheet with a password. Which option would he use for allowing others to open and edit his workbook?

Password to open

Carol needs to protect the structure of her Excel file. Which feature should she use?

Protect Workbook

In this grouping of dates, what values would be in the Filters area and the Rows labels area of a PivotTable Fields List?

Quarters is in the Filters area, and Years below Months is in the Rows labels area.

What are the constraints shown in Solver telling Excel to do?

Return a Gross Profit of $265,000 by having Cost of Goods Sold drop, with Sales and Shipping increasing within specified dollar amounts.

To control the display of comments, what tab should you go to?

Review

Olivia needs to add a comment to a cell. What is the quickest way for her to do so?

Right-click on the cell and select New Comment in the shortcut menu.

Which tool would a sales manager use to forecast various sales perspectives?

Scenario Manager. This tool allows you to change or substitute input values for multiple cells.

You have three workbooks open that you are working on. How can you most easily navigate from one workbook to another?

Select Switch Windows in the View tab on the ribbon.

What is the primary advantage of Slicers when viewing a PivotTable?

Slicers allow you to see which fields in the PivotTable are being shown and which are hidden.

You have a worksheet with numbers represented as dollars, percentages, and normal formatting. What happens when you try to realign some of these?

The format of the numerical entry will determine whether the alignment occurs.

Ramesh is entering text into a worksheet, but everything he types is in capital letters. Which indicator in Excel lets him know this?

The status bar will read "Caps Lock" in the lower left corner.

If you make the selection shown, what will the result be on your spreadsheet?

There will be a solid line to the left of the highlighted cells, a dashed line around the full range of cells, and a dashed line around each of the cells both vertically and horizontally.

What is true about formatting cells to a particular category, such as Accounting or Currency?

This adjusts the display of your data, but not the data itself.

You are working in Excel and can't remember where a certain feature is. Which Help step would provide you a list of possible options for your situation?

Type your feature in the "Tell me what you want to do" field.

You are working on a spreadsheet and have entered several lines of data and text. You need to adjust the two lines you just entered. Which action is the best to take?

Undo the lines.

How will you correctly write a formula in cell C5 for year-to-date profits that you can drag across for the next four months?

Use =C4+B5

Margo is preparing for a presentation to her staff on the year's overall budget numbers. How will she provide a summary and details as needed?

Use the Outlining feature in Excel.

You are setting protections for a workbook under Save As > Tools > General Options. If you enter a password into the Password to open option but not in the Password to modify option, what does this mean for other users?

Users you give the password to can open the workbook with that password, and any user who can open the workbook can also modify it.

Mike wants to insert a new row into his data without disturbing an adjacent set of data on the same sheet. He right-clicks his mouse and sees Insert with three dots. What will happen?

When he clicks on that, he will see a dialog box where he can shift cells down. The three dots after Insert will display a dialog box of options to shift cells, or add entire rows and columns.

You are working on a data set with customer names in the Name column, in the form of first name and last name in all caps. What is the power of using Flash Fill to set up the data the way you prefer?

You can create a new column, enter the first name on the list the way you prefer, and then select Flash Fill so the column will populate.

Slicers provide you with _____ for filtering PivotTable data and visualizing what you have filtered.

buttons

Which action with a PivotChart is the same as dragging data from a column into a row?

dragging an item from Legend into Axis Category

You would like to add commands to the Quick Access Toolbar that you use frequently. Where on a drop-down menu can you access the full list of available commands?

in All Commands

What does the Goal Seek tool do?

lets you adjust a value used in a formula to reach a desired outcome

Date entries are numbers and by default they _____.

line up on the right side of the cell

A _____ is a way to automate a sequence of steps in your work, and can include _____ steps.

macro; an unlimited number of

If you want to have a vertical split, where will you place your cursor before selecting Split on the View tab of the ribbon?

on the active cell in the first row where you want the split to start

Which type of chart would you use if you wanted your chart to be automatically updated with data?

pivot table

What does data validation allow you to do?

restrict or limit data entry to your specifications (i.e. only positive numbers, maximum of 10 characters, etc.)

Excel's menu system is known as the _____.

ribbon

Ravi presses F12 (Command+Shift+S) and then clicks on the Tools button. What is he doing?

saving the file and setting up a password for the worksheet

Jason has a worksheet with over 500 rows and 20 columns. He needs to sort it, and decides to select a cell and press Cmd/Ctrl+A. What does this do?

selects the entire worksheet of data

When moving or copying data from one cell to another, in order to maintain the formulas of the original cell you must _____.

simply complete the action. Excel maintains the formulas for you.

Workbooks containing _____ cannot be shared.

tables

You have received a worksheet in which the first and last name of each employee is in one cell. Which feature will enable you to break out first and last names into two columns?

text to columns

How can you access Insights, which takes you outside of Microsoft and into the web?

the Smart Lookup feature in a drop-down menu on the ribbon

What does the solid blue line indicate in Page Break Preview mode?

the boundary between data that that will and won't print

Your data set is total sales per month. What does the value $500.0 in the lower right area of the Status Bar tell you?

the largest dollar amount of sales across all 12 months

Tony is exploring new ways to represent his company's breakout of department contributions to the bottom line. Which type of chart would classify the data in a block view?

treemap

How many times does Excel allow you to undo entries?

up to 100

When will you use Save rather than Save As?

when you make changes to an existing workbook

In Excel, a standalone file is called a _____.

workbook

What kind of workbooks are you looking at in this image? (where you get templates)

workbooks you have frequently used and are suggested by Excel

Janis wants to be creative with her data in a worksheet. She has selected a section of data and presses Alt+F1 (Fn+Option+F1). What will the result be?

A chart from the data she selected is created.

In order to use Slicers to filter some data, what must you first do to that data?

Convert the data into a table.

Germain is creating a worksheet with a master formula =PMT. Which feature is she using?

Data Table

You have a spreadsheet, and want to create a macro for the cells with just numbers. Which of the following series of actions would you take to ensure your macro works properly in this case?

Go To Special, click on Constants, then uncheck all Formulas except Numbers.

Steve is making a chart of data from his worksheet. He has selected his data and inserted a chart; however, the visual of the numbers is distorted. What is the likely cause of this issue?

He selected the totals from his worksheet to include in the chart.

Using the Remove Duplicates feature is a powerful tool. However, before you can use it to remove the duplicate data of an employee, what do you have to do with your data?

Make sure there are no empty rows or columns.

How does protecting a workbook differ from protecting a worksheet?

Protecting a workbook deals with structure, while protecting a worksheet deals with content.

What should be your starting point in changing chart elements?

Select the chart, and then select the + sign to the right.

Elias has three workbooks open and needs to be able to quickly navigate to each of them. Which feature should he use?

Switch Windows under View

After you adjust the width of column F containing numerical data, these number signs (####) appear. What is Excel telling you?

The columns are too narrow for the length of the numbers.

Hershey has created a macro in a particular workbook to see Visible data with a shortcut, Ctrl V (Command+V). However, after copying some data, he noticed the Ctrl V (Command+V) to paste the data did not work. What is the likely cause of this issue?

The shortcut he created for the macro overrides Excel's shortcut for pasting.

Shakira is trying to print a six-page worksheet. She just doesn't like the way it looks. What advice could you provide to Shakira to help her be more efficient and prevent wasting paper?

Use the Print Preview feature first.

You have a worksheet with company budget projections for the coming year. If you change the sales projection in Scenario Manager, what is the result?

You will be able to view what revenue would be based on that value, without affecting the original data set.

Jeremy is formatting a worksheet for his sales team. He wants to highlight several lines of information and presses Ctrl+1 (Command+1). What does this action do?

brings up the Format Cells dialog box

Your notice that a worksheet displays columns A, B, C, E, F, and G. What happened to column D?

column D is hidden

Some of the buttons in the ribbon have a small arrow in the bottom right corner. What are these arrows?

dialog box launchers

You want to get the percentage increase in profits from January to February. If you use the formula shown below (=C4-B4/B4), what value will be returned?

29

If you type 7/6/20 into a cell and press Enter, what data will be displayed by default?

7/6/2020

Suppose cell A2 contains the formula =B2+B3. You select A2, then drag the AutoFill fill handle (in the lower-right corner of A2) down several cells. Which formula now appears in cell A3?

=B3+B4

Suppose you have a long list of text values in column A. You want to calculate how many times the word "overstock" appears in column A. What function should you use?

=COUNTIF(A:A,"overstock")

What does it mean that Conditional Formatting is dynamic?

If you change a value in a cell, the formatting will apply automatically.

Rafael thought he had enough security on his worksheet by locking cells, but someone was still able to access the data in the worksheet. What did Rafael forget to do?

Protect the entire workbook.

The title in cell H3 is meant to read First Half Total. How can you make the full title appear without widening the column?

Use Wrap Text.

Your data is in six rows. If you create a sparkline using columns, what caution is there in making comparisons?

You cannot use the height of the sparkline columns to compare data between rows.

For some reason, such as an interruption, you selected Delete in the warning pop-up for a sheet that you were not sure you wanted to delete. What have you done?

You have deleted the sheet, and you will not be able to use Undo to undo the delete.

You want a PivotTable showing Salesperson, sales by salesperson, and sales by product. With these choices in the PivotTable Fields dialog box, what will appear in your PivotTable?

a column of names with Row Labels heading, a Sum of No.Items above that, headings for each product, and values across each row of names. When using a PivotTable, the columns will be displayed across the heading.

In Excel 2016, which type of workbook do you have to use to enable Track Changes?

a shared workbook

You are using your mouse (or cursor) within your Excel sheet. If you slide leftward and put it on a boundary between rows, what does your cursor look like?

a two-way up-down arrow

Jake has a worksheet with many columns of data. He wants to see different parts of a list at the same time. Which action would be best for him to take?

split vertically

The zoom slider bar lets you enlarge or shrink the data shown on screen. Where is it located?

status bar at bottom of screen

The highlighted worksheet shows collapsed subtotals of a much more detailed worksheet. If you copy and paste this into a new worksheet, what will happen?

All data from the worksheet will be displayed, not just the subtotals you highlighted.

What does it mean if you are sharing a workbook and you keep the following default option? Ask me which changes win

If another user makes a change to a workbook you just changed, Excel asks whether you want to retain that user's change.

You are giving a presentation of your sales for the year across all regions and by all months. Why are you considering using outlining?

Outlining can collapse the spreadsheet into, for example, only quarterly totals to make the spreadsheet easier to read.

You used the Tiled selection under Arrange All to display three open workbooks. To more easily work with the data, you want a particular workbook to fill the screen. What can you do?

Select the Maximize button for that workbook.

Assuming all your worksheet names are color-coded, what action did you just take, or are you taking, with the East, South, Midwest, and Pacific worksheets?

You have grouped the four sheets.

You created this argument for an approximate VLOOKUP. What will you enter into the argument, after the last comma, for the column that has the answer?

You will enter 2. You use the number 2 to denote the column.

Which type of chart is shown in this image?

a Pareto chart. A Pareto chart contains columns of data with curved lines and percentages as labels.

If you use the Alt+F1 shortcut to create a chart, which chart type will appear?

a clustered column chart

The AutoSum tool first looks for values _____ the selected cell, then looks for values _____ of the selected cell.

above; to the left

Suppose a cell contains the formula =(E3*$H$2) * E3. The variable $H$2 is a(n) _____ reference.

absolute

Jhane is the regional manager for 10 states in the Northeast. She is presenting her region's sales and has used a formula in her worksheet: =sum('Maine:Pennsylvania'!) What is this formula doing?

adding data for Maine through Pennsylvania into a new worksheet

To create a chart, you begin by _____.

selecting data

You are about to print a report so you go into Page Setup > Header/Footer. What can you add to the printed pages that CANNOT be added from any other page setup location?

the date of the report

Marcus would like to have subtotals in his worksheet. What must he do before using this feature?

Sort the data first.

The master formula for the data table you are creating is =PMT(B1/12,A1,B2). What values will you enter in the Data Table text box?

$B$1 in Row input cell and $B$2 in Column input cell. The row and column number need to be specified using the dollar signs between them.

Which formula would you enter in cell J3 to derive a meaningful value?

=COUNT(A2:G13)

What is the best formula for adding together five adjacent values in a column?

=SUM( )

If you're using the VLOOKUP function for an approximate match, not an exact match, how should data in the left column of the lookup table be arranged?

In numerically ascending order

What are the limitations when entering a macro name in the Record Macro text box?

It cannot begin with a number, and it cannot contain spaces.

You do not want anyone to make changes in column G, and you do not want column H to be visible. What should you do?

Lock column G, hide column H, and then select Protect Sheet on the Review tab.

Suppose you highlight a column of data in an Excel worksheet. In the status bar below, you'll see all of the following by default, except: _____.

Minimum and Maximum Values. You will see Average and Count, Average and Sum, and Caps and NumLock.

You want to keep columns A and B visible while you scroll through your other worksheet data. How will you do this?

Position the active cell as C1, go to Freeze Panes on the View tab of the ribbon, and then select Freeze Panes.