- WindowsTips.net - Windows Tips and Tricks with Geek

Thursday, February 10, 2022


1. Round to a Certain Number of Digits: ROUND

If you have decimal numbers in your sheet that you want to round up or down, use the ROUND function in Excel.

The syntax is ROUND(value1, digits) where both arguments are required. For value1 , use the number you want to round. For digits , use the number of decimal places to round the number.

For example, to round the number 2.25 up one decimal place, enter the following and press Enter:


And you have your results. If you want to round down, simply use a negative number for the digits argument.

ROUND function in Excel

2. Truncate a Number by Removing the Fraction: TRUNC

Maybe you’d prefer to truncate a number rather than round it. Using the TRUNC function, you can remove the fraction from the number.

The syntax is TRUNC(value1, digits) with value1 required and digits optional. If you don’t enter the digits, the default value is zero.

So, to truncate the number 7.2 you would enter the following and press Enter:


The result of this formula would be the number seven.

TRUNC function in Excel

3. Find the Product by Multiplying Cells: PRODUCT

If you need to multiply several cells, using the PRODUCT function is more efficient than using the multiplication symbol (*) in a formula.

The syntax is PRODUCT(value1, value2,...) with value1 required and value2 optional. You can use value1 for the cell range and value2 for an additional cell range if needed.

To find the product of cells A2 through A10, you would enter the following and hit Enter:


As you can see, this is much simpler than entering A2 * A3 * A4 , and so on.

PRODUCT function in Excel

4. Use the Reference Number of a Given Cell: COLUMN and ROW

With the COLUMN and ROW functions in Excel, you can return the position number of a cell. These functions are useful for entering a series of reference numbers in your sheet, or row numbers, for instance.

The syntax for each is COLUMN(reference) and ROW(reference) where the argument is not required. If you don’t enter an argument, the formula returns the reference for the cell containing the formula.

For example, if you enter the following formula into cell B2, the result would be 2 because B2 is in the second row.


But if you enter the following formula with an argument, you’ll receive the reference number for the cell.


You can see here; the result is 5 because C5 is in the fifth row.

ROW function in Excel

5. Eliminate White Space: TRIM

Oftentimes you when you paste or import data, it contains extra spaces. The TRIM function eliminates white space.

The syntax is TRIM(reference) with the argument required for the cell reference containing the data.

To remove extra spaces from cell A1, you would enter the following and hit Enter:


You’ll then see the data in your referenced cell without the leading and trailing spaces.

TRIM function in Excel

6. Count the Number of Characters in a String: LEN

Maybe you need to find the number of characters in a string of text. Here, you’d use the LEN function in Excel.

The syntax is LEN(reference) with the argument required for the cell reference containing the text.

To find the number of characters in cell A1, enter the following formula and press Enter:


The result is 25 because “Use the data from Finance” contains that number of characters and note that spaces are counted as characters.

LEN function in Excel

No comments:

Post a Comment