Level: Beginner
This is in continuation to the first introductory post on spreadsheets; if you have missed it, read it here.The whole intention of writing the Excel tutorial is to enable the use of its functions to manipulate/ massage the data. So skipping all the steps on operating, formatting, templates, styles etc, we leap forward into the use of formulas and functions in Excel.
What is the difference between an Excel formula and function?
A formula can be defined in a layman terms, as a statement that can contain values or references to cells that is written to perform a calculation and return a result/ value. A formula must always start with an equal to sign (=). For example, a formula to add some numbers would be like "=5+2+3"
Excel functions on the other hand, can be are defined as the set of formulas that has the objective to do a higher and predefined calculations in a shortest possible way. For example, in the same above example of addition, a SUM function can be used by typing "=SUM(" and select the cells areas using mouse as below "=SUM(C4:E4)"
In the above example, you will definitely understand the use when you are handling data in large volumes.
Now lets get into something that we will be using day-in and day-out : operators.
Chapter 1: Operators in Excel
Please remember that Excel is tool, to help us in data analysis and hence all the Mathematics you have been learning in school days will be applicable here.Some of the commonly used operators are given below.
| Operator | Name |
| + | Addition |
| – | Subtraction |
| * | Multiplication |
| / | Division |
| ^ | Exponentiation |
| & | Concatenation |
| = | Logical comparison (equal to) |
| > | Logical comparison (greater than) |
| < | Logical comparison (less than) |
| >= | Logical comparison (greater than or equal to) |
| <= | Logical comparison (less than or equal to) |
| <> | Logical comparison (not equal to) |
And like there's precedence of operators (remember BODMAS), similar follows for operators in Excel.
| Operator | Name | Precedence |
| ^ | Exponentiation | 1 |
| * | Multiplication | 2 |
| / | Division | 2 |
| + | Addition | 3 |
| – | Subtraction | 3 |
| & | Concatenation | 4 |
| = | Equal to | 5 |
| < | Less than | 5 |
| > | Greater than | 5 |
However please note that brackets or parenthesis is used in Excel for data control. For example if we put
=(A1-B3)*C4 , the operation within brackets will precede over the multiplication. To know more about a function in Excel, you can go to Formulas tab and have a view of all the formulas available in Excel.
Using mouse pointers/ movements to enter formulas
Excel provides an alternative method of entering formulas that is generally easier, faster, and less error-prone. Though it still involves some manual typing. For example, to enter the formula =C1+B2 into cell D3, follow these steps:
1. Move / point the cell pointer to cell D3.
2. Type an equal sign (=)
3. You can either use the directional arrows to the mentioned cell locations or simple click on the cell locations C1.
4. Type the operator plus sign (+).
5. Use the directional arrows again to select B2 or use mouse to click the cell.
6. Press Enter to end the formula.
Tip:
1) Using the Formula tab to calculate
Instead of manually typing, you can use the Formula tab and bring up the dialog box, to operate.
2) Using cell reference
You can use cell reference, not only referring to the current sheet, but another sheet or another worksheet.
e.g =D1*Sheet2!D1 would mean multiplying D1 cell of the current sheet with the D1 cell of sheet 2
3) Correcting formula in cell
At times, when you feel you have inputted the formula incorrectly, you can directly point to the cell where the formula is and press F2 to view the formula and edit it.
4) Copying a formula
If you wish to use the same formula for a list of rows, all you need to do is click on the right lower corner of the cell where the formula is and drag it down till where you wish it to be copied.
5) Copy and paste options
When you copy a cell data, you have multiple options while pasting it. To check, copy a cell containing a data, point it to a separate cell and click Alt+E+S. It is called Paste special function.
Chapter 2: Most commonly used functions in Excel.
Counting and summing functions.
COUNT Gives the number of cells that contain a numeric value
COUNTA Gives the number of nonblank cells
COUNTBLANK Gives the number of blank cells
COUNTIF Gives the number of cells that meet a specified criterion
SUM Gives the sum of its arguments
SUMIF Gives the sum of cells that meet a specified criterion
SUMIFS* Gives the sum of cells that meet multiple criteria
Data lookup formulas
CHOOSE Returns a specific value from a list of values (up to 29) supplied as arguments.
HLOOKUP Searches for a value in the top row of a table and returns a value in the same column from a row you specify in the table.
IF Returns one value if a condition you specify is TRUE, and returns another value if the condition is
FALSE.
LOOKUP Returns a value either from a one-row or one-column range.
MATCH Returns the relative position of an item in a range that matches a specified value.
VLOOKUP Searches for a value in the first column of a table and returns a value in the same row from a column you specify in the table.
Chapter 3: Introduction to charts
What Is a Chart?
A chart also known as graph is nothing but a visual representation of the numeric values. Excel provides you with the tools to create a number of charts.
Creating a Chart
Steps to create a chart
1. Ensure that the data is appropriate (no blanks, same data formats etc)
2. Select the data range which contains the data.
3. Go to Instert tab and click Charts. You will see a variety of chart types.
4. Select the chart you wish to create and click ok. You will see the chart instantaneously.
Chart types
Below is a dialog, listing all the chart types available in Excel.
Keep exploring. Should you have any queries, please comment or drop a mail.
