• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint

Troubleshooting

Editing an Array Formula

Q1:I entered an array formula, but when I try to edit or copy it, the results change or I get an error message.
A1: Editing an array formula is tricky. If the array formula was entered across multiple cells, you must select every cell that contains the array before you can edit it. If the array formula is contained in a single cell, you can edit it just as you would a conventional formula, but you must remember to press Ctrl+Shift+Enter to store your changes as an array formula. If you forget and press Enter, Excel stores it as a standard formula, with the wrong results. Finally, you'll notice some restrictions when you try to copy an array formula. If the destination range you select also contains the array formula, you'll get an error message; select a new destination range, or use AutoFill to copy the formula. Oh, and don't try to cheat by adding your own curly braces to create an array formula—the only way to enter an array formula is to press Ctrl+Shift+Enter and let Excel add the curly braces.

Check Formulas Before Deleting Range Names

Q1:After I deleted a range name in my worksheet, some of my formulas displayed error messages.
A1: It's a frustrating fact of life: When you delete a range name from a worksheet, Excel does not automatically adjust any formulas that contain that range name. Even though it should, logically, be able to substitute the old cell address for the range name, it leaves the name there to torture you. After deleting a range name, you will see a #NAME? error in any cell that contains a formula with a reference to the deleted range name. Unfortunately, there's no easy way to determine which cell goes with the defunct name. If you spot these errors immediately after deleting the range name, press Ctrl+Z to undo your change. If you remember this possibility before deleting a range name, you can easily change any cells before deleting or changing the defined name. Press Ctrl+F to open the Find dialog box, enter the name of the cell or range, choose Formulas from the Look In box, and click Find Next to jump to and edit each cell that contains that name.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint