Trending February 2024 # How To Create A Dynamic Chart Range In Excel # Suggested March 2024 # Top 7 Popular

You are reading the article How To Create A Dynamic Chart Range In Excel updated in February 2024 on the website Kientrucdochoi.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 How To Create A Dynamic Chart Range In Excel

When you create a chart in Excel and the source data changes, you need to update the chart’s data source to make sure it reflects the new data.

In case you work with charts that are frequently updated, it’s better to create a dynamic chart range.

A dynamic chart range is a data range that updates automatically when you change the data source.

Below is an example of a chart that uses a dynamic chart range.

Note that the chart updates with the new data points for May and June as soon as the data in entered.

There are two ways to create a dynamic chart range in Excel:

Using Excel Table

Using Formulas

In most of the cases, using Excel Table is the best way to create dynamic ranges in Excel.

Let’s see how each of these methods work.

Using Excel Table is the best way to create dynamic ranges as it updates automatically when a new data point is added to it.

Excel Table feature was introduced in Excel 2007 version of Windows and if you’re versions prior to it, you won’t be able to use it (see the next section on creating dynamic chart range using formulas).

Pro Tip: To convert a range of cells to an Excel Table, select the cells and use the

To convert a range of cells to an Excel Table, select the cells and use the keyboard shortcut – Control + T (hold the Control key and press the T key).

In the example below, you can see that as soon as I add new data, the Excel Table expands to include this data as a part of the table (note that the border and formatting expand to include it in the table).

Now, we need to use this Excel table while creating the charts.

Here are the exact steps to create a dynamic line chart using the Excel table:

Select the entire Excel table.

Go to the Insert tab.

In the Charts Group, select ‘Line with Markers’ chart.

That’s it!

The above steps would insert a line chart which would automatically update when you add more data to the Excel table.

Note that while adding new data automatically updates the chart, deleting data would not completely remove the data points. For example, if you remove 2 data points, the chart will show some empty space on the right. To correct this, drag the blue mark at the bottom right of the Excel table to remove the deleted data points from the table (as shown below).

While I have taken the example of a line chart, you can also create other chart types such as column/bar charts using this technique.

As I mentioned, using Excel table is the best way to create dynamic chart ranges.

However, if you can’t use Excel table for some reason (possibly if you are using Excel 2003), there is another (slightly complicated) way to create dynamic chart ranges using Excel formulas and named ranges.

Suppose you have the data set as shown below:

To create a dynamic chart range from this data, we need to:

Create two dynamic named ranges using the OFFSET formula (one each for ‘Values’ and ‘Months’ column). Adding/deleting a data point would automatically update these named ranges.

Insert a chart that uses the named ranges as a data source.

Let me explain each step in detail now.

Below are the steps to create dynamic named ranges:

Go to the ‘Formulas’ Tab.

The above steps have created two named ranges in the Workbook – ChartValue and ChartMonth (these refer to the values and months range in the data set respectively).

If you go and update the value column by adding one more data point, the ChartValue named range would now automatically update to show the additional data point in it.

The magic is done by the OFFSET function here.

In the ‘ChartValue’ named range formula, we have specified B2 as the reference point. OFFSET formula starts there and extends to cover all the filled cells in the column.

The Same logic works in the ChartMonth named range formula as well.

Now all you need to do is insert a chart that will use the named ranges as the data source.

Here are the steps to insert a chart and use dynamic chart ranges:

Go to the Insert tab.

With the chart selected, go to the Design tab.

In the Series value field, enter

=Formula!ChartValues

(note that you need to specify the worksheet name before the named range for this to work).

In the ‘Axis Labels’ dialog box, enter

=Formula!ChartMonths

That’s it! Now your chart is using a dynamic range and will update when you add/delete data points in the chart.

A few important things to know when using named ranges with charts:

There should not be any blank cells in the chart data. If there is a blank, named range would not refer to the correct dataset (as the total count would lead to it referring to less number of cells).

You need to follow the naming convention when using the sheet name in chart source. For example, if the sheet name is a single word, such as Formula, then you can use =Formula!ChartValue. But if there is more than one word, such as Formula Chart, then you need to use =’Formula Chart’!ChartValue.

You May Also Like the Following Excel Tutorials:

You're reading How To Create A Dynamic Chart Range In Excel

How To Create A Combo Chart In Microsoft Excel

When you want to display different data sets visually, you can create a combination chart. If you want to show something like sales with costs or traffic with conversions, a combo chart in Microsoft Excel is ideal.

We’ll show you how to create a combo chart in Excel as well as customize it to include the elements you need and give it an attractive appearance.

Table of Contents

How to Create a Combo Chart in Excel

You have a few ways to create a combo chart in Excel. You can convert an existing chart, select a quick combo chart type, or set up a custom chart.

Convert an Existing Chart to a Combo Chart

If you already have a chart showing your data, like a bar chart or even a pie chart, you don’t have to delete it and start from scratch. Simply turn it into a combo chart.

Select your current chart and go to the

Chart Design

tab.

Choose

Change Chart Type

in the Type section of the ribbon.

When the chart window opens, pick

Combo

on the left.

Then, select one of the combo chart layouts at the top and customize the series at the bottom.

Choose

OK

and you’ll see your new chart replace the original one.

Select a Quick Combo Chart Type

Excel offers three combo chart types that you can pick from for your data.

Select your data set and go to the Insert tab.

In the Charts group, choose the Insert Combo Chart drop-down arrow to see the options. Pick from a clustered column with a line chart, a clustered column and line chart with a secondary axis, or a stacked area and clustered column chart.

Create a Custom Combo Chart

If you don’t have an existing chart and prefer to customize the series and axis for the combo chart from the start, you can create a custom chart.

Select your data set and go to the

Insert

tab.

Choose the

Insert Combo Chart

drop-down arrow and pick

Create Custom Combo Chart

.

When the chart window opens, you’ll see four combo chart types at the top. Select one of these as the base for your custom chart.

At the bottom, you’ll see your data series, chart types, and options to pick a secondary axis. As you make your adjustments, you’ll see a nice preview of the combo chart directly above.

While a column chart and line graph work well together, you can choose a different chart type for each data series if you like. Select the drop-down box below

Chart Type

to the right of the series you want to change and select the new chart type.

By default, the first data series displays on the primary axis. However, you can change this or simply add a secondary axis using the

Secondary Axis

check boxes on the right side.

When you finish creating your custom combo chart, pick

OK

to save it and place it on your worksheet.

How to Customize a Combo Chart

Once you choose and insert your combo chart, you may want to add more elements or give the chart some pizzazz. Excel offers several features for customizing a chart.

Go to the Chart Design Tab

For basic appearance features and chart elements, select your chart and go to the Chart Design tab.

Starting on the left side of the ribbon, you can use the Add Chart Element drop-down menu to add and position items like the chart title, data labels, and legend.

To the right, use the Quick Layout menu to change the layout to include and position elements without having to do so one-by-one.

In the Chart Styles section, you can use the Change Colors drop-down menu to pick a different color scheme or the Styles box to choose a whole new design.

With the remaining options on the ribbon, you can switch columns and rows, change the chart data selection, pick a new chart type, or move the chart to another sheet.

Open the Format Chart Area Sidebar

Use Chart Options or Text Options at the top of the sidebar depending on which item you want to change. You can then use the tabs directly beneath to make your changes.

Chart Options: Change the fill and border styles and colors, add effects like a shadow or soft edge, and set the size or position for the chart.

Text Options: Change the fill or outline styles and colors, add effects, and position or align the text.

Use the Chart Buttons (Windows Only)

One more way to make adjustments to your chart is to use the buttons that display on the right side of it. These are currently only available in Microsoft Excel on Windows, not Mac.

Chart Elements (plus sign): Like the Chart Elements drop-down box on the Chart Design tab, you can add, remove, and position items on the chart.

Chart Style (paint brush): Like the Chart Styles section on the Chart Design tab, you can pick a different color scheme or style for your chart.

Chart Filters (filter): With this button, you can check or uncheck the details in your dataset that you want to display on your chart. This gives you a quick way to view only specific chart data by hiding other details temporarily.

Now that you know how to create a combo chart in Excel, look at how to make a Gantt chart for your next project.

Step Chart In Excel – A Step By Step Tutorial

Watch Video – Creating a Step Chart in Excel

A step chart can be useful when you want to show the changes that occur at irregular intervals. For example, the price rise in milk products, petrol, tax rate, interest rates, etc.

Let’s take the example of a Petrol hike in India. It can happen any day (as decided by the government) and the value remains constant between these changes. In such a case, a step chart is the right way to visualize such data points.

Unfortunately, Excel does not have an inbuilt feature to create a step chart, however, it can easily be created by rearranging the data set.

Step Chart in Excel

In this tutorial, you’ll learn:

The difference between a Step Chart and a Line Chart.

Creating a Step Chart using the “Line Chart technique”.

A line chart would connect the data points in such a way that you see a trend. The focus in such charts is the trend and not the exact time of change.

On the contrary, a step chart shows the exact time of change in the data along with the trend. You can easily spot the time period where there was no change and can compare the magnitude of change in each instance.

Here is an example of both the line chart and step chart – created using the same data set (petrol prices in India).

Both of these charts look similar, but the line chart is a bit misleading.

It gives you the impression that the petrol prices have gone up consistently during May 2024 and June 2024 (see image below).

But if you look at the step chart, you’ll notice that the price increase took place only on two occasions.

Similarly, a line chart shows a slight decline from September to November, while the step chart would tell you that this was the period of inactivity (see image below).

Hope I have established some benefits of using a Step Chart over a Line Chart. Now let’s go ahead a look at how to create a step chart in Excel.

First things first. The credit for this technique goes to Jon Peltier of chúng tôi He is a charting wiz and you will find tons of awesome stuff on his website (including this technique). Do pay him a visit and say Hello.

Here are the steps to create a step chart in Excel:

Have the data in place. Here I have the data of petrol prices in India in 2024.

Have a copy of the data arranged as shown below.

The easiest way is to construct the additional data set right next to the original data set. Start from the second row of the data. In cell D3, enter the reference of the date in the same row (A3) in the original data set. In cell E3, enter the reference of the value in the row above (B2) in the original dataset. Drag the cells down to the last cell of the original data.

Copy the original data (A2:B18 in the above example), and paste it right below the additional dataset that we created.

You will have something as shown below (the data in yellow is the original data and green is the one that we created). Note that there is a blank row between the header and the data (as we started from the second row). If you are too finicky about how data looks, you can delete those cells.

You don’t need to sort the data. Excel takes care of it.

That’s it! You’ll have the step chart ready.

How does this work:

To understand how this works, consider this – You have 2 data points, as shown below:

What happens when you plot these 2 data points on a line chart? You get a line as shown below:

Now to convert this line chart into a step chart, I need to show in the data that the value remained the same from 1 to 2 January, and then suddenly increased on 2 January. So I restructure the data as shown below:

I carry forward the data to the next date when the change in value happens. So for 2nd January, I have two values – 5 and 10. This gives me a step chart where the increase is shown as a vertical line.

The same logic is applied to the restructuring of the data while creating a full-blown step chart in Excel.

While it would be nice to get an inbuilt option to create step charts in Excel, once you get a hang of restructuring the data, it won’t take more than a few seconds to create this.

Related Tutorials:

Other Excel Charting Tutorials You Might Like:

Excel Sorted Dynamic Unique List

I really wanted the title of this post to be “Excel Sorted Dynamic Unique List Ignoring Blanks and Errors”, but I didn’t want to brag 😉

It has never been so easy to extract a unique or distinct list of values in Excel than it is now that we have Dynamic Array formulas*.

*Dynamic Array formulas are only available in Office 365. I’ll provide links to an alternative for Excel 2023 and earlier versions at the bottom of this post.

Note: At the time of writing, Dynamic Arrays are only available in Office 365 and are currently in beta on the Insiders channel. We don’t have an ETA for when they will be available to all Office 365 users yet. And to be clear, Excel 2023 does not come with Dynamic Arrays. The only way to get them is with Office 365 …or wait until Excel 2023 (?) comes out.

Excel Sorted Dynamic Unique List Formula

The formula for extracting a list of sorted unique values that ignore errors and blanks is super easy.

Step 1: Format the source data in an Excel Table. That way when new rows are added, or rows removed, the formula will automatically pick up the changes.

Step 2: The formula.

The most complicated part of the formula, which isn’t really that complicated, is the FILTER function, that enables us to return the list excluding errors and blank cells, among other things. The UNIQUE and SORT functions then enclose FILTER.

The FILTER function takes the following arguments:

=FILTER(array , include, [if_empty])

The array argument is the table or range of cells you want to filter.

The include argument allows you to insert a logical test specifying which values to include.

The if_empty argument is an optional value to return if there are no records that match our ‘include’ criteria. We don’t need it in this example.

The formula is:

=

SORT(

UNIQUE(

FILTER(Table1[Names]

,

NOT(ISBLANK(Table1[Names]))

*

NOT(ISERROR(Table1[Names]))

)

)

)

FILTER the Names column of Table1 and return a list of values, where the Names are not blank AND the Names are not errors.

The NOT(ISBLANK(Table1[Names])) and NOT(ISERROR(Table1[Names])) formulas return a list of TRUE and FALSE Boolean values as shown below:

=SORT( UNIQUE( FILTER(Table1[Names], {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE}* {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE}) ) )

The FALSE values are discarded before passing the list to the UNIQUE function. The UNIQUE function then removes the duplicate names before passing the list to the SORT function, which sorts it in ascending order.

If you prefer the list sorted in descending order you can add  ‘,,-1’ to the SORT formula like so:

=SORT( UNIQUE( FILTER(Table1[Names], NOT(ISBLANK(Table1[Names]))*NOT(ISERROR(Table1[Names])) ) )

, ,-1

)

 

Dynamically Update

And because I formatted the source for my Excel Sorted Dynamic Unique list in an Excel Table, when I add new names it automatically updates:

Notice how the results automatically ‘spill’ to the cells below? This is the new Dynamic Array formula functionality available to Office 365 users.

Unique Lists in Earlier Versions of Excel

If you’re not fortunate enough to have Office 365 and these handy dynamic array formulas, you can use one of the techniques described here.

Related Lessons

SORT Function – there’s more to the SORT function than I’ve demonstrated here. You can also sort multiple columns and choose which column to sort by.

UNIQUE Function – the UNIQUE function can also handle multiple columns and differentiate between unique and distinct values.

FILTER Function  - you can filter more than one column and FILTER can handle OR criteria as well as AND criteria.

NOT Function – Not enables us to check if a logical test doesn’t exist.

ISBLANK Function and ISERROR Function – the IS functions check to see if a condition exists and return a TRUE FALSE, depending on the outcome. There are more IS functions at the link above.

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the Excel Workbook . Note: This is a .xlsx file please ensure your browser doesn’t change the file extension on download.

Return The First And Last Values In A Range

I’ve been asked many times how to find either the cell reference of the first or last value in a range, or even return the values from those cells, and there are many ways to do it.

As usual I’m going to share the methods I think are the best.

Note: The difference between returning the value or cell reference is subtle yet significant. You’ll see later.

Here’s our range. You can see that column C contains numbers and column D contains both numbers and text, and both columns contain blanks.

Find the First Value in a Range

Like I said, I’ve seen many ways to find the first value in a range but one formula stands out from the rest for its simplicity.

Drum roll…..

Entered with CTRL+SHIFT+ENTER as it’s an array formula.

Why it’s so special:

The INDEX function returns a reference to a cell and when used on its own, like the above example, it returns the value in that cell.

This formula works with text or numbers.

How it Works

If you’re new to the INDEX & MATCH dynamic duo take a few moments to read this tutorial first. These are two ‘must know’ power functions.

The MATCH function is the star in this formula:

If we look at how the MATCH function evaluates it looks like this:

Step 1: Evaluate the logical test and return an array of TRUE and FALSE values:

MATCH(

TRUE

,

{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}

,

0

)

Step 2: Return the position number of the first ‘TRUE’ result in the array:

3

That is the first TRUE is the 3rd item in the array returned by the logical test.

If we give this result to the INDEX formula we get:

=INDEX(C$2:C$13,

3

)

Which evaluates to cell C4 and coincidently the value in C4 is 3.

That is cell C4 is the 3rd cell in the range C2:C13 and it contains the number 3.

That’s it! Done.

Find the Last Value in a Range

Unfortunately finding the last value in a range isn’t quite as straight forward as finding the first, but it’s not too tricky.

Again entered with CTRL+SHIFT+ENTER as it’s an array formula.

Let’s zoom in on the MAX(IF part of the formula:

In English:

Test the cells in the range C2:C13 for blanks, if they’re not blank i.e. TRUE then return the corresponding number from the array of values returned by the ROW function, now just tell me the MAX value.

It evaluates like so:

Step 1 – evaluate the logical test:

MAX(

IF({FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},

ROW($A$1:$A$12)

)

)

Step 2 – evaluate the ROW function:

MAX(

IF{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE},

{1;2;3;4;5;6;7;8;9;10;11;12}

)

)

Step 3 – complete the IF formula by replacing the TRUE’s with the corresponding numbers returned by the ROW function:

MAX(

{FALSE;FALSE;

3

;

4

;FALSE;

6

;FALSE;

7

;FALSE;

10

;FALSE;FALSE}

)

Step 4 – find the MAX of the values:

10

Now we can give that 10 to INDEX and we get this:

=INDEX(C$2:C$13,

10

)

=C11, and the value in C11 is 4.

This formula works for both text and numbers.

Find the Last Number in a Range

If your range contains only numbers, you can use this formula to find the last cell containing a number in the range:

=INDEX(C$2:C$13,MATCH(1E+100,C$2:C$13,1))

Note: for a change this isn’t an array formula.

The trick with this formula is the 1E+100.

1E+100 is just a really big number. You can use any number here as long as it is bigger than any number in your range.

Side note: You might have seen 9.99999999999999E+307 used in formulas before and that’s because it’s the biggest number you can enter into Excel.

The problem with 9.999 blah, blah, blah is that I find it really hard to remember how many 9’s to enter. Whilst 1E+100 is not the biggest number, it is still big enough to work in most scenarios and comes with the bonus that it’s a whole load easier to remember.

Ok, sorry I digress… when you ask MATCH  to find a number that is bigger than any of the numbers in your range it will return the position of the last value in the range when you use 1 (for less than) as the ‘match_type’ argument.

Remember the syntax for MATCH is

MATCH(

lookup_value

,

lookup_array

,

[match_type]

)

Lookup the biggest number you can imagine, in the range C2:C13, if you can’t find it then return the position of the last number you find.

Remember this formula only works with numbers, not text.

Using INDEX to Return a Reference

“The difference between returning the value from a cell or a reference to a cell is subtle yet significant”

Well, since we know that the INDEX function returns a reference to a cell we can use the above formulas to return a range that starts with the first occupied cell and ends with the last.

To do this we simply join the two formulas together with a colon, which is the range operator:

Remember the first INDEX formula returns a reference of C4 and the second returns C11 so the above formula returns the following range:

C4

:

C11

What’s the point when you could just enter C4:C11? Well, because using INDEX gives us a dynamic range that will grow or shrink with your data.

Don’t forget if your range contains text you need to use the MAX(IF version:

In most cases you’ll need to enter a formula containing either of the above dynamic ranges using CTRL+SHIFT+ENTER, or you can set it up as a dynamic named range for use in your formulas.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the Excel Workbook . Note: This is a .xlsx file please ensure your browser doesn’t change the file extension on download.

How To Create A Share Folder In Dropbox

If you have not used, tried or even heard of Dropbox, probably you are still living in the stone age. Dropbox is one of the best (if not, the best) online file storage solution for all platforms, including your iPhone and Android phone. You can easily and quickly sync your desktop files to the cloud and access it from other computers. Other than file storage, Dropbox also allows you to share your files publicly (just place your files in the Public folder) or share a folder with friend(s).

Here’s how you can create a share folder and share it with your friends.

First of all, if you have not used Dropbox, download and install the desktop client, register a Dropbox account and get yourself started.

Open your file manager and navigate to the Dropbox folder. Create a new folder that you want to share (let’s call it the Share folder).

This will open your default browser and launch the Dropbox Web interface.

Once the folder is shared, Dropbox will immediately sync it to your desktop. You should now see a new icon on the share folder.

The recipient will receive an email that looks like the following:

That’s it.

What you can/cannot do with the share folder

You can’t share a folder from within your Public folder

You can’t share a folder inside another shared folder. The folder is already shared

Anyone you’ve invited to a folder can then add, delete or change files within that folder

Anyone you’ve invited to a folder can then invite others to join the folder

Only the creator of the shared folder can remove people from the folder

If two people both open and edit a file in a shared folder at the same time, Dropbox will save both of their changes, but in separate files. It does not try to automatically combine or merge changes.

Damien

Damien Oh started writing tech articles since 2007 and has over 10 years of experience in the tech industry. He is proficient in Windows, Linux, Mac, Android and iOS, and worked as a part time WordPress Developer. He is currently the owner and Editor-in-Chief of Make Tech Easier.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sign up for all newsletters.

By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time.

Update the detailed information about How To Create A Dynamic Chart Range In Excel on the Kientrucdochoi.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!