Guide: Navigation

Learning to navigate around a spreadsheet can reduce your time spent doing tasks significantly. If you grab the scrollbar to navigate up and down long documents or if you use the mouse to drag up and down documents while selecting cells then this guide is for you!

There are a number of shortcuts that can be used individually or combined to do repetitive tasks much more quickly. We'll look at each of these individually below.

Shorcut	Usage
Control	Jump
Page Up	Jump
Page Down	Jump
End	Modifier
Home	Modifier
Shift	Selection
Space	Selection
Tab	Selection
Enter	Selection

Control
The control key helps the user move around the document by jumping from place to place. It jumps around in a very specific, predictable way. Pressed by itself, it will do absolutely nothing; instead it must be combined with an arrow key or one of the modifiers (end or home). When jumping, the active cell selection will travel in the direction of the arrow key pressed and it will land on the last cell with data or the first cell with data if passing over empty cells.

For example, if you have a row of data that spans from A1 to A15 and your currently selected cell is A1 and you press the down arrow while holding control, it will jump to A15, which is right before a blank cell. If A1 to A14 was empty and A15 had data in it, it will jump through all of the empty cells and land in A15. This works with any of the arrow keys. Control behaves slightly different when combined with the End and Home key, though...we'll cover that a bit later.

Page Up/Down
Page up and down don't do anything spectacular but they do make it easy to skip through large chunks of data instead of jumping to the end of it or scrolling through all of it. The size of the jump up or down is dependent on your screen size and zoom level. If 30 rows are visible on the screen, it will jump up or down 30 cells. Not much more can be said about this aside from it can be combined with shift to select all the cells between the jumps.

End/Home
The End and Home keys function in a very similar way with opposite results. When you press End by itself, it turns on "End Mode" which will stay active until you press a key. If you press an arrow key in end mode, it will function exactly like pressing Control and an arrow key. This aspect of the Home key doesn't function much like the End key but when you press the Home key by itself, it will jump you to the first column in the row (back to A).

The functionality of these two keys becomes significantly more useful when combined with the Control key. Pressing Control and then End (while still holding Control, which will be abbreviated as Control + End from this point on) will jump you to the last cell in the spreadsheet. This may not be exactly where you expected it to be because Excel considers the furthest cell used, even if the contents have been cleared out since, the last cell until you have saved the sheet.

In normal usage, pressing Control and either the End or Home keys will cause Excel to jump to the last cell in a sheet or back to the beginning (A1). The last cell is a combination of the furthest column and the lowest row such that contents in G4 and A10 would cause it to jump to G10.

