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.
Conclusion
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! 🙂
Can i change “,” to “+” when holding ctrl
Hi Shirantha,
I don’t know of a way to do that. If you are adding cells together then you can use the SUM function and separate the cells/range references by commas, like I show in the example images above.
I hope that helps. 🙂
Simple and useful!
Good Morning.. Thanks for your Tips.. Always so well explained and I have prooved they work, I like them so much I have created Directory “Tool-Box” for them.. Thanks Again.. my Congratulations..
Hi Jon,
I am enjoying your youtube broadcast. I wonder can you mail merge images to email for a Barcode ID Card. I need to identify 60,000 people and I need a process to use. I have contact Tec it.com for barcode software and have input it in a excel spreadsheet. I am using Word to mail merge the person’s name, Id number, and barcode number which is the same as Id number(barcoded). I know that Word does not insert images without using (INCLUDEPICTURE) command.
The problem I am now having is to email each of the 60,000 ids to my individual constituents. Outlook for some reason (problem maybe with WORD) will not put the id card(picture) in the body of the email for each of the emails. What suggestions do you have to help me? I think the macro approach maybe helpful here, which is why I enjoy your webinar. Let me know your thoughts. Thanx