Microsoft Office Excel Tips

There are two ways for calculating the difference between two times. If you use a simple subtraction operation such as “=C1-A1,” select the custom format “h:mm” from the Format Cells options. If you use the TEXT() function such as “=TEXT(C1-A1,”h:mm”),” define the custom format within the equation. The TEXT() function converts numbers to text using a custom format.

There are two ways for calculating the difference between two dates. You can use a simple subtraction operation such as “=B2-A2” to calculate the amount of days between those two calendar dates. You can also calculate the amount of week days between two calendar dates with the NETWORKDAYS() function. For example, if you input “=NETWORKDAYS(B2,A2)” in a new blank cell, your result will include only the days between Monday and Friday.

=54*89

To multiply range of number, type an equal sign, PRODUCT, and then enter the numbers, cells, or ranges you want to multiply, in parentheses. Separate each number or cell reference you’re multiplying with a comma, and each range of cells with a colon, like this: A1:A3. In the example, the formula would look like this:

=PRODUCT(A1:A3,C1:C3)

Select the cells you want to limit the number of decimal places. Right click the selected cells, and select the Format Cells from the right-clicking menu. In the coming Format Cells dialog box, go to the Number tab, click to highlight the Number in the Category box, and then type a number in the Decimal Places box. For example, if you want to limit only 1 decimal place for selected cells, just type 1 into the Decimal places box. See below screen shot:

To limit Number Of Decimal Places In Formulas In Excel, you can use the Round function. The basic Round formula syntax is =ROUND(number, num_digits); and if you want to combine the Round function and other formula, the formula syntax should be changed to =Round(original_formula, num_digits).

In our case, we want to limit one decimal place for sum value, so we can apply the formula as =ROUND(SUM(A1:A9),1). In this formula, A1:A9 is the range you want to sum, 1 means you want to limit one decimal place.

1. Enter a formula in the top cell.
2. Select the cell with the formula, and hover the mouse cursor over a small square at the lower right-hand corner of the cell, which is called the Fill handle. As you do this, the cursor will change to a thick black cross.
3. Hold and drag the fill handle down the column over the cells where you want to copy the formula.

   
   In a similar manner, you can drag formula into adjacent cells to the right, to the left or upwards.

If the formula includes relative cell references (without the $ sign), they will automatically change based on a relative position of rows and columns. So, after copying the formula, verify that the cell references have got adjusted properly and produce the result that you want. If necessary, switch between absolute, relative and mixed references by using the F4 key.

Copying a formula down by dragging the fill handle not only copies the formula, but also the source cell formatting such as font or background color, currency symbols, the number of displayed decimal places, etc. For prevent overwriting the existing cell formatting, drag the fill handle as demonstrated above, release it, click the Auto Fill Options drop-down menu, and select Fill Without Formatting.