You can see this in action by entering some data in cell D5 of a new, blank sheet and pressing Control + End and Control + Home a few times (you don't need to release the Control key between strokes of End and Home). The active cell will jump back and forth between D5 and A1. Now put some data in G10 and repeat the key strokes, it'll now jump back and forth between G10 and A1. Last step: click in G10 and press delete to remove the contents of the cell and repeat the keystrokes. It will still jump back and forth between G10 and A1! Save the workbook as anything you would like and then repeat the keystrokes, it'll resume jumping back and forth between D5 and A1. So remember this, anytime you delete the furthest contents in a spreadsheet, Control + End will still jump to it as if it is still there until you save the sheet.

Shift
The shift key helps the user make selections, especially in combination with the jump/modifier keys above. If you had been holding Shift in the exercise above, you would have not only jumped between A1 and D5/G10 but you'd also have selected all the cells in a box between them. Anytime you are holding down shift, you will make a selection from the current cell to the target cell. Holding shift while pressing the arrow keys will make selections of adjacent cells. This is pretty straight-forward, play around with it to get a feel for how it works.

Space
The Spacebar has a pretty nifty function within Excel when combined with Shift or Control. If you want to select an entire column (columns are at the top and run vertically), then simply press Control and the Spacebar. It will select the column or columns of all active selections. If you click and drag from F5 to I5 and then press Control + Space, it will select columns F through I. If, however, you click F5, G5, H5, and I5 while holding Control, you are making individual selections and only the last selections column will be selected (in this case only I is selected). The same concept applies to rows if you press Shift and the Spacebar. The same restrictions apply as well. There is a very easy way to remember which highlights which: the Shift key is longer horizontally and thus coorelates to the rows or horizontal selection while the Control key is shorter than the Shift key and thus coorelates to the columns or vertical selection.

Tab/Enter
The last navigation keys to touch on are Tab and Enter. They don't do a whole lot but they do have interesting functionality that can be taken advantage of in your everyday use of Excel. Pressing them normally will cause you to travel either horizontally from column to column (Tab) or vertically from row to row (Enter); combining them with Shift will cause the direction to be reversed. Shift + Tab goes back to the left and Shift + Enter goes to the cell above.

The interesting behavior comes into play when you make individual cell selections using mouse clicks combined with Control. I have set up a spreadsheet to illustrate the point:

I have selected each individual cell in sequence starting with 1 and ending with 11. When I press Tab or Enter, Excel will jump from cell-to-cell in the same sequence that I selected. The idea is interesting but there is very limited usage for it; however, when you select things using Shift and click-and-drag a box, it becomes significantly more useful.

In this example, I have highlighted the columns in which I will enter data (Name, Age, and Sex). By doing this, I am able to simply press Tab each time I want to move to the next cell and after entering data in the Sex column, it will wrap back around to the Name. This allows for speedy entry by limiting the number of times you have to manually return to the beginning row of your data. If I wanted to enter Lloyd, who is a 52 year old male, I would make the following keystrokes:

Lloyd
[Tab]
52
[Tab]
M
[Tab]

There is no need to press Enter after each entry, Tab will automatically skip you to the next cell over.

There have been a lot of topics covered here and I know it can be overwhelming to someone used to using the mouse and scrollbars to navigate spreadsheets; however, if you begin using these keystrokes, as unnatural as they may feel in the beginning, you will be zipping around your spreadsheets faster than you even realized was possible!

Guide: Function Usage

After the initial post, I feel it'd be a good idea to step back a bit and take a look at the basics of Excel. To the very beginner, jumping directly into syntax and functions may not be the best approach. I believe that if you found your way to this blog, you are familiar enough with Excel that explaining the general concepts, such as cells and formatting, won't be necessary; however, learning to manipulate functions may be useful.

To begin, we'll start with a function that nearly everybody is familiar with: SUM(). The SUM() function is one of the simplest functions in that it takes nearly any kind of input. A function is designed to take input and return something back to you. If you give SUM() an input of 1, it will return 1. You can try it out in Excel by typing in:

=SUM(1)

This is fairly useless for anybodies purpose, though. To do something useful, we need to give it more arguments. In Excel, when we talk about arguments we are talking about the input to a function. It looks something like this:

=SUM(argument_1, argument_2, argument_3, argument_4)

This example includes four arguments separated by commas, this is the standard method of passing information into a function. Cell references, numbers, names, ranges, other functions, or even strings can be passed into a function -- as long as the function is expecting it. SUM() cannot accept strings and if you try to pass it a string it will return #VALUE! meaning, which is Excel's way of saying that you gave a function a data type that it wasn't expecting.

A cell reference is simply a reference to another cell. If you type "Hello," in B1 and " world!" (without quotes) in C1 then =B1 & C1 in cell A1 then Excel will take the value of B1 and append (which is what & does) C1 onto the end returning a value of "Hello, world!" to A1.

There are a couple of ways to pass an argument to a function: you can simply type in the function box or in the cell (figure 1) or you can click on cells, row/column headers, or click-and-drag selection boxes (figure 2).

Figure 1

Figure 2

You can select multiple cells at once by clicking and dragging or select multiple individual cells by clicking them while holding down the Control (CTRL) key. Once your selection is made you can click the check mark between the "x" and "fx" buttons on the function bar (pictured at the top of both figures above) or by pressing enter on the keyboard. If you had a column of numbers in A, you could type in "=SUM(" and then click on the column header A and press enter to sum the entire column.

Tip: SUM() has a very quick keyboard shortcut. If you select the cell above or to the side of the data you wish to sum, you can simply Alt and = together and then press enter, Excel will automatically sum the data as best it can.

A number or a string can also be passed to many functions. A string is anything in quotes, such as HeLlO WOrlD! in =PROPER("HeLlO WOrlD!"), that is passed to a function and a number is any number passed in quotes or out of quotes (Excel does its best to guess at what you want, if you try to add "1" and "2" it will guess that you mean 1 and 2 and return 3, it will NOT work like this, though, "one" and "two").

Ranges can be passed just like cells, numbers, or strings. You can type in the range such as: A:A selects the entire A column, A:B selects both columns A and B, A1:B15 selects all cells inside the "box" created between A1 and B15. Likewise, you can simply click and drag the selection you'd like.

Named ranges, or names, can be passed to functions as well. These have two purposes: dynamic cell selection (which we'll cover another day) and user-friendly readability. You can name any cell or any selection of cells by simply selecting them and entering the name you wish to the left of the function bar (red arrow in figure 3).

If you were to type "BeeTwo" over "B2" in the Name Box, you could reference that cell as "BeeTwo" from that point on. This is extremely useful for other people that use your spreadsheets to be able to understand what you've done.

Figure 4 illustrates in a very simple way how this can be useful. By naming the value for total orders ORDERS and the value that contains the number of days those orders cover DAYS we can write in another cell the following function: =ORDERS / DAYS. This function makes it very simple to the next person to use this spreadsheet to say that this cell takes the number of orders and divides it by the number of days. This is not evident if you simply typed =A2 / A3. Naming cells or ranges is something to keep in the back of your mind but it is not common to name everything.

Figure 4

That about sums up how to pass arguments to functions. The concepts here can be applied in a variety of ways to get very interesting and useful results.

Function: SUMIF()

Welcome to Learning to Excel! In coming days, I hope to start rolling out posts that help the average Excel user learn new skills, better ways to use Excel, and enjoy the experience of solving problems a little bit more.

The first topic on the agenda is the SUMIF() function. This little function is invaluable in adding lines based on a condition. For example, you may have a sheet of shipping data, hundreds or thousands of records, identified by customer name and you'd like to find out how many units you shipped to each customer. Let's set up a sample to work with first.



Optionally: Download the file here: SUMIF() example

As you can see, we're looking at shipments made to three companies: Apple, Google, and Microsoft. For this example, Item Type and Date are irrelevant (they'll be used in later examples). We want to select and add together only those shipments that belong to a particular company; in order to do this, we can use the function SUMIF(). The benefit of this method is that it does not modify the original data. It can stay organized in the same way it is now which is important as the data grows or becomes more complex. The drawback is that we cannot define multiple criteria -- we cannot select all Code shipped to Apple using this function without adding something to our data, another column in this case.

To begin, we can place our three company names to the side of our data. You want to avoid putting it underneath the data as you can run into problems with Circular References. This error is caused when you have a reference in a cell to another cell that is also referencing the original cell. Here is a visual representation of what is going on:


Circular Reference

Cell A depends on Cell B for its value while Cell B relies on Cell A for its value. Imagine if you were falling, a good reference would be grabbing onto a person standing on solid ground, a circular reference would be like grabbing onto a persons hand who is falling along with you. You certainly can put the summary data under the original data, you simply need to be aware of and avoid creating circular references.

Now that we've covered good practices regarding where to place our summarized data, we can begin to write the formulas.

We've added Apple, Google, and Microsoft in column F and added our formulas to column G. Let's take a look at the formula: what it is doing and why it is doing it. First, we'll look at the syntax of the function (or what you need to enter to make it work):

=SUMIF(range, criteria, [sum_range])

Required: range, criteria

Optional: sum_range

In our example above, the sum_range is not optional because when it is omitted, it uses the range field as the data to add together (and words can't be added together like numbers). If we wanted to find all occurrences of ship quantities of 9, we could enter the range into SUMIF() on row D, make the criteria 9, and leave sum_range blank. Likewise, if we wanted to sum up shipments greater than 5, we could write =SUMIF(D:D, ">5").

We have a couple of options when referring to a range, using: an entire column or row, a range of cells (say A1:A10 or A1:D1), or a named range (which we'll cover in more detail at a later time). All we need to know right now is that the data we want is in column A and we can use the entire column as our range in the function. When we click the A header or type in A:A, we are saying select the ENTIRE column, be careful that there is no data below the data you want. If, for example, we had another table of shipments beginning on row 100, it would also be included in the summary and return unexpected results.

The criteria can be static data, a cell reference, or it can make use of > (greater than), < (less than), <> (not equal to) characters if put inside of quotations: ">5" is valid but >5 is not...or it can be a combination of these joined together with &. For example, =SUMIF(D:D, ">" & D2) will sum all values greater than 9 (which is cell D2) in column D.

The sum_range need not be the exact same size as the range, any data that is above or below the range will be omitted, however. If the range is A1:A10 and the sum_range is D:D, the results will still be accurate in our example.

To put this information to use, we have the following formulas in cells G2, G3, and G4:

G2: =SUMIF(A:A,F2,D:D)

G3: =SUMIF(A:A,F3,D:D)

G4: =SUMIF(A:A,F4,D:D)

A:A tells SUMIF() to look at the entire A column. F2, F3, and F4 tells the function to look at the values in those respective cells. D:D tells the function to use column D as the sum column. It looks at each value individually. If A1 matches cell F2, then it adds the value of D1. Here is a visual description:

I hope this guide to using SUMIF() proved helpful! Please leave any feedback or questions you may have in the comments!