Excel - Boost Your Excel Calculations with Helper Cells - Episode 1014
1,676 views · Published 14 May 2009 · 4:19 · Indexed 31 October 2026
Channel: MrExcel.com · 2009 · Education
Microsoft Excel Tutorial: Boost Your Excel Calculations with Helper Cells. Welcome back to the MrExcel netcast! I'm Bill Jelen and today we're diving into the world of helper cells. We all know that Excel can handle massive amounts of data, but sometimes we need to find ways to make our analysis more efficient. That's where helper cells come in. In this episode, we'll explore how using helper cells can drastically improve the speed of your calculations. But first, I want to remind you that the spring seminar season is almost over. I'll be at the national IMA conference in Denver, so if you're attending, be sure to say hi! As I continue to travel and do seminars, I won't be able to answer Excel questions every week on the podcast. So, if you have a question you'd like to see featured, please send it my way. You can email me at [email protected] or leave a voicemail at 866-581-0221. Don't forget to introduce yourself and where you're from! Now, let's get back to helper cells. In this episode, we'll be looking at an example where we have 11,000 rows of data and need to calculate revenue as a percentage of total revenue for each product. Our initial formula uses a SUMIF function, but upon closer inspection, we realize that this means the formula will have to evaluate 11,823 other cells for each row. That's a lot of calculations! So, we introduce helper cells to make the process more efficient. By moving the time-intensive calculation to three helper cells, we are able to drastically reduce the calculation time from 31 seconds to less than 0.02 seconds. That's a 1,550x improvement! This is because the VLOOKUP function we use in the formula is much faster when referencing a smaller table. So, by thinking about what can be moved to a helper cell and referenced in the formula, we can greatly improve the speed of our calculations. If you found this tip helpful, be sure to check out episode 1011 from Monday and the accompanying white paper for more examples of how helper cells can improve your Excel efficiency. And as always, thank you for tuning in to the MrExcel netcast. If you have any questions or suggestions for future episodes, don't hesitate to reach out. See you next time! 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/ In our second look at Formula Recalc, a 750x improvement in formula recalc times by using some helper cells. Episode 1014 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book! Table of Contents: (00:00) Introduction to MrExcel netcast (00:14) Upcoming seminar season (00:28) Request for questions for future podcasts (00:44) How to submit questions (01:00) Introduction to formula recount times (01:13) Example of a formula with a large number of references (01:51) Introduction to helper cells (02:11) Comparison of calculation times with and without helper cells (03:00) Conclusion and recommendation for using helper cells (03:42) Additional resources for improving calculation speed (03:52) 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 This video answers these common search terms: Calculation speed Excel questions Formula optimization Helper cells Pivot table analysis Podcast questions Recount speed Secondary cell references SUMIF function VLOOKUP White paper on Excel optimization Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152411/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:37
Excel - Enhance Your Excel Charts: Add a Picture as a Background in Excel - Episode 917
-
2:00
Excel Hiding Data in Plain Site - Episode 919