Excel Reference
You may find Microsoft’s Excel Reference helpful. Super User is also an excellent resource.
Entering Data and Functions¶
Data entry is easy in Excel. Most of the time, you’ll just need to type text or numbers directly
and Excel will figure out the rest. Be careful when you want to enter digits as text (when you need
to keep leading 0s, as in identification numbers) — then you’ll want to use an apostrophe first
to signal text.
| To enter … | Type this | Example |
|---|---|---|
| Formulas | = |
=SUM( A1:A6) |
| Text | ' (apostrophe) |
'00501 |
| Number | = or just the number |
12342 |
Note
Cell formatting (<ctrl>+1) can interfere with this is the cell format is set to Text. If your
formulas aren’t working, check the cell format — it should be set either to Numeric or
General.
Data Types¶
| Excel | Example |
|---|---|
| text | “Dahl, Robert”; 00501 (ZIP) |
| numeric | 2.718, 9.24E+07 |
| logical | #TRUE, #FALSE |
| date | 43068 |
| currency | 123.45 (fixed) |
| missing | @NA |
Wildcards¶
Wildcards stand for any character and are very useful for matching — especially if you suspect multiple spellings or mispellings. Excel’s wildcards are rudimentary but work for many general cases.
- Use
*to stand for zero or more characters. - Use
?to match exactly one character.
| Pattern | Matches |
|---|---|
S*ober |
Sober, Shober, Schmaltz-October |
S?ober |
Shober, Stober |
Logic Functions¶
IF() and its ilk allow you to choose a different value or calculation based on a condition.
IF() can handle multiple conditions when AND(), OR(), or XOR() are used, and IF() can be
nested (that is, one IF() inside another). Just be sure to count your parentheses.
IF( condition, then, else )¶
Returns the first value (then) if the condition is true and the second (else) if the value is
false.
=IF( A6 > 0.2, 'Above 0.2', 'Below or Equal to 0.2' )
=IF( AND( A6 = $A$1, NOT( ISNA( $B$39 ) ) ), "Yes", "No" )
=IF( ISERROR( FIND( "-", A6) ), IF( A6="F", 2, IF( A6="PF", 1, IF( A6="NF", 0, "ERROR" ) ) ), NA() )
AND( condition1, condition2 )¶
Returns true if both conditions are true, otherwise it returns false. Most often used with
IF().
| AND | True | False |
|---|---|---|
| True | True | False |
| False | False | False |
=IF( AND( A6 > 1990, B6 = "DDR" ), "No such country", "OK" )
OR( condition1, condition2 )¶
Returns true if either condition is true — or both of them, otherwise it returns false. Most often used with
IF().
| OR | True | False |
|---|---|---|
| True | True | True |
| False | True | False |
=IF( OR( A6 > 0.5, B6 = "uncontested" ), "Win", "Lose" )
XOR( condition1, condition2 )¶
Returns true if either condition is true — but not both of them, otherwise it returns false. Most often used with
IF().
| XOR | True | False |
|---|---|---|
| True | False | True |
| False | True | False |
=IF( XOR( A6="Frosh", B6<54 ), "One is False", "Either both are true or both are false" )
Conditional Aggregation Functions¶
Use these functions when you want to perform a function on only some data in a row. These functions take multiple values and summarize their contents into a single value. Typically, you’ll put these functions in a single cell (or in a separate summary sheet in a workbook) as they provide a single number summarizing many rows of data. Oddly, test conditions are strings of characters — except for cell references. See the examples below.
Note
Be careful with the test conditions. For these functions, cell references should not
be inside the quotation marks — Excel will think you are looking for a literal string to match
(“You want to find the letter A and the number 1 in the cell…”). If you refer to a cell in the
test condition, you’ll want to concatenate (&) the cell reference with the rest of the string.
Then, Excel will look into the cell first and concatenate its contents into your test string.
COUNTIF( test-range, test )¶
Count the number of cells for which the test condition is true. The test must be a character
string, but be careful! Cell references (e.g. A6 or C19) should not be inside quotation marks
or Excel will treat it as a string literal (i.e. just text) rather than a cell reference.
Numbers are treated as numbers within a string. This counts everything in A6:A384 greater than
0.5, even though the 0.5 is inside the string:
=COUNTIF( A6:A384, "> 0.5" )
Count all occurrences where a candidate received less than some percentage of the vote, where that percentage is in cell A1:
' This works:
=COUNTIF( B2:B2376, "<" & A1 )
' But this won’t work:
=COUNTIF( B2:B2376, "< A1" )
You can use wildcards if you want to search for variations in a text string. Count all occurrences of “Lawrence U,” “Lawrence University,” and “Lawrence Univ.” (among others):
=COUNTIF( A6:A176, "=Lawrence U*" )
COUNTIFS( test1-range, test1, test2-range, test2 )¶
Count the number of rows (or columns) for which multiple test conditions are true. The test must be a character
string, but be careful! Cell references (e.g. A6 or C19) should not be inside quotation marks
or Excel will treat it as literal text rather than a cell reference. If you refer to a cell in the
test condition, you’ll want to concatenate (&) the cell reference with the rest of the string.
This function assumes that the tests should be done jointly. That means that the function will count a value if the first cell in the first range meets the test and the first cell in the second range also meets the test.
Consider this function:
=COUNTIFS( Votes!A1:A6, "< 0.40", Candidate!B11:B16, "=Incumbent" )
Here, COUNTIFS() will count “1” if the value in Value!A1 is less than 0.40 and the value
in Candidate!B11 is Incumbent — both cells are the first in their ranges.
SUMIF( test-range, test, sum-range )¶
Adds the values in sum-range if they corresponding value in test-range meets the test (the first
cell in test-range corresponds to the first cell in the sum-range). You
may omit the sum-range, in which case the function adds the values in test-range.
See COUNTIF() for detail about tests.
If candidate names and their incumbency is in a worksheet named “candidates” and campaign spending is in a worksheet named “spending,” the following sums the spending of all incumbents. The answer is placed in the cell which has the formula:
=SUMIF( candidates!A1:A234, "=Incumbent", spending!A1:A234 )
SUMIFS( sum-range, test1-range, test1, test2-range, test2 )¶
Adds the values in sum-range when the corresponding cells meet all of the test conditions. That
is, the first cell in sum-range is only added if the first cell in test1-range meets test1 and
the first cell in test2-range meets test2. See COUNTIFS() for more details about
joint tests.
Warning
Note that the parameter list is in a different order than COUNTIFS(), and, unlike
SUMIF(), you must have a sum-range, and it is first in the parameter list.
Adds the values in A3:A982 where the corresponding row in B is greater than the value in A1, and the text “WI”
is in column C in the same row:
=SUMIFS( A3:A982, B3:B982, ">" & A1, C3:C982, "= WI" )
AVERAGEIF( test-range, test, avg-range )¶
Averages only those cells where tests in the test-range meet the test. See [SUMIF()]{#sumif}
for details.
AVERAGEIFS( avg-range, test1-range, test1, test2-range, test2 )¶
Averages only those cells where tests in the test-ranges jointly meet the corresponding
tests. See [SUMIFS()]{#sumifs} for details.
String Manipulation¶
These functions are used to look for patterns in strings of characters or to cut out parts of
strings. Character positions start from 1. In the string EXCEL, the Es are in positions 1 and 4,
and the L is in position 5.
SEARCH( search-text, cell, start-pos )¶
Gives you the first character position where search-text matches. Not case sensitive (treats
upper- and lower-case characters the same), and, unlike FIND(), it can use
wildcards. Note that start-pos is optional, but if you need it, the first character
position is 1. Returns #VALUE! if it can’t find the search-text.
' If A1 is “Jonathan Smyth”, this returns 10
=SEARCH( "Sm?th", A1 )
' If A1 is “Jonathan Smyth”, this returns 7 (start searching a position 5)
=SEARCH( "a", A1, 5 )
FIND( search-text, cell, start-pos )¶
Gives you the first character position wher search-text matches. A case-sensitive finding
function (that is, an upper-case “A” does not match a lower-case “a”). You cannot use
wildcards, either, but this function is much faster than SEARCH(). Note
that start-pos is optional, but if you need it, the first character position is 1. Returns
#VALUE! if it can’t find the search-text.
' If A1 is “2013-Jul-15”, these returns 5 and 9, respectively
=FIND( "-", A1 )
=FIND( "-", A1, 6 )
' If A1 is “Sancho Panza”, this returns #VALUE!
=FIND( "Quixote", A1 )
LEFT( cell, count )¶
Returns only the count left-most characters in cell. If you give a number larger than the
actual length, LEFT() returns the full string.
' If A6 is “54912-4721”, this returns “54912”
=LEFT( A6, 5 )
RIGHT( cell, count )¶
Returns only the count right-most characters in cell. If you give a number larger than the
actual length, RIGHT() returns the full string. See LEN() for an example.
MID( cell, start, count )¶
Returns a middle portion of a string, starting at start and continuing for count characters.
Often used with LEN() and FIND().
' Find the area code from a phone number: “(414) 233-5161” in A1:
=MID( A1, 2, 3 )
' Return “lawrence” from “honorcouncil@lawrence.edu” in A1:
=MID( A1, FIND( "@", A1 ) + 1, FIND( ".", A1, FIND( "@", A1 ) ) )
LEN( cell )¶
Returns how many characters a string is. Often useful with RIGHT().
' Keep all but the first six characters
=RIGHT( A1, LEN( A1 ) - 6 )
TRIM( cell )¶
Eliminates spaces at either end of a string. It leaves spaces in the middle.
' If A1 is “ García Marquez ”...
=TRIM( A1 )
' ... this returns “García Marquez”
CONCATENATE( string1, string2, … )¶
Same as & (ampersand). Simply puts the strings together.
' A1 has “Abra”, B2 has “ham”, and C6 has “Lincoln”
= CONCATENATE( A1, B2, " ", C6 )
' Result “Abraham Lincoln”
= A1 & B2 & " " & C6
' Result “Abraham Lincoln”
Data Conversion¶
TEXT( value, format )¶
Formats a number as text following a certain format. See <ctrl>+1 (cell format) for examples
of format.
' Gives 20-Jan-2018
=TEXT( 43120, "dd-mmm-yyyy" )
' Gives $56.93
=TEXT( 56.92723, "$0.00" )
' If ZIP codes were entered as numbers and lost leading zeros, try this:
=TEXT( 501, "00000" )
VALUE( text )¶
Returns the value of a decimal number in a text string. It will return #VALUE! if there are any
non-numeric characters in the string.
' Gives #VALUE!
=VALUE( "4jul1776" )
' Gives the number
=VALUE( "1776.51" )
ROUND( value )¶
This rounds a floating point value (number with a fraction). By default, it will round to a whole number, but you can specify other rounding precision.
' Gives 5736
=ROUND( 5736.34 )
' Gives 5736.3
=ROUND( 5736.34, 1 )
' Gives 5740
=ROUND( 5736.24, -1)
Recoding and Joining¶
Any time you need to gather data from more than one source, you will need to “join” the data
together on some common variable. INDEX() and MATCH() are two efficient functions that,
together, allow you to match a common fields in separate parts of your workbook and transfer
matching data. (They can be in the same worksheet, but that is not a good idea for any reasonable
dataset.) They can also serve to “recode” data from one set of codes (or tags) to another. Say
you’d like to transform “Always,” “Sometimes,” “Never” to 2, 1, and 0 — these two functions are
the way to do it.
MATCH( lookup-value, lookup-range, match-type )¶
Returns the relative position of lookup-value in the lookup-range. That is, if lookup-value
is the first value in the lookup-range, MATCH() will give you a 1.
The lookup-range must be either one column wide or one row long (you can’t have a two column by
ten row lookup-range). The match-type specifies whether you want the largest value less than or
equal to lookup-value (1), the first equal value (0), or the smallest value greater than or
equal to lookup-value (-1). You can think of a match-type of 1 as “find the number just below
(or equal)” and -1 as “find the number just above (or equal).”
Note
Typically, you will want to use 0 for match-type to find the first exact match only.
INDEX( value-range, which-row, which-column )¶
This function simply returns the row (or column) given by which-row. If which-row is 3, then
INDEX() will return the third value in the value-range. (Use 1 to indicate the first item in
the range.) If your value-range is only one column, you may omit the which-column parameter.
Unlike match, the value-range does not have to have n × 1 or 1 × n dimensions. That means that
you can match on one column but return a value from a different column. Occasionally this is
useful.
INDEX(MATCH())¶
Together, INDEX( MATCH() ) allow you to find one code and return another.
You can think of the parameters this way:
=INDEX( what-to-return, MATCH( my-value, what-my-value-should-match, 0 ) )
If my_data!A1 is a postal code (like WI or FL), fips!A1:A50 are postal codes, and fips!B1:B50
are FIPS codes used by many federal government datasets, the following will return the FIPS code for
the state listed in my_data!A1.
=INDEX( fips!B1:B50, MATCH( my_data!A1, fips!A1:A50, 0 ) )
Dates and Times¶
In Excel, dates are stored as integers and times as a decimal fraction. This was, a timestamp (a day and a time together) can be made by simply adding a time to a date. While this makes for very easy date calculations — you just add and subtract — it also means that any number could be interpreted as a date, which may or may not be what you want. Also, Excel dates are restricted to 1900 and later, so they are not useful for most historical data.
To make a date or time string from an Excel-format (integer or fraction), use TEXT().
You would want to do this if you are exporting your data for analysis in another program.
DATE( y, m, d )¶
Creates an integer for Excel’s date format from a year, month, and day. (Note that Mac Excel
spreadsheets from the 1990s used a different algorithm called the “1904 date system.” You may never
encounter any data like this, but be warned.) Use TEXT() to format the number as
a string.
' Result is 43120 --- if formatted as a Date, it will show 1/20/2018
=DATE( 2018, 1, 20 )
' Result is 1, or 1/1/1900
=DATE( 1900, 1, 1 )
YEAR(), MONTH(), DAY()¶
These three functions take an Excel date (integer) and return the specified time unit.
WEEKDAY( date )¶
Returns an integer representing the day of the week for the given (integer) date. Sunday is 1.
=WEEKDAY( DATE( 2018, 1, 20 ) )
' Result is 7 (Saturday)
TIME( h, m, s )¶
Creates an floating-point number for Excel’s time format from a 24-hour hour, minute, and second.
The result is always less than 1. This function returns the fraction of the day that has elapsed.
If you’d like to a make a timestamp (date and time together), simply add the date and time together.
To split a timestamp, use the TRUNC() function. Use TEXT() to format the number as
a string.
' Result is 0.604340 --- if formatted as a time, it will show 14:30:15, or 2:30pm.
=TIME( 14, 30, 15 )
HOUR(), MINUTE(), SECOND()¶
These three functions take an Excel time (floating point) and return the specified time unit.