{"id":45575,"date":"2013-09-10T17:55:10","date_gmt":"2013-09-10T17:55:10","guid":{"rendered":"https:\/\/www.pcmatic.com\/blog\/?p=45575"},"modified":"2013-09-10T17:55:10","modified_gmt":"2013-09-10T17:55:10","slug":"creating-excel-charts-that-automatically-update","status":"publish","type":"post","link":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/","title":{"rendered":"Creating Excel Charts that Automatically Update"},"content":{"rendered":"

<\/p>\n

Creating Excel Charts that Automatically Update<\/h3>\n

By Allen Wyatt for Excel.Tips.Net<\/p>\n

David has a worksheet that he uses to track sales by company over a number of months. The company names are in column A and up to fifteen months of sales are in columns B:P. David would like to create a chart that could be dynamically changed to show the sales for a single company from the worksheet.<\/p>\n

There are several ways that this can be done; I’ll examine three of them in this tip. For the sake of example, let’s assume that the worksheet is named MyData, and that the first row contains data headers. The company names are in the range A2:A151, and the sales data for those companies is in B2:P151.<\/p>\n

One approach is to use Excel’s AutoFilter capabilities. Create your chart as you normally would, making sure that the chart is configured to draw its data series from the rows of the MyData worksheet. You should also place the chart on its own sheet.<\/p>\n

Now, select A1 on MyData and apply an AutoFilter. (Display the Data tab of the ribbon and click the Filter tool.) A small drop-down arrow appears at the top of each column. Click the drop-down arrow for column A and select the company you want to view in the chart. Excel redraws the chart to include only the single company.<\/p>\n

The only potential drawback to the AutoFilter approach is that each company is considered an independent data series, even though only one of them is displayed in the chart. Because they are independent, each company is charted in a different color. If you want the same charting colors to always be used, then you will need to use one of the other approaches.<\/p>\n

Another way to approach the problem is through the use of an “intermediate” data table\u2014one that is created dynamically, pulling only the information you want from the larger data table. The chart is then based on the dynamic intermediate table. <\/p>\n

Follow these steps:<\/a><\/p>\n

This post appears with permission from Allen Wyatt and Excel.Tips.Net.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

\"\"<\/p>\n

David would like to create a chart that could be dynamically changed to show the sales for a single company from the worksheet.<\/p>\n","protected":false},"author":29,"featured_media":66012,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[733],"tags":[735],"class_list":["post-45575","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-tips","tag-microsoft-excel"],"yoast_head":"\nCreating Excel Charts that Automatically Update<\/title>\n<meta name=\"description\" content=\"Creating Excel Charts that Automatically Update - David would like to create a chart that could be dynamically changed to show the sales for a single company from the worksheet.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Creating Excel Charts that Automatically Update\" \/>\n<meta property=\"og:description\" content=\"Creating Excel Charts that Automatically Update - David would like to create a chart that could be dynamically changed to show the sales for a single company from the worksheet.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/\" \/>\n<meta property=\"og:site_name\" content=\"PC Matic Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/pcmatic\" \/>\n<meta property=\"article:published_time\" content=\"2013-09-10T17:55:10+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.pcmatic.com\/blog\/wp-content\/uploads\/pcmatic-fallback.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Excel Tips\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@pcmatic\" \/>\n<meta name=\"twitter:site\" content=\"@pcmatic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Excel Tips\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/creating-excel-charts-that-automatically-update\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/creating-excel-charts-that-automatically-update\\\/\"},\"author\":{\"name\":\"Excel Tips\",\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/#\\\/schema\\\/person\\\/d0f9f5c962d979029f4d178721dbaafd\"},\"headline\":\"Creating Excel Charts that Automatically Update\",\"datePublished\":\"2013-09-10T17:55:10+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/creating-excel-charts-that-automatically-update\\\/\"},\"wordCount\":362,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/creating-excel-charts-that-automatically-update\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/wp-content\\\/uploads\\\/pcmatic-fallback.png\",\"keywords\":[\"Microsoft Excel\"],\"articleSection\":[\"Excel Tips\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/creating-excel-charts-that-automatically-update\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/creating-excel-charts-that-automatically-update\\\/\",\"url\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/creating-excel-charts-that-automatically-update\\\/\",\"name\":\"Creating Excel Charts that Automatically Update\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/creating-excel-charts-that-automatically-update\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/creating-excel-charts-that-automatically-update\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/wp-content\\\/uploads\\\/pcmatic-fallback.png\",\"datePublished\":\"2013-09-10T17:55:10+00:00\",\"description\":\"Creating Excel Charts that Automatically Update - David would like to create a chart that could be dynamically changed to show the sales for a single company from the worksheet.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/creating-excel-charts-that-automatically-update\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/creating-excel-charts-that-automatically-update\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/creating-excel-charts-that-automatically-update\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/wp-content\\\/uploads\\\/pcmatic-fallback.png\",\"contentUrl\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/wp-content\\\/uploads\\\/pcmatic-fallback.png\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/creating-excel-charts-that-automatically-update\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Creating Excel Charts that Automatically Update\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/\",\"name\":\"PC Matic Blog\",\"description\":\"Tech Tips and Tricks\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/#organization\",\"name\":\"PC Matic - Top Antivirus Company in the USA.\",\"url\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/techtalk.pcmatic.com\\\/wp-content\\\/uploads\\\/PC-MaticLogo-e1472689639222.png\",\"contentUrl\":\"https:\\\/\\\/techtalk.pcmatic.com\\\/wp-content\\\/uploads\\\/PC-MaticLogo-e1472689639222.png\",\"width\":1535,\"height\":483,\"caption\":\"PC Matic - Top Antivirus Company in the USA.\"},\"image\":{\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/pcmatic\",\"https:\\\/\\\/x.com\\\/pcmatic\",\"https:\\\/\\\/www.instagram.com\\\/pcmaticusa\\\/\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/pcmatic\",\"https:\\\/\\\/www.youtube.com\\\/c\\\/PCMaticVideo\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/#\\\/schema\\\/person\\\/d0f9f5c962d979029f4d178721dbaafd\",\"name\":\"Excel Tips\",\"description\":\"The tips and ideas from ExcelRibbon.Tips.Net are designed specifically for users of Excel starting with Excel 2007. You'll find just about everything you need to become productive with the program right away. (If you need help with other versions of Excel, visit the ExcelTips site.)\",\"sameAs\":[\"http:\\\/\\\/excelribbon.tips.net\\\/\"],\"url\":\"https:\\\/\\\/www.pcmatic.com\\\/blog\\\/author\\\/exceltips\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Creating Excel Charts that Automatically Update","description":"Creating Excel Charts that Automatically Update - David would like to create a chart that could be dynamically changed to show the sales for a single company from the worksheet.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/","og_locale":"en_US","og_type":"article","og_title":"Creating Excel Charts that Automatically Update","og_description":"Creating Excel Charts that Automatically Update - David would like to create a chart that could be dynamically changed to show the sales for a single company from the worksheet.","og_url":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/","og_site_name":"PC Matic Blog","article_publisher":"https:\/\/www.facebook.com\/pcmatic","article_published_time":"2013-09-10T17:55:10+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/www.pcmatic.com\/blog\/wp-content\/uploads\/pcmatic-fallback.png","type":"image\/png"}],"author":"Excel Tips","twitter_card":"summary_large_image","twitter_creator":"@pcmatic","twitter_site":"@pcmatic","twitter_misc":{"Written by":"Excel Tips","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/#article","isPartOf":{"@id":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/"},"author":{"name":"Excel Tips","@id":"https:\/\/www.pcmatic.com\/blog\/#\/schema\/person\/d0f9f5c962d979029f4d178721dbaafd"},"headline":"Creating Excel Charts that Automatically Update","datePublished":"2013-09-10T17:55:10+00:00","mainEntityOfPage":{"@id":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/"},"wordCount":362,"commentCount":0,"publisher":{"@id":"https:\/\/www.pcmatic.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/#primaryimage"},"thumbnailUrl":"https:\/\/www.pcmatic.com\/blog\/wp-content\/uploads\/pcmatic-fallback.png","keywords":["Microsoft Excel"],"articleSection":["Excel Tips"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/","url":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/","name":"Creating Excel Charts that Automatically Update","isPartOf":{"@id":"https:\/\/www.pcmatic.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/#primaryimage"},"image":{"@id":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/#primaryimage"},"thumbnailUrl":"https:\/\/www.pcmatic.com\/blog\/wp-content\/uploads\/pcmatic-fallback.png","datePublished":"2013-09-10T17:55:10+00:00","description":"Creating Excel Charts that Automatically Update - David would like to create a chart that could be dynamically changed to show the sales for a single company from the worksheet.","breadcrumb":{"@id":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/#primaryimage","url":"https:\/\/www.pcmatic.com\/blog\/wp-content\/uploads\/pcmatic-fallback.png","contentUrl":"https:\/\/www.pcmatic.com\/blog\/wp-content\/uploads\/pcmatic-fallback.png","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/www.pcmatic.com\/blog\/creating-excel-charts-that-automatically-update\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pcmatic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Creating Excel Charts that Automatically Update"}]},{"@type":"WebSite","@id":"https:\/\/www.pcmatic.com\/blog\/#website","url":"https:\/\/www.pcmatic.com\/blog\/","name":"PC Matic Blog","description":"Tech Tips and Tricks","publisher":{"@id":"https:\/\/www.pcmatic.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pcmatic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.pcmatic.com\/blog\/#organization","name":"PC Matic - Top Antivirus Company in the USA.","url":"https:\/\/www.pcmatic.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.pcmatic.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/techtalk.pcmatic.com\/wp-content\/uploads\/PC-MaticLogo-e1472689639222.png","contentUrl":"https:\/\/techtalk.pcmatic.com\/wp-content\/uploads\/PC-MaticLogo-e1472689639222.png","width":1535,"height":483,"caption":"PC Matic - Top Antivirus Company in the USA."},"image":{"@id":"https:\/\/www.pcmatic.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/pcmatic","https:\/\/x.com\/pcmatic","https:\/\/www.instagram.com\/pcmaticusa\/","https:\/\/www.linkedin.com\/company\/pcmatic","https:\/\/www.youtube.com\/c\/PCMaticVideo"]},{"@type":"Person","@id":"https:\/\/www.pcmatic.com\/blog\/#\/schema\/person\/d0f9f5c962d979029f4d178721dbaafd","name":"Excel Tips","description":"The tips and ideas from ExcelRibbon.Tips.Net are designed specifically for users of Excel starting with Excel 2007. You'll find just about everything you need to become productive with the program right away. (If you need help with other versions of Excel, visit the ExcelTips site.)","sameAs":["http:\/\/excelribbon.tips.net\/"],"url":"https:\/\/www.pcmatic.com\/blog\/author\/exceltips\/"}]}},"_links":{"self":[{"href":"https:\/\/www.pcmatic.com\/blog\/wp-json\/wp\/v2\/posts\/45575","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pcmatic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pcmatic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pcmatic.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pcmatic.com\/blog\/wp-json\/wp\/v2\/comments?post=45575"}],"version-history":[{"count":0,"href":"https:\/\/www.pcmatic.com\/blog\/wp-json\/wp\/v2\/posts\/45575\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.pcmatic.com\/blog\/wp-json\/wp\/v2\/media\/66012"}],"wp:attachment":[{"href":"https:\/\/www.pcmatic.com\/blog\/wp-json\/wp\/v2\/media?parent=45575"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pcmatic.com\/blog\/wp-json\/wp\/v2\/categories?post=45575"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pcmatic.com\/blog\/wp-json\/wp\/v2\/tags?post=45575"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}