 |
| Microsoft Excel Tricks and Functions |
Essential Microsoft Excel Tips
1). Vlookup
2). Filters
3). Sum
4). Sumif
5). Count
6). CountA
7). Average
8). PivotTable
9). Dashboard in Excel
10). Data Validation
11). Picture Lookup
12). Hlookup
13). Data transfer from excel to word
14). Data transfer from notepad to excel
Basic Functions
are those functions that we use easily for basic work in excel like the sum
and average, etc. These are just examples. It does not mean that only these are
the basic functions, There are as many as functions considered basic like
vlookup, hlookup and many more.
As we have
seen other few names as well above in the list to learn essential Microsoft excel
tips, there are also a few functions we saw as advanced level excel too like
creating a dashboard in excel, picture lookup, etc.
Now here will
discuss the basic and advanced level functions however once we get to know about
the features of basic and advanced then also we can call ourselves the master
in excel.
If you have to work across
different sheets & workbooks then the vlookup will help you to combine and summarise the data but when you want
to use this formula or function then make sure you must have at least one column that appeared an identically in both places.
For example, you have a list of names
of some people next to their email addresses in one sheet and a list of those
same people’s email addresses next to their school names in another however you
need the names, email addresses with company names to appear in one place.
If you have to work across
different sheets & workbooks then the vlookup will help you to combine and summarise the data but when you want
to use this formula or function then make sure you must have at least one column that appeared an identically in both places.
For example, you have a list of names
of some people next to their email addresses in one sheet and a list of those
same people’s email addresses next to their school names in another however you
need the names, email addresses with company names to appear in one place.
The formula will be = Vlookup (lookup value, table array, column
number,(range lookup)).
The screenshot is attached here for a better explained below.
 |
| Microsoft Excel tricks and functions |
Filters
 |
| Microsoft Excel tricks and functions |
Basically
what we are discussing here is that if we learn basic and advanced-level functions
in excel then, of course, we can even learn new tricks in excel.
Now
we will add a few functions here in the excel tricks with examples. First of
all let s discuss the most important feature of excel, called MACRO.
Question:-
What is macro in excel?
Answer:-
When we have a task to repeat any data work that we need to make as repeatedly
then we can simply record a macro for it. Macro saves time and we can run
it as much as time. That is called macro.
Question:-
How to record a macro in the excel sheet?
Answer:- Let's suppose we have to create data in the
excel sheet where we need to perform repetitive work means there is work that
we do that is coming under repetition. Now here we will record a macro so
that repetition will be stopped and it will create the data itself easily. Let
c how it works.
Now
we are recording a macro so we need to open our excel sheet and if it is
Microsoft Office 2010 then look at the last button on the top called VIEW and
click on it. Once we click on it then we have many options but we need to focus on the last options as says macro. Check the screenshot below.
 |
| Recording a macro |
As
per this image check in the last that we have 3 options altogether then
we have to see all of them and us going to click the first one from the bottom.
1).
View Macro
2).
Record Macro
3). Use relative references
Now
let us get the details of View Macro
first.
1). View Macro – If we
already recorded a macro in the excel sheet then we click this VIEW MACRO to run or view the macro in
the spreadsheet.
It
also has many features when we click it.
Like As:-
1)Run
– This option runs the macro.
2)Edit-
It gives a chance again to edit the macro coding.
3)create
– Here we create the macro.
4)Delete-
We can delete the macro in the excel sheet by clicking it.
5)Options
– Here we get many other functions that we can check here in the screenshot
below.
2). Record Macro – Here we record a
macro and also we have the option to create a shortcut for running it as if like
[CTRL+SHIFT+()]. Remember when we
create a shortcut then we also press a shift key if we do so then we will not
be confused about the shortcut key to run the macro.
Even
if we forget the shortcut key then we can simply run it by clicking the first option that we have in the top VIEWMACRO, Here we get a button as RUN to run the macro.
For better understanding let us watch the video here for recording a macro.
