Excel Certification

If you are creating a formula and want column N to be absolute and row 95 to be relative, how do you enter the cell into the formula?

$N95

What symbol do you use to concatenate (glue together) columns to look for multicolumn duplicates?

&

Choose the best answer. Which of the following contains data that a pivot table can help aggregate?

1. Average sales by day for an online clothing business during the month of May 2. Average weight loss by gender for participants eating a keto diet vs. counting calories 3. Total sales by product for six new products released for the summer line

What is the keyboard shortcut for forcing the Flash Fill feature?

<Ctrl> + E

What is a What-If analysis?

A basic form of simulation

What is one significant difference between a bar chart and a histogram?

A histogram requires one set of data whereas a bar chart requires two sets of data.

Why would investors looking to have a diversified stock portfolio view a negative covariance in stocks as a good thing?

A negative covariance means the stocks would not behave the same way, which would hedge against significant loss.

What is the term used for synthesizing multiple pieces of data into a single piece of data?

Aggregation

When merging two tables, what data would be included in an Outer Join?

All data from both tables

What mathematical concept posits that the leading digit of natural numbers most frequently is 1, followed by 2, followed by 3, through 9?

Benford's Law

When using the Solver, what is a simple test to determine whether you have linked your data correctly?

Changing the input also changes the output

Excel's CORREL() function can be used to calculate the correlation between two variables. What is an important item to keep in mind when determining correlation?

Correlation does not necessarily mean causation.

What is the first step to identifying outliers in a data set?

Create parameters regarding what constitutes an outlier.

What is one requirement for using the TRUE command when using the VLOOKUP() function?

Data in the table must be sorted.

In creating worksheets and analysis, what task likely requires the most time?

Data preparation and cleanup

What is the first-order test to determine whether data conforms to Benford's Law?

Determining the distribution of raw values

To prepare your data for creating a pivot table, the data must be "tidy." What does it mean to have tidy data?

Every column has a column header, and the cells within that column contain the data indicated by that header.

What is the exact match command, which you will use when using the VLOOKUP() function?

FALSE

What does the Flash Fill feature do?

Fills out subsequent data when it senses a pattern in the data being entered

What analysis does an optimization problem enable you to solve?

Finding the best possible solution from a collection of solutions

A powerful tool in Microsoft Excel automatically enters data when it senses a pattern in what you are entering. What is the name of this tool?

Flash Fill

What is a quick way to search for blank data in a column?

Highlight the first cell in the column and press Ctrl-[down arrow] to highlight to the first empty cell in that column.

What does the From Web button enable you to do?

Import data from a web page into Microsoft Excel

What can businesses learn from applying a Benford's Law test to data?

In some cases, fraud.

Name a disadvantage to the second-order tests for Benford's Law

It is relatively new and unproven

What is the purpose of the Convert Text to Columns wizard?

It splits data in a single column containing multiple pieces of data into multiple columns of data.

What does the VLOOKUP() function enable you to do?

Join data columns in Excel

One way to define an outlier generally is to look at the difference between the mean (average) and the standard deviation. How would you use these two values to determine where there might be outliers?

Multiply the standard deviation by an accepted value, and flag anything falling above or below that value from the mean.

The classic educational or classroom bell curve is what type of distribution?

Normal

How many inputs must you enter in order for Solver to work properly?

One

If you sort one column of data in a table without sorting all of the columns, what happens?

Only the column you sorted will sort, which will mean rows of information are now out of alignment.

What kind of pattern do you expect to see in the first two digits of ordered differences test?

Plotted results show spikes in the 10s (10, 20, 30, etc.) conforming to Benford's Law, with numbers between those 10s also conforming to Benford's Law.

All of the following statements regarding the Power Query tool in Excel are true EXCEPT:

Power Query works only if the tables getting merged both have the exact same number of rows with data.

The first step in using Power Query is to turn your data table into an Excel table. How do you do this step?

Press the <Ctrl> + T key combination.

What two functions would you use to create a Monte Carlo simulation on historical data?

RAND() and NORM.INV()

What is the first step to run a first-order test to test for Benford's Law?

Remove any items with a value that is less than zero.

What is the name of an important filter in pivot tables that filters the entire worksheet before it is ever aggregated?

Report filter

All of the following chart types can be created from within a pivot table except:

Scatter plot.

What is one way to preserve the value of a calculation without retaining the calculation in the cell?

Select the cells, copy, and use the Paste Special option to paste the values of the calculations, not the calculations themselves, in the cells.

Pivot tables are powerful aggregation tools that first do what with the data before aggregating?

Sort the data in categories

______________ is a standardized measure of dispersion.

Standard deviation

What is a probability distribution?

The mathematical function that provides the chance that each of several possible outcomes will occur

What is a simple test to determine whether variables are distributed symmetrically?

The mean is equal to the median.

What does the function AVERAGE() enable you to determine?

The mean of a data set

What is data stratification?

The process of dividing a population into subpopulations, each of which is a group of sampling units that have similar characteristics

What is the result of the function MIN(B17:B52)?

The result is the lowest value of the values in cells B17 through B52.

What is the purpose of a model in What-If analysis?

To capture important types of input that could have an impact on results

Which of the following types of charts enables you to visually represent part-to-whole relationships?

Treemap

Microsoft Excel's auto-sequencing capabilities enable you to number rows or columns sequentially without typing each of the numbers. How do you auto-sequence a row of numbers?

Type the first cell of numbers (1), click on the "magic box" in the lower right corner of the cell, press the Ctrl key, and drag across the number of cells you want numbered automatically.

What are slicers in a pivot table?

Visual filters

When would you use the VAR.P() function?

When determining the variance when you have all of the data, not just a sample of the data

Every formula needs to begin a specific way to indicate that it is a formula. How do formulas begin?

With an equal sign (=)

Microsoft Excel's Remove Duplicate (Data/Remove Duplicates) enables you to remove duplicates more quickly than finding and removing them manually. What is one disadvantage of this feature?

You do not have as much control over the process.

What is the main reason you should consider sampling rather than polling an entire population?

You do not have available resources to poll the entire population.

Every cell in a Microsoft Excel worksheet has a cell address that notes the row and column where the cell lives. What is this address called?

cell reference