Amdall Gallery

As a huge Excel fanatic, over the years I’ve amassed a nice collection of spreadsheets; if you’ve explored this website much, I’m sure you’ve seen graphs from some of them. As part of that data crunching, I’ve also utilized a wide variety of functions and formulas to process data. I keep a master reference list of these Excel functions in a .txt file, and I think it’d be nice to share the wealth. So, I’m going to publish some of the functions I’ve used most frequently; maybe these will help someone! If anyone happens to read this and has any questions, definitely feel free to reach out in the comments.

As a side note, I’d like to pass some general advice – and that is, color code your cells. 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), and the lightest blue for manually entered data (post processing).

And now, here’s the list. Basically, how this is organized is 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.

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

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)

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

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

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)

I can’t help but feel like I’m still missing some good function solutions on this page that I’ve used in past. Are there any additional functions or data-crunching scenarios you’re interested in? If so, let me know in the comments! I’m always interested in figuring out new things, so a “what function could make this happen?” challenge might be pretty fun.

Also, since I’ve been adding “items used/discussed” links to posts recently, I thought I’d add something here too. I don’t actually own any of the items below, but maybe I should start advertising my love of spreadsheets more? These items aren’t directly associated with me or anything, I just thought they were funny.