Cheap Windows 7 Excel Team Favorite Keyboard Short

This post is brought to you by Chad Rothschiller a Program Manager on the Excel Team and is Part 2 of his ongoing series on the Excel Team’s Favorite Keyboard Shortcuts.

In Part 2 of this series on Excel shortcuts, I’m going to focus on a few shortcuts that help out with the absolute essence of Excel: Formulas.

 

F2 – Edit Mode

For starters, pressing F2 is a convenient way to start editing a formula in a cell: it places the cursor at the end of the formula so you can make quick additions.

Before F2:


After hitting F2:


Now that I’m in “edit” mode, I can start typing to add to the end of my formula, or use arrow keys to get to the spot in the formula I want to change. Additionally,Office Home And Student 2010, hitting F2 again will now put me into “enter” mode, which means that if I am at a place in the formula that is expecting a reference, then using my arrow keys will now start selecting a cell or range in the grid (instead of navigating through the text of the formula, like in “edit” mode).

This same concept of “edit” vs. “enter” mode also applies when working with a RefEdit control (it’s found in dialogs that ask you to select a range or type a formula, for example the Conditional Formatting “new rule” dialog):


Let’s say I create a Conditional Formatting rule based on a formula, and now need to go back and edit that formula, either because I made a mistake, or I wanted to build a simple formula first to see if it works, then go add complexity.

Once the RefEdit control has focus, because I’m a keyboard user, I always try to use the arrow keys to go to the part of the formula I want to change. However, since we’re not in “edit” mode by default here, Excel responds by selecting the cell to the left of my active cell in the sheet and adding a reference to it in my formula. Usually after screaming in frustration I remember I can press F2 to change into “Edit” mode, which allows me to use the arrow keys to navigate through the formula text instead of selecting new cells as references.

In the following screenshot, the conditional formatting dialog is active,Windows 7 Home Basic Key, and I am attempting to edit the formula. I have accidentally hit the left arrow key while not in “edit” mode,Office 2010 Product Key, and now I have an extra, unwanted reference in the formula:


If instead I had pressed F2 first, that enables my arrow keys to move the cursor through the formula (Note that the status bar now says “Edit”):


F9 – Partial formula evaluation

F9 is really helpful for debugging complex formulas, because it allows you to select smaller pieces of your formula and evaluating just that highlighted part. To try it out, just select the portion of the formula that you want to evaluate:


and press F9:


Be careful because any changes that are made in the formula bar will be committed to the cell if you press ENTER. It’s a good idea to press ESC to preserve the original formula.

F4 – Absolute / relative references

Before talking about the shortcut, here’s an explanation: an absolute reference means that as the formula is copied from cell to cell (or as the cell is shifted around due to inserts or deletes of other cells), the reference does not adjust or change; the reference is not relative to the cell location, it is absolute. A relative reference does adjust or change as the formula using that reference is copied from cell to cell; it adjusts because it is considered to be relative to the formula cell’s location. For absolute references, each part of the reference (the row and column parts) are preceded by a “$”, for example $A$1 is an absolute reference to cell A1. The row and column portions of a relative reference are not preceded by a “$”, for example A1 is a relative reference to cell A1.

Excel uses relative references by default, which makes it easy to fill formulas down and across: the references automatically update, which is what you want, most of the time.

One case where you might want to use an absolute reference is when using the VLOOKUP function, because though the value you’re wanting to look up might change (for example, as you fill the formula down a column), the actual location of the table of values that you’re wanting to look at doesn’t change or adjust as you fill the column down. That table’s location isn’t the same relative offset to each cell containing the formula.

When entering the following VLOOKUP formula I will select all the references using my keyboard, to allow me to easily choose the range for my formula (remember CTRL + SHIFT for quickly selecting blocks of data). Once I get the proper range selected for the table array parameter, I hit F4 to cycle through all of the various combinations of absolute and relative references. Here’s my formula before pressing F4:


…and after F4:


Continuing to press F4 will cycle through the other “mixed reference” options, i.e. relative row with absolute column and absolute row with relative column.

CTRL + ` – Show formulas

If I want to get a quick understanding / review of all the formulas in a sheet, instead of selecting each cell individually and examining the formula bar, I can use Show Formulas. Here is what my workbook normally looks like:


And after pressing CTRL + ~ I see the formulas in the cells, not the values:


ALT + = for AutoSum

If your active cell is at the bottom of or to the right of a range of data, typing ALT + = will automatically put the SUM function into that cell, along with the range reference for the data above, or to the right (respectively).

Before ALT + =:


After ALT + =:


Incidentally, if Excel doesn’t know what range to put it, you’ll just get =SUM(), which is better than typing it all out, right?

CTRL + [ and CTRL + ] to select direct precedent & dependent cells

In a complex spreadsheet with formulas that build on and reference each other,Office 2010 Home And Business, you may want to know which cells are precedents of or dependent on a particular cell. For example,Cheap Windows 7, consider this spreadsheet (in show formulas mode (CTRL + `)):


Cells A1, G3, and G5 contain a fixed value of “1”, and C1 depends on A1, E1 on C1, G1 on E1, and i1 on G1, G3, and G5. Since I don’t normally work in Show Formulas mode, this is more like what I’ll see on a day to day basis:


…and of course, this view gives me a great sense of the results, and no sense of where the formulas are, or what the dependencies are.

Selecting cell i1 then typing CTRL + [ shows me:


Then I can press ENTER repeatedly to cycle through the selected cells.

To select all cells that are precedents of i1 (several levels of formula reference), type CTRL + SHIFT + {:


The converse works as well, for formula cells that are dependent on the active cell. Use CTRL + ] and CTRL + SHIFT + } for that functionality.

That’s the end of Part 2 of my Excel shortcuts series, Formulas. In Part 3 we’ll cover some of my favorite miscellaneous shortcuts and wrap up this series.
<div