Bottom line: Learn a quick shortcut for adding multiple cell or range references to a formula.
Skill level: Beginner
Save Time Referencing Ranges with the Ctrl key
When writing formulas we sometimes need to create references to multiple cells or ranges. One quick way to do this is by holding the Ctrl key and then selecting the cells or ranges.
Excel will automatically add the commas between the range references in the formula.
This is great for functions like SUM, COUNTIFS, SUMIFS, VLOOKUP, or any function that has arguments for multiple arrays (ranges).
This technique can also be used in any of the tools that use a RefEdit control to reference a range. I'm referring to features like the Name Manager, Conditional Formatting, Data Validation window, etc.
Remembers the Absolute or Relative Reference State
Excel will also apply the reference state to the additional references you add using the Ctrl key.
For example, if you hit F4 after selecting the first cell to make the reference absolute, all the other references you create with the Ctrl key will also be absolute references.
This can be useful when you want all the range references to have the same absolute/relative state.
It's not as useful if you are writing something like a SUMIFS formula and you want each argument to be a different state.
In this case you can still use the Ctrl key to select all the range references. You will just need to go back and manually apply absolute/relative references to the different ranges.
If you have complex SUMIFS or COUNTIFS formulas, checkout my free SUMIFS Formula Analyzer add-in. It applies the filter criteria to multiple columns in the source range, to make it easier to tie out numbers.
I hope that quick tip saves you some time when writing formulas or creating range references. I also have a recent post on 5 Tips for Writing Formulas that contains more tips and shortcuts.
Please leave a comment below if you have any questions or additional time saving tips. Thank you! 🙂