Transcripts
1. Charts 101 intro: Very rarely the
default chart options are what you want to
actually present. Sometimes there's
missing data that breaks a chart or you
want to get rid of if your chart elements or the default formatting is
not as good as it could be. That in this section of the
actual boot camp training, we will focus on
skills that will help you to make any
chart look good. And just a few steps. Hi, I'm bus and I'm a trainer
and consultant for Excel, Power, BI, and Tableau. I run my own company
data training, and I'm also a YouTuber. I've built this complete
online actual training to help you master Excel in the quickest way without wasting time learning things that
you want to use in practice. This training is the third part of the actual bootcamp where we will talk in detail about visualizing data in
Excel, we have charts.
2. Creating charts - where it all starts: When we have one big dataset, it's very difficult to retrieve any insights from it by
just looking at the data. So therefore, we want
to summarize the data and communicated in the most
efficient way possible. So in this section we're
going to have a look how to create different
types of charts and adjust all of the different
elements that you find inside of a
giant to communicate your insights in the
most efficient way possible that a workbook for this section you find in 03,
making insightful charts. Here we go to 01
charts one-on-one, and open the workbook
that you'd find inside of this folder and this
workbook we're going to start in the
very first sheet, 01, creating a basic charts. Now you might think easy, just go to Insert
and then choose the chart type you
want that that's true. However, just like
always an axon, there are 345 different ways
of doing the same thing, and maybe you prefer one
option over the other. So let's try the first option. Now, I want to
create a line chart. Now, how can we do that? We can first select the data set that would go
to the Insert tab. Then here in the
middle, there you find the Charts section. Now here you see all of these different icons that
represent the chart type. And I want to have
the light chart. So let's click on
the line chart icon. Then it gives you all of the different line
chart options. And you see also here, but hidden, we have the
area chart as well. Now you can hover over it. You see exactly how the charge would look
like in the preview. I just want to have
simple line chart. So let's go for the
very first option. Now if you want to have markers, you could also go over
here to the fourth option. However, markets we can
also add at a later point, so it doesn't matter for now. Now, I go for the first option. Once you click, then
it inserts the charts. Now a chart basically
floats on top of the sheet. So that means we are not
restricted to the cells, we can just place it
wherever we like. And also here to resize it, just go to the
bottom right corner. And here you can resize it
or you select the chart. Go to the Format tab, which is only visible when
the charge is select it. And then all the way on the right-hand side
there we have the wave. So we can, for example, go for width and the height of it. Okay? So there you go. We have a chart. Now if we want to
remove the chart again, just select the chart, press the Delete key on your
keyboard, and it's gone. Okay, now let's go to
the second option. For this option, we can
again select the dataset. Go here to Insert. And you see over here in the bottom right corner there
we have Recommended Charts. Now we can either click there or click over here on
Recommended Charts, it opens up the same window. Now here we have two tabs. We have Recommended Charts, and we have all charts. Now Recommended Charts looks
at your data and tries to give you some recommendations of what chart types
would be suitable. Now this might be helpful
to get a quick idea of how your data would be visualized using these different
chart types. However, I don't use it that
often because usually I do know what Chart
Type I want to create. However, if you have no clue, then this might be helpful. Alright, now on the
second tab, All Charts. Now over here you see all of the different chart
types that you can choose from on the
left-hand side, which corresponds to the
chart types that you have seen here on
the ribbon with here and there,
and exhale option. Now we wanted to
create a line chart. So I choose line chart. From here, we see all of
these different variations. And then the Preview
option where we can choose how we
want to visualize it. Now, I want to go
for it for adoption. Let's click Okay. And we have a line chart again. If you want to remove
it, just press Delete. Now, option number three
is to select the data. Then go here to the
bottom right corner. Quick analysis. Then from here we can
also go to the chart step and then choose the genotype
that we like, line charts. And there you go. We have a line chart again. Now let's delete this one and
go to option number four. You could also just insert a line chart without
selecting any data. Now of course, this
line chart is empty. And only now we go to Chart
Design here in the ribbon. Select data. And now we can choose
the data-rich, not a database is
the data over here. And you see it fills out automatically what the
two different series are. Series for client one client to what we want to have
on the horizontal axis. Click Okay, and we
have a line chart. Of course, this option required us to do a
little bit more work. However, it gives us
more flexibility, which we will need when we are going to create more
advanced charts. But if you don't need
this extra flexibility, then probably you would go and prefer one of the first options. Now let me delete
this chart again. And now option number five
is to use a shortcut. We can also select the dataset. Now use the shortcut Alt F1. So if all this one, it will insert a column chart. Now how can we then convert
it to a line chart? Well, now we can go
to chart design, change the chart type or
right-click on the chart. Go to Change Chart Type. From here, change it again
to my chart and you see this is the same window as
we were in just before. Now let's go to the next
dataset that we find over here, where we have three columns, one with the time period, and then we have website
visits data for region 12. Okay, now, select the data, then go to the Insert tab. And then from here we want
to have a column joint. And here we have all of
these different options. We have clustered column chart stacked and a 100%
stacked column charts. Now we will go over them
in detail in a second. However, just remember stay
away from the 3D columns. It's very difficult to see where the and end of every column. And therefore just stay
away from 3D options. Not the same for the bars, which is basically just flipped, gone chart, a bar chart. Now also here, kind
of similar options. We have clustered bar charts, we have stacked and
Anna percent stack. Now, for this visualization, I want to have a simple
clustered column chart. So the very first option, click on it, and there you go. Now, you could also,
of course use one of these other options that
we've covered this before. At any point you've changed your mind and you want to
change the chart type. Then just right-click
on the chart. Change the chart type opens up this window where we can switch between the different options. Let's click on cancel, not something that I want
to do at the moment. Now let's go to the
next dataset where we have two different series, one with the number of
clicks for each month and the other series is the
CBC, the cost-per-click. So the average cost per
click for that month. Now let's try to
visualize this data. So I'm going to select the
dataset, go to Insert. And then let's go for
column chart again. Now there we have the chart. I'm just going to
remove the other one. And over here we see the clicks. But the cost per click
is not really visible, but it is there because
you see it in the legend. Now why is the
cost-per-click not visible? Because the CPC,
the cost-per-click. Well, these values
are very small compared to the total number
of clicks that we have. Therefore, if measured
on the same axis, you don't really see any bars and the
columns for the CPC. Now how can we fix
that? By using a second axis that is then going to be used for the
cost-per-click. Now how can we add
that second axis? We can just go over
here to chiding. It, makes sure that
it's selected. Then go here to the top chart, design and change
the chart type. Or just right-click on the chart and then Change Chart Type. And then from here we
can go for gumbo giants, where we combine two
different types of charts that it clicks is going to be
clustered column chart. And the CPC can be a line chart, can actually also
be column chart, but let's keep it
as a line chart. And we have the option here to show a secondary
axis for the CPC. Click on the checkbox. Done on okay. I see now on the
right-hand side we have a second axis that
measures the CPC, now the line for the CPC. So now you have already seen
how to create a line chart, a column chart,
and a combo chart. So the combination of
different chart types. However, we didn't talk about all of these different
options that we had before for the column and bar charts and actually
also fit a line chart. We had clustered stack,
a 100 per cent stack. Now let's scroll down
a little bit further. And here you see another
dataset where we have the number of visits for different regions and
different months. Okay, let's select the
data, go to Insert. And then from here we have the different giant options
for the column chart. So we have clustered,
stacked and stacked. Now you can hover over it and then it already
gives you a preview. But let's start with
the very first one. Now, let me just move it
here to the right-hand side. Now if you go for the class adoption and
you see that you have a separate column
for each region, which is also being
used in a larger. However, if you
have many months, there's becomes very
difficult to read. Now in this case we
just have five months, but usually we
want to have more. And it might also
be difficult to see how it changes from
one month to the other. So as an alternative, you have the stacked
option where each region, each item within the field that's being used in the legend. It's not placed right
next to one another, but on top of the other one. Now let's take this joint
and move it a little bit to the right and see
the next option. So the stacked column chart. Now I'm going to select
the data insert. And over here I go
for the stack option. Now I'm going to place it
right next to the other one. You see the stacked
column chart option where we have the
same level of detail. So we still have the
breakdown by the region, but instead of having separate
columns for each region, we stack them on
top of one another. So it means that
the whole column is the overall total AC, how it's split between the different regions by looking at the legend
and the colors. That is the last option,
option number three, which is 100 per cent
stacked column chart. So I'm going to
place this one here, just going to delete the
previous chart over there. And I'm going to select
the dataset, go to Insert. And now the last option
on a per cent stacked. Now if we compare this
one to the other ones, then you see it is like
a stacked column chart, but everything is expressed as a percentage of the
total for that month. So 100 per cent
stacked column chart. So what acts are basically does, it just takes each
value and then divide that by the
total of that month, 100 per cent stacked option. You don't have to go to
your dataset and then calculate all of these
percentages yourself. No, Excel does it for you, which is kind of nice. So now that you know
the difference between the clusters stacked and a
100 per cent stacked option. Let's talk about how
you can easily switch whatever you have on the legend with what
you have on the axis. That's very easy. You just select
one of the charts. Now, I'm just going to go over here to there stacked option. Then at the top in the
ribbon you see Chart Design. And here we can choose
switch rows and columns. And when we click on it, then you see it switches
whatever you have on the legend with what you have on the axis, okay, So switching
rows and columns, so you don't have to go back to the underlying dataset and
restructure the dataset. Now you can just
click on that button. So now you already know how
to create a few charts. Now in the next
section we're going to have a look how to deal with missing data and how
to filter the charts.
3. Dealing with missing data and filters: We just had to look how to
create a few basic charts. Not the next thing that
we're going to have a look at is how to deal with blanks in your
dataset with missing data. Now, here we go to the next
sheet, Z12 missing data. And we have the same
dataset as before, but we have some
blanks over here. So we have, for example, no data for me and no data
for September and October. Okay? Now what happens when
you create a basic drive? Now, then you will see that
you end up with some gaps. Now let me recreate the chart
that you see over here. So I'm going to delete
it, select the dataset, insert, and then choose
the type that you're like. This one over here. Let me make it a
little bit bigger. And you see indeed we have
gaps where we have no data, which is probably what you want, which is the most
accurate reflection of the data that you use as
a source for this chart. However, there are few other options that
you can choose from. Now, how can we
see these options? But first, select the chart, then in the revenue
SI, joint design. And then here we can
go to Select Data. Now we have seen this
when they'll be four. Because here we can see
the data that flows into the chart and see the split into the different series
and what is on the axis. But you also have
this extra button in the bottom left corner
hidden an empty cells. Now click on it. And here we can choose
what we want to show when the cells
have no data. So by default, it's gaps, but we can also go for 0 or connect data
points with light. Now let's go for that option. Let's click on,
okay. Okay, again, you see now it looks
as if there are no gaps in the underlying
dataset for discharge. Now if this is a good thing to do while you have to
make that decision. Because what happens is
that exhale just puts the data points right in the middle between the very last
data point before. So that's the April value
and the June value, and it just takes the average. And so the middle value and
plot that value on the chart. Now, of course, this could have been the case that
it's somewhere in between, but it could also be 2
thousand year in this case. Now you might think
are then better, stay away from that feature. However, maybe you're
not so interested in one outlier and it takes away from the main message
to show gaps. So in those scenarios, it might actually be helpful
to connect the dots. Now you know the different
options of dealing with gaps in your data. Now. But what happens actually
if you hide the data, so the entire dataset. Now first of all, why
do you want to do that? Because maybe the only
thing that you want to show it through the end-user
is it Gerald itself, not the underlying dataset? Of course, you could take the dataset and place it
on a different worksheet. But maybe you just want to
quickly highlight over here the very first few columns
and just show the chart. Let me do this. I'm going to hide these columns. The giant disappearance
while it's still there, but it doesn't show anything. Now why he's not
showing anything? Because when you hide
the underlying data, it also things that
you don't want to show it on the chart now, but in this case we
actually do on it. So select the chart, go to Chart Design, Select Data, and go back to the hidden
and T-cells window. Now here, at the bottom, there we have another checkbox, show data in hand in
rows and columns. Click Okay. Okay, again, there you go. We have our lines back again. So it doesn't matter whether
the data is hidden or not. Now in this case, let
me unhide the dataset again over here and
write data is back. Now the next thing
that we're gonna do is filter out some of the data. Let's say we are only
interested in client one and the months
for the year 22. Now, how can we do that? Option one is to
select the chart and then adjust the selected
dataset for that charge. So over here you see in purple
is what is on the axis. In rat is the name
for each series. And then in blue, there we have the values for each series. Here we can just
make an adjustment. In the bottom-right corner. I could drag it a little bit to the left so that I only have
the data for client one. What if I want to show only the data for
one of the years? Well then we can also
adjust this part over here. So I drag this down. So they only have the
data for the year 22. This is, in this case, quite easy because the dataset is right next to the child, but this is not always the case. Now, there's also
another better option which gives us a little
bit more flexibility. So instead of doing this, let me undo this. So I'm going to re-select
client to the year 21. And instead of that,
just select the chart. You see there we
have Filter button. Now click on the
Filter button where we can also just
choose calling one. And then here I de-select
all the months and then re-select the month for the
year 20 to apply the filter. And there you go. So much easier. And in this way,
we also don't have to make adjustments to the selected data set for
that joint and this action. Let's do a quick exercise
on the next sheet, 03, where did my chart go? Now, over here, you see we have actually even do
have a chart over here. Why is the data not showing? But think about
it. Try to fix it. Once you have found a
solution, only then continue. And did you figure out what the problem was and
how to solve it? Well, it's actually quite easy. Now, here we do have the giants, but they are in columns
in-between BNF. So we either unhide it and then shows or alternatively
it takes chart, go to Chart, Design, Select Data and T-cells, and show data in the
hidden rows and columns. And this way you can
get the data back. Now, another thing that can happen is when you have a chart, then right next to
the formula bar, there you see the
name of the chart. So Chart six in this case, just under six giants Dan was
inserted in this workbook. Now, you can give this
a different name. We could, for example,
call this one sales by client, just like this. Now, why am I showing this? Because if we now go to the Home tab and then here
we go into a Financing Act. Then we can open
the selection pane. And here you see, well, there's a hidden child
joins on the right. There. There was
another chart hiding that you might have
totally messed if you wouldn't know that
this selection is really useful to keep your reports organized and
sometimes hide an element. There's only report if you want to hide it for the time being and then maybe
bring it back later. Or maybe it's just important for you and you don't
want to delete it. So in this case, just by clicking on
all of these symbols, you can hide elements and
you can rename the elements. So if I bring back, let's say the sales by clients. Also from here, you can just
double-click and rename it, press Enter, and there you go. So now you know already
quite a bit about charts, but not how to adjust all of
these different elements. Because inside of a chart, you have a title, you have axis, you have the data series, you have some grid lines
in the background. How can we make adjustments
to those things? That is going to be
in the next part.
4. How to customize any chart in any way you like: So now you're able to
create a few giants, but we didn't talk about
the different elements inside of a giant yet and how we can make
adjustments to them. So let's do that next. Here I'm on the sheet
Z04 chart elements. And here we have a
simple dataset where we shorted revenue for three different regions,
three different years. And I want to insert
a column chart, okay? Now that I have a column chart, let's have a look at which different elements are
inside of this chart. So first of all, we have
the title there at the top. Then on the left-hand
side we have the y-axis, we have an x-axis, and we have different
data series, three different data series. And we have a legit. Now in the background you
see these little lines, those are grid lines. So each one of them is a separate element through which you can
make adjustments. Now how can we do that? Now the first option is to
click on the plus icon. Then this gives you an overview of the
different elements. And then from here
we can, for example, add a data table, or we can go over
here to data labels. And when you hover
over it, it already gives you a preview of how
it would look like, right? So if you liked the data
labels, you just click. There you go. Now, if you want to
see more options, there is this arrow
on the right. Here. You can choose between
center, inside and inside, base, outside and data callout
option or more options. Now if you click
on More Options, then it opens up more options. And here you see
all of the options for that element that we're
currently working with. A separate menu that
pops up from the right. Now, this is the
interesting part. Now you have this for
every single element. So here you see all
of the food options. Now, if there's ever
disappears or you close this, than to get it back, you can also just double-click on the chart, pops back open. However, you need to be
careful what is selected, because now I double-clicked
on the giant area. So that includes basically
all of the elements. Now if I double-click
on the access, then you see formula axis. Now, once this menu is open, you can just click
on any element is see how it changes
what is selected. Now alternatively, let
me close it again. We can also do control one shortcut that we
have seen before. Control one opens up the formatting options
for whatever is selected, and now we have the
chart selected. So the formatting options
for the chart show. Now, I want to make
changes to the labels. Can select the labels. You see here Format Data Labels. And if you want to switch again, there's also hear a drop-down where you see
also all of the elements. Then we have four different
depths to choose from. I always start with the one that is actually on the right. Because there we have the
options that are really specific to the element
that is selected. So in this case,
the label options. And from here we can say, what do we want to show
on the link on there? Well, by default it's the value. And the leader lines
is when you just drag this a little bit to
the left or the right, then it connects it to
the data points, okay. Now labeled possession. Well, we have center
inside and outside. And so basically
what we had before, and here we have number. What is that? That is number formatting. So here we can change the
number formatting for the values that are being
shown on the label. Now let's see another example. So I'm going to add
over here a data table. Data table. Now I want to make adjustments.
How can I do that? Well, either just click on
the arrow to the right, see if that is enough, or go to more options, or just double-click
on the elements. Then the formatting
panel opens up with that element selected and then go to the three little
columns on the right, we see all of the
options that are specific to the data
table in this case. And we can play around
with the grid lines for the data table and if we want
to show a legend or not, okay, now I'm just
going to remove it. We don't need it here. So over here, I just dislike data table
and then it's gone. Now, a very last option to add chart elements
or get rid of them, is to select the chart. Then going here to
the top joint design, and then all the way on
the left-hand side there, we see the same options,
almost the same. There is one or two that you have here that
you don't have there when you click on the plus
icon and then there's lines and abdominal bars. Alright, so let's say
that we want to make an adjustment to the
y-axis. How can we do that? Well, let me just
close these panels. And now I go over
here to my chart and click or double-click
on the y-axis. Here on the right-hand
side, double-check what is selected format
axis, That's good. Now we go to access options,
the three little columns. Then here you see for
access we actually have quite many different
groups with options. Now, the one that we're
interested in is, let's say that we want to show everything not
with three zeros, but we just want
to say thousands. And so then we get
rid of three zeros. So I go here to display units, then choose thousands, okay? What if we don't want to go from 0 to one to two to three, but we want to change
the step size. So go from 0 to two to four, then we can adjust the units. So here instead of a
thousand for the major unit, we can go for 2 thousand. And when you change this, then also the grid lines, you see they are connected
to the major units. Now you might be wondering
what is then the minor unit. Well, if we put this 1 thousand, then nothing really changes because the minor units
are connected to it. Certain elements. For example, here for the grid lines there
we have also minor. So here we can go for
primary, minor, horizontal. And if we select that checkbox, you see we have also now
grid lines in-between. Now adjusting any elements works always in
exactly the same way. Once you have done it for two or three
different elements, you will see it's quite easy. Now let's do another example. Let's change the gaps
between the columns. Well, which element
do we need to select? Well, I want to
change the gap width between the data series, so I need to select one
of the data series, okay? Now you see I clicked once. So here on the
right-hand side it says Format Data Series.
I always double-check. Once you are a 100% sure you have the right
elements selected. Then go here to the
three little columns. And here we see the
Series Options. Here we can change
the series overlap. So if I would put
in 50 per cent, it overlaps for half
of the column F. However, I don't want this, I just want to put it to
maybe let's say 0%, okay? Then the gap wave is the wave that we have in-between
different years here. So there I would go 450%. Now here the guideline, it is if you would have one column and we'd
have put it to 50%. But in this case we have
three different series. So therefore, I want to have 1.5 times the width of one column in-between
different years, and that is 50 per cent. Of course, this is
just a guideline, can choose whatever
you like here. This is a rule of thumb
that I often use. Now you see that this
process of how to change each element is always the same and pretty
straightforward, however. Now and then there is
this one tricky case where you really
have to watch out, not let me give you
one example here. Let's remove this jar
that would you have felt. And let's go to
the other dataset which we had before as well. We're going to
create a line chart. Now let me make this line chart
just a little bit bigger. What I wanna do is I want to
have it a legend connected to the last data points of the lines here for
the legend options. And let's have a look. There's no option that
lets us say, okay, I want it to be there
on the right side, connected to the
last data point. Now I can put it on the
right, but that's it. But if the data point changes, for example, here, the hands, let's say it turns into
2000s for one of the series. Then I want that client to, to show up right next to
the last data points. Now why would you wanna do that? Because it makes it easier to
read the chart so you don't have to go back and forth
between the legend, the data series to
figure out what it is. However, to achieve that, we cannot make use of the legend. So let's click on it, delete it. And instead of that,
we're going to work with data labels. Now, what the chart selected? If I go here to Data
Labels and check that box, you see it adds a data label to every single data point
inside of the chart. Not what we want. Alright, so let's
undo this. Now. I'm going to select
only one data series. Then click on Data
Labels and see it only applies the data labels to
the series that was selected. I still not what we want
because we only want to have a labor for the very
last data point. Okay, so again, let's undo it. Now. I'm going to select the very last data point of that series.
Let's click on it. Now you see on the
right-hand side, always double-check
format data point. Okay? So I'm going to add data label to the very
last data points. Alright, that works, but I
want to place it on the right and I want to not show the
value but the series name. Now, this adjustment
we want to do, Do not the data points
but to the label. So I'm going to click on it. And now this is the tricky
point because here it says Format Data Labels. Hello. Now we're clicking
on steroids name. Then you'll see all of
the other ones up again. Okay, well, because it
says Data Lake box. Now, how can we make sure
that this doesn't happen? Let me undo that with Control Z. Now, I'm going to click
on that data label once more and you say only now
it says Format Data Labels. Now of course, there's
example that's a bit extreme, but just to show my points
are tricky, it can be. So now I'm a 100% sure. The right elements selected then go to the free
little columns. And now I can say today also want to show
the series name. And let's get rid of the
value of the leader line. Okay? So there you go. We have now client to, instead off the value. I want to have it on
the right hand side. So the labor positions,
what we need to change. I want to have it on
the right. The right. It doesn't really
show on the right. What if I show it on the left? Clearly shows in the
lab, but for the right. Well, the problem
here is that there's not enough space in-between. Well, over here the plot
area and the chart area. So plot areas, the metal part, that chart area is
the outer part. So if we take the plot area, the middle part, and
drag it a little bit.
5. Best practices: making charts pretty and easier to read: It's time to make a chart
prettier and easier to read. Now this is very important
because it all contributes to better user experience
and that in turn helps with communicating our data
insights from our charts. Alright, now let's get
started on sheet 05, making a giant pretty. Now here we have the
same dataset as before. We are showing revenue
by here and region. Now, we're going
to start off with a simple column chart that we then make it better and better. Now, the first thing
that I want to do is create this column chart over
here, which is the default. And then turn that one into
the one that you see here, which is already
quite a bit better. However, you can still
do again better. That is that last chart that
you see here at the bottom. Okay, Now let's go step-by-step. Let's scroll all the way up, select the dataset,
go to Insert. And I wanted to have
a column chart. Okay, now I'm going to place it here on the left-hand side. And let's start
making some changes. Now how can we turn this charge into the next version
that we got over here, where we have a different
color scheme and also the positioning of the diatonic
collagenous bit different. You see it there changes
made to the y-axis. Now how can we do all
of these changes? Let's go step-by-step. Starting off with the legend, I want to have the
legend at the top. Here I can go plus icon. Look for the legend. Click on the arrow. And here we can either
choose the top from here. I'll go to more options. Let's go to more options because I wanted to
have it in the bright. So I go here and
click on top right. Now, this is not
exactly how I want to have it because it
needs to maybe overlap. Still not perfect. Now, how did we manage
to get it there then? Well, you can also just manually drag certain elements to
where you want to add them, okay, so this works
not for everything. For example, here I
cannot change the order of the years 2001 to the
right, that doesn't work. However, for the title, for the legend, you can do this. So also here for them, title, I can just drag it to
the top left corner. And for the legend, we
can make it a little bit wider and drag it to
wherever you like it to be. Now for the plot area as well, I can make it a little bit smaller and put it over
here in the middle. So now that everything is
in the right position, Let's go to the next change. Now. Here I want to go
to the y-axis first, and I want to change
the step size so that we make steps
of 2000s every time. And I want to get
rid of three zeros. So show everything in thousands. So select the element which you want to
make the adjustments. Then go here to the
right hand side. If it doesn't show up F1 or
double-click on the axis. And then here we go
to access options. Now, x is option, so let's open it up and
see what we got here. Now for you, just go top down. You see we have, they're
displaying units. Now let's change
this to thousands. Okay, so now we are displaying
everything in thousands. And then the next change
that we want to do is to make the steps of thousands
instead of 1 thousand. So here we have the
units and a change to thousands to do fact
that the miner unit, it's not really
relevant for this case, the y-axis starts to look
like in the example. Now, the next thing
that we want to change is maybe the gap width
in-between the columns. So let's select one of the data series,
doesn't matter which one. Then again, go to
the right-hand side, the formatting menu,
three little columns. And here we can
change the gap width, so maybe minus Dan per cent, so that we make the gap a little bit smaller
in between the columns. And then in-between
the years we go for a smaller gap where for
maybe let's say a 100%. Or if you follow that rule from before and at 50 per cent, so that it is 50% all of
the data series combined, then we have therefore
a certain year, okay? So that starts to look more
or less the same as this one. Then the next thing
that we could do is change the goddess. Now one option is to
just click on one of the data series that you want
to change the color off. Then going into the right
hand side, fit a line color. And then from here we can
change the fill color. Now you can do this one by one. Or you could also go to
chart design, change colors. Here we have different balance. So if we choose a
palette from here, let's go for this one. Then you see it
applies it to all of the data series so it could save you a
little bit of time. Now for all of the data
elements, actually here, There's also this whole section that you could make use of. And if you hover over it,
you see what it does. However, I never really use it, just gives you some suggestions
to get to an end result. Maybe a little bit more quickly. But if you know very well how to modify the
different elements, then you have much
more flexibility. It can make exactly the chart
that you're looking for. So therefore, I don't
use this too much only when you want
to be really quick. Now, at this point, the chart looks quite similar. However, there are a few more
changes that we need to do. For example, we need
to change the title. Over here. I want to
have revenue by region. So let's change the title
to revenue by region. Now, we want to have different
color for the title. Different color. For
all of the other texts. I would first select all charts, pick the main color. Let's go for maybe
a lighter gray. And then for the title, let's select the
title and let's go for a dark blue color. So this one over here. Okay? So now we have exactly the
same colors in the exam. What if you want to have
a smaller font size? Well, then you just select
what you want to change. For example, maybe
the y-axis title. And then here we can make the y-axis diets are a little
bit smaller if you find it, notice the very last change. Let's make these grid
lines in the background, maybe also a different color. So select them. Go to the right-hand side, go
to the finish line bucket. And then from here we can
play around with the color. So maybe a lighter gray, this one and maybe the compound type or the dash diet to make
changes to their lifestyle. Discharge is quite okay, especially if you compare it to the default charts that
we started off with. However, that doesn't mean
we can still do better. Compare this one to the example that we have here at the bottom. Now why do I think
that one is better? First of all, we don't
have a y-axis anymore. So it makes it a little
bit easier to read. I don't have to go to a
column and then follow the grid lines but to the left to figure out what the value is. And then you still don't
have exactly the value. You have more or less the value. If you just add labels, then you see it straight away. Now another reason why
I think this chart is a little bit better is
because of the colors. Now here you see that we have
different kinds of blue and sometimes it might be a bit difficult to see what
is, what region. Now if you have a
look at the last one, there is very clear and distinct colors that nicely
matched as well. Then also makes it better if you have a report and
multiple charts, you have clearly distinct
color for each media. And the very last reason
why I think that chart at the bottom is a
little bit better is because it's less crowded. We don't have grid lines and
we don't have the y-axis. And there's more space
around the plot area. So that works a bit
more gotten me. Now let's create it. Let's go back to the chart
that we were working with and turn it into the one that we have
there at the bottom. So I'm going to
slide it down here. Okay, let's start
making some changes. Now let's get rid
of the grid lines. Select them, press Delete. Get rid of the y axis. Selected. Press Delete. Alright. Now I take the blood area, resize it a bit. And let's create a
bit more space on the left than the
right, just like this. Now, of course, we still
have to add the data labels. So let's click on the plus
icon at some data labels. Alright? The data labels, they don't
show one extra decimal. Now, how can we get
that decimal in there? Well, that's number four, number formatting of the labels. Okay, so to change that, It's like one of the
data labels series and then go over here to three
little columns, number. And here we can change
the number formatting. Let's go here for number
with one decimal, okay? You can do that
for the other ones exactly in the same way. So also does I'm
just going to change to it number with one decimal. And then the last one also here, we want to have number
with one decimal, okay? Now that starts to look better. Now then for the x axis, I want to get rid of that line that you see there
at the bottom. Now, to do that, select the
elements that you want to. Just where do we go now? While before I always said go to the three little columns, but this is more
formatting change. So therefore we need to be
on the fill and Line Bucket. Now here you see we have line and we can just simply
click on no night, then that axis line disappears. Okay? Now the next thing
that I wanna do is get here this in thousands. Well, how did it get it? Right below the title? This is just another element
that we can add to a chart. Well, this other element is
not available from here. However, we can select
the chart, go to Insert. And then you see here we
have shapes or icons. So here we can add a shape or the textbox that
would also work. So I'm just going to add a
text box and let's just add a text so in thousands. Okay? And then we can select the
textbox and drag it right, belonged to Dido,
re-size, it just fits. And then also update
the formatting. Maybe it's the same color, blue and lipids smaller,
just like this. Now it still looks
a bit different, but that is just
because of the colors. So now we have to choose colors that
nicely work together. Now, one option would be if you have a good feeling for
colors to do it yourself. So select one of the series, got to fill in line. And then over here we
can choose a color. These are the default
colors that are in line with your theme. But you see if we
go to Page Layout. Then we have different themes
that we can choose from. A lot of companies, they have their own thing, so that
would be the easiest. Having a company
theme, of course, makes this whole process a
bit more straightforward because you don't have to
think about which colors, the years it's given. It's in the template, in the theme that you
can import into Excel. If you don't have it,
then you have to figure out which colors
go well together. Now, if you have a good feeling for it, then you're lucky. But I think most people
don't really have a good feeling of which
colors go well together. So they are a lot of tools that help you figuring this out. Now, let me show you. You could, for example, open
up your browser and then do a simple Google search
like color themes. And then here we have that
first option, coolers. And from here we can
explore different bytes. Now let's see what it gives us. A website full with
different color palettes. Now, just go down until you find the ballot that
you want to use. Now let's say you
find a good one. You can open it up just by clicking on the three
dots and then open it. And here you have a detailed
view of the color palette. We have the hex codes. Here, an axon, you
either need to use HSL or you can also
use RGB colors. And then you see the color codes that you can then
bring over to Excel. Now let's remember
this one here. So doing a 24 to 51 to 52, then you just go back to Excel. Let's say we want to apply
it here to this data series. Then I'm gonna go here
to color more colors. And that code we can
just copy in here. Also the hex code. Or we switch here to
HSL, whatever you like. So we have 224 to 51
to 50 to click. Okay. And there you go. Then you can do it for all
of the other ones as well. Now I went for a different
color palette that I liked. So let me just reapply
the same color palette. So I go over here, select the first data series on color and then make them purple. Then I go to the next one, change the color to green. And then the very
last one over here. Let's change that one to think. Ok. And now we have the
same color palette. So this is how you
can figure out the color palette and
apply it to your job. So let's do a last
finishing touch bar chart. Let's say we want to change these borders and
make them routes, and maybe add a little bit of shadow so that it
looks like they float on the report that
we're going to create. So I'm going to select
the chart, Finland line. And then here on the border
all the way at the bottom, then we have rounded corners. Okay, so that looks nice. Now I'm going to select
the chart again. Now I want to add
a bit of a shadow. Here. Shadow you find in the effects, not always be very
careful with effects. Don't overdo it, but
a shadow can be nice. So here I'm going to
add a bit of shadow. The shadow I like, maybe a
little bit more transparency. And see now it looks as if
it's floating on my report. So this is how you can go from an ugly default chart
to pretty tired. That's easy to read and creates
a better user experience. Now, it takes a bit of time. However, you will
get faster over time and you can make
use of templates, which makes this whole
process much quicker, which is going to
be the next part.
6. Saving time with chart templates: We just have seen how
to do a chart may go over and make pretty charts. However, this process
takes a bit of time to speed this up. You can make use of templates. So how can you create a template and apply it to a new chart? Now let's try it. I'm here on the same sheet as before 05 making a giant pretty. And here we created
this chart over here. And I'm going to
right-click on it. And from here we can
choose Save as Template. Alright, so here you can
give your template a name. So this is going to be my
golden giant template, CRT x, that is the extension. Just leave it as it is. And then we have to
choose location. This location you
cannot change because axon looks at this folder
for your templates. Now of course you could change it and put it on your desktop. However, then exon
doesn't find it. So therefore, I'm not going to change that folded,
leave it as it is. Click on Save. Now what happened? Well, we saved the
template that we can now reuse that chart
that we have over here. This was the default chart. Now let's try it out. You just select the chart, right-click and then go
to Change Chart Type. Now, from here you see
there's a folder templates with our first
template, selected. Press. Okay? And boom, there you go. We don't have to do anything
and it looks exactly the same as the one
that we just created. Okay, now, let's try this again. But then on a different
dataset, would it still work? Now, follow me 206
chart templates. You see we have a
different dataset here. I'm going to create
a column chart. Now again, let's
apply the template. I go here to Change
Chart, Type, done blades. There it is. Click on Okay. And
boom, there you go. I tried, looks much
better already. Probably in this case, I
want to switch rows and columns so that we have
the periods on the access. And over here the legend that's just adjust it a little
bit, it looks a bit better. So just like this, place it wherever you like. So maybe here. And that is done
the final chart. Now for discharge,
you probably want to change the position
of the legend. So let's put it over here on the right-hand side so we can either manually drag it there, make use of more options, but let's do it manually. Then we have full
control and then adjust the plots, areas science. And that's it. And you see how much time these templates can save you
when you create a report. But what if your colleague also wants to use
the same template? Well, then you just go to the folder where you
store the template, take it from there, and send
it over to your colleague. Now how do you know
where that four layers? Well, when we right-click on the chart and say
Save as Template, then it gave us the file path, which you can just copy. And then you just go to your
file explorer over here. So then there, and it brings you to that folder where you find the template and then that is the file that you can
share with you got it. Now if you didn't have a
whole bunch of templates for different types of
charts, put them together, share them in your team
and then you also create more consistency in your
reporting between team members. That's time to practice
again because of course it's easier to follow when
I do it with you. However now you're gonna
do it on your own. Go to 05, making sure I pretty, and here we have
a second dataset. Now use that dataset to
create a line chart, then turn it into a pretty more insightful and
more readable line chart. Save the template, and then go to zoom and six Dr. templates. Now here again, datasets
with some values over time, show it as a line chart, but then apply the template. Good luck.
Bas Dohmen, Founder + YouTuber
