Skip to main content

Guide to Using MS Excel More Effectively

Microsoft Excel is one of the most widely used spreadsheet tool. But most of the time, we use only basic functionality of Excel. This guide taps into functionalities offered by MS Excel.

List of Formulas/Functions in Excel

Here is a quick reference of important formulas in MS Excel. Functions are pre-defined formulas in Excel and can be interchangeably used with term Formula. The list has been prepared using Excel 2007. But most of the formulas should be applicable to other versions as well.
  • Lower/Upper/Proper – You can use these formulas to convert text to your desired case (lower case, UPPER CASE, Proper Case).
  • Left/Right/Mid – Use these functions to extract data from a cell or text.
  • Concatenate – Concatenate multiple cells or strings into one.
  • Trim – Leaves only one space between words.
  • Len – Display number of characters in text string.
  • Find – Returns starting position of text string in cell or another text string.
  • Sum – Adds all the numbers in a range of numbers of cells.
  • Sumif – Adds all the numbers based on given criteria.
  • Average – Returns average of range of cells.
  • Min/Max – Returns minimum/maximum value from given range.
  • Round – Rounds a number or cell to given number of decimals
  • Count – Count number of cells with numbers
  • CountA – Counts number of cells that are not empty
  • CountIf – Counts number of cell in a range based on given criteria
  • If – Returns a true value and false value for a given condition
  • And – Returns true value if all the conditions are true and false if any one condition is false
  • Or – Returns true value if any of the condition is true and false if all conditions are false
  • PMT – Calculate monthly payment for a loan or mortgage.
  • Days360 – Returns number of days between two dates.
  • Now – Returns current date and time
  • VLookup – Vertical Look Up returns value from the specified column while looking value in left most column of a table. Table must be in ascending order for the function to work.
  • HLookup – Horizontal Look Up returns value from the specified row while looking value in the top row of a table. Table must be in ascending order.

Referencing different Worksheets in Excel

If you want to use values from one worksheet to another, you would need to use a reference to the worksheet.

Add Worksheet name and ” !” Mark in front of referenced cell to accomplish worksheet referencing. For Example, Formula =SUM(A1,Sheet1!A1) will add cell A1 from current Worksheet and cell A1 from Sheet1.

Using What If Analysis in Excel

What If Analysis function Goal Seek

When you know the result but are not sure of the input that would bring the desired result, Goal Seek function can help. Goal Seek is one of the” What If” analysis functions in Excel.

For example, if you know that you want to have revenue of 120,000 USD and you are selling 1000 products. You can use Goal seek function to find what should be the price of the product. The cell that contains result (120000 in our example) must contain a formula.

What If Analysis Function Scenarios

You can use Scenarios to see what would happen to your bottom line figure if you change certain values.

For example, you may want to know what would happen if you change per unit price of your product. How would it impact revenue and profit?

You can enter a cell that you would be changing (example, unit price) or range of cell (example C4:C7) to create different scenario. Go to What If Analysis in Data tab and select Scenario to perform the function.

What If Analysis Function Data Table

Data Table is a What If Analysis Function to find out result by altering an input cell in Excel.

For example, you may want to see impact on your revenue or profit by altering per unit price of your product.

Go to What If Analysis in Data tab and select Data Table. Enter Row Input cell (if you are filling data horizontally) or Column Input Cell (If you are filing data vertically) or both if you are filling data vertically as well as horizontally.

Relative, Absolute and Mixed References in Excel

There are different ways a cell can be referenced or called in Excel.

Relative referencing is the most common type of referencing a cell in which cell is called by row and column level (example A1 means column A and Row 1). When a formula with relative referencing is copied, reference of cell will have relative change. For example, if cell C1 has formula =SUM(A1,B1) and the formula is copied to Cell C2, it will be changed to =SUM(A2,B2).

Absolute Referencing will have absolute reference to the cell. If you place $ sign in front of cell reference, cell reference will not be changed during copy. For example, if cell C1 has formula =SUM($A$1,$B$1) and the formula is copied to Cell C2, it will remain =SUM($A$1,$B$1).

Mixed referencing is the mix of relative and absolute referencing. Example is =SUM(A$1,B$1) or =SUM($A1,$B1) or any other combination of relative and absolute referencing.

Using Name Range in Excel

Name range in Excel is a way to define a range to be used in Formula.

For example you may be using a formula =Sum(C1:C10). You can replace range C1:C10 with a name (example, EmployeeSalary).

Select cell range, Go to Formula -> Define Name and provide a name and enter

To use it in Formula, Go to Name Manager and select the name.

Conditional Formatting in Excel 2007

Conditional Formatting allows changing appearance (different color, font, format etc) of cell based on certain condition. For example you can show employees with Salary Higher than 100000 USD in Red. This makes easy and visually appealing to read and interpret data.

Try this example below to better understand the concept. Enter Data below in Excel

Emp NoDesignationSalary
E001Manager100000
E002Associate101000
E003Team Lead200000
E004Team Lead50000
E005Manager120000
E006Manager110000
E007Associate50000
E008Associate80000
E009Associate90000
E010Associate50000
E011Team Lead90000
E012Associate100000
E013Manager90000

