Build a Quick Set of Name Ranges for your Fields Using This Excel Shortcut

Let’s keep this short and sweet and under 60 seconds.

 

  • Type Ctrl+A to highlight your range of cells/table.
  • Once all cells including the column headers are highlighted, type Ctrl+Shift+F3.  This will bring up the following text box.  Because typically business intelligence software dumps the data into column format, you are going to want to select “Top Row” here which is where your field names are located (if they were on the left, bottom or right, you would select the option which you would want to name the range for).

Ctrl_Shift_F3_Pop_Up

 

  • Once you hit “OK”, you’re set.  All of your name ranges have been created based on your column headers.  You can see this through the navigation drop down bar:

 

Name_Ranges_Created

Why Is this Useful?

 

You can do a ton with name ranges but one really exciting feature is that you can use them as ranges in your formulas (note that using offset formula on your cells can allow you to make your ranges dynamic).  Just as an example, you can get a sum of your total quantity on hand by just using the QTY_ON_HAND name range.

 

Why Is This Useful

Let me know in the comments where you get the best use out of name ranges!

signatureB