# 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 `0`s, 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-range`s jointly meet the corresponding `test`s. 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 `E`s 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.