hi there, welcome to Teachers Tech my name is Jamie and it's great to have you here today
today I want to show you five different powerful yet underused charts in Microsoft Excel so some
of these charts are simple from the insert chart but people just Overlook them a lot and some are
multi-step ones that were combining two different charts to get something like this gauge where we
can input a number and the needle will change based on what we put in now I'll show you how
to set up all these and why we'd want to use these and if you want to follow along I'll
put a link to the worksheet Down Below in the description so you can click and follow along
with me let's get started with these charts in Microsoft Excel today on teachers Tech
together so sparklines are a useful tool for visualizing Trends and patterns and
data sets they're small data dense and can be easily integrated into text tables and other
visualization so here I have some data here and this is just streaming service over a five-year
period and these numbers represent subscribers in millions and you can go through and look at the
data but if I use spark lines it's not going to take up very much room and it'll give me a quick
idea of the trend so this is how you do how you do sparkline so I'm going to just click in this
spot here and I'm going to go up to insert so make sure you go up to your tab here and then
within the ribbon take a look past charts bit and we have sparklines right here notice if I
can do quick click on Reliance column or win loss I'm going to go to column and I think these two
will represent this data the best I'm going to go to column and all I need to do at this point is
choose the data range so the data range is going to be this right through here and the location is
going to be this through here so this is where I want to get represent where to represent and I'm
going to just go ahead and hit OK and then you can see I get this quick visualization I can see
the trends that are happening based on this data I really like these because it doesn't take up
much room now notice as soon as I click in the spark lines up here I get I get the tab up top
and this is where you can do more customization so if I wanted to change the line to the line
I can just click on it and then I can still go through and make more customizations if I wanted
a little bit thicker let's say I could go through and change the weight and I'll just increase it
so it shows up a little bit more I can make some style changes to color and you can see how quickly
you can make those so some things to play with so that's sparklines now I like this because it is
space saving easy for comparison between between the two so you can see the trends if I'm looking
at different streaming services I can really tell the difference it helps me contextualize when I
look at these and engagement I think they're easy to add on to lots of data like I said it doesn't
take up much room and it can be just used very simply another great thing that you can use for
data visualization is a heat map and you don't even have to go to charts for this this is just
in conditional formatting so what I'm going to do first is make sure you're under the Home tab
we are going to go to conditional formatting but I'm going to highlight my data first and I'm going
to do this in a couple different ways because you might not want to highlight all the data depending
on what you're trying to show but in this case I am going to highlight everything right here
so I'm just highlighting the data I'm going to go up to conditional formatting and I want you to go
to color scales here and as I hover over each of these you can see now how it's getting represented
in the colors that are defaulted to the one I chose you could see the 50 and product D is the
low the red I and the four hundreds are the green if I move over it does the opposite so now the
green is the low number and as you hover over any of these you have different color patterns you can
actually set your rules too to what color patterns if you want so if I go to more rules uh if I go
and if I wanted the three color scale I could drop down here and you can see how you can adjust you
can pick your different colors uh you pick it whether this is lowest value you can see your
midpoint maximum you can go through and adjust any of these so I'm just going to go back and
quickly give it the color scales of this first one so I have red as 50 in my green as 400 here makes
a nice visualization of this but what happens if each product should be represented in the column
compared to each other maybe not the whole thing so you just can't select everything I and then add
the conditional formatting so I'm just going to go Ctrl Z and undo how you might want to do this then
is just to highlight one column at a time and then go to it so if I go and I'll just give an
example here and I'll just quickly do these here and make sure I pick the same formatting because
what you'll see as I pick each of these now each one is connected to the column that I picked over
here so in this case you can see the green is the 200 because this is the highest one over here and
then we have a hundred as red in this one and 200 is read in this one so it's each connected so if
I do it this way I can kind of just look at it for the product in comparison but if I highlight
everything the numbers are going to be based on it so this is using a heat map very easy to use
you can set your colors and customize it the way you would like so this chart isn't as quick as
the other one it takes a couple extra minutes but I really like the the visualization of this this
is a gauge chart so I need to actually use a combination of two charts to create this one
but what it does is I can simply so if I have a YouTuber example let's say they got 300 but the
target was 500 you can see it was 60 percent reached uh if also the target was let's say
a thousand and if I change this the gauge changes the needle move so and then if this got lower you
can see how it changes like that to give that kind of that fun representation so I'm going to show
you how you can create this gauge chart using the data on the sheet so first of all with the data
that we use there's just certain ways that you have to do this to create this chart so uh this
up here was just some random information that I put in to create a goal so of Subs this could be
something else though so you could adjust that to what you want this is just the percentage you can
see it's just B2 divided by C2 and I changed it into a percent now this down here where it says
donut and Pie because these are the two charts we're going to be combining to create our gauge
chart this is how it needs to be written out so if we put 25 50 25 100 like that and then the other
thing we've got to do is we're going to kind of connect to this top part here so first of all I
can't have the percentage I need to turn this back into a whole number so I need to connect this so
I'm just going to put equals here and I'm going to put this one and I'm just going to multiply this
by so multiply this by 100 and this will give me the whole number here so make sure your format it
to the number one so this is connected if this changes over here you can see that this number
will change so the next one is going to be the number one and this is going to be for the needle
that black thing what I showed you that was moving it's just really narrow and that's why we're
making it one and so the last thing that we're going to need is we have to do an equation here
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
we're going to minus this one just like that so we need to set up things the way I've showed you this
first part this you could change but this part the equations and what the donut is you need to do the
same okay so we have our data we're going to start creating our combination chart so I'm going to go
ahead and highlight these two different all this data here and I'm going to go up to insert and
we can go through recommended charts or if you drop down right here we have combo I'm going to
go create a custom combo chart and when this comes out comes up you can see it's two different charts
we don't see pies and donuts here we need to change that so I need to go to drop down here on
the first one and I need to find a donut so this is why I can label the two donut imply so you know
what you're talking about when you're finding each and so the next one is going to be Pi so we're
going to go find pi and we need to check this on here so sometimes you have to click that twice to
get it but this needs to be selected we're going to hit OK so we have the start of this we could
delete our chart title and the legend down here I'm not going to worry about that right now so
what I need to do next is to to do some formatting so I have this selected if I look up top Under
The Format here I have format so move over in the ribbon and I want you to drop down right here so
we need to choose the series Pi so that's going to be the chart we're going to start formatting first
and then we're going to choose format selection so I'm going to choose there and I need to rotate
the pi 270 degrees so right here I'm going to put in 270 and rotate it just like that
okay so now I have to start changing some colors making some say something see-through so I want to
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
need to make this uh see-through so transparent so I have that one that's slice selected I'm going to
drop down and I'm going to go no fill notice I can see through it so I can see through and see
the donut on the other side just a chunk because I can still see that now this is tricky because
it's hard to see what's happening next there's a little sliver right here which is the needle
that's what I put that number one as and you can hardly see it we need to move to the next one so
I'm just going to hold down control and use my arrow and control and move over once and when
I did that when I used the arrow and control notice it says Pi point two that's the needle
I'm going to color this one black so I'm going to drop down and I'm just going to choose black and
you can kind of see it there so now I'm going to move again I'm going to go control and arrow right
and now I have Pi point three so I'm going to choose this now to go to no fill and notice now
I have my needle here and that's the formatting I have to do on the pie but now I want to go through
to the donut and do some formatting to make it look a little bit better so now we're going to
make sure I select the donut I'm going to go up top here and I'm going to select the series donut
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
rotated on it so you can see things are getting set up now with this I want to make sure that I
have the donut here now I clicked off it and this happens here you just make sure you select your
donut again up here and I need to select this piece right here but notice that's always the
pie that I'm selecting because it's on top so this is where I can do the control and arrow
through and if you notice the series change that I need the donut so sometimes it takes a little
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
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
going to go to no fill but let's say if I want to change the colors I can make some more changes so
if I wanted this to be the red uh and then the green and the yellow so I can go through so in
this case I could Arrow the other way I'm already in Donuts if I go to control and on my left Arrow
I can go back so you can see here I have this one selected and I'm going to go and choose this one
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
and then I'm going to just drop down I'll choose this one to be yellow and I'm going to go control
right arrow again and this will be my green here so I'll just choose this as a green right here so
I have these different parts so when we look at it the donut is the top part that we have showing and
all that we're seeing on the pi is the needle now I could give it a chart title or I could
delete these two so I could go through again and do more formatting it makes a little bit bigger
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
to 50 percent if I type 500 here you can see that that's where the dial it goes so I can make those
adjustments so I just like this this is a fun interactive way you could use these on interactive
dashboards too but even as this as an example it just makes it engaging to use as a chart
in this next example we're going to use the waterfall chart to show our company's starting
value of 100 changes as a result of various positive negative factors so you can see product
sales would be a positive advertised in cost would be a negative and then how we ultimately get to
the final result of 130 here so let's go ahead and put our waterfall chart in we're going to need to
go to insert on this one and I'm just going to go ahead and highlight the data I want to represent
now I'm going to go to recommended charts all charts and I'm going to choose waterfall right
here so waterfall I could preview it but I'm just going to hit OK so I'll move this off
and just make this slightly larger so we can see a little bit better and remember with all
the different formatting chart title we can make those adjustments to and customize it the way we
want so if I go ahead and look at the chart now I can see the blue how it starts off here would
be an increase and then the total would be your running total on the side so when we have our
increases of this 100 then an increase to 50 and it shows me the total but when we have a negative
or decrease here it brings the total down we're just looking at here so by using a waterfall chart
you can see how each category contributes to the overall change in value and you can identify which
factors have the biggest impact on the company's biggest line with that quick visualization
so in my next example we're going to be using a funnel chart so why would we use the funnel chart
well let's go ahead and create this let me explain the data that we have here so we're going to use
a sales funnel to track the progress of a product launch from the initial stage of awareness through
the final stage of purchase so the awareness starts up here with 10 000 people and then it ends
up with the purchases down here of 100. so before I go on and explain more about why we do want to
use the funnel chart let's go ahead and put this in so I'm just going to actually just highlight
all the data that we want to chart here and I'm going to go to insert and this time I'm going to
go to recommended charts and this is where we can find all of our charts if we go here to all charts
and if we take a look here we have our final chart so I'm just going to hit funnel chart and you get
the preview I'm going to go with this and just say okay now I'm not going to go through and customize
this chart at all I have a different video if you want to learn more about charts and I'll put the
link up in the description up in the end in the card up above description down below and uh so now
on this one what you're seeing it's going to give me this quick visual to show me the stages here so
I can track the number of visitors at each stage and we can identify where the most significant
maybe the drop-off occurs and make changes to the product marketing strategy or website to improve
conversion and rates and ultimately increase sales so this allows me to see where was the
major drop-offs of the data up here so it's just a quick a quick way to visualize this type it works
good in a sales funnel and this is a funnel chart but it gives that visualizations where
you can really key in on a specific area and then start making changes to see how you increase your
sales so I hope these different charts will help you visualize your data in Microsoft Excel let
me know what other types of charts that you like using or which ones you like to learn about more
thanks for watching this week on teachers I'll see you next time with more Tech tips and tutorials