MrExcel.com - Your One Stop for Excel Tips & Solutions

Calculate One Range

January 26, 2022 - by

Calculate One Range

Problem: I have a workbook that takes 3 minutes to calculate. I changed to manual calculation mode so I can enter data without waiting for a recalculation after each data entry. I just changed 10 cells and I only want to recalculate the 10 rows that rely on those cells. I don’t want to wait 3 minutes when there are only 10 cells that need recalculated.

Strategy: You can press F2 and Enter on each cell that has to be calculated. This will force a single cell to calculate. For 10 cells, try this trick:

  • 1. Select the cells that should be calculated. This must include more than one cell, or Excel will calculate the entire worksheet.

  • 2. Ctrl+H to display the Find and Replace dialog.

  • 3. Type an equals sign in the Find What box.

  • 4. Type an equals sign in the Replace With box.

  • 5. Click the Options >> button. Make sure that Look In is set to Formulas and that Match Entire Cell Contents is not checked.

  • 6. Click Replace All.

The Find and Replace dialog, changing all equals signs to equals signs. Click Replace All.
Figure 295. Replace an equals with an equals.

Result: Only the selected range will be calculated.

Additional Details: After you have done the six steps above, you can quickly recalculate the selected range by using Ctrl+H, Alt+A. This will re-open the Find and Replace dialog, then do a Replace All with the previous settings.

Tip: If calculation bottlenecks are causing problems, you need to check out the amazing Fast Excel V3 utility from Charles Williams. Details are at http://tinyurl.com/fastexcel.

This article is an excerpt from Power Excel With MrExcel

Title photo by Vincent Ledvina on Unsplash