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
Excel Training by Manish

4. Click OK.

The 'Function Arguments' dialog box appears.

5. Click in the Range box and select the range A1:C2.

6. Click in the Criteria box and type >5.

7. Click OK.

Result. The COUNTIF function counts the number of cells that are greater than 5.

Page no.  21
Excel Training by Manish

Note: instead of using the Insert Function feature, simply type =COUNTIF(A1:C2,">5"). When you arrive at:
=COUNTIF( instead of typing A1:C2, simply select the range A1:C2.

Page no.  22
Excel Training by Manish

Count and Sum Functions in Excel

Count | Countif | Countifs | Sum | Sumif | Sumifs
The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or
multiple criteria.
Count
To count the number of cells that contain numbers, use the COUNT function.

Note: to count blank and nonblank cells in Excel, use COUNTBLANK and COUNTA.
Countif
To count cells based on one criteria (for example, greater than 9), use the following COUNTIF function.

Note: visit our page about the COUNTIF function for many more examples.
Countifs
To count rows based on multiple criteria (for example, green and greater than 9), use the following COUNTIFS
function.

Page no.  23
Excel Training by Manish

Sum
To sum a range of cells, use the SUM function.

Note: visit our page about the SUM function for many more examples.
Sumif
To sum cells based on one criteria (for example, greater than 9), use the following SUMIF function (two arguments).

To sum cells based on one criteria (for example, green), use the following SUMIF function (three arguments, last
argument is the range to sum).

Page no.  24
Excel Training by Manish

Note: visit our page about the SUMIF function for many more examples.
Sumifs
To sum cells based on multiple criteria (for example, circle and red), use the following SUMIFS function (first
argument is the range to sum).

General note: in a similar way, you can use the AVERAGEIF function to average cells based on one criteria and the
AVERAGEIFS function to average cells based on multiple criteria.

Page no.  25
Excel Training by Manish

Logical Functions in Excel

If | And | Or | Not
Learn how to use Excel's logical functions, such as IF, AND, OR and NOT.
If
The IF function checks whether a condition is met, and returns one value if true and another value if false.

1. For example, take a look at the IF function in cell C2 below.

Explanation: if the score is greater than or equal to 60, the IF function returns Pass, else it returns Fail. Visit our page
about the IF function for many more examples.
And
The AND Function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false.

1. For example, take a look at the AND function in cell D2 below.

Explanation: the AND function returns TRUE if the first score is greater than or equal to 60 and the second score is
greater than or equal to 90, else it returns FALSE.

Or
The OR function returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false.

1. For example, take a look at the OR function in cell D2 below.

Page no.  26
Excel Training by Manish

Explanation: the OR function returns TRUE if at least one score is greater than or equal to 60, else it returns FALSE.
Visit our page about the OR function for many more examples.
Not
The NOT function changes TRUE to FALSE, and FALSE to TRUE.

1. For example, take a look at the NOT function in cell D2 below.

Explanation: in this example, the NOT function reverses the result of the OR function (see previous example).

Page no.  27
Excel Training by Manish

AVERAGEIF in Excel
The AVERAGEIF function in Excel calculates the average of cells that meet one criteria. AVERAGEIFS calculates the
average of cells that meet multiple criteria.
1. For example, the AVERAGEIF function below (two arguments) calculates the average of all values in the range
A1:A7 that are greater than 0.

2. The AVERAGEIF function below (three arguments, last argument is the range to average) calculates the average of
all values in the range B1:B7 if the corresponding cells in the range A1:A7 contain exactly Apple.

3. The AVERAGEIF function below calculates the average of all values in the range B1:B7 if the corresponding cells
in the range A1:A7 do not contain exactly Banana.

Page no.  28
Excel Training by Manish

4. The AVERAGEIF function below calculates the average of all values in the range B1:B7 if the corresponding cells
in the range A1:A7 contain a series of zero or more characters + berry. An asterisk (*) matches a series of zero or more
characters.

5. The AVERAGEIF function below calculates the average of all values in the range B1:B7 if the corresponding cells
in the range A1:A7 contain exactly 4 characters. A question mark (?) matches exactly one character.

The AVERAGEIFS function (with the letter S at the end) in Excel calculates the average of cells that meet multiple
criteria.
Page no.  29
Excel Training by Manish

6. The AVERAGEIFS function below calculates the average of all values in the range A1:A7 that are greater than or
equal to 500 and less than or equal to 1000.

Note: first argument is the range to average, followed by two or more range/criteria pairs.

7. The AVERAGEIFS function below calculates the average of all values in the range C1:C7 if the corresponding cells
in the range A1:A7 contain exactly Apple and the corresponding cells in the range B1:B7 contain exactly Red.

Note: again, the first argument is the range to average, followed by two or more range/criteria pairs.

Page no.  30
Excel Training by Manish

Round in Excel
Round | RoundUp | RoundDown
This chapter illustrates three functions to round numbers in Excel. ROUND, ROUNDUP and ROUNDDOWN.
Before your start: if you round a number, you lose precision. If you don't want this, show fewer decimal places without
changing the number itself.
Round
The ROUND function in Excel rounds a number to a specified number of digits. The ROUND function rounds up or
down. 1, 2, 3 and 4 get rounded down. 5, 6, 7, 8 and 9 get rounded up.

1. For example, round a number to three decimal places.

Note: 114.7261, 114.7262, 114.7263 and 114.7264 get rounded down to 114.726 and 114.7265, 114.7266, 114.7267,
114.7268 and 114.7269 get rounded up to 114.727.
2. Round a number to two decimal places.

3. Round a number to one decimal place.

4. Round a number to the nearest integer.

5. Round a number to the nearest 10.

Page no.  31
Excel Training by Manish

6. Round a number to the nearest 100.

7. Round a number to the nearest 1000.

8. Round a negative number to one decimal place.

9. Round a negative number to the nearest integer.

RoundUp
The ROUNDUP function in Excel always rounds a number up (away from zero). 1, 2, 3, 4, 5, 6, 7, 8 and 9 get rounded
up.

1. For example, round a number up to three decimal places.

Note: 114.7261, 114.7262, 114.7263, 114.7264, 114.7265, 114.7266, 114.7267, 114.7268 and 114.7269 get rounded up
to 114.727.
2. Round a number up to two decimal places.

Page no.  32
Excel Training by Manish

3. Round a number up to one decimal place.

4. Round a number up to the nearest integer.

5. Round a number up to the nearest 10.

6. Round a number up to the nearest 100.

7. Round a number up to the nearest 1000.

8. Round a negative number up to one decimal place.

Note: remember, the ROUNDUP function rounds a number up (away from zero).

9. Round a negative number up to the nearest integer.

Page no.  33
Excel Training by Manish

Note: again, the ROUNDUP function rounds a number up (away from zero).

RoundDown
The ROUNDDOWN function in Excel always rounds a number down (toward zero). 1, 2, 3, 4, 5, 6, 7, 8 and 9 get
rounded down.

1. For example, round a number down to three decimal places.

Note: 114.7261, 114.7262, 114.7263, 114.7264, 114.7265, 114.7266, 114.7267, 114.7268 and 114.7269 get rounded
down to 114.726.
2. Round a number down to two decimal places.

3. Round a number down to one decimal place.

4. Round a number down to the nearest integer.

5. Round a number down to the nearest 10.

Page no.  34
Excel Training by Manish

6. Round a number down to the nearest 100.

7. Round a number down to the nearest 1000.

8. Round a negative number down to one decimal place.

Note: remember, the ROUNDDOWN function rounds a number down (toward zero).

9. Round a negative number down to the nearest integer.

Note: again, the ROUNDDOWN function rounds a number down (toward zero).

Page no.  35
Excel Training by Manish

Date & Time Functions in Excel

Year, Month, Day | Date Function | Current Date & Time | Hour, Minute, Second | Time Function
To enter a date in Excel, use the "/" or "-" characters. To enter a time, use the ":" (colon). You can also enter a date and
a time in one cell.

Note: Dates are in US Format. Months first, Days second. This type of format depends on your windows regional
settings. Learn more about Date and Time formats.
Year, Month, Day
To get the year of a date, use the YEAR function.

Note: use the MONTH and DAY function to get the month and day of a date.

Date Function
1. To add a number of days to a date, use the following simple formula.

2. To add a number of years, months and/or days, use the DATE function.

Note: the DATE function accepts three arguments: year, month and day. Excel knows that 6 + 2 = 8 = August has 31
days and rolls over to the next month (23 August + 9 days = 1 September).

Current Date & Time

To get the current date and time, use the NOW function.

Page no.  36
Excel Training by Manish

Note: use the TODAY function to enter today's date in Excel.

Hour, Minute, Second
To return the hour, use the HOUR function.

Note: use the MINUTE and SECOND function to return the minute and second.

Time Function
To add a number of hours, minutes and/or seconds, use the TIME function.

Note: Excel adds 2 hours, 10 + 1 = 11 minutes and 70 - 60 = 10 seconds.

Page no.  37
Excel Training by Manish

CONCATENATE in Excel
Use CONCATENATE, CONCAT, TEXTJOIN or the & operator in Excel to concatenate (join) two or more text
strings.
1. The CONCATENATE function below concatenates the string in cell A1 and the string in cell B1.

2. Use the & operator to produce the exact same result.

3. The CONCATENATE function below concatenates the string in cell A1, the string " and " (enclose text in double
quotation marks) and the string in cell B1.

4. Use the & operator to produce the exact same result.

5. The CONCATENATE function below concatenates the string in cell A1, a space and the string in cell B1.

6. Use the & operator to produce the exact same result.

Page no.  38
Excel Training by Manish

The CONCAT function in Excel 2016 or later produces the exact same result as the CONCATENATE function. Simply
replace CONCATENATE with CONCAT in the formulas shown above.
7. The CONCAT function can also join a range of strings. If you don't need a delimiter (space, comma, dash, etc.) this
can be useful.

The TEXTJOIN function in Excel 2016 or later joins a range of strings using a delimiter (first argument).

8. First, take a look at all the extra spaces in column E below when you drag the CONCATENATE function in cell E2
down to cell E11.

9. The beauty of the TEXTJOIN function is that it can ignore empty cells (if the second argument is set to TRUE).

10. When using the CONCATENATE function, use the TEXT function to format numbers.

Page no.  39
Excel Training by Manish

Note: use 0 to display the nearest integer value. Use 0.0 for one decimal place. Use 0.00 for two decimal places, etc.
Visit our page about the TEXT function for many more examples.
11. Use CHAR(10) to insert a line break.

Note: don't forget to enable text wrapping. On the Home tab, in the Alignment group, click Wrap Text.

12. Let's take a look at one more cool example that uses the TEXTJOIN function. First, the IF function below finds all
members of Team 1.

Explanation: the IF function checks each cell in the named range Teams. If equal to the value in cell E3, it returns the
name from the named range Names, else it returns an empty string (two double quotes with nothing in between).
13. The IF function returns an array constant stored in Excel's memory. Select the formula in the formula bar and press
F9 to see this array constant.

Page no.  40
Excel Training by Manish

14. Add the powerful TEXTJOIN function to concatenate these names. Use a comma and space as the delimiter (first
argument) and set the second argument to TRUE to ignore the empty strings. Finish an array formula by pressing
CTRL + SHIFT + ENTER.

Note: Excel adds the curly braces {}. Using Excel 365 or Excel 2021? Finish by simply pressing Enter. You won't see
curly braces. Mia, James and Sophia are in Team 1. Double click the lower right corner of cell F3 to quickly copy this
formula to the other cells.

Page no.  41
Excel Training by Manish

Text Functions in Excel

Join Strings | Left | Right | Mid | Len | Find | Substitute
Excel has many functions to offer when it comes to manipulating text strings.
Join Strings
To join strings, use the & operator.

Note: instead of using the & operator, use the CONCATENATE function in Excel.
Left
To extract the leftmost characters from a string, use the LEFT function.

Right
To extract the rightmost characters from a string, use the RIGHT function.

Mid
To extract a substring, starting in the middle of a string, use the MID function.

Note: started at position 5 (p) with length 3.

Len
To get the length of a string, use the LEN function.

Page no.  42
Excel Training by Manish

Note: space (position 8) included!

Find
To find the position of a substring in a string, use the FIND function.

Note: string "am" found at position 3. Visit our page about the FIND function for more examples.
Substitute
To replace existing text with new text in a string, use the SUBSTITUTE function.

Page no.  43
Excel Training by Manish

Lookup & Reference Functions in Excel

Vlookup | Hlookup | Match | Index | Choose
Learn all about Excel's lookup & reference functions, such as VLOOKUP, HLOOKUP, MATCH, INDEX and
CHOOSE.
Vlookup
The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table, and then returns a value
in the same row from another column you specify.

1. Insert the VLOOKUP function shown below.

Explanation: the VLOOKUP function looks for the ID (104) in the leftmost column of the range $E$4:$G$7 and
returns the value in the same row from the third column (third argument is set to 3). The fourth argument is set to
FALSE to return an exact match or a #N/A error if not found.

2. Drag the VLOOKUP function in cell B2 down to cell B11.

Page no.  44
Excel Training by Manish

Note: when we drag the VLOOKUP function down, the absolute reference ($E$4:$G$7) stays the same, while the
relative reference (A2) changes to A3, A4, A5, etc. Visit our page about the VLOOKUP function for much more
information and many examples.
Hlookup
In a similar way, you can use the HLOOKUP (Horizontal lookup) function.

Note: if you have Excel 365 or Excel 2021, use XLOOKUP instead of HLOOKUP to perform a horizontal lookup.
Match
The MATCH function returns the position of a value in a given range.

Explanation: Yellow found at position 3 in the range E4:E7. The third argument is optional. Set this argument to 0 to
return the position of the value that is exactly equal to lookup_value (A2) or a #N/A error if not found. Use INDEX and
MATCH in Excel and impress your boss.
Index
The INDEX function below returns a specific value in a two-dimensional range.

Page no.  45
Excel Training by Manish

Explanation: 92 found at the intersection of row 3 and column 2 in the range E4:F7.

The INDEX function below returns a specific value in a one-dimensional range.

Explanation: 97 found at position 3 in the range E4:E7. Use INDEX and MATCH in Excel and impress your boss.
Choose
The CHOOSE function returns a value from a list of values, based on a position number.

Explanation: Boat found at position 3.

Page no.  46