Excel - Mastering Dynamic Charts in Excel: Displaying the Last 6 or 10 Points - Episode 1013

Microsoft Excel Tutorial: Mastering Dynamic Charts in Excel: Displaying the Last 6 or 10 Points.

Welcome back to the MrExcel NetCast, where we dive into all things Excel. I'm Bill Jelen, and in this episode (1013), we're going to revisit a topic from episode 993. In that episode, we talked about how to create a dynamic range for a chart, so that it automatically extends as you add more data. However, we received some feedback from our viewers, specifically from Andre in England, who pointed out that this method will cause the chart to keep growing forever. So, in this episode, we'll be addressing that issue and showing you a solution for displaying only the last 6 or 10 points on your chart.

In episode 993, we used a named range to create our dynamic chart. However, in order to limit the number of points displayed, we need to make a slight adjustment to the formula. As Andre suggested, we will now be subtracting a number from the end of the range, rather than adding it. For example, if we want to display the last 6 points, we will subtract 7 from the end of the range. This may seem counterintuitive, but trust me, it works. And as always, we'll be providing you with the necessary formulas to make this process as easy as possible.

But that's not all. In episode 994, we showed a faster way to create a dynamic chart using the Ctrl+T shortcut in Excel 2007 (or Ctrl+L in Excel 2003). However, our very own Tracy, who manages all of our VBA projects, caught a mistake in my demonstration. As it turns out, the feature works slightly differently in Excel 2003 compared to 2007. While it does update when adding new rows, it does not update when adding new columns. But don't worry, we have a workaround for that as well. And as always, we'll be providing you with a step-by-step guide and some helpful tips to make sure you can easily implement this in your own work.

So, a big thank you to Andre from England for bringing this issue to our attention, and to Tracy from South Dakota for keeping me on my toes. And as always, thank you for tuning in to the MrExcel NetCast. We hope you found this episode helpful and informative. Don't forget to like, comment, and subscribe for more Excel tips and tricks. And if you have any suggestions for future episodes, please let us know in the comments below. Until next time, this is Bill Jelen signing off. Happy charting!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/
Andre provides a set of formulas to chart the last rolling six months of data. Episode 1013 shows you how.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Table of Contents:
(00:00) Introduction to MrExcel NetCast with Bill Jelen
(00:10) Challenge for viewers to solve
(00:22) Creating a dynamic range for charts
(00:35) Alternative method for showing last 6 or 10 points
(01:01) Testing the method with new data
(01:13) Faster method using Ctrl T or Ctrl L
(01:24) Correction from viewer Tracy
(01:46) Feature works for adding new rows, not columns
(02:06) Alternative method for adding new columns
(02:16) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Ctrl L
Ctrl T
Dynamic range
Excel 2003
Formulas
Last 10 points
Last 6 points
Pivot table
Transpose
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152410/

Видео Excel - Mastering Dynamic Charts in Excel: Displaying the Last 6 or 10 Points - Episode 1013 канала MrExcel.com