create account

Step by Step - Named Ranges in Excel by theexcelclub

View this thread on steemit.com
· @theexcelclub ·
$78.58
Step by Step - Named Ranges in Excel
<p><center>Before we really get stuck into this article please go ahead and download the workbooks so that you can practice along step by step. There is no email or sign up to download the files.  This training is free for all and the best way to learn Excel is to practice along.  </center></p><center>Wanna learn more from us, there is a newsletter sign up at the end of the post and we are the only Excel blog in the world where you can earn while you learn.</center></p>
<a href="https://www.dropbox.com/sh/wupp6vfogpchfkh/AABgue2T4-sx_oR_GioqC-JVa?dl=0" target="_blank" role="button" rel="noopener noreferrer">
Download worbooks
</a>
<h2><a name="_Toc365287756"></a>Understanding Named Ranges in Excel </h2><p>To begin, open workbook 1-1 using Microsoft Excel.</p><p>Named ranges in Excel are labels that you can assign to individual cells or cell ranges. This allows you to use range names anywhere you would use a cell or cell range reference. For example, you can define the cell range C1:C45 as “Employees.” Now, whenever you need to enter that cell range, you don’t have to remember the exact cell range. You just need to type the name that you used to define it. In addition, range names use absolute cell references. This means if you copy a formula or use AutoFill when working with named ranges, the formula will maintain its original cell reference.</p><p>Named ranges are most useful when working with formulas as they make them much more readable and improve their overall organization. In the sample workbook, cells A2 and B2 have each been given a name (Sales and Expenses, respectively). Click on cell C2. You can see the formula for that cell displayed in the formula bar:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr1-300x82.png" alt="" width="300" height="82" /><br/></p><p> </p><p>Rather than cell references being used in the formula, there are named ranges. To someone working on this workbook for the first time, this formula is much more self-explanatory than “=A2–B2” would be.</p><p>Perhaps what is most notable about named ranges is that they allow you to construct formulas before adding the data. When you are designing your worksheet, you can create formulas using names instead of traditional cell references, and then define the names for the corresponding ranges as data becomes available.</p><p>For example, in the sample worksheet, click to select cell A5. As you can see, this formula (=productprice*productcount) results in a #NAME error. This error will remain visible until both named ranges in the formula have been defined:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr2-300x139.png" alt="" width="300" height="139" /><br/></p><p> </p><p>Close Microsoft Excel 2013 without saving any changes that you may have made.</p><h2><a name="_Toc365287757"></a>Defining Named Ranges in Excel</h2><p>To begin, open workbook 1-2 using Microsoft Excel.</p><p>To define a range name in Excel, you first need to select the cell or range of cells that you would like to work with. For this example, select cells A2-A4 in the sample workbook. Next, click Formulas → Define Name (not the drop-down arrow):</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr3-1024x261.png" alt="" width="1024" height="261" /><br/></p><p> </p><p>This action will open the New Name dialog box. Here, you can give the new range a name, select which part(s) of your workbook that this range will reference, and add comments. By default, if the cells are all in one row or column, the Name field will be filled in with that column or row’s header (if one has been defined).</p><p>In this example, you can see that the Sales header has been inserted into the Name field. Leave the default settings unchanged and click OK to apply the new named range:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr4-300x227.png" alt="" width="300" height="227" /><br/></p><p>You will now be returned to the worksheet you’ve been working with. Repeat the above steps to define “Expenses” as the named range for cells B2-B4:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr5-300x122.png" alt="" width="300" height="122" /><br/></p><p>You will now see that the formula that was based upon named ranges has changed to incorporate the values you just defined:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/Nr6-300x133.png" alt="" width="300" height="133" /><br/></p><p>Save the changes that you have made to the current workbook and close Microsoft Excel 2013.</p><h2><a name="_Toc365287758"></a>Editing Named Ranges in Excel</h2><p>To begin, open workbook 1-3 using Microsoft Excel.</p><p>Once a named range has been defined, you can see the name in the name box (next to the formula bar) when the cell or cell range in question has been selected. For this example, select cells A2-A4. You will see that they have been named “Sales:”</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr7-300x144.png" alt="" width="300" height="144" /><br/></p><p>To quickly select a named range in the current workbook, click on the name box arrow and select one of the options listed. For this example, choose Expenses:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr17-300x133.png" alt="" width="300" height="133" /><br/></p><p>The entire Expenses range is now selected:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr8-300x149.png" alt="" width="300" height="149" /><br/></p><p> </p><p>The data within this range may now be modified in any way that you would like. If you insert rows or columns into the range, they will automatically be incorporated.</p><p>To modify the named ranges themselves, click Formulas → Name Manager:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr9-1024x120.png" alt="" width="1024" height="120" /><br/></p><p>The Name Manager dialog will be displayed. Here, you will see all named ranges inside the current workbook:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/Nr10-300x233.png" alt="" width="300" height="233" /><br/></p><p>Let’s edit the Expenses range. Click its name to select it and then click Edit:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr11-300x76.png" alt="" width="300" height="76" /><br/></p><p>The Edit Name dialog will now be displayed. Here, you will see controls to change the name, add comments, and change the range of cells:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr12-300x227.png" alt="" width="300" height="227" /><br/></p><p> </p><p>Click Cancel to return to the Name Manager without saving any changes. In the Name Manager, click Close to return to the Excel window. Close Microsoft Excel without saving any changes that you may have made.</p><h2><a name="_Toc365287759"></a>Deleting Named Ranges in Excel</h2><p>To begin, open workbook 1-4 using Microsoft Excel.</p><p>To delete a named range, you first need to open the Name Manager. Do this by clicking Formulas → Name Manager:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr13-1024x120.png" alt="" width="1024" height="120" /><br/></p><p>When the Name Manager dialog box opens, you will see all of the named ranges in the current workbook. Delete the Expenses named range by selecting it from the list and then clicking the Delete button:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr14-300x69.png" alt="" width="300" height="69" /><br/></p><p>A warning dialog will then be displayed that asks you to confirm the operation. Click OK:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr15-300x136.png" alt="" width="300" height="136" /><br/></p><p>Deleting a named range is a permanent action. You cannot directly recover a named range that you deleted. However, if it was a recent action, it can be undone using the Undo command.</p><p>You will now be returned to the Name Manager. Here, you will see that the Expenses range has been deleted. Click Close to return to the primary Excel 2013 window:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr16-300x233.png" alt="" width="300" height="233" /><br/></p><p>Because the formula in the Profit column uses the Expenses named range, it will now display a #NAME? error. Save the changes that you have made to the current workbook and close Microsoft Excel.</p><p><a name="_Toc365287760"></a></p><p> </p><h2>Using Named Ranges in Excel Formulas</h2><p>To begin, open workbook 1-5 using Microsoft Excel.</p><p>To use named ranges in formulas, begin with an equals sign like any formula. For this example, select cell C2 and then type an equals sign in the formula bar:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr18-300x129.png" alt="" width="300" height="129" /><br/></p><p>Next, start typing the name of the named range that you would like to use. For this example, start typing “Sales.” A drop-down list will be displayed with a number of suggestions to choose from. Double-click Sales:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr19-300x194.png" alt="" width="300" height="194" /><br/></p><p>After a named range has been typed into the formula bar, it will be color coded:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr20-300x128.png" alt="" width="300" height="128" /><br/></p><p>Next, you need to add an operator. For this example, type a minus sign (-):</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr21-300x140.png" alt="" width="300" height="140" /><br/></p><p> </p><p>Add the Expenses named range by typing it out fully. Once it is written out entirely, it will also appear color-coded:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr22-300x130.png" alt="" width="300" height="130" /><br/></p><p> </p><p>Press Enter to apply the new formula. The results will be displayed immediately:</p><p><img src="http://theexcelclub.com/wp-content/uploads/2017/07/nr24-300x130.png" alt="" width="300" height="130" /><br/></p><p> </p><p>Save the changes that you have made to the current workbook and close Microsoft Excel.</p><h2><center><strong>Sign up to my newsletter for Excel and Power BI Tips, Ticks and Hacks</strong></center></h2>
<a href="https://theexcelclub.newzenler.com/f/sign-up-for-newsletter" role="button">
Sign Up
</a>
<h4><center>In return for this Tip - Lets get started with Named Ranges in Excel - I ask that you share this post or the video with your friends and colleagues</center></h4>
<h2>Learn and Earn Steem Activity</h2>
<ol><li><h6>Now that you have an understanding of Named Ranges, consider the spreadsheets you work with.  Describe a workbook and a situation where you could now use Named Ranges?  Leave your answer in the comments section below to be eligible for STEEM rewards.</h6></li><li><h6>Have you any questions on Named Ranges, or feedback on this post?  If so please use the comments below and you could earn STEEM rewards.</h6></li></ol><p> </p>
<img width="225" height="225" src="http://theexcelclub.com/wp-content/uploads/2019/05/steem.png" alt="learn and earn steem activity" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/05/steem.png?w=225 225w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/05/steem.png?resize=150%2C150 150w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/05/steem.png?resize=100%2C100 100w" sizes="(max-width: 225px) 100vw, 225px" /><br/>
<h5><center><strong>Now there is value in Learning with The Excel Club and our Learn and Earn STEEM activities.</strong></center></h5><center><strong>We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn.</strong></center></p><h6 style="text-align: center;"><a href="https://theexcelclub.com/learn-and-earn-steem-activities/">Find out more now and start earning while you are learning Excel and Power BI</a></h6> <br /><center><hr/><em>Posted from my blog with <a href='https://wordpress.org/plugins/steempress/'>SteemPress</a> : http://theexcelclub.com/step-by-step-named-ranges-in-excel/ </em><hr/></center>          
👍  , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , and 236 others
properties (23)
post_id75,097,979
authortheexcelclub
permlinkstepbystep-namedrangesinexcel-lgbgjj9q5j
categoryexcel
json_metadata{"community":"steempress","app":"steempress\/1.4","tags":["excel","powerbi","dax","powerpivot","tutorial"],"original_link":"http:\/\/theexcelclub.com\/step-by-step-named-ranges-in-excel\/"}
created2019-05-21 11:25:12
last_update2019-05-21 11:25:12
depth0
children8
net_rshares150,678,563,775,928
last_payout2019-05-28 11:25:12
cashout_time1969-12-31 23:59:59
total_payout_value56.814 SBD
curator_payout_value21.762 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length12,225
author_reputation14,753,290,326,944
root_title"Step by Step - Named Ranges in Excel"
beneficiaries
0.
accountsteempress
weight1,500
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
author_curate_reward""
vote details (300)
@dernan ·
$0.27
@theexcelclub you made this nice and easy indeed to earn steem rewards.  I have a number of spreadsheets set up using named ranges, but most often I would use Table format and use a column name in a formula over a named range.  I know you can set up dynamic named ranges, but with tables, if you reference them in a formula and update the table with more data, the formula updates too.  with static named ranges this does not happen.
👍  , , , ,
properties (23)
post_id75,099,267
authordernan
permlinkre-theexcelclub-stepbystep-namedrangesinexcel-lgbgjj9q5j-20190521t115955690z
categoryexcel
json_metadata{"tags":["excel"],"users":["theexcelclub"],"app":"steemit\/0.1"}
created2019-05-21 11:59:54
last_update2019-05-21 11:59:54
depth1
children1
net_rshares489,666,290,916
last_payout2019-05-28 11:59:54
cashout_time1969-12-31 23:59:59
total_payout_value0.217 SBD
curator_payout_value0.053 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length433
author_reputation140,892,828,816
root_title"Step by Step - Named Ranges in Excel"
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
author_curate_reward""
vote details (5)
@paulag ·
I think I have to make a tutorial on dynamic named ranges now too, tables do rock and that is a great alternative :-)
properties (22)
post_id75,120,260
authorpaulag
permlinkre-dernan-re-theexcelclub-stepbystep-namedrangesinexcel-lgbgjj9q5j-20190521t204538141z
categoryexcel
json_metadata{"tags":["excel"],"app":"steemit\/0.1"}
created2019-05-21 20:45:39
last_update2019-05-21 20:45:39
depth2
children0
net_rshares0
last_payout2019-05-28 20:45:39
cashout_time1969-12-31 23:59:59
total_payout_value0.000 SBD
curator_payout_value0.000 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length117
author_reputation145,657,661,552,446
root_title"Step by Step - Named Ranges in Excel"
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
@bdvoter ·
You got a 60.43% upvote from @bdvoter courtesy of @theexcelclub!

