Abstract
Transcript
Hey, Dave here from Exceljet.
We’ll look at how to use named ranges to make your formulas easier to read.
Okay so let’s go do the training.
Notice that you don’t need to enter a closing parentheses.
you’re able to also use a feature called the INSERT function.
you could also ask Excel to insertfunction argumentsfor you, using the shortcut Control-Shift-A.
Then you’re able to opt for arguments, and replace them with the values you need.
First, you might select all the cells, enter the formula, and then use Control-Enter.
Or, you might enter the formula and double-poke the fill handle.
you could quickly see all the formulas in a worksheet using the keyboard shortcut Control + Grave.
you’re free to use this shortcut to toggle all formulas on, and all formulas off.
you could also useGo To Specialand Formulas.
This will select every formula in the worksheet.
When you want to copy formulas that contain relative references, you could temporarily convert them to text.
I’ll search for the equals sign and replace it with a hash character.
Then I’ll copy and paste the formulas.
Now all formula references are unchanged.
You’ll often want to convert formulas to values.
A simple way to do this is to usePaste Specialwith the Values option.
go for the formulas, copy, bring up Paste Special, and choose Values.
Then you’re free to delete columns that you no longer need.
you could also use Paste Special to convert values in place without any formulas at all.
In this case, I’ll move all of these dates forward by 14 days, or 2 weeks.
Just copy the value, bring up Paste Special, choose Values, and a math operation.
In this case, we want to add.
This time, I want to choose values and the multiply operation.
you might use named ranges to make your formulas easier to read and understand.
In this case, I’ll name the cell that contains the hourly rate and then update the formulas.
To use F9, work from the inside out.
Here we’ll use F9 to evaluate TODAY().
Each time you click evaluate, Excel will solve one step of the formula working from the inside out.
Okay, so that’s 20 tips to help you work a little faster with Excel formulas.
Give these tips a try and save a little bit of time in Excel today.
