All Resources

Top 5 Time-Saving Tips to Automate Excel Charts

Data analysis, communication, and visualization continue to grow in importance and popularity. At Marquee, Data Analysis and Communications with Excel, Power BI, and Python for Finance Professionals (Part 1 and Part 2) are among our fastest-growing, highest-demand courses.

While Power BI and Python are powerful data representation tools, the majority of professionals continue to chart their data in Excel (assuming their data sets have not outgrown Excel’s capabilities). We have spent countless late nights tediously creating and updating charts in Excel, which is why we especially want to share these immediately-applicable, time-saving tips to automate your charts:

  1. Reference a Dynamic Data Source. If you expect your data set to grow, we recommend referencing a dynamic data source for your chart. For example, the chart on the left was created using all available sales data on the 5th day of the month. The chart on the right was created using all available data on the 10th

    If the first chart was created by sourcing a static range (i.e. A2:A6), then on day 10 you would have to tediously update your chart manually by selecting an updated static range (i.e. A2:A10). However, if you originally created the first chart by sourcing a dynamic range (for example, you can create and reference a Table or a Dynamic Named Range), then, on day 10, your chart will automatically be updated.

  2. Ensure All Text is Dynamic. We want all aspects of our charts to update automatically, including any text in the chart (such as data labels, sub-titles, and titles). In our example above, we want to ensure the title automatically updates along with our data when we update the chart on day 10. In order to do so, we used Excel’s Concatenate Function to make the title dynamic.
  3. Link Titles to Dynamic Text. Next, we link the chart title to the dynamic text to automatically update the chart title. In this case, we selected (left-clicked) the chart title, hit “=” (you will notice an “=” sign in the formula bar), and linked to cell D1 (which contains our concatenate function). The same technique can be applied to axis titles and sub-titles.
  4. Link Text Boxes to Dynamic Text Too. Text boxes can also be linked to cells in Excel. However, when you click on the text box, you must type “=” directly into the formula bar (or hit F2 to access the formula bar) because typing “=” after clicking on the text box will treat the “=” as text within the text box. When inserting a text box into a chart, it’s also helpful to group the text box with the chart (either by selecting the chart before inserting the text box, or by highlighting both the chart and text box > right click > Group). If the two are ungrouped, the text box will not move when the chart is moved.
  5. Paste as a Linked Picture. If you need to paste a copy of your chart to another area of your worksheet or workbook, it’s not ideal to paste as a picture because a picture is not “live” and will therefore not update when you change your original chart. Instead, copy and paste your chart as a Linked Picture (select all of the cells behind your chart > CTRL + C > Home > Paste > Linked Picture). Note the Linked Picture is “live” (it’s a “live” view of the cells you highlighted before copying) and will therefore update as you change your chart. You may want to turn off gridlines (View > Gridlines) so they don’t appear in your Linked Picture.

We hope you found this article helpful. Did we miss any of your favourite quick, time-saving tips for automating charts in Excel? If so, please let us know at info@marqueegroup.ca.