Amdall Gallery

Welcome to my quick-reference page for Excel functions! Over the years, I’ve amassed a large collection of spreadsheets; if you’ve explored this website much, I’m sure you’ve seen graphs from some of them. Throughout all of this analytical fun, I’ve used a wide variety of functions and formulas to process data. I previously shared part of my master list via blog post, but I thought I’d make the cheat sheet a bit more permanent by embedding it in a menu.

My list of what I think are the most useful functions is provided below. Basically, I’ve indicated what the function does or its best use in bold, then a short explanation with the function in block quotes immediately below it.

Important Note: If you copy/paste one of these functions into Excel, you will probably have to retype any quotation marks (“). For some reason, this websites’s font does not transfer well.

 

Everything left of a comma:

Useful for pulling the city from a [city, state] field. Gets everything to the left of a comma. Change the comma within the quotes to whatever else you want; it will retrieve text from the left of anything in those quotes.

=LEFT(B3,SEARCH(“,”,B3)-1))

Everything right of a comma:

Useful for pulling the state from a [city, state] field. Gets everything to the right of a comma. Same as above, change the comma within quotes to something else as needed.

=RIGHT(B4,LEN(B4)-FIND(“,”,B4)))

Vlookup (referencing other data to return a result):

Create a basic table, first column should be a searched value in your main data, second column should be what you want to display. This searches your main table and reports back a matching second (or whatever) column.

B13 is your lookup value, the next portion is a static search location in the spreadsheet, 2 is the column number to return text from, and FALSE means you want exact matches only.

=VLOOKUP(B13,’Data Tab’!$S$4:$T$430,2,FALSE)

Percentage Change:

For determining the percentage change between two values (% increase = positive, % decrease = negative). T2 is the final/most recent number, L2 is the initial value

=((T2-L2)/L2)

Blank if nothing:

For almost all functions, you can start it with this to make the result an empty cell if another cell is blank. It can be very useful, but is entirely optional.

=IF(A11=””,””,{the rest of your function}

Combine text from different columns:

In this example, text from C11 will display with text from B11 in parenthesis. If A11 is blank, it will show an empty cell (uses the function above).

=IF(A11=””,””,CONCATENATE(C11,” (“,B11,”)”))

Show only the year from a date:

=TEXT(M11,”yyyy”)

This might also work:

=YEAR(M11)

Change the format of a phone number to all dashes:

This will probably work to change format of phone numbers, social security numbers, or any other pre-formatted number.

=TEXT(M23,”???-???-????”))

Is a date more than 60 days ago?

B9 is the target date, it should show TRUE or FALSE depending on whether it’s been 60 days since the target (based on today’s date).

=TODAY()-$G9>60)

Number of days from a certain date:

G13 is the target date.

=DATEDIF(G13,TODAY(),”d”))

Fiscal year of a given date:

J3 is the target date, Y5 is a static month number (reference cell) indicating the fiscal year’s starting month. For some strange reason, to get a fiscal year start of October 1st, Y5 should be a value of 4.

=YEAR(DATE(YEAR(J3),MONTH(J3)+($Y$5-1),1))

Separating a file name from a file path:

Find everything to the right of a character type. Similar to the functions above that I used to separate things to the left or right of a comma. Difference here is that file paths usually have multiple of the target/searched character, so using the above functions won’t do the trick.

Best method:

=TRIM(RIGHT(SUBSTITUTE(C2,”\”,REPT(” “,LEN(C2))),LEN(C2)))

Alternative method that also works:

=MID(C16,FIND(“=”,SUBSTITUTE(C16,”-“,”=”,LEN(C16)-LEN(SUBSTITUTE(C16,”-“,””))))+ 1,256)

Find a portion of text in a cell, return different cell results if the text is found:

FALSE is what is being searched, G11 is the cell to search. F11 is the value if true, E11 is the value if false.

TRUE/FALSE Result:

=IF(ISNUMBER(SEARCH(FALSE,G11)),F11,E11)

In the next example, the function is searching for any text that says “Document” in cell AV2, then returning text from AT2 if true, AV2 if false.

Text Result:

=IF(ISNUMBER(SEARCH(“Document”,AV2)),AT2,AV2)

Does a cell contain error text?

Useful when some of your functions return #N/A, which cannot be manipulated. Returns TRUE or FALSE, which can then be searched or processed with IF functions. Can be followed by the above “ISNUMBER(SEARCH” very effectively.

=ISERROR(F11)

Counting duplicates:

Counts all instances of the text from S6 in the entirety of column A.

=COUNTIF(A:A,S6)

Counting sequential duplicates, creating unique IDs:

