20 February 2022

Clever solution for Singular/Plural in Excel

Peter Deegan Microsoft 365, Microsoft Excel, Microsoft Office, Microsoft Office for Mac, Office 2007, Office 2010, Office 2013, Office 2016, Office 2019, Office 2021 / Office LTSC, Office 365, Office for Mere Mortals, Office Watch

Jon has found a clever way to show words (singular, plural or zero) with many numbers in Excel using conditional formatting.

We already shown you several ways 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: