Two formated values in the same cell

Sometimes you need to insert two values into the same cell. Like having the profit variation in absolute terms and in percentages in a single cell.

Different format for two values in a single cell

The solution to this problem is the TEXT(value,format) formula. Imagine that you need to show the variance over two years profit in absolute terms and in percentages, like in the picture bellow:

two formated values in the same cell

The formula looks like this:

=TEXT(C2-B2,”#,000″)&”      “&TEXT((C2-B2)/B2,”0.0%”)

You can variate the format code between the quotation marks as you like. The single disadvantage of this formulat is that negative numbers cannot be shown in red or other color.

Keep on excelling!


Author has written 295 articles

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.