Up next in 10
Excel Course For Beginners
67K views
Jul 10, 2023
Here is an Excel Course for Beginners. It covers the most basic concepts and moves all the way to Pivot tables. At the end of the course, you will be able to make a Dynamic Travelling Spreadsheet. Course Files - https://arkiana.com/free-excel-course-for-beginners/ My Weekly Newsletter - https://arkim.substack.com My blog - https://arkiana.com My Website - https://hadlixe.com Let's connect on Twitter - https://twitter.com/arkim_phiri FREE SPREADSHEET TEMPLATES Here are free spreadsheet templates - https://payhip.com/hadlixe SECTIONS 0:00 Introduction to Excel 01:07 Getting Started Excel Interface 03:52 Data Entry and Formatting 09:31 Custom Lists 11:57 Tables, Sorting, and Filtering 15:31 Inserting Rows and Columns 17:00 Conditional Formatting
View Video Transcript
0:00
I welcome you to this Excel adventure. So in this course, we are going to start with the most basic
0:05
concepts and slowly build up our skills. We will learn how to organize data, perform calculations
0:11
create charts, and yze data. Even if you are a complete beginner and you have never used Excel
0:17
before, you'll be able to build a project like this one once you are done with the course
0:21
Actually, this will be our final project that we'll build once we are done with this course
0:26
So this is a travel spreadsheet. You can just choose your destination from here and all the information here will change in line with what you have chosen
0:35
It shows you the total cost of your destination. It breaks it down there. You have a simple graph to visualize
0:42
Then you can also see the places you have not visited and here you'll be able to see the experience that you have had at a certain location
0:49
If it's a place that you have visited, you'll be able to see your comment like that
0:53
So this is what we're going to build once we are done with this course
0:57
You can also consider joining my newsletter where I share weekly insights about data ytics and software development
1:05
The link is in the description. Alright, let's get started. So before we do anything else, you need to make sure that you have Excel installed on your machine
1:17
You can just go here on the Windows tab and here where the applications are
1:21
can just scroll down and look for the program called Excel. So in my case it's this one and once
1:28
you click on it you have this welcome interface there. So this will be the first screen that will
1:34
greet you once you open Excel. So here you can just click on blank workbook and Excel is going
1:39
to open this interface. Now just in case you don't have Excel installed on your machine you can just
1:45
go to your browser any browser of your choice then you can go to office.com this url here
1:52
you'll be required to sign in but it's free to use and once you sign in you can access excel from
1:57
here you just click on it then you can click on new blank workbook here this is going to work the
2:03
same way as this one that is installed on your machine so i'll be switching between this one on
2:08
my desktop and the one online just to show that there's no difference between the two except some
2:13
fewer functionalities and more functionalities here and there. So once you have opened your
2:18
Excel program, this will be the interface. You can see we have tabs here on top and each of
2:24
these tabs contains different tiles that we can see here that will get to use a lot as we go by
2:30
So you can switch between these different tabs here and as you can see as I switch between the
2:36
tabs also these tiles are changing. So right now you don't have to worry what this means, we're
2:41
going to know them as we go by. Then down here you're going to see where it says sheet one
2:47
You can add another sheet by clicking the plus sign here. That would be sheet two. So you have
2:52
this as your workbook. Then within that workbook you have different sheets which is sheet one and
2:58
sheet two just like pages in the book. Then you have these that are about A, B, C, D all the way
3:04
going this side and these will be our columns. That's where we're going to enter our data and
3:09
And when we look at it going this side, we have rows numbered from one all the way down there
3:15
So we can easily reference any cell from here. So if I click this one, this cell is in column F and row 5
3:23
And in case you want to find out something, that can be one of the easy way to reference them
3:29
So this is the basic interface of Excel that we're going to work with
3:33
We're going to put all of our data within these cells. and these cells can be adjusted as we wish including this size you can adjust as you wish
3:42
and so on just so you can fit the data that we need. So basically this is the Excel interface
3:49
Let's go ahead and data some data and see how we can work with Excel
3:56
Now that we understand the interface of Excel we can begin to work with data. So we're going to
4:02
enter our data in any of these cells that we can see here. So I'll start with this cell, you just
4:07
click on it and you begin to type. So I'll just say names. Then you can place tab to go to the next
4:13
cell, which I'll call numbers. Again, tab to the next cell or say dates, like so. Then within these
4:20
columns that I've said name, I'll just enter some random names. And you can keep going. So we can
4:30
see that some of the names are not fitting within this column so you can
4:34
just easily adjust this column. You go in between these two columns here. You can
4:38
see when I reach this line the cursor changes like so. Once it does that you can
4:43
just hold down the left key on the mouse and just drag this line where you want
4:47
it to be like so. If you need more room you can extend it further or just put it
4:52
here. And you can see that these names that I've written here automatically
4:56
they are aligned to the left of this column. When I write the numbers here like that
5:03
you can see that all the numbers are aligned to the right of this column. Now this different
5:08
alignment does not mean so much in Excel but it helps you to quickly know that what you have
5:13
written here is a string and what you have written is a number. Automatically all the numbers and
5:18
the dates are going to be aligned to the right and the strings are going to be aligned to the left
5:23
but you can easily change this alignment and it doesn't have any effect
5:28
So if we write the dates here, okay so there'll be times you write something and it looks like this
5:35
It just means this column is too small for the data that you have entered
5:39
You just go there and adjust the column there like that and you have your data like that
5:44
So you can see again the dates are aligned to the right. So once you have done that, you can format this data that you have
5:52
and it's very easy to do that. First of all just click on this one now that it's highlighted I'll
5:58
just hold down the left key on the mouse and just drag it to the left all the way to this one until
6:04
everything like that is highlighted. Once it's highlighted I can change their formatting. I can
6:10
go to this one and I can say board and this will change into board. I can change the background of
6:16
these cells just going on the bucket here and if you're not sure about any of these tiles and what
6:21
they do you can just hover on them like that and it will give you a simple tip to show you what that
6:27
towel is about i'll just click on this one and i can choose any color here so let's say this one
6:33
like so and we can see the background changes equally we can do the same we can highlight all
6:38
these that we have written there then go back to the bucket i'll just choose a light gray color like
6:44
this one and you can see that it changes like that. This helps to format this data any way that
6:51
you want. Further, there are other tips that are very helpful when you're working with data
6:56
For example, if I want the months, I'll not type all the months that I need. I can just type January
7:03
like so. Then I'll just go on this corner. You can see there's something on the corner and as
7:09
as I hover on it, this sign changes to a small plus sign, I can just hold using
7:14
the mouse there and just drag down like that and it's going to give me all the
7:20
months that I need. You can see automatically it moves from January
7:24
February all the way to December then it starts again So again if we need more room for this month you can just extend this like that and we have that space We can do the same for the days you can say Monday
7:37
again you go on the corner you just drag it down all the way until maybe here and it will give you
7:43
all the days of the week. Again you can give it more room, there we have it. Now this works even
7:50
if you just use the shortcut instead of saying January, just say Jan like that, then drag it down
7:56
like that. It works. And the same is true for the days. You can just say Mon, which is Monday
8:02
drag it down and it does the same trick. So this makes it very easy to work with days and months
8:08
rather than entering these manually. If you want them arranged like that as they are on the calendar
8:14
this becomes easy to do. We can go ahead and get each day of a month, let's say for January
8:20
you can just type 1 January and instead of typing the rest like 2 January 3, we can just drag this
8:26
down all the way until the day that we want, let's say 13 like that. Of course this works even when
8:33
you don't start on the first day, we can start on 20 January like so, drag it down and do to work
8:41
until it goes into the other month. But if you use another string, let's say I'll just put it in here
8:47
let's say data ysis. So if we use this string and just drag it down, it will just copy it into
8:54
the other cells. Let's do that and see. It doesn't do anything special, it just copies it. So if you
9:02
want to copy a lot of text, this is one way that you can do it. So we can see that these have been
9:07
highlighted. Maybe you don't want them highlighted. You can just highlight them
9:11
all of them. You go back to the bucket and you can say no fill here. And maybe if you want a
9:17
different color, you can choose a different color. So I'll just say no fill and I'll go to board and
9:22
remove it and they become normal like the rest of the data. So this makes it very easy to work with
9:29
your data. And this is how you enter your data in Excel. Okay, so let's clear all of these
9:36
including the formats so just highlight everything in here like so make sure everything is selected
9:42
then we can finally use some of the tools here so at the far end here you can see where it says
9:48
clear just click on it and where it says clear all you click on it and everything will be cleared
9:53
including the formats like that now you saw in the previous lesson how we could just enter the month
9:59
like january and drag it down like that and it would fill for the other month that's what we
10:04
want to do in this one want to make our own list that behaves like this because this is just a
10:09
custom list now if there are names or there's some data that you keep using every time or maybe it's
10:15
in a classroom the names of students that you want to be using every time you can just make a custom
10:21
list it's like an automated list that you can use every time you want to so in my case i have made
10:26
one i can just type exuder hills like so then once i drag this down it's going to populate with
10:34
different names a lot of them like that so you can see these are different names that will populate
10:40
just by writing this one and dragging it down so we want to see how we can make this list so we
10:45
want to make a list of data ysis tools that we can use every time that we want to so to do that
10:51
just go on file where it says file here you click on it and down here you can see where it says
10:57
options just click on it then you have this pop-up window just go where it says advance click on it
11:04
and scroll down until you reach where it says create list for using sorts and fill sequences
11:10
click on where it says edit custom list so you can see all those lists that we are using they
11:15
are here including the one that i made which is this one so to make your own list you can just go
11:20
to the top here where it says new list then you type your own list so the tools that we need will
11:26
will just say Excel, Google Sheets, SQL, Power BI, Python, Aram, Tableau, Julia. So once you're done
11:34
with your list, it can be any list that you want. This is just an example. So you just click OK
11:39
Again, you click OK. And just like that, your list has been made. Now to use it, you just go to any
11:44
sale and type the first name on the list. In our case, it was Excel. Once you do that, just drag this
11:50
down and you can see it fills with all that we have written there just give it some space and
11:57
there we have it so next we're going to look at sorting and filtering data now i'll save you the
12:03
time here i'll just bring in some data and i'll leave the worksheet in the description below so
12:08
that you can get it and use the same data so we have the candidate here the first interview the
12:14
second interview and also the status some of the candidates were employed others failed the
12:19
interviews. What we want to do here is to sort these candidates either by interview one or
12:24
interview two. Then later on we're going to filter this candidate based on the status here. So let's
12:30
start with sorting. So sorting is very easy to do in Excel. So if you want to sort by interview one
12:35
here, you can just click on any value in interview one. So just click on this one for example
12:41
then right click on your mouse and you have these options here. And from these options we can go to
12:47
sort and we can click the kind of sort that we want in this case we want largest to smallest
12:52
and then it sorts our data like that from largest to smallest so you can see that in interview one
12:58
the largest score is 97. if we scroll down the lowest score was 51. we can do the same we can
13:04
sort interview two from largest to smallest so let's do that again we like click on any of these
13:10
values like so then you go to sort then again largest to smallest again it will sort the entire
13:16
table from largest to smallest. So for interview 2 we can see the highest value was 96. So that
13:22
makes it very easy to look for the data that we want. But we can take this further and just filter
13:28
this data by all the candidates that we are employed. So to do that again just click on
13:33
any of these, right click then just go to filter, filter by selected cell value like that. So you
13:40
can see it just leaves us with only those that were employed. I can undo this and just click on
13:46
failed then i go back again then filter filter by cell value then it's just going to give me all the
13:52
candidate that failed so the other thing we can do is you can select this entire table like so
13:58
and after doing that we can go to this different tab now it's time to use another tab we can go to
14:03
insert and here on insert we can pick on table just click on it so it will give you a pop-up
14:09
and ask you a few questions my table contains headers you just mark this one and click ok and
14:15
Excel will change this data that we had into an official Excel table
14:20
And once it does that, you can see here there's a new tab called Table Design
14:24
Here you can design your table however you want. So you can see here it has header row, total row, blended rows
14:32
If we uncheck these blended rows, you can see the rows are gone. Then we have these filters there
14:37
If we uncheck the filters, you can see they are gone. But for now we need them
14:41
We want to use them to filter our data. so once you have done that you have an official excel table this table makes it very easy to
14:49
filter your data you can just place this drop down here and you can select the data that you want from this so we don want to see those that are employed place ok and it will just give us those that are failed You can go back to it and this time we just want to see those that are employed We remove the failed Okay and it gives us those You can do the same even using
15:10
these interviews. Let's say we just want to filter by those who got 96. So deselect the rest and
15:16
just choose 96. Then okay and we just have two people that got 96 in the second interview. So
15:23
this table is very very powerful to help you easily filter your data. The Excel table has
15:28
many other benefits but we're going to see them as we go through this course
15:35
Now let's say that for some reason I forgot to add another candidate in here. They were employed
15:41
but I forgot to add their details. Well let's see how we can add them. So wherever we want to add
15:46
them in this table we can just click on that value. Then I'll just like click on this one
15:51
then there is where it says insert now here you have two options you can either insert a column
15:57
or you can insert a row above so you just click on draw above so it will create this empty space
16:03
here so here we can enter the person who was missing just like that would have added our
16:10
employee we can go ahead and add another column if we wanted to you can just click on any column
16:16
in here then light click again you say insert you can insert a column either to the right or to the
16:22
left so in this case let's say to the left you can insert that column name it whatever we want
16:28
like so and we'll have added our column so that's one of the easy ways you can use to add columns
16:34
and rows then for some reason if you wanted to get rid of the column or the rows you can easily do
16:40
that just click on it light click then you can just say delete so it will show you there delete
16:46
table column and you can delete it. Equally you can do the same for that, go and delete and you
16:51
can delete it like that. Now for everything I've done there are a lot of shortcuts that can help
16:56
you to do it faster but we're going to learn those as we go by. So the next thing we want to do is
17:04
now that we have our data here it would be nice if we are to highlight some of this data. For
17:10
example if we have 8 and above want to highlight it in a different color that is called conditional
17:16
formatting and that's what we want to do so for example i'll just highlight this column like that
17:22
then on this tab here i'll go where it says conditional formatting just click on it then
17:27
you have a lot of options here i'll just choose the first one for a start and we can pick also
17:32
the first one that says greater than here you can pick the value that you want to be highlighted for
17:37
interview tool. Let's say we just want 80 and above so just put an 80 there then here you choose the
17:44
color that will be filled once this criteria has been met. So right now it says light light fill
17:50
with dark red color. You can change that if you want you can go for yellow for green or whatsoever
17:56
that you see here. So let's pick the green like that then you press ok and you can see right away
18:01
that these values have been highlighted only the values that are above 80 have been highlighted
18:07
for interview one we'll do the same we'll just highlight in a different color but this time we
18:12
highlight all the values that are above 70. so again you just highlight the entire column then
18:18
you go to the condition of a matching tile you click on it you pick the first option that says
18:23
highlight sales rules then again we'll go for greater than then we want greater than 70. this
18:29
time it will be in red it's okay you click okay then you have all the values highlighted that are
18:34
greater than 70 like that now there are many conditions that we can use this include formulas
18:40
and some special conditions as we'll be able to see later on in the course so again i'll just
18:45
highlight this one just to see the options we have i've got conditional formatting so here you
18:51
can see if you just want the top 10 items the top 10 percent the bottom 10 items the ones that are
18:57
above average, ones below average and also that is not enough you can even make your own rule
19:04
that your data must obey in order to carry out conditional formatting
19:11
Now that we understand how to enter our data let's move on to formulas and functions. Now
19:17
formulas and functions are what makes Excel a very powerful software. They're going to make your work
19:22
in excel very easy and you'll be able to do a lot of work in just a few lines of these formulas and
19:28
functions so let's get started now every time you want to use a formula you must start with an equal
19:34
sign so whenever you enter an equal sign like this excel will know that what you're about to
19:39
write is either a formula or a function so let's start with a simple formula we'll say one plus one
19:45
and when we press enter it will calculate and it will give us two so we can even do bigger numbers
19:51
we can say equals whatever this number is plus this another number excel do the calculation
19:57
and give us that number so this would be addition let's just write here
20:06
okay so i just wrote these headers and did a little bit of formatting we saw how we can add
20:11
we can also do the same for subtraction we can say equals we get the number that we want let's say 10
20:17
minus then it's just say maybe two and Excel will do the calculation
20:21
Of course you can do it even for bigger numbers and Excel will do the calculations. For multiplication you can say the same
20:33
you can say equals, let's say two so you don't use the x sign here, you say two
20:40
use the asterisk that's the one that stands for multiplication. Then you can put any number we
20:45
want enter and it will multiply again you can do the same for bigger numbers and it will do the
20:52
multiplication and for division we'll do the same we'll say equals let's say 10 then for divide use
20:58
the backslash symbol this one and you can divide with whatsoever we want like that then we have our
21:04
value and we can go ahead and use a bigger number like that and excel do the calculations so that's
21:11
how you can easily work with formulas. So this is one way you can do it. The other way you can do it
21:17
is just using cell differences. So let me just get rid of this one. I'll just go to clear here
21:23
and clear everything. So we can have different values in this column and others in this column
21:28
I'll just put some random values. We can have our values there like that. And in this cell
21:36
we can have the results of these values. So we can do our multiplication, our addition
21:41
subtraction and division on these values. So for example, if we wanted to add this one and this one
21:47
we can just say equals, then we pick this value and say plus, and we pick this other value
21:53
We say equals and Excel will get this one and this one and add them and give us the results
21:59
Equally, we can multiply this and that by doing the same. Equals, you get this value
22:05
multiplied it by this value, enter, and then you have your result
22:09
We can also divide, we can say equals this one, divided by this value, enter
22:16
and you have your results there. We can also subtract and say this one minus this one, and we get the result there
22:24
So we can also multiply divide add and subtract even if the numbers are not in the same order like that For example I can get this one and add it with this one
22:35
So here I will just say equals. I get this value, then say plus this one here and enter and have my value
22:43
Just add this one and this one. And you can divide and do whatsoever you want with any value in any of these cells
22:50
As long as you start with the equal sign at the beginning of the cell
22:54
then you can enter the formula that you want. Equally, if you want you can combine these
22:58
operations. We can say equals, I get this value plus this one, then minus this value here, multiplied
23:06
by let's say this value and divide by let's say that value. Enter and we have our value there
23:13
So as long as you put the equal sign at the beginning, you are able to use formulas like that
23:18
Now let's make this a little bit more practical. I'll go to another sheet here. I just created
23:23
another sheet in advance. So this sheet has the name, the days that this employee worked, the rate
23:29
per day, and the total end. So let me just quickly put in the rates here. So we have our rates here
23:37
Now when you look at them, they just look like ordinary numbers like this one. So to show that
23:42
this is currency, we can add either the dollar sign, the pound sign, or whatever currency that
23:47
you're using here so just highlight everything here then we'll go to this number tower here
23:53
so here where this channel just click the drop down then let's go where it says more number
23:58
formats here just click on it then choose currency there and it will show you how your currents will
24:05
look like so mine will be this which is 12 quarter but i don't want it in quarter i'll just choose
24:10
the dollar so scroll down here and choose us dollar there so it shows that it has two decimal
24:17
places i'll just remove the decimal places here by clicking this one or just entering the value
24:23
zero here then i'll click ok and you can see all of them at the dollar sign before the number starts
24:28
now we know what this is so to find out how much each of these people end here we're going to
24:34
multiply this number of days here with this rate per day. So what we'll do, just say equals
24:40
then get this value here and multiply it by this one there. Press enter and you have the value. So
24:47
this person in 40 days, earning $12 per day, and a total of $480. So we can do the same for this
24:55
one. We can say equals, select this value, multiply it by this second value, press enter, and we have
25:03
our value. Now you can see there are a lot of values going down here. We will not keep on saying
25:07
equals, then we select this one and that one and place enter. This will take a lot of time
25:13
What we can actually do is just to copy down this formula all the way to the end. So you can just
25:19
click this one down and you can see on the corner here, once this button changes to this small plus
25:25
sign, you can just drag it down all the way to the end and you to use this formula for the rest
25:30
of these. This becomes very easy to do. So for each one of these, it's just applying the same
25:36
formula and multiplying these. So that's how you use formulas. Now let's look at Relative and Absolute reference. It's something that comes up a lot in Excel
25:48
and it's something that you have to know early on. So what I'll do is just to duplicate this file
25:53
So I'll just go on this one. I'll just hover on this file. Then once I'm there, I'll just
25:59
place ctrl on the keyboard and just drag this file onto this plus sign like that then it will
26:06
make a copy of this file so there's no difference between these two files even though i'm switching
26:11
you can see there's no difference i'll just rename this file by just like clicking on this file and
26:17
say rename so i'll just give it a random name like that nice name then i'll just delete these values
26:24
here, just highlight them, I'll go to clear and clear all. Now let's suppose that the rate for
26:29
each of these was the same. So let's say all of them were earning $20 per day. So we can just put
26:35
a 20 there and drag this 20 down. Then we can do our multiplication by saying equals. We get this
26:42
value, multiply it by this one and we'll get their total earning. Then you can just drag this one all
26:48
the way to the end and it will work. But what I want to do in this exercise is that instead of
26:54
putting 20 all through this column, we'll just delete this entire column. So just select it like
27:00
that or you can select any cell in this column then light click and place delete here and just
27:06
choose entire column like that. So the column has been deleted and just here I'll say rate per day
27:13
which is $20 as we saw like that. So instead of putting it in its own column, I'll just put it
27:19
here. Then when doing the multiplication here, we'll say equals this amount here multiplied by
27:26
the right there, like so. Then press enter. You can see it does the same. It will just get this
27:31
value and multiply it by this one. Then we have 800. Then we can apply this formula all the way
27:37
for the rest of these guys. It will get each of these and multiply it with this one. Now watch
27:42
what happens here. I'll just drag this formula down to this one. Now you can see that we are
27:47
having an error here and there's a zero. Sure, if 13 multiplied by 20, shouldn't be a zero
27:53
So you can see that most of these values are wrong. Now what has gone wrong? So let's just
27:58
double click this one. So you can see that it's multiplying this value with this empty value here
28:04
Now why is it doing that? So what is happening here is an issue of relative and absolute difference
28:09
So when we go to this value and double click, you can see it moves to this one
28:13
When we go to this one, double click this one, it will move to this one. So it moves to this one
28:18
This one will move to the next like that. So this red one keeps going down
28:23
So instead of getting this 13 and multiplying it by a 20, it's getting this 13 and multiplying it by a 0 here
28:31
You can see there is nothing. Then here, instead of getting a 21 and multiplying it by 20, it's getting the 21 and multiplying it by this number of days here
28:41
So when we click this value, you can see it's this one and this one. Again, this one, instead of getting 36 and multiplying it by 20, it's getting 36 and multiplying it by 40, this one
28:53
So this keeps going down. So to avoid that, we'll go back to the formula and we'll change this 20 here, which is the F1, to be absolute reference
29:02
are not relative reference so to make it absolute reference you add the dollar here and you also add
29:08
the dollar there like that so what will happen here is that this 40 will multiply by a 20 then
29:14
in the next one this 13 will multiply by a 20 then the 21 by 20 this 20 will not be moving because of
29:21
these dollars that we have put here once you do that press enter and you have that one i'll just
29:27
drag this one all the way to the end and now you can see that we have the right calculations
29:33
If we double click on this one you can see that this 13 is multiplying with the 20
29:38
In the other instance this 13 would have multiplied by this empty space here. So I hope you get that
29:43
one. The first time I came across relative and absolute reference I had difficulties in understanding
29:49
what was happening but just know that if we don't make this an absolute reference the second time
29:56
we use this formula it will not multiply to the 20 to move one
29:59
step down here. The third time we use the formula here, it will move three times up to there. The
30:06
fourth time, it will keep moving down like that. If you have difficulties understanding relative
30:11
and absolute difference, you can just leave a comment in the description below. I'll just make
30:15
a specific video just on this one. Functions makes Excel a very powerful software. You'll be able to
30:24
do a lot of work using functions. So if you want to use the function, you can just go in any cell
30:29
where you want to use it from and start with equals. Then once you begin to type any letter
30:35
let's say C, you'll be able to see a list of functions in Excel that you can use. So there
30:40
are a lot of them that do a lot of work. And if you are not sure what a function does, you can
30:46
just go to this function helper here. So here is where you're going to see a function. So if I start
30:51
here, you can see there it shows and there are suggestions here. But you can click this one that
30:57
says F with a sub X there. When you just click on it, it will bring a function helper, something
31:03
that will help you to understand functions. So there are a few functions that are listed here
31:08
and as soon as you click on them, you will be able to see the information there. You can also type
31:14
the functions that you are looking for here. So if you have an idea of a function, you can just
31:19
search for it in here. Once you search, you'll be able to see there, and when you have it, you have
31:24
all the information that you need there. So for now we're going to use functions directly in our
31:29
sales. There is no difference between using this and using the sales. So we have this data that I've
31:35
prepared in advance. We have employees there. We have the department and we also have the salary
31:40
So these are salaries per month and we want to find out how much we are paying for salaries
31:45
every month as a company. So to do that if we are just using formulas we can say equals then we can
31:52
get this amount, add it to the next one, then add to the next one and keep doing that until we are
31:58
done with all of them. Then press enter and it will show us our results there. As you can see
32:04
this is not the right way of doing it. If you have a lot of entries, you are going to struggle
32:09
The easiest way to do it is to use functions. So to add all these that are here, we are going to use
32:15
the sum functions by saying equals sum which is this one here. Once you have it, you can just
32:21
place tab on the keyboard and it will pick it. Then underneath here we have a little helper that
32:27
shows you what you must add to this function. So in our case we want to add everything in here. So
32:33
we're just going to highlight everything that is in here. Once we do that we can cross the function
32:38
enter and it's going to add all of these and give us the total results there. So this is much
32:44
easier than adding each one of these individually. So let's quickly look at other functions. So I'll
32:49
just list them here and see how we can use them. Okay there we are. So if you want to find the
32:55
average salary for these employees, we can just go in this one and say equals. We can use the
33:00
average function, average, and again we choose this entire column, close it from there, enter
33:06
and this will give us the average salary. If we want to find the maximum salary which is the
33:11
highest salary here, we'll say equals, max. Again we'll just select the entire column, close, enter
33:18
and it shows us that it should be 3000 and when we look into this column we can see 3000 here and
33:24
there's no value greater than 3000. If we want the minimum we'll do the same equals mean choose
33:30
the entire column cross and it will give us the lowest salary which is 1400 and we can see we
33:37
don't have any amount lower than 1400. For the count it's just going to count all these entries
33:42
individually so in our case we have one two three four five six seven eight nine ten eleven twelve
33:49
thirteen fourteen so we expect to have a 14 here so say equals count and just select everything
33:56
cross enter and we have 14. then the median again we'll say equals median we enter everything and
34:04
this will be our median salary so functions makes it very very easy to work with excel now the other
34:10
thing we want to do here is data validation. So I've left this column for departments so that we
34:16
can use some data validation here. Data validation makes it very easy to enter our data. So instead
34:22
of putting them individually here like marketing, IT, finance and so on, this will take a lot of
34:29
time. The easiest way to do it is using data validation. So I'll select this entire column
34:35
then I'll go to the data tab here on top then once we are here I'll click on data validation
34:40
so here where it says allow you can just click the drop down then you say this then here you put
34:45
in the list that you want it must be comma separated so in our case we want marketing
34:51
it finance animations once you do that you press ok and as soon as you do that when you click on
34:58
any of these cells here you'll be able to see an arrow there so you can just click on it and pick
35:03
any department that you want. Okay so once we do that now we can use another function to calculate
35:13
how many of our employees are working in marketing or in finance in IT and so on. So I'll just put
35:19
them here. So to find all the employees working in IT we'll just say equals count if so that's
35:27
another function that we can use. So the range that we want to count is this one and the criteria
35:33
should be IT which is this one you can just click on it then press enter and there you have it so
35:39
there are only two employees working in IT so if we change this one and move them to IT you can see
35:45
these updates we have three there so we can do the same for animations marketing and finance we can
35:51
just go to this one drop it down so that it copies the formula and you have four employees in animation
35:58
three in marketing and two in finance again if we change move this one from animations to finance
36:05
this one updates accordingly so when you combine data validation and functions you can see already
36:10
that you'll be able to do extraordinary things with excel so there are many things about functions
36:16
that we can learn and there are many functions that we can use but these are just a few for
36:20
start to look at some of them as we go by. So as I mentioned when we are starting, we can switch
36:29
between Excel on the desktop and also Excel on the web. Right now I'm on Excel on the web. I just
36:35
went to this URL here, office.com, then I just logged in. Then I opened this Excel file, which
36:42
is this one here. What I'm about to do, you can do either on the desktop or on the web here. If
36:48
If you have Excel that is older than 2021, the XLOOKUP function will not be available
36:53
If you use it on the web, you will have access to it. Now, the lookup functions are very popular in Excel
37:00
They are very common. There are some functions that we are going to use frequently
37:04
They help us retrieve data very quickly. Now, in this case, we have this data
37:08
Now, we want to get the department and the salary of any employee here
37:13
So in the first place, we want to use VLOOKUP to get the salary and department of Diana
37:19
And in the next one, we want to use the XLOOKUP to get the salary and department
37:24
So here is how the VLOOKUP works So here in the department we say equals VLOOKUP So the lookup value is this name that we are looking for here We can put it in quotation like this We put a comma
37:37
Then the table array, you select your entire table, which is this one in our case. You put a
37:42
comma again. Then for the column index, that's the column that we are interested in. So if we look
37:48
out for the name in this one, what do we want to get? In our case, we want to get the department
37:53
So the department here is column 1, 2. So you put a 2 there
37:58
Just cross the function, enter. And you can see that Diana here is in finance
38:03
And when you go to this one, you can see it's finance. If we change this one and take it to marketing, you can see it also change and goes to marketing
38:12
Change, you put it to animations, it will change there. So it makes it very easy to look for any information that you want
38:18
Again, for the server, we'll do the same. we'll just say equals VLOOKUP. So the lookup value again is Diana. So we don't have to type it here
38:28
We can just click on this one. We can just reference this where we already have the name
38:32
Then for the table array again we'll copy the entire table. The column index here. So want column
38:38
number three. That's where the salary is. One, two, three. So you put a three. Cross the function
38:44
enter and we have 2300. When we go to Diana there it's 2300. So that's how the VLOOKUP works
38:53
The XLOOKUP is a simple and much more powerful function than the VLOOKUP. So the major difference
38:58
is that the VLOOKUP works horizontally. So if this employee name was somewhere here in this column
39:04
it will not give us anything because it will move from here going this side. But in this case we're
39:11
able to get the value because the employee name was here. So just to show what I'm saying I'll just
39:16
cut this here and I'll just paste it here. So you can see that the VLOOKUP in this case will give an
39:22
error because the employee is at the end. It will move from here going this side but there are no
39:27
other values there. So that's what I meant when I said the VLOOKUP works horizontally from left to
39:33
right. So I'll just take it back like that. But for the XLOOKUP it doesn't matter the arrangement
39:39
of these it will still work. So in our case let's get the department here using the xlookup to say
39:45
equals xlookup. So start with the lookup array. We can either type the name here or we can just
39:51
select it from here. Then for the lookup array you just choose the array where this name you
39:55
have selected is in. So in our case this name is in this list. Then for the return array here you
40:02
just choose what you want to get. So we want to get the department so this is the array you want
40:06
to get. For if not found, you put a value that you want to be written if nothing is there. So for last
40:12
we can just put nothing in quotation marks or we can just say not found. And you can just close the
40:19
function, enter and you have animations like that. Again, when we change Diana from animations to
40:26
finance, you are financed like that. You can do the same even for salary because xlookup
40:32
the lookup value again is this one, want it from here and want to get the salary which is this one
40:38
Again if it's not there we'll just leave this one for now, we'll just close it from here
40:43
enter and we have our value there. So if I just delete one value from here, you can see it's
40:49
saying not found because we had mentioned that if this is not available it should say this
40:54
while in the second one we didn't specify. So for the xlookup it doesn't matter the arrangement of
41:00
this so we can again just move this column like we did in the first one just put it here you can
41:06
see that the XLOOKUP still works but the VLOOKUP fails so it's always better to use the XLOOKUP
41:12
because it provides more functionality and it's much more reliable so looking at functions would
41:18
take us a lot of time because there are a lot of functions that would need to cover it can be a
41:23
course on its own but on my channel i have a lot of short videos that look at specific function and
41:29
how they behave. So I'll just leave the link on top or in the description below
41:37
Charts and graphs are a very easy way to explain data. So in this video we are going to understand
41:43
how we can use charts starting with this simple table. So we have the student, we have five
41:48
different tests and want to see the progress within this table using simple charts and graphs
41:53
For a start we're going to use a random function to generate the results for all these tests
41:58
So just say equals, RAND. It doesn't take in an argument. We have that. You can either turn this
42:05
into percentage or just multiply it by 100 so that you can get the whole number. So just change it
42:12
into percentage by just going here and clicking this percentage sign. Once we do that, we can just
42:19
drag this one all the way to test 5 and we can drag this one all the way to the last student
42:26
So just like that, we have all the results that we need. Now we can show the progress for this test in this progress column
42:33
So just click on it. Then we'll go to insert, click on line
42:39
Then here where it says data range, we'll just select all our data up to the last student like
42:44
that. And here where it says location range, we'll select this location
42:50
That's where we want to put our progress up to the last one
42:53
Once we do that, we just press OK and you can see that it puts this line that helps us to understand
42:59
what is happening here. We can change the format of this line and the color. I can choose maybe
43:04
let's say the black one. We can even choose to add the high points here or preferably we can just
43:10
put the markers for every other point but I'll just put the high point and the low point. So like
43:16
that, it helps us to understand what is happening to each of the students. We don't have to look at
43:21
these calls individually. We can just look at this progress line and be able to see what is happening
43:26
to each of these students. So if you don't want to use the lines, I'll just delete these. We can use
43:32
the column chart. You can just go back to this cell, then we'll go back to insert, then we'll click
43:38
column here. Again it will ask us the same question. We'll select the data range and then we'll select
43:44
the column range here. Then we can press ok and you can see it adds the columns like that. Okay
43:49
and if you want it to be more visible you can just adjust these lines like so and you'll be able to
43:55
see what is happening to each of these students like that. So this is an easy way to visualize
44:01
data simply using the line chart and also the column chart but that does not tell the whole
44:07
story we can use charts separately and control them as much as we like. So I have a table of
44:13
products here and I have the sales so what I want to do is to put a pie chart that shows me the
44:18
distribution of these. So to be able to do that you can just select this entire table. Once it's
44:24
selected you can go to insert and here you're going to see different graphs and charts that you can
44:29
use. In our case we're interested in the pie chart so I'll just click the drop down here and choose
44:35
the kind of chart that I need. So you can either choose this one, this 3d pie chart or this donut
44:42
chart here. I'll go with the basic one here and once you do that you can customize this however
44:47
you like. There are some customization here that have been made for you that you can just
44:52
pick like that You can also change the colors here So maybe if you just want this color and it will also change the designs there i just leave it at the basic one like that so apart from these customizations
45:10
here we can do our own customizations for example you can just select this pie chart
45:16
and anywhere just like click and then you can format the data or format the data series
45:22
Let's start with the data labels. So here if you want you can remove the percentages
45:27
and it will be like that. So if you want you can change the position of these labels. You can put
45:31
them at the center like that or you can put them outside or you can choose the best fit which Excel
45:37
will do it for you. I'll just leave them outside for now. There are a lot of customizations that
45:42
we can make. Even this one that we see here we can edit it and just say if you want you can delete
45:49
these legends here by just pressing delete on the keyboard and it will be like that but I'll just
45:56
leave them there. There are just a lot of customizations that we can make. Now while we are on this chart
46:01
we can just right click and change the chart style. So we can choose a different one, we can go for
46:07
this one and it will still work like that. Once you do that you can see even here there's a change in
46:13
the designs. If you click on this one, you have that one there and you have a lot of customizations
46:20
that you can make. So that's the most basic way of using charts. Of course you can play with this
46:25
data and change your own charts and so on. But the other thing I want to show is we have this
46:30
different table where we have the month, we have the sales and also have the profit and want to
46:35
show this on a chart. What we can do is just select the entire table. Again we'll go to insert
46:41
then insert chart. So we can click on the recommended charts here and Excel will look
46:46
for the best chart and that's the one that it's going to give us. So let's click on it and you can
46:50
see it provides some of the charts that we can use. Let's say we pick the first one, then you
46:56
have your chart like that. You have the months here and you have the sales there and you also
47:01
have the line that shows the profit and the line that shows the sales. These two lines can also be
47:07
different chart you can just go to change chart then go here where it says combo so you can see
47:13
there it gives us an example but we can pick this one or that one and we can do a customization here
47:20
i'll just go with this one and i'll just say okay and here we can see we have the buzz for sales
47:26
and we have the line for profit again you can adjust this one as you wish you can change the
47:31
styling here and you can customize it to your satisfaction. So that's the basic way of working
47:37
with charts. You can download these worksheets and experiment with all these different charts
47:43
so that you can gain more familiarity in how these work. So here are just a few techniques
47:50
for data cleaning. Before you do data ysis, you must always make sure that your data is clean
47:55
there are no duplicates, all the values are in the right format, numbers should be numbers and
48:00
strings should be strings. So in this case we want to check for duplicates. So you can either use a
48:06
formula or you can just check using Excel features. So to use a formula you need to use a unique
48:11
function that select your data but in this case we'll just use the Excel feature. So what we'll do
48:16
is just select your entire table like so. Once you do that you just go to the data tab here and you
48:22
click where it says remove duplicates here. So Excel just check for a few things here and want
48:28
to remove from all these columns then you just click ok and you can see from here it says nine
48:32
duplicate values found and removed you click ok you can see our tape has even become smaller so
48:38
all the values that we are here are gone so this is the easiest way of removing duplicates in your
48:44
entries then the other thing you want to look at is there'll be times where you have data just in
48:50
one column but it's hard to work with data in one column like that for example this data has the
48:55
first name last name and the customer id but they're all in one column so there are many ways
49:01
that we can use to separate this data we can use formulas and functions we can just use features
49:06
and so on so for the first one we'll just use an excel feature i'll just select this data here
49:12
once we do that we just go to the data tab then we'll click where it says text to columns just
49:18
click on it and here you choose delimited you click next then here you choose your delimiter if
49:24
your data is separated by commas you put a comma if it's separated by a space you put a space like
49:30
in our case here is separated by a space so we pick a space and you can see here it shows us the
49:35
example of how our data will look like and this is what we want we'll click next if we had a date
49:41
let's say in this column we'll choose date there but as it is is okay we'll just say finish
49:47
and just like that our data has been separated now we can just change the headers here on top
49:55
like so and our data have been separated that's one way you can do it but the easiest way to do
50:01
it is to use the flash fill so for the flash fill you just enter the first entries here so in our
50:06
case we just enter these in their columns manually like so once you do that for the rest you just
50:15
click in this cell and you just press ctrl and e like that it fills for all of them up to the end
50:22
You go in the next one, you do the same, Ctrl E, and it does it
50:27
And even in this one, Ctrl E. And all your data has been separated like that
50:32
You still even have the original that you can make reference to. If you don't want it, you can just get rid of it, like so
50:39
And you have your data there. The flash view can also be accessed here on the data tab, and here where it says flash view
50:46
So that's an easy way of cleaning your data. There'll be other things you come across during data cleaning
50:51
but just make sure that before you do data ysis your data is screened out
50:56
Dates should be in date formats, numbers should be in number formats
51:00
strings should be as they are like that. The other thing that makes Excel very powerful are pivot tables
51:08
So pivot tables are a simpler way of yzing your data. In our case we have the customer here denoted by customer A to E
51:15
We have the product there, the product cost, the revenue and the order ID
51:20
Now we want to yze this data and be able to find how much living we earned from each
51:25
customer and how much living we earned from each of these products
51:28
We also want to find out how much profit we earned from each customer and how much profit
51:33
we earned from each of these products. So if we are to use formulas and functions, this process will take us a lot of time
51:40
But pivot tables make it very easy to get such insight from this data
51:45
So first of all, I'm going to select the entire data by clicking on this one and I'll
51:49
press ctrl shift forward then down to select the entire data. First I'll turn this into an official
51:56
excel table by going to insert then clicking on table like that. Then we click ok. I'll just remove
52:03
the filters on the blended rows. Turning our data into an official excel table is very important
52:09
because in the future if we want to add more entries to this data we can easily add and refresh
52:16
the pivot table and the entire data will populate. So once we do that we can go back to insert and
52:22
this time around we say pivot table This will allow us to yze the data using pivot tables Just click on it and you have this pop asking you whether you want to use this existing worksheet or you want a new worksheet so just
52:35
choose new worksheet and click ok so we'll have this interface here and at the far right here you'll
52:41
be able to see the pivot table fields so we have the fields that came from our table then we also
52:46
have the filters there columns values and rows now to see what is happening i'll just get the
52:52
customers here and drag it into the rows column here like so then you'll be able to see the
52:58
customers like that and you can see that excel quickly give us all the customers that we have
53:03
now if you want to see the revenue by each of this customer you go back to your fields then
53:07
you just get the revenue then drag it into the values here everything you want to calculate will
53:13
go into the values field so just let go in there and you'll be able to see your revenue like that
53:18
So we can format it a little bit, just light click on any of these cells, like so
53:24
Then you can go to number format. Then here we can choose currents and just get rid of the decimals here
53:30
Click OK and we have the current sign. Again I'll light click on any of the cells
53:35
Then I'll go to sort, largest to smallest. And then we are able to see that most of the revenue came from customer E
53:41
Apart from that, you can even rename this header here that says sum of revenue
53:46
We can say total revenue for example. like that. By simply using the pivot table, we've seen that we're able to get the revenue for each
53:54
of these customers very easily. We can take this further and find the total number of orders for
53:59
each of these customers. I'll go back to the fields here and I'll drag order id and just put it in the
54:06
values here. But then when we look at it, this doesn't seem okay. We can't have all these orders
54:11
when in our data we just have a few orders here. So what Excel is doing here is just adding these
54:17
values for each of these customers. So go back to our pivot table. Again, you just like click on this
54:23
one, then go to where it says summarize value by, then just pick count. Like that, we're able to see
54:29
the number of orders for each of these customers. If you want, you can just rename these as orders
54:35
Further, we can add another column here to show this revenue as a percentage, so that we're able
54:41
to know how much in percentages we earned from each of these customers. I'll go back to my fields
54:46
here i'll pick the revenue again and just drag it into the values like that once i have it i'll just
54:52
like click on any of these cells like so then i'll go to where it says show value as then we'll say
54:58
percentage of column total click on it and you have the percentages for each of these customers
55:04
now if there's some of this data that you no longer want to see like the orders for example
55:09
we can just go back to the values here drag the orders out and just leave it anywhere here
55:15
and just like that it goes away. So apart from looking at the revenue by customer we can also
55:20
find out the revenue by the product. So I'll just drag the customer out of these rows and I'll go
55:26
back to my fields here and I'll pick the product and drag them into rows. Like that you're able to
55:32
see our products. We'll do the same process just like creek, sort by largest to smallest and here
55:38
we're able to see that most of the revenue is coming from athletic shoes, then oxford shoes
55:44
and lastly sleepers. So again this shows us how much easy it is to get the data you want using
55:50
pivot tables. Formulas and functions here would have taken a lot of time. So we can also combine
55:56
the products and the customers to see what kind of products each customer was interested in. So
56:02
I'll just go back to my fields here, pick the customer and put them in the rows there and just
56:07
from here you are able to see that athletic shoes were bought by customer C, E and B, Oxford shoes
56:13
by these customers and so on. Then if you want you can just get the customers and drag them on top
56:19
like that and the table changes. Now you're able to see that customer E got this, customer C got
56:25
only athletic shoes meaning that if we have to market to this customer we just concentrate on
56:30
athletic shoes, customer B these ones and so on. Now that we have yzed our data I'll just go
56:36
back into the rows I'll take out the customer ID like so I'll just remain with the product. Now I
56:42
Now I want to find out how much profit we had just from each of these products
56:47
So I also get these percentages out by going into the values and dragging this one out
56:52
You can see that in our fields here we don't have the profit. But as we know in simple terms, profit is always equal to the revenue minus the product cost
57:01
So for comparison's sake, I'll just get the product costs and put them in there
57:05
So we're able to see that this was a revenue for athletic shoes, but this was the cost of
57:09
making these shoes. Now to find the profit which we want to put in this quorum, we're not going to use formulas
57:15
or functions like we would do in a regular table. Instead we're going to do something slightly different that is meant for pivot tables
57:23
So on top here we'll just go to this tab that says pivot table yze, you click on it
57:29
then here you'll see where it says fields, items and sets, just click on it, then here
57:34
you click calculated fields. So here we are going to give the name of the field that we want to calculate
57:40
our case it's profit so for the formula here we'll say equals revenue just insert it minus product
57:48
cost here again you insert it then you click ok just like that the pivot table do the calculations
57:55
and it will give us the profit in here so if you want you can change the name here but ultimately
58:01
we're able to see the profit by product so we can sort this one from largest to smallest and we can
58:07
see that most of the profit came from athletic shoes then slippers and lastly oxford even though
58:13
the oxford shoes at the secondary sleeve venue it did not have the second highest profit so this is
58:19
very important in ysis so we can also see the profit by customer we can go back to our fields
58:25
here we can just get rid of the product like so then we can pick the customer and just drag them
58:31
in there and we're able to see how much profit we end from each of these customers the other thing
58:36
we can do let me just drag out some of these so just remove the product cost i also remove the
58:42
revenue like that i'll just remain with the profit we can get the products here and just put them in
58:48
the columns like that and once we do that we're able to see that from customer a we didn't have
58:53
any profit on athletic shoes only on slippers and oxford shoes for customer c there was no profit on
59:00
slippers and oxford shoes only on athletic shoes and of course this works the other way around you
59:06
you can get the product and put them in the rows and get the customers put them in the columns and
59:11
it will still work like that. So you can see how easy it was to yze this data just by using the
59:17
pivot table. The last thing we're going to do is to use charts and slicers. So here on the tabs on
59:23
top here you can place on insert and here you can pick on any chart that you want. So let's say
59:28
recommended charts then we'll just say okay on this one. So we have our chart here that shows us
59:34
the customer and the profit we made from each of these customers
59:38
Now the most interesting thing about this is you can insert a slicer
59:42
You can just click where it says slicer here. And this slicer can be for different things
59:47
So for last we're going to save for product and click OK. Let me just drag it here
59:52
So if we just click on Oxford shoe, you can see that the chart changes
59:57
to show us the profit only for Oxford shoes. If we click for athletic shoes, again it changes to show us the profit for athletic shoes and it
1:00:05
does the same for slippers like that. And if we click control, we choose this one and that one
1:00:11
again it will just show us for these two. Then you can format these charts and tables however you
1:00:16
want but ultimately we are able to see how easy it is to yze our data using pivot tables
1:00:22
So rather than using formulas and functions, you can just use pivot tables and they'll be much
1:00:27
quicker. Now for the final project we're going to put everything we have learned together and make a
1:00:35
traveling inspiration template. So for this project I'm going to use the online version of Excel, you
1:00:41
can find it on office.com, then you just click Excel here, then you can click new workbook. So
1:00:47
I've already done that and I've already prepared some data that we're going to use here. We have
1:00:52
the title of this table, the destination and all the other necessary information for traveling
1:00:58
So first of all we want to start by formatting this table. So what I'll do is just select this
1:01:03
entire data like so. Then we'll just go to borders here then click on border color then I'll just
1:01:09
choose this green color. I can choose any color of your choice. Once I do that I'll just place on
1:01:14
these borders so that I have borders all around. Then I'm going to highlight these cells and just
1:01:19
merge them so i'll just click this tab here like so and i'll make sure this is on the center by
1:01:25
going to the alignment and choosing the center then i'll just make this board and just increase
1:01:30
the font size now select this one again go back to borders just select this bottom border and i'll
1:01:36
do the same for this one go back and select the bottom border this is already taking shape i just
1:01:42
select this one go to the bucket here to choose a fill color and i'll just choose this green color
1:01:48
there and i'll do the same for these titles on the bucket and i'll just choose a lighter color
1:01:54
like this one so just select these values all of them should have the current format i'll just go
1:02:00
here where it says general then i'll click on currents like that then i'll just remove the
1:02:05
decimals here now for the status which is empty we want a drop down where we're able to choose
1:02:10
whether we have visited the place or we have not visited it so just do some data validation in here
1:02:16
just highlight the entire column then go to data data validation then here on the drop down you
1:02:22
just choose list then here you can put your list to say visited comma not visited just click apply
1:02:30
and you have your drop down so now we can just choose like so
1:02:37
okay so we are done with our table if we visit a place we can change this status to visit and we and we can add our comment here about the experience we had when we visited a certain place Now we going to make a dashboard to be able to show this
1:02:50
information for each of these destinations. So down here just click the plus sign to make a new
1:02:56
sheet and just like click rename and we just call this as dashboard. For a dashboard we'll start by
1:03:04
choosing the destination. So say choose destination. Then we're going to do some data validation again
1:03:11
in here so that we're able to choose one destination. So just go to the data tab, data
1:03:16
validation again. Then we'll select list. Then here we're going to choose all our destination by going
1:03:22
to our data table. Then just picking all the destination from here and clicking apply. Okay
1:03:28
so now we are able to choose the any destination that we want like so next want to see the total
1:03:34
cost of going to this destination so here we'll just say then i'll just highlight these cells and
1:03:43
just merge them home tab and just click merge again i'll put it on the center like that then
1:03:49
again i'll just merge these four cells here that's where the total cost will be so just say merge
1:03:55
and we'll leave it like that for a moment so here the summary of the table that shows us the cost
1:04:00
distribution for this trip so here i'll just say item then here i'll say cost so these items that
1:04:07
want to put here are these that we have here transport food and so on so just manually write them
1:04:13
here okay once we do that we'll format it a little bit i'll just merge these two cells again
1:04:22
like that then i'll put all of them on the center i'll select this entire small table and go to
1:04:28
borders then i'll select these borders i'll do the same for this one select the borders and even for
1:04:35
this one the borders like that just add the bottom border here like so then we'll have a few color
1:04:41
there great now to get to the cost for each of these we're going to use the xlookup and we'll
1:04:48
get the values from our table. So here we'll just say equals x lookup. Then the lookup value is
1:04:53
whatsoever value that will be here, that we'll choose here. The lookup array is from here
1:04:59
from these destinations. And the return array, we want to get the transport. So just choose this here
1:05:06
then cross the function. And so we have the transport for this destination. We'll do the
1:05:11
the same for food and the rest. Okay. So we have our values there
1:05:16
If we change our destination here, let's say Victoria force, you can see that our data
1:05:21
changes as well. Now to get the total cost here of this trip we going to add all these values and just show it here So say equals sum then we select all these values like that We can just put the dollar sign by going back to our currency here and just choose
1:05:39
the currency like that. Again we'll get rid of the decimals. We'll do the same for this
1:05:44
Great. Then just center this like so and then make it big
1:05:50
There we have it. So when we change again there, you can see the total cost changes
1:05:58
Now we want to show a graph here that disputes these values. I'll just highlight this data like
1:06:04
so, then we'll go to insert, then we'll click on the chart there and we have our chart like that
1:06:09
I'll just align it. I'll get rid of these settings here by clicking on it then pressing delete
1:06:17
I'll do the same for the chart title. Great. Now you can just like click on any of these bars
1:06:23
then click format, just click the drop down and we'll choose that green, like that. Then here
1:06:29
where it says data rebords, we'll just switch it on. Then we can go to horizontal axis and just
1:06:35
decrease the gap width here. Great. Now that we have done that, we can move on. So here we want
1:06:42
to show the places that we have not visited so just right here
1:06:49
so again i'll choose two cells just merge them like that then make sure this is on the center
1:06:55
and on the bucket i'll just click that fill then go to the borders and i'll just create the
1:07:01
borders like that then i'll highlight all the cells up to the end go to borders again i'll
1:07:07
click those borders so in here we want to get all the places that we have not visited we're going to
1:07:12
use the filter function and we'll say equals filter. For the array that we want to filter
1:07:18
we'll just go back to our data table here and we want to filter this array. Then you put a comma
1:07:24
for the include this one. You're going to pick the status column. Then you say equals in quotation
1:07:31
marks just say not visited. Close the quotation, close the function and we have all the places that
1:07:37
we have not visited like that. Then here we'll make another table that shows the experience we've had
1:07:43
in that place that we have visited. So here we'll just say my experience. Again we'll choose the two
1:07:50
of them, match them, then we'll make sure this is at the center and also at the center there
1:07:57
Make it board and just make sure that we choose this again
1:08:01
like that. Then we're just going to meet all the cells that are inside here
1:08:10
Great So for the experience from the table we are going to get this comment column that is here So to do that we going to use the XLOOKUP once more So just say equals XLOOKUP
1:08:22
For the lookup value, pick this one. The lookup array, we go back to our table and pick this
1:08:28
And the return array, what we want to get, we want to get the comments there
1:08:32
Cross the function like that. So we're able to see our comment here
1:08:36
So let me just bring this on top. like that. But then we can see a zero here. So we don't want to see a zero. If we have not
1:08:48
visited that place, we want to show that we have not visited. So we're going to modify our function
1:08:54
I'll just copy everything here and cut it. So here we're going to use the if function. We'll say
1:08:59
if the value we're looking for is giving us zero, it's equal to zero, then we should say
1:09:07
not visited yet. Otherwise we should show the actual comment which is this one. Okay so this
1:09:16
may look complicated but what we are simply saying is if we check for the comment and we find that
1:09:21
it's giving us zero then we're going to say not visited yet. But if it's not zero then we're going
1:09:27
to show the actual comment which is this one. So press enter and you can see it's saying not visited
1:09:33
yet because we have not visited this place. So if I change to the place that we have visited
1:09:38
and you can see the comment here. So what we're going to do is just to go on wrap here on the wrap
1:09:44
tile. Just click on it and there you have our comment. Now when we change to another place
1:09:51
you can see the comment changes as well. Now this looks great already. So we have our dashboard here
1:09:58
We're able to see the information that we need. The last thing we're going to do is just go on view
1:10:03
Then here we'll just remove the grid lines by unchecking this box here like so and look at that
1:10:10
Our dashboard is ready and beautiful Now whenever you change the destination every information here updates
1:10:19
So this was our final project to put everything that we have done together and be able to see what we can build
1:10:26
So leave this worksheet in the description below so that you can work on it and make the changes that you want
1:10:33
All right, we've come to the end of our Excel course. I really hope you had fun and you've
1:10:37
learned Excel. Now you can go out there and practice. I've left a lot of Excel files in
1:10:43
the description. You can feel free to download them and use them to make your work easy. So
1:10:49
thank you so much for watching. You can also subscribe to my newsletter and to this channel
1:10:54
I'll see you in the next one
#Data Sheets & Electronics Reference