Will return the text from A15 with an underscore, followed by the number in the series of duplicates so far. If it’s not a duplicate, it will just have underscore and “1”. Useful for creating unique IDs when identifiers contain duplicates.

=A15&”_”&COUNTIF(A$2:A15,A15)

Separating combined fields that are associated with a duplicate:

Must be used in conjuction with the above function (unique IDs for duplicates). Searches text from column B, which is related to duplicates from column A. The C$1 value (appears twice) is changed based on the number of the duplicate (ex. A is first, B is the second, C is the third).

=IF(COLUMNS($A$1:C$1)>$R7,””,INDEX($B:$B,MATCH($S7&”_”&COLUMNS($A$1:C$1),$E:$E,0)))

Evaluating two dates for a date range:

For example, can be used for date of birth and date of death. In this example, L3 is birth and M3 is death. The first function will return “present” if they do not have a date of death, or return the date if they do:

=IF(AND(L3=””,M3=””),””,IF(M3=””,”present”,TEXT(M3,”yyyy”)))

The next will combine the date of birth with the above result, which will show DOB-present for the living, and DOB-DOD for the deceased:

=IF(L4=””,””,CONCATENATE(W4,”-“,X4))

 

Recommendation: Color Code Your Data

 

As a side note, I’d like to pass some general advice: color code your cells as you create them (not afterwards). In my opinion, it really helps to use a uniform color system if you do a lot of spreadsheets, that way you quickly and easily know where your formulas are or how your spreadsheet works. I go with “no fill” for normal data (an export or the base dataset), the lightest orange for automatically calculated data (functions/formulas), and the lightest blue for manually entered data (post processing).

It’s not a big deal when your spreadsheet is small. But, these things have a way of expanding massively once you start pursuing ideas. Trying to recognize where your functions are, and where you can paste data, becomes very challenging if you’re looking through thousands of columns/rows of black and white.

 

Pivot Tables – Data Crunching the Easy Way

 

The secret to looking like a data analysis pro is to let Excel do the heavy lifting by using Pivot Tables. Naturally the first step is to begin entering data. Before you get started though, I recommend deciding on some formatting standards you will use. As I mentioned above, it’s good to use color to make your cells quickly recognizable for what’s in them. When entering data, generally you can use different columns for different categories. Once you get the hang of it, entering your data becomes like second nature. I’m going to build some nonsense for this example:

In the above example, as I mentioned, I built different categories for each column. A few notes: First, make sure you format cells appropriately. In this example, everything is fine as “General” except for DOB, which I’ve formatted as a Date (Right Click -> Format Cells -> Choose a Category). Next, you can see the last column is calculated from the DOB using a formula, and I’ve color-coded it (see previous linked article for formulas). Lastly, note the header column has little arrows; you can click those arrows to apply filters or sort columns. To add these, highlight your columns, go to the “Data” tab, and click “Filter.” Just make sure you have filters on all columns with data, or you’ll get things mixed up!

I recommend focusing on getting your base data set squared away before getting into much else. When you’re ready, the next (and biggest) step is to jump into creating your first Pivot Table! Pivot Tables are insanely powerful data manipulation and exploitation tools, built into MS Excel for your enjoyment. The primary requirements are that 1) you have properly structured data, and 2) that every column has a header. We meet both of those requirements, so let’s start.

The easiest way to start is to highlight all columns with data. In our case, we’re going to highlight columns A through F. Then, go to the “Insert” tab and click “Pivot Tables.” A menu will pop up asking you about a range and worksheet; the default should be fine.

Now, don’t be intimidated by this crazy screen. If you’re like me, you’ll eventually love it and feel relaxed by seeing this. Like Pavlov’s Dog, but much sadder. Now that you’re here, I recommend you jump right in and start messing around with your Pivot Table. The key areas are the “Rows” and “Values” at the bottom; dragging various fields here will create some interesting results. For example, if we want to see the distribution of favorite foods from our data, just drag Favorite Foods down to Rows and Values. You can also have multiple “values”, representing raw totals or percentage (Right Click -> Value Field Settings, Show Value As). Or, you can have multiple Rows to make things more interesting. Like in this example:

Here, I was able to very quickly figure out where people live and their favorite foods just by dragging fields around. Along with learning how to effectively apply formulas, building Pivot Tables is in my opinion the most important Excel skill to develop. We don’t have much data in this example, but imagine being able to quickly assess data sets with thousands of records/rows. Graphs are also easily built from these Pivots, because the tables are already structured well for them. Just click in your Pivot Table, go to the “Insert” tab, then click Pivot Chart. And there you go – Graphs the Easy Way.