How to put labels into the cells without forgoing the formulae

November 1st, 2005 by admin admin

Assuming you have a list of values as shown:

Value 1 Value 2 Total
34 (3) 31
23 66 89

The first set is actually calculation for hrs and the second is for metres. You wish to make them more meaningful by putting the description “hr” to the first set of values and “m” to the second. For most users, they would have make the calculation first, convert them to values and then input the metrics into these values. By doing so, the formulae are wiped out.

If you do not wish to eliminate the formulae, here is the solution for you:
1. Set up the table as shown.
2. Add up the numbers or input whatever formula you have for the total column.
3. Highlight the first set of numbers, 34, (3) and 31.
4. Go to format, cells.
5. Select the numeric form you wish to present the data, e.g. if you select the number category, choose 0 decimal places, use thousand separators, click on brackets and red color for negative values. Click here for diagram.
6. Now select the category “Custom”
7. You should see the format “#,##0_);[Red](#,##0)” in the type box.
8. Add in the word hr after the zero and after the bracket for negative values as shown #,##0 “hr”_);[Red](#,##0) “hr”. If you are conversant in the numeric format, you can skip step 5.

9. Click ok and you should see the numbers presented as below. The formulae are retained while the presentation has changed.
Value 1 Value 2 Total
34 hr (3) hr 31 hr
23 66 89
10. Change the second set of numbers to include “m” after the numbers by repeating step 3 to 9.

Leave a Reply

You must be logged in to post a comment.