Excel Page 9

Home Browsers MsOffice 97 & 2000 Site Search Windows

MsOffice Index 1 | MsOffice Index 1I | Microsoft PowerPoint 97 | Microsoft Bug of the Month | Excel Page 8 |   
[ Excel Page 10 ] [Updated 06/07/06]


Turn Rows Into Columns (And Vice Versa)
Works with Excel 97 and above

If your data is organized in columns (January through December, for example, in the first column, sales figures in the next column, and so on), and you want the data to be displayed across columns in rows, you need Excelís transpose feature.

Select the range that you want to transpose (as many rows and columns as you wish), then use the Edit/Copy command from the main menu. Move to the cell that you want to be the upper-left corner of the transposed range and use the Edit/Paste Special command from the main menu.

  1. From the Paste Special dialog box, check the ďTransposeĒ option. 
  2. The left-most column of your data range now becomes the top row of the new range, the second column of the original range becomes the second row, and so on.

You can apply the transpose function in the other direction -- that is, if you data is laid out in rows, select it and repeat these directions to paste the data into columns.

Combine Transpose with Other Operations

Note that Excel automatically replaces any values (formulas, numbers, dates, you name it) in the destination location. However, transpose can be added to (combined with) another Paste Special operation. For example, suppose your data is in cells A1 through B3:

Jan  1
Feb 2
Mar  3

Now suppose you have the values 100, 200, and 300 in three cells arranged in a single row -- in D1, E1, and F1, for example.

  1. Copy cells B1 through B3 (which contain 1, 2, and 3). 
  2. Use the Edit/Copy command, move to cell D1, use the Edit/Paste Special command, then check the Transpose option. In addition, in the Operation section of the Paste Special dialog box, choose the ďAddĒ option. 
  3. When you click on OK, the values 1, 2, and 3 are added to 100, 200, and 300, changing cells D1 through F1 to 101, 202, and 303.

Absolute Cell Reference Shortcut---Hint? Think F4

Entering absolute cell references in a formula to keep the cells constant when copying the formula, can be a real pain. To enter the cell reference we have to enter several dollar signs. There is a simpler way to convert the cell addresses to absolute addresses: Use the F4 key.

For example, 

   =A1+A2

After the formula is entered, select it (swipe it with your mouse), then press F4 to convert the formula to

   =$A$1+$A$2

You can continue to press F4 for variations of absolute addresses. Press it once more and the formula becomes

   =A$1+A$2

Which ďfreezesĒ the row but not the column. Press it once again and the formula becomes

   =$A1+$A2

Press F4 one more time and youíre back to your original formula.

You can also use F4 as you build a formula. For example, you can enter

   =A1

and press the F4 key once to turn the reference you just typed to $A$1. Type the plus sign, then A2, press F4, and Excel adjusts the second factor (A2) in your formula, leaving A1ís absolute reference untouched. That is, the formula becomes

   =$A$1+$A$2

Note use the shortcut key combination of:

 "Shift + 4" is the $ symbol -- so itís F4 that you want.

NOTE: If the whole of the formula is highlighted, then "F4" will change all the references. If one highlights only a single reference within the formula then "F4" will only change that reference.


Quick Copy, Paste-Value Options

If you need to quickly copy a formula from one cell to another, the typical approach is to use the familiar Edit/Copy/Paste technique.

However, in some cases you may not want Excel to adjust the formula when you copy a cell to the cell directly below it. You have two options.

Consider a quick example: cell A1 and A2 contain values. In cell A3 is the formula

=A1+A2

If you copied cell A3 to cell A4, cell A4 will end up with the formula

=A2+A3

because Excel "adjusts" formulas using relative addressing.

Instead, move to cell A4 and press Ctrl + ' (Ctrl plus the apostrophe). Cell A4 will now contain exactly the same formula as A3.

If you want to quickly copy the value (not the formula) from one cell to the cell directly below it, use Ctrl + " (Ctrl plus Shift plus the apostrophe).

Wait, There Is More To This Story:

The shortcut works only if you want to copy the formula for the cell immediately above the current cell.  Thus, you can move to cell A4 and press Ctrl + ' and Excel will copy the formula (without adjusting cell addresses) from cell A3, the cell immediately above it.  You cannot copy cell A3, move elsewhere on the worksheet (such as to cell B200), and press Ctrl + ' to copy the unchanged formula. 

To copy a cell to a cell other than the one directly below it without adjusting cell references: 

1. Move to the cell whose formula you want to copy.   

2. Swipe the entire formula in the formula bar (at the top of the worksheet) with your mouse to select it.

Alternatively: Double-click the cell to switch to edit mode or press F2.  Excel displays the formula (rather than the result) in the current cell.  Swipe the entire formula with your mouse.

Second alternative: Press F2 to enter edit mode; Excel puts the cursor at the end of the formula.  Press Shift + Ctrl + Home to select the entire formula -- from the current cursor location to the beginning of the formula.  This is similar to using Shift + Ctrl + Home works in Word, which tells the word processor to "Select from beginning of document to current location.")

3. Use the Edit/Copy command from the main menu or the familiar keyboard shortcut for copying: Ctrl + C.

4. Press the Esc key to exit Edit mode.  (This is important.  If  you don't press Escape and move to another cell, Excel continues to work in formula edit mode -- thinking you want to modify the displayed formula using the new cell's address.)

5. Move to the destination cell and use the Edit/Paste command from the main menu or the familiar keyboard shortcut for pasting: Ctrl + V.

Note: It's Step 2 that makes all the difference between copying the formula text as is (to make an exact copy of the formula string itself) and the usual "Edit/Copy" command sequence which copies the formula and tells Excel to adjust the references when pasted.

Still more options
There is more then one way to skin a cat!
If you like skinning cats, that is.

Still More On Copying Formulas Without Adjustment

Yet another way to copy formulas without having Excel adjust the formulas (using relative addresses).

 

Not to beat a dead horse, but...

I do use the F4 key to make references absolute sometimes, and I think that has been around since Lotus 1-2-3!

 

But when I need to replicate an ugly nested vlookup within an

IF statement/formula

and will need to tweak only one or two references later; Try this:

 

1. Edit the formula, add an apostrophe (') to the beginning of the current formula, and press Enter. Now it is just text.

2. Copy this wherever you need it.

3. Go back and edit the original cell to remove the (') and press Enter again so it will calculate as a formula again.

4. Go back to the newly copied text formula, you can make any tweaks and then remove the (') from that one


Prevent duplicate Excel entries within a range

If you want to avoid duplicate entries within a range when you're working in Excel, then check out this tip from Mary Ann Richardson. You'll learn how to set it up and avoid redundancy.

Sometimes you may want to prevent users from entering duplicate values in a range of Excel cells. For example, you are entering price data for a number of items, and you want to make sure all item numbers entered in range A2:A52 are unique. To prevent duplicates, follow these steps:

  1. Select the range of cells that will contain the item numbers.
  2. Go to Data | Validation.
  3. Click the Settings tab.
  4. Under Validation Criteria, select Custom.
  5. Under Formula, enter 
    =COUNTIF($A$2:$A$52,A2)=1.
  6. Click the Error tab.
  7. Under Title, enter Duplicate Entry.
  8. Under Error message, enter an appropriate message, such as: The value was already entered. All Item Numbers must be unique. Please try again.
  9. Click OK.

Now when the user attempts to enter a duplicate number, Excel will prevent the entry and alert the user with an error message.


Create a formula for entering a series of incremental times in Excel

You can have Excel enter time increments in your worksheet automatically.

Suppose you're working in Excel and need to record the price of a certain stock every 15 minutes as it is traded over a period of five hours. Rather than manually typing in the time for each recording, you can use a formula to quickly and automatically fill in the time increments for you. Follow these steps to enter 15-minute time increments in the range B1:B20:

  1. Select B1:B20.
  2. Right-click the selection and select Format Cells.
  3. Click Time and select 1:30 PM and click OK.
  4. Click B1 and enter 10:00 AM.
  5. Select B2:B20 and enter =B1+Time(0,15,0)
  6. Press [Ctrl][Enter].

Excel fills the range with 15-minute time increments. You can now enter the data in the corresponding cell range in column C.


The Match Function

Excelís Match function helps you find a value in a cell range.  For example, if your worksheet contains part numbers sorted in ascending order, you can use the Match function to locate a desired part number in the list.

The syntax for Match offers several options.  There are three parameters:

   Match (value to find, cell range to search, match type)

Letís suppose that our part number list begins in cell A1 and goes through A100.  Weíll ask the user to enter the part number in cell C1, and look for that value in the list.  To find the row containing the part number, weíd write the formula as:

   =Match(C1, A1:A100, 1)

We are asking Excel to find (match) the value in cell C1 with values in the range A1 through A100.  Excel returns the occurrence (relative to 1) within the range where the part number is found.  (If multiple occurrences are found, Match returns the last one,
which Iíll explain in a moment.  For now, letís assume that the values in our range are unique.)

Letís look at the last parameter in the formula -- the "match type" option.  There are three possible values for the match type: 1 (the default), 0, and -1.


Match Type 1

If you use 1 for this parameter, as we did above, you are asking Match to find the largest value less than or equal to the value youíre looking for.  Think of it just as though youíre playing like the TV game show The Price is Right -- Excel finds a match without going over the value youíre looking for.

For example, if this is our list of part number list:

   100
   110
   120

and we look for 110, the Match function will return 2, since 110 is the second element in the list.  However, if we look for 115, thereís no exact match, so the closest value to 105 -- without going over 105 -- is 100.  Thus, the Match function will return 1, because 100 is the first item in the list that doesnít exceed the searched-for value.

If your list has multiple values in the range youíre looking at, such as:

   100
   110
   110
   120

and you ask Match to look for 110, the function will return 3, not 2. Match finds the last element in the list that matches, and thatís the third element in this example.

Special Note: if you want to find (match) the value 200, the Match function will return 3, since thatís the last value in the table -- that is, 120 is the value closest to 200 without going over 200.

Also note that when you use "1" for the match-type parameter, the range of values youíre searching for should be in ascending order. 

For example, if our parts list was out of order and read

   100
   120
   105

and we look for 105, Excel will return the value 1.  Thatís because Excel starts at the top of the list and looks at 100, thinks "letís try the next value," looks at 120 and says, "Oops, thatís too big," so Excel goes back to 100 and says "OK, thatís the match," in spite of the fact that 105 is actually in the list.  When a list is out of order, itís possible Excelís Match wonít return the correct value you want if you use "1" for the match type.

Note, also, that Match always returns a value when the match type is 1 (assuming the range youíre searching isnít empty and the search value is greater than the first value in the list).  Thatís because at the very least Match will always match the last element in your range.

However, if the value youíre looking for is less than the first element in the list (if youíre looking for 50 in our three-element list, for example), Match returns an error: #N/A.

Exact Matches: Match Type 0

To avoid the problem of returning the wrong value in an unsorted list, as well as returning a value as though a match were found when no exact match is made, use the value 0 as the match type. 

With 0, Match finds the first cell containing a value equal to what youíre looking for.  Therefore, if the list is our out-of-order

   100
   120
   105

and we look for 105, Match will return 3, since 105 is the third element in the list.  Match doesnít expect the list to be sorted, and keeps looking for an EXACT match.

If no match is found (if we look for 123 in this three-element, unsorted list, for example), Match returns the error value #N/A.

Descending Sorted Lists: Match Type -1

If your list is sorted in descending order, such as

   120
   110
   100

Then you can use the -1 function to find the smallest value greater than or equal to what youíre looking for.  For example, if you look for 105, Match will return 2 because 110 is the smallest value that is greater than the value youíre looking for (assuming there is no
exact match, of course).  Remember, itís looking for the SMALLEST value that is LARGER than the value youíre looking for.

Putting Match to Work: Looking Up Part Numbers:

Letís look at a slightly different example, one that uses Match to interactively find a part number.  Assume in this example that "Part Number" is the column heading in cell A1, and unique part numbers are arranged in ascending numerical order in cells A2 through A100.  Weíll prompt users to enter the part number theyíre looking for in cell J1.  We want to display the row number in J2.  See our illustration in the online edition to follow along in this discussion.



To find the row the matching value is found in, we could write the following formula in cell J2:

   =Match(J1,A2:A100,1) + 1

Note that since thereís a header row, we need to add 1 (an offset) so that the formula returns the correct row number (if a match is found in the third element in the list, itís in row 4).

Now thereís a problem with this approach.  If the part number a user enters isnít in the list, the Match function returns a row number as though a match were made.  Therefore, letís try

   =Match(J1,A2:A100,0) + 1

Unfortunately, with this formula, if there is no match, the program returns the user-unfriendly row number #N/A.

Therefore, letís change the formula to show the row number if thereís an exact match and "PART NOT FOUND" otherwise:

=IF(ISNUMBER(MATCH(J1,A2:A100,0)),MATCH(J1,A2:A100,0)+1,"PART NOT FOUND")

This tells Excel to see if the match returns an exact match (and thus a row number).  If it does, then return the "Match"ed value plus one (to adjust for the header row), and if not, return PART NOT FOUND.

Of course, this approach has an inherent inefficiency: it asks Excel to execute the Match function twice.  Given the speed of todayís systems, thatís probably not a big deal.  There are, however, other approaches: you could hide the #N/A error, such as using conditional formatting. 

Alternatively, you could use the Match logic in a hidden cell (or in a cell not in view of the end user), then in cell J2 use the ISNA function and point to that out-of-view cell (letís say itís Z1000), such as:

  =IF(ISNA(Z1000),"PART NOT FOUND",Z1000)

Other Match Notes

Match shares some behavior with the lookup functions VLOOKUP and HLOOKUP.  Those lookup functions have a fourth argument, the range lookup parameter; it is set to False when you want to find an exact match.  This is the same as using the 0 match type in the Match function.

Why not just use the Edit/Find command? 

That certainly works when you want to find a value in the list, but Match is used when you want to do something with the number that represents the relative location of the searched-for value in your list; that is, you want to use "3" in another formula when the third element in the list matches your searched-for value.

Finally, note that Match works with more than just numbers, as in our examples.  Match can search through cells containing dates or text, for example.   When looking for text, however, note that Match does a case insensitive search.  That is, if your cell range
contains A, B, and C, and you look for "a", the Match function will return 1 (the first element in the list), since "a" and "A" are considered equivalent.


Use custom views in Excel to enhance your presentations

by Mary Ann Richardson | More from Mary Ann Richardson | Published: 2/14/06 Keywords: Office suites | Microsoft Office Rating: 5 (out of 5) Rate it Comments: 6 | 1 NEW | View all Save to my Workspace Takeaway: With Microsoft Excel's custom views, you can readily access whatever worksheet areas you need, which will help your presentations to flow. Here's how to get started.

You want to illustrate your talk by pointing out selected areas of your worksheets. Rather than interrupting the flow of your presentation by having to scroll to and position each area as needed, you could create custom views in Microsoft Excel, which you can readily access by clicking on a list in your toolbar.

First, you will need to create custom views. 
To do so, follow these steps:

1. Go to and select the worksheet area for your view. 
2. Go to View | Custom Views. 
3. Click the Add button, and then enter a name for the view and click OK.

Repeat these steps for each custom view you want to present. To build the custom toolbar for your presentation, follow these steps:

1. Go to Tools | Customize. 
2. Click the Toolbar tab and click New. 
3. Enter a name for your toolbar (for example, Presentation). 
4. Click the Commands tab. 
5. Click View under Categories. 
6. Click Custom Views under Commands and drag it to your custom toolbar. 
7. Click Close.

To quickly go to a view during your presentation, click the drop-down arrow on the Custom View button and select the view from the list.


Editing Cells In Multiple Worksheets Concurrently

Many times I've created a multi-worksheet workbook with column headings, only to realize that I want to change the heading on each worksheet.  For one or two worksheets, that's no problem, but when I have 20 or 30 worksheets, there is a shortcut that makes the

Changes easy.

To edit data in the same cell on multiple worksheets:

1. Click on the tab of the worksheet that contains the data you are going to edit. 

2. Once that worksheet is selected, hold down the Ctrl key and click on the other tabs that contain the same cells you want to make the same change to.  (Alternatively, if the selected worksheet is the first of a contiguous group, you can hold down the Shift key

and click on the last worksheet in the group.  For example, if you chose Sheet1 in Step 1, you can hold down Shift and click on Sheet3 to select the group consisting of Sheet1, Sheet2, and Sheet3.)

3. Click back on the tab selected in step 1.  Make the changes to the cell(s) on this worksheet.

4. When you are done editing, click on a different worksheet tab to "unselect" the selected worksheet tabs.  Alternatively, right-click on the current tab and choose the Ungroup Sheets command.

Writing Formulas Across Multiple Worksheets

One way to organize data is to use multiple worksheets.  For example, suppose you manage payroll for a small business.  You can use identical worksheets -- one for each employee -- in a single workbook.  Imagine that cell C15 on each worksheet contains the total hours worked for that employee for the current week. 

To illustrate, suppose we have three sheets, with tabs labeled

Adam, Betty, and Carl.

To sum the total hours for all three employees, you need to refer to the same cell (you can also refer to a cell range) on the three sheets in your formula. 

Here's how:

1. Click on the cell where you want to place the total.  Let's suppose it's on a fourth worksheet labeled "Total".  We'll put the total hours in cell A1, so move to cell A1 on the Total worksheet.

2. Begin creating the formula in A1 by pressing the equal sign, followed by the function (SUM in our example) and an opening parenthesis.  So far in the cell we have:

   =SUM(

3. Click the tab for the first worksheet you want to include.  In our example, we'd click on Adam.

4. Click on the cell you want to include in the formula.  In our example, we want to include the value from cell C15, so click on cell C15 on the Adam worksheet.

5. Hold down the Shift key and click the tab for the last contiguous worksheet in the range you want to sum.  In our example we would choose the tab for the Carl worksheet.

Note: The formula has to reference contiguous worksheets.  For example, you can't click on the Adam tab, then press Ctrl and click on Carl to select just the male employees.

6. Type a closing parenthesis to complete the formula.

7. To see the formula, click on the Total tab and look at the formula in cell A1.  You'll see

   =SUM(Adam:Carl!C15)

NOTES:

Note 1: Using ranges 

In Step 4 we selected a single cell.  However, you could select a range of cells, such as A1 through B10.  If you want to sum the values for these 20 cells on all worksheets, simply choose a range instead of a single cell in Step 4.

Note 2: Take care when arranging worksheets

You may be tempted to rearrange worksheets in order to use range formulas.  For example, you might rearrange the worksheets so the Adam and Carl's worksheets are side by side, then select the range of Adam through Carl (effectively excluding Betty's data).  Your formula in the Total worksheet will now read

   =SUM(Adam:Carl!C15)

which will include just the male employees' hours.

However, if you move the Betty worksheet back to its original location (between Adam and Carl), the formula in the Total worksheet doesn't change, but the value now includes Betty's hours.

Note 3: Only some functions work

Not all worksheet functions will work with these 3D references.  In addition to SUM, you can use the following functions:

AVERAGE and AVERAGEA
COUNT and COUNTA
MAX and MAXA
MIN and MINA
PRODUCT
STDEV, STDEVA, STDEVP, and STDEVPA
VAR, VARA, VARP, and VARPA

Excel: Copying Charts

Reader Chuck wrote with this problem:

Question:   I have written a VBA/Excel application to read data from a laboratory instrument, record the data with the elapsed time, and simultaneously chart the data/time.  Seems to do this well, but I want to be able to copy the spreadsheet and chart to another workbook.  Unfortunately, the chart is linked to the original worksheet. 

I know how to laboriously unlink the chart, but is there a quick way of doing this so that I can continue to make changes to the chart format if desired?   Would a macro be able to do that for a general case?  (Hmm.  I think I will look into that possibility; get path to new, active worksheet, change the series name, substitute new path into series info. Make it a public macro.)

The laborious way is to click each data set and overtype the reference workbook/sheet name with the current one.  Pasting the chart as a graphic will not allow modifications.

Answer: Yes, that's a lot of work -- and there's a much better way to solve this dilemma.

As you've discovered, there's no easy way to select a range of cells and the chart and copy them both to a new worksheet.  Even when you copy and then paste a chart, cell references are adjusted to point to the original data, not the data in the new worksheet. 

There is a simple solution, however.  You can copy the data and a chart from your workbook to a new workbook and have the new chart reference the data in the NEW workbook.  The trick is to use the pop-up menu on the workbook sheet's tab (at the bottom of the work area).

1. Open the old workbook (with the chart and data you want to copy).
2. Right-click on the tab of the worksheet containing the chart and data and select "Move or Copy".
3. In the "To book" field, choose "(new book)".  (I chose a new workbook, which opens a new workbook within a brand new file.)  Be sure to check the "Create a copy" option -- otherwise it moves the data from the old workbook.
4. Click OK.

Your new workbook should now have a new tab with the data and chart.  If you wish, rename the chart tab (double click on the name in the tab and enter a new name) and the chart's data series points to the data using the new name.

Note: You can also copy your data and chart to an existing workbook.  Before performing step 1 above, open that existing file, and in step 3 choose its name (and the position among worksheets within that file) instead of choosing "(new book)".


Displaying values with more than 12 characters in Excel

By default, Microsoft Excel displays cells that contain a number with more than 12 characters as scientific notation. Follow these three simple steps to change how Excel formats these large numbers.

If you ever enter a very large number into a cell, you'll notice that Microsoft Excel displays it in scientific notation. For example, when you enter the number 1231231231234 in a cell, Excels displays 1.23123E+12. No matter how large you make the cell width, Excel still displays a number larger then 12 characters in scientific notation.

To display the number in a format other than scientific notation, you need to reformat the cell(s). Here's how:

  1. Select the cells that will hold the larger values and right-click the selection.
  2. Select Format Cells.
  3. In the Number tab, select the desired format (e.g., Number) and click OK.

Now when you enter the values that have more than 12 characters they will be formatted as numbers and not scientific notation.


Create a summary report in Excel with data consolidation

If you have a number of Microsoft Excel worksheets that contain related data, you'll likely need to create a report that consolidates and summarizes the data. If those worksheets are laid out identically to one another, this tip will show how you can have Excel's Data Consolidate feature consolidate the worksheets into a summary report.

If you have two or more Microsoft Excel worksheets that are identical to each other (except the values are different), you can have Excel's Data Consolidate feature consolidate the worksheets into a summary report.

For example, suppose you have a workbook that consists of two worksheets. One worksheet has your students' names in A1:A20 and their corresponding midterm grades in B1:B20. The second worksheet lists the students' names in column A and their final grades in column B.

To create a worksheet listing the students' average grade, follow these steps:

  1. Create a new worksheet and click A1.
  2. Go to Data | Consolidate.
  3. Select Average from the Function drop-down list.
  4. Click the Collapse dialog button.
  5. Select A1:B20 in Midterm Grades Sheet.
  6. Click the Collapse dialog button and click Add.
  7. Click the Collapse dialog button and Select A1:B20 in Final Grades Sheet.
  8. Click the Collapse dialog button and click Add.
  9. Under Use Labels In: select the Left Column check box. Click OK.

The students' average grades are now listed in the new worksheet.


Make subtotal values stand out in Excel

When using Data | Subtotals to summarize data in your Excel worksheets, the more columns there are between the resulting subtotal labels and their values, the harder it is to read the data. Here's a step-by-step process that will make it easier to read the summarized data.

When Excel's Data | Subtotals menu is used to calculate subtotals and grand totals in a list, Excel displays the resultant grand total and subtotal headings in bold; however, their associated values are not reformatted.

If there are a number of columns between the subtotal labels and their values, you may need to reformat the values manually to make the worksheet easier to read. Or, you can set conditional formatting that will automatically reformat the values for you.

For example, suppose you want to subtotal employee payroll worksheet data by employee and gross pay per month. You may prefer for the subtotals to appear bold and underlined, while the grand total is bold and double underlined.

Before running Data | Subtotals on the worksheet, follow these steps:

  1. Open the worksheet and select the raw data.
  2. Select Format | Conditional Formatting.
  3. Under Condition 1, select Formula Is.
  4. Press [TAB] and enter the following code:
    =$A1="Grand Total"
  5. Click the Format button.
  6. On the Font tab, select Double in the Underline drop-down menu and select Bold in the Font Style drop-down menu.
  7. Click OK and then click the Add button.
  8. Under Condition 2, select Formula Is.
  9. Press [TAB} and enter the following code:
    =Right($A1,5)="Total"
  10. Click the Format button.
  11. On the Font tab, select Single in the Underline drop-down menu and select Bold in the Font Style drop-down menu.
  12. Click OK twice to exit both dialog boxes.

When you run Data | Subtotals to obtain the gross pay subtotals and grand totals, both the labels and the total values for each employee and grand total will now be formatted.


Use index sheets to quickly navigate in an Excel workbook

Navigating through dozens of sheet name tabs in Microsoft Excel to find the one you need can be quite time consuming. Cut your navigation time in half by creating index sheets that contain a list of hyperlinks to every worksheet in the workbook.

When working with large workbooks in Microsoft Excel, you can spend a good deal of time navigating through dozens of sheet name tabs to find the one you need. One method for making this task easier is to build an index sheet that contains hyperlinks to every worksheet in your workbook.

To create the index, follow these steps:

  1. Insert a new worksheet at the beginning of your workbook and rename it Index.
  2. Right-click on the sheet tab and select View Code.
  3. Enter the following code in Listing A.
  4. Press [Alt][Q] and save the workbook.

The next time you open the workbook, the Index sheet will contain a list of hyperlinks to each worksheet in the workbook. Each worksheet also contains a hyperlink back to the Index sheet. In this example, the link back to the Index is contained in cell H1, but it can be in any cell that is blank for all worksheets.

The Code:

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M As Long
M = 1
    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
    
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
        M = M + 1
        With wSheet
            .Range("H1").Name = "Start" & wSheet.Index
            .Hyperlinks.Add Anchor:=.Range("H1"), Address:="", SubAddress:="Index", TextToDisplay:="Back to Index"
        End With
            Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
        Next wSheet
End Sub

Add a Date/Time stamp to your Excel worksheets

Are you tired of manually entering a time and date formula into each of your Microsoft Excel worksheets? Then learn how to shave time off your Excel work by creating a named Date/Time stamp formula.

Suppose your Microsoft Excel workbook contains 10 worksheets, and you want to add a time and date stamp to each worksheet. You could manually enter a time and date formula into a cell in each worksheet, or you can create a named formula that can enter the current date and time without having to retype the formula.

To create the named formula, follow these steps:

  1. Press [Ctrl][F3].
  2. Enter DateStamp in the Names In Workbook text box.
  3. In the Refers To text box, enter the following formula:
    =TEXT(TODAY(),"d-mmmm-yyyy") & " " & TEXT(NOW(),"h:mm AM/PM")

    NOTE: Change the Date Stamp to suit the way your document displays dates.
    As displayed the date will be seen as 11-January-2006 and the current time.
  4. Click the Add button and then click OK.

Now you can add a time and date stamp to every worksheet in your workbook by typing =DateStamp in any cell.

Bo Quick Tip: If you enter times and/or dates on a regular basis then this shortcut is for you. Everyone knows that if you hold down on the Ctrl Key and pressing the : Colon on your keyboard, it will show the current date in whatever format you have for the desired cell. But, did you know that holding down on the Ctrl and the Shift key while hitting the colon / Semicolon  will display the current time? Give it a try.


Adding Worksheets In Numeric Order

Adding worksheets in Excel can get tedious if you do this sort of thing a lot. The code below can be used to add Worksheets (one at a time) to any Workbook in numeric sequence.

Sub AddNumberSequence()
Dim wSheet As Worksheet
Dim lNum As Long
Dim lIndex As Long

    For Each wSheet In Worksheets
            If IsNumeric(wSheet.Name) Then
                lNum = wSheet.Name + 1
                lIndex = wSheet.Index
            End If
    Next wSheet
   
    If lNum <> 0 Then
      Worksheets.Add After:=Worksheets(lIndex)
      ActiveSheet.Name = lNum
    End If
   
End Sub

Adding Worksheets In Month Order

The code below can be used in any Excel Workbook to add Worksheets in a monthly order. If there is already a Worksheet called Dec or December it will NOT add anymore Worksheets.

Sub AddMonthSequence()
Dim wSheet As Worksheet
Dim lMonth As Long
Dim lIndex As Long

    For Each wSheet In Worksheets
        Select Case wSheet.Name
            Case "Jan", "January"
                lMonth = 2
                lIndex = wSheet.Index
            Case "Feb", "Febuary"
                If lMonth < 3 Then lMonth = 3
                lIndex = wSheet.Index
            Case "Mar", "March"
                If lMonth < 4 Then lMonth = 4
                lIndex = wSheet.Index
            Case "Apr", "April"
                If lMonth < 5 Then lMonth = 5
                lIndex = wSheet.Index
            Case "May"
                If lMonth < 6 Then lMonth = 6
                lIndex = wSheet.Index
            Case "Jun", "June"
                If lMonth < 7 Then lMonth = 7
                lIndex = wSheet.Index
            Case "Jul", "July"
                If lMonth < 8 Then lMonth = 8
                lIndex = wSheet.Index
            Case "Aug", "August"
                If lMonth < 9 Then lMonth = 9
                lIndex = wSheet.Index
            Case "Sep", "Septemeber"
                If lMonth < 10 Then lMonth = 10
                lIndex = wSheet.Index
            Case "Oct", "October"
                If lMonth < 11 Then lMonth = 11
                lIndex = wSheet.Index
            Case "Nov", "November"
                If lMonth < 12 Then lMonth = 12
                lIndex = wSheet.Index
            Case Else
                If lMonth = 0 Then
                  lMonth = 1
                  lIndex = wSheet.Index
                End If
            End Select
        Next wSheet
   
    If lMonth <> 0 And lMonth < 12 Then
       On Error Resume Next
        Worksheets.Add After:=Worksheets(lIndex)
        ActiveSheet.Name = Format(DateSerial(Year(Date), lMonth, 1), "mmm")
       On Error GoTo 0
    End If

End Sub


Sorting Cells by Color

Reader Jean writes: I have been working with an extensive Excel spreadsheet with another individual. That person has color-coded cells and now we are trying to find a way of sorting by color-coded cells. All the text is black. Any help would be appreciated.

We suggest looking at the user defined function (UDF) ColorRank described at OzGrid's site:

http://www.ozgrid.com/VBA/Sort.htm

Another method can be found at Pearson Software Consulting, LLC

At this site you will also find::


Simplify your Excel formulas with named constants

Do you use a constant in your Microsoft Excel workbook formulas, such as sales tax or car mileage allowance? If so, you know that if the value changes, you have to manually make the change where it appears in every single formula in your workbook.

You can save yourself considerable time searching for and updating those formulas by creating a named constant. For example, suppose employees are reimbursed $0.37 per mile for car travel. To create a named constant for all formulas that calculate mileage allowance, follow these steps:

1. Press [Ctrl][F3].
2. Enter MileageAllowance in the Names In Workbook text box.
3. In the Refers To text box, enter the following formula:

=.37

4. Click Add and then click OK.

Now you can use the named constant in a formula. For example, if the number of miles for car travel is entered in B10, the formula for Mileage Reimbursement would be: =B10*MileageAllowance.

If the allowance changes to $0.40 per mile, you would follow the following steps to change the constant where it appears in every formula in your workbook:

1. Press [Ctrl][F3].
2. Select MileageAllowance.
3. In the Refers To text box, enter the following formula:

=.4

4. Click Add and then click OK.

You'll be amazed by how much time named constants will save you with your workbook formulas.


Offset And Dynamic Ranges

PCreating Dynamic Ranges 
PThe Self-Adjusting Range

In most tips about formula, the formula evaluates to a single value -- sum(A1:A5) is equal to 100, for example.  We'll discuss the OFFSET function, which returns a cell range.

The syntax of the OFFSET command is:

   offset(origin, rows, columns, height, width)

For example,

   =offset(A1,0,0,5,6)

returns a cell range of five rows and six columns, beginning at cell A1.  That is, it returns the cell range A1:F5.  If each cell in the range contains the value 1, then

   =sum(offset(A1,0,0,5,6))

returns 30.

The rows and columns parameters (both zero in our example) refer to the number of rows and columns away from the origin -- the cell address where you wish to start.  Think of it as the offset to the origin cell address within the offset function itself.

For example,

   =offset(A1,1,0,5,6)

begins at the cell one row below the origin cell address (A1); thus, it starts at cell A2 and returns a range that is 5 rows high and six columns wide (which is the cell range A2 through F6).

Likewise,

   =offset(A1,0,1,5,6)

starts at cell A1 and moves 1 column to the right before beginning the range.  The formula returns the cell range B1 through G5.

To start at a cell to the left of the origin cell address, use a negative number.  For example, to start one column to the left of  the origin address, use -1 as the columns value.  In our example, one ccolumn to the left of A1 makes no sense, but the origin cell address will not always be A1!

Creating Dynamic Ranges

We've described how to change the definition of a cell range.  For example, if you define myrange as the cell range A1:B5, you can use the formula

   =sum(myrange)

to calculate the total of all the cells in that range.  If you insert a row, say at A3, the range is automatically adjusted to include the new row.  No range re-definition is needed.

However, if you add a row AFTER the last existing row in the range, Excel DOES NOT adjust the cell range to include the new row.

The Self-Adjusting Range

This is where the OFFSET function comes in handy.  Using it, you can create a dynamic cell range so whenever you add a row to an existing range, the range expands to incorporate that new row.

For simplicity, let's assume a cell range in Sheet1 that begins in cell A1 and extends to cell A5.  We'll give this simple five-row, one-column range the name myrange.

Use the Insert | Name | Define menu commands from the main menu.  When in the "Define Name" dialog box opens, in the "Names in workbook" field enter

   myrange

In the "Refers to" text box, enter

   =offset(Sheet1!A1,0,0,COUNTA(Sheet1!A:A),1)

Yes, the sheet name (Sheet1) is required in the formula.

The formula tells Excel to define the range as beginning in cell A1, with no adjustment to the row or column of this starting point. The length of the range is the count of the number of filled (non-empty) cells in column A.  The range is one column wide (specified by the last "1" in the formula).

Put the value "1" in each cell in the range A1 through A5, and in cell B1 enter the formula

   =sum(myrange)

You should see the value 5 displayed in cell B1, since there are currently five non-empty cells in the column.

Next, enter the value 100 in cell A6.  COUNTA recognizes that there are now 6 non-empty cells in column A.  Cell B5 will now return 105 as the sum of all values in the newly expanded range.

Using this technique, as long as you add a value to the next consecutive row in the range, COUNTA and the OFFSET  function will keep your range defined properly.  If you skip a row, the formula will be thrown off, since the number of filled cells no longer equals the number of rows in the cell range (the blank row isn't tallied by COUNTA).


Deleting Rows

P There is more then one way to skin a cat:
Is skinning cats something you do on a regular basis?

P
Some Macros do not have to be overly large do accomplish a simple task Check out this simple Macros for size.
P
Another method; Choose  Edit | Delete | Entire Row
P
Yet Another Simple Method without the need for a macro.
P There is more then one way to skin a cat or to delete rows:
           Here is a more reliable alternative:
P Clearing the End Point in Excel
P Get this add-in Microsoft Excel Excess Formatting Cleaner Add-in - Link goes to the appropriate Microsoft site
Supported versions of Microsoft Excel: Microsoft Excel 97, Microsoft Excel 2000, Microsoft Excel 2002, and Microsoft Office Excel 2003.

Reader Anuja writes: Is there a quick way of deleting all rows that do not contain any values in the row?

There are several macros available to delete an entire row, though they're a bit long.

Often, people ask for a macro to delete all blank rows or all duplicate rows from a range of rows in a worksheet. This page has three macros, DeleteBlankRows, DeleteRowOnCell, and DeleteDuplicateRows, which will do this.

Remember, these macros delete entire rows from your worksheet. They do not delete individual cells.

Delete Blank Rows

This macro will delete all of the blank rows in the active worksheet or in the selection. If the
current selection covers more than one row, only blank rows in those rows will be deleted. Otherwise, all blank rows in the entire worksheet will be deleted. The entire row must be blank for the row to be deleted.

Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


End Sub

DeleteRowOnCell

The macro DeleteBlankRows will delete a row if the entire row is blank. This macro will delete the entire row if a the cell in the specified column is blank. Only this column is checked. Other columns are ignored. This macro was suggested by Dana DeLouis, in the Excel programming newsgroup. Thanks, Dana !

Public Sub DeleteRowOnCell()

On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange

End Sub


To use this macro, select a columnar range of cells, and then run the macro. If the cell in that column is blank, the entire row will be deleted. To process the entire column, click the column header to select the entire column.

DeleteDuplicateRows

This macro will delete duplicate rows in a range. To use, select a single-column range of cells, comprising the range of rows from which duplicates are to be deleted, e.g., C2:C99. To determine whether a row has duplicates, the values in the selected column are compared. Entire rows are not compared against one another. Only the selected column is used for comparison. When duplicate values are found in the active column, the first row remains, and all subsequent rows are deleted.

Public Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


In the following examples, suppose we have select cells A1:A10 before running the DeleteDuplicateRows macro:

Before DeleteDuplicateRows 

 

This illustrates the range before running DeleteDuplicateRows. 

 

 



After DeleteDuplicates 

 

This illustrates the range after running DeleteDuplicateRows. 

 

 



Here's another procedure that may be useful. Suppose you have two columns of data -- column A containing some names, and column B containing some dates. If the data is grouped (not necessarily sorted) by column A (but not necessarily by column B), this code will delete the duplicates rows, but retaining the latest entry (by column B) of each name in column A. 

Sub DeleteTheOldies()
Dim RowNdx As Long
For RowNdx = Range("A1").End(xlDown).Row To 2 Step -1
If Cells(RowNdx, "A").Value = Cells(RowNdx - 1, "A").Value Then
If Cells(RowNdx, "B").Value <= Cells(RowNdx - 1, "B").Value Then
Rows(RowNdx).Delete
Else
Rows(RowNdx - 1).Delete
End If
End If
Next RowNdx
End Sub


These examples show some data before and after running the code.

       

Note that only the latest (from column B) row remains for each group of names. Notice, too, that in the "before" data, the names are grouped, but the groups need not be in order.

There is more then one way to skin a cat:
Is skinning cats something you do on a regular basis?

Use the Sort Command...DUH!

1. Highlight the section of the worksheet containing your data, or click the top left corner of your worksheet to highlight the whole sheet.

2. Select Data/Sort/Column A/Ascending and click OK.

3. Your data will be sorted by Column A, and blank rows will have been eliminated. (If you need to re-sort by any other column, you can do that easily from that point.)

Another method; Choose  Edit | Delete | Entire Row,

1. Highlight range or column.  (Note: select the range that has the blank cells; in our original example, you would select column A.)

2. From the main menu, select the Edit | Go To command, then choose the Special button. 

3. Choose the Blanks radio button and select OK.  Blank rows are selected.

4. From the main menu, choose Edit | Delete | Entire Row, then click OK.

Yet Another Simple Method without the need for a macro.

There is also a very simple way to remove blank rows without the need of a macro, but just using the standard functionality of
Excel:

1. Select the entire table with the data and activate the AutoFilter (use the Data/AutoFilter command).

2. In column A, click on the filter dropdown and select "blanks".This will show only the blank rows in the table.  (Note: Well, not entirely -- it only identifies rows that are blank in column A, which are the rows the reader wants to delete.  Checking column A doesn't ensure that the entire row is blank -- just rows in which column A is blank.)

3. Select all the blank rows in the table (these are marked by blue row numbers) and right-click and choose Delete to delete these rows.

4. Set the filter dropdown in column A again to Show All and you're done! All blank rows have disappeared.

This is the quickest and easiest way, without the need for extra macro code. As with most all Microsoft Office Programs, there is many ways to accomplish the same tasks. There must be a whole lot of skinned cats out in the real world. Sorry folks, the truth is out, I'm a Dog person and unashamedly so. 

Some Macros do not have to be overly large do accomplish a simple task Check out this simple Macros for size.

Years ago I used the following short macro to check content for all cells in the row before deleting the row and presumably it still works in current versions of Excel. I have not tested it however.

' DeleteEmptyRows Macro
' Delete Empty Rows recorded 3/7/00
'
'
Sub DeleteEmptyRows()
    LastRow = ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = False
    For r = LastRow To 1 Step -1
      If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
    Next r
End Sub

There is more then one way to skin a cat or to delete rows:

One flaw in the previous macro is; that if data does not begin on row 1, it will not delete the rows above the data area. UsedRange.Rows.Count is not a proxy for the last row, because UsedRange is comprised of the rectangular area from the first used cell to the last used cell, rather than from cell 1 to the last used cell.

Here is a more reliable alternative:

   Sub DeleteEmptyRows()
   With ActiveSheet.UsedRange
       Lastrow = .Cells(.Cells.Count).Row
       Application.ScreenUpdating = False
       For r = Lastrow To 1 Step -1
         If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
      Next r
   End With
   End Sub

Clearing the End Point in Excel

Reader Jerry writes: While you're on the subject of blank rows/columns in Excel, I wonder if thee is a way to completely remove these from a worksheet. When I delete a row, the content disappears but the row still seems to remain active.

For example, a worksheet with 10 rows of data in one column has one of the rows deleted by highlighting the entire row, right clicking and selecting delete. Pressing Ctrl-End should now take you to A9 but it does not, you still end up in A10. The same is true when deleting columns.

You may find this tool useful: the Microsoft Excel Excess Formatting Cleaner Add-in, which you'll find at the Microsoft Site.


Excel: "Paste Values" Shortcut

Many of our readers wanted to know if there is any way to use shortcuts to paste values in Excel. We know that the shortcut commands for the simple operations are: 

Sadly there is no shortcut to do the simple tasks of Ctrl+? To paste Values into a Worksheet. Or a Work Book. However, that doesn't mean you can't create one.

The trick is to record a macro that does a simple Edit/Paste Special command in which you choose the Values option and then press OK. Before you record those keystrokes, however, pick the letter or number you want to use in conjunction with the Ctrl key as your shortcut in the Record Macro dialog box.

Alternatively, create a new macro using the following code, which performs this simple "paste values" operation:

_______________________________________________________________________

Sub paste_values()

Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

_______________________________________________________________________

Assign the macro a shortcut key using the Tools/Macro/Macros command (shortcut: Alt + F8), select the macro, and click the Options button to assign the keyboard shortcut.


Renaming Ranges

Excel offers no simple way to rename a range. Instead you must use a two-step process: copy the range with a new name, then go back and delete the original. Here's the undocumented technique we use:

1. Use the Insert/Name command from the main menu. Choose Define from the submenu (alternatively, press Ctrl + F3).

2. Select the name of the existing range you want to rename.

3. In the "Names in workbook" field, enter the new name and click the Add button.

4. Repeat step 1.

5. Choose the original name of the range and click the Delete button. 

Changing Cell References To Range Names

While it's often easy to set up formulas using cell addresses, Excel also supports using range names within a formula. For example, if you have data in cells A1 through E1, and the formula =SUM(A1:E1) in cell F1, you could just as easily give the cell range A1:E1 a name (such as sales) and rewrite the formula as =SUM(sales).

We won't go into the advantages of range names here (there are many, first among them for us is readability). We do want to explain how to get from cell references to range names without having to do a lot of typing.

Let's assume that you've created the range name sales and entered the formula in F1 using the explicit cell addresses (A1:E1). To change the cell addresses into a range name:

1. Move to the cell(s) containing the formula(s) you want to change. In our example, move to cell F1.

2. Use the Insert/Name command from the main menu, and choose Apply from the submenu.

3. In the Apply names dialog box, select the range names you want to apply. You can choose as many as you wish. In our example, select the range name "sales."

4. Click on OK.

Excel looks through the cell formulas and translates cell ranges into range names whenever it can. The net result in our example is that Excel converts the formula in cell F1 to =SUM(sales).

If Excel can't find a match between a cell range in your original formula and any of the range names selected, it will display a warning message and leave the original formula alone.


Excel Vlookup formula-function explained

Perhaps one of Excels most commonly needed Functions is the VLOOKUP. It is also possibly the function that most people have problems understanding.

The Excel VLOOKUP function is used to look for specified data in the first column of a table of data. Once found it will return a result, on the same row, a specified number of columns from the first column. The syntax for VLOOKUP is:

=vlookup(lookup_value,table_array,col_index_num,range_lookup)

It is used in the following manner:

=VLOOKUP("Dog",$A$1:$E$1000,3,False)

Note the use of False as the optional range_lookup Argument. This tells the VLOOKUP to find an exact match and is most often needed when looking for a text match. If this is omitted, or True, you will often get unwanted results when searching for text that is in an unsorted column of data. This means that when True is used, or the range_lookup Argument is omitted, your data should be sorted (by the first column) in ascending order.

The use of True, or range_lookup Argument is omitted, is most often used when looking at numeric data that resides in the first column of your table of data.

 

A

B

C

D

1

Amount

Name

Age

 

2

$256.95

Bill

56

 

3

$125.63

Joe

22

 

4

$25.66

Mary

59

 

5

$548.00

Dave

21

 

6

$489.32

Frank

48

 

7

$500.25

Sue

19

 

8

$368.59

Hillary

15

 

9

$901.56

Kate

25

 

10

$99.95

Aleisha

33  

If we were to use:

=VLOOKUP(368.59,$A$1:$C$10,2)

on the data to the left, we would get a result of "Mary" and NOT "Hillary" as we have omitted the range_lookup Argument AND our data is not sorted by Amount. This is because VLOOKUP looks in Column A, starting from A1, and as soon as it encounters the amount $548.00 it drops back to the cell above because $548.00 is greater than the lookup_value of 368.59

If we sorted our data by Amount we would get the desired result. We could also get the desired result by using:

=VLOOKUP(368.59,$A$1:$C$10,2,False)

The use of False as the range_lookup Argument forces VLOOKUP to search all values and only stop when it finds an exact match. If an exact match does not exist we get the #N/A! error.

The method of sorting is best as a VLOOKUP that searches in a sorted range is MUCH faster. The effect of this can be significant if the table is large and/or you have many VLOOKUP functions.

How to stop the #N/A! error when using VLOOKUP

One very common question asked by Excel users is "How can I stop VLOOKUP returning #N/A! when it cannot find my data?". There are a few ways this can be done, unfortunately the most popular way is also the least efficient. This is to use the ISNA function as shown below:

=IF(ISNA(VLOOKUP(368.59,$A$1:$C$1000,2,False)),"",VLOOKUP(368.59,$A$1:$C$1000,2,False)

The use of the ISNA function like this will force Excel to perform the VLOOKUP twice if the value does exist, which is most often the case. This can become a problem by slowing down Excel's recalculation time. A slightly better options is:

=IF(COUNTIF($A$1:$A$10000,368.59),VLOOKUP(368.59,$A$1:$C$10000,2),"")

See Lookup Function


Lookup Functions

Excel is very rich in Lookup & Reference Formulae, with the most popular probably being VLOOKUP. These functions are all very generic and can be used to extract data from just about any table of data. The biggest mistake made by most, is the forcing of Excel to look in thousands, if not millions of cells superfluously, see: Formulae. As shown by the Formulae link, one of the best ways to overcome this is via the use of Dynamic Ranges. 

The other mistake is that the lookup functions (in the case of VLOOKUP, HLOOKUP and MATCH) are told to find an exact match. That is, the optional fourth argument Range_lookup is set to False in both VLOOKUP and HLOOKUP. In the case of MATCH the last optional argument (Match_type) is set to 0 (zero). This means that Excel will need to check all cells until it finds an exact match. If possible, always use True (or omitted) for VLOOKUP and HLOOKUP, or 1 (sort ascending) or -1 (sort descending) in the case of Match. So, whenever possible, sort your data appropriately.

Another very bad mistake is the double use of the Lookup Function nested within one of Excels Information functions. see example below

=IF(ISNA(VLOOKUP(100,MyRange,2,False)),"",VLOOKUP(100,MyRange,2,False))

This is used to prevent the #N/A when no match can be found. The big problem with this is, it forces Excel to use the VLOOKUP twice! As you can imagine, this doubles the number of Lookup functions used. The best approach (if possible) is to live with the #N/A, or hide it via Conditional Formatting. Or, if this is not an option, place the Lookup in a 'out-the-way' spot on the same Worksheet (eg IV1) and then use:

IV1=VLOOKUP(100,MyRange,2,False)

Result Cell = IF(ISNA(IV1),"",IV1)

This halves the number of Lookup functions needed!

One other common problem is storing the Lookup Function on another sheet to the Table. While the effect of this is not too bad on approximate matches, it can be dramatic on exact matches. Consider placing the Lookup functions on the same Worksheet as the Table, then create a simply reference (eg =Sheet1!IV1) to the cell(s) to get the result into the needed Worksheet. Doing this also opens up another opportunity in that we could now use:

 IF(ISNA(Sheet1!IV1),"",Sheet1!IV1)

Last, but far from least, learn how to use Excels Database functions. They are very easy to use and are often much faster than their Lookup & Reference counterpart.


Microsoft's Tips For Optimizing Speed

In Microsoft Excel, recalculation performance is affected by the way data and formulas are arranged on the worksheet. The following list contains tips for optimizing your worksheet to improve recalculation speed:

Microsoft Knowledge Base Article - Q72622


Conditional Formatting

In Excel 97, Microsoft introduced a nifty new feature called Conditional Formatting. It can be found via the Worksheet Menu Bar under "Format". It allows us to format a cell, or range of cells, based on a specified criteria for the cell(s), or other cell(s).

A simple example would be to use Conditional Formatting to highlight all cells in any given range that are greater than 100. Let's say this range is A1:A100. We would start by selecting cells A1:A100, starting from cell A1. This will ensure A1 is the active cell in the selection and from this, Excel will know all other cells we specify are relative to cell housing the Conditional Formatting. Now go to Format>Conditional Formatting and then choose "Cell value is", then "Greater than" and then type 100 in the far right.

Now click the "Format" button and choose the desired formatting for all cells greater than 100. After this, click "Ok" then "Ok" again. The same logic can be applied to any other of the criteria we can choose. E.g "Less than", "Equal to" etc

The only drawback with this way, is that cells housing text may be seen as having values greater than 100! We can over-come this by resorting to the "Formula is" option as apposed to "Cell value is". When using the "Formula is" option, any formula we use, MUST evaluate to either True, or False. With this in mind, here is a formula that we can use in place of simply 100.

=AND(ISNUMBER(A1),A1>100)

Again, it is vital the you select starting from A1 as all other formulas, in A2:A100, will change their cell references relatively. That is, A2 Conditional Formatting will read;

=AND(ISNUMBER(A2),A2>100)

If you are not already aware, both conditions in an AND Function must evaluate to True for the Function to return True. In other words, all cells in A1:A100 will need to house a number AND the number must be greater than 100 for the chosen formatting to be applied.

TIP! If you need to reference another Worksheet when using Formula is you can do so in 2 ways.

  1. Name the range, then use the name apposed to the range address.

  2. Enclose the range is the INDIRECT function. E.g INDIRECT("'Sheet2'!A1:A100")