{"id":588,"date":"2022-05-18T22:45:42","date_gmt":"2022-05-18T17:15:42","guid":{"rendered":"https:\/\/sheetstips.com\/?p=588"},"modified":"2022-05-21T12:33:05","modified_gmt":"2022-05-21T07:03:05","slug":"named-ranges-in-google-sheets","status":"publish","type":"post","link":"https:\/\/sheetstips.com\/named-ranges-in-google-sheets\/","title":{"rendered":"How to Create Named Ranges in Google Sheets (Static &#038; Dynamic)"},"content":{"rendered":"<p>Google Sheets Named Ranges lets you use a single range across several sheets, formulas, and other operations, and then change it from a specific location. For example, Instead of naming a range of cells &#8220;A1:B2,&#8221; you may call it &#8220;Fruits.&#8221; A formula like &#8220;=SUM(A1:B2, D4:E6)&#8221; could be written as &#8220;=SUM(Fruits, Vegetables)&#8221; in this way. In this article, let&#8217;s understand how to create static and dynamic named ranges with important <strong><a href=\"https:\/\/sheetstips.com\/google-sheets-tips\/\" target=\"_blank\" rel=\"noopener\">Google Sheet tips<\/a><\/strong>. Read on to find more.<\/p>\n<table>\n<tbody>\n<tr>\n<td width=\"601\">\n<p style=\"text-align: center;\"><strong>Table of Contents<\/strong><\/p>\n<ul>\n<li><a href=\"#How-to-Create-Named-Ranges-in-Google-Sheets?\">How to Create Named Ranges in Google Sheets?<\/a><\/li>\n<li><a href=\"#List-of-Rules-to-be-Followed-to-Create-Named-Ranges\">List of Rules to be Followed to Create Named Ranges<\/a><\/li>\n<li><a href=\"#How-to-Edit-Named-Ranges-in-Google-Sheets?\">How to Edit Named Ranges in Google Sheets?<\/a><\/li>\n<li><a href=\"#Creating-a-Dynamic-Named-Range-in-Google-Sheets\">Creating a Dynamic Named Range in Google Sheets<\/a><\/li>\n<\/ul>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2 id=\"How-to-Create-Named-Ranges-in-Google-Sheets?\">How to Create Named Ranges in Google Sheets?<\/h2>\n<p>Follow the steps as listed below to create a named range in Google Sheets:<\/p>\n<ul>\n<li><strong>Step 1:<\/strong> Choose the dataset to create a named range.<\/li>\n<li><strong>Step 2:<\/strong> From the homepage, select &#8220;<strong>Data<\/strong>&#8221; and then &#8220;<strong>Named Ranges<\/strong>&#8221; from the drop-down menu.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/08\/How-to-Create-a-Dependent-Drop-Down-List-in-Google-Sheets-1.jpg\" alt=\"named ranges in google sheets \" width=\"1457\" height=\"736\" \/><\/p>\n<ul>\n<li><strong>Step 3:<\/strong> The \u201c<strong>Named Ranges<\/strong>\u201d tab in Google Sheets will now open. Now select &#8220;<strong>Add a range<\/strong>&#8221; from the drop-down menu.<\/li>\n<li><strong>Step 4:<\/strong> Mention the \u201c<strong>Named Ranges<\/strong>\u201d name as well as the cell range.<\/li>\n<li><strong>Step 5:<\/strong> Select &#8220;<strong>Done<\/strong>&#8221; from the drop-down menu.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/08\/How-to-Create-a-Dependent-Drop-Down-List-in-Google-Sheets-2-1536x619.jpg\" alt=\"named ranges in google sheets\" width=\"1536\" height=\"619\" \/><\/p>\n<h3 id=\"List-of-Rules-to-be-Followed-to-Create-Named-Ranges\">List of Rules to be Followed to Create Named Ranges<\/h3>\n<p>The list of rules which one should follow while creating a named range is:<\/p>\n<ul>\n<li>Only letters, numbers, and underscores are permitted.<\/li>\n<li>Starting with a number or the words &#8220;true&#8221; or &#8220;false&#8221; isn&#8217;t allowed.<\/li>\n<li>There are no spaces or punctuation allowed.<\/li>\n<li>1\u2013250 characters are required.<\/li>\n<li>There can&#8217;t be any A1 or R1C1 syntax. If you name your range &#8220;A1:B2&#8221; or &#8220;R1C1:R2C2,&#8221; for example, you might get an error.<\/li>\n<\/ul>\n<h3 id=\"How-to-Edit-Named-Ranges-in-Google-Sheets?\">How to Edit Named Ranges in Google Sheets?<\/h3>\n<p>We can edit named ranges in Google Sheets by following the steps listed below:<\/p>\n<ul>\n<li><strong>Step 1:<\/strong> Open the Google Sheet where you would like to edit the named ranges.<\/li>\n<li><strong>Step 2:<\/strong> Then select <strong>Data<\/strong> from the menubar and choose Named Ranges from the drop-down menu.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/08\/How-to-Create-a-Dependent-Drop-Down-List-in-Google-Sheets-1.jpg\" alt=\"named ranges in google sheets\" width=\"1457\" height=\"736\" \/><\/p>\n<ul>\n<li><strong>Step 3:<\/strong> Click<strong> Edit<\/strong> on the identified range you wish to edit or delete.<\/li>\n<li><strong>Step 4:<\/strong> Enter a new name or range to edit the range, then click the <strong>Done<\/strong> button.<\/li>\n<li><strong>Step 5:<\/strong> To delete the named range, click <strong>Delete<\/strong> range next to the name.<\/li>\n<li><strong>Step 6:<\/strong> Now a pop-up appears. Just click on &#8220;<strong>Remove<\/strong>&#8220;.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/08\/How-to-Create-a-Dependent-Drop-Down-List-in-Google-Sheets-3-1536x369.jpg\" alt=\"named ranges in google sheets\" width=\"1536\" height=\"369\" \/><\/p>\n<p>Note: Any calculations that reference a named range will no longer operate if you delete it. Protected ranges that refer to a named range will continue to work with the cell values.<\/p>\n<ul>\n<li><a href=\"https:\/\/sheetstips.com\/jump-to-cell-range-in-google-sheets\/\">Jump To Cell Range in Google Sheets \u2013 How To Jump To Specific Cell Range in Google Sheets?<\/a><\/li>\n<li><a href=\"https:\/\/sheetstips.com\/how-to-extract-year-from-google-sheets\/\">How to Extract the Year from Google Sheets-YEAR Function in Google Sheets<\/a><\/li>\n<li><a href=\"https:\/\/sheetstips.com\/how-to-make-an-organization-chart-in-google-sheets\/\">How to\u00a0Make an\u00a0Organization Chart in Google Sheets? \u2013 Create an Org Chart in Google Sheets<\/a><\/li>\n<\/ul>\n<h3 id=\"Creating-a-Dynamic-Named-Range-in-Google-Sheets\">Creating a Dynamic Named Range in Google Sheets<\/h3>\n<p>Named Ranges are useful because they allow you to update the named range once and have all the formulas that use it update as well. You may execute this by using a clever INDIRECT function trick. Assume you have a dataset like the one below, and you want to build a named range for the sales data that updates automatically anytime new data is uploaded.<\/p>\n<p>Enter the formula =COUNT(C2:C100)+1 in a cell (D2 in this example). This will tell us how many cells have a number in them. Because our computation data begins in row 2, 1 is added to the formula. It&#8217;s also important to note that we use C2:C100 so that any further data will be counted automatically in the future. We also used the COUNT function because the data is purely numerical. COUNTIF can also be used based on your data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-591 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/08\/named-ranges-in-google-sheets.png\" alt=\"named ranges in google sheets\" width=\"479\" height=\"325\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/08\/named-ranges-in-google-sheets.png 479w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/08\/named-ranges-in-google-sheets-300x204.png 300w\" sizes=\"(max-width: 479px) 100vw, 479px\" \/><\/p>\n<p>Enter the formula =\u201cSheet1!C2:C\u201d&amp;D2 in another cell (F2 in this example). This would provide us with a reference for the complete computation data column. For instance, if there are ten sales transactions, Sheet1 will be generated! C2:C11. Sheet1 will be generated if there are 15 transactions! C2:C16.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-590 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/08\/named-ranges-in-google-sheets2.png\" alt=\"named ranges in google sheets\" width=\"594\" height=\"324\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/08\/named-ranges-in-google-sheets2.png 594w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/08\/named-ranges-in-google-sheets2-300x164.png 300w\" sizes=\"(max-width: 594px) 100vw, 594px\" \/><\/p>\n<ol>\n<li>Select Named Ranges from the Data tab.<\/li>\n<li>Use Sheet1!E2 as the range name for a named range called SalesData.<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-589 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/08\/named-ranges-in-google-sheets3.png\" alt=\"named ranges in google sheets\" width=\"306\" height=\"459\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/08\/named-ranges-in-google-sheets3.png 306w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/08\/named-ranges-in-google-sheets3-200x300.png 200w\" sizes=\"(max-width: 306px) 100vw, 306px\" \/><\/p>\n<p>You can now refer to the dynamic named range with the following formula: =INDIRECT(SalesData)<\/p>\n<p>The INDIRECT function would refer to cell F2, which has the reference for the sales data, by using the designated range. Because we used =\u201cSheet1!C2:C\u201d&amp;D2 to make the range in E2 dynamic, the named range also becomes dynamic.<\/p>\n<p>For example, you can now use the formula =SUM(INDIRECT(SalesData)) to determine the total sales. If you add more transaction records, the formula will immediately update and tell you the updated sales total.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Google Sheets Named Ranges lets you use a single range across several sheets, formulas, and other operations, and then change it from a specific location. For example, Instead of naming a range of cells &#8220;A1:B2,&#8221; you may call it &#8220;Fruits.&#8221; A formula like &#8220;=SUM(A1:B2, D4:E6)&#8221; could be written as &#8220;=SUM(Fruits, Vegetables)&#8221; in this way. In &#8230; <a title=\"How to Create Named Ranges in Google Sheets (Static &#038; Dynamic)\" class=\"read-more\" href=\"https:\/\/sheetstips.com\/named-ranges-in-google-sheets\/\" aria-label=\"More on How to Create Named Ranges in Google Sheets (Static &#038; Dynamic)\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[131],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1656,"url":"https:\/\/sheetstips.com\/how-to-rename-columns-or-rows-in-google-sheets\/","url_meta":{"origin":588,"position":0},"title":"How To Rename Columns and Rows in Google Sheets? 2 Easy Methods to Add Column Title in Google Sheets","author":"Varsha","date":"May 13, 2022","format":false,"excerpt":"Naming a Column in Google Sheets will be very helpful when you are applying formulas. For example, if you have a column name as Employee Salary and when you want to perform some calculations using formulas such as SUM, then you can simply enter the formula as =SUM(Employee Salary) instead\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"how to rename columns and rows in google sheets4","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/11\/how-to-rename-columns-and-rows-in-google-sheets.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":278,"url":"https:\/\/sheetstips.com\/dependent-drop-list-google-sheets\/","url_meta":{"origin":588,"position":1},"title":"How to Create a Dependent Drop Down List in Google Sheets: Multiple Drop Down List","author":"Varsha","date":"May 21, 2022","format":false,"excerpt":"Dependent Drop Down List in Google Sheets: Usually a drop down list in Google Sheet is used to create a list of options where users can choose from. Drop down list is one of the most helpful functions in G-Suite since it helps us to save more time by avoiding\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"How to Create a Dependent Drop Down List in Google Sheets","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/08\/How-to-Create-a-Dependent-Drop-Down-List-in-Google-Sheets-8.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1369,"url":"https:\/\/sheetstips.com\/named-versions-in-google-files\/","url_meta":{"origin":588,"position":2},"title":"Named Versions in Google Sheets: How to Name Version History in Google Sheets","author":"Varsha","date":"May 15, 2022","format":false,"excerpt":"Google Sheets Named Versions\u00a0is one of the best features which helps us to name the edit history while working. For example, let us consider you are writing a story in Google Sheets. So the first step is to write a draft, then submit it for review and then move the\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"named-versions-in-google-files","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/10\/named-versions-in-google-files.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":680,"url":"https:\/\/sheetstips.com\/table-of-contents-google-sheets\/","url_meta":{"origin":588,"position":3},"title":"How to Create a Table of Contents in Google Sheets (Add\/Update TOC)","author":"Varsha","date":"May 18, 2022","format":false,"excerpt":"Table of contents is one most useful features which we often use in Google Docs and other editing software. However, we don't have the Table of Contents feature in Google Sheets. But still, you are working on the huge data set and want to insert the table of contents inside\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"table-of-contents-google-sheets","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/08\/table-of-contents-in-google-sheets4-1.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1572,"url":"https:\/\/sheetstips.com\/sum-column-google-sheets\/","url_meta":{"origin":588,"position":4},"title":"How to SUM a Column in Google Sheets? (Add Numbers, Row, Columns, Cells)","author":"Varsha","date":"May 14, 2022","format":false,"excerpt":"If you are a Google Sheets user, then you will be fond of adding rows and columns in between the cells as and when required. Most spreadsheet users will use Google Sheets to save the numbers and perform mathematical calculations. One of the widely used mathematical calculations in Google Sheets\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"sum-column-google-sheets","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/10\/how-to-sum-in-google-sheets1.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1612,"url":"https:\/\/sheetstips.com\/how-to-see-basic-calculations-without-formulas-in-google-sheets\/","url_meta":{"origin":588,"position":5},"title":"How to See Basic Calculations Without Formulas in Google Sheets?","author":"Varsha","date":"May 13, 2022","format":false,"excerpt":"At times, when we are using Google Sheets we might fall under a situation where we would like to view the sum or average of the numbers without applying formulas. Google Sheets allows us to count, minimum value, maximum value, the sum of the numbers, average numbers, count numbers, and\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"how-to-see-basic-calculations-without-formulas-in-google-sheets","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/11\/how-to-perform-basic-calculations-without-using-formulas-in-google-sheets.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/588"}],"collection":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/comments?post=588"}],"version-history":[{"count":1,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/588\/revisions"}],"predecessor-version":[{"id":2444,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/588\/revisions\/2444"}],"wp:attachment":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/media?parent=588"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/categories?post=588"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/tags?post=588"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}