Functions

Question 1

CONCAT

Answer 1

=CONCAT is one of the easiest to learn but most powerful formulas when conducting data analysis. Combine text, numbers, dates, and more from multiple cells into one.

makes everything a string

Formula:
=CONCAT(SELECT CELLS YOU WANT TO COMBINE)

Question 2

LEN

Answer 2

=LEN quickly provides the number of characters in a given cell. As in the example above, you can identify two different kinds of product Stock Keeping Units (SKUs) using the =LEN formula to see how many characters the cell contains. LEN is especially useful when trying to determine the differences between different Unique Identifiers (UIDs), which are often lengthy and not in the right order.

Formula:
=LEN(SELECT CELL)

Question 3

What does COUNTA do, and how is it useful?

Answer 3

The Microsoft Excel COUNTA function counts the number of cells that are not empty as well as the number of value arguments provided.

COUNTA will allow you to evaluate any gaps the dataset might have without having to reorganize the data.

Formula:
=COUNTA(SELECT CELL)

Question 4

DAYS/NETWORKDAYS

Answer 4

=DAYS is exactly what it implies. This function determines the number of calendar days between two dates. This is a useful tool for assessing the lifecycle of products, contracts, and run rating revenue depending on service length – a data analysis essential.

=NETWORKDAYS is slightly more robust and useful. This formula determines the number of “workdays” between two dates as well as an option to account for holidays. Even workaholics need a break now and then! Using these two formulas to compare time frames is especially helpful for project management.

Formulas:
=DAYS(SELECT CELL, SELECT CELL)
OR
=NETWORKDAYS(SELECT CELL, SELECT CELL,[numberofholidays])

Question 5

SUMIFS

Answer 5

=SUMIFS is one of the “must-know” formulas for a data analyst. The common formula used is =SUM, but what if you need to sum values based on multiple criteria? SUMIFS is it. In the example below, SUMIFS is used to determine how much each product is contributing to top-line revenue.

Formula:
=SUMIF(RANGE,CRITERIA,[sum_range])
note: [sum_range] is optional

Question 6

AVERAGEIFS

Answer 6

Much like SUMIFS, AVERAGEIFS allows you to take an average based on one or more criteria.

Formula:
=AVERAGEIF(SELECT CELL, CRITERIA,[AVERAGE_RANGE])
note: [average_range] is optional

Question 7

VLOOKUP

Answer 7

VLOOKUP is one of the most useful and recognizable data analysis functions. As an Excel user, you’ll probably need to “marry” data together at some point. For example, accounts receivable might know how much each product costs, but the shipping department can only provide units shipped. This is the perfect use case for VLOOKUP.

In the image below we use reference data (A2) combined with the pricing table to have excel looking up matching criteria in the first column and returning an adjacent value.

Formula:
=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM, [RANGE_LOOKUP])

Question 8

FIND/SEARCH

Answer 8

=FIND/=SEARCH are powerful functions for isolating specific text within a data set. Both are listed here because =FIND will return a case-sensitive match, i.e. if you use FIND to query for “Big” you will only return Big=true results. But a =SEARCH for “Big” will match with Big or big, making the query a bit broader. This is particularly useful for looking for anomalies or unique identifiers.

Formula:
=FIND(TEXT,WITHIN_TEXT,[START_NUMBER]) OR =SEARCH(TEXT,WITHIN_TEXT,[START_NUMBER])

Question 9

IFERROR

Answer 9

=IFERROR is something that any analyst who actively presents data should take advantage of. Using the previous example, looking for specific text/values in a dataset won’t return a match. This causes a #VALUE error, and while harmless, it is distracting and an eyesore.

Use =IFERROR to replace the #VALUE errors with any text/value. In the example above, the cell is blank so that data consumers can easily pick out which rows returned a matching value.
Formula:
=IFERROR(FIND“VALUE”,SELECT CELL,VALUE_IF_ERROR)

Question 10

COUNTIFS

Answer 10

=COUNTIFS is the easiest way to count the number of instances a dataset meets a set of criteria. In the example above the product name is used to determine which product was the best seller. COUNTIFS is powerful because of the limitless criteria you can input.
Formula:
=COUNTIFS(RANGE,CRITERIA)

Question 11

LEFT/RIGHT

Answer 11

=LEFT, =RIGHT are efficient and straightforward methods for extracting static data out of cells. =LEFT will return the “x” number of characters from the beginning of the cell, while =right will return the “x” number of characters from the end of the cell. In the example below, =LEFT is used to extract the consumer’s area code from their phone number, while =RIGHT is used to extract the last four digits.

Formula:
=LEFT(SELECT CELL,NUMBER)
OR
=RIGHT(SELECT CELL,NUMBER)

Question 12

RANK

Answer 12

=RANK is an ancient excel function, but that doesn’t downplay its effectiveness for data analysis. =RANK allows you to quickly denote how values rank in a dataset in ascending or descending order. In the example, RANK is being used to determine which clients order the most product.

Formula:
=RANK(SELECT CELL,RANGE_TO_RANK_AGAINST,[ORDER])
note: [order] is optional

Question 13

MINIFS

Answer 13

=MINIFS is very similar to the min function except it allows you to take the minimum of a set of values, and match on criteria as well. In the example, =MINIFS is used to find the lowest price each product sold for.

Formula:
=MINIFS(RANGE1,CRITERIA1,RANGE2)

Question 14

MAXIFS

Answer 14

=MAXIFS, like its counterpart minifs, allows you to match on criteria, but this time it looks for the maximum number.
Formula:
=MAXIFS(RANGE1,CRITERIA1,RANGE2)

Question 15

SUMPRODUCT

Answer 15

