Adding unit labels to Google Sheets without messing up sorting

Google Sheets has come a long way, so far that I consider it a viable alternative to Excel. I did, however, bump into a little annoyance recently involving scientific data collected in a spreadsheet. Luckily it has an easy solution.

Say you’re collecting some measurements and you want to include “cm” after each value. If you add cm to the data (1cm, 2cm, 2.4cm, 12cm), you’ll notice that sorting the data results in some unexpected orderings. To get around this, you can set up a custom format for the column of data.

1. Highlight the data you want to format.
2. Click the Format menu and hover over “Number” (or click Number format  in the toolbar).
3. Hover over “More Formats.”
4. Select Custom number format.
5. Search in the menu text box to select a format. You can also add your own custom number format into the text box. In our case we would add (0 “cm”) without the parentheses in order to have “cm” added at the end of each value.
6. Click Apply.

This works fine as long as all the units are cm. Have to put cm, mm, etc., in the same column? Sorry… I haven’t figured that out yet.