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.



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.



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)


Display Text And A Value In One Cell

Category: Formulas | [Item URL]

Did you know that you could combine text and values in a single cell?

For example, assume cell A12 contains the value 1435. Enter the following formula into another cell:

="Total: "&A12 

The formula cell will display: "Total: 1435."

The ampersand is a concatenation operator that joins the text with the contents of cell A12.

Applying a number format to the cell containing the formula has no effect, because the cell contains text, not a value. As a work-around, modify the formula to use the TEXT function (the second argument for the TEXT function consists of a standard Excel number-format string).

="Total: "&TEXT(A12,"$#,##0.00")

This formula will display "Total: $1,435.00."

Here's another example formula that uses the NOW function to display some text along with the current date and time:

="Report printed on "&TEXT(NOW(),"mmmm d, yyyy at h:mm AM/PM")


Automatic List Numbering

Category: Formulas | [Item URL]

It's fairly easy to create a formula that generates consecutively number items in nonconsecutive cells. Refer to the figure below.

Column A consists of formulas that refer to column B. The formula in cell A1 is:

 =IF(B1<>"",COUNTA($B$1:B1)&".","")

This formula, which is copied down to the other cells in column A, displays the next consecutive item number if the corresponding cell in column B is not empty. If the cell in column B is empty, the formula displays nothing.

As items are added or deleted from column B, the numbering updates automatically.


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

Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2017, J-Walk & Associates, Inc.
Privacy Policy