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 )

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.