Hello, time for more #formulafriday fun with Excel Formulas.
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.
I will share with you how I used this method last week update one of my own Excel workbooks. My Excel workbook calculated the value of sales generated per sales person, that were then ranked each month over a period of 3 individual months.
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
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.
- 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.
- 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.