Oct 022014
 

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.

Goalseek thumb Guide to Using MS Excel More Effectively image

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.

Scenario thumb Guide to Using MS Excel More Effectively image

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.

datatable thumb Guide to Using MS Excel More Effectively image
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

definename thumb Guide to Using MS Excel More Effectively image

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 No Designation Salary
E001 Manager 100000
E002 Associate 101000
E003 Team Lead 200000
E004 Team Lead 50000
E005 Manager 120000
E006 Manager 110000
E007 Associate 50000
E008 Associate 80000
E009 Associate 90000
E010 Associate 50000
E011 Team Lead 90000
E012 Associate 100000
E013 Manager 90000

 

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

Set condition as below

excel1 thumb Guide to Using MS Excel More Effectively image

Salary more than 100000 will be converted into red.

excel2 thumb Guide to Using MS Excel More Effectively image

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 Number Grade
2001
2002
2003
2004
2005
2006

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

Enter values as below

Settings

excel3 thumb Guide to Using MS Excel More Effectively image

Input Message

excel4 thumb Guide to Using MS Excel More Effectively image

Error Message

excel5 thumb Guide to Using MS Excel More Effectively image

Now try to enter different values in Grades and see how Excel reacts.

excel6 thumb Guide to Using MS Excel More Effectively image

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

EmpNo Ename Designation Department Gender Salary DOJ
E001 John Associate Finance Male 40000 1/1/2009
E002 Denise Manager IT Female 90000 1/2/2009
E003 Maya CEO Operations Female 200000 1/3/2009
E004 Carol Manager Finance Female 120000 1/4/2009
E005 John Associate Operations Female 50000 1/5/2009
E006 Sheela Associate Operations Female 50000 1/6/2009
E007 Rajiv Manager Operations Male 80000 1/7/2009
E008 Asha Manager Admin Male 75000 1/8/2009
E009 Kevin Sr. Associate Operations Male 60000 1/9/2009
E010 Arnold Sr. Associate Finance Male 65000 1/10/2009
E011 Kevin Associate Operations Male 40000 1/11/2009
E012 Julia Associate Operations Male 30000 1/1/2007
E013 Robert Associate Finance Male 30000 1/12/2009
E014 Ray Associate IT Male 40000 1/16/2009
E015 Shaun Associate IT Male 40000 12/10/2008
E016 John Associate Operations Male 50000 1/14/2009
E017 Karen Associate Operations Male 40000 11/10/2008
E018 Brian Manager Operations Male 90000 1/17/2009
E019 Nancy Associate Operations Male 40000 12/20/2007
E020 Debra Associate Admin Male 50000 1/2/2007
E021 Michal Associate Admin Male 40000 1/21/2009
E022 Charles Sr. Associate Finance Male 70000 1/23/2009
E023 Sandy Associate IT Male 40000 12/1/2007
E024 Doug Associate Operations Male 45000 1/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.

pivottable thumb Guide to Using MS Excel More Effectively image

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
Name Gender
Maya Female
John Male
Dan Female
Nancy Female
Dale Male
  • Now select data and click copy.
  • Open a word document and click paste special.
  • Select paste Link and Microsoft Office Excel Worksheet Object. ole thumb Guide to Using MS Excel More Effectively image
  • 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.

 October 2, 2014  , , ,  Add comments

Leave a Reply