Excel: Calculate One Range

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

  1. 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.