Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
Jon has found a clever way to show words with many numbers in Excel
Back in July 2012 we showed you how to put singular or plural words against numbers in Excel – like 1 cookie or 3 cookies. At the end we noted how it got more complicated when you tried to deal with one, zero and negative numbers as well because Excel’s cell formatting has limitations.
Jon E. has come up with a clever solution which uses conditional formatting to split negative and positive cell values then have formatting appropriate for each side of zero.
You need TWO custom numbers, one for the positives, one for the negatives.
what I used for the positive values:
[=1]0 “day late”;[>1]0 “days late”;”On Time”
what I used for the negative values:
[=-1]0 “day early”;[<-1]0 "days early";"On Time"
the key is to have a conditional format on all the cells telling it to use the positive custom format for cells >0 and the negative values for cells <1. (I guess you could also use >-1 and <0)
then you’ve got positive, negative, singular, plural, AND zero all at the same time! “
Those rules when setup in Excel look like this:
One rule handles cell values greater than zero and the other for zero and less. In fact zero can be formatted by either rule.
In the format dialog choose Custom and paste in the formatting code above, with words changed to suit your needs.
The result is a cell with the value alone in the cell but displayed in the form of readable text.
Very nice, great work Jon!
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.