=SUMPRODUCT is an excellent function to calculate average returns, price points, and margins. SUMPRODUCT multiples one range of values by its corresponding row counterparts. It’s data analysis gold. In the example below, we calculate the average selling price of all our products by using sumproduct to times Price by Quantity and then divide by the total volume sold.

Formula:
=SUMPRODUCT(RANGE1,RANGE2)/SELECT CELL

Question 16

Paste Special

Answer 16

Paste Special enables you to pick which elements of the copied cell you bring over. After you’ve copied your cell (Ctr+C) hit Ctrl+Alt+V (or go to the Clipboard section of the Home ribbon, or Edit > Paste Special) to bring up Paste Special and make your selection. Alt+E+S+V is the shortcut to just paste values

Question 17

Add Multiple Rows

Answer 17

The shortcut (Ctrl, shift, +) is pretty handy, especially as you can toggle the + to add multiple rows. Often, just highlighting the number of rows you want to add (say 5) and using right-click, insert is quicker when adding in bulk as it will add the number of rows you’ve highlighted.

Question 18

Flash Fill

Answer 18

Fill automatically fills your data when it senses a pattern.
Suppose you have a list of product numbers in the first ten cells of column A, e.g. ‘ABC-00001’ to ‘ABC-00010’ and you only need the numbers after the ‘-’. You can easily discard the ‘ABC’ using Flash Fill. Pre-2013 this was possible, but relied on a combination of functions (FIND, LEFT, &, etc). Now, this is much faster and will impress people.
Establish the pattern by typing ‘00001’ into the first blank cell. If Flash Fill is turned on (File Options, Advanced) just start to type the next product number in the cell below and Flash Fill will recognize the pattern and fill down the remaining product numbers for you. Just hit the Enter key to accept. Or, get it going manually by clicking Data > Flash Fill, or Ctrl+E.

Question 19

INDEX-MATCH

Answer 19

Aside from VLOOKUP (which looks up the value in one column and returns a corresponding value from another column), INDEX and MATCH are the most widely used and most powerful tools in Excel for performing lookups. Used separately, these functions are invaluable, but it’s when you combine them that their true power is unleashed. INDEX and MATCH used in combination help you extract the data you need from a large dataset efficiently and precisely. Mastering these functions will not only make you look like an Excel whiz to your colleagues and manager, but can make a tedious, mundane task quick and simple. Here’s how these functions work:
VLOOKUP is a great function, but it has its limitations. It can only look up values from left to right. The lookup value must be on the left in the lookup table. INDEX and MATCH allows you to look up a value anywhere in the lookup table regardless of its position.
Let’s say you have a spreadsheet with a list of products. You have columns titled “Product Number”, “Profit”, “Product Name”, and “Revenue”. On another spreadsheet, you have a list of the product names and you want to look up how much profit each product has generated. In this scenario, we are using the product name (our lookup value) to look up the profit. The product name sits to the right of the profit and so VLOOKUP would not work. This is the perfect scenario for INDEX and MATCH.
The syntax would be:
=INDEX(Profit column,MATCH(Lookup Value,Product Name column,0))
Here’s a good way to remember how it works:
=INDEX (Column I want a return value from, MATCH (My Lookup Value, Column I want to Lookup against, Enter “0” )) (Zero gives you an exact match, you can match against less than (-1) or greater than (1) as well.)
At first and even second glance, INDEX, and MATCH look complex. It certainly needs some practice, but it’s worth getting your head around as it is more flexible and more powerful than a VLOOKUP. It’s one of the most important uses of Excel, period.

Question 20

SUM

Answer 20

This is one of the first functions you’re likely to learn in Excel — how to sum a row or column. But did you know you can select the cell at the end of a row or column and press Alt + to do this functions in seconds?

Question 21

CTRL Z / CTRL Y

Answer 21

This is your Excel get-out-of-jail-free card. If you aren’t using Ctrl Z to undo mistakes in Excel, then you should be. What many people don’t know is that Ctrl Y does the opposite — redo. The two work in tandem very nicely, and you cycle through iterations of your work until you find the right one.

Question 22

Remove Duplicates

Answer 22

This is so simple and quick to use. Remove Duplicates does exactly what you’d expect — it removes the duplicates in any given range of data. Our advice is to remove the values you want to dedupe and place them in another sheet. It’s found on the Data tab in the Data Tools section of the Ribbon.
If you just want to highlight duplicates, you can do this using Conditional Formatting. The shortcut to get you there is Alt H L. (Or find it on the Home ribbon under Styles).

Question 23

Freeze Panes

Answer 23

Ever scroll down a large table of data only to forget which columns are which? Freeze Panes is the answer here. You can freeze just the top row, first column or any number of either. Identify the columns and rows of the area you want to freeze. Then select the cell immediately to the right of those columns and beneath those rows. Go to the View tab and Freeze Panes in the Window section. Alt W F is the shortcut.

Question 24

F4

Answer 24

There are two especially satisfying ways to use F4 in Excel. The first is when creating an Absolute Reference: F4 toggles you through the various options. The second is one that few people know about, but could seriously improve your Excel productivity. F4 repeats your last action, where available. For example, if you’ve just applied a border to one cell, use F4 to apply it to others.

Question 25

CTRL + Arrows

Answer 25

If you’ve found yourself scrolling through a dataset to reach the bottom of it, stop right now and start using Ctrl + the arrow keys! This simple shortcut takes you straight to the end of the data in a column or row you are using. Combine it with Ctrl, Shift to highlight/select large areas of data in seconds.
Warning: If you have gaps in your data this will just take you down to the first gap. So if you want to get to the bottom fast, choose a column of data that has no gaps.