Posts Tagged 'Excel'

Ctrl + ; – Puts the current date into the active cell; does not update automatically.

Ctrl + Shift + ; – Puts the current time into the active cell; does not update automatically.

Ctrl + A – Selects all the cells in the worksheet.

Ctrl + Page Up – Makes the previous sheet in the workbook the active worksheet.

Ctrl + Page Down – Makes the next sheet in the workbook the active worksheet.

F2 – Lets you edit the cell without double-clicking on it.

F11 – Creates a chart on a separate sheet from the selected data.

To highlight the cells referenced by an Excel formula, click into the cell with the formula, and press Ctrl-[ (Ctrl and the open square bracket key).

Excel highlights all the cells referenced by the formula, and selects the first referenced cell. Press ENTER to move the cell pointer to the next referenced cell.

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.