Round

 

This chapter illustrates three functions to round numbers in Excel. The ROUND, ROUNDUP andROUNDDOWN function.

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

1. Round a number to two decimal places.

Note: 1, 2, 3, and 4 get rounded down. 5, 6, 7, 8, and 9 get rounded up. In this example, 114.7211, 114.7221, 114.7231 and 114.7241 get rounded down to 114.72 and 114.7251, 114.7261, 114.7271, 114.7281 and 114.7291 get rounded up to 114.73.

2. Round a number to one decimal place.

3. Round a number to the nearest integer.

4. Round a number to the nearest 10.

5. Round a number to the nearest 100.

RoundUp

The ROUNDUP function always rounds a number up (away from zero). For example, round a number up to one decimal place.

RoundDown

The ROUNDDOWN function always rounds a number down (toward zero). For example, round a number down to the nearest integer.

Read More

Cell References

 

Cell references in Excel are very important. Understand the difference between relative, absolute and mixed reference, and you are on your way to success.

Relative Reference

By default, Excel uses relative reference. See the formula in cell D2 below. Cell D2 references (points to) cell B2 and cell C2. Both references are relative.

1. Select cell D2, click on the lower right corner of cell D2 and drag it down to cell D5.

Cell D3 references cell B3 and cell C3. Cell D4 references cell B4 and cell C4. Cell D5 references cell B5 and cell C5. In other words: each cell references its two neighbors on the left.

Absolute Reference

See the formula in cell E3 below.

1. To create an absolute reference to cell H3, place a $ symbol in front of the column letter and row number of cell H3 ($H$3) in the formula of cell E3.

2. Now we can quickly drag this formula to the other cells.

The reference to cell H3 is fixed (when we drag the formula down and across). As a result, the correct lengths and widths in inches will be calculated.

Mixed Reference

Sometimes we need a combination of relative and absolute reference (mixed reference).

1. See the formula in cell F2 below.

2. We want to copy this formula to the other cells quickly. Drag cell F2 across one cell, and look at the formula in cell G2.

Do you see what happens? The reference to the price should be a fixed reference to column B. Solution: place a $ symbol in front of the column letter of cell B2 ($B2) in the formula of cell F2. In a similar way, when we drag cell F2 down, the reference to the reduction should be a fixed reference to row 6. Solution: place a $ symbol in front of the row number of cell B6 (B$6) in the formula of cell F2.

Result:

Note: we don't place a $ symbol in front of the row number of B2 (this way we allow the reference to change from B2 (Jeans) to B3 (Shirts) when we drag the formula down). In a similar way, we don't place a $ symbol in front of the column letter of B6 (this way we allow the reference to change from B6 (Jan) to C6 (Feb) and D6 (Mar) when we drag the formula across).

3. Now we can quickly drag this formula to the other cells.

The references to column B and row 6 are fixed.

Read More

Logical Functions

 

Learn how to use Excel's logical functions such as the IF, AND and OR function.

If Function

The IF function checks whether a condition is met, and returns one value if TRUE and another value if FALSE.

1. Select cell C2 and enter the following function.

The IF function returns Correct because the value in cell A1 is higher than 10.

The AND Function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false.

And Function

1. Select cell D2 and enter the following formula.

The AND function returns FALSE because the value in cell B2 is not higher than 5. As a result the IF function returns Incorrect.

Or Function

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

1. Select cell E2 and enter the following formula.

The OR function returns TRUE because the value in cell A1 is higher than 10. As a result the IF function returns Correct.

General note: the AND and OR function can check up to 255 conditions.

Read More

Count and Sum Functions

 

The most used functions in Excel are the functions that count and sum. You can count and sum based on onecriteria or multiple criteria.

Count

To count the number of cells that contain numbers, use the COUNT function.

Countif

To count cells based on one criteria (for example, higher than 9), use the following COUNTIF function.

Countifs

To count cells based on multiple criteria (for example, green and higher than 9), use the following COUNTIFS function.

Sum

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

Sumif

To sum cells based on one criteria (for example, higher 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).

Sumifs

To sum cells based on multiple criteria (for example, blue and green), use the following SUMIFS function (first argument is the range to sum).

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

Read More

Protect

Encrypt an Excel file with a password so that it requires a password to open it.

1. Open a workbook.

2. On the green File tab, click Save As.

3. Click on the Tools button and click General Options.

4. In the Password to open box, enter a password and click OK.

5. Reenter the password and click OK.

Note: this feature also encrypts your Excel file. If you lose or forget the password, it cannot be recovered.

6. Enter a file name and click Save.

It requires a password to open this Excel file now. The password for the downloadable Excel file is "easy".

Read More

Data Validation

Use data validation in Excel to make sure that users enter certain values into a cell.

Data Validation Example

In this example, we restrict users to enter a whole number between 0 and 10.

To create the data validation rule, execute the following steps.

Create Data Validation Rule

1. Select cell C2.

2. On the Data tab, click Data Validation.

On the Settings tab:

3. In the Allow list, click Whole number.

4. In the Data list, click between.

5. Enter the Minimum and Maximum values.

Input Message

Input messages appear when the user selects the cell and tell the user what to enter.

On the Input Message tab:

1. Check 'Show input message when cell is selected'.

2. Enter a title.

3. Enter an input message.

Error Alert

If users ignore the input message and enter a number that is not valid, you can show them an error alert.

On the Error Alert tab:

1. Check 'Show error alert after invalid data is entered'.

2. Enter a title.

3. Enter an error message.

4. Click OK.

Data Validation Result

1. Select cell C2.

2. Try to enter a number higher than 10.

Result:

Note: to remove data validation from a cell, select the cell, on the Data tab, click Data Validation, and then click Clear All. You can use Excel's Go To Special feature to quickly select all cells with data validation.

Read More

Find & Replace

     

You can use Excel's Find and Replace feature to quickly find specific text and replace it with other text. You can use Excel's Go To Special feature to quickly select all cells with formulas, comments, conditional formatting, constants, data validation, etc.

Find

To quickly find specific text, execute the following steps.

1. On the Home tab, click Find & Select, Find...

The 'Find and Replace' dialog box appears.

2. Type the text you want to find. For example, type Ferrari.

3. Click 'Find Next'.

Excel selects the first occurrence.

4. Click 'Find Next' to select the second occurrence.

5. To get a list of all the occurrences, click 'Find All'.

Replace

To quickly find specific text and replace it with other text, execute the following steps.

1. On the Home tab, click Find & Select, Replace...

The 'Find and Replace' dialog box appears (with the Replace tab selected).

2. Type the text you want to find (Veneno) and replace it with (Diablo).

3. Click 'Find Next'.

Excel selects the first occurrence. No replacement has been made yet.

4. Click 'Replace' to make a single replacement.

Note: use 'Replace All' to replace all occurrences.

Go To Special

You can use Excel's Go To Special feature to quickly select all cells with formulas, comments, conditional formatting, constants, data validation, etc. For example, to select all cells with formulas, execute the following steps.

1. Select a single cell.

2. On the Home tab, click Find & Select, Go To Special...

Note: Formulas, Comments, Conditional formatting, Constants and Data Validation are shortcuts. They can also be found under Go To Special.

3. Select Formulas and click OK.

 

Note: you can search for cells with formulas that return Numbers, Text, Logicals (TRUE and FALSE) and Errors. These check boxes are also available if you select Constants.

Excel selects all cells with formulas.

General note: if you select a single cell before you click Find, Replace or Go To Special, Excel searches the entire worksheet. To search a range of cells, first select a range of cells.

Read More