Hello, time for more #formulafriday fun with Excel Formulas. Do you ever need to update multiple Excel formulas in Excel? If so, you’ll be happy to know that there is an easy way to do this. In this blog post, we will show you how to edit multiple formulas in Excel. Sometimes, you just need a faster way to do stuff in Excel, especially when it comes to creating and updating formulas. So, here is a way to update multiple formulas in Excel really fast using a method you have probably used before, but maybe not for updating formulas.
Update Multiple Excel Formulas.
I will share with the process I used on my own Excel workbooks. My Excel workbook calculated the value of sales generated per sales person. These sale are then ranked each month over a period of 3 individual months.
Example Formula.
I have my formula set up to calculate the descending rank (the highest value sales are awarded the highest rank – or lowest value!) for the month of June 2016 as below.
=RANK(C3,$C$3:$C$11)
If I want to now show the ranking of July 2016 I do not need to re write my formulas, I can simply use Find and Replace method. Yes, the simple and often used feature in Excel.
Using Find And Replace To Update The Formulas.
- Select the cells you want to use Find and Replace in – in my example it is Column H
- Hit CTRL+H to bring up the Find & Replace Dialog Box
- On the Find Tab, we can type C
- Hit the Options Tab
- Select Within Sheet – By Columns – Look In – Formulas
- Select the Replace Tab – Type D
- Hit Replace All
Note- Any cells that ou have highighted that contain C will be updated.
- Fially, job done – all of my cells have been updated.
That was easy wasn’t it?. I can then again just update to show August 2016 ranking by using Find & Replace to find D and replace with E.
Have you ever had to edit a lot of Excel formulas at once? Maybe you copied and pasted them from one sheet to another, or maybe you just had a lot of cells that needed the same formatting change. In any case, if you’ve ever done this kind of bulk editing, then you know it can be a pain. Especially if you have to do it over and over again. I hope you found this method quick and easy.
More Excel Formula Tips
Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.
Why not bookmark my Formula Friday and Macro Monday pages. I update them every week with new Excel tutorials.
