Making An Exact Copy Of A Range Of Formulas

Category: General / Formulas / General VBA | [Item URL]

Assume that A1:D10 on Sheet1 has a range of cells that contain formulas. Furthermore, assume that you want to make an exact copy of these formulas, beginning in cell A11 on Sheet1.  By "exact," I mean a perfect replica -- the original cell references should not change.

If the formulas contain only absolute cell references, it's a piece of cake. Just use the standard copy/paste commands. But if the formulas contain relative or mixed references, the standard copy/paste technique won't work because the relative and mixed references will be adjusted when the range is pasted.

If you're a VBA programmer, you can simply execute the following code:

With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With

Following are step-by-step instructions to accomplish this task without using VBA (contributed by Bob Umlas):

1. Select the source range (A1:D10 in this example).
2. Group the source sheet with another empty sheet (say Sheet2). To do this, press Ctrl while you click the sheet tab for Sheet2
3. Select Edit - Fill - Across worksheets (choose the All option in the dialog box).
4. Ungroup the sheets (click the sheet tab for Sheet2)
5. In Sheet2, the copied range will be selected. Choose Edit - Cut.
6. Activate cell A11 (in Sheet2) and press Enter to paste the cut cells. A11.D20 will be selected.
7. Re-group the sheets. Press Ctl and click the sheet tab for Sheet1
8. Once again, use Edit - Fill - Across worksheets.
9. Activate Sheet1, and you'll find that A11:D20 contains an exact replica of the formulas in A1:D10.

Note: For another method of performing this task, see Making An Exact Copy Of A Range Of Formulas, Take 2.

Comparing Two Lists With Conditional Formatting

Category: Formatting / Formulas | [Item URL]

Excel's Conditional Formatting feature has many uses. Suppose you need to compare two lists, and identify the items that are different. The figure below shows an example. These lists happen to contain text, but this technique also works with numeric data.

The first list is in A2:B19, and this range is named OldList. The second list is in D2:E19, and the range is named NewList. The ranges were named using the Insert - Name - Define command. Naming the ranges is not necessary, but it makes them easier to work with.

As you can see, items in OldList that do not appear in NewList are highlighted with a yellow background. Items in NewList that do not appear in OldList are highlighted with a green background. These colors are the result of Conditional Formatting.

How to do it

1. Start by selecting the OldList range.
2. Choose Format - Conditional Formatting
3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
4. Enter this formula:

   =COUNTIF(NewList,A2)=0

5. Click the Format button and specify the formatting to apply when the condition is true (a yellow background in this example).
6. Click OK

The cells in the NewList range will use a similar conditional formatting formula.

1. Select the NewList range.
2. Choose Format - Conditional Formatting
3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
4. Enter this formula:

   =COUNTIF(OldList,D2)=0

5. Click the Format button and specify the formatting to apply when the condition is true (a green background in this example).
6. Click OK

Both of these conditional formatting formulas use the COUNTIF function. This function counts the number of times a particular value appears in a range. If the formula returns 0, it means that the item does not appear in the range. Therefore, the conditional formatting kicks in and the cell's background color is changed.

The cell reference in the COUNTIF function should always be the upper left cell of the selected range.

Locate Phantom Links In A Workbook

Category: Formulas | [Item URL]

Q. Whenever I open a particular Excel workbook, I get a message asking if I want to update the links. I've examined every formula in the workbook, and I am absolutely certain that the workbook contains no links to any other file. What can I do to convince Excel that the workbook has no links?

You've encountered the infamous "phantom link" phenomenon. I've never known Excel to be wrong about identifying links, so there's an excellent chance that your workbook does contain one or more links -- but they are probably not formula links.

Follow these steps to identify and eradicate any links in a workbook.

1. Select Edit, Links. In many cases, this command may not be available. If it is available, the Links dialog box will tell you the name of the source file for the link. Click the Change Source button and change the link so it refers to the active file.
2. Select Insert, Name, Define. Scroll through the list of names in the Define Name dialog box and examine the Refers to box (see the figure below). If a name refers to another workbook or contains an erroneous reference such as #REF!, delete the name. This is, by far, the most common cause of phantom links
3. If you have a chart in your workbook, click on each data series in the chart and examine the SERIES formula displayed in the formula bar. If the SERIES formula refers to another workbook, you've identified your link. To eliminate the link move or copy the chart's data into the current workbook and recreate your chart.
4. If your workbook contains any custom dialog sheets, select each object in each dialog sheet and examine the formula bar. If any object contains a reference to another workbook, edit or delete the reference.

Next, save your workbook and then re-open it. It should open up without asking you to update the links.

Dealing With Negative Time Values

Category: Formulas | [Item URL]

Because Excel stores dates and times as numeric values, it's possible to add or subtract one from the other.

However, if you have a workbook containing only times (no dates), you may have discovered that subtracting one time from another doesn't always work. Negative time values appear as a series of hash marks (########), even though you've assigned the [h]:mm format to the cells.

By default, Excel uses a date system that begins with January 1, 1900. A negative time value generates a date/time combination that falls before this date, which is invalid.

The solution is to use the optional 1904 date system. Select Tools, Options, click the Calculation tab, and check the 1904 date system box to change the starting date to January 2, 1904. Your negative times will now be displayed correctly, as shown below.

Be careful if you workbook contains links to other files that don't use the 1904 date system. In such a case, the mismatch of date systems could cause erroneous results.

Converting Non-numbers To Actual Values

Category: Formulas | [Item URL]

Q. I often import data into Excel from various applications, including Access. I've found that values are sometimes imported as text, which means I can't use them in calculations or with commands that require values. I've tried formatting the cells as values, with no success. The only way I've found to convert the text into values is to edit the cell and then press Enter. Is there an easier way to make these conversions?

