Microsoft Excel is a program that can be incredibly powerful if you know how to use it, and one of the most frustrating things in the world if you don't. The latest version of Excel for PC's is 2007, and the version before that is 2003. While I have Excel 2007 on one of my home computers, I use 2003 on a daily basis at work and on all other computers that I have at home. The purpose of this review is to not only introduce you to what Microsoft Excel is, but to also let you know how to use common features and functions.

I realize that this review is pretty long. If you're here to learn how to graph or how to use specific functions, please scroll below. I've made each section header bold in an effort to make it a little easier to find (if you're just scrolling through). The breakdown is as follows:
Overview
The Basics - Getting Started
A Little More Advanced - Graphing
Back To Basics - Simple Calculations
A Little More Advanced - Simple Formulas
More Advanced - The Formulas You Want To Know About
Make It Fast! – Keyboard Shortcuts
Overall

Overview

Who Uses Microsoft Excel?
Excel is one of the programs included within Microsoft Office, so if you got your computer from one of the big retailers with office installed, you should have it. That being said, you may never have used it. When I was a student, both high-school and college, I used it all the time. Now that I've moved onto the real world, I use it for 90% of what I do.

What Is Microsoft Excel Used For?
You may know Excel as That Spreadsheet Program, but do you really know what it can do? It can be used for graphing, calculations, data mining, simple databases, keeping tracking of your personal finances, and more! As you can see, there are a million uses for Excel - you just have to figure out which you need it for.

So, What Is A Spreadsheet, Exactly?
A spreadsheet is a sheet of cells that has 266 columns (labeled A through IV) and 65,536 rows (labeled by their row number). You can have an infinite number of spreadsheets (from here on out, I'll refer to them simply as sheets) within each workbook (which is simply an Excel file). If you do not like the idea of having your columns labeled with letters instead of numbers, you can change your settings to have numbered columns instead. You can do this in Tools/Options/General and click the box next to R1C1 reference style. I would not recommend this, though, since it can get confusing, but to each his own.

The Default Settings
If you actually went into Options box above to check out how to change the cell reference style, you may have noticed that there's a lot in there. Let me first go through what the default settings are for Excel (as I remember them), and then you can decide what to do with them. When you open Excel for the first time, you will see three worksheets with the default font being 10 pt Arial. Your files will be automatically directed to the My Documents folder, and under File you will see your four most recent Excel files that you have opened (not if this is your first time opening Excel, though). If this sounds good enough for you, great! If you want to change it up and make your Excel workbooks a little more unique, read the next section!

Changing The Default Settings
So you've decided that you want to add your own touch to your workbooks. First, start out by going into General Options by selecting Tools/Options/General. This is where you can change a lot of your default settings - let's start from the top:

Recently used file list - This is where you can change the number of most recent excel files that you see under the File dropdown. The default is 4, the max is 9, the minimum is 0.

Sheets in new workbook - This is where you can change the number of sheets that are in each new workbook you create. The default is 3, the minimum is 1, there is no maximum (that I'm aware of).

Standard font - This is where you can change your default font and size. Change this to be you! If you want to use 20 pt Comic Sans MS, go right ahead! You like typewriters - go for 12 pt Courier New! Change this to be whatever you want. The default is 10 pt Arial. If you decide to change it up, just make sure it is something that you will be able to read!

Default File Location - This is the folder that will pop up when you hit Save or Save As. For the home user, the My Documents default option is probably okay. If you work on a network, however, and save all of your files on there, I would recommend changing your location to your specified network path (it can be labeled as whatever letter your assign your network path).

User Name - This is the name that is going to be displayed on all comments (don't worry, I'll explain later) that you put in a workbook, as well as when you look at a file and it says who it was most recently edited by.

The Basics - Getting Started

So you have your default settings just the way you like them now - the next step is figuring out how to do some of the basic stuff.

Entering Data
To begin, data doesn't have to be just numbers; it can be text, dates, or anything, really. The easiest way to manually enter data into a cell is to just select the cell (either by clicking on it or moving the selected cell around with the arrow keys on your keyboard) and just starting typing!

Setting A Border
You can set a border around a single cell, or around a selection (or group) of cells. To set a border, highlight what you want to have the border around, and either click the Borders dropdown arrow up top (which is the one that looks a little bit like a window), or right-click within the selection that you want, select Format Cells, and then select the Border tab. Additionally, you can select Format/Cells/Border from up top. Here you can choose if you want a border just on one side (or top or bottom) or if you want to outline the whole thing. You can also select the width of the border, as well as the color. This is generally very useful for printing, or for showing where inputs are.

Setting A Cell's Color
Changing the color of a cell can be very useful - you can use it to differentiate between inputs and outputs, as well as just have different colors for different things. Or you can just have colors for no reason. There are a few ways to change the color of a cell. The easiest way is to select the dropdown arrow next to the paint bucket up top. This will bring up a color pallet, which you can then select a color from. Whichever cells you have selected when you click on the color will be that color. An alternate way to change the color is to select Format/Format Cells/Patterns.

Changing A Tab's Color
Equally as fancy is setting the color of a tab. You may want to do this if you have different tabs for different things (one tracks your personal spending habits, while another tracks your bills), or if you want to have one as an input tab and another as an output tab (I'll go through linking tabs together later). To do this, you simply right-click on the tab name at the bottom and select Tab Color... - it's really that simple! Alternatively, you could go Format/Sheet/Tab Color and select a color from there.

Formatting Text
The keyboard shortcuts for formatting specific cells is exactly the same as Microsoft Word - CTRL+B for bold, CTRL+I for italics, and CTRL+U for underline. You can also click on the icons at the top of your screen to set these formats as well. To change the color of text, simply click the dropdown next to the A up top and select which color you would like the text to be (this is right next to the paint bucket, which we used before to change the color of specific cells).

Aligning Text
Text alignment can be very important, especially if you are going to be merging cells (see next section). To do simple horizontal alignment, use the buttons up top - either Align Left, Align Center, or Align Right. The pictures use lines that look like the length of text to display this. However, if you would like more advanced alignment, right-click on the cell (or selection of cells), choose Formal Cells..., and select Alignment (you can also go Format/Cells.../Alignment). From here, you have horizontal and vertical alignment choices, as well as indent and text orientation. Text control also has Wrap Text, Shrink To Fit, and Merge Cells.

Horizontal Alignment Choices:
General - This should be the default, normally just starting at the left and working to the right.
Left (Indent) - Aligns text left to right, and if there's an indent it will start it from that point in the cell (you can select the size of your alignment by changing the number underneath Indent).
Center - Aligns text in the center of the cell.
Right (Indent) - The same as Left (Indent), except it aligns the text right to left.
Fill - Pretty useless in my mind, it will take whatever is in the cell and repeat it until there is no more room in the cell. This could get confusing and I don't really ever see how this could be useful... but that's just me...
Justify - Makes text go the full length of the cell by adjusting the position of words (basically by adding more space where spaces are).
Center Across Selection - This is an alternative to merging cells horizontally. If you have multiple cells selected and what the text to go over all of the cells, select this option and the text will be centered across your selection.
Distribute (Indent) - Aligns text the same as Justify, but if you have an indent it will indent from both sides.

Vertical Alignment Choices:
Top - Aligns text to the top of a cell.
Center - Aligns text to the center of a cell.
Bottom - Aligns text to the bottom of a cell (this is the default).
Justify - Starts by aligning text to the top, and if it goes past the length of the cell will put the next line on the bottom.
Distributed - Basically the same as Justify, except if you have only one line (and the cell is vertically bigger then it), it is aligned in the center instead of on top.

Text Orientation
Text orientation is something that is very useful, especially if you are merging cells vertically. If you have a merged cell on the side of data (think of it as an axis in a graph), you can gave the description be aligned vertical. If you play around with it, you can get a feel for how useful it will be for you.

Merging Cells
When you merge cells, you are making the selected cells become one. To do this, select multiple adjacent cells and click the Merge and Center button up top (in the alignment section), or right click within your selection, click Format Cells..., select Alignment, and then select Merge Cells.

Merging Cells vs. Center Across Selection
This is purely my opinion only, but merging cells is great for vertical merging, but for horizontal, center across selection is the right way to go. This is because if you scroll through a sheet and come upon a merged cell, it can screw up a selection if you are doing it with the keyboard. Not a huge deal, but it's a great trick to know!

Formatting Cell Type
After spending all this time in the Format Cell popup, you may be wondering what some of the other tabs actually do. To change the type of cell (e.g. number, percent, text, date, etc.), click on Number, and under Category select what you would like.
Keyboard Shortcut! - To quickly bring up this screen, hit CTRL + 1.
Advanced Keyboard Shortcuts!:
Set Cell As Number - CTRL + Shift + 1
Set Cell As Time - CTRL + Shift + 2
Set Cell As Date - CTRL + Shift + 3
Set Cell As Currency - CTRL + Shift + 4
Set Cell As Percent - CTRL + Shift + 5
Set Cell As Scientific - CTRL + Shift + 6
Set Border Around Cell - CTRL + Shift + 7

Conditional Formatting
Conditional formatting is a function that is very useful for tracking changes, as well as highlighting certain information. To set a conditional format, select Format/Conditional Formatting.... You can have up to three conditional formats per cell - and they can either be for the cell value, or for formulas (so if you want to highlight a row of data when something in the particular row is a certain value).

A Little More Advanced - Graphing

So you have a bunch of data and you want to see it graphed, or you just feel like making a pie chart because you're hungry. This section will walk you through the different types of graphs that are available in excel.

Basic Graph Information
The easiest way to put together a graph is to have the data in various columns or rows (typically columns, though), with the name of each in the first cell. If you would like a specific x-axis, I would put that as another column, but with no header.

Column and Bar Graphs
These are normally used as a time-series, meaning they show what has happened over time (for example, if you want to see production volume over time, this is a good way visually display it). Column Graphs are vertical, while Bar Graphs are horizontal.

Column and Bar Graph Example
In cell A2 enter Year 1, in cell A3 enter Year 2, in cell A4 enter Year 3, etc., all the way through cell A7.
In cell B1 enter Actual. In cell C1 enter Projected.
In cells B2 through B7 enter: 1, 2, 5, 7, 6, 12.
In cells C2 through C7 enter: 1, 2, 4, 6, 8, 10.
Highlight cells A1 through C7 (select cell A1, hold shift and select cell C7).
Click the graph button (which should be up top, near to your zoom - which is most likely set to 100%). Alternatively, you can go Inset/Chart....
Select Column as your chart type.
The first chart type will have the two data sources next to each-other, the second will have them on top of each other (same column, separated by colors), with the total on the axis, and the third will have them on top of each-other as a percent. I suggest using the first one. If you would like to see how the different graphs will look (given you data is set up correctly), select which one you would like and hit the Press and Hold to View Sample button. Click Next.
Your chart should look exactly like the sample. Click Next.
Here you have a lot that you can change. Titles Menu allows you to enter a title for the chart, as well as categories for the X and Y axes. Axes Menu allows you to choose to display your axes or not. Gridlines Menu will let you chose if you want different gridlines in the chart. Legend Menu allows you to choose if you want to display a legend, as well as where you want it positioned (the legend is the box currently on the right side that shows what each color means). Data Labels menu allows you to add labels to various points if you would like, and Data Table Menu allows you to put a data table underneath the graph. Click Next.
Lastly, choose where you would like the graph displayed - either in a new worksheet or somewhere on the current sheet. Click Finish.

Line Graph
Line graphs are also very useful as a time-series tool, especially if you have a lot of data where your columns would make it very cluttered. If you would like to see how a line graph looks, please use the previous example and select Line instead of Column or Bar.

Pie Chart
Pie charts are very useful for seeing the breakdown of data within one data set (so we want to see how much certain things make up the whole). If you try and create a pie chart out of the existing data from the examples above, however, you can see how useless it is as a time-series tool.

Pie Chart Example
In a new worksheet (different from the one in the Column and Bar example - which you can do by selecting Insert/Worksheet), enter Volume into cell B1.
In cell A2 type Bob, in cell A3 type John, in cell A4 type Amy, and in cell A5 type Amanda.
In cell B2 type 2, in cell B3 type 3, in cell B4 type 1, and in cell B5 type 5.
Create a new graph (as described above), and select Pie from the dropdown.
If you press the View Sample button, you will see the breakout of these four individual's performances as a total. All of the options throughout are the same as the Column and Bar graph example.

XY Scatter Plot
If you wind up using Excel for a statistics class, this is going to be the plot that you use a lot to learn about regression, slope, correlation, etc. If you have lots of data points, you use the scatter plot to plot them, and you can see how they all fall visually. It's very similar to all of the previous graphs.

Back To Basics - Simple Calculations

The first step to understanding how the more advanced calculations and formulas work is to understand the basics. Each cell can contain any sort of data, but for calculations it obviously has to be numbers. Any number of cells can be added together, subtracted from each-other, multiplied together, or divided by each-other. There are also a whole slew of other things that can be done as well. So without further ado, let's jump into simple calculations:

2 + 3 = 5
We all know this. Now let's let Excel show it to us:
In cell A1 enter 2
In cell A2 enter 3
In cell A3 enter =A1+A2 (you can do this by either actually typing "=" and then selecting A1 and A2, or by actually typing the formula out)

You can now change around the values in cells A1 and A2, and the total will change in cell A3.

5 - 2 = 3
Let's let Excel help us once again:
In cell B1, enter 5
In cell B2, enter 2
In cell B3, enter =A1-A2

As you can see, this equation clearly holds up...

5 + (-2) = 3
An important thing to remember is that subtraction is also just the addition of a negative. This becomes important in a little bit, when formulas can become a little more complex.
In cell C1, enter 5
In cell C2, enter -2
In cell C3, enter =C1+C2

6 x 2 = 12
Multiplication is also very straight forward in Excel:
In cell D1, enter 6
In cell D2, enter 2
In cell D3, enter =D1*D2 (the * is multiplication - which can either be found by pressing Shiftƶ, or it is on the number pad on the right side of your keyboard (if you have a number pad, that is...))

12 / 6 = 2
Division is equally as straight forward:
In cell E1, enter 12
In cell E2, enter 6
In cell E3, enter =E1/E2 (the / is division, which is on the bottom right of your keyboard (beneath the ?), or on top of the number pad)

A Little More Advanced - Simple Formulas

So now you understand how to do simple addition, subtraction, multiplication, and division in Excel. What happens, though, when the calculations become a little more complicated?

2 + 2 + 5 + 4 + 2 = 15
In cell F1, enter 2
In cell F2, enter 2
In cell F3, enter 5
In cell F4, enter 4
In cell F5, enter 2
In cell F6, you now have a choice: you can enter one cell after another, which would look like =F1 + F2 + F3 + F4 + F5, or you could simple use the sum formula.
In cell F6, enter =sum(F1:F5)

As you can see, this sums up everything that falls within the cell range of F1 to F5, so cells F1, F2, F3, F4, and F5.

(2 x 5) + (4 x 7) + (6 x 9) = 92
In cell G1, enter 2
In cell G2, enter 4
In cell G3, enter 6
In cell H1, enter 5
In cell H2, enter 7
In cell H3, enter 9

We will first solve this problem the longhand way - which will hopefully teach you a few other things about Excel as well...
In cell I1, enter =G1*H1
Copy Cell I1, and paste into cells I2 and I3 - the formula should have adjusted to use the cells in rows 2 and 3, respectively. This is because in Excel, we can copy and paste formulas so that we don't have to write them out continuously (this also reduces errors).
In cell I4, enter =sum(I1:I3) - the value should come out as 92.
As you can see, copying formulas is something that can save a lot of time when you are doing the same thing over and over. However, knowing a better formula can also save you time...

In cell I5, enter =sumproduct(G1:G3,H1:H3) - the value should come out as 92.

AS you can see, understanding the various functions that Excel has to offer can be very useful and time saving.

Understanding Freezing Reference Cells
No, they're not cold! But freezing a cell can be very important. If you are going to get in the habit of moving formulas around, you may want to figure out how to freeze certain elements of the cell. For example:

Change the formula in cell I1 to: =$G$1*$H$1
Now copy and paste the formula into cells I2 and I3 - the values should stay the same as I1 - 10 - because the formulas are still referencing the first row.

Now change the formula in cell I1 to: =G$1*H$1
Copy and paste the formula into cell I2. It should stay at 10.
However, copy and paste the formula into cell J1, and it should say 50. This is because we have frozen the row in this scenario, but the columns can change (if you would like to see this for sure, paste the formula into cell J2 as well, you will see that it also equals 50).

Finally, change the formula in cell I1 to: =$G1*$H1
Copy and paste the formula into cell I2. It should now change to 28.
Paste the formula into cell J1, it will now say 10. If you paste it into cell J2, it will say 28. This is because we have frozen the columns, but not the rows are free to move.

How Can This Be Useful?
A great example of how freezing reference cells can be useful is a running total.
In cells K1 through K5 enter 2, 4, 6, 8, 10.
In cell L2 enter: =sum(K$1:K2)
Copy cell L2 and paste into L3. The value should change from 6 to 12 (because the 6 in cell K3 was just added). If your value is 10, this means that you did not properly freeze your reference call, and it is now just summing K2 and K3.
As you can see, for something like this, freezing reference cells can be a huge time saver (otherwise you would have to create a new formula for each row). This comes in handy for some of the more advanced formulas, too.

The easy way to freeze reference cells is by pressing F4 - this will lock both columns and rows. If you hit it again it will lock just the row, if you hit it for a third time it will lock just the column, and a fourth time turns all reference cell freezing off.

More Advanced - The Formulas You Want To Know About

Nesting Formulas
Before we dive deep into the more advanced formulas, the concept of nesting is an important one. A formula is nested when it is contained within another one. While this may sound pretty simple, order of operations definitely comes into play at times, and you need to be careful to properly close out all formulas. Otherwise, you'll run into a lot of trouble (and frustration!).

PEMDAS
So you thought sixth grade math would never come back, huh? Well guess what!? Here it is! The order of operations is:
Parentheses
Exponents
Multiplication
Division
Addition
Subtraction

What does this mean for formulas?? - Anything that is inside parentheses will be completed before anything else - just keep this in mind!

And now for the formulas...

ROW
=ROW([REFERENCE])
The ROW function is used to return the row number of a given cell. The cell can be itself. So give it a try, and select a random cell in your workbook and put in the formula, replacing [REFERENCE] with whatever cell you would like.

COLUMN
=COLUMN([REFERENCE])
The COLUMN function is used to return the column of a given cell, represented as a number. The reference cell can be itself. Do the same thing that you just did for the ROW function, but use COLUMN instead.

LOOKUP
=LOOKUP([LOOKUP_VALUE], [LOOKUP_VECTOR], [RETURN_VECTOR])
The LOOKUP function will search for a value in the array and return whatever is the result in the return vector – and if the value is not in the array, it will find the closest value smaller then the one you are looking up.

Example
If you're still working in the same workbook as the previous examples, insert a new worksheet (remember how?).
In cells B1 through B4 enter 1, 2, 3, 4.
In cell C1, enter Jim
In cell C2, enter John
In cell C3, enter Adam
In cell C4, enter Ralph
In cell A1, enter 1
In cell A2, enter =lookup($A$1,$B$1:$B$4,$C$1:$C$4)

Cell A2 should now return Jim. If you change this to 2, it will return John. If you change it to 1.5, it will return Jim. If you enter 4, it will return Ralph. If you enter 5, it will return Ralph.

ISERROR
=ISERROR([VALUE])
The ISERROR function checks to see if there is an error, and then will return either True or False based upon what it finds. ISERROR is very useful when nesting functions (as will be shown below). The important thing to remember is that the function is checking if there is an error, so if it returns true, this means there is an error, while false means that there is no error.

IF
=IF(LOGICAL_TEST,[VALUE_IF_TRUE],[VALUE_IF_FALSE])
The IF function is one that should make a lot of sense to people - it tests to see if a statement is true; if it is true it does one thing, if it is false it does the second.

Example 1 - Simple Numbers
In the same workbook that you've been working in:
In cell E1, enter 1
In cell F1, enter =IF($E$1=1,"YES!!","NO...")
Cell F1 should now display "YES!!" - change E1 to 2 (or something other then 1, really), and it should say "NO..."

Example 2 - Simple Numbers with Reference Cells
In cell E3, enter YES!!
In cell F3, enter NO...
In cell E4, enter 1
In cell F4, enter =IF($E$4=1,$E$3,$F$3)
Cell F4 should now say "YES!!" through the reference. If you change what's in cell E3, it will also change the result (type your name in, or something, and it will change it). However, if you change the value of cell F3 right now, it should have no affect on F4. If you change the value of E4 to anything other then 1, though, whatever is in F3 will be displayed in cell F4.

Example 3 - Text
The beauty of the IF function is that it doesn't work on just numbers, it can work on text too!
In cell H1, enter Jim
In cell H2, enter =IF($H$1="Jim","JIM!!","Not JIM...")
Cell H2 should now display "JIM!!" It's important to understand also that capitals do not matter in this situation - change cell H1 to "JIM" from "Jim" and H2 will still say "JIM!!" However, if you add a space to the end of H1 (so it now says "JIM "), it will return "Not JIM...". The blank space is something important to understand, and I will explain how to deal with it later...

Example 4 - Text with Formulas
Do you remember the lookup function that we performed before? Hopefully you still have it... if you don't, scroll up and recreate!
In cell A2, you should still have your lookup function
In cell A3, enter =IF($A$2="Jim","JIM!!","Not Jim...")
In cell A1, enter 1
This should return "JIM!!" in cell A3.
While this is pretty similar to Example 3, you should be getting a feel for how you can interlink formulas.

Example 5 - Nesting A Formula
This is going to be our first real nesting situation.
In cell A4, enter =IF(LOOKUP($A$1,$B$1:$B$4,$C$1:$C$4)="Jim","JIM!!","Not Jim...")
Now change the value in cell A1. If it returns anything other then Jim for the LOOKUP function, it will display "Not Jim...". While the results are identical to Example 4, it is an important first step to seeing how formulas can be nested.

Example 6 - Formula References
What if we want to use a name other then Jim for our lookup? Do we want to have to go back into the formula and change it everywhere? No! That leaves room for error, especially if your formulas become complicated because you can miss just one instance and screw it up...
In cell B5, enter Jim
In cell A5, enter =IF(LOOKUP($A$1,$B$1:$B$4,$C$1:$C$4)=$B$5,"JIM!!","Not Jim...")
Now cell A5 should display Jim. If we change the value in cell B5, though, it will say "Not Jim... since it is trying to find whatever value is in cell B5. It might be easier for you to understand if you change "JIM!!" to "True" and "Not Jim..." to "False" in your formula.

Example 7 - Your Turn!
Now it's your turn! Try and set up a whole thing that also incorporates Example 2 into Example 6. Think you can do it? I hope so! If you have troubles, feel free to post a comment or shoot me an email.

VLOOKUP
=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM,[RANGE_LOOKUP])
One of the best lookup tools that Excel has to offer. It's very simple, once you understand it... Getting the hang of it, though, can be a pain at first. VLOOKUP works by looking for a value in the leftmost column of the table you are looking up info from, and then returning whatever column you specify. While this sounds complicated, I think a few examples might work really well to explain it.

Example 1 - VLOOKUP Basics
New worksheet! Clean slate! Let's go!
We're going to first create a simple table that we're going to VLOOKUP off of.
In cells G1 through G10, enter Luke, Dan, Emma, Paige, Eric, Sarah, Jake, Jason, Andrew, Courtney, respectively
In cells H1 through H10, enter 15, 20, 17, 5, 7, 9, 7, 10, 12, 11, respectively
So our table to lookup off of is going to be G1 to H10. Just to be clear, though, it doesn't need to be numbers. The second column could have been anything (last names, for example, or a city).

In cell A1, enter Luke
In cell A2, enter =VLOOKUP($A$1,$G$1:$H$10,2,0)
This statement is looking for whatever is in cell A1 within the table G1 to H10, and then returning the value in the second column. The last value should be either 0 or FALSE if you want to find an exact match, or should say TRUE if you do not.
A2 should now say 15, which is what is next to Luke in the table. Try entering some of the other names and see what it returns. Now try entering a name that's not in the list and see that it returns an error, displayed as "#N/A".

Example 2 - A Bigger Table
Let's add a little more detail to our table that we're looking up off of.
In cells I1 to I10, enter A, B, C, D, E, F, G, H, I, J, respectively
In cell A3, enter =VLOOKUP($A$1,$G$1:$I$10,3,0)
Notice that we changed the table array from $G$1:$H$10 to $G$1:$I$10, and changed the lookup row to 3 from 2.
In cell A1, enter Luke
Cell A3 should now say A. If you change the names, the lookup values will change as well.

It is important to note that you do not have to look up the farthest column. In the formula in cell A3, you can change the column lookup to 2, while keeping the table array at $G$1:$I$10, and it will return the same values that are in cell A2.

Example 3 - Reference Column Numbers
It's great that you're setting up your columns manually, but what if you want to set up a lot of lookups that will look at different rows? If this does seem like a pain to you, imagine you are setting up a database where you will have 50 rows of data per piece of information that you are looking up. Do you really want to go in and adjust your formula 50 times? No! You can go in and set reference cells that will change your lookup.
In cell B4, enter 2
In cell B5, enter 3
In cell A4, enter =VLOOKUP($A$1,$G$1:$I$10,$B4,0)
Copy cell A4 and paste it into A5 - the reference cell should have changed from $B4 to $B5.

If A1 still says Luke, A4 should say 15 and A5 should say A. Play around and see how this changes too!

Example 4 - Errors
While this example isn't going to fully incorporate VLOOKUP, it will help you understand the ISERROR function better (hopefully).
In cell A6, enter =ISERROR($A$5)

Change A1 to Luke. All of your other VLOOKUPs should work, and A6 should say FALSE (do you remember why this is?). However, if you change A1 to a name that is not in the list, you will have an error returned in all of you other lookups, and A6 will say TRUE.

Example 5 - Incorporating ISERROR
It was pretty obvious before when there was an error, so why do we need to have the ISERROR function in there? For aesthetics, really. We can have it return something nicer than #N/A if the name is not in the list.
This example is also going to incorporate the IF function.

In cell B7, enter 2
In cell A7, enter =IF(ISERROR(VLOOKUP($A$1,$G$1:$I$10,$B7,0)),"Name Not In List", VLOOKUP($A$1,$G$1:$I$10,$B7,0))

While this looks very complicated, it's not. It says if there is an error in the VLOOKUP function, display "Name Not In List", otherwise complete the VLOOKUP. The VLOOKUP is identical to the one that we used before, except referencing cell B7 this time instead of B4.

If that formula was a little too complicated for you to follow, think about it this way. In cell A7, we could have simply put: =IF(ISERROR($A$4),"Name Not In List",$A$4)
We simply put the formula where we had $A$4.

Example 6 - Row and Column References
One of the great things that you can do is set up your table in the exact order that you are going to want your data returned. This is something that you could use if you were setting up a simple database in Excel.

We're going to set this one up a little bit lower on the sheet...
In cell A15, enter Eric
In cell B15, enter =IF(ISERROR(VLOOKUP($A15,$G$1:$I$10,column(B15),0)),"Name Not In List", VLOOKUP($A15,$G$1:$I$10,column(B15),0))
The value in B15 should be 7
Now copy cell B15 and paste it into cell C15. The value should be E. This is because the column moved over 1, so it is now in the third column, so the column(C15) formula returns 3.

Example 7 – Multiple Values In Lookup Table
Understanding how a function fully works is the only way that you’re going to be able to fully take advantage of it. VLOOKUP works by looking in the leftmost column and returning the first instance of the lookup value that it finds. So let’s add to the table that we’re looking up off of.

In cell G11, enter Eric
In cell H11, enter 22
In cell I11, enter Z
In cell A16, enter =IF(ISERROR(VLOOKUP($A$15,$G$1:$I$11,column(A16) #&43 1,0)),"Name Not In List", VLOOKUP($A$15,$G$1:$I$11,column(A16) #&43 1,0))
Make sure that cell A15 still says Eric from the previous example
You can see that it is returning the first instance of where it finds Eric in the lookup table - so the new data that we just entered above is not going to be seen. The best way to get around this is to make sure that you have a little more detail in your lookup name - so use first and last name, or first name and zip code, etc.

Another thing happened above as well - I used a formula with the column function. This is because we are referencing a cell in column A for the lookup, which will return a value of 1, but we want it to say 2 (or 3, etc.). It is very easy to just reference the cell next to it, but it is equally as easy to just add a #&43 to the equation as well.

HLOOKUP
=HLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,ROW_INDEX_NUM,[RANGE_LOOKUP])
HLOOKUP works just like VLOOKUP, but instead of looking in the left-most column, it looks in the top row. The other different is that it uses a ROW_INDEX_NUM, which means that the different references basically mean how far away from the top row to return. Try to run all of the examples that we just did for VLOOKUP but using HLOOKUP instead! (This means that you're going to have to change the initial table to have the first column go across the top row, and the second column go in the second row, etc.

**Quick Note - VLOOKUP vs. HLOOKUP**
You're most likely going to use the VLOOKUP function more than the HLOOKUP function, but this isn't to say that HLOOKUP isn't important! Most people are going to naturally create tables that have the data by column instead of by row, which is why you'll use VLOOKUP more - not to mention that if you're creating a simple database you have 65,536 rows to work with, while only 266 columns.

LEN
=LEN(text)
The LEN function returns the length of a string of text. While I can imagine you might find it hard to believe that this would be useful in any way, shape, or form, you need to trust me that it is. Let's go through example that might remind you of something we discussed before...

Example - Using LEN
LEN can be used as a great check for things that you know should be a certain length, and the best way to explain this is to bring back something I mentioned before. Do you remember Example 3 from the IF function explanation? I said that I would come back to the space issue later - well now is later!
In cell A1, enter "Jim"
In cell A2, enter "Jim "
In cell B1, enter =LEN($A1)
Copy cell B1 and paste into cell B2
B1 should return 3, while B2 should return 4. This is because the space is counted as a text character. We know that "Jim" should only have 3 characters, but if it says 4, that means we have something else in there!

LEN is also very useful when being used in conjunction with other functions, so don't be surprised when you see it come back!

FIND
=FIND([FIND_TEXT],[WITHIN_TEXT],[START_NUM])
The breakdown is as follows: what text do you want to find within a string of text, and do you want to start from a certain character number? As always, examples explain the best:

Example 1 - Using FIND
We can stick with the same worksheet that we used for the LEN example (if you didn't do the LEN example, go do it now! I'm going to use it here...).
In cell C1, enter =FIND(" ",$A1)
Copy C1 and paste into C2
This formula is looking for a space in the reference cell - which means that there is going to be an error returned in C1 (since there is no space in A1), and it should have a 4 in cell C2 (since the space is the fourth character in A2).

Example 2 - Incorporating ISERROR
Like we did in the VLOOKUP function, we need to use ISERROR to return an error message a little more comprehensible to the average person.
In cell D1, enter =IF(ISERROR($C1),"No Space",$C1)
Copy D1 and paste it into D2

D1 should say No Space, while D2 should say 4.

Example 3 - No Middleman!
It's important that you try and figure out how to do some of this on your own. In cell E1, try and change the formula in D1 so that it doesn't reference C1, but has a FIND formula there itself. Try it!

Example 4 - The End of FIND
This one isn't actually an example, but the answer to example 3. You should have come up with the following formula in cell E1: IF(ISERROR(FIND(" ",$A1)),"No Space",FIND(" ",$A1))
Did you??

LEFT
=LEFT([TEXT],[NUM_CHARS])
This function will look at a string of text and return a certain number of characters, starting from the left. So say you want to return the left 6 characters in a cell with text in it, you will reference a specific cell, and input that you want to take the left 6. How is this useful? Well let’s go back to that whole space example from above:

Example - How LEFT Is Useful
Hopefully you still have that one spreadsheet open. We’re going to take this a few steps at a time - we want to find the left 3 letters, but we do not want to have the number 3 hard-coded.

For you to see how it works first, though:
In cell A4, enter =LEFT(A2,3)
The result should be “Jim”

Now we need to figure out how to get the 3, or a different number if it’s a different word. Remember that we are trying to eliminate any spaces that might be at the end.

In cell A5, enter =FIND(“ “,A2)
This is the same function that we used before, and it should return 4.

In cell A5, enter =IF(ISERROR(FIND(" ",A2)),A2,LEFT(A2,FIND(" ",A2)-1))
Let’s break that one down for you:
It starts by trying to find a space in the cell. If it finds one, then there is no error, so it goes through and uses the left function and takes everything left of the space (LEFT(A2,FIND(" ",A2)-1)). However, if it does not find a space, then there is no error, so it just takes the value of cell A2.
Ta-da!

RIGHT
=RIGHT([TEXT],[NUM_CHARS])
The RIGHT function is exactly the same as the LEFT function, but starts from the right side of the cell (so it takes a certain number of right-most characters). So the same question that has been posed before - how can this be useful? Example me!

Example - How RIGHT Is Useful
Right can be useful if you have two words in a cell and you want the second word.
Let’s start a new worksheet, just for kicks.
In cell A1, enter Ryan Brennan
In cell A2, enter =LEN(A1)
In cell A3, enter =FIND(“ “,A1)
In cell A4, enter =RIGHT(A1,A2-A3)
This should return Brennan in A4

Now it’s your turn! In cell A5, can you create one thing that will return Brennan (using the same functions that we just used)? Good luck!

CONCATENATE
=CONCATENATE([TEXT 1],[TEXT 2],[TEXT 3],ETC)
This function combines different cells or text references into one. It is very straight forward when you see it in an example:

CONCATENATE Example
Let’s use the same sheet that we just used for RIGHT (although it really doesn’t matter)
In cell C1, enter Ryan
In cell D1, enter Brennan
In cell C2, enter =CONCATENATE($C$1,” “,$D$1)
Cell C2 should now say “Ryan Brennan”
In the formula in C2, we used “ “ to put the space in between two different cells we are combining, but it also works as a good example to show that you do not have to use only cells, but can also put text phrases directly into the formula.

CONCATENATE vs. &
Using the ampersand (&) is a lot easier than the CONCATENATE function. Not only is CONCATENATE a little bit harder to spell than &, it is easier to follow too (in my mind).
The last example using an ampersand would look like this:
In cell D2, enter =C1&” “&D1

You can judge for yourself, but I think it is a lot easier to follow if you use the &.

OFFSET
=OFFSET(REFERENCE, ROWS, COLS)
The OFFSET function will return what is in a cell that you designate it to look to. Once again, without further ado, let’s keep using the same worksheet that we were just using for the CONCATENATE and RIGHT functions.

As a reminder, cell A1 contained Ryan Brennan, (and that’s the only part that we’re going to need).

In cell F4, enter =OFFSET(F4,-3,-5)
This means that we are using the current cell as the reference cell, and offsetting it the cell by 3 up (-3 rows) and five columns to the left (-5). So the value in cell F4 should now be Ryan Brennan. You can change it around and have the references be linked to cells as well, or even use the ROW and COLUMN functions that we learned about before! Go nuts!

If you have a question about a formula that I haven't written about, please post a comment! I am more than happy to explain each-and-every function (I've used a lot of them and can hopefully figure out the rest). I definitely have not written about everything - other functions that I have used extensively but did not write about include (but are not limited to): INDIRECT, SUMIF, MATCH, COUNT, COUNTIF, STDEV, ADDRESS, CHAR, VALUE, etc. You get the idea - I have a lot more information that I can share if you would like me to! Chances are that if you're wondering about a specific one, someone else is too!

Make It Fast! – Keyboard Shortcuts

**Quick Note - Press And Hold vs. Press In Sequence**
It's important to understand when you need to press two buttons together (meaning hold one and press the second), versus when you need to press buttons in order. When you need to press and hold, I will use #&43 to show the keyboard keystroke. When you need to hit buttons in order, I will use /.

So you know how to do stuff the long way, by dragging your mouse around clicking your way to it, and now you want to know how to do it quickly with just keystrokes. Here goes nothing!

The Basics
Open File - CTRL + O
Close File - CTRL + W
Print - CTRL + P
New File - CTRL + N
Select All - CTRL + A
Undo - CTRL + Z
Redo - CTRL + Y
Bold - CTRL + B
Italic - CTRL + I
Underline - CTRL + U
Edit Formula in Cell - F2
Free Reference Cell - F4
Select Row - Shift + Spacebar
Select Column - CTRL + Spacebar

More Advanced
Paste Values - Alt / E / S / V
Paste Transpose - Alt / E / S / E
Paste Formula - Alt / E / S / F
Paste Format - Alt / E / S / T
Paste Transposed Values - Alt / E / S / V / E
Scroll Through Tabs - CTRL + PG-Up or PG-Down
Change Workbooks - CTRL + Tab
Bring Up Cell Format - CTRL + 1
Set Cell As Number - CTRL + Shift + 1
Set Cell As Time - CTRL + Shift + 2
Set Cell As Date - CTRL + Shift + 3
Set Cell As Currency - CTRL + Shift + 4
Set Cell As Percent - CTRL + Shift + 5
Set Cell As Scientific - CTRL + Shift + 6
Set Border Around Cell - CTRL + Shift + 7
Sum Everything Above - Alt + = + = (you need to hold ALT and press equals twice)

In general, if you use ALT, that will bring you to the top row, where you can press any of the letters next that are highlighted up top (e.g. F for the File menu, E for the Edit menu, V for the View menu, etc.). After that, you can see what to hit next. While this may seem slow at first, once you get the hang of the keystrokes that you use a lot, it will make your process a lot faster (this is how I first started with paste values, for example).

There are a lot more keyboard shortcuts out there - these are the ones that I can think of now! If you have others that you think are useful, post a comment or shoot me an email!

Overall
As I said at the beginning of this review of Microsoft Excel, it is a tool that can be very powerful if you know how to use it, but very deterring if you do not understand it. Hopefully this has helped you out! As always, please post feedback and comments, or feel free to email me! If you would like me to explain functions that are not listed, please let me know and I will do my best to go through them!

Recommended: Yes