Microsoft Excel Advanced Skills

Sarah wants a simple line graph that shows how sales totals have changed over the course of 6 months. Importantly, she wants to make sure this graphic will print with her Excel worksheet. What should she create?

A sparkline

What Excel tab has the Macro buttons in it?

Developer tab

Use the "Margins" option to change how closely the worksheet is printed to the edge of the paper.

Margins

An example of a financial function is:

PMT

Where is the 'stop a Macro recording' button located?

Under the View tab

One of the most powerful things about Excel is the variety and power of its functions to quickly analyze data.

functions

The IF, SUM and COUNTIF in Excel are all:

functions

What are the complete steps taken in a macro to add filters to the column headers?

start macro recording> click data tab> select filter button> stop macro recording

Use the "text that contains" option to set conditional formatting to highlight cells that contain a specific word.

text that contains

In order to identify the blank rows in a data table, a "zero" must be used to identify the blank row in the table.

zero

Delores is trying to create a Macro identifying blank rows in her worksheet. What function does she use to identify the blank rows in her macro?

=COUNTA (value1,[value2

What is the most accurate definition of an Excel Macro?

A way to record a sequence of keystrokes and actions in excel

What does a PivotTable in Excel primarily create?

A way to reorganized data in tables for further analysis.

What is an example of a logical function in Excel?

AND

One of the add-ins included with Excel is the:

Analysis ToolPak

The Conditional Formatting allows you to automatically apply a color or highlight to cells that meet a specific value.

Conditional Formatting

Amy is still analyzing the call center work of Mike, the newest employee. This time she wants to highlight all the calls he took so she can look at them in more detail. What Excel feature should she use to quickly label cells with Mike's name?

Conditionally format by highlighting cells that contain the text Mike

Sally just received a large worksheet with the last five years of names from the monthly mailers. She wants to quickly determine how many duplications there are in the list. What should she do?

Conditionally format with Highlight Cells Rule > Duplicate values

What is the best way for Ben to create PivotTables for his data if he is familiar with creating PivotTables already?

Create PivotTables Button

How do you change data field positions for a PivotTable?

Drag the data field to a new table area.

The AND function returns a FALSE if all the arguments in the formula are false.

FALSE

If a function searches through a range of data and returns a value's position at the intersection of a row and column, it is the INDEX lookup function.

INDEX

Where is the Marco record button in Excel?

In the View tab, Macros button.

What is the best way for Janet to integrate a website reference link into an existing Excel spreadsheet?

Insert a hyperlink to the website in the Excel spreadsheet.

Titus would like to make his products stand out a little more in his Excel spreadsheet. What actions would help to distinguish one product from another?

Insert pictures from the internet of each product in the Excel spreadsheet.

What is easier to do with a macro using the Developer tab?

It is easier to edit a macro.

If a function searches through a range of data and returns a value's position in a table, column or row this is the MATCH function.

MATCH

To use an add-in that is ready and available in Excel, select the Add-ins button and then the My Add-ins button.

My Add-ins

A function that contains more than one formula in it and tests whether something is true or false on multiple criteria is what type of logical function?

Nested IF Function

The "Orientation" allows you to automatically switch the printed direction of your worksheet.

Orientation

What does Oscar use in his recorded macro for a customer contact list to have the identical customer names removed?

Oscar uses the Remove duplicate button

The tool in Excel reorganizing data to bring about additional insights and answer questions is known as a:

PivotTable

In order to record a macro that removes duplicates from a data table it must include using the:

Remove Duplicates button

Sparkline Tools can be used to change the way a sparkline looks, such as lines instead of columns or color options.

Sparkline Tools

What happens if a macro shortcut key of Ctrl + C is used?

The copy shortcut gets overwritten and the macro runs.

Fred wants to record a Macro that removed duplicates from his worksheet. What is the key component to be included in the macro for this to happen?

Under the data tab, he uses the remove duplicate button

Rachel is preparing handouts for a training session and plans to place everything into a binder so students can write directly on the pages. If she wants the pages to be read like a regular sheet of paper but wants plenty of room for her to hole-punch them, what should she do?

Use the default orientation and change the margins to wide

What is an example of a lookup function in Excel?

VLOOKUP

If you have a function that uses the far left column in a table to calculate its formula what is that function in Excel?

VLOOKUP function

Identify the correct order to run a macro on a new set of data.

View tab > Macros > View Macros > Run relevant macro

What are the complete steps to take to record a macro in Excel?

View tab>macros button>name the macro>record macro> record steps> stop recording

What is the definition of a function in Excel?

a formula exists in Excel to easily and quickly make specific types of calculations

Jerry found a website offering a way to put additional features in Excel. He promptly downloaded it and put it into his Excel. What is this type of file or program known as in Excel?

added commands or features known as a add-in

What does the PV function do?

calculated the present value of the starting value of a loan when it taken out originally

What does the PMT function do?

calculates a loans payment terms based on a given timeframe for repayment

Mike has created a sparkline in a "line" format but realizes he would rather see the information in columns. What is the easiest way for Mike to get a column sparkline?

change the format from the Design tab of Sparklines Tools

Sarah has been working on collecting inventory data and wants a physical copy to check against the stockroom. She plans to print her worksheet but is afraid that it is too wide to fit on a piece of paper. What should she do?

change the orientation to landscape

Tina knows computer programing and wants to create a specific feature Excel does not include in its program. What is the most likely type of add-in Tina will add-in?

custom add in

In order to record a macro that adds filters to column headers it must include using the:

filter button

What is Sam trying to do with his macro when he uses the COUNTA function in his worksheet?

identify the number of cells in a range that aren't empty

Where should Yazmin go in Excel if she wanted to see specific types of functions at a glance?

select the formulas tab

The [dropdown1] tab contains the buttons for creating a sparkline in Excel.

"Insert"