5 Powerful yet Underused Charts in Microsoft Excel for Data Visualization
5 Powerful yet Underused Charts in Microsoft Excel for Data Visualization
In this video, I’ll explore five underused but powerful charts in Microsoft Excel that can help you analyze and visualize data in new and insightful ways. I’ll start with Sparklines, which are small, data-dense charts that can be easily integrated into the text, tables, or other visualizations. I’ll show you how to create Sparklines and use them to identify trends and patterns in data sets. Next, I’ll look at Heatmaps, which are visual representations of data that use color coding to highlight patterns and trends. I’ll show you how to create Heatmaps and use them to analyze large data sets and identify areas of focus. Then, we’ll explore Gauge charts, which are ideal for displaying a single metric and comparing it to a target or benchmark. I’ll show you how to create Gauge charts and use them to track progress toward business goals. After that, I’ll dive into Waterfall charts, which are great for showing how different factors contribute to a total value. I’ll show you how to create Waterfall charts and use them to analyze financial data, sales figures, and other complex data sets. Finally, we’ll wrap up with Funnel charts, which are useful for tracking the progress of a process, such as a sales funnel or a marketing campaign. I’ll show you how to create Funnel charts and use them to identify areas where you can improve efficiency and increase conversions. By the end of this video, you’ll better understand these five powerful charts in Microsoft Excel and how you can use them to analyze and visualize data in new and insightful ways.
0:00 Introduction
0:42 How to use Sparklines in Excel
3:01 How to make a heatmap in Excel
5:36 How to make a gauge chart (speedometer) in excel
13:23 How to use the waterfall chart
14:51 How to use the funnel chart in Microsoft Excel
Download the practice worksheet: https://bit.ly/powerfulcharts
Content
0.18 -> hi there, welcome to Teachers Tech my name is
Jamie and it's great to have you here today
4.38 -> today I want to show you five different powerful
yet underused charts in Microsoft Excel so some
10.56 -> of these charts are simple from the insert chart
but people just Overlook them a lot and some are
15.42 -> multi-step ones that were combining two different
charts to get something like this gauge where we
20.7 -> can input a number and the needle will change
based on what we put in now I'll show you how
26.4 -> to set up all these and why we'd want to use
these and if you want to follow along I'll
30.48 -> put a link to the worksheet Down Below in the
description so you can click and follow along
34.8 -> with me let's get started with these charts
in Microsoft Excel today on teachers Tech
41.16 -> together so sparklines are a useful tool
for visualizing Trends and patterns and
47.94 -> data sets they're small data dense and can be
easily integrated into text tables and other
53.16 -> visualization so here I have some data here and
this is just streaming service over a five-year
58.56 -> period and these numbers represent subscribers in
millions and you can go through and look at the
64.2 -> data but if I use spark lines it's not going to
take up very much room and it'll give me a quick
68.7 -> idea of the trend so this is how you do how you
do sparkline so I'm going to just click in this
74.76 -> spot here and I'm going to go up to insert so
make sure you go up to your tab here and then
82.08 -> within the ribbon take a look past charts bit
and we have sparklines right here notice if I
88.44 -> can do quick click on Reliance column or win loss
I'm going to go to column and I think these two
94.26 -> will represent this data the best I'm going to go
to column and all I need to do at this point is
99.9 -> choose the data range so the data range is going
to be this right through here and the location is
107.1 -> going to be this through here so this is where I
want to get represent where to represent and I'm
112.74 -> going to just go ahead and hit OK and then you
can see I get this quick visualization I can see
118.86 -> the trends that are happening based on this data
I really like these because it doesn't take up
124.44 -> much room now notice as soon as I click in the
spark lines up here I get I get the tab up top
131.94 -> and this is where you can do more customization
so if I wanted to change the line to the line
136.8 -> I can just click on it and then I can still go
through and make more customizations if I wanted
142.02 -> a little bit thicker let's say I could go through
and change the weight and I'll just increase it
147.9 -> so it shows up a little bit more I can make some
style changes to color and you can see how quickly
153.54 -> you can make those so some things to play with so
that's sparklines now I like this because it is
159.72 -> space saving easy for comparison between between
the two so you can see the trends if I'm looking
165.66 -> at different streaming services I can really tell
the difference it helps me contextualize when I
171.6 -> look at these and engagement I think they're easy
to add on to lots of data like I said it doesn't
178.5 -> take up much room and it can be just used very
simply another great thing that you can use for
184.8 -> data visualization is a heat map and you don't
even have to go to charts for this this is just
190.08 -> in conditional formatting so what I'm going to
do first is make sure you're under the Home tab
195.72 -> we are going to go to conditional formatting but
I'm going to highlight my data first and I'm going
200.52 -> to do this in a couple different ways because you
might not want to highlight all the data depending
205.56 -> on what you're trying to show but in this case
I am going to highlight everything right here
210.3 -> so I'm just highlighting the data I'm going to go
up to conditional formatting and I want you to go
215.64 -> to color scales here and as I hover over each of
these you can see now how it's getting represented
222.18 -> in the colors that are defaulted to the one I
chose you could see the 50 and product D is the
226.98 -> low the red I and the four hundreds are the green
if I move over it does the opposite so now the
233.4 -> green is the low number and as you hover over any
of these you have different color patterns you can
238.32 -> actually set your rules too to what color patterns
if you want so if I go to more rules uh if I go
244.62 -> and if I wanted the three color scale I could drop
down here and you can see how you can adjust you
250.2 -> can pick your different colors uh you pick it
whether this is lowest value you can see your
255.24 -> midpoint maximum you can go through and adjust
any of these so I'm just going to go back and
260.64 -> quickly give it the color scales of this first one
so I have red as 50 in my green as 400 here makes
268.74 -> a nice visualization of this but what happens if
each product should be represented in the column
275.22 -> compared to each other maybe not the whole thing
so you just can't select everything I and then add
282.06 -> the conditional formatting so I'm just going to go
Ctrl Z and undo how you might want to do this then
287.22 -> is just to highlight one column at a time and
then go to it so if I go and I'll just give an
293.16 -> example here and I'll just quickly do these here
and make sure I pick the same formatting because
299.76 -> what you'll see as I pick each of these now each
one is connected to the column that I picked over
309 -> here so in this case you can see the green is the
200 because this is the highest one over here and
315.96 -> then we have a hundred as red in this one and 200
is read in this one so it's each connected so if
322.08 -> I do it this way I can kind of just look at it
for the product in comparison but if I highlight
328.8 -> everything the numbers are going to be based on
it so this is using a heat map very easy to use
334.62 -> you can set your colors and customize it the way
you would like so this chart isn't as quick as
340.56 -> the other one it takes a couple extra minutes but
I really like the the visualization of this this
344.88 -> is a gauge chart so I need to actually use a
combination of two charts to create this one
349.74 -> but what it does is I can simply so if I have a
YouTuber example let's say they got 300 but the
356.22 -> target was 500 you can see it was 60 percent
reached uh if also the target was let's say
362.64 -> a thousand and if I change this the gauge changes
the needle move so and then if this got lower you
370.32 -> can see how it changes like that to give that kind
of that fun representation so I'm going to show
374.82 -> you how you can create this gauge chart using the
data on the sheet so first of all with the data
381 -> that we use there's just certain ways that you
have to do this to create this chart so uh this
386.7 -> up here was just some random information that I
put in to create a goal so of Subs this could be
392.22 -> something else though so you could adjust that to
what you want this is just the percentage you can
397.5 -> see it's just B2 divided by C2 and I changed it
into a percent now this down here where it says
403.62 -> donut and Pie because these are the two charts
we're going to be combining to create our gauge
408.54 -> chart this is how it needs to be written out so if
we put 25 50 25 100 like that and then the other
418.38 -> thing we've got to do is we're going to kind of
connect to this top part here so first of all I
423.78 -> can't have the percentage I need to turn this back
into a whole number so I need to connect this so
428.28 -> I'm just going to put equals here and I'm going to
put this one and I'm just going to multiply this
433.98 -> by so multiply this by 100 and this will give me
the whole number here so make sure your format it
443.04 -> to the number one so this is connected if this
changes over here you can see that this number
449.04 -> will change so the next one is going to be the
number one and this is going to be for the needle
454.5 -> that black thing what I showed you that was moving
it's just really narrow and that's why we're
459.54 -> making it one and so the last thing that we're
going to need is we have to do an equation here
465.36 -> so if I just put in equals and it's going to be
200 minus and I'm going to click on this one and
473.82 -> we're going to minus this one just like that so we
need to set up things the way I've showed you this
479.76 -> first part this you could change but this part the
equations and what the donut is you need to do the
485.94 -> same okay so we have our data we're going to start
creating our combination chart so I'm going to go
491.4 -> ahead and highlight these two different all this
data here and I'm going to go up to insert and
498.72 -> we can go through recommended charts or if you
drop down right here we have combo I'm going to
504.96 -> go create a custom combo chart and when this comes
out comes up you can see it's two different charts
512.46 -> we don't see pies and donuts here we need to
change that so I need to go to drop down here on
519 -> the first one and I need to find a donut so this
is why I can label the two donut imply so you know
525.96 -> what you're talking about when you're finding each
and so the next one is going to be Pi so we're
530.58 -> going to go find pi and we need to check this on
here so sometimes you have to click that twice to
536.22 -> get it but this needs to be selected we're going
to hit OK so we have the start of this we could
542.34 -> delete our chart title and the legend down here
I'm not going to worry about that right now so
547.98 -> what I need to do next is to to do some formatting
so I have this selected if I look up top Under
555.54 -> The Format here I have format so move over in the
ribbon and I want you to drop down right here so
563.34 -> we need to choose the series Pi so that's going to
be the chart we're going to start formatting first
567.78 -> and then we're going to choose format selection
so I'm going to choose there and I need to rotate
574.08 -> the pi 270 degrees so right here I'm going
to put in 270 and rotate it just like that
582.84 -> okay so now I have to start changing some colors
making some say something see-through so I want to
588.48 -> make sure so I'm going to click on this blue here
and you can see I'm in series Pi point one and I
595.26 -> need to make this uh see-through so transparent so
I have that one that's slice selected I'm going to
600.48 -> drop down and I'm going to go no fill notice I
can see through it so I can see through and see
605.46 -> the donut on the other side just a chunk because
I can still see that now this is tricky because
610.2 -> it's hard to see what's happening next there's
a little sliver right here which is the needle
615.18 -> that's what I put that number one as and you can
hardly see it we need to move to the next one so
620.28 -> I'm just going to hold down control and use my
arrow and control and move over once and when
626.1 -> I did that when I used the arrow and control
notice it says Pi point two that's the needle
632.16 -> I'm going to color this one black so I'm going to
drop down and I'm just going to choose black and
637.2 -> you can kind of see it there so now I'm going to
move again I'm going to go control and arrow right
642.84 -> and now I have Pi point three so I'm going to
choose this now to go to no fill and notice now
650.28 -> I have my needle here and that's the formatting I
have to do on the pie but now I want to go through
657.18 -> to the donut and do some formatting to make it
look a little bit better so now we're going to
661.92 -> make sure I select the donut I'm going to go up
top here and I'm going to select the series donut
668.1 -> and I need to do the 270 degrees what I did before
here so I'm just going to type 270 and I have this
675.72 -> rotated on it so you can see things are getting
set up now with this I want to make sure that I
682.8 -> have the donut here now I clicked off it and this
happens here you just make sure you select your
688.56 -> donut again up here and I need to select this
piece right here but notice that's always the
694.98 -> pie that I'm selecting because it's on top so
this is where I can do the control and arrow
700.2 -> through and if you notice the series change that
I need the donut so sometimes it takes a little
705.78 -> bit to kind of just arrow through and then if I
go through donut you can see 0.1 0.2 0.3 and then
713.58 -> 0.4 so this would be the transparent so if you do
click off it you can just find it that way so I'm
719.04 -> going to go to no fill but let's say if I want to
change the colors I can make some more changes so
725.7 -> if I wanted this to be the red uh and then the
green and the yellow so I can go through so in
731.88 -> this case I could Arrow the other way I'm already
in Donuts if I go to control and on my left Arrow
737.28 -> I can go back so you can see here I have this one
selected and I'm going to go and choose this one
744.66 -> to be red so if I drop down I'll choose this B red
now I'm going to go control and then right arrow
751.38 -> and then I'm going to just drop down I'll choose
this one to be yellow and I'm going to go control
756.06 -> right arrow again and this will be my green here
so I'll just choose this as a green right here so
762.84 -> I have these different parts so when we look at it
the donut is the top part that we have showing and
768.84 -> all that we're seeing on the pi is the needle
now I could give it a chart title or I could
774.18 -> delete these two so I could go through again and
do more formatting it makes a little bit bigger
778.5 -> as I do this but now we can test these out so if
all of a sudden if I wanted to see if it go up
783.66 -> to 50 percent if I type 500 here you can see that
that's where the dial it goes so I can make those
791.04 -> adjustments so I just like this this is a fun
interactive way you could use these on interactive
796.8 -> dashboards too but even as this as an example
it just makes it engaging to use as a chart
804.54 -> in this next example we're going to use the
waterfall chart to show our company's starting
808.92 -> value of 100 changes as a result of various
positive negative factors so you can see product
815.4 -> sales would be a positive advertised in cost would
be a negative and then how we ultimately get to
821.64 -> the final result of 130 here so let's go ahead and
put our waterfall chart in we're going to need to
828.48 -> go to insert on this one and I'm just going to go
ahead and highlight the data I want to represent
833.82 -> now I'm going to go to recommended charts all
charts and I'm going to choose waterfall right
841.44 -> here so waterfall I could preview it but I'm
just going to hit OK so I'll move this off
846.9 -> and just make this slightly larger so we can
see a little bit better and remember with all
851.28 -> the different formatting chart title we can make
those adjustments to and customize it the way we
856.44 -> want so if I go ahead and look at the chart now
I can see the blue how it starts off here would
862.8 -> be an increase and then the total would be your
running total on the side so when we have our
867.24 -> increases of this 100 then an increase to 50 and
it shows me the total but when we have a negative
873.24 -> or decrease here it brings the total down we're
just looking at here so by using a waterfall chart
879.3 -> you can see how each category contributes to the
overall change in value and you can identify which
885.3 -> factors have the biggest impact on the company's
biggest line with that quick visualization
891.6 -> so in my next example we're going to be using a
funnel chart so why would we use the funnel chart
898.02 -> well let's go ahead and create this let me explain
the data that we have here so we're going to use
902.1 -> a sales funnel to track the progress of a product
launch from the initial stage of awareness through
907.2 -> the final stage of purchase so the awareness
starts up here with 10 000 people and then it ends
914.22 -> up with the purchases down here of 100. so before
I go on and explain more about why we do want to
920.64 -> use the funnel chart let's go ahead and put this
in so I'm just going to actually just highlight
925.26 -> all the data that we want to chart here and I'm
going to go to insert and this time I'm going to
931.32 -> go to recommended charts and this is where we can
find all of our charts if we go here to all charts
936.66 -> and if we take a look here we have our final chart
so I'm just going to hit funnel chart and you get
942.54 -> the preview I'm going to go with this and just say
okay now I'm not going to go through and customize
948.72 -> this chart at all I have a different video if you
want to learn more about charts and I'll put the
953.16 -> link up in the description up in the end in the
card up above description down below and uh so now
960 -> on this one what you're seeing it's going to give
me this quick visual to show me the stages here so
966 -> I can track the number of visitors at each stage
and we can identify where the most significant
970.92 -> maybe the drop-off occurs and make changes to the
product marketing strategy or website to improve
976.62 -> conversion and rates and ultimately increase
sales so this allows me to see where was the
982.8 -> major drop-offs of the data up here so it's just a
quick a quick way to visualize this type it works
990.42 -> good in a sales funnel and this is a funnel
chart but it gives that visualizations where
994.74 -> you can really key in on a specific area and then
start making changes to see how you increase your
1000.26 -> sales so I hope these different charts will help
you visualize your data in Microsoft Excel let
1005.48 -> me know what other types of charts that you like
using or which ones you like to learn about more
1009.62 -> thanks for watching this week on teachers I'll see
you next time with more Tech tips and tutorials
Source: https://www.youtube.com/watch?v=GEw6_nNngKk