MS Excel Custom Formatting - Show Units Of Measure Without Changing Your Numbers To Text

Without Changing Your Numbers To Text Show Units Of Measure in MS Excel with Example.

Many of you don't know that you can display a unit of measure (eg. lb., kg., km., in., etc...) in a cell of MS Excel worksheet without changing your numbers to text, so you can still use them in calculations?

It is not a very tough work, it's very easy! All you need to do is create a Custom Number Format.

To Add Measure Units in your MS Excel worksheet cell without changing the format of your cell from Number to Text follow the given below steps:-

1) Select the cells which containing the numbers;

2) On the Home tab, click the small dialog launcher arrow on the bottom right corner of the Number group;

3) In the Format Cells dialog on the Number tab click Custom in the Category list;

4) In the Type field, select a number format for your cells;

5) Type the unit of measure enclosed in quotes (e.g. " km." ) to the right of the number format in the Type field.

Format Cells as Number with Text (Measure Units) in Excel

Now when you look at the numbers in your MS Excel worksheet, they will display the unit of measure, but in the formula bar you can easily see that they are still numbers that you can use in calculations.

Read More

Hide Duplicate / Repeating Values in a Column in MS Excel.

How to hide Repeating /  Duplicate Values In A Column in Microsoft Excel with Example?


As you are working with lists in MS Excel, after sorting, you may find that, you have multiple rows with the same values repeating down one or more columns. If you are printing this list as a report, for aesthetic (pleasing) purposes, you may prefer to have only the first instance of each item shown.

One option is to clear the cells for all duplicate items below the first one. The problem with this approach:

1) approach can be very time-consuming if you're working with a long list and

2) once you delete values from the cells, you won't be able to sort or filter your list.

Here is a clean and easy trick using Conditional Formatting that you need only do one time. After that all future duplicate values will be hidden.

So, How to hide repeating values in a Column in Microsoft Excel (While Printing) with example?
1) Select a range of cells in the column where you want to hide duplicates;

2) In this example we have selected B2:B28, you will notice that cell B2 is the Active Cell;

3) On the Home tab, click Conditional Formatting in the Styles group and then choose New Rule...;

4) Select 'Use a formula to determine which cells to format';

5) In the formula field type =B2=B1

6) Click the Format... button, click the Font tab, select the Color dropdown, choose white* from the color palette and click OK.

Note:- * (Aestrik) This assumes the background of your cells is white so the duplicate text will be invisible.

7) Click OK to close the Conditional Formatting dialog.

You should now see that the only first value is visible but all the duplicates below are hidden. However they are still there (just look in the formula bar) but they blend into the white background.

Hide duplicates in Excel 2007 using this Conditional Formatting trick

Use Conditional Formatting and white font to hide duplicates in Excel 2007.

Read More