Transcripts
1. Intro video learn excel for office work: Hello friends. My name is Sanjay. I have been teaching
Excel for office in my local city
Institute since 2013. I'm training students
to work in big firms. So I thought of starting this class exclusively
on Skillshare and share some Excel skills I
have with you. In this class. I will cover the most important
and most used functions and formulas of Excel that are widely used in offices work and will make you grow in
using Excel very quickly. After completing this class, you will be in a
position to take on any interview or impress
your boss in office, fit your expertise and
proficiency working in Excel. This class, we'll keep you updated with new
videos and lectures. I will create videos,
lectures every week. You will keep learning new tips and functions
and formulas. Even I will create videos
on your questions that you may have or might be facing
while working in Excel. I tried my level best
to make this class as easy and it's small
inland as I could. Also try to use very
simple live example for showing solutions
of big questions. The end of the class, there is a project that average
students should attempt. I have provided an Excel
sheet with some data. You have to solve the
questions that are given in the project
section of this class. You will, this will help you a certain how much you have
learned in this class. In the end, I just want to say, don't hesitate to ask questions. I will reply very
quickly and even can create videos showing you the
solution of your questions. Thanks for watching
this intro video. See you in the main
class. Thank you.
2. My Opinion and Insight on Using Excel for Data entry works: Before we actually get
started with the class, I want to put my opinion why
it is good for you to learn, Excel and what you shall get. We all know that time investment is required for
learning anything. So is the case with the Excel. But Excel is really no too much time-consuming
and complicated. You can easily pick
up the essentials of using Excel in office
work within few hours. Not days, not weak. In few hours. You can be ready for productive work if fuel
or concentration is good. And yes, practice
makes them perfect. This implies with Excel,
more you practice, more will be the desert and
you will be more efficient. So what are the most dog about the benefits
of using Excel? Now, very first
benefit of learning Excel is that it is amongst the most asked me an essential
skill for many jobs. Think of any
department of field. You will see there
are many people in the office involved and
working with Excel. E.g. financial institutions
or content offers schools, data entry, surveys, company, banks, as well as hospitals. These are the obvious fields
where Excel is compulsory. But apart from this, if you widen your imagination, you will find that almost every officer might have some weren't doing
work in Excel. Therefore, if you are interested
in getting a good job, Excel would be the
first requirement. Some companies you
can donate to have very high level of
expertise in excel. While on the other hand, some menu just basic. But Excel is
required everywhere. So the sooner you get
started with learning Excel, the quicker you will get the
job or what you dream of. Seconds. Second, XL make you
smarter, faster, and more efficient while
dealing with data big or small. My insight and tip for Excel. I think the more
you work on excel, more you will learn
and know the software. Once you have enough grip, you will find yourself
using shortcuts and your work will be more fast. I know many people
who work on Excel, but they hardly use
mount while working. Why? Because they find
it easier to use shortcut instead of using
mouse, which take time. Starting Excel may seem
quite intimidating as it has almost to learn
and it is very wide. You can easily make you think that it's
very hard to learn. But the reality is
totally different. It's pretty straightforward. You really don't have to be a technical person to
learn, Excel. For many. It may seem like a dull
or boring program, but once you start learning, you will see how
interesting it would when it completed the
work of hours in minutes. And automate so many
things that you might have never imagined. Set your mind. Because you will be learning Excel and don't leave
the class in between. As I will keep adding more
tips in the class regularly.
3. Text is in the Cell But not showing up. OR How to Remove Line and space from Starting of a cell: First of all, I'd like to
thank you for enrolling in the main class and friends, I will try my best to make
the videos and lectures of this class as simple
and easy to understand. So let us start without
wasting too much time here. Let me make it correct. Okay, first very,
very first video. We're going to
learn how to remove extra lines and extra space
from the starting of a cell. Sometime you copy some
data from that page or any other external source and paste it in the egg cell. And you see that data is
not there in the cell, but you know, you just paste it. So this kind of problem can happen if you copy data
from other sources. Let me show you
with an example so that you can
understand your liver. I mean, I need your
attention on the column j. Here you can see
there are some cells. In fact to all the cells
except just to our blank. But actually if I
select the cells, I can see the counts are there. That means there is
definitely some data here. And just if I drag the
formula bar downwards, I can see there is some data. So this data have some extra space and
also have line still. Okay, extra lines I'll still. So how can we get
rid of this issue? So let me show you an example, a formula that you can
use to first remove the extra space from the beginning so that you can see the text
from the beginning. Then I will show you the
easy way of removing the extra lines
that are added with each cell before the
text of that set. So we use trim I M to get rid of the extra
space before the text. And now we can see
the data is there. From the beginning. We just took copy the same formula
to all the cells. We want control C to copy
and paste Special Use best, especially to paste the data
in the column where we want. Okay, now we can remove
this formula that we apply. And now it's okay. But it's still, if I
brag, I can see that. Yeah, I get rid of
the space extensive, but it's ten lines are there. So I need to get
rid of this line. You can see there's a line. Then we have our data. So how to get rid of
this line as well? So there's a shortcut
key here for the XL uses Go Control F to open
the Find and Replace window. Go to replace section
here, and press Control. And J, you won't see
anything but the formula is already applied and replace. Okay. Replace all. You see. There were 28, 29,829 cells having that
extra line in each cell. Now, you can see the extra line has been removed and it
is from the beginning of set is text is now
starting from the beginning. So this way you can get
rid of extra lines as well as extra space using
these two simple tricks. And believe me, these kind
of problems are usually occurs when you work
a lot with Excel. And your work involves some copying and pasting
data from external sources. This idea and this trick and this formula will help you
to solve your problem. So friends see you in
the next video. Bye-bye.
4. Avoid trailing zeros Why Typing Big numbers: Hello friends today in this
video and move on to discuss a very important
function of the actual, which is quite important
if you work in an office. So let me show you what I mean. When you type big numbers. If the visit of these numbers
is more than two pin, then what will happen? Excel will
automatically start to converting the after digit, digit after 15 into zeros. So how to avoid this? Sometimes we may want to have numbers more than having
more than 15 digits. Two days of this, if you are dealing just
with the single cell, what you can do before
typing you just to add a quotation mark here. And then now you can add
as many numbers you want. But it will be like text number, because if you use
it as a number, it will going to be converted into zeros after 15 disease. Another thing that
you can do is you can select the whole column and right-click and select Format
Cells and then go to tax. And now press Okay. Now, now this, if you type numbers having
visit more than 15, they will not
convert into zeros. So these are the
two work around off with a problem that you might face while dealing with the
number 715 digital mole. So I hope you liked this idea and take,
Thanks for watching.
5. God of all shortcuts - Easily Know the Shortcut of Anything you want: Hello friends. This video of this class is very,
very important. As you know, that I'm going to show you the God
of all shortcuts. As you know that almost
every application that you use on your PC has
some shortcuts. So as is the case with to excel. And do you know that the
egg cell has a shortcut for almost everything
you do in Excel, right? But, you know, there
are hundreds of shortcuts and remembering
them all is quite difficult. But if you could, then you are really genius. When you use shortcuts,
you'll look professional. Not only your computation
boosts, confidence, boost, but also the work you do. The way you do your work, become very professional
and efficiently. You can work in Excel. But yes, again, it is quite difficult to remember
all the shortcuts. You can remember
like 2030 shortcuts. But all shortcuts remembering all sorts what is
quite difficult. But today in this video, I'm going to show you the
God of all shortcuts. Yeah. You just need to go on your keyboard
and press Alt for 2 s. And you will see some
alphabets start appearing on the excel sheet that we just bends those alphabets like
I'm pressing edge here. And boom, I'm in Home tab. And in the Home tab, again, there are
so many alphabets. These are the shortcuts. You just start
pressing the shortcut like I want to go to
format table as I can, just need to press T here
and I'm right here, right? So skip and you are
back to the shortcuts. And now you get like
if you want to go and see and underline
or bold something, you can press 123 like that. So this way you can see all the shortcuts
that Excel have. Another way. Another way which I
personally use is I know these are Home Insert and I can remember
these alphabets. Okay, Another way of knowing what the shortcut would be
for a particular command, like if I go here in the copy, I can see the alphabet
is underline. You can see this underlined
and this P is also underline. That means it is a shortcut of coffee or copyist
picture, right? Similarly, you can
go to underline, you can underline and
development underline using the shortcuts like this is underlying and
it is a shortcut. So how to use this, okay? How to use these shortcuts
is very important. You can do. Like I want to go to Control, Find and Replace,
and I want to go to Replace tab here, right? So what I will do
use you can see the small and the line
in the alphabet. So I will press
Alt and be, okay. While keeping the press, I can press either. I do find all, find F to find next. Replace all. I will press a while
giving the Alt key or our press R to replace a
particular texts in the cell. If I want to move to
the option of these, I can press T while
pressing the Alt key. I think. I see all options here. Again, there are
so many shortcuts while keeping your Alt key you can go to within pressing Alt. So you just need to type edge, I think. Yeah, I'm here. I can go search here. So this way you can easily see there is a shortcut
for everything and you can easily know what is the
character that you need to. So you see that you can
get the idea or no, what could be the shortcut for something that
you want to use? When you start using
these shortcuts. These shortcuts. You can keep these shortcuts, remember for a long time. And once you start using them, it becomes your habit
of using shortcuts and your work in office
will gradually improve. So I hope you liked this idea
and thanks for watching.
6. How to Insert a New Line in a Cell: Hello. Do you know how to
insert a new line in the sand? And it can be useful
in entering address in Excel and in paragraph of text snus and unwrapping
column headers. And in this video you will
assert go to enter line break, or you can call it, insert a new line in
a particular cell. So let's get started. So I'm in my working
sit here and suppose, yeah, I need to
enter a new line. So how will it can be done? So k, So you've got to just keep, press and hold and press Enter. And it's a new line,
you can enter. New text. Yeah. Okay. This can be helpful if you want to have a digraph
for text in a cell. Or even if you want to
have a column name. And is useful in wrapping, okay? So this is how you can
enter a line break or displayed text into
multiple lines in a single cell. Thank you.
7. How to Select Blank Cell and Fill each of them with some Text at once: Okay, friends play. In this lecture of this class, I'm going to show you
how you can select all the blank cells and fill them with some text all at once. As you can see in the seat, I have many cells that are already blank and don't
have any data, right? And I want to select all the blank cells
and fill some data. You may already know the process of selecting all the blank cell. But do you know what do
you really know that you can fill those blank cells simultaneously with
just one click. Let me show you first
of all how to select all the blank cells off the range or the holes you do can use this formula with both. If you want to select
the blank cell only from the particular range, you just select the range. I selected those column L and
press F5 on your keyboard. And goal is special. Here. Select blanks. Right? Now, it will automatically selected the blank
cells only, right? So you can see this is a
blank and blank cells here. And do all other blank cell
that you're seeing in this, I'll come to that point
why this command couldn't, couldn't just select these
blanks. They look blank. I'll show you why. So
now we want to enter some text simultaneously in all a blank cell that
we have selected. What you can do, you
just do-nothing. We just use F5 command, select the spatial
and press blanks. And then okay, you
selected the blank cells. Now without touching anything, you just start typing
the text you want to enter in these selected
blank cells, okay? So I want to die. And right, and after typing, just press Control
and press Enter. And now all the blank
cells that were selected now have this text
and a in each of them. As you can see I'm scrolling. You can see them. Okay? So this way you
can enter the text selected blank cells
simultaneously by just pressing two buttons. That's keep pressing the
Control and press the Enter. Right? Now, I want to talk
about these blank cells. They look blank, but they
are actually not blank. If I just select these three, I can see there are
three counts here. What the content here? We have to see. We can stress this formula bar
and the data is not there. But it is. If we press F2, we could see that it is jumping. One is space, that
means there is space inside these cells. Okay? So it is simple to get rid of this page just to
select the space. Like I did. Control C, Control F, and go to Place and
press replace all. Okay. Wow, there were 57,000
basis of this whole sheet. Now these are blank. And now we can easily go and press F5 liked
cells and blacks known. It will select these things, these cells as well. I hope you liked this class and let me know if you
have any questions. Thanks for watching and
see you in the next class, which is going to be
very, very important.
8. Other Great usages of Auto correct feature of Excel: Okay, friends. Most of us already know about autocrine,
but in this video, I'm going to go a step
further and we'll show you some other
very useful thing. Other useful. Okay, friends, most of
us already very much familiar with autocorrect
feature of Excel. And today in this video, I'm going to show you
some other way of using Autopilot to
make our work faster. So let's get started. First of all, we need to go
to File select option here. And then proofing and click this autocorrect option to being the auto correct
function window here. Okay, So they're already, there are only so many things
added to this option here. If you want, you can
add it or add your own. But you can use this auto correct to automatically
apply a formula as well. So suppose if I want, I have to quickly add time
and date in my cells. I used to frequently add
these two things in Excel. So instead of using an typing or copying or pasting the
formula again and again, what I can do, I can specify a special character
here, like DD. Okay? And I can use the formula today. All right, like this. And
let's see how it behaves. So I'm here and I
just type predict. Well, it gives me the
formula and the date here. So this is one way that you can use autocorrect feature to apply a formula and assign a special
character of your choice. Whenever you type
those two characters, it will automatically
enter the date. But there is a catch-up. Once if you just
need to type dd, it will automatically
convert the date. But don't worry, if you
are typing like this, won't convert to date
because it has TB button, not only to you, but if you type B or B or any special character do
assign to something than it has to be separate and only those characters on it to make things happen
in the order of act. So that you can you can do so many things
with auto, correct? You can assign anything with anything too
long text or you can use to assign the long sentence that you usually type or have to enter in
the Excel sheet. I'm just typing that loans
and dance and assign some two or three number or
character or anything there. And it will work for you. So autocorrect feature is very, very important that you
can use here in Excel. Thanks for watching and see
you in the next lecture. Hope you liking the class. If you do, please don't
forget to leave a feedback. And the feedback section.
9. Select Non Adjacent Cell ★: This video is very small
inland, but very important. If you have to select known adjacent rows
or cells of a XL, see it, then you
get all of this. I know you already, almost all of you already
know how to do that. You just simply go and keep, press and hold control and select the cell that you
want to copy or select. But I'm going to show you
another way of doing this. So what you can do, you can simply select the
cell that you want to select, like this cell, I want
to select this term. I know press Save and
F on your keyboard. Now, you don't have
to keep pressing Control D. You can just go and select the cells
that you want. Like this. I'm not pressing
and holding Control D, right? Select the cell that you want, press Shift and eight. And then just start selecting the cells that
you want to copy or sacked. This is where you can, this is the way you can select node adjacent cells using
a different method. Thank you. I hope
you like the class. Thank you.