A Must-know Excel Charting Trick!

Create table and chart that will dynamically update to display only the last number of data points. In other words, as data is added to a data set, the chart will update to only show however many data points you want to see. Any data points before this will "drop off" from the chart not not be show.
- - - - - - - - - - - - - - - - - - -
Full Playlist: • How do SPC control cha...
- - - - - - - - - - - - - - - - - - -
Check out my channel for more videos and tutorials for engineers!
Like, follow, share and don't be afraid to drop me a comment/message with feedback, questions, or video suggestions!
Thanks for watching!
Channel Link: / @theengineeringtoolbox...
- - - - - - - - - - - - - - - - - - -
Other great content!
Desc: [URL]
- - - - - - - - - - - - - - - - - - -
Follow and interact!
Twitter: / theengtoolbox
Facebook: / theengineeringtoolbox
- - - - - - - - - - - - - - - - - - -
#EngineeringToolbox
#EngineeringTutorials
#Engineering

[@snethembankomo]

ubani lomjita? you are a life saver bro ill be acing my lab reports from now on

[@TheEngineeringToolboxChannel]

Another shout out to Vamshi for asking good questions! I am always more than happy to help and your questions often inspire ideas for content that I can create and share with the rest of my viewers. Hope you enjoy this awesome trick. Thanks for watching. Evan

[@vamshia7408]

Thanks for the video Evan! Keep them coming!!

[@TheEngineeringToolboxChannel]

Will do. Thanks again for the engagement.

[@javiergorostiaga5537]

Good show! Thanks!

[@connozac1109]

really great following along with the control charts, thanks a lot. I would suggest though for dynamic dates to create a pivot table and then a pivot chart. Pivot charts have slicers built in with more flexibility. Vlookups can become resource heavy as the dashboard is built out.

[@TheEngineeringToolboxChannel]

Good point. If you don't mind updating the slicer and refreshing the pivot table as data is added then I would go this route as well. In fact, I would just add a date range slicer to the source table (slicers work on tables too, not just pivot tables). But if you are looking for a chart that updates completely automatically to a certain time range with no need to refresh and change filters then this would be the way to go.

[@allabout1135]

Thanks! Please more data analisys concepts. Thanks for sharing You knowledge.

[@TheEngineeringToolboxChannel]

Thanks for the input! Anything in particular? Thanks for watching.

[@kylebell5637]

Great idea. Have you found a way to select a particular week or month, say, and have it pull the data from that month from the larger table even if number of entries may be different between periods?

[@TheEngineeringToolboxChannel]

Hey Kyle, interesting thought. It might be possible to adapt this method to achieve that. There are some other ideas I have as well. Feel free to shoot me an email and I’d be happy to discuss what you’re looking for and see if we can come up with [e-mail] (Full transparency: I won’t try to sell any services - I am just an enthusiast trying to help people and grow my channel)

[@TAnotepad]

Thank you.

[@movaziri7340]

Great, if I want to give the user this privilege to just insert the number as the rows of this table (e.g. the number of days of the data to be plotted), how could this formula take into account that? thanks

[@broliesan]

Hi there. Your tool is awesome and very usefull. However when I try to add the line for the index and move the date column the chart uses the index colomn as a x value and messes up the chart. I must overlook something basic but I can't figure it out.

[@TheEngineeringToolboxChannel]

That is normal behavior. You may have to modify or rebuild the chart after setting up the table. Remember, If the date uniquely identifies each row then an index column is not needed.

[@vtyankeefan]

All this does is subtract 1 day from the largest date x times. If you don't have data for each date (think weekends) then it doesn't work at all.

[@TheEngineeringToolboxChannel]

Like I said in the video you can use an index column if you want to just show x number of datapoints. 1,2,3, etc.

[@adamf468]

My dates are having issues updating for the last 30 days. The max date is always updating automatically each day but the furthest date back (30th day and other dates in between) don't always update automatically. My formula is =MAX('On Track Inventory'!A35:A5012)-(ROWS(On_Track_Rolling)-ROW())-1. Any ideas why?