Today we will learn about two shortcuts that will change all the formulas in an excel sheet. This trick will help you save time and increase your productivity while working.
For detail explanation, watch this video
Suppose you are given following tables in an excel sheet and your boss asks you to calculate the sum. You will easily calculate it using SUM formula and display answers at the bottom.
But what if, at the last moment, your boss asks you to show AVERAGE values instead of SUM values. Changing all the formulas would take a lot of time.
This is where shortcut CTRL Tilde & CTRL H comes handy.
First of all you have to press CTRL Tilde and you will see all the formulas in the sheet have become visible.
Then you have to press CTRL H
In the "Find and Replace" window that pops up, click on Options
Then select "Formulas" in the Look in option
Type the function you want to change in "Find what", which in this case is SUM
Type the function you wish to obtain in the "Replace with" option, which in this case is AVERAGE
Then click on "Replace All"
You will see that all the SUM formulas are changed to AVERAGE formulas
Now press CTRL Tilde again to hide the formulas
The work which could have taken a lot of time is done quickly using two simple formulas
Check out this video for more clarity