To select a cell in Excel, you have two basic methods: RANGE and CELLS:

Range ("A1").Select

Range("RangeName").Select

Cells(3, 4).Select   'Selects Row 3, Column 4, i.e. cell D3

Range works well for hard-coded cells. Cells works best with calculated cells, especially when you couple it  with a loop:

For i = 1 to 10
     Cells(i, 1).value = i   ' fill A1 through A10 with the value of i
Next i

Note that your focus does not change. Whatever cell you were in when you entered the loop is where you are when you leave the loop. This is way faster than selecting the cell, changing the value, selecting the next cell, etc. If you are watching the sheet, the values simply appear.

Slicers are a new feature in Excel 2010 that let you add a snapshot view of a pivot table to a worksheet.  Slicers are like visual filters.  You may want to use a slicer when you only need to display a section of a pivot table.

For example, the pivot table below shows conference registrations broken down by city and state.  You can add slicers to this pivot table to display the data from one state or from only one town from one state.

Sparklines are miniature graphs that fit inside single worksheet cells.  They can show you trends or changes that may not be easily noticed by viewing the values in the spreadsheet.

Although Sparklines can be located in any cell, they are most effective when placed next to the data to which they refer.

Note that Sparklines are only available in .XLSX or .XLSM files. The option is greyed out in .XLS files.

One of the techniques I have found very useful in writing Excel VBA code is to make the cell, row, and column references public constants rather than hard coding them.

Take a reference to a row and column:

   cells(4,27).value

If row 4 is the first data row and column 27 is the last column in the list, it is more useful to make them constants and refer to the constant:

   Public Const intFirstDataRow As Integer = 4
   Public Const intLastRow As Integer = 27

And then make the above reference:

    cells(intFirstDataRow, intLastRow).value

This way, when you add a couple of columns or move the first data row down, you change the constant once and the code all works the first time.

Searching and replacing 27 with 31 is fraught with problems since it is too easy to change values or longer numbers (12734 to 13134) inadvertently.

If you are working with a formula that may refer to a blank cell or otherwise generate an error message, you can use a combination of the IF-THEN-ELSE and the ISERROR function to handle the #DIV/0! or other error messages that may appear.

The IF-THEN-ELSE has 3 parts: the condition, the TRUE condition, the FALSE condition. The following formula tests A1 for the number 100. If it is found, the cell displays the contents of A1. If it is not found, it multiplies another cell (B1) by 2:

=IF(A1=100,A1,B1*2)

By using one of the ISERROR functions, you can test for an error condition and use the IF statement to display a message or nothing at all. The common IS functions are ISERR, which returns TRUE for any error value except #N/A and ISERROR, which returns TRUE for any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

Suppose a cell is going to contain the average of several cells. If the cells are empty, the result will be division by zero and the #DIV/0! error will appear. The following formula will take care of this condition:

=IF(ISERR(AVERAGE(A1:A10)),”No Data”,AVERAGE(A1:A10))

If the formula AVERAGE(A1:A10) returns an error (most likely because there are no numbers in the range), the cell will display the words “No Data”. If there is no error, the cell will display the results of AVERAGE(A1:A10).

Most of you probably know about the Extend box in Excel, even if you don’t know its name. The Extend box is the little square at the bottom right corner of the current cell or range. When you put your cursor on it, the cursor changes to a small black cross and the fun begins.

Extending a Cell’s Contents

The simplest way to use the Extend box is to drag it, either down or across, over a range of cells. When you let go, the range will fill with the contents of the original cell or range, based on these rules:

• Text will be copied
• A single number will be repeated.
• A range of numbers will create a series based on the differences between the numbers
• A formula will be copied and all cell references will change appropriately.
• A day, month, or quarter name will be extended into a list: Monday will be followed by Tuesday, Wednesday, etc.
• A custom list will be expanded. (You can create custom lists by highlighting the range, then selecting Tools | Options | Custom Lists and adding the range.)

Smart-Tag Options

With the advent of Smart Tags in Excel 2002, when you finish extending, a Smart Tag will appear. Clicking the Smart Tag will display a list of choices that will vary based on the type of data you are extending:

• Text or formula will let you extend with or without formatting, or just the formatting.
• Numbers will let you create a series (1, 2, 3, etc.) and extend with or without formatting.
• Dates will let you fill days, weekdays, months, years, with or without formatting.

Right-Dragging the Extend Box

If you extend by right-dragging, you’ll get a Quick Menu with the Smart Tag options as well as a link to the Fill Series dialog box, where you can create quite complex series, if you need them.

Double-Clicking Magic

Often, when you are working with a list, you often need to add a formula, as shown in the top picture above. If you double-click the formula (in C2 and D2 above), Excel will extend this formula to the length of the list, as shown in the picture on the bottom. (In our example, we used a range of two cells. We could have done C2 and D2 individually.) This is especially helpful if you list is a long one. Be aware that it will stop at the first blank cell in the column to the left.

This is a situation that often comes up in our Excel classes:

“I need to add data to my spreadsheet, but I want to lock the cells that contain formulas so they don’t get overwritten.”

It’s easy to Protect an entire spreadsheet from the Tools | Protection | Protect Sheet… menu item. However, this option protects all the cells in the spreadsheet.

Unless…you first unlock the cells you want to remain editable. Here’s how:

1. Select the cells you want to be editable.
2. Select Format | Cells, and click the Protection tab:



3. Clear the check from the Locked checkbox.
4. Click OK.
5. Select Tools | Protection | Protect Sheet…



6. Leave all the default items checked. (If you wish, you can add checks to any features you want users to access.) Add a password if you want to prevent a user from Unprotecting the sheet from the Tools menu.
7. Click OK.
8. Users will now be able to edit only the cells that are unlocked. If a user tries to edit a locked cell on a protected worksheet, he/she will see the following message:

By default, Word and Excel list the last four files you opened at the bottom of the File menu. You can increase the number of files on this list to nine by changing a program setting.

Here’s how:

1. Select Tools | Options.
2. Click the General tab.
3. Change the number in the Recently used file list to 9 entries.
4. Click OK.

When you are editing a large spreadsheet, you may want to lock the row or column headings into place so that they don’t scroll when you scroll the other cells in the worksheet. Excel calls this freezing panes. You can freeze any number of rows at the top of the screen or any number of columns on the left side of the screen, or both.

To keep row and/or column headings visible as you scroll:

1. To freeze one or more rows, select the row below the row you want to freeze.

To freeze one or more columns, select the column to the right of the column you want to freeze.

To freeze both rows and columns, click the cell below and to the right of the cells you want to freeze.

2. Select Window | Freeze Panes.

To remove “Freeze Panes” so that the worksheet scrolls normally:

1. Select Window | Unfreeze Panes.

When panes are frozen, you typically move in the bottom or lower right pane. However, you can click into any frozen pane and scroll around it.

2 + 3 * 4 =

Which is the correct answer?

a.) 24
b.) 20
c.) 14

If you were paying attention in junior high school math class, you know about the Order of Operations, which dictates that formulas evaluate multiplication and division before addition and subtraction.

Therefore, Excel reads this formula as 2 + 12, not 5 * 4, and the correct answer is 14. (24 is not an option, but someone in class always guesses it, so I thought I’d throw it in).

If you need a handy mnemonic for remembering the Order of Operations, or the order in which formulas are evaluated, remember

PEMDAS, or

P lease Excuse My Dear Aunt Sally

(P = Parentheses, E = Exponent, M = Multiplication, D = Division, A = Addition, S = Subtraction).