Excel Functions

Click on a link to see the associated Excel functions

• Intro
• Numeric
• String/Text
• Boolean
• Date and Time
• Table
• Level 1
• Level 1 and 2
• All

Intro

This is list of useful functions in Microsoft Excel. If you're not familiar with functions, you can think of them as a way to plug in a number, etc., and Excel will do the math for you. For example, if you have several numbers and you want to find the total (the sum) Excel has a function to sum them for you. There's also a function that can find the highest (maximum) number, or the lowest (minimum) number. And it's not just numbers. There are functions for text (or what we generally call "strings" in the data world), where you can get the total number of letters or characters, convert the text to all capitals or all lowercase, etc. There are even functions to test if something is true, like whether a cell is blank. That might sound strange considering that you can just look at a cell to see if it's blank, but sometimes it's not quite that simple.

We say Excel returns a value as the result of all the calculations it does. Generally, Excel will show you the value returned on the spreadsheet itself, instead of showing you the function in that cell. I'll use the term "return a value" below, but it just means the "result" of the function.

Excel Notation

• Use double questions for text (ex: =A1="foobar", not =A1=foobar )
• Use a colon for a continuous range (ex: =SUM(A1, A2, A3, A4, A5) is the same as =SUM(A1:A5)
• Omit the row number to refer to an entire column (ex: =SUM(A:A) sums all values in column), and omit the column number to refer to an entire row (ex: =SUM(1:1) sums all values in row 1)
• Excel has several operators: symbols with special meaning, just like the plus sign or parenthesis in a math equation. Logical operators are used to test whether something is true (see below for info on Boolean Functions). Math operators do math equations. Here are the basics
• + - * /: these standard math operators work just as you'd expect; of course, use parentheses to avoid ambiguity in the order of operations (ex: =(A1*(B1+14))/365 )
• = : if the values are equal (ex: =A1=B1 )
• > : if the first value is greater than the second (ex: =A1>B1 )
• < : if the first value is less than the second (ex: =A1<B1 )
• >=: if the first value is greater or equal to the second (ex: =A1>=B1 )
• <=: if the first value is less than or equal to the second (ex: =A1<=B1 )
• <>: if the first value is not equal to the second (ex: =A1<>B1 )
• & : concatenate strings (put together text in two different cells, or add text to the text in another cell (ex: text in A1 is "Hello", text in B1 is "World, use =A1&" "&B1 to get "Hello World", and note that the space must be added manually to separate the values with the " " text in the middle)
• Functions can have different numbers of parameters. Parameters are the values that the function uses; you can think of them as the variables you would use in a math equation. Some parameters are a single value (like the number 34, or the string "foo"), and others are a range (like "A1:A5" meaning all values in cells between A1 and A5). Sometimes you can plug in the "literal value" (like 34 or "foo") or use the cell that has the value (like A5). Some functions use both types: COUNTIF takes a range for one argument and a single value (as the criteria) in the other (and counts the number of cells in the range that meet that criteria). Some functions have optional parameters, which are not required but can give you more control over the data returned.

Nesting functions

As explained above, a does a mathematical (or other) process on values and ranges in its parameters and it returns a value (the result). Normally this result is displayed on the screen, but not always. You can actual use the return value of one function in the parameter of another function. This is called "nesting function", as it's putting one function inside another. This is a very common and very powerful technique.

For example, say you have a long range of numbers and you want to find, I don't know, say the point halfway between the highest and the lowest value (not the median of the range, just the average of the highest and the lowest). A funny example, but it will do here. You can do this manually by looking through all the values in the range to find the highest and lowest, maybe sort all the values to make that easy. But you can also combine the functions and have Excel do all the work: =(AVERAGE(MAX(A1:A500)), MIN(A1:A500)). Looks a little complicated? Let's look at what's going on. There are the functions MAX, which returns the highest value, and MIN, which returns the lowest value. You might wonder, "Returns to where? It doesn't get displayed on the screen." Right. It returns the value to outside function: AVERAGE. So Excel first goes through all the values in the range A1 to A500 and find the highest value, and then does the same for the lowest value; once it has those values, it plugs them into the AVERAGE function, and then returns that value to the screen. So for our example:

1. =(AVERAGE(MAX(A1:A500)), MIN(A1:A500))
2. =(AVERAGE(90), MIN(30))
3. =60

User Level

I broke these down by "user level". It's not a real measurement, of course, but I thought I'd give you a heads up since sometimes functions can give you unexpected results, and some functions are used for very technical things that most people don't need to bother with. There's no real cute and dry way to measure experience: the more you use these, the more comfortable you'll feel with them. But it can help new users know how user-friendly use each function is. For additional info, I don't recommend the built in Help features, nor the official Microsoft online help. Instead try Tech on the Net (Excel series) and maybe scan the questions at Stack Overflow (a bit more technical, but a good place to learn tricks). Hope you find it helpful.