Look Who it Was Random video

Excel - Combine Formulas in Excel: Quick & Efficient Method for Building a MegaFormula - Episode 525

353 views · Published 21 August 2009 · 3:20 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Combine Formulas in Excel: Quick & Efficient Method for Building a MegaFormula.

Welcome back to the MrExcel netcast where we share tips and tricks to help you become an Excel master. I'm Bill Jelen and today we have a comment from Gary Whiteford who must be catching up on old podcasts because he just viewed episode number 387 and shared a better way to solve the problem we discussed. That was from last November, about 150 episodes ago. So, if you don't remember, I was talking about how to build a MegaFormula and in yesterday's podcast, I had to use three columns to solve the problem. But what if we could do it in just one column? Well, Gary's tip involves combining all those formulas into one column and I'm excited to share it with you.

My tip for combining the formulas involves these simple steps. First, we need to go back to our N formula which refers to cell C1. So, I'll go back to cell C1, hit the F2 key to put it in edit mode, and then copy everything except for the equal sign. Next, we'll use Ctrl+C to copy and escape to get out of edit mode. Then, we'll go back to where we have C1 in the original formula and use Ctrl+V to paste. This process can get tedious, but Gary has a better way to do it.

In this case, we have several instances of cell B1 in the formula. So, let's go to B1 and use the exact same method we used before. We'll hit F2 to go into edit mode, highlight all the characters except for the equal sign, and copy. But this time, we'll select a couple of cells and use the shortcut Ctrl+H to open the Find and Replace dialog box. We'll find every occurrence of B1 and replace it with the value we just copied. And with a simple click of "Replace All", Excel will quickly rewrite our formula, replacing all instances of B1 with the value we copied. This method can also be used for other terms in the formula, making it a quick and efficient way to create a MegaFormula.

I want to thank Gary for sharing this great tip with us. As a token of our appreciation, we'll be sending him an Excel master pin. If you have a tip or a question for the podcast, please feel free to drop us an email or leave a voicemail and we'll feature it on a future episode. Thanks for stopping by and we'll see you next time for another netcast from MrExcel. Don't forget to like and subscribe 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) Introduction
(00:20) Gary's suggestion for a better way to solve a problem
(00:31) Using three columns vs. one column
(00:46) Steps for combining formulas into one column
(01:05) Gary's suggestion for a faster method
(01:41) Excel rewriting the formula
(01:51) Using the method for other terms in the formula
(02:01) 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:
Copying and pasting formulas in Excel
Creating a MegaFormula in one column in Excel
Editing formulas in Excel using F2 key
Excel formula editing tips and tricks
Excel master pin giveaway for helpful tips and questions
Fast way to use edit replace in Excel (Ctrl+H)
How to build a MegaFormula in Excel
Rewriting formulas in Excel using edit replace
Tips for efficient formula editing in Excel
Using Ctrl+C and Ctrl+V to copy and paste in Excel
Using edit replace in Excel to replace cell references
YouTube video tutorial on combining formulas in Excel


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


This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

More from this channel