Transcripts
1. Visualisation tools intro: Visualization and axle is so
much more than just charts, knowing how to work with tools such as conditional formatting, sparklines, data validation
and form controls can take your reporting to the next level and make it much more
clear and insightful. Hi everyone. I'm bus, I'm a trainer
and consulted for Excel, Power, BI, and Tableau. I run my own company, data training hail, and
I'm also a YouTuber. I've built this
complete online actual training W, mass, the axon, and the quickest
way without wasting time learning things that
you want to use in practice. This training is part six
of the actual boot camp, where you will perfect your visualization skills by learning about all-important visualization
tools besides charts.
2. The basics of conditional formatting: Visualization and axial
is not only about charts, it's also about
conditional formatting. Sparklines form controls,
Data Validation and custom formatting strings and
those topics we're going to cover in this section, the very first topic
that we're going to cover is conditional formatting, which is basically just formatting with a ruler
attached to it so that you can determine when the formatting
should be applied. Now, follow me to the
very first workbook here and follow 01
conditional formatting. Let's open it up. The very
first sheet, 01 basics. We're going to have a look how Conditional
Formatting exactly works and what kind of different worlds there are
that you can choose from. Alright, now, let's start with the very first column over here. We want to apply the rule
that anything that's above the value 7,500
we want to format, maybe live in different
color, different backgrounds. Now let's see. Alright, so now just like
with normal formatting, you're just select
the sounds to which you want to apply a
conditional formatting to. And then you go here all the way at the top
to the Home tab. And then here in the middle, there you find conditional
formatting. Let's click on it. And here we have a
categorization of the difference conditional formatting rules
that we can choose from. Now let's start at the top
with the highlight cell rules. Now here you see we have roles such as greater than, less than, or equal to texts that contains a date occurring
and duplicate values. Now I think most of these rules are kind
of straightforward. Now what do we want
for our case here? We want to have the greater
than rule because we want to highlight everything
that is greater than 7,500. So let's go for that
row and you see with others clicking
on anything else, we already see
Conditional Formatting being applied enter sheet. Now, the rule is still not set up in the way that
we want it to be. However, everything
that's now above 6,063 has a red background
and a red font color. Now let's adjust it. So instead of the 6,063, we can put in the value
that we want, 7,500. Alright, that already
looks better. That's basically everything
for the rural part. So the first part of a
conditional formatting and setup. However, now, the second part, which is the formatting itself. How should the
cells be formatted? What background
color do you want? What font color do we want? What do we want to have for
the borders now that we're going to adjust over here on the right-hand
side of this box. And you see we have
some default options. Here. The first default is
currently being applied. The light red fill
with dark red text. We want to have maybe
something different. Now, we could go for maybe a Greenfield with
dark green text. Or if we go back, we can also go for
own custom format. Now let's choose that. And
this dialogue box should look familiar because this kind of corresponds to
normal formatting. So here we can, for example, go to fill and choose
the color that we like. Let's go for orange. And then here for border, we can put a border
around the cells. Alright? Now, let's say this is exactly the formatting
that we're looking for. Then we can just click on Okay. Now we have the second part of our conditional
formatting also setup. And you can just
double-check in the sheet. If everything is exactly
like you want it to be, less, then click Okay,
and you're done. But let's see if our
conditional formatting rule is working exactly in
the way that it should. So you see everything above
7,500 is highlighted. And if we now take any
random cell that's below it, for example, this one over here, and I make this 1 thousand, then it should also
be highlighted. It indeed works. Okay, So it is basically
just dynamic formatting. Now what if at any
point you want to make an adjustment to the
conditional formatting, well, that's of course possible. You just go back to
Conditional Formatting tab. And then here at the bottom, there we have managed roles. Now if we click on it, then we get a new dialog
box from which we first of all can choose which
formatting rules do we actually want to see? At the moment, we only see the conditional formatting
rules of the selected cells. Now you see I have one of these
cells selected over here. Therefore, I see the conditional formatting
rule that we set up. Now, if you don't see it, just switch to this worksheet or if you want to look at
conditional formatting rules of different worksheets, then you can choose
those of course as well. Alright, now, here we do have our rule showing up
that if we want, we can delete it or we
can add it through. And if we play it at it, then we get again a
new dialogue box. And from here we can
adjust the rule. So you see the moment it says greater than 7,500, alright? Or we can also change
the formatting itself. You see over here we
have the formatting. Click on Format and then make the adjustments
that we want. Alright? Just simply click Okay
and click Okay again. And then we're back
and we can just check if everything is fine by
first clicking and apply. If it looks good. Then close the box, I'll click. Okay, and that's basically how conditional
formatting works. You see not that difficult. Now with this, you already know enough to start using
it in practice. However, of course, there
are always tricky cases. So in the next part, we're going to have a look at
what happens when you apply multiple conditional formatting
rules to the same cells.
3. What happens if two rules apply to the same cells?: We just got up and running
with conditional formatting. However, what
happens when we have multiple conditional
formatting rules that apply to the same cells. Now, let's have a look. Alright, now back
to our example. So over here we go now
to the next column. Now here we want to highlight everything above the average. So we have conditional
formatting always two parts, the room and the formatting. And the rule is going
to be above average. So we look at all of the values and if they're
above the average, then we want to
apply formatting. And the second part is
the formatting itself, where we just want to apply a
different background color. Okay? Now let's first select
the cells to which we want to apply a conditional
formatting over here. There we go to their own tab and then choose from
the metal here, conditional formatting that
it's always the question, where is the rule that
we want to apply? That at the beginning, it's
a little bit of searching. However, they are not
crazy many rules, so you will get an
overview quite quickly. Now. I don't see it here, so I go here now to
the top bottom rules. And now over here at the bottom, then we have above average
and below average. Now, I want to highlight
everything above the average. So I go for this
rule and you see everything that's
above the average is now already in red. However, I would like to
have our own custom format. So I go to Custom Format. And then from here we choose
a different fill color. Now let's go for maybe
just a neutral blue click, Okay, click Okay again. And there you go. Now, this is the first rule. Everything works as it should. Nothing special,
just like before. However, now we're going to
apply a rule number two. Now the next thing
that I want to do is I want to have everything
that's in the top three to be in green
with a dark green font. Alright, now let's
do this again. I'm going to select
the whole range, and now we go to conditional
formatting top bottom rules. And we can choose here top-down items that's called top-down, however, can be
any number, right? So it doesn't have to be done, can also just be dub three. Alright, now over here we can go for green fill with
dark green text. Alright, so Greenville,
darker index. Alright, Let's click on Okay. And here we have three values that get highlighted.
It looks good. Now you might wonder, however, why did it show up with
a green background and dark green color for the font
and not on blue because, well, these three values, they are also above the average. Now to dig a little bit deeper
of what is going on here, we have to go to
conditional formatting and to the Manage
Roles dialog box, because from here we can control how these rules
exactly get applied. No, I don't see anything yet. And that's because I didn't select the range
that we were working with to which we applied
the conditional formatting. So I change over here from the dropdown
to this worksheet, okay, now I see all of the
rules on these worksheets. Now the first one I'm
not too interested in, so let me just delete this one over here so that we
only add the other two. Now the question is,
how do these rules exactly get applied? Now, here for our
top three rule, which clearly shows
here for 8,373 is the green background and dark green font
that shows in yet. So what is happening is that conditional formatting
goes Top-down, looks at the value and
then checks the rule. Now is an industry yes. Green fill color and
dark green font. Then goes to the next rule.
Is it above the average? Gas is above the average. And once you apply a blue
background, however, there's a conflict because the Greenfield color already has been a planet and therefore it cannot apply the blue
background. And that's it. However, what would happen if we change the order
of the rules? Now let's have a look. Let's take that
second rule here. By using these arrow keys, we can change the order. So I placed it up. So now the above average
row is the first row. So now let's click on Apply. And you see it clearly
looks different. If we go back to that
same cell over there, 8,373, well, that value
is above the average. So a blue background
colon gets applied, then goes to the next row and it checks if that
value is and adopt free. Yes, it is in the top three, so it wants to apply a
green background color. However, there is
a conflict because the blue background color
already has been applied. And then it also wants to
apply a dark green font. Well, is there a conflict? No, because the first rule did not change the
default color. Just said default color. Okay. It didn't specify any specific font color
that needs to be applied. However, the second one does, because there's no conflict
for the font color, the dark green font
color gets applied. So we end up with blue background color
and dark green font. Well, what if we
actually want to stop after a rule returns true? Well, then they are still check boxes over here on
the right-hand side. Then we can click on. So if we click here
on stop, if true. Now, it will not go
to the second one. Top three. So once we apply this, you see the dark green font
color disappears because the value is above
the average and then doesn't continue
to the second. Okay? Now, all of this
only really matters if you start applying
conditional formatting rules to the same cells. Otherwise, you don't
really have to bother. But let's click on Okay, and now it's time to go to the next part of
conditional formatting, which is just exploring all of the different
roles that out there. You probably noticed
when we went here to conditional formatting, we have data bars, color scales I can set, however, also hear new rule when you go here to one of these
buckets morals. Also. Here, we still have all kinds of different roles
that we can choose from. And that is going to
be the next part.
4. Exploring formatting rules: It's time to start exploring more conditional
formatting rules. So let's dive in straight away. Now here in the middle,
I have the column with the values to which we want to apply a conditional formatting. And the role that
we want to check is cells that contain black. So all of the blank cells, we want to give a
different colour. Alright, so as always, first, select the cells to which you want to apply a
conditional formatting. And now we go into
Conditional Formatting and look for that rule. However, if you go over
here, I don't see it. Well, that is because we have more rules than just the
ones that you see over here. We can also go to morals, or over here we have new rule. And then a new dialog
box pops up from which we can choose the
rule that we want to apply. So here you see we have
different buckets again, format all cells based
on their values. Only cells that contain
top and bottom, only values that
are above or below. Alright, so start exploring
all of this. Now. Here we probably
don't need this one. Format only cells that contain. Then we can set up the row and we want to have the blacks. Alright, now that's it. Then we have the second part
of conditional formatting, which is the formatting itself. So let's go here to
format and choose the color that we want to apply that let's go
for a green color. Click, Okay. Okay, again, and now all of the blank cells have a green
collar applied. Alright? Now, if I delete it
and one of the values, you see, it becomes green. So now it is good points
to stop the movie. Go to conditional formatting
and explore all of these roles that you can apply just to get a little
bit of an overview. Now, once you have
done that continuum, now there's one special
group of formatting rules, which are the color scales, icon set, and the data bars. Now, let's have a look over
here at the database first. Okay, Now, the way to
set it up is the same. You select the cells conditional
formatting data bars. And now here you choose the data bars that
you find Brittney, I'll go for the very first one. And that's it. You might wonder,
what is the rule? There's always two parts. We have the rule and we
have the formatting. However, here, the rule
is set up automatically. It just looks at what's the highest value that
you have in that range. What is the lowest value? And then scales the
bars on that basis, the minimum and
the maximum. Okay? Now, of course, you can
still make changes to that. So to make changes, just go here to Conditional
Formatting, Manage Roles. And you see we have
here our data bar rule. Here, we can just add them. Now, that brings us, do this dialogue box from
which we can make the changes. You see, it's
basically just here format all cells
based on the values. Then here we have data bars. I could say here, show the bar only. And then following after that, we could change the way
everything is scaled, right? So now let's leave it as it is four seconds and then
continue bit further. Here we can change the color. Also whether we want to have a solid fill or a gradient fill. And don't overlook
this button here, negative values and access. So in case you would
have negative values, which we don't in
this case, however, if we do, then we could choose
a different fill color, for example, red. Alright. Now, after all of this is setup, we can click OK, OK, OK again and see now we have
our adjusted data bars. And what if we think okay, this database, but they
are a little bit too big. Well, one option
would be just to make the color a
little bit less wide. However, this is not
really what we want. We can also adjust the scaling where we were just before that. So if you just go
back managed rule, click here on add it here
we could, for example, make the bars 50% of
their current wave. How could you do that? Well, we could overhear
change the maximum. Now, if we go and juice
here from the drop-down, either a fixed number or if we want to make it a
little bit more dynamic, good go for formula. We could say is equal to. And then we can look
for the maximum. Let me drag this a
little bit to the right. And we want to find the
maximum inside of that range. Alright, now, close the brackets and then we could
multiply that by two. So then we make
everything 50% smaller. Click here on Okay. And Okay again, I
can see everything is now half the
size from before. And that is because we
increased the scale for this data bars from
one to 10 thousand, referred to now more or
less one to 20 thousand. But it is dynamic because
we use a formula to determine the maximum where
the scale basically ads. Okay? So now we can also have
a look at the last two, which is the color scale. And I can set now, they basically work
in a similar way, but let's see them in action. I'm going to select
the next color. Go here to conditional
formatting. And now we want to go
here for a color scale. Now also here for a color
scales, some default setups. And over here, the one that we have there at the bottom
is with two colors. And the ones that we have
here is with three colors. Now, I actually want to
have only with two colors. Let's say this one over here. Click, and that's it. Alright, now, if
we wanted to make an adjustment,
Conditional Formatting, Manage Rules, take the
rule added through. Now over here we can still switch between the
other alternatives. We can also change the minimum. I think we wanted to have,
the 75th percentile. So that means our gradient
scale will only be applied to the top
twenty-five percent. And so everything that's
above the 75th percentile and everything below it gets the color that we choose
here on the left. So let's just go for white. Click OK, OK again. And you see only five of the
cells have a green color. Okay? Then the last one where
we want to have icons, but only an icon for the values that are
above the 8 thousand. Okay, now I selected the cells Conditional Formatting icon set. Then choose the set of
icons that we want to use, maybe just over
here, these bubbles. And then we can go
back to manage. And let's say that we
want to add a dead rule. And here you see the rule
that's being applied. And we want to
make an adjustment because I only want to
have the green icons. And we can also play around
with the God of barns. But let's say we only want
to get rid of the icons. Now we can choose, you know, sell Eigen and click Okay. Click Okay. Now we only have
the green circles. Now at this point, it should have a
good overview of the different roles that
you can choose from. However, this is not
where the story ends. You can also come up with
your own custom roles where you decide exactly when the
formatting should be applied, even if it's not one of these
default options over there. So that's what we're going to explore in the next section.
5. Writing your own custom rules: The nice thing of conditional formatting is that you're not limited to the default rules
that actually gives you. You can come up with your own custom rules and use those for
conditional formatting. And that is what we're going
to have a look at right now. Okay, So to get started, I have an example set
up on the next sheet, 0 to custom logic. Now, let's say we want to highlight all of the
cells that contain text. Now, we want to do this
with our own rule. Now than we need a formula
that returns true or false. And every time, well
it returns true, formatting gets applied
and it returns false, the formatting
doesn't get applied. That means we can basically
write any logic as long as our formula returns
true or false. Now, let's start
with a simple one. I'm first going to go over here right next to our
small little dataset. So that's select
here F6, alright? And here I would like to
come up with a formula. There were checks if that value
that we have here and C6, if that is value,
numerical value, or if that is taxed. Okay, so how can we do that? Well, there's actually a
function that's called is taxed. Alright, now let's select
it by pressing Tab. And I'm going to refer to C6, close the brackets
presenter while false because that's
the numerical value. Now, if I drag this
down and to the right, then you see that we have true here for the next one, f seven, because here in C7, the cell that we're
referring to, There's some texts on the
same over here for C nine, alright, so here F9 refers to Ceylon less tax
than two-tenths true. And here we have a
third one in D7, okay, So therefore,
here we also have two. Good, So the formula
is working now, can we use that formula now
for conditional formatting? Well, I'm going to go here
to the top left cell. And here we can just
copy the formula. And after you copy that, you select the cells to which you want to apply a
conditional formatting, go to conditional formatting
and choose new row. Now here we want to
have our own customer, which we can do here to butter, use a formula to determine
which cells to format. And here we can simply based
on a formula and that's it. Then the second part
is the formatting. So let's do that as well. I'm going to choose
here green color. Click Okay,
double-check over here. The formula is still the same. And click Okay again. Now clearly something went
wrong because we don't have conditional
formatting applied here on the left-hand side. However, I do have green here, green there, which shouldn't be. Well, this is because conditional formatting
sometimes is a little bit annoying and you will see
that in seconds if we go back to Manage Roles, and we click here
on the role you see it's now referring to be to have their C6 before. So we have to go
back leg gonads rule and just make sure
that this is C6. Click, Okay. Okay, okay. Now it works. Alright, now, how
does it exactly work? Well, basically it took that formula that we
dive down over there, applies it to the top-left
cell in the selected range, drags it down, drag it
to the right. Again. Therefore, we have,
well here true, they are true, they are true, and the formatting gets applied. Do we need still this example here on
the right-hand side? Now of course not. That was just to show you
how the formula works. So over here, I can
delete that again. Perfect. Now let's give this another
try on the dataset below it. Now here we have
a list of hotels. And what I would like
to do is that it automatically
highlights the hotel that we fill out over here. So when events yes, dive in here, it should
highlight the row where we find. Whenever I dive in hired, it should highlight it, okay? Now, something that
conditional formatting should be able to do. However, there's no Well, default rule that does
exactly what we want. So we have to write
it ourselves. So to do that, I first check my former. ****. We need a former that
returns true or false when the hotel name is equal
to the search hotel. Okay? Now, how can we do? Well, we can just
use an equal sign. Here is a search hotel and
we check if it's equal to this one over
here, not false. Now, the same thing I wanna do for all of
the other hotels. Now, if I drag this down, you will see my cell
reference is also moved up. So that means we
need a dollar sign. So let's go back and let's
put the dollar sign. Where think. Alright, you got it again. Now, we needed over
here in front of D 17. Because we want to
fix the row number of the sound reference
de sovereignty. And if we want to
fix the row number, we need a dollar sign in front
of the row number, okay? Now we can just drag
it down, right? And you'll see it turns
true here for IBA. So that part is working. So that means if we use
this formula as an S with a dollar sign for
conditional formatting, then it will give a different
color here to see 25, okay? But we don't only want to
have here a different color, we also want to have a different
color right next to it. So we need to take a Formulas and also
copied over to the right. You see that? Right there
we have where we have true, we have false in the second
column and that shouldn't be. Okay. Now why does this happen? Because, well, as cell
references moving again, so we need more Donor sites. So let's undo what
we did over here. And I only keep that very
first one because here, where do we also
need dollar signs? We need still a dollar sign in front of the dean here
for the search adult, because while the columns
should also not change when we copy it from the left
to right here for C 19. Well, where do we need
the dollar sign here? In front of the column as well? Because that reference to
the name of the hotel column should still always be in that goal and shouldn't
move to the next one. Okay? Once we have that, we can copy it and then select the cells where
we want to base that. We see here for us, we have two times
true and true. Okay? Now this is the formula that we can then finally copy over. So I go here to the top
left cell of my example. I copy it, and then I select the cells to
which I want to apply it. Go to conditional formatting. New rule. Use a formula to determine
which cells to format. Here we can paste it
in, and that's it. And then we can just choose the color again and
we want to apply. Click Okay, click Okay,
and just double-check. Yeah, this time we don't have
to change and back again. Now it's working. So we cannot change this
one to read his son. And now reticent
gets highlighted. Perfect. Now our
own custom roles. So again, just a formula
that returns true or false. Now the next example you're
going to try on your own. Follow me 203 highlighted row. Now here we have
a bigger datasets to make it a bit more realistic. I see there is a distribution channel column and we'd like to highlight all the rows of the distribution channel
that you fill out over here. So if I type in here for web, then it should highlight the entire rows that
belong to wrap. So here's one, There's one, etc. And if I change my mind and
put something else in here, for example, CEA or hotel, Dan should highlight those. So a perfect example of where you can use
conditional formatting, just like that previous example. Okay, now, give it a try.
6. Data bars with icons: The next topic strictly doesn't belong to
conditional formatting. However, it is a good
alternative to data bars, which does belong to
conditional formatting. So therefore, it's
still squeezed it in because it's just too, too nice to skip over it. Now what am I talking about? I'm talking about how we can use custom icons and repeat them a certain number of times on the basis of your
summary values. Now, here, I put an image
of what we want to create. And you see we have
here a certain, I can repeat it as many times as the value
that we have right next. It kind of like
what data bars do. Okay, Now let's give this a try. Here we have the real datasets which we are going to apply it. And you see the number of
employees by activity. Okay, So I'm going to go here
to E7, started my formula. And here we're going to use a
function that's called rat, which just means repeat. Okay, select by pressing tab, the ducks that we're
going to repeat. Let's just go for an x comma. How many times? Well, as many employees as we
have for that activity, brass answer is C. We have now for
the first 16 axis. And I can drag this down
and see we have over here a certain number of axis corresponding to the
number of employees. Okay, So that is working. However, now I want to
have a custom icon. Alright, now, where do
we have these icon's? Well, if you go to Insert and then all the way
on the right hand side, then we have symbol. Now here we can
choose first of all, the font, fonts that
we want to use. Here, you just have to choose
a font that has icons. For example, Wingdings
or web things are ringed in storing
things three, and then start looking,
which I can use. Now here we can look for that little person icon
which is recording. So I'm just going to switch it the web things and then
a little bit lower. And there we have
that little person. But thanks. 128. Okay. You can remember that God, or alternatively
you can say Insert, go back, just copy it. And then you go over here
to where we add the axis. And over here,
paste it in there, press Enter and here, why is it blank? Because, well, this is
Calibri and not updating, so we still have to make sure that we apply the right font. So let me change
this to wrap things. And then I can do the same
for the other ones are I can just drag it down
just like this. Okay, now, this is
one way to do it. Alternatively, you could also
use the character function. Just type in character C-H-A-R. And then the character
number was one to eight. And then press Enter. You see there's also works,
but that's basically it. Now, using these
custom icons and creative ways on your dashboard can really take it
to the next level. Now, give it a try on the next example
dataset here below, where we have different
products with a rating from one to five right next to it, what we would like to see is just the corresponding
number of stars. Good luck.
7. Custom formatting strings: Now that we've talked about
conditional formatting, it's time for the next topic, which is custom
formatting strings, which basically just gives you more control over
number formatting. For example, when
you want to display your values in
thousands or millions, then you can do that with
custom formatting strings. And also when you
want to have maybe a different date formats
which you cannot find the default date formats. Then also here you could do that with custom formatting strings. So let's dive in and
let's start exploring. Follow me to the next follow
0 to formatting strings. Let's open that file. And here on the
first sheet I have some examples setup on which we can test our formatting strings. So let's start here
all the way at the top with this first value, 123,456. Now if we want to change the number formatting,
we can do this. And then the Home tab
here in the middle, there we have the number group, and we have some default formatting options that
we can choose from. This is not always enough. Now, if we want to see
more number formats, we could go over here, and that brings up
this dialogue box. Now, another way to get there, let me click on
cancel is just with control one or right-click. And then Format Cells. And I brings us to
this dialog box here. And here. On the left-hand side,
we basically just have the default options
that you also found under the drop-down. However, here, all the way at the bottom there we can choose custom formatting strings or write our own
formatting strings. And I remember from me and beginning debt
when I saw this, it looks very intimidating. However, they are just a few things
that you need to know. First of all, what are all
of these zeros and hashtags. So this pound symbols, that is 0 is basically just a mandatory placeholder for digit. And the hashtag or pound symbol, that one is an optional digit. Now, for example, if we have this format
explained over here, 0. Now look, the
sample is saying we have now two decimal places. Now if I click on Okay, then here my number has
two decimal places. And as your resume,
it's not really necessary to show
for this number. However, is 0 is a mandatory
place on the fourth digit. So therefore it always
will show two digits. So if I go over here, let me type in
comma and then 15. All right, it shows
also both of them. What if I have three digits? O now a rounded to two, because the formatting
string 000, it always shows, just do. Okay. Now if we go back and I'm
gonna go here to format cells, now, we could change the 00 with £2 symbols
here for the digit. Now let's see what changes. Then nothing changes. We
still have two digits. However, there is a difference. As soon as we change over
here the value to 10. Now, we only have one
digit here at the end. Why does it only show one? Because the hashtag is
an optional digit and it is not really necessary
for the value to show dot 10. We can leave out that last 0, and therefore, it doesn't,
it's not necessary. It doesn't show that this is not only for
the decimal places. If we go back to our
formatting string, we could also change
this to 003 zeros. Click on okay, and
when I type in, let's say just one. Now, you see we have 001. It always shows three digits because in a custom
formatting string, we have three
mandatory placeholder, placeholders for digits. Over here when we have 12th, you see we have 012. And everything that's above
shows in a normal way. And what if we would like to
show a thousand separator? How can we then adapt the
custom formatting string? Now let's go back again and see if we go a bit lower
down, we have hashtag. Hashtag. Hashtag is 0. And overhear that comma
that you see there, that is the thousands separator. Okay, so that's why we also
now need a few more hashtags. And that last one is a 0 because while we always want to
show at least one digit, okay, so let's test this. Let's click Okay. Now we have our thousand
separate, Okay? Now if we go back,
now we can add a decimal places just
by putting in DOD. And then again, how many decimal places we
would like to have. So for example, one
decimal place would be 0. And let's click here on okay, so now that you understand
the differences between the hashtags and zeros, it is time to explore
the other ones.
8. Display values in thousands or millions: We've just got up and running with custom formatting strings and looked at the difference between the pound
symbols and the zeros. Now, let's explore some
of the other things that you can do with
custom formatting strings. Now, first of all, scaling. So when we want to show our values in
thousands or millions. So instead of having to
work with formulas and divide everything
by 1000 million, we can just do this with custom formatting strings and show our values differently. Okay, now, to show you this, that's going into
the next example. Control one to open the number formatting
options and we go to Custom. Let's choose here they're custom formatting string on which we ended this one here where
we had a thousand separator. Let's say we want to
show this in thousands. It's very easy. We just place
a comma there at the end. It's the sample. It already nicely updates. There you go. Okay, what if we want
to have in a millions? Then we can just go back. And then here we can just place another comment
there at the end. Now, because we are below a
million, it just shows 0. So maybe you would also want
to have decimal places. Well, that is still possible. We just go here in front of these two commas
and just do a dot. N is 0, and now we
have 0.1 million. Perfect. And what if we want to
have another digit, then we just placed
in another 0. Now let's click on Okay,
and let's see how it looks. We have now a 0 points tough, but the value itself, you see it's still the 123,456. Okay? Now, what if we
want to make it a little bit clear that
these are millions, then we can just put in an M or an N in the custom
formatting string. It will show the text. So if we go back, now here we
can just add it at the end. And this needs to be in-between quotation
marks, just like this. You see already in the sample. There you go. We have time. I'm going to click on Okay, now, everybody knows
it's in millions. Now what will happen when
r value is negative? Now, let's give this a try. I'm going to put a minus
sign right in front of value shows with a minus sign. But maybe you would
like to have brackets. And how should the value shown when the value
is actually 0? So let's show it like this or should it show
nothing while this, you can also control with
custom formatting strings. So if we go to our next
example over here, and we're going to open up the gust and
formatting options. Then here under costume, you often see very long
custom formatting strings. For example, this one over here. Now, why do we have over here
two times, almost the same. Now what we have here
at the beginning, right in front of that
delimiter, the Golan. Well, that is the custom
formatting string for positive values. And what follows after? That's the custom formatting
string for negative values. So you see that one has a minus, but now I'm going to
change that with bracket. So just like this, for like click here again. You see when we now change a
value to a negative value, it shows with the
brackets around that m is a positive value. Then it takes that first custom
formatting string and 0. Well, then we'll just shows 0. But maybe when it's 0
and we want to show an empty cell or the
tax, not possible. Alright, now, you can also control with
formatting strings. So let's go back until one. And over here we can
add a third part. So let's put another semicolon. And here in-between
quotation marks, I'm going to put
text not possible. Right? Now I'm going to
click here on, okay. It seemed when it's
0, it's not possible. And when we have a value here, it just picks up that first formatting
string, the first part. And when it's negative, well, I put it in between
the brackets. So the first part of
the formatting string, that was for positive values, the second part for
negative values, the third part for zeros. And they can also be a
fourth part for text. Okay, So if there's, for example, text,
now, let's say dust. Then while it shows
here on the left side, just basically as
general, however, we can open our number formatting
and add a fourth part. And then say, this is the text. Now let's click on okay, and every time when we
have some texts there, it will just replace
it with this text. Now, if you don't want
to show anything, which you can do is go back. And let's say we don't
want to show any tax, then don't we just
do quotation mark, quotation mark,
let's say minus 0. We also want to show nothing quotation
mark, quotation mark. Click. Okay, Let's see. Every time when we have text on it is 0, it shows nothing. Only when we have a value, it bumps up, okay, Now sometimes you have
numbers that should be formatted in a specific way, like phone numbers or
social security numbers. Now, let's say we have a
phone number over here. I don't just want to
show a value like this. I want to first show
what the counter God has put a little
plus in front of it. And then maybe put some space right after as some
of the values so that we have blocks of numbers so that it's easier to
see their phone number. Okay, so let's go to
custom formatting. So let's go to our number
formatting options. Custom. And over here we can
just make this empty and start writing
a placeholders. So for example, we could
do hashtag, hashtag, hashtag and then make
blocks of three, for example, let's
see what happens. Now. Over here you see
at the beginning we have to go and record
plus four-ninths. Now, I want to have a
plus in front of it. So here at the beginning, I'm just going to write plus. And then over here we can
write the blocks of hashtags, the placeholders as
we want them to be. So over here, I
would like to have for a four here at the end. I always want to. I have my phone number is
formatted in this way. Now, let's click Okay. And you see that looks
already much better. Now we can use
custom formatting, also kind of like
conditional formatting. It's kind of the old
school way of doing conditional
formatting when there was no conditional formatting. Well, let me show you
we can apply rules. Let's open the
formatting options. Let's go here to Guston. Let's choose whichever one
you like, go for this one. And now let's say we
only want to apply that formatting string when we are bought for
thousands there, which can just go
here right in front, opening in square brackets, say bigger than a thousand, and then close the
square bracket. Okay? That is basically the rule only when the values
above a thousand, that formatting string
will get applied. So when I click on Okay, now you see we have a
thousand separator, but when we are at a thousand, so not above it
that we don't have a thousand separator and the Augustan formatting
string doesn't get applied. Now what could we do when
we don't want to show values above a
certain threshold. Now, then we can
go back over here. And let's say that we don't
want to show the values above a thousand and we can
do mock quotation mark. Click on Okay. Now the thousands still shows, but as soon as we go above
it, Let's see 2000s. Then you see we don't
show the value. However, the value
is still there, but with the custom
formatting string, we make it look as
if it's not there. Now we not only limited
to setting up the rule, can also apply colors. Now let's see how that works. Let's go over here
to the next example. Let's open up our
formatting options. And over here I choose this one. And let's say we want to show the positive
values and blue. Then I again do the square
brackets open, right? The color that I wanted to show, and then square brackets close than the normal
formatting string. Now I want to have also, let's say for negative
values the same. However, then I want to
show the values in red. And I wanted to have a
minus sign in front of it. Okay, let's click on Okay. You see the positive
value shows in blue and as soon as this
is a negative value, then it shows in red, you see how you can
work with rules and color in your custom
formatting strings. However, I would still usually go for conditional
formatting where you have a little bit more
flexibility and a better overview of all of the rules that are
being applied. Okay, now one more, we have over here also the ability to repeat
a certain character. For example, if we go here, two, last one, and we open up our formatting
options, go into custom. Let's choose here the
one that we like. Then we can go over
here at the end, then at an asterisk sign, then I can then we
want to repeat. Okay, let's click on Okay. I'll just repeat that
until the end of the cell. Alright? And we can also
do this in front. So if we go back over here and do the same thing in
the front pic, okay? Then we just have the same thing but in front of the value. Now, these are all
numerical values, dates we've talked
about all the way at the beginning of the
axon boot camp training. However, let me repeat that also quickly to complete the story. Now, over here, I'm
just going to copy this and let me delete
that value that's there. And let me just change the
formatting to general. Alright, and over here we are
going to talk about dates. Now if we have the
date of today, now let's dive in today. Presenter, you see we have
the seventh of August 22. Now we can update
that formatting. Let's go over here to custom. And here we can play around
with the number of these amps and wise to change how that
date format looks like. So if we have one day, it just returns the
number for the day. If we have two days, that
does the same thing, but puts a leading 0
over here always, okay, even if it's not
necessary with three, you get the abbreviated guard for we have the full
name of the Weekday. Now the same thing we can
do with the number of amps. So 1234, just look at
the sample are changing. So if you have forearms than
a write out August fully, if we have just three, you get the abbreviated God. Now, then for a year and the same thing with
one or two y's, we get just the 22. The two y's is then also
with leading 0 or not. And we can increase
it until four. Okay? So now we have everything
written out fully, everything with four times. Now to make it a
little bit shorter, Let's just go for it to this. And then over here, three ones and then four Y's. Now, if we want to ask something
different than spaces, for example, forward
slash, it's possible. Okay, So you just put
the identity like in-between the
difference date parts. Okay? Then once you're happy,
click Okay, and that's it. And what if we want to show a date in a specific language? Well, then we can make
use of language codes. Now, let me show
you how that works. I'm gonna go back and
the easiest way to do it is to go first few today it choose the location
that you want. For example, let's go
here for German, Germany. And then to something that shows the month name,
for example, here, March and our Z max. And then we can go
back to Custom. And now you see you have the language code here
at the beginning, and the rest still
stays the same. The add sign at the end. Don't really need that
for the tax part. And then you just
play around with the number of Ds and y's. And once you're happy,
just click on Okay. Now that Let's get rid of it. And that's my final result. So now that you know
the fundamentals of custom formatting strings, it's time to practice, go to
the next sheet, practice. And over here we have
different values and date on which you can
start practicing. Because some formatting strings.
9. Custom formatting strings in charts: Now that we've covered
the fundamentals of custom formatting strings, and you had a chance to
practice a little bit. It's time to see another
application where we combine custom formatting
strings with charts. Let's have a look
and follow me to the sheet, practice number two. Now here we have a
small little dataset that we're going to
use for our chart. And what do we want
to build is, well, what you see on this picture here, on the right-hand side. You see here on the
horizontal axis, we have basically two rows. The first row that just shows the partner and then
the second row there, what the growth
percentages and a little triangle icon
right next to it. Okay, now let's
get this to work. Now, let's go to the dataset that's here on the
left-hand side. Now for this, we of course
need custom formatting again. So let's see how we
can use it here. Let's go to the
dataset where we have the growth percentages
that we want to visualize later on, on the axis. And let's open the
formatting options. So right-click and then Format Cells or
shortcut Control one. And from here we can go for percentage and we want to
have a percentage with one decimal place now here to customize that a little
bit further later on, we can also go here to Gaston. You see this is the
custom formatting string for these percentages. And if we now click on Okay, you see we have the
formatting applied. But now the interesting part, how can we get these little triangles right in front of it, but also with
formatting strings. Let's go back to our
formatting options. Let's go to custom
that you probably remember from before
that if we want to show something in
thousands or millions and indicate that
with a little k on m, We could just put that inside
of a formatting string. And for the triangles,
it's kind of the same. You can basically put any character here in
the formatting string. Now, what kind of characters? Unicode characters? Now, where do we find them? Not to get a full overview
and to find them an easy way. For websites like Unicode
table.com, for example, if I would like to have a truck, then I can look for truck. Then I want to have this one
over here, a black truck. And I can just simply copy it. And then I can
just place it over here in our format string. And it will always show with a little truck in front of it. But what if we
don't want to have that for the negative values? Well, then we can just make use of the different
parts, so semi-colon. And then for negative values, Let's say we just wanted
to show percentages, then 0 per cent. And let's also put in a minus
sign right in front of it. And click Okay, you see
only the positive values. The positive percentages have a little truck right after it. Now, of course we don't
want to have a truck, we just wanted to have arrows. Well, now we just have to
look for different icons. So let's go back to our
formatting options. And now I'm not going to
have droplets delete that. And here we just need to
look for these arrows. So I'm gonna go back here
to Unicode character. And then here we can search for an up arrow that this is exactly the one
that I wanted to have. And let's just copy it and then paste it over here in front
of the positive values. Then we do the same
for negative values. So arrow down. But that's the one. And then over here, I also copied that one. Go back again. And instead of the minus sign, you're going to have
the arrow down. Alright, let's click Okay, and there you go. Okay, So the first part, the custom formatting
string part that is done. Now, the second part, which is the chart, how do we get these
growth percentages to show on the axis? Now, let's start with
a normal column chart. I'm going to select
the market column and the current month column. And then over here I'm going to Insert and choose
a column chart. But let me put that
image a little bit to the side, just like this. Now on the axis, the purple
selection over here, we have the different partners. Now I want to include
that column over here. Now, let me see if
I can just drag the bottom left corner to
the left, doesn't work. However, we can go
here to chart design. And then here select data. Now over here, what do we want to have on the
horizontal axis? Let's add it in here. We can now select both columns, the first one as well
as the second one. Then click on Okay. Okay, again, I see, That's it. Now we have the growth
percentages on a second row. What if we showed the
negative values in red? Does that get reflected
in the chart? Well, let's try. I'm gonna go back over
here, control one. And here when it's negative, I would like to show it in red. So I'm going to put the
color red in front of it. Click Okay. You see now the negative
values and rats, and then the charge
doesn't get to reflect. That would have been
nice but not possible. Another thing that
you probably want to know is what happens when you add more and more
rows to the axis. Well, that's no problem. You can do it in a similar way as we did for the
rough percentages. However, you need
some kind of title, identifying what
is on that role. And to get that, we can make use of
simple text boxes or shapes now so you can go to Insert and then
over here textbox. And we can then just add a text box that says
over year growth. Then let's make
this a little bit smaller. It's like this. Then we put that right
here at the beginning. Now, of course, you need to be careful that when
you move the chart, that the textbox also moves. Okay, So make sure
that you group them together so that it
forms one whole. And now when I move the group, you see they move together. Okay, could have made that a little bit prettier,
but you get the idea. That's it for custom formatting
strings, super helpful. I hope that these custom
formatting strings that you see when you open
the number formatting, that it doesn't look like magic anymore and that you know
exactly what it means. Okay, now it's time for the
next visualization topic, which is sparklines little
charge that exactly fit inside of one cell that show you in a very
quick way, a trend. But let's see how
we can use them.
10. Sparklines to quickly see the trend: Now it's time for the
next visualization topic, which is sparklines,
little giants that fit exactly
inside of one cell. And they are there
to show you a trend. And on purpose, don't
show much detail. Now let's have a look
how we can insert them into a worksheet and why
you would want to use them. Now follow me to the
next workbook here in 0 of three sparklines
and open the workbook. That's pretty straightforward
to create sparklines. So let's dive in straight away. Now here on the very
first sheet and the very first dataset
that you see here at the top there we have values for different partners overtime. We'd like to have sparklines right next to my little dataset. Okay? Now, how can we do that? Well, I first select where
I want to have them. Then I go here to
Insert and then the sparklines if find
next to the charge. Okay, so here we have
three types of sparklines. The line, sparkline,
colon and then lost. Let's go here for
the very first one, line sparklines that we
can see what the data is. And here we do not include
that it without data. And yet Location Range
is already filled out because that is the range that we
select it over here. Now we could have also
selected the dataset itself. And then the first part
would have been filled out and then say where we'd
like to have it, okay, now, what is important
is that here we have the same number of rows
as over here, okay? Now, then we can click on Okay. And that's it. We have our sparklines. Sparklines, they just
show the trends. There is no visible
access and there are no data labels and all of these kind of things that
you will have four charts. And that's for a reason because the only purpose is
to show a trend. That's it. And if you need more details, well then you should
go for a chart. Now let's try this again. But then four gallons here, I'm going to do it
the other way around. So I select the dataset
itself without the others. Then I go here to Insert. And then over here we have
the golden variation. And now we have to say, okay, where would we like to have it? Or we'd like to have it right
next to my little data-set. Press Enter, Enter again. And now we have also sparklines, but in the form of
grown-ups, okay? And also here not much detail. The last variation is win-loss, which, well, you can
insert it the same way. So let's do this one more time. And we can choose
over human loss. Or maybe one alternative
would be if we select a dataset and here
in the bottom right corner. And then from here we can
also choose sparklines. And here we have the
three variations. Now, let's go for it. When loss. Okay, so now you have
seen the different ways in which we can insert
the sparklines. Now, what formatting
options do we have? Now let's go over here to
the beginning, the delight. And as soon as you select one cell inside of
that sparkline group, then here at the top, we
have sparkline options. So let's have a look. What we find over here. Now. In the middle, there
is just style, which is pretty straightforward. We choose a color
and we find predict. Now let's go in here for now. Then, right next to it, we have sparkling color. And there we can also
change the color. We have little bit more
flexibility and control. However, we also
find the weight, so it's a little
bit in the color. So here we can say how
thick the line should be. Let's go and make them
a little bit thicker. Alright? And then over
here we have market, but we don't have markets yet. So where can we insert these, Margaret and then markers
for the data points, okay, Now here we have
these checkboxes. And if we click on
the high points, now, it always highlights the
highest point with a marker. And the same thing we
could do for low point. If we want to give
these different colors, then we can go to marker color
and say that, for example, the high points
should be in green and the low points
should be in red. So now you basically know
all of the options that you'll find here on the show
and style for sparklines. Now what else do we have? Well, what if we want to
get rid of the sparklines? Well, let's select one
of these sparklines, this one over here and press
Delete. Nothing happens. That is because
while we can still write a value inside
of the same cell, you see it's a different
layer inside of that cell. And when we make the cell wider, the sparkline also
get stretched. But there's a
different layer than the numerical values that we can put inside
of their cells. Okay, and now if
we press Delete, we just delete the value
and not the sparkline. If we want to delete
the sparkline and we can go to
sparkline here at the top and use clear selected sparklines
or sparkline groups. So there's a difference
between sparklines, sparklines group
because sparklines, they come as part of a group. And you have already seen that
when we change the color, because as soon as we
change the color, you see. Then this color gets applied
to the entire group. And if we delete a sparkline, well then we also
have to specify just that one or
the entire group. So what if we just want to have the middle one over
here in a different color, well then we can ungroup
that sparklines. So basically we have two groups. One group it for sparklines and a single sparkline
in its own group. And now, because it's
in a different group, I can give a different
color to that spotlight. Now let me just undo
that control Z so that it's part of the same
group again, once more. And then we go to
sparkline options. Now I can go here all the way on the left-hand side
where we can enter the group location and data
at a single sparklines data. If we just choose one of
these and you say it just bumps up the same
dialogue box from before. Okay, now, what else do we have? We have hidden it, empty cells. Now, this should
look familiar from the chart section where we
had exactly the same options. Okay, Now, same for sparklines. Now let's go back again. And over here we also have switch rows and columns,
which looks familiar. And we can just switch what we have over here on the axis. And so the partners
or the month, okay, now you've seen almost all of the options
for sparklines. However, there's still one
important consideration that you have to make and that is the scaling of the axis. What There's no axis? Well, there's an access. It's just not visible
because somehow XL still needs to scale the values
for that it uses access, but that access is hidden. However, you can still
control the scale. Now, let's have a
look over here and then a sparkline options access. And here you have vertical axis, minimum and maximum options. And by default it's
set to automatic. So basically I'm just means
that looks at the maximum, minimum and all of the other values just
get scaled in-between. And it does that
for each sparkline to each sparkline as
a different scale. And that can be a little
bit dangerous because maybe you want to have a
direct comparison between the different partners. Well, then we could also choose same for all sparklines and
the same for the minimum, same for all of the sparklines. And then it suddenly looks
quite a bit different, right? But now all of these different partners
have the same access. So important consideration
if you want to make comparisons between
the different items for which you have sparkling. Now you probably
also noticed that he and the sparkline
options access, we have the general access
and data access and data access that
becomes relevant when the periods in-between the different columns
is not always equal. And just like for charge, then we have Show access. Well, nothing really happens. Well, show axis only shows the horizontal axis when you have negative as
well as positive values. So for example, here we can say, okay, show the axis. You see Dan, it would show the access at the top
for the negative values, at the bottom for
the positive values. But let me just change that to a line so that's
a bit clearer. Now you see a little
horizontal line for each sparkline and
that's the accident. So now we're up and
running with sparklines. So in the next part we're
going to have a look at another implementation
idea for sparklines.
11. KPI cards with sparklines and custom formatting : Now let's start practicing
a bit more with sparklines. Now, over here we
have another dataset, and I would like to have sparklines here on
the right-hand side. And I would like to still apply some custom formatting
string to make it look like the image here
on the right-hand side. Now let's do the guts and formatting strings first
as a quick reminder. So over here we can select
the cells to which we want to apply these custom
formatting strings. So I'm going to select all
of these lines control on. Then we go to Custom. And from here we can apply
a custom format extreme, which is actually in
one of the defaults. So if we just scroll
down, down, down, down, he's seen here at
the bottom. There you go. If you want to have
different icons, then just do it like we did
in the previous videos. Alright, so this is the
formatting string done. Let's click on. Okay. Now let's decrease the font
size so that it fits a little bit better, just like this. Okay, so now the first
part is working. The next part is going
to be the sparklines. Over here. The sparklines, they
go over two rows. So we can just merge of VAD cells and the same thing
we can do for the next ones. So let's do this five times. And once you have done that, then we can apply the
sparklines area to the right. So let's do it for
the first row. Let's go here to Insert. And let's choose the
line, sparkline. Now, I want to have
it right next to it over here and click on, Okay, now we just have to
repeat this four times. So let's go here
to the next one. To be a bit Gregor, make use
of the quick analysis box, and then sparklines light. Alright, then another
time for partner three, then bargainer for the
last one, bargainer five. So once you have
inserted the sparklines, maybe also want to give them
different colors than just go here to sparkline and choose
the color that you like. And if you want, you can
also add some markers and give them the
color that you like. Okay, so here we are using
sparklines right next to a dataset so that we don't have to look at all of the
individual values. But we get a quick
idea of the trends for each partner just by looking at the sparklines and if there is an interesting trend on it, then we can go over here to the data and
look at the value, so much more efficient. Now another way in
which we can use sparklines is on KPI cards, like the one that you
see here at the bottom. Now here we have a
KPI cards where we are showing the growth
percentage from January to May and we have a title and we have
here the trend line, which is then the
sparkline. Okay? Now how can you rebuild
something like this? We can, first of all, make a little bit more space
by making it a bit wider. And then let me just
select where we want to create the card and
then add border lines. So that's going into border. We can just go for it.
Simple bottom line. Or if you would like some color, just make use of the color
box and click on our outline. Let's make a little
bit thicker like this. Alright, so after you have that, then here we can
give it a title. What are we looking at? So let's go and
write here revenue. And also here we can
give that same color. And let's make it thick. Let's put it in the center. Now right below it, we would like to have the
growth percentage for the first partner
from January to May. Okay, now let's calculate it. So over here we have
to save 755 divided by the 1039 minus one gives
us the grove percentage. Not in this case, it went down. And then we can update the
formatting control one, put a number, Gus them. And then over here
at the bottom, there we have the formatting
that we want to apply. Let's click Okay. And let's make it a little
bit thicker bolt. And then the last
part, the sparkline, let's go here to Insert
and then click on line. And the data range is going to be these values that
we have over there. Then the location range
is the selected cell. And click on Okay, and then we have the sparkline
can add some markers, give these markets
a different color. Then we have our
guard over a year. I just have to
refund a little bit. Let's put the
revenue at the top. Let's make it a little bigger so that it looks more
or less the same. And this value maybe
a little bit smaller, just like this. Okay? So now it's more
or less the same. Okay, So this is our KPI guard. However, I wouldn't put it
like this on a dashboard because this is dependent on the column width
and the rawhide. Instead of that, you're going to place this on
a sheet that you can hide later and you're
going to copy it. And on the sheet
where you want to use it, you just right-click. And then here we
have Paste Special, and then we have
a linked picture. See when I go to Page Layout and also
get rid of the grid lines. That looks pretty good. This is a link picture, so that means when
the data changes, so let me just change one
of these values here. Let's go forward, let's say 600, so that we have some graph. And then I'll go back to Sheet1. You see there's also
nicely updated. So now you've seen how
sparklines work and different implementation ideas
that there's still two important visualization
topics loved, which is Form Controls,
and data validation.
Bas Dohmen, Founder + YouTuber
