Excel Training by Manish
Contents
Sort in Excel .................................................................................................................................................................................. 3
One Column ......................................................................................................................................................................... 3
Multiple Columns ................................................................................................................................................................ 4
Filter in Excel................................................................................................................................................................................. 6
Conditional Formatting in Excel ...................................................................................................................................................... 9
Highlight Cells Rules ........................................................................................................................................................... 9
Clear Rules......................................................................................................................................................................... 11
Top/Bottom ........................................................................................................................................................................ 12
Conditional Formatting with Formulas .............................................................................................................................. 13
Enter a Formula ................................................................................................................................................................. 17
Edit a Formula ................................................................................................................................................................... 18
Operator Precedence .......................................................................................................................................................... 18
Copy/Paste a Formula ........................................................................................................................................................ 19
Insert Function ................................................................................................................................................................... 20
Count and Sum Functions in Excel ................................................................................................................................................ 23
Count.................................................................................................................................................................................. 23
Countif ............................................................................................................................................................................... 23
Countifs.............................................................................................................................................................................. 23
Sum .................................................................................................................................................................................... 24
Sumif.................................................................................................................................................................................. 24
Sumifs ................................................................................................................................................................................ 25
Logical Functions in Excel ............................................................................................................................................................ 26
If ......................................................................................................................................................................................... 26
And .................................................................................................................................................................................... 26
Or ....................................................................................................................................................................................... 26
Not ..................................................................................................................................................................................... 27
AVERAGEIF in Excel ................................................................................................................................................................. 28
Round in Excel ............................................................................................................................................................................ 31
Round ................................................................................................................................................................................. 31
RoundUp ............................................................................................................................................................................ 32
RoundDown ....................................................................................................................................................................... 34
Date & Time Functions in Excel.................................................................................................................................................... 36
Year, Month, Day .............................................................................................................................................................. 36
Date Function..................................................................................................................................................................... 36
Current Date & Time ......................................................................................................................................................... 36
Hour, Minute, Second ........................................................................................................................................................ 37
Time Function .................................................................................................................................................................... 37
CONCATENATE in Excel........................................................................................................................................................... 38
Page no. 1
Excel Training by Manish
Text Functions in Excel ................................................................................................................................................................ 42
Join Strings ........................................................................................................................................................................ 42
Left..................................................................................................................................................................................... 42
Right .................................................................................................................................................................................. 42
Mid..................................................................................................................................................................................... 42
Len ..................................................................................................................................................................................... 42
Find .................................................................................................................................................................................... 43
Substitute ........................................................................................................................................................................... 43
Lookup & Reference Functions in Excel ........................................................................................................................................ 44
Vlookup ............................................................................................................................................................................. 44
Hlookup ............................................................................................................................................................................. 45
Match ................................................................................................................................................................................. 45
Index .................................................................................................................................................................................. 45
Choose ............................................................................................................................................................................... 46
Page no. 2
Excel Training by Manish
Sort in Excel
One Column | Multiple Columns
You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order.
One Column
To sort on one column, execute the following steps.
1. Click any cell in the column you want to sort.
2. To sort in ascending order, on the Data tab, in the Sort & Filter group, click AZ.
Result:
Note: to sort in descending order, click ZA.
Page no. 3
Excel Training by Manish
Multiple Columns
To sort on multiple columns, execute the following steps.
1. On the Data tab, in the Sort & Filter group, click Sort.
The Sort dialog box appears.
2. Select Last Name from the 'Sort by' drop-down list.
3. Click on Add Level.
4. Select Sales from the 'Then by' drop-down list.
5. Click OK.
Page no. 4
Excel Training by Manish
Result. Records are sorted by Last Name first and Sales second.
Page no. 5
Excel Training by Manish
Filter in Excel
Filter your Excel data if you only want to display records that meet certain criteria.
1. Click any single cell inside a data set.
2. On the Data tab, in the Sort & Filter group, click Filter.
Arrows in the column headers appear.
3. Click the arrow next to Country.
4. Click on Select All to clear all the check boxes, and click the check box next to USA.
Page no. 6
Excel Training by Manish
5. Click OK.
Result. Excel only displays the sales in the USA.
6. Click the arrow next to Quarter.
7. Click on Select All to clear all the check boxes, and click the check box next to Qtr 4.
8. Click OK.
Result. Excel only displays the sales in the USA in Qtr 4.
9. To remove the filter, on the Data tab, in the Sort & Filter group, click Clear. To remove the filter and the arrows,
click Filter.
Page no. 7
Excel Training by Manish
There's a quicker way to filter Excel data.
10. Select a cell.
11. Right click, and then click Filter, Filter by Selected Cell's Value.
Result. Excel only displays the sales in the USA.
Page no. 8
Excel Training by Manish
Note: simply select another cell in another column to further filter this data set.
Conditional Formatting in Excel
Highlight Cells Rules | Clear Rules | Top/Bottom | Conditional Formatting with Formulas
Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.
Highlight Cells Rules
To highlight cells that are greater than a value, execute the following steps.
1. Select the range A1:A10.
2. On the Home tab, in the Styles group, click Conditional Formatting.
3. Click Highlight Cells Rules, Greater Than.
Page no. 9
Excel Training by Manish
4. Enter the value 80 and select a formatting style.
5. Click OK.
Result. Excel highlights the cells that are greater than 80.
6. Change the value of cell A1 to 81.
Result. Excel changes the format of cell A1 automatically.
Page no. 10
Excel Training by Manish
Note: you can also use this category (see step 3) to highlight cells that are less than a value, between two values, equal
to a value, cells that contain specific text, dates (today, last week, next month, etc.), duplicates or unique values.
Clear Rules
To clear a conditional formatting rule, execute the following steps.
1. Select the range A1:A10.
2. On the Home tab, in the Styles group, click Conditional Formatting.
3. Click Clear Rules, Clear Rules from Selected Cells.
Page no. 11
Excel Training by Manish
Top/Bottom
To highlight cells that are above average, execute the following steps.
1. Select the range A1:A10.
2. On the Home tab, in the Styles group, click Conditional Formatting.
3. Click Top/Bottom Rules, Above Average.
Page no. 12
Excel Training by Manish
4. Select a formatting style.
5. Click OK.
Result. Excel calculates the average (42.5) and formats the cells that are above this average.
Note: you can also use this category (see step 3) to highlight the top n items, the top n percent, the bottom n items, the
bottom n percent or cells that are below average.
Conditional Formatting with Formulas
Take your Excel skills to the next level and use a formula to determine which cells to format. Formulas that apply
conditional formatting must evaluate to TRUE or FALSE.
1. Select the range A1:E5.
Page no. 13
Excel Training by Manish
2. On the Home tab, in the Styles group, click Conditional Formatting.
3. Click New Rule.
4. Select 'Use a formula to determine which cells to format'.
5. Enter the formula =ISODD(A1)
6. Select a formatting style and click OK.
Page no. 14
Excel Training by Manish
Result. Excel highlights all odd numbers.
Explanation: always write the formula for the upper-left cell in the selected range. Excel automatically copies the
formula to the other cells. Thus, cell A2 contains the formula =ISODD(A2), cell A3 contains the formula
=ISODD(A3), etc.
Here's another example.
7. Select the range A2:D7.
8. Repeat steps 2-4 above.
9. Enter the formula =$C2="USA"
10. Select a formatting style and click OK.
Page no. 15
Excel Training by Manish
Result. Excel highlights all USA orders.
Explanation: we fixed the reference to column C by placing a $ symbol in front of the column letter ($C2). As a result,
cell B2, C2 and cell D2 also contain the formula =$C2="USA", cell A3, B3, C3 and D3 contain the formula
=$C3="USA", etc.
Page no. 16
Excel Training by Manish
Formulas and Functions in Excel
Enter a Formula | Edit a Formula | Operator Precedence | Copy/Paste a Formula | Insert Function
A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already
available in Excel.
For example, cell A3 below contains a formula which adds the value of cell A2 to the value of cell A1.
For example, cell A3 below contains the SUM function which calculates the sum of the range A1:A2.
Enter a Formula
To enter a formula, execute the following steps.
1. Select a cell.
2. To let Excel know that you want to enter a formula, type an equal sign (=).
3. For example, type the formula A1+A2.
Tip: instead of typing A1 and A2, simply select cell A1 and cell A2.
4. Change the value of cell A1 to 3.
Page no. 17
Excel Training by Manish
Excel automatically recalculates the value of cell A3. This is one of Excel's most powerful features!
Edit a Formula
When you select a cell, Excel shows the value or formula of the cell in the formula bar.
1. To edit a formula, click in the formula bar and change the formula.
2. Press Enter.
Operator Precedence
Excel uses a default order in which calculations occur. If a part of the formula is in parentheses, that part will be
calculated first. It then performs multiplication or division calculations. Once this is complete, Excel will add and
subtract the remainder of your formula. See the example below.
Page no. 18
Excel Training by Manish
First, Excel performs multiplication (A1 * A2). Next, Excel adds the value of cell A3 to this result.
Another example,
First, Excel calculates the part in parentheses (A2+A3). Next, it multiplies this result by the value of cell A1.
Copy/Paste a Formula
When you copy a formula, Excel automatically adjusts the cell references for each new cell the formula is copied to. To
understand this, execute the following steps.
1. Enter the formula shown below into cell A4.
2a. Select cell A4, right click, and then click Copy (or press CTRL + c)...
...next, select cell B4, right click, and then click Paste under 'Paste Options:' (or press CTRL + v).
Page no. 19
Excel Training by Manish
2b. You can also drag the formula to cell B4. Select cell A4, click on the lower right corner of cell A4 and drag it across
to cell B4. This is much easier and gives the exact same result!
Result. The formula in cell B4 references the values in column B.
Insert Function
Every function has the same structure. For example, SUM(A1:A4). The name of this function is SUM. The part
between the brackets (arguments) means we give Excel the range A1:A4 as input. This function adds the values in cells
A1, A2, A3 and A4. It's not easy to remember which function and which arguments to use for each task. Fortunately,
the Insert Function feature in Excel helps you with this.
To insert a function, execute the following steps.
1. Select a cell.
2. Click the Insert Function button.
The 'Insert Function' dialog box appears.
3. Search for a function or select a function from a category. For example, choose COUNTIF from the Statistical
category.
Page no. 20