Transcripts
1. Introduction to the Course: Hello and welcome to the course. If you ever thought to yourself, is that a better way to
do it while using Excel? Then you know that
you're probably right. You may be a newbie or
an expert in Excel, but may still find
yourself stuck at times on simple scenarios
like cleaning the data, printing a document correctly, running multi VLookup,
custom formatting of data, or maybe just using the
plain simple sum function in complex scenarios that are accomplished in
just a few clicks using some hidden takes. Xl could give you exactly what you need to
separate yourself from the rest of your colleagues by working smarter or
being more efficient. And my role here is to give you the tools that will
differentiate you and grow your chances to
be a valuable piece in any competent,
impress your boss. And then while your colleagues
welcome to amazing Excel, Tips and Tricks volume do
for every Excel expert. Hi, my name is Suarez darkly, and I will be your
instructor for the course. I'm a computer engineer
and a quality expert. Diehard fan of Microsoft Excel, with over two decades of experience in using it
day in and day out. I didn't multiple courses online starting from basic
to advanced Excel, dashboard and PivotTable
masterclass and advanced dashboards, but
professional presentations. In this fast-paced course, you will learn some of
the coolest tricks in Excel that will make you look like an Excel
expert on that. To do all. The best part of this
course is that you can find the best exit tips and tricks that will not
only save you time, but could also give you the confidence in
working with Excel. And even if you're an
advanced Excel user, that is something for you in this particular course as well. For easy understanding
and bite-size learning. Every lecture covers five
to six topics so that you can practice those steps before you proceed
to the next lecture. All the resources needed for your side-by-side
learning and practice. Attached to the
course, I would highly recommend you download them
first before you start. The resource file is indexed. So you click on the
relevant topic as you learn and you can use it for your own practice and dirty if you need any
help on the courts, do let me know in
the comments section and I will be happy to help. Also, I will be releasing
new volumes of the course. So you're learning never stops. Remember that there are only
86,400 seconds in a day. And you can make
time or save time. You can only spend
it and invest in. The lung is amazing tricks and make the maximum out
of your Excel skills. I'm super excited to see
you inside the course. So let's get started.
2. Printing Tricks and Tips in Microsoft Excel: Many a times while using
Excel will land up in a situation where we
go ahead and create a workbook which is
well-organized and up-to-date. You have formatted
it really well. But when you go ahead and
decide to print it out, it looks like a mess. Now, Excel worksheets
don't always look great on paper because they are not
designed to fit on a page. They are designed
to be as long and wide as you need them to be. Now this is great for editing and looking at
them on the screen. But it does mean that
your data might not be a natural fit to fit on a
standard sheet of paper. And our objective in this particular lecture
will be to go ahead and learn the tips
and tricks that will help us in
printing the document, giving it the look and
feel that we prefer. So let's get started. So let us assume that this is the data that I have at my hand. And my boss comes to me
and says that, okay, I want to go ahead and print this data on a piece of paper. Then you would say, Okay,
what's the big deal in that? All I have to do
is just click on the File tab and then click
on the print tab over you. But the moment I do that here
is where my problem starts. You will observe that my entire data does not
fit on one single page. In fact, it is
overlapping and half of my data is getting
printed on a second page. Now, this is not the way I want to go ahead and
print this data. So here is my first tip that can help us to
resolve this problem. What we can do over
here is we can go to the Page Layout
option over here. Then I will come down to this option that
says orientation. And then I click on landscape. Okay? And then if I go to
my file option over here and then click on print, I can see that, okay, my data somewhat fit on
this particular page. Let me just try to go ahead and adjust this a little more. Let me come back to
my page over here, and I will just go ahead
and delete this additional, extra column that
I have over here. And let's see if my data fits. Okay, so I will go back to
my file option, go to Print. And I can see that my data
fits perfectly right now. Okay? So this is how I can go
ahead and fit the data just by changing the
orientation of the page. But now, let's look at this second problem
that we have over here. Now if I look at my data, I will observe that
my data is slightly indented towards the left-hand
side and towards the top. I want to go ahead
and fit my data somewhere exactly in the middle
of this particular page. So how can I go
ahead and do that? So for that, what I can do is I can just go ahead and click on this particular button over here that says normal margins. Click on the
drop-down over here, and then I can click
on custom margins. And over here I have this particular option
that says center on page. So I'm gonna go ahead and put
a checkmark on horizontal. And I will go ahead and put
a checkmark on vertical. Then click on, Okay. Now you will observe
that my data fits exactly in the
middle of the page. It looks pretty decent this way. Okay? So this is another
way wherein I can just go ahead and
adjust my data on the page by just going ahead and changing the
margin settings. Let's go ahead and bring
it back to default. So I'm going to go to
my Custom margins over here and go ahead and
change it to defaults. Okay, Then I will go
back to this page. Now let us look at
a second scenario. Now my boss says
that I don't want this data to be printed
in a landscape format. I want the orientation
to remain as portrait. And still the entire data has
to fit on one single page. So I will go back
to my orientation, change it back to portrait. Okay. And if I try to go ahead
and print this document, I come back to my square one. Okay, the same problem
comes up again. Now, let's look at another tip of going ahead and solving that. What I can do is I can come to my page
layout option over here. Okay? And then I will just
go ahead and change the scale of this
particular page. So I will just go ahead and
change this to maybe 90%. Okay, and see if it
fits in one page. So let me go to
File, go to Print. And you will observe that
nothing has actually changed. Let me just bring it back
to maybe 80 per cent, or let's try 70 per cent. Okay? And now let's
see if it fits. So what I've done is I've scaled down my page to 70 per cent. If I go to File and
then click on Print, you can see that it's printing the whole document
on one single page. Okay. Let's go back to our
main page again. And let's say, I just
bring this back 200. Okay. And I don't want to go ahead and manually keep on
changing the scale. I want to excel to go
ahead and do that for me. So here is a quick tip. What you can do is
you can come down to this particular option
that says width. Click on the drop-down and
then click on one page. Okay, so I want my entire width to be fitting on
one single page. And then I will come
down to height. And I will change this
as well to one page. Okay, Let's come back to file
and let's click on Print. And you will observe that the document prints
on one single page. Now I can go ahead and make
that change is on this page. Also. If I click on this
drop-down over here, you will observe that I have a lot of options over
here where it says fit sheet on one page with
all columns on one page, or fit all rows on one page, I can use these options. So as of now, what I
have done is I have used this particular option fit
sheet on one page and that is the reason it is going
ahead and printing my entire document
on one single page. Okay. Let's go back now. Let us say that I
want to go ahead and just print a particular
area on my page. Okay, so let's say I
just want to go ahead and print the data for team a. Okay. I don't want any
other data to be printed when I go ahead
and hit the print button. So how can I go ahead
and achieve that? So what I can do is I can
just go ahead and select my data will go to Print Area, and then I will click
on Set Print Area. Okay? Now my print area is set. If I go to File and
then click on print. And if I look at the preview, you will observe that
only my selected area is getting printed
on the document. Okay. If I want to go ahead and
add some more area to it, Let's say I also want to go ahead and print the
data for team B. I can just go ahead
and select that data, go to Print Area, and then I can click
on add print area. And now if I go to File
and then click on print, you will observe that
on my first page, it is printing the
data for team a. And when I hit the
second page over here, it is printing the
data for team B. But what if I want to go
ahead and print the data of team a and team B on
one single page itself. Then what I can do is
I can once again go to my print area and then I will
click on Clear Print Area. And then I will select
these two datasets, blueprint area, and then
click on Set Print Area. And now if I go to File
and then click on Print, you will observe
that it is going ahead and printing
the data for team a, as well as DMB. Once again, if I want to go ahead and clear the print area, I can click on
Print Area and then click on Clear Print Area. Okay? Now, here is another trick that I want to go
ahead and share with you that is looking
at the page breaks. Now, if I go ahead and then click on this
View tab over here, and then click on
Page Break Preview. You will observe
that this is how the printable area on
my page looks like. Okay? If I go ahead and click on
File and then click on print, this is how my data looks. Okay? Now let's go back to the
normal view over here. Okay? And then we will
go to Page Layout, and then I will go ahead
and set this to automatic. Okay? And when it is set to automatic, if I click on View and then
click on Page Break Preview. This is where the page
break is occurring, okay? And therefore, any data after these dotted lines is
moving on to another page. So if I click on File
and then click on Print, you will observe that the year is where my
dotted lines were and any data after the dotted lines is actually getting
printed on another page. So what we can do is we can actually go ahead
and make use of the page break option in order to go ahead and
set our printable area. What I can do is I can
just go ahead and select this dotted lines and I can
just go ahead and drag it. Okay? Once I've dragged it, you will observe that the
dotted lines have disappeared, which means that
everything that you see on this particular preview
page over here is now going to be printed
on my original document. If I hit the print button, it will print everything that you see on this
particular page. So if I click on File and
then I click on Print, you will observe that
everything gets printed. Okay, let's go ahead and
boot one normal view. Let's go ahead and create
a copy of this data. And I'm going to go ahead
and paste it over here. And let's say I want
to go ahead and print everything on
one single page. Now. Now if I go to my
Page Break Preview, you will observe
that my entire data is getting printed
on one single page. Which means if I click on File
and then I click on Print, you will see that entire data is coming on the first page. Now let's say I want to go
ahead and insert a page break. We'll just about my
column a and column B. So what I'm gonna
do is I'm going to select the entire row. And then I will go
to Page Layout. And then I will click on breaks. And I will say
insert page break. Okay, The moment I did that, Let's see what happens. I will go back to my
normal view where you, you will observe
that you will see some dotted lines
over here, okay? Which means a page break
has been inserted. If I go to the Page
Break Preview, you will observe that there
is a blue line over here, which means here is where
my page one will end. And from here on page
two will be printed. If I go to File and
then I click on Print, you will observe that all the
data which we're showing on page one will be printed
on one single page. And all the data that we're
showing on page two in my Page Break Preview has
moved on to another page. Okay. If I want, I can just
go ahead and drag this. And this will ensure
that everything gets printed on page one only, which means all that
information will be printed on one
single document. Okay, let's go to a normal view. And then I will click on File, and then I will click on Print. And you will see everything
is coming on one single page. Okay? Now, one more thing that
you will observe that whenever you go ahead and
hit the print button, you will observe that wherever there are page
breaks on your page, you will start seeing
these dotted lines. Okay? There are also there
on the columns, but right now they
are not visible. But wherever you
see a page break, let's say I go ahead and
insert a page break over here. Okay, so what I do is I select this particular
column over here, go to my page layout option, and then I click on breaks, and then I click on
insert page break. Now if I go to File and
then click on Print, you can see my printable area, but when I come
back to this page, you will start seeing these
dotted lines over here. Okay? So these are nothing but the
page break dotted lines. Okay. So let's say I want
to get rid of them. I don't want to see these
dotted lines on my page. So how do I go ahead
and get rid of them? If I just go ahead and
click on Remove grid lines, you can see them very well, okay, they are more evident now. Okay, so this is a page break. Then you can see that I can see a page break line
over here as well. And I want to go ahead
and get rid of them. So how can I do that? Very simple. All I have to do is click
on my file tab over you. Go to Options. Then I will come down
to this particular tab that says Advanced. I will scroll down over here. I can see a menu that says display options for this
particular worksheet. And I have a checkmark on something called as
show page breaks. So what I'm gonna do is I'm
going to uncheck this and then click on OK. And
the moment I did that, all my page breaks have vanished from this
particular page. Okay, So this is how you
can go ahead and use the print options within Microsoft Excel and bring the data in the required format. So I hope you enjoyed this particular lecture and I shall see you in the next one.
3. Data Cleaning Tips and Tricks in Microsoft Excel: Excel data cleaning is a
significant skill that all business and data
analyst must possess. In the current era
of data analytics, everyone expects accuracy and the quality of data to be
of the highest standards. And a major part of
Excel data cleaning involves the elimination
of blank spaces, incorrect, and
outdated information. Some simple steps can easily do the procedure of data
cleaning in Excel. And in this particular lecture, we will learn about some of the fundamental and
straightforward Excel data cleaning procedures. So let's begin. Now let us say that this is the data that we
have at our hand. And this is the format in which
I have received the data. Okay, So basically what has happened is my address
has come over here, my city has come over here, and nice date has come over you. Now I want to go ahead
and clean this data in such a way that the address comes in
this particular column, the city comes in this
particular column, and the state comes in
this particular column. So how can I go
ahead and do that? Now practically it
is not possible to go ahead and do this manually. If at all you have lot
of data in front of you, then in that case, this particular trick can
really come in handy. All you have to do
is come down to this cell under
the address column and say equal to and select this particular
value and hit Enter. Then come down to City, hit equal to and select this particular
value and hit Enter. And now come down to the state. Equal to select the state
and hit enter. Okay? Now select all these
three rows over here and then just
drag the data. Okay? You will observe that depending upon this
particular format that we have created, all the other information has been copied exactly
in the same manner. Now, with this particular
data selected, all I have to do is
press Control G. Click on special, click on
blank, and then click on. Okay. And you will observe
that all my blank cells are now selected. Now with all my
blank cell selected, all I have to do
is press Control and the minus key
on my keyboard. And then I will select this
particular option that says shifts cells up and
then click on Okay. And with that, my
data is sorted. Okay? You will observe that all the addresses has come
under the address column. All the cities are
under the city column. And then all the states are
under the state's column. Easy, isn't it? Let's move on to our next tip. Now, let us go ahead and look
at this second scenario. I have received information
in this particular format. And now I need to start the
data as per the product ID, the product and the status. And this particular value is P 101 should come under
the product ID. The name of the product should
come under the product, and the status of
the product should come under this
dataset over here. Okay, so I need to go ahead
and rearrange this data. Okay? So how can I go
ahead and do that? Ready simple. So what I'm gonna do is I will select any random
cell over here. And I will type at, now where is my product ID? Product ID is in this
particular cell B5. So I will say at B5, okay? Then I will say at B6. And then I will say at B7. Okay, instead of at, you can write or in anything
that comes to your mind. Why I'm doing this? Just wait and watch. Okay? Now what I'm
gonna do is I will type at b eight, okay? Then I will say at B nine, and then I will say
at v ten. Okay? And then what I'm gonna
do is I will just select these two rows and
I will just drag this data up to B9 team, okay, because my last
value is in B9 TWO, if I had more data, I would have dragged
it more further. Okay. Now with this particular
range selected, all I have to do is press
Control and S on my keyboard. And this will pop up the
Find and Replace menu. Okay? I will click on Replace
and I will say fine what, so fine what I'm going to say, add, okay, Then I will say replace the app
with the equal to sign. Now what will happen is once the act is replaced
with an equal to sign, this will become equal to V5, equal to V6, equal to B7. Okay? And let's see what happens
when I click on, Replace All. Click on Okay, and
then click on Close. And now I have the data in
the format that I need. Quick and easy, wasn't it? Let's move on to our next step. Now let us look at
the data that we have at our handover you and you will observe that there are a lot of empty rows in-between
this data here and there. Okay? Now currently this
data is not much. It's only running up to 41 rows. So probably I can just go
ahead and delete it manually. But let's say this particular
data was running in thousands and thousands
of rows and you had these kind of blank
rows here and there. Then practically removing them one-by-one is not gonna be easy. So is there a shortcut
that we can use? Of course there is. Let's learn it over here. So what I'm gonna do is
I'm going to just go ahead and select my
entire data over here. Okay? Then with this particular data, I'm going to press the
F4 key on my keyboard. That brings up the go-to menu. Click on special and I will select blanks, and
then click on. Okay. You will observe that all the blank rows in my dataset have been
highlighted now. Okay? Now, all I need to do is
click on the Home tab and click on this
particular button over here that says delete. And I have this
particular option that says delete Sheet Rows. The moment I hit that, you will observe that all the blank rows in my dataset have been removed
and my data is clean now. Okay, good, isn't it? Now let's look at
another scenario. We'll now once again, in this particular data, I have the name, I have the
product one and product two. What you will observe that along with the blank rows over here, there are certain instances in which the sales for my product to our empty or probably for my product one
is empty somewhere. Okay. This could be because
maybe Ben Perez has not done any sales
for product two, or maybe Herman
Williams have not done any sales for product one. Okay. Which is quite possible. So in that case, I only
want to go ahead and remove those rows where the data
does not exist at all. So I don't want to go ahead and delete this particular
row because just because there is no
data for product two, okay? I only want to go ahead
and delete these kind of rows where there is
absolutely no data. Okay, so how can I go
ahead and achieve that? Now, very simple, what I can
do over here is I can just go ahead and make use of
an empty column over here. Okay? This is just
a helper column. And now what I'm gonna
do is I'm gonna make use of the COUNTIF
function over here. Okay? I'm going to just
go ahead and select all the data that I have on
this particular over here, and then close the
brackets and hit enter. Okay? So there are three
cells which have data, so it has gone ahead and
return the value is three. I will just go ahead
and scroll this data. And you will observe
that all the rows in which there is no data absolutely have returned
the value as 0. Now what I'm gonna
do is I'm going to just go ahead and select this particular row
or you go to Data, and then I will click on Filter. And then I will just
go ahead and click on the drop-down under the
empty section over here. I'm going to select
all and I will just select zeros over here. Okay? So that will go ahead and
select all my blank rows. So all I'm going to
do is select them, right-click on it, and
then click on delete rows. And now I will just go ahead and put a checkmark on all the data. I have my data
available over here. And now I can just go ahead and delete this helper
column over you. And my data is neat and clean. Now many a time when we copy
data from another source, a common type of error pops up. What happens is if you look at this particular name
over here, then Paris. There are some extra spaces between the first
and the last name. Similarly, if I look at Kyle car and I just
click over here, I will observe that there is an extra space before
the first name. Okay? Similarly, if I look
at Sonya moulins, there are a lot of extra spaces before the first name
has been entered. Okay, now what happens
is these types of errors create bigger
errors in future. For example, if I'm
running a VLookup, the VLookup will give me an
error because it will not be able to pick up the first
and the last name correctly. So even before we go ahead and do some kind of
a data analysis, it is very important
that we go ahead and clean the data that we have
received from another source. So let's say if I have
to go ahead and clean this first and last name for all the different
names that I see in this particular list over here. Then I can go ahead and
use the trim function. So let me just type in
clean name over here. And then I will go ahead
and use the trim function. I would say trim open brackets. I will select this
particular value, or you close the
bracket and hit Enter. And with that, you will observe that it
has gone ahead and removed any extra spaces before, after, or in-between
the text value that I have over here. Okay, and once I
have the clean data, all I have to do is
just click and drag. And it will go ahead and clean the data for the entire
source that I have. That was a handy one. Let's move on to our next step. Now let us say I received the data in this
particular format, wherein there are a lot of
duplicates within my data. For example, Lauren Gibson
from California appears twice. Okay. If I look at any other data, let's go ahead and look
for Irene Mad Dogs. Irene Mad Dogs once
again appears twice. Okay. So there is a combination of data that is appearing
twice in my dataset. So how can I go ahead
and remove that? I can easily use the Remove
Duplicates function. And then I can remove all the duplicate values
within my dataset. And I can also go
ahead and remove any duplicate combinations as well using this
particular function, which lot of people do not know. What I'm gonna do is
I'm going to select this entire data and then
click on Remove Duplicates. Then it is asking me
which particular column. So I'm going to keep
a check on both of them because I
want to go ahead and remove the duplicates of the name and the state
combination together. Okay, so I will keep a check mark on my name
as well as on my state. And then I will click on, Okay. It says three duplicate
values found and remove. 23 unique values remain. Now if I look at my data, there are absolutely
no duplicate values within my dataset. So that's how I can
go ahead and remove duplicates from any
dataset that I receive. Now let us look at this
particular scenario. We'll, let's say I
receive the data in this particular format wherein
I have the sales figure. But along with the sales figure, there is a lot of text
in each of the cell. And I want to go
ahead and only get the numerical values from these particular
cells over here. For example, I only
want to know that the series for bookcases is 366. The sales for
chairs is only 389. So then how can I go ahead
and accomplished that? What I'm gonna do is I will
just type 366 over here. Then if I go to this
particular cell and type 389, you will observe that
the autofill function in Excel is prompting me that it can go ahead and easily give you the output
that I'm looking out for. Let's say if I go
ahead and hit Enter, you will observe that
it has gone ahead and return the
numerical values to me. But along with diet in any of the cells after
the numerical values, if there was any
particular text. I can see that text
has also come along. And I want to go ahead
and get rid of that. So what I'm gonna do
is I will come to my cell number two are you? And I will just go
ahead and delete this. Okay, and hit Enter. The moment I did that, you will observe that my
entire value is now cleaned. All I need to do over here
is now go to my Home tab and then give it a dollar sign,
removed the decimals. And my job is done. Easy, isn't it? I hope you enjoyed
this lecture on data cleaning and it goes a
good value add for you.
4. IF Function Tips and Tricks in Microsoft Excel: Now we all know that IF function allows you to make logical
comparison between a value and what you expect by testing for a condition
and returning the result. If that condition
is true or false. That is, if something is true, then do something, otherwise,
do something else. But that is a lot more that we can do with the f function. And our objective in this particular lecture will be understanding the
different tips and tricks that we can
implement using the f function. So let's begin. So let us start off by using the basic syntax of the
function first of all. And let's assume that this
is a seaside data of a team. And the condition is any seaside which is about
eighty-five percent, is considered to
be a good seaside as it is considered
to be a bad season. So how will I use the
IF function over you? My basic syntax of the
function goes like this. So after if it is asking
me for the logical test, so I'm going to say this
particular Seasat is greater than or equal
to 85 per cent, then return the value as good. Okay? And then I will give double-quotes
and then give a comma. And then I will say,
if the value is false, then return the
value as bad. Okay? And then I hit Enter. And if I just drag this, it will go ahead and tell me whether my Seasat
is good or bad. Okay, So this is the basic
functioning of the function. But now let's say I have
another condition over here. And the second condition is
depending upon the Seasat, my team members will
receive a bonus. Okay? So in other words, anybody who has called a
seaside about 90 per cent, we get to 1000
rupees as a bonus. Anybody about 80 will get 750, about 70, it will be 300. Okay. I have to
just correct this. This has to be 60 and below. And anybody who's a below 60 does not get any bonus at all. So how will I go ahead and
feed this condition over you? I will go ahead and make use
of the nested IF function. And my nested if function
will go like this. So I'm going to say is equal to, I'm gonna say if open brackets. And then I will get
my first condition. My first condition
is if this value is greater than or equal
to 90 per cent, then return the
value as thousand. Okay? I will go ahead and put my second
condition over here. So I will open up another
if condition over here. And then I will say
if this value is greater than or equal
to 80 per cent, then return the value as 750. Okay? I will open up another if
condition, open the brackets. My third condition is about 70. So I will say if this
value is greater than or equal to 70 per cent, then return the
value as 300. Okay? I will open up another if
condition, open brackets. And then I will say if
this value is greater than or equal to 60 per cent, then return the value as 100
and return the value as 0. And then I will close
all the brackets, okay? And I will hit Enter. So currently Ben Perez has
a C set up 90 per cent, so he gets a bonus of 1000. Okay? If I just go ahead and drag this formula, it will tell me, depending upon the
sea SAT score, how much bonus has been achieved by each of
the team members? Now you observe that I
had to open up a lot of brackets and closed a
lot of brackets over here. Ok, so many times people find this particular
formula a little confusing. And this is where the EPS
formula will come handy to us. Okay? And let's see how the h function will give us the same output. And my apes function
goes like this, is equal to ifs. I will open brackets. I will go ahead and get my first logical condition over here. And my logical condition is
this particular value greater than or equal to
90 per cent, okay? Then I will say return
the value as 10 thousand. Okay? And then I will go
ahead and give a comma. I will not open any more
brackets over here, and I will just go ahead and
get my second condition. And my second condition is this particular value is greater than or equal
to 80 per cent. Then return the
value at 750 comma. If this particular
value is greater than or equal to 70 per cent, then return the value as 300. Okay? Then I will say if this particular value is
greater than or equal to. 60 per cent. Then return the value as 100. Okay? And at the end, now I will go
ahead and give it a comma. And I will just give
the value as one, okay? Give a comma, and then I
will give the value as 0. Ok, and then close the
bracket and hit Enter. It says for 90 per cent
Ben gets $1000 as bonus. And if I drag this
particular formula, you will observe
that it gives me exactly the same output. So this is how my
hips function works. Easy, isn't it? Let's move on to
our next function. Let us look at a
second scenario. We'll now this is, let's say my tuition schedule
or my study schedule. Okay. So every Sunday I
study languages, Monday, I study maths, Tuesday
I study science, and so on and so forth. And over. You're just next to the date. I want to know on
which particular date, which particular subject
Do I have to study or which particular tuition
classes I have to attend? Okay. So in order to know which particular day
of the week it is, I will go ahead and make use
of the weekday function. And my weekday function
goes like this. I will open brackets. Then it is asking me
the serial number. So over a year, then I will give a comma. And then it is asking me in which particular format I want. It wants me to return
a value as 123, etc. Now what does this mean is
if I select the number one, it means my Sunday
will be my day one and my Saturday
will be by D7. Okay, so my data is arranged
exactly in the same fashion. So I will go ahead and
select the number one. And then I will close the
bracket and hit Enter. And then I will just go
ahead and drag this data. So now I have got the weekday
for these particular dates, but I'm not interested
in knowing the weekdays. What I'm interested in knowing is on which
particular day, which particular subject
Do I have to study? And what I'm gonna do is
I'm going to make use of the Jews function to go
ahead and achieved that. And my 2s function
goes like this. I'll say choose open brackets. Then it is asking me
the index number. My index number will be returned by my weekday
function over you. Then I will go ahead
and give a comma. Then it is asking me
what is the value one, value two, value
three, et cetera. What I will have to
do is I will have to go ahead and manually feed this. Okay, so I'm just going ahead
and typing it. Language. Then Matt, science. Remember to put
double-quotes all the time because this is text. Okay? Then I would go ahead
and once again open double-quotes and
typed language again, social studies, science again. And then Matt. And now I will go ahead and close
the brackets and hit enter. Okay. So it tells me that
on first of Jan, I have to go ahead
and study math. And if I just go ahead and
drag this particular formula, according to my timetable, it will tell me on
which particular date, which particular subject
Do I have to study? So that is how you go ahead and make use of the Jews function. Let's move on to our next tip. Now, instead of going ahead and making use of the
language, choose function. Yet as a shortcut tip that I want to go ahead
and share with you. Now my weekday function will return the value
for the week day. Okay? So what I
will do is I will once again use the
weekday function. And then I will give
the number one, and then I will close the
brackets and hit enter. Okay, that gives me the
value for the weekday. And now I'm interested
in knowing on which particular David subjects
do I have to study. Okay, So for that, what I can do is I
can go ahead and make use of the index
function as well. Okay, So I can say index. I will open brackets. Then it is asking me,
where is the array? My array is over you. Okay? Then I give a comma. I will freeze the data
using F4, give a comma. And now it is asking
me the row number. Okay? My row number comes from
the weekday function. I will close this and
I will hit Enter. And then I will just go
ahead and drag this. Okay? So once again, I get
the same output. Only difference is instead
of using the 2s function, I made use of the
index function, which gives us the
output much quicker. Let's move on to our next tip. Now let us look at this
particular condition over year. Over year. I have a list of certain
cricket players. And then over here I have the
list of football players. Now, depending upon the
name that I put over here, I want to know which particular
sport the person place. Okay? So you will say, okay, I can go ahead and make use
of the VLookup function. But my data is spread across
two different datasets. So how will I go ahead and use the VLookup function over here? Very simple. What I'm
gonna do is I'm going to say IF error. Okay? Then I will open
brackets and I will get my first we lookup
condition over here. Okay, So I would say
VLookup open brackets, and I will say find this name in this particular data and
return the value. Okay? However, if this
particular data is not found in this
particular table over here, it is bound to return an error. So if you get an error, then I would say, look for the other value using the VLookup function in this
particular table over here. Okay, the one which is below. I will go ahead
and give a comma, give the number two comma 0, and then close the
bracket and hit enter. Okay, So now we're at
coli, plays cricket. So I got the output as cricket. But let's say I go ahead and put the name Ronaldo over you. You will see that it returns
the value as football. Okay? If I select any other name, let's say like Ben Stokes. It says he plays cricket. So that's how you
can go ahead and use the IF function and create a complex VLookup function in order to play with two
different data tables. I hope you enjoyed this trip. Let's go for the next one. Now here's the bonus tip that
I want to share with you before I go ahead and end
this particular lecture. Now let's assume that I have these conditions
over a year. Okay, So any Seasat which is above 85 per
cent is excellent, about 80 per cent is good. About 70% is fair and
less than 70 is poor. So using the h function
that we just learned, I have gone ahead and given
the grade accordingly. But let's say I want
to go ahead and change this good,
fair, excellent, poor into this beautiful emojis and given new look
and feel to my data, then how can I go
ahead and do that? Okay, so what I'm gonna do is over a year instead
of excellent, because it has picked
up the value from you. Okay? Instead of excellent,
I'm going to highlight that particular
cell and I will hit the Windows key and the
dot key on my keyboard. And then I will go
ahead and select this particular face or your smiling face
with sunglasses. Okay? And I will hit Enter. So now you will observe that
wherever it was excellent, it has already gone ahead
and changed the emoji. Okay, now let's select this particular cell
which says good. And then I will go ahead and hit my windows and my
dots over here. I will select this
smiling emoji, okay, smiling face
with smiling eyes. And then I will just go
ahead and close this. You will observe all
the goods have changed. Now, let's change the fair ones. Once again, windows
and the dot key. Let's select this particular
phase neutral face and then hit Enter or just
close this particular window. And then for the poor ones, I will go ahead and hit the
windows and the dot key. And then I will go ahead and
select this frowning face. Ok, and then close the window. And you will observe that
all the goods but excellent, poor, has now
changed into emojis. I can select these
emojis to my font color. And then I can go
ahead and select the font color of my choice. Okay? I can even make
them bold, incline, and even underlying, okay, because they act as a font. Okay, so that's how you
can even go ahead and add some life to your
data using emojis, using the IF function. So I hope you enjoyed
this particular lecture and I shall see you
in the next one.
5. VLOOKUP Tips and Tricks in Microsoft Excel: Now VLookup is one of the most important
functions in exit. Then you need to find information
in a large spreadsheet. You're always looking
for the same kind of information we make use
of the VLookup function. Now we look up works a
lot like a phone book. But you start with a piece
of the data that you know, like somebody's name in order to find out the data
that you don't know, like their phone number. However, we look, a function has its own limitations because of the kind of
syntax that it uses. However, our focus in this particular lecture will be to understand those drawbacks. And then how we can go about tweaking the VLookup
function year-end dead in order to go ahead and get the output that
we're looking out for. So what are we waiting for? Let's begin. Now let us look at a scenario that we have at our
hand over to you. Okay? Let's assume that this is a database that has
been provided to us. And now based upon the product ID that I
select over a year, I want to get the
relevant output in this particular range. So for example, when I select
the product ID as 1002, I get the product
as desktop prices, 4,500, quantity 69, and then
also the respective total. Okay, and if I look at
this data over here, it is giving me the exact output that I'm looking out for. If I change this
particular value, it is going ahead and giving
me the relevant output. Now you would say, what
is so great about that? Anybody can achieve that
using the VLookup function. However, the drawback of
the VLookup function is that let's say if I go ahead and put a VLookup function
for my product ID, then I have to again type the VLookup function
for product, then price than quantity, then total, and so
on and so forth. And let's say if this
list is pretty long, Let's say I have
around 1520 columns, then I will have to go
ahead and write down the VLookup formula for each
and every column over here. Okay? And that can be really a
time consuming process. Then how do I go
ahead and fix that? Let's look at this
wonderful trick over here. So first of all, what
we will do is I will just go ahead and delete
everything from you. Okay? Then I will go ahead and type
the VLookup formula, weird. And my basic VLookup
formula goes like this. So I will say VLookup
open brackets. Then I will say lookup for
this particular value. And then I will give a comma. Then it is asking me
where is my table array? So I will say, okay, my
table array is over here. I will freeze the table
array by pressing F4. Then I will give a comma, and then I will go ahead
and give the value as one because my data is
in column one over here. Okay? Then give us 0 and
then close it and hit enter. Okay, So this is my
basic VLookup function. However, if I try to go
ahead and drag this formula, you will see that I will get an error because
I cannot just go ahead and drag the VLookup
formula just like that. I will have to go ahead and
tweak this a little bit. And here is where Mike column function
will come handy for me. So first of all, I will just
delete this error from you. Now, first of all, let's understand what the
column function does, okay? And my column function
works like this, I will say is equal to columns. Then I will open brackets, and then it will just ask me which particular array
to look out for. So I will just go ahead and give this particular array and then
close this and hit Enter. It gives me an output as five. Now why did it give
me an output as five? Because when I selected this
particular array over here, it has five columns. So it basically calculates
the number of columns in that particular given
at a and gives me the respective output. Okay? So if I just go ahead and
change this from F9 to just E9, what will be the output? Definitely, it will give
me the output as four. Okay? So this is the basic functioning
of the column function. So now what I'm gonna
do is I'm going to use this column function, merge it with my
VLookup function, and then get me the
required output. Let's learn how to do that. Okay, So let me just go
ahead and delete this. What I'm gonna do is
I'm going to just go ahead and say columns over here. And then I will open
brackets. Okay? Now what I'm gonna do
is I'm just going to go ahead and select this
particular cell over here. Beefy. Okay, and then I will
press F4 to freeze it. And now it asks me
where does my end? And 1 second I
will select beefy, but this time I will
not freeze the range. Okay? I'm not pressing
F4 over here. So you will observe that the dollar signs
haven't appeared here. Okay, so what I've done is I've selected my range
from B52 to be 15. But when my range starts, I have gone ahead and please
the rows and columns, but I haven't done that
where my range ends. And because of
that, what happens? Let's see, I close
this and I hit Enter. So right now it says the
number of columns is one. But when I just go
ahead and stretch this, you will observe that it
is giving me number 2345. Okay. And why is it giving
me this output? Because when I selected
my range over year, I made use of the absolute and the relative
reference over here. Okay. I froze the beginning
part of my reference, but I did not freeze the closing part of my
reference over here. And therefore, every time
it will keep on adding that one extra column for me and give me the
output respectively. And now what I'm gonna
do is I'm going to make use of the column
function over a year. So what I will do is I will just hit the backspace over here. And now where it is asking
me that column index number, I'm going to make use of the
columns function, will you? Okay? I will open the brackets. I will tell Excel that might
columns dots over here. Okay, press F4 and
freeze the reference. And then I will
once again select this cell and close
the brackets, give a comma, give us 0. And then I will just close
the brackets and hit enter. Okay, So now it is giving
me the product ID. And now if I just go
ahead and drag this, you will observe that it is giving me the respective output. Okay? And this way, what I
can do is I can always merge my column function along
with my VLookup function. And that will save
me the trouble of typing my VLookup function
over and over again. So I hope you enjoyed
this wonderful trick. Let's move on to our next tip. Now let us go ahead and look at another cool trick with
the VLookup function. Now let's see, the data that
I have at my hand is in this particular format wherein
for the same product ID, I have been given
different sizes. Okay? And I want the output
for product ID equal to 1001 and size equal to large. Okay? Now how do I go ahead and get the required output using
the VLookup function. What I'm going to do over
here is I'm going to go ahead and play a
small trick over here. I will just go ahead and
insert extra column over you. Okay? And I will call this
column as a helper column. And in this particular column, I'm just going to use
a very simple formula. I'm going to say equal to
select the product ID, give ampersand, which
is the sign that can be produced by hitting the shift and the number seven
on your keyboard. And then I will just select this particular
cell and hit Enter. And then I will just drag this. Okay? And now I will just go ahead and make use of the
VLookup function, but I will just tweak
it a little bit. So see what I'm gonna do. I'm going to open brackets. And what I'm gonna do
is I'm going to say VLookup this particular cell. Then once again hit the ampersand and then
select this particular cell. Then give a comma. And now I will just go
ahead and give my range. And my range will
start from here. Okay, From my helper cell. Okay? Press F4, give a comma. And my total is in the
fourth column over here. Okay, so I will give
the number four, give a comma, give the
number 0, and hit Enter. And it gives me the
required output. Okay? Now let's say I want the
output for product 1002, size equal to small. So what I'm gonna
do is very simple. We look up open brackets and then I will select
the product ID, give them present,
select the size, give a comma, select
my range from here. Okay? And then I will
give the number four, give a comma, and the
number 0, and hit Enter. And it gives me the
relevant output. Okay? And now what I can
do is I can just go ahead and hide this cell. And it still gives
me that output. Amazing, isn't it? Let's move on to our next step. Now let's look at a
scenario that we have at our handover you we have been provided this
particular database and I have applied a
VLookup function over here. However, this VLookup function
is giving me an error. Okay? I have applied the
VLookup function exactly in the format that
it should be applied. However, I'm still
getting an error. Now, this usually
happens when you transfer data from
one sheet to another or copy data from a particular source and just go ahead and paste
that data in Excel. And many times your
numerical values get copied in a text format. And that is exactly what
has happened over u. Now there are two ways of
going ahead and fixing that. Let's look at the first way. I will just type the
number one over u. I will just go ahead
and copy this number. Okay? And then I will select
this entire range over here. Right-click on it,
click on paste special. And then I will just go
ahead and change it to values and then select Multiply. So all these values
will be multiplied with one and they will change
to numerical format. So if I click on, Okay, you will see that now I
get the relevant output. So this is one way
of going ahead and fixing this
particular problem. I can just go to
this number section over here and remove
the decimals if I want. Okay? This is one way of
going ahead and fixing this. This is another way
in which we look up, can't take care of this. Let's look at this
second method. So what I'm gonna
do is I will just go ahead and uncheck everything. Okay? And again, my numbers are
back to the text format. And now what I'm gonna do
is I will just go ahead and tweak my VLookup
function a little bit. So what I'm gonna do
is I will just type in VLookup open brackets. Then I will give double-quotes. Give double-quotes again. Okay? Then give an ampersand, then select this particular
number, give a comma. And now I will just go ahead
and give my range from you. And then I will give the
number four comma 0, and then close the
brackets and hit Enter. And it gives me the
required output. Okay, even if I go ahead and
change the number from here, it still gives me that output. So these are the two
ways in which you can actually fix this issue, wherein your numbers have been copied as text in
an Excel sheet. Now here is another
cool VLookup trick to add to your kitty. Now let's say this is the data that I have at my
hand over here. And then I only have this
information where it tells me that the name
starts with k by l. So I don't have the complete
name of my team member. I only know the first
three characters. And based upon that, I need to know what are the sales value for
this particular person. Okay, so here is where I can go ahead and make use of a
very cool VLookup trick. And this is how it goes. I'm going to type in
VLookup open brackets. I will just select
this particular value. Okay? Then I will go ahead
and give an ampersand. And then I will open brackets. Then I will just give the
asterix sign over here. Then I will close the
brackets, give a comma. And now I will go ahead
and select my range away. You give a comma, keep the number to give a comma, and then type 0 and then close the brackets
and hit enter. Okay? So now it tells me that the sales value is
due like 76,180. Okay. And if I look at
the value for Kyle, it returns the
exact value for me. If I just type in, let's say MAB yo-yo, it returns the value
for maple Lindsay, and the value is one
leg six to 2670. Exact match. Cool trick, isn't it? Let's move on to our next one. Now let us look at the scenario that we have
at our handover you, I have two different
data tables over here. And I want to go ahead and get the sales figure for
all these team members. Okay? Now if I just go ahead and
apply a basic VLookup formula, let's see what happens. I'm going to say VLookup. And I would say look
for this particular value, give a comma. And then I will just select this particular range
because I cannot select two different
range in VLookup. Okay? So I will just freeze
this, give a comma, and then I will give the
number to give a comma, and then give the
number 0 and hit enter. Okay? Now let's see if I drag
this formula, what happens? Okay? So up to Sherry silver, I got the values because all this data was available
in my data table over here, in my data area
where you, however, Hoffman is in this
particular dataset and I did not select
this particular dataset. So how do I go ahead
and tell Excel that if this value is not found
in this particular dataset, then look for that value in
this particular dataset. Very simple. All I have to do is make use of the IFERROR function of u. This is how I go ahead
and modify my formula. So just before my VLookup, I will say if EDA open brackets, okay, I will give my first
VLookup function over here, which means if it returns
the value villain, good. However, if it does not
find the value over here, then go ahead and look
for the second V lookup. So what it says is, what should be the value
if an error is found? So if an error is found, then go ahead and do
a second VLookup. So I will open brackets. I will just go ahead
and once again select this particular value
over you, give a comma. Now I will select this
particular table. So in other words, I'm
telling Excel that if you don't find my data in
the first data array, lookout for that value
in this second data. So I will give the
number to give a comma, the number 0, close the
brackets and hit Enter. And now if I just go ahead
and drag this formula, let's see what happens. Okay, I get the value
for all my team members. So what Excel did it search for the value
in this first table. And when it found an error, it started searching for the relevant value
in my second data. So this is how we
can go ahead and make use of the
IFERROR function. Do multi VLookup if our data is stored in
different databases. So this is how we can
go ahead and play around with VLookup and use a different permutations
and combinations to go ahead and get the
relevant output that we're looking out for. So I hope you
enjoyed this lecture and I shall see you
in the next video.
6. Custom Formatting Tips and Tricks in Microsoft Excel: Now usually if we have a data that is input in one
particular cell, but overflows in
corresponding cells. Then in that case usually we go ahead and select
that entire range, click on Merge and Center, and then our data gets
aligned properly. However, the problem with
that is that in that case, all the three cells will be considered as one single cell. Okay? And we can't do
anything about that. Then in that case, what is the other
alternative that we have? Here is a quick tip. What I'm going to do
is I will just go ahead and remove this
merge and center. And then what I'm gonna
do is I will just select this entire range
under the Home tab. Click on this arrow over here, under the alignment section
that this alignment settings. And when I click on that, it will open up this
particular format cells menu. What I'm gonna do is I will go ahead and click on alignment. And then I will go
ahead and click on this particular menu
that says horizontal. And then I will select this particular option that
says center across selection. And then click on, Okay. You will observe
that it has given me the very same effect. But still all the cells
that I selected in that particular range
exist independently. Cool, isn't it? Let's move on to our next tip. Now let us say that this is the data that I have at my hand. And I have been assigned
a task to go ahead and add bullet points to this
particular column over here. Then how can I go ahead and achieve that in Microsoft Excel? Very simple. What I will do is I will just go ahead and select
a blank cell. And in this particular
blank cell, what I'm gonna do is click on the Insert tab and
then click on symbols. When I click on symbols, you will observe
that this particular symbols menu pops up over here. Okay? And I will just click on
this subset over here. And then I will select
Ding bats Soviet. Okay? When I click on Ding bats, you will observe that this
particular menu pops up. And I will select any
particular type of bullet points that I want to
go ahead and add to my data. Let's say I select this
one and then click on Insert and then
click on Close. So you will observe
that the bullet point has appeared in this
particular cell over here. And now I want to
go ahead and apply this bullet point to all the
data that I have over here. So all I have to
do is just select this bullet point and then
press control C to copy it. And now what I will do is I will select this particular
data over you. Right-click on it, then
click on Format Cells, then come down to
the custom option, go to general, delete this, press control V to copy
that symbol over here, give a space, and then
just type at the rate, Okay, Now add the rate basically represents the data that I already have over
you or whatever data exists in that
particular cell. Then I will just go ahead
and click on, Okay. And you will observe that
the bullet points have been added to my
entire range, yoyo. So that's how you
can go ahead and add bullet points in
Microsoft Excel. Cool tip, isn't it? Let's move on to our next one. Now, Excel provides several
building number formats. And you can use these
built-in format as is, or you can use them
as a basis for creating your own
custom number formats. Now when you create
custom number formats, you can specify up to four
sections of the format code. These sections of
the code define the format for positive numbers, negative numbers, 0
values, and text. In that order. The section of the code must
be separated by semicolon. Now the following example shows the four types of
format code sections. The first format is
for positive numbers, the second format is
for negative numbers. The third one is for zeros, and the fourth one is for text. Now if you specify only one
section of the format code, the code in that section is
used for all the numbers. If you specify two sections
of the format code. The first section
of the code is used for positive numbers and zeros. And the second
section of the code is used for negative numbers. When you skip code sections
in your number format, you must include semicolon for each of them missing
sections of the code. And you can also use
ampersand or text operators to join or
concatenate two values. Now if all this sounds a little confusing at first,
do not worry. As we learn these four
different examples that we see on the screen, things will become more and
more clear and you will have a good hold on using the custom formatting
techniques in Excel. So let's learn them one-by-one. Now, using this
particular concept that we've just learned, Let's look at some of the
custom formatting techniques that we can achieve
in Microsoft Excel. Now let's say that this is the data that I have
at my hand here, Austin numbers in
this entire column, regions that have selected. And I want to go ahead
and hide this data. Now you would say
it's pretty simple. I will just select this
entire range and I will probably just change my
font color to white. But then if somebody goes and changes the background
color over here, you will see that the numbers
become visible again. Then how can I go ahead and hide these numbers just in case if I have some kind of a sensitive data that I want
to present in a dashboard. And I don't want this
particular data to be visible to a third party. So in that case, what I would do is
I'll first go ahead and change this
background color back to normal and I will just
bring the numbers back. All I have to do over here is I have to just select
this entire range. Right-click on it, or you can, what you can do is you can press Control one on your keyboard. And that should pop up the
format cells menu for you. Now, go to Custom. And over here where
it says general, I'm going to delete this. And then I will just give
three semi-colons, Okay? And then hit, Okay. And you will observe that
the numbers are now hidden. Okay? If I go ahead and select that particular
cell over here, you can see the numbers
of visible over here. But then even if I go ahead and change the
background over here, you can see the numbers. So basically they are headed. Okay, now what has
happened over here? Now, as explained earlier, Excel looks at my data in
this particular format, okay, So general semicolon, general, semicolon,
general, semicolon gender. And when I go ahead
and delete this, and I will delete
this general as well. I will delete this one as well. And then I will delete
this one as well. Okay, So basically what
remains is three semi-colons. And basically what I'm
telling Excel is do not show me any data in that
specified range. And that is the reason the
entire data is hidden. And if I want to go ahead
and get this data back, all I have to do is
just press Control, Shift and Tilda and
my data is back. Cool trick, isn't it? Now let's look at the
second scenario where you have this particular sales data that has been provided to me. And I want to go
ahead and represent this data in thousands
and millions. Then in that case, this is how the custom formatting method
will help me achieve that. First of all, I'm going to
select this entire range yo-yo press Control
and the number one. And it should pop up
the format cells menu. Okay, and now I want you to just pick attention over here. So I'm going to come
down to my Custom. And then you can see that the number is visible over here. Okay? I will come to this section
over here and delete this. And now you will
see that I can see the number without any
commas in between. Okay? And now I will just
type in the number 0. Okay? Then I type in 0,
nothing really changes. But now just keep observing what happens when I add a comma. At the moment I add a comma, you will observe that
the last three digits in this particular
range have vanished. Okay? So basically, it
has gone ahead and converted my data
into thousands. And what I will do is
I will just give us p, So we give double-quotes and I will just type keoyo, okay, K represents thousand, Okay, and then hit Okay. And you will observe
that my data is now converted into thousands. Okay? Now, if I do all I wanted
to in the decimal format, I will go back to my
format cells over here. We're just after my zeros. I will go ahead and add two decimal points and
then click on, Okay. And you will observe
that the data is now converted into thousands, okay? Now similarly, how do I go ahead and convert my data
into millions? I will select it,
right-click on it, click on Format Cells. And then once again, I will come down to
custom formatting. I will just go ahead
and delete whatever is there, type the number 0. And now instead of one comma, I will give two commas. And you will observe that now the six digits from your
has vanished. Okay? So instead of this 25 lakhs, 61,300, you only see the
number three over here. Okay? And now what I'm gonna do
is I will give a comma, give double-quotes,
and per million, I will type m. Okay, and hit Okay. And you will see that my data
is converted into millions. But what it has done is it
has rounded of the figures. So I need the
decimals over here. Right-click. Go to Format Cells. And once again, just
after my zeros over here, okay, I will add two decimal
places and then click, Okay. And you will observe
that the data is converted into millions
now, similarly, if I give three
commas over there, my data will be
converted into billions. So this is how you can
go ahead and represent numerical data in different denominations
in Microsoft Excel. Just in case you need to
go ahead and do that. Now let us look at the
third scenario here. I have some data in
this data range. But if I look at this
data very carefully, I see some zeros here and there. So either I want to get
rid of these zeros are represent them as something
like data are not available. Then how do I go
ahead and achieve that using the
custom formatting? So as explained earlier, there are four parts
to my custom format. So what I'm gonna do
is I'm going to keep the format for positive
and negative as it is. But I will only go ahead and
change the format for my 0. And that is how I will
accomplish this task. What do I mean by that? Let's look at this
example over here. If I go ahead and select
this entire data range, right-click on it and then
click on Format Cells. When I click on custom, you will observe that the default format
is generally okay. So what I'm gonna
do is I will just give a semicolon over here. So this is the format
for my positive numbers. I will once again
type gender lawyer, then give a semicolon. So this is the format
for my negative numbers, but I don't want
to see the zeros. So what I'm going to
do is I will just go ahead and give
another semicolon. And then I will once
again type gender. Okay, so what I'm telling
Excel is keep the format for my positive and negative
numbers as it is for my zeros, don't display anything,
and then keep the format for my text as it is. And then I will click on, Okay, and you will observe that
all the zeros have vanished. But if I click on these cells, you will observe that the
values are still there. Okay, only thing
is the Antarctic visible in this
dataset over you? But let's say instead
of this blank, I want to go ahead and represent this as something like
data are not available. Then in that case,
what I will do is I will right-click on it. Then once again go
to Format Cells. And now what I'm gonna do is I'm just going to go ahead
and type general. Then once again, it
will be general. Then after that,
give a semicolon. And now what I'm
gonna do is I will type data not available. Okay? And I will give
this in WA, goods. Are you, okay? Then give a semicolon
once again. And then once again
just type gender. Okay, I'll just expand this. And you will see
wherever there was a 0, it is now being represented
as data not available. But once again, if I go ahead and click on that
particular cell, it shows the value as 0. So basically, when I go ahead and use the
custom formatting, it does not change the value
of that particular cell. It only changes the way in which the data is displayed
to the viewer. Now let's look at this scenario
that we have over you. I have the seaside data of certain team
members over here. And I want to lay down
a condition wherein in the seaside data is
more than 85 per cent, then that particular
person gets a bonus. However, if the seaside data
is less than 85 per cent, then the person is not
eligible for a bonus. So you will say,
okay, Definitely, I can go ahead and make use of the F formula and
get that output. But the same thing
can be achieved using custom formatting as well. And in this lecture we are
focusing on custom formatting, yet is a custom
formatting trick. To achieve that, What
I'm gonna do is I'm just going to go ahead
and copy this over you. I will just name this
column as bonus. Okay? And now what I'm gonna do is I'm going to select
this entire range. We'll right-click on it. Then once again, go to Format
Cells, come down to custom. Now I will lay down my
condition over here. So I will remove this gender
open square brackets. And I will say if greater
than or equal to 85, then I will close the brackets and then I
will get my condition. My condition is if
it is greater than 85, then give bonus. Okay? And then I will
give a semicolon. And basically now what
I have to tell Excel is if the condition
is not fulfilled, then what is the output? Then in that case, the
output should be no bonus. Okay? And now I will close
the double-quotes. You will observe that since
the values are texts, I have given them in
double-quotes over here. Okay, so click on, Okay. And you will observe
that wherever the seaside is more than 85, it goes ahead and declares
that there is a bonus. If I go ahead and change
any value over here, let's say I change
this 59 to 85. You will observe that the no bonus has now
changed to bonus. So I hope you enjoyed learning
these formatting tricks and you will definitely use them in your next
set of projects. I'll see you in the next video.
7. SUM Function Tips and Tricks in Microsoft Excel: Now we all know what the
function does in Excel. It basically sums up all the values that are
given in a particular range. But you may not know that there are a lot of
cool things that we can go ahead and achieved
using the sum function. And in this lecture,
we will learn some really cool
and handy tricks that we can perform
using the sum function. Now let us say I have this
data at my hand over here. And you will also that after every subset there is a
blank row where you, okay? What I want in this
particular data is wherever this
particular subset ends, I wonder sum of all
the values above it. Okay, so over here, I wonder sum of all the values from
this particular range. Over here I want the sum of all the values from this rate, and so on and so forth. So how can I go ahead
and achieve that? Now you may say that what
I can do is I can use the sum function
over here and then keep on copying the sum
function over here. But then if my data is
running in thousands of rows, then how much time
is it gonna take? It's gonna take a
**** lot of time. And yet is where a quick
shortcut can come really handy. All I need to do over here is I will select this entire
range over here. Press Control G on my keyboard. And that will open
up the go-to menu. For me. I will click on special, click on blanks, and
then click on, Okay. You will observe that
the moment I did that, all the blank cells in that entire range have
been highlighted. And now all I need to do is just press Alt and equal
to on my keyboard. And it will give me the sum
of all the values above it. Okay, cool trick, isn't it? Let's move on to our next trick. Now let us look at
a scenario where I have this dataset and in
this particular column, I have the profit
and the loss values. Now, the profit is denoted by positive numbers and the losses denoted by negative numbers. And what I'm interested
over here is to get the sum total of
all the positives, as well as the negatives
separately, okay, so I want the sum of
only the positives. And here I want the sum
of only the negatives. Now how will my sum functions
segregate this data? So here is where a small
trick can come really handy. What I'm gonna do is for my
sum function for positives, I'm gonna go ahead and
type is equal to some. I will open brackets, I will open one more brackets. So View. And I will go ahead and select this range over here, okay? And press F4. And then I will go ahead and give the
command as greater than 0. Okay? Because I wonder
sum of all the positives. So I will close the brackets. And now what I will
do is I will go ahead and hit the Asterix key to multiply this value
open brackets again. And I will multiply this with this same value all over again. Okay? And then once again, I will freeze this data by pressing F4, close this, and hit enter. Okay, but right now
I got an error. We are. And why did I get
an error over here? Because I have gone ahead
and given to RAs over here. My standard function
is now being converted into an
array function. And whenever we go ahead
and give an array function, then instead of just going
ahead and hitting Enter, we have to hit Control
Shift and Enter. And it will give us
the relevant output. Okay? And now in order to get
my negative sum over u, I will just drag this
particular formula, come down to this
particular cell over here. I will just go ahead and
change this greater than to, less than or equal to 0. And then once again, I will
hit Control Shift and Enter. And it gives me the
negative sum as well. This is how we can
go ahead and use the sum function in order to get the sum of positives as well as negative values
in Microsoft Excel. Now, many times, while creating
professional dashboards, we need to go ahead and display numerical values
within certain shapes, shaped like a rectangle, square, circle, or anything
of your choice. Now, if you observe the sum total is appearing within this
particular rectangle. And let's say if I go ahead and change any value over here, you will observe that the values changes within this particular
rectangle as well. So how do we go ahead
and get the sum of different values within
a specified shape over you. Very simple. What we can do here is we'll just go ahead and
click on Insert. Go to Shapes, and select any particular shape
of your choice. Okay? And then just draw
this particular ship. Okay? Then you can just go
ahead and format it and give it any color
of your choice. Okay, so let's keep
it black over you. And now what I want is I
want to go ahead and display the sum of all these cells within this particular
rectangular. So how can I achieve that? So what I will do
is I will select any blank cell over here. And I will say is equal to, I will open double-quotes and
I will type total sum is. Okay, and then I will go ahead and close
the double-quotes. Use the ampersand sign on my keyboard by pressing Shift and the number seven
on my keyboard. Then what I will do is I will use the sum
function over you. And I will go ahead and give
this particular Ranger. We'll close it and hit Enter. So now it gives me that the total sum is this
particular value. And now what I need to do is come to this
particular rectangle. We'll get the equal to sign and give the reference of this particular cell
over here and hit enter. Okay? The value has appeared, but we cannot see it
because the value is in black font right now. I will just go ahead and
change the font to white. And the value has appeared. Now I will just go ahead and align it in the
center with you. And with that, my value is showing up within this
particular rectangle. If I change any value over here, you will observe that
the value changes within my shape as well. And I can move this
shape anywhere I want. And many a times such shapes are used within professional
dashboards as well. So I hope you liked
this particular trick. Let's move on to our next trick. Now let us go ahead and look at the data that we have
at our handover you. And for ease of understanding, what I have done is I have
added five values as positive, 105 values as negative
100 over you. Okay? If I go ahead and
sum up these values, the sum is gonna be 0. But I want the absolute
sum of these values. And when I say absolute sum, it means that even if
the value is negative, I want it to be considered as a positive value and
give me a sum of that. So ideally, if I
go ahead and take an absolute sum of
these ten values, the answer should be 1 thousand. Okay? But if I just go ahead
and run the sum function, the value will be 0. So how do I go ahead and
get the absolute sum of all the data that I have
in my particular injury you. So what I'm gonna
do is I'm going to use the sum
function over here. And inside my sum function, I'm going to make use of
the absolute function. Okay? So what I'm gonna do is
I will open brackets. I will use the absolute
function, open brackets again. And then I will go ahead and
get this entire range yo-yo. Okay. Close the brackets. And then I will close
the brackets again. Close the bracket one
more time and hit Enter. But what happened? It gave me an error
because the sum value, by default only took the sum of the first
value in the range. However, it is no more a standard function
because we have gone ahead and given
an error eoyo. So this has become
an array function. And as explained earlier, whenever you go ahead and
give an arrow function, you will go ahead and hit
Shift Control and Enter. And that will give you the absolute sum of the
range that we selected. So this is how you
go ahead and get the absolute sum in
Microsoft Excel. Now let us look at
this particular scenario that we have over you. I have five different sheets, so and in each of this sheet, I have the sales done by my team members for
that particular month. And then finally,
I have gone ahead and created this
consolidated sheet. And on this particular sheet, I need the sum of all
the sales done by these individual team members
for the last five months. Then you would say, what
is the big deal in that? I would just go ahead and make use of the sum function and keep on giving the reference
from each and every sheet. But let's say if my sheet
was running into live maybe like 50 sheets
or a 100 sheets, then that would become
really cumbersome job. So here is a shortcut trick
that can come really handy. All I need to do over here is select this first
cell over here. And I will say some. I will open the brackets. I will go to my first
sheet over here and select the first cell over here. Because this is the
sales done by Ben Paris. And now what I'm gonna
do is I'm going to press the Shift key
on my keyboard and select the last sheet in
my entire range of years. So when I did that, you will observe that all
the sheets from Jan May 2 have been selected in one go you all I need to do over
here is now close the brackets and
hit enter, okay? And you will observe
the consolidated sum of all the five months has appeared in this
particular cell. If I just go ahead
and drag this value, you will observe that for
each and every team member, I get the sum for the
last five months. And even if there were
more than five sheets, maybe 50 or a 100 sheets, I can still go ahead and
make use of this trip. And it will give me
the exact output. So this is how you
can go ahead and use the 3D some function
in Excel to get a consolidated some from variety of worksheets in your
entire workbook.
The Guruskool