Now select Salary column, Go to Home -> Conditional Formatting -> Colour Scales -> More Rules -> Format Only Cell that contain

Set condition as below


Salary more than 100000 will be converted into red.


The concept can be applied more complex scenarios.

Data Validation in Excel


When you need to verify data that can go in a cell, Data validation is the function in Excel to use.

For example, to enter grades for students you may want to enter only A, B, C, D, and F. To see how to accomplish this in excel, enter data below

Student NumberGrade
2001
2002
2003
2004
2005
2006

Now select Grade Column, and Go to Data -> Data Validation

You have different options while setting validation
  • Restrict only whole number (Such as for Student role number).
  • Provide a list (List of grades).
  • Restrict number of character to enter (For example SSN will always be 9 digit).
  • Or you can provide a custom formula.
You also have the option of setting hard error, soft warning or just information.

Pivot table in Excel

Pivot table is great tool in Excel to create reports and analyze data. Go thru the simple exercise below to understand the concept

Open Excel and enter data below

EmpNoEnameDesignationDepartmentGenderSalaryDOJ
E001JohnAssociateFinanceMale400001/1/2009
E002DeniseManagerITFemale900001/2/2009
E003MayaCEOOperationsFemale2000001/3/2009
E004CarolManagerFinanceFemale1200001/4/2009
E005JohnAssociateOperationsFemale500001/5/2009
E006SheelaAssociateOperationsFemale500001/6/2009
E007RajivManagerOperationsMale800001/7/2009
E008AshaManagerAdminMale750001/8/2009
E009KevinSr. AssociateOperationsMale600001/9/2009
E010ArnoldSr. AssociateFinanceMale650001/10/2009
E011KevinAssociateOperationsMale400001/11/2009
E012JuliaAssociateOperationsMale300001/1/2007
E013RobertAssociateFinanceMale300001/12/2009
E014RayAssociateITMale400001/16/2009
E015ShaunAssociateITMale4000012/10/2008
E016JohnAssociateOperationsMale500001/14/2009
E017KarenAssociateOperationsMale4000011/10/2008
E018BrianManagerOperationsMale900001/17/2009
E019NancyAssociateOperationsMale4000012/20/2007
E020DebraAssociateAdminMale500001/2/2007
E021MichalAssociateAdminMale400001/21/2009
E022CharlesSr. AssociateFinanceMale700001/23/2009
E023SandyAssociateITMale4000012/1/2007
E024DougAssociateOperationsMale450001/24/2009

Now select data and Go to Insert –> Pivot table. Click OK.

Pivot table has been created.  Now drop fields in the right frame to Report filter, Colum levels, Row levels and Sum of values windows to create the report as desired. Then filter various values and see how report appears.


For example, when you filter on Designation Manger, reports shows you how managers in different departments are paid.

Using Object Linking and Embedding (OLE)

OLE is technology developed by Microsoft to make different programs interact with each other to make each program more useful and visually appealing. OLE object can be embedded into different application; they however retain original format and link to originating program.

For example, an image can be embedded or linked into text document. Something a text editor could not do earlier.

Embedding vs Linking

When you embed the object, any changes in the original object are not reflected in the embedded object. When you link a object, changes in the original object are reflected in the linked object.

Understanding OlE by embedding Excel data into Word

Object Linking and Embedding is Microsoft framework for allowing objects from one program to be available in other programs.

Follow this simple exercise to understand the concept better.
  • Open Excel and enter data below
NameGender
MayaFemale
JohnMale
DanFemale
NancyFemale
DaleMale
  • Now select data and click copy.
  • Open a word document and click paste special.
  • Select paste Link and Microsoft Office Excel Worksheet Object.
  • You will realize all the data along with excel formatting has been copied.
  • Any data you change in original excel will be available in word. This is called Linking.
  • If you had selected simply Paste in paste special option, any changes in excel will not be reflected in Word. This is called Embedding.

Extracting Data from Internet in Excel

Excel comes with the option of querying web to export table data from internet. This is a very good option if you rely on constantly updated data such as exchange rates etc.

Go To Data tab -> From Web. Enter the url of the webpage where table is stored and follow directions. You will have data in your excel sheet.

Note: The Examples are written using Excel 2007, but they work in Excel 2010 and Excel 2013 as well.

Comments

Popular posts from this blog

26 Proven Work at Home Business Ideas You Can Run Part Time

If you are looking for work at home business ideas, then this list can help you. All of these can be done as part-time business, so you don’t have to leave your job until you are confident of making full time income from your Work at home business.

How to Find Niche Website Ideas for Affiliate Blogs

Choosing right niche website ideas is the most critical decision. The topic of your affiliate blog will have huge impact on the outcome of your efforts over several years.

21 Passive Income Ideas to Achieve Financial Independence

Passive income is the key to achieving financial independence.  Earning Passive income requires you to create some form of asset that can generate income. You might still need some effort to maintain the asset, but it would be significantly less than effort required to build the asset. If you are looking for ideas to generate passive income, than below list of options can help you generate it.