Named ranges are<\/strong> used to assign a name to a certain cell or range of cells. This can help us in situations where we want to use these ranges later in formulas, functions, or any other part of our workbook.<\/p>\n\n\n\nAlthough we would usually use letters, numbers, and underscores to name our range, we can also use special characters<\/strong>. There are a few rules and things to have in mind when doing this, though. In the example below, we will explain what they are.<\/p>\n\n\n\nLimitations for Special Characters in Named Ranges<\/h2>\n\n\n\n Things to consider when we use special characters in named ranges are as follows:<\/p>\n\n\n\n
\nAllowed Characters:<\/strong> We can use letters (uppercase and lowercase), numbers, underscores, and periods in all of our named ranges. They are safe to use and will not cause any issues.<\/li>\n\n\n\nUnderscores and Periods:<\/strong> As already said, these characters are allowed. Underscore (_) and periods (.) <\/strong>are commonly used in named ranges. You will often see the names such as \u201cTotal_Sales\u201d or \u201cAverage.Monthy\u201d.<\/strong><\/li>\n\n\n\nSpaces and Other Special Characters:<\/strong> Although it is possible to use spaces and other special characters such as hyphens (-), ampersands (&), and exclamation points (!)<\/strong> in our named ranges, it is better to avoid these<\/strong>. Some Excel formulas might have trouble interpreting these characters, and we can end up with unexpected errors.<\/li>\n\n\n\nStarting the Named Ranges<\/strong>: Named ranges should always start with a letter (A-Z or a-z) or an underscore (_)<\/strong>. We cannot start them with a number, period, or other special characters.<\/li>\n\n\n\nCase Sensitivity:<\/strong> Named ranges are not case-sensitive. \u201cSalesTotal\u201d or \u201csalestotal\u201d are considered the same range<\/strong>.<\/li>\n\n\n\nLength:<\/strong> Named ranges can be up to 255 characters<\/strong> long.<\/li>\n<\/ul>\n\n\n\nUsing Special Characters in Named Ranges<\/p>\n\n\n\n
For our example, we will create the random sales data for the North Region<\/strong>:<\/p>\n\n\n\n <\/figure>\n\n\n\nTo create a name range with special characters, we would typically use \u201cName Manager\u201d or go to the \u201cDefine Name\u201d dialog box<\/strong>. We access these by going to the Formula tab and then going to Defined Names section<\/strong>. We will choose Name Manager then:<\/p>\n\n\n\n <\/figure>\n\n\n\nIn the window that appears, we will click \u201cNew\u201d<\/strong> or select an existing name range. In our case, we will do the former:<\/p>\n\n\n\n <\/figure>\n\n\n\nIn the “Name” field, we will enter “North_Region.Sales\u201d<\/strong>. In the “Refers to” field<\/strong>, we select the cells that contain the sales data for the North region (range A2:A11)<\/strong>. For our Scope, we choose Workbook<\/strong>:<\/p>\n\n\n\nFinally, we click “OK”<\/strong> to save the named range.<\/p>\n\n\n\n <\/figure>\n\n\n\nWe will be presented with the list of our named ranges:<\/p>\n\n\n\n <\/figure>\n\n\n\nOur named range contains two special characters: underscore and period<\/strong>. If we want to calculate the AVERAGE <\/strong>for our range, for example, it would be simplified a lot. All we have to do is insert the following formula:<\/p>\n\n\n\n=AVERAGE(North_Region.Sales)<\/code><\/pre>\n\n\n\nAnd we will get the result we are searching for ($214,661<\/strong>):<\/p>\n\n\n\n <\/figure>\n\n\n\nRemember that while special characters can be used in named ranges, it’s a good practice to keep names meaningful, concise, and easy to understand for better workbook organization and readability.<\/p>\n","protected":false},"excerpt":{"rendered":"
When working in Excel, there are a lot of ways to mark our data. Named ranges are used to assign a name to…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\n
Using Special Characters in Named Ranges in Excel<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n