**Delegate your SP to us at @bdvoter and earn daily 100% profit share for your delegation & rewards will be distributed automatically daily.**

[500 SP](https://v2.steemconnect.com/sign/delegateVestingShares?&delegatee=bdvoter&vesting_shares=500%20SP), [1000 SP](https://v2.steemconnect.com/sign/delegateVestingShares?&delegatee=bdvoter&vesting_shares=1000%20SP), [2500 SP](https://v2.steemconnect.com/sign/delegateVestingShares?&delegatee=bdvoter&vesting_shares=2500%20SP), [5000 SP](https://v2.steemconnect.com/sign/delegateVestingShares?&delegatee=bdvoter&vesting_shares=5000%20SP), [10000 SP](https://v2.steemconnect.com/sign/delegateVestingShares?&delegatee=bdvoter&vesting_shares=10000%20SP).

If you are from Bangladesh and looking for community support, Join [BDCommunity Discord Server](https://discord.gg/yEPcKTq) & If you want to support our service, please set your witness proxy to [BDCommunity](https://steemconnect.com/sign/account-witness-proxy?proxy=bdcommunity).
👎  
properties (23)
post_id75,100,979
authorbdvoter
permlinkre-theexcelclub-stepbystep-namedrangesinexcel-lgbgjj9q5j-20190521t123614957z
categoryexcel
json_metadata{}
created2019-05-21 12:36:15
last_update2019-05-21 12:36:15
depth1
children0
net_rshares-10,587,336,443
last_payout2019-05-28 12:36:15
cashout_time1969-12-31 23:59:59
total_payout_value0.000 SBD
curator_payout_value0.000 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length1,047
author_reputation6,293,451,488,408
root_title"Step by Step - Named Ranges in Excel"
beneficiaries[]
max_accepted_payout100,000.000 SBD
percent_steem_dollars0
author_curate_reward""
vote details (1)
@spydo ·
re-theexcelclub-stepbystep-namedrangesinexcel-lgbgjj9q5j-20190521t125305432z
You got a 73.15% upvote from @spydo courtesy of @theexcelclub! We offer 100% Payout and Curation.
properties (22)
post_id75,101,674
authorspydo
permlinkre-theexcelclub-stepbystep-namedrangesinexcel-lgbgjj9q5j-20190521t125305432z
categoryexcel
json_metadata{"app":"postpromoter\/2.1.1"}
created2019-05-21 12:53:06
last_update2019-05-21 12:53:06
depth1
children0
net_rshares0
last_payout2019-05-28 12:53:06
cashout_time1969-12-31 23:59:59
total_payout_value0.000 SBD
curator_payout_value0.000 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length98
author_reputation4,455,422,450,444
root_title"Step by Step - Named Ranges in Excel"
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
@detlev ·
$0.33
Did I say that I love excel.  

### This is a useful tip and I need to use this more. 

Tell us a bit about pulldown menus, functions like sumif (German sumwenn) and the function to color the background by a specific value / range. 

Posted using [Partiko iOS](https://partiko.app/referral/detlev)
👍  ,
properties (23)
post_id75,115,651
authordetlev
permlinkdetlev-re-theexcelclub-stepbystep-namedrangesinexcel-lgbgjj9q5j-20190521t183318283z
categoryexcel
json_metadata{"app":"partiko","client":"ios"}
created2019-05-21 18:33:18
last_update2019-05-21 18:33:18
depth1
children1
net_rshares560,603,953,172
last_payout2019-05-28 18:33:18
cashout_time1969-12-31 23:59:59
total_payout_value0.251 SBD
curator_payout_value0.083 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length297
author_reputation146,779,926,762,207
root_title"Step by Step - Named Ranges in Excel"
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
author_curate_reward""
vote details (2)
@paulag ·
$0.07
hi @detlev - glad to hear you love excel and thank you very much for the suggestions in topics.  Stay tuned for more and I will gladly provide tutorials on the same :-)
👍  , ,
properties (23)
post_id75,120,216
authorpaulag
permlinkre-detlev-detlev-re-theexcelclub-stepbystep-namedrangesinexcel-lgbgjj9q5j-20190521t204354323z
categoryexcel
json_metadata{"tags":["excel"],"users":["detlev"],"app":"steemit\/0.1"}
created2019-05-21 20:43:54
last_update2019-05-21 20:43:54
depth2
children0
net_rshares118,340,399,934
last_payout2019-05-28 20:43:54
cashout_time1969-12-31 23:59:59
total_payout_value0.053 SBD
curator_payout_value0.017 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length168
author_reputation145,657,661,552,446
root_title"Step by Step - Named Ranges in Excel"
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
author_curate_reward""
vote details (3)
@eastmael · (edited)
$0.33
hiz8b23z8f
Named ranges is one of the most useful features that excel has. Before pivot tables and charts, this is one of its core functions that helped me develop and understand excel macros.

A particular scenario I encountered extensive use for named ranges is in a timelogger we use at work.

Particularly, to get a list of deliverables or tasks for a particular development phase like detailed design creation. The excel file we use has a named range "DD" and we use this named range in conjunction with excel's list data validation to be used as a dropdown when inputting a time log. :)

A very helpful topic covered by the @theexcelclub.
👍  , , ,
properties (23)
post_id75,154,025
authoreastmael
permlinkunhl6nypzzecl4g
categoryexcel
json_metadata{"app":"steemit\/0.1","tags":["excel"],"users":["theexcelclub"]}
created2019-05-22 12:07:09
last_update2019-05-22 12:12:54
depth1
children0
net_rshares561,329,354,609
last_payout2019-05-29 12:07:09
cashout_time1969-12-31 23:59:59
total_payout_value0.244 SBD
curator_payout_value0.085 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length633
author_reputation50,634,257,333,295
root_title"Step by Step - Named Ranges in Excel"
beneficiaries
0.
accountsteempress
weight500
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
author_curate_reward""
vote details (4)
@arcange ·
Congratulations @theexcelclub!
Your post was mentioned in the [Steem Hit Parade](/hit-parade/@arcange/daily-hit-parade-20190521) in the following category:

* Pending payout - Ranked 9 with $ 82,02
properties (22)
post_id75,164,095
authorarcange
permlinkre-stepbystep-namedrangesinexcel-lgbgjj9q5j-20190521t175149000z
categoryexcel
json_metadata{}
created2019-05-22 15:54:45
last_update2019-05-22 15:54:45
depth1
children0
net_rshares0
last_payout2019-05-29 15:54:45
cashout_time1969-12-31 23:59:59
total_payout_value0.000 SBD
curator_payout_value0.000 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length198
author_reputation161,353,272,413,946
root_title"Step by Step - Named Ranges in Excel"
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000