Create Amazing Auto Expanding Charts: Excel Dynamic Charts Tutorial

You can have this amazing auto expanding dynamic chart, which automatically highlights the columns with the most sales and highest profit percentage. You'll learn exactly how you can produce and use this Excel chart in this video.

Excel charts that expand are referred to as dynamic auto expanding charts. In this Excel dynamic charts tutorial you will learn how to make dynamic charts in Excel. These amazing auto expanding charts use an Excel dynamic named range and offset to collect the data for the chart. Technically it's an Excel dynamic named range offset count, because the count is used to determine how large the offset named range should be. The key point is that this Excel chart grows with data, because of this dynamic named range offset count, leading to expanding excel charts - hey presto, auto expanding charts.

You'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 you'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. You 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, you then want to start highlighting different columns in different colours depending on different things. You're 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 you're going to do is set up our named ranges. You 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 are links to some other videos you will find useful on chart techniques:

» 7 Tips for Improving Excel Chart Appearance

» How to Forecast with Excel Chart Trendlines

» How To Create A Clustered Stacked Column Chart In Excel

======================================================================================
#Up4Excel
#Up4ExcelAdvanced
#Up4ExcelCharts

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.

We release videos each week, packed full of ways to save time and impress those around you.
💎 Don't miss out and fall behind.....
🅾 SUBSCRIBE NOW 🅾

======================================================================================
🎁 Your small gift will help me make better videos for you and others.

======================================================================================

[UpExcel]

✅ FREE Access to ALL Up4Excel Files. Includes Excel Templates, Training Workbooks, Example Data, Cheat Sheets and more. New Content Added Weekly!

[wayneedmondson]

Nice one John! Thanks for sharing these great dynamic charting tips :)) Thumbs up!!

[Hg]

How to create auto expand to the PivotTables