Custom Formats: Doing Math with text (and its not algebra)

What you see is what you get... usually.

A cell can contain data (text or numbers) or formulas that manipulate other cells with data. Formulas, however, always return data as well, so ultimately cells always contain data.

Some times, you will want the data to appear exactly as it was entered; however there will come times where you want the data in your cell to appear on the screen slightly differently than how it was entered, when this happens you usually want to change the format of the data.

There are many common formats that you can apply with a few key strokes:

Currency Format - CTRL + SHIFT + $
Percentage Format - CTRL + SHIFT + %
Number Format - CTRL + SHIFT + !
Scientific Format - CTRL + SHIFT + ^

However, sometimes you are dealing with a metric that isn't a currency. And you would like for the value to still be used like a number by other formulas, however show up like a text string.

[Text strings are any sequence of characters like "2 Weeks" or "8 Days" that can contain numbers or non-numerical digits. While these strings can be more informative to users than just numbers alone, you can't perform mathematical operations on these strings"]

When this happens you can alter the format of the cell to have it appear with text by going into the Format Cell wizard (CTRL + 1) and in the number tab select custom. Then type in the additional text you would like to appear in the cell surrounded by double quotation marks.

Here is an example of a spreadsheet that looks like text but is actually numbers and simple arithmatic.

First you can enter the following values and formulas into Excel

Then with your cursor sitting on the number 2 go into the custom format screen and enter the following custom format as shown. Then highlighting the next cell down enter '0* "Sheep / Farm"' and in the cell with the formula enter '0* "Sheep"'

After you have done that you will end up with the following spreadsheet that looks like you typed in words next to the numbers, but are still able to perform simple math on them.

Another slightly more practical example would be as follows:

Could be formatted to look like ->









 You can find all the examples that were shown above here.

As always, feel free to leave me your questions comments and future blog ideas.

-Danny