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.
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
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
Now select Grade Column, and Go to Data -> Data Validation
Enter values as below
Now try to enter different values in Grades and see how Excel reacts.
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
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
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.
- 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.