{"id":455,"date":"2015-09-07T11:41:13","date_gmt":"2015-09-07T10:41:13","guid":{"rendered":"http:\/\/www.mathsfiles.com\/blog\/?p=455"},"modified":"2015-09-07T11:41:13","modified_gmt":"2015-09-07T10:41:13","slug":"the-offset-function-and-dynamic-named-ranges-in-excel","status":"publish","type":"post","link":"https:\/\/www.mathsfiles.com\/blog\/2015\/09\/07\/the-offset-function-and-dynamic-named-ranges-in-excel\/","title":{"rendered":"The Offset Function and Dynamic Named Ranges in Excel"},"content":{"rendered":"

Understanding the Offset function and its use in creating dynamic named ranges opens up some very powerful techniques in Excel.
\nOne such use is in creating a dynamic chart which updates automatically when its linked data is changed.
\nAlthough this can be more easily done in Excel 2007 onwards by using Tables the dynamic named range method is more powerful and has a wider range of uses.I have used these techniques extensively in my Excel work.<\/p>\n

This excel file available in the Excel techniques section of\u00a0www.mathsfiles.com\/Excel_Files<\/a> shows how the Offset function works and how it can be used to create a simple dynamic graph.<\/p>\n

\"offset<\/a>
Offset function screen shot<\/figcaption><\/figure>\n

Direct link:
\nOffset Function Excel file<\/a><\/p>\n

You can also watch a Demo which shows in more detail how to set up a dynamic named range and use it to create a dynamic graph.<\/p>\n

Watch the Demo<\/a> (based on Excel 2007) or<\/p>\n

Watch the Demo<\/a> (based on Excel 2003)<\/p>\n","protected":false},"excerpt":{"rendered":"

Understanding the Offset function and its use in creating dynamic named ranges opens up some very powerful techniques in Excel. One such use is in creating a dynamic chart which updates automatically when its linked data is changed. Although this can be more easily done in Excel 2007 onwards by using Tables the dynamic named … Continue reading The Offset Function and Dynamic Named Ranges in Excel<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[18,20,17],"tags":[34,32,3,35,33],"class_list":["post-455","post","type-post","status-publish","format-standard","hentry","category-excel-files","category-excel-techniques","category-mathsfiles-com-resources","tag-charts","tag-dynamic-named-ranges","tag-excel","tag-graphs","tag-offset-function"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p6flNW-7l","_links":{"self":[{"href":"https:\/\/www.mathsfiles.com\/blog\/wp-json\/wp\/v2\/posts\/455","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mathsfiles.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mathsfiles.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mathsfiles.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mathsfiles.com\/blog\/wp-json\/wp\/v2\/comments?post=455"}],"version-history":[{"count":3,"href":"https:\/\/www.mathsfiles.com\/blog\/wp-json\/wp\/v2\/posts\/455\/revisions"}],"predecessor-version":[{"id":459,"href":"https:\/\/www.mathsfiles.com\/blog\/wp-json\/wp\/v2\/posts\/455\/revisions\/459"}],"wp:attachment":[{"href":"https:\/\/www.mathsfiles.com\/blog\/wp-json\/wp\/v2\/media?parent=455"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mathsfiles.com\/blog\/wp-json\/wp\/v2\/categories?post=455"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mathsfiles.com\/blog\/wp-json\/wp\/v2\/tags?post=455"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}