EXCEL LINKED-IN QUIZ

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

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

pivot chart

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.

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

to create new data categories comprised of data from multiple fields

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.

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.

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.

What does this formula tell you?

This is a workbook with a destination linkage formula.

This worksheet has a formula in cell E2, =DATEDIF(D2,TODAY(),"Y"). What will happen if you insert a new column D? Image: InsertColumn.png AltText: "A worksheet with a new column D inserted"

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

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.

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.

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.

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

Excel's menu system is known as 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

You are about to print a report. If you go into Header/Footer from the Page Setup commands box, what can you add to the pages you print that you CANNOT add from any other page setup location?

the date of the report

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 Hint

When will you use Save rather than Save As?

when you make changes to an existing workbook

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

0

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

=SUM()

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.

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

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

Home > Conditional Formatting

What does it mean that Conditional Formatting is dynamic?

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

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.

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.

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.

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.

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

use the SUM function

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.

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.

Which type of chart is shown in this image?

a Pareto chart

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.

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

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.

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

Protect Workbook

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

Scenario Manager

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.

Assume you want all employees to receive a 2.1 percent pay increase. If the value shown in cell H1 is 2.1 percent, what is the correct formula to use in cell F2 and to drag down to the other rows?

When you copy this formula, Excel will change H1 to H2, which has a zero value, and so on down the column.

Date entries are numbers and by default they _____.

line up on the right side of the cell

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

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.

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.

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

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

What should be your starting point in changing chart elements?

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

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

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.

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.

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

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

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

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.

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

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.

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.

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

dialog box launchers

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

To create a chart, you begin by _____.

selecting data

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

status bar at bottom of screen

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

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.

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

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

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

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.This enables you to change the size of the image while keeping the content ratio of height to width.

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

a clustered column chart

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 formula would you enter in cell J3 to derive a meaningful value?

The COUNT function is used to add all the values together.

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.

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

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.

What is Excel?

all of these answers

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.

You want to get the percentage increase in profits from January to February. If you use the formula shown below, what value will be returned?

Because there are no parentheses, Excel's hierarchy of operations will divide B4 by B4 (=1), and then subtract it from C4 (30-1=29).

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.

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.

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

a shared workbook

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

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.

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

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

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.

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.

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.

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.

Workbooks containing _____ cannot be shared.

tables

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.

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.

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.

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

What is the basis of using formulas in Excel?

Formulas are usually based on location.

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.

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.

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.