Press "Enter" to skip to content

How to Create Amazing Auto Expanding Charts: Excel Dynamic Charts Tutorial: With FREE Download

FREE Finished Chart:
FREE Shortcuts Cheat Sheet »»
I’ll step you through an amazing auto expanding dynamic chart which automatically highlights the columns with the most sales and highest profit percentage. I’m going to show you exactly how you can produce this Excel chart in this video.

We’re going to use several techniques but the main technique is a dynamic named range. You don’t really need to know much about it, as in this video I’m going to talk you through it slowly enough so that you can learn about it.

What we’re going to do is take areas and give them names, but we’re going to make those areas automatically expand to cover everything as new data appears. We then link those named ranges to the chart and then it allows it to expand. Once we have this expanding chart, which is pretty amazing in itself, we then want to start highlighting different columns in different colours depending on different things. I’m going to use two highlights, one for maximum sales and one for maximum profit percentage, but you could highlight anything you want using the exact same technique.

The first thing that we’re going to do is set up our named ranges. I want to keep the names as short as possible because you have to type them manually into the chart and you don’t want to be creating big long names that you can’t remember exactly. For my named ranges I use a lowercase ‘n’ at the beginning of the word, which lets me see straight away that they are named ranges. This is particularly useful in VBA or in these charts, as I know that what I’m seeing is a named range. It helps with formulas to I feel. Within the named range definition, we’re going to make use of two particular functions in excel. We’re going to use a COUNT function to count the number of numbers in the sales column, and that’s going to give us our auto expanding feature, and use that within an OFFSET formula to pick up a range.


Here at Up4Excel we’re on a mission to help YOU:

» Get your Excel skills UP and your task time DOWN
» Focus on shortcuts and fast impressive results
» Improve your productivity and free up your time

Everyone will assume you work 24 hours a day to produce the kind of output you’ll be producing in no time…. with the help of Up4Excel training.

Here’s some of the things you could do with the time you’re going to save:
» Invest in yourself, saving even more time with Up4Excel training videos
» Spend more of your time on interpreting rather than compiling reports
» Get ahead of the game and accelerate your next promotion
» Or perhaps leave work on time, or even early, and enjoy your home life

We release a video every week, packed full of ways to save time and impress those around you. Make sure you subscribe, and hit the bell icon, so you don’t miss out and fall behind!

Here are links to some other videos you will find useful on chart techniques:

» Excel Charts Made Easy: Excel Charts and Graphs for Beginners

» 7 Tips for Improving Excel Chart Appearance

» How to Forecast with Excel Chart Trendlines

» How to Create 2 Axis Chart in Excel

» How To Create A Clustered Stacked Column Chart In Excel

The Webcam I Use:
Logitech C920 HD Pro Webcam, Full HD 1080p

Recommended Advanced Formulas Book:
Ctrl+Shift+Enter: Mastering Excel Array Formulas (by Mike Girvin of ExcelIsFun fame)

#Up4Excel #Up4ExcelCharts #Up4ExcelAdvanced

Download the workbook with free working chart at:

FREE Shortcuts Cheat Sheet »»


0 0 vote
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
8 months ago

I'll step you through an amazing auto expanding dynamic chart which automatically highlights the columns with the most sales and highest profit percentage. You can download the spreadsheet and work along or just use it as a finished template. Get it here

Samsung Aircel
Samsung Aircel
8 months ago

Great video!!! I requested for the chart online!

Would love your thoughts, please comment.x