This is a common problem in Excel. The good news is the Excel 2002 is able to identify such cells and you can easily correct them If you're using an older version of Excel, you can use this method:

1. Select any empty cell
2. Enter the value 1 into that cell
3. Choose Edit, Copy
4. Select all the cells that need to be converted
5. Choose Edit, Paste Special
6. In the Paste Special dialog box, select the Multiply option, then click OK.

This operation multiplies each cell by 1, and in the process converts the cell's contents to a value.

Compare Ranges By Using An Array Formula

Category: Formulas | [Item URL]

In Excel, you can compare the cells in two ranges with an array formula. For instance, to see if all of the values in A1:A100 are identical to those in B1:B100, type this array formula:

=SUM(IF(A1:A100=B1:B100,0,1)) 

Note: This is an array formula and it must be entered using Ctrl-Shift-Enter.

The formula will return the number of corresponding cells that are different. If the formula returns 0, it means that the two ranges are identical.

Calculate The Number Of Days In A Month

Category: Formulas | [Item URL]

Excel lacks a function for calculating the number of days in a particular month, so you'll need to construct your own formula.

If cell A1 contains a date, this formula will return the number of days in the month:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)

Identify Formulas By Using Conditional Formatting

Category: Formulas | [Item URL]

How many times have you accidentally deleted or overwritten cells containing formulas only to discover the mistake after it's too late? One solution is to write-protect important cells. Another approach is to give those cells a visual flag.

This clever technique was submitted by David Hager. It uses Conditional Formatting (available in Excel 97 or later) to apply special formatting to cells that contain formulas--something that's not normally possible. With this technique you can set up your worksheet so that all formula cells get a yellow background, for example, or so that negative values are in boldface.

Follow these steps:

1. Select Insert, Name, Define.
2. In the Define Name dialog box, enter the following in the 'Names in workbook' box

   CellHasFormula 

3. Then enter the following formula in the "Refers to" box
   

   =GET.CELL(48,INDIRECT("rc",FALSE))

4. Click Add, and then OK.
5. Select all the cells to which you want to apply the conditional formatting.
6. Select Format, Conditional Formatting
7. In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and then enter this formula in the adjacent box (see the figure below):
   

   =CellHasFormula

8. Click the Format button and select the type of formatting you want for the cells that contain a formula.
9. Click OK.

After you've completed these steps, every cell that contains a formula and is within the range you selected in Step 4 will display the formatting of your choice.

How does it work? The key component is creating a named formula in Steps 2 and 3. This formula, unlike standard formulas, doesn't reside in a cell, but it still acts like a formula by returning a value -- in this case either 'True' or 'False'. The formula uses the GET.CELL function, which is part of the XLM macro language (VBA's predecessor) and cannot be used directly in a worksheet. Using a value of 48 as the first argument for GET.CELL causes the function to return 'True' if the cell contains a formula. The INDIRECT function essentially creates a reference to each cell in the selected range.

Displaying Autofilter Criteria

Category: Formulas | [Item URL]

Excel's AutoFilter feature definitely ranks right up there when it comes to handy tools. This feature, which you access with the Data, Filter, AutoFilter command, works with a range of cells set up as a database or list. When AutoFiltering is turned on, the row headers display drop-down arrows that let you specify criteria (such as "Age greater than 30"). Rows that don't match your criteria are hidden, but they are redisplayed when you turn off AutoFiltering.

One problem with AutoFiltering is that you can't tell which criteria are in effect. Stephen Bullen developed a custom VBA worksheet function that displays the current AutoFilter criteria in a cell. The instructions that follow are for Excel 97 or later.

Press Alt+F11 and insert a new module for the active workbook. Then enter the VBA code for the FilterCriteria shown below.

Function FilterCriteria(Rng As Range) As String
    'By Stephen Bullen
    Dim Filter As String
    Filter = ""
    On Error GoTo Finish
    With Rng.Parent.AutoFilter
        If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
        With .Filters(Rng.Column - .Range.Column + 1)
            If Not .On Then GoTo Finish
            Filter = .Criteria1
            Select Case .Operator
                Case xlAnd
                    Filter = Filter & " AND " & .Criteria2
                Case xlOr
                    Filter = Filter & " OR " & .Criteria2
            End Select
        End With
    End With
Finish:
    FilterCriteria = Filter
End Function

After you've entered the VBA code, you can use the function in your formulas. The single-cell argument for the FilterCriteria function can refer to any cell within the column of interest. The formula will return the current AutoFilter criteria (if any) for the specified column. When you turn AutoFiltering off, the formulas don't display anything.

The figure below shows the FilterCriteria in action. The function is used in the cells in row 1. For example, cell A1 contains this formula:

=FilterCriteria(A3) 

As you can see, the list is currently filtered to show rows in which column A contains January, column C contains a code of A or B, and column D contains a value greater than 125 (column B is not filtered, so the formula in cell B1 displays nothing). The rows that don't match these criteria are hidden.

Calculating A Conditional Average

Category: Formulas | [Item URL]

In the real world, a simple average often isn't adequate for your needs.

For example, an instructor might calculate student grades by averaging a series of test scores but omitting the two lowest scores. Or you might want to compute an average that ignores both the highest and lowest values.

In cases such as these, the AVERAGE function won't do, so you must create a more complex formula. The following Excel formula computes the average of the values contained in a range named "scores," but excludes the highest and lowest values:

=(SUM(scores)-MIN(scores)-MAX(scores))/(COUNT(scores)-2)

Here's an example that calculates an average excluding the two lowest scores:

=(SUM(scores)-MIN(scores)-SMALL(scores,2))/(COUNT(scores)-2)

Page 2 of 5 pages
[Previous page]   [Next page]