How to Add Multiple Range References to Formulas in Excel

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.

Hold Ctrl Key Selecting Multiple Cells for Formula References and Commas

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.

Ctrl + Select Technique Use in any RefEdit Control to Reference Ranges in Excel

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.

Hold Ctrl Key Selecting Multiple Cells Applies Absolute Relative Reference State

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.

SUMIFS Formula with Mixed Absolute and Relative References

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! 🙂

5 comments

Your email address will not be published. Required fields are marked *

  • 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

  • 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 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. 🙂

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly

Macros and VBA Training Webinar