Excel

If you're trying to use VLOOKUP to find an exact match, what value should replace the question mark?

0

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?

=COUNTIFA (A:A "overstock")

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

Convert the data into a table

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

buttons

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

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 does the Goal Seek tool do?

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

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

lots of ink

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

macro; an unlimited number of

How many times does Excel allow you to undo entries?

max 100

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

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

plot area

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

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

review

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

Workbooks containing _____ cannot be shared.

tables

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

0

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?

0.5

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.

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

7/6/2020

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

=SUM( )

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.

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

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.

Three of these choices are required to begin creating a PivotTable. Which is NOT required?

Begin with an equal sign and open parenthesis.

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.

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.

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.

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.

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

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

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.

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.

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

How does protecting a workbook differ from protecting a worksheet?

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

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 $258,760,000 by having Cost of Goods Sold drop, with Sales and Shipping increasing within specified dollar amounts.

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.

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.

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 should be your starting point in changing chart elements?

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

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.

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

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

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.

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? Image: InsertColumn.png AltText: "A worksheet with a new column D inserted"

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

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.

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

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.

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.

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

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.

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

above; to the left

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

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

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

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

data table

Which of the following is NOT a typical use for Excel?

designing a website

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

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

hiding rows of data you do not want to see

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?

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

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

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

What is the main purpose of the Freeze Panes command?

to keep information from scrolling off the screen

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

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

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

use the SUM function

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?

using the outlining feature in excel

When would you use a line chart to display data?

when you are comparing data that involves a time period

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

when you are just updating a file

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?

workbooks related to your excel data