{"id":4284,"date":"2019-01-05T12:44:25","date_gmt":"2019-01-05T17:44:25","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=4284"},"modified":"2019-01-10T13:17:31","modified_gmt":"2019-01-10T18:17:31","slug":"did-you-know-you-can-name-ranges-in-excel-to-make-formulae-clearer","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=4284","title":{"rendered":"Did you know \u2026 you can name ranges in Excel to make formulae clearer?"},"content":{"rendered":"\n<p>Formulae in Excel aren\u2019t always easy to decode \u2013 even a\nrelatively simple formula, like the volume of a right rectangular pyramid below,\ncan be a little cryptic with the A2 type cell identifiers. <\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"296\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/01\/Excel-NamedRange01-1024x296.png\" alt=\"\" class=\"wp-image-4285\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange01-1024x296.png 1024w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange01-300x87.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange01-768x222.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange01.png 1172w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>You can name ranges and use range names to make a formula easier to understand. Highlight a data set \u2013 in this case, I am highlighting the \u201clength\u201d values \u2013 column A. On the \u201cFormulas\u201d ribbon bar, click on \u201cDefine Name\u201d (you don\u2019t need to hit the inverted caret on the right of the button \u2013 just click the \u2018define name\u2019 text). <\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"804\" height=\"452\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/01\/Excel-NamedRange02.png\" alt=\"\" class=\"wp-image-4286\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange02.png 804w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange02-300x169.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange02-768x432.png 768w\" sizes=\"auto, (max-width: 804px) 100vw, 804px\" \/><\/figure>\n\n\n\n<p>Supply a name for the range \u2013 in this case, I am calling it \u201cBase_Length\u201d\n(range names need to start with a letter or underscore and cannot contain\nspaces). Click OK to save the range name. Repeat this operation with all of the\nother data groups \u2013 in my case, I named Column B \u201cBase_Width\u201d and Column C \u201cHeight\u201d.\n<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"302\" height=\"232\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/01\/Excel-NamedRange03.png\" alt=\"\" class=\"wp-image-4287\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange03.png 302w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange03-300x230.png 300w\" sizes=\"auto, (max-width: 302px) 100vw, 302px\" \/><\/figure>\n\n\n\n<p>Use the name instead of the cell identifier \u2013 as you type\nyour formula, the range names matching your typed text will appear. <\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"713\" height=\"184\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/01\/Excel-NamedRange04.png\" alt=\"\" class=\"wp-image-4288\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange04.png 713w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange04-300x77.png 300w\" sizes=\"auto, (max-width: 713px) 100vw, 713px\" \/><\/figure>\n\n\n\n<p>It is now a <em>lot<\/em> clearer\nwhat this formula <em>means<\/em> \u2013 base length\ntimes base width time height all divided by three. Which <em>is<\/em> the formula to calculate the volume of a right rectangular\npyramid. <\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"172\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/01\/Excel-NamedRange05-1024x172.png\" alt=\"\" class=\"wp-image-4289\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange05-1024x172.png 1024w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange05-300x50.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange05-768x129.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange05.png 1184w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>The calculated answer is the same either way \u2013 but this\nmakes it easier to figure out what exactly you were computing when you open the\nspreadsheet again in six months \ud83d\ude0a (Or share the\nspreadsheet with others). <\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"661\" height=\"353\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/01\/Excel-NamedRange06.png\" alt=\"\" class=\"wp-image-4290\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange06.png 661w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/01\/Excel-NamedRange06-300x160.png 300w\" sizes=\"auto, (max-width: 661px) 100vw, 661px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Formulae in Excel aren\u2019t always easy to decode \u2013 even a relatively simple formula, like the volume of a right rectangular pyramid below, can be a little cryptic with the A2 type cell identifiers. You can name ranges and use range names to make a formula easier to understand. Highlight a data set \u2013 in &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[675,708,709,691,729,731],"class_list":["post-4284","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-did-you-know","tag-excel","tag-microsoft-excel","tag-microsoft-office","tag-microsoft-office-365","tag-ms-excel"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4284","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4284"}],"version-history":[{"count":2,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4284\/revisions"}],"predecessor-version":[{"id":4292,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4284\/revisions\/4292"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4284"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4284"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4284"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}