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).
- 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:
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.
Let me know in the comments where you get the best use out of name ranges!
Hi Brad –
I was not aware of this, so I played with it a little while. This works really well when using a ‘data range’ table – though is not needed when using a ‘Table’ (Excel 2007, 2010 or 2013). The ‘Table’ has this function built-in – although it is a SUBTOTAL funtion and not a SUM function.
Cheers!
Hey Joe,
You are absolutely right you don’t need to do this if you are using an excel table for your data. Often times when you pull data from business intelligence or reporting software you can automatically set up a database connection which removes your Excel table every time you refresh. This is where I find using the range and this shortcut especially useful in the newer versions of Excel.
Of course if you are connecting to a Microsoft Access database then Excel automatically creates an Excel table and then it’s nice because your field names can be used as ranges. The nice thing is you can use your table headers and field names to build any formula on any other sheet. For example if you were doing a lookup to find a value in your table, you could do =INDEX(ON_HAND_QTY,MATCH(A2,ITEM_NUMBER,0)) where our ranges in this formula are our field names/column headers.
Great point though Joe. I should have included something in this post about that but you covered the bases for me so thanks!
Cheers