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.
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.
When I try to combine a cell that contains text with one that contains a date, the result is nonsense. The cell that holds the date is correctly formatted, but the resulting text says something like "Today is 36232" instead of displaying a date.
A1:
As you've seen, Excel ignores the formatting of the original cell when concatenating the two values and instead displays the serial date value. Before concatenating a date with text, you must convert the date to text and choose a format. Use the TEXT function followed by a format in quotation marks. If the date is in cell A15, for example, use this formula to get the result you're looking for: ="Today is "&TEXT(A15,"mmmm d, yyyy").
