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

### 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.