Excel - Too many INDIRECT slows down calc - Episode 1824

1,461 views · Published 7 November 2013 · 10:06 · Indexed 14 November 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: Speeding Up Excel Calculations: Too Many INDIRECT Functions | MrExcel Podcast.

Welcome to another episode of the MrExcel podcast. In this episode, we will be discussing the topic of using too many INDIRECT functions in Excel and how it can significantly slow down your calculations. This is a timely topic as we have recently explored the benefits of using INDIRECT in episodes 1822 and 1823, but in those examples, we were only dealing with a small number of cells. Today, we will be looking at a spreadsheet sent in by Abhijeet, which contains a large database of 5600 rows by 80 columns.

Abhijeet has cleverly used named ranges to make his spreadsheet more organized and easier to navigate. However, he has also used a lot of INDIRECT functions in his formulas, which has caused a major slowdown in his calculations. In fact, it took 1.53384 seconds for Excel to finish calculating the spreadsheet, which is a significant amount of time if you are dealing with a lot of data entry. Abhijeet is also planning to add more features to his spreadsheet, which will only make the problem worse.

So, what is the solution to this problem? Well, in this episode, we will explore two methods to speed up the calculations in a spreadsheet with a large number of INDIRECT functions. The first method involves using a formula to build the formula, which may seem like a lot of work, but it can significantly improve the calculation time. The second method involves using VBA code to replace the INDIRECT functions with their corresponding values. Both methods have their pros and cons, but either one is better than manually writing 1200 formulas across the spreadsheet.

In the end, we will see that using VBA code is the most efficient way to deal with a large number of INDIRECT functions. With just a few lines of code, we can significantly reduce the calculation time from 1.53384 seconds to just 0.0024 seconds. This is a tremendous speed improvement and will make a huge difference if you are dealing with a lot of data in your spreadsheet. So, thank you to Abhijeet for bringing up this issue and thank you for tuning in to another episode of the MrExcel podcast. We hope this episode has been helpful and we will see you next time for more Excel tips and tricks.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Too many INDIRECTs
(00:17) Explanation of the issue with using too many INDIRECTs
(00:28) Introduction to the spreadsheet example
(00:38) Explanation of the named ranges used in the spreadsheet
(01:06) Description of the formulas used in the spreadsheet
(02:05) Demonstration of the slow calculation time
(03:15) Introduction to the solution using a formula method
(05:15) Demonstration of the improved calculation time using the formula method
(06:12) Introduction to the solution using VBA
(09:44) Demonstration of the improved calculation time using VBA
(09:56) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


Abhijeet took INDIRECT to the extreme - 1200 formulas, each looking at 5600 records and the recalc time is taking too long. Two ways to build those formulas without INDIRECT.

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1153646/

More from this channel