Microsoft excel is one the basic and most important tool in today's Corporate world.

This article is dedicated to all the new joinees, the young talent who decide to start their careers early.Hope this gives you an edge, to the other wise unknown challenges.

Note: If you know at-least the below, you will make it through.

A Refresher

When you open an excel sheet, this is the first thing you will see. The blue part is the Title Bar, as the name suggest, this is where the name of the workbook will appear. The grey part is the Menu bar, This is just like the restaurant menu that tells you everything that is available in the restaurant. Similarly,Menu bar tells you or takes you to all the functions avilable in Excel.

Toolbar Link, is the most important accoding to me, as it gives you access to most of the items, that you would use for formatting in excel. And everything is pretty easy, if you surf through them, you can learn a lot with trial and error. All you really have to do is, tick in front of the name, and the toolbar will appear in excel for your use. Most commonly used and in my opinion the once, that should always be marked are the standard toolbar, the formatting tool bar and the web toolbar.

Small functions that can make a huge difference.

1) Sorting: Path on the menu bar for Sort is : DATA - SORT.

• What does Sorting do?

Say you have a huge data base with a lot of categories and the data is not arranged in order or any sequence.

Eg: Name      Class       Roll Number     Division      Date of joining      Address      Telephone Number        Teacher          Rank          Marks      

And what you want to do is, get the data class wise or Rank wise. Do you remember your ascending/descending lessons from class IV. Yes, this is the same thing. Sorting will put your data in ascending or descending order as per your requirement.

How to use Sort?

Step 1 You need to select the data you want to sort, The first row has to be the titles like I have shown above.

Step 2 Select the entire data with a click on the first cell and a continues drag till the data ends. (P.S:- for tips on easy dragging and selection please read my next article)

Step 3 When you open the sort from the above path. (DATA- SORT) you will get the below dialog box.

 All you need to make sure is to select the title as well where all the categories are written and then mark Header row on the dialogue box . If you mark no header row, the titles will not appear in the drop down like above, It will say column a, column b . referring to the excel default names of the column. Select your reference for sorting and click on how you want it to sort ascending/ descending and click on Okay. Its done.

2) Filtering: Path is : DATA - FILTER - AUTO FILTER ( For a simple filter/ there is also Advance filter, but it is not as commanly used as Auto Filter)

What does Filter do.

Filter is again very useful when it comes to an extensive database, and when you are looking for specific information.

Say a database with the details of all the employees in the company.

Date of Joining    Name    Last Name      Experience      Address     Team      Manager       Grade  

And you are looking for people who joined on, (E.G) 20th April.

How to use- You should select the entier title row and click on DATA - Filter - Auto Filter

Once you click on the same, you will see a drop down created against each category and if you filter the data from the date category, it will give you the information you need. 

3) Conditional Formatting: Path: FORMAT - CONDITIONAL FORMATTING.

This a useful tool, when it comes to highlighting specific information to make is visible. What it means is, say you have a presentation, where you want everyone to see the names of the student, who have failed in the class. Best way is to highlight the name with a Colour.

What does CONDITIONAL FORMATING do:

It allows you to apply a condition on the data and colour the data that meets the condition.eg:- A teacher asked all the students of below 5 years of age to make a separate line. here;

Condition = Students < 5 years of age.

Formatting = A separate Line.

How to use:- First select the row, column, area or cell where you want the condition to be applied. Go to format - Conditional Formatting.

All you need to do is select the condition by surfing through the drop downs. 

Then go to format and select the font size, colour, and formatting you want for the condition.

Click okay it works like magic  

4) V Look Up:- Path - INSERT - FUNCTION .

This is a very helpful and a little tricky at the same time. But if you know VLOOKUP, most of your problems at office will be solved.

What does it do:- 

 Say you have two files, with 100 names each, However not in the same order or format. One file has just the name of the employees and does not have the employee ID, but other details that you need. And the second file has the name and employee ID but all the other information is missing. What you can do is find each employee manually on the first data base and write the employee ID over there, or sort the data base and copy paste and then change the format again. Or you can do a magic trick which is VLOOKUP. What VLOOKUP will do is, it will match the common information, which in our case is the employee name and paste the information you need that is the employee id.

How does it work:- Take a worksheet with the first data on the SHEET 1 and call the data with employee id and name as reference data. Make sure to paste the information you need, which is the employee id after the common data which is the name .

So, you will have to arrange the colums like this

Data1: Name      Place      Date of joining     Date of promotion      Date of Transfer       Bank Account etc.

You will need to create a empty column after the Name which is our common data.

Data 2: Name     Employee ID.

Place the cursor on a empty cell next to name of one of the employee in Data 1 and click on INSERT - Function and type VLOOKUP in the empty box and click okay. This is what you will get after clicking okay.

 Now is the difficult part, if you follow my advice without understanding the logic it will work. If you try to understand the logic before using it, it will not work.

click on the radio button with, first option lookup value, and any written next to it . And then select the entire row of employee names, as that is the common information available.

Then click on the radio that has, number written next to it, and go to the Data 2 which has the NAME and EMPLOYEE ID written in it. Select all the thousand entries. As what VLOOKUP will do is search for employee names in this Data 2 and paste the employee numbers back from here to the Data 1 empty cell for you.

Then in the third column which the option of col_index_num type "2", in the next one range look up type "0"

LOGIC of the 4 entries:-

1^st entry:  Asking for the place where you want VLOOKUP to paste the result

2_^nd entry: Asking you for the reference data, where you want VLOOKUP to match and search the reference data, and find the common information

3^rd entry: Here we are putting a condition or telling VLOOKUP, that when you find the common information( employee name) you need to paste the information in the second column. back on SHEET 1, and in the cell where I had started the function VLOOKUP.

4^th entry: In this column we are telling VLOOKUP , if you don't find a common entry please enter 0.

Hope the above helps you.

• Written by preeti vaswani.
• In category Careers.
• 15 years ago.

Hot Hits: 2498

Like it on Facebook, Tweet it or share this article on other bookmarking websites.