Excel – General

ROUNDUP

ROUNDDOWN

Moving Between Sheets

Ctrl + Page Down = move one sheet left

Ctrl + Page Up = move one sheet right

Keystrokes for Moving the Cell Cursor

Home = Cell in Column A of the current row

Ctrl + Home = A1

Ctrl + End = last cell in active area of the worksheet

Copying a Worksheet

If you hold down the Ctrl key while you drag the sheet tab icon, Excel inserts a copy of the worksheet at the place where you release the mouse button. You can tell that Excel is copying the sheet rather than just moving it in the workbook because the pointer shows a plus sign (+) on the sheet tab icon containing the arrowhead. When you release the mouse button or remove your finger or stylus, Excel inserts the copy in the workbook, which is designated by the addition of (2) after the tab name.

FORMULATEXT() adds notes to formulas

If you and your colleagues share spreadsheets, it’s nice to have notes that explain what your formulas are doing (plus a copy of the formula). Some organizations even require it, especially if you’re a programmer or analyst.

This little formula and the +N function are the quick answer to your spreadsheet documentation needs. Move your cursor to the column beside the formula column. If that column of cells has additional data in it, you can insert another column (which you can hide when you’re working or printing the spreadsheet), or you can create a separate “FORMULATEXT” matrix out to the side of your original spreadsheet.

The spreadsheet shown below occupies A1 through D15. Move your cursor to E5 and select the FORMULATEXT() function from Formulas > Function Library > Lookup & Reference. In the Reference field of the Function dialog, enter the cell address D5 or just point to it and click OK. Notice that a text version of the actual formula prints in cell E5.

N() function: Another way to add notes

In this example, the formula is self-explanatory, so additional comments aren’t really necessary. However, if this were a long, complicated formula, you could add comments that explain what the formula is doing by just entering +N plus the comment, inside quotes inside parentheses, at the end of your formula in D5. (Note: You would not put it at the end of the Reference in E5.)

For example, move your cursor back to cell D5 and press Function key F2 (to edit your formula). Then type +N(“your comments here”) at the end of your formula (with no spaces). And, if you’d like (although it’s redundant), copy and paste the formula down to D15 and E15.

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: