Up next in 10
How to Create Dynamic Charts in Excel
2K views
Jan 1, 2025
Learn how to create dynamic charts in Excel with this step-by-step tutorial! Discover techniques to make your charts automatically update as your data changes. Perfect for professionals and students looking to visualize data effectively. Master Excel's dynamic charting tools and take your data presentations to the next level. Watch now! Learn more here: https://excelweez.com/
View Video Transcript
0:00
hi and welcome back to our YouTube
0:03
channel on this channel we are going to
0:05
show you how to create Dynamic chart
0:08
using Excel a dyamic chart is a chart
0:11
that you can that automatically updates
0:14
as you key in new data for example on
0:17
this we have on column A with month
0:20
column B with sales if I key in uh let's
0:24
say may uh
0:27
August and key in like 200
0:31
you'll see it on on the chart it
0:34
automatically puts it in August but if
0:37
you're doing this in the other way just
0:39
you'd have
0:41
to every time uh select your data and
0:46
put in a new chart every other time so
0:51
the purpose of this video is to show you
0:53
two ways you could actually do this the
0:55
very first method we are going to use is
0:58
we'll use uh the use of tables and the
1:01
second one will use the formula of
1:05
offset we have this Excel sheet Excel
1:08
sheet sheet one which has column a month
1:11
column B Sales so what we'll do is the
1:15
very first thing you need to do you
1:17
select the range of your
1:19
data click on insert uh under tables
1:23
click on table ensure the a popup will
1:27
appear and there's a check box as how
1:30
has my table as headers ensure that this
1:33
has is checked on otherwise this uh will
1:36
not work so once it's checked click on
1:40
okay and then now uh move on to insert
1:44
under charts or over chart choose a
1:47
chart of your choosing you could choose
1:50
2D column 3D bar 3D bar whichever that
1:55
you need suits you but for this example
1:57
we'll take uh two de column here it is
2:02
2D column it has the month of January to
2:05
July as it is on the sales here so if we
2:08
key in
2:12
August August with the data of 500 you
2:16
see it keys in as you update new
2:21
data so a let's say September
2:27
[Applause]
2:31
it updates as you do so this is the
2:34
easiest way you can use a dynamic chart
2:37
it's the
2:38
same uh it's the same with all other
2:41
charts here that is could be the B chart
2:45
it could be the hierarchy chart it also
2:47
could be the waterfall funel stock
2:50
surface or R chart in short whichever
2:53
chart that you choose so long as you
2:56
click and make your data as a table and
2:59
and then insert the uh in insert the
3:03
chart it's going to work as
3:07
intended creating Dynamic chart using
3:10
the offset formula the first step that
3:13
you need to
3:14
do go on your sheet click open your
3:17
sheet click on formulas click on the
3:20
name manager on this popup table click
3:23
on new to create new uh data names for
3:28
the data that you'll use on your Dynamic
3:30
table on the name in this case we'll
3:33
start on our values in column A so our
3:36
name is going to be
3:40
month and then right down here where it
3:43
says refers to this is where you're
3:45
going to key in your offset
3:49
formula you begin with
3:54
offset the first parameter for this
3:57
particular formula you'll come and click
4:01
on A2 that is on
4:03
January a comma this is your starting
4:06
point for the data that you need for
4:08
your chart then you're going
4:11
to key in 0 comma 0 comma for those zero
4:16
represents the row offset and the second
4:19
zero represents the column offset then
4:23
we are going to move on to the next part
4:25
of the formula that is Count a count a
4:29
is going to be responsible for the
4:30
height of the data that we need so in
4:33
this case since we are using we are
4:36
looking at the data in colum a we are
4:38
going to select the whole
4:41
column it and then close your
4:46
parentheses then minus one the reason
4:49
why we are putting in minus one is to
4:51
ensure that once the data is being
4:53
calculated they do not count the month
4:56
and the sales the headers but in case
4:59
you do not have uh headers on your data
5:03
you do not need to put in to keep in1
5:06
minus one so once you're done click on
5:11
okay since we have two columns we're
5:13
going to do to repeat the same process
5:15
but this time it's on the column of B on
5:19
sales so the first step as we said uh we
5:23
move on to formulas name manager your
5:26
popup will still be around so click on
5:29
new
5:30
repeat the same process in this case the
5:33
our name is going to be sales since we
5:35
are dealing with the sales column on
5:38
refers two we repeat the same offset
5:41
formula you begin with
5:44
offset and the first parameter for your
5:46
offset is going to be B2 which is a
5:50
starting point of your data comma zero
5:54
for the row
5:57
offset comma another zero for the column
6:01
offset and then count count a count a as
6:05
we said earlier it represent the height
6:08
of the data that you're going to be
6:10
using so in this case since we are on
6:13
column B we'll have to select the whole
6:16
column close your parentheses and then
6:19
minus one once again minus one because
6:22
you're going to get rid of this header
6:27
header and click on okay
6:30
once that has been done close
6:33
your close
6:35
it once you're done with that move on to
6:40
insert insert and now choose a chart of
6:43
your choosing once again we'll use a 2d
6:46
column chart click on it click on your
6:50
chart right click on your chart to
6:51
select it and then right click once
6:54
again and select on select data another
6:59
popup will appear on this one now we are
7:01
going to add the X AIS and our y AIS so
7:06
in this
7:08
case an edit series will appear and on
7:11
this popup you have the series name and
7:14
the series values on the series name
7:17
just click on select your header for the
7:20
sales and under
7:23
values you select the sheet that you're
7:26
currently in after the sheet just key in
7:29
the name of of
7:32
the of the data that you're currently
7:35
using in this case we are using sales so
7:37
we are going to use the same uh the same
7:41
name and ensure this name that you're
7:44
keying in is the name that you uh
7:47
created on the formulas data earlier
7:52
click on okay and then on the horizontal
7:55
axis
7:56
labels click on edit this time you're
8:00
going to do the same as you just did
8:02
click on the
8:04
sheet and this time around now H take
8:08
the month since we are using both sales
8:11
and the month key in the name of the
8:12
month that you're currently using and
8:15
click on okay see this is what you'll
8:19
have you'll have on your xaxis January
8:22
to the number of months that you have on
8:25
the legend entries you'll have sales
8:28
click on okay
8:30
and your table will appear like this you
8:32
have a couple of different tables on top
8:36
you can choose uh a different one for
8:39
yourself in this case this is what uh we
8:43
we go
8:44
with to check whether the chart now has
8:48
been Dynamic we will try K in more data
8:52
let's try and key in a couple more
8:57
months and then we'll also
9:00
put key in
9:05
data see as you key in your
9:09
data it's automatically changes and this
9:13
shows you that you've already created
9:16
and dynamic chart so if you have a lot
9:19
of database and you need to create chart
9:22
this is the most effective way but if
9:24
you need a very easy way of creating a
9:27
dynamic chart if you do not have a lot
9:29
of data the very first method that I
9:32
showed you at the beginning of this
9:34
video is the one that's highly
9:37
recommended
#Business & Productivity Software
#Educational Software