Displaying a Value and a Percentage in a Single Cell

Steve would like to display a value and a percentage in the same cell. For instance, if the value is 150 and that value represents 2.6% of a total found in cell B32, he would like "$150 (2.6%)" to be displayed in a cell (without the quote marks). He wonders if there is a way to do this without a macro.

Yes, this can be done using a formula. Steve said his total was in cell B32, but he didn't indicate where the 150 is located. If, though, it is located in a different cell, such as B7, then the following formula will work fine:

=TEXT(B7,"$#,##0") & TEXT(B7/B32," (##.0%)")

This approach relies on the use of the TEXT function two times. The first usage converts for the dollar value and the second converts for the percentage. Note in the second usage of the TEXT function that the pattern it uses can include a leading space and the parentheses that surround the percentage.

This formula rounds the value at B7 so it has no decimal places. If you prefer to have decimal places in your dollar amount, then you can modify the formula slightly:

=TEXT(B7,"$#,##0.00") & TEXT(B7/B32," (##.0%)")

If your 150 value is not in a cell, but must be calculated, then all you need to do is to replace B7 in this formula with the formula used to calculate the 150. That has the potential to make the overall formula much longer, of course. Here's an example where the 150 value is the result of summing cells A12:A19 and then dividing that by 100:

=TEXT(SUM(A12:A19)/100,"$#,##0") & TEXT((SUM(A12:A19)/100)/B32," (##.0%)")

You could also use the DOLLAR function in place of the first TEXT function. Going back to the simpler version where the 150 value is in cell B7, you could do this:

=DOLLAR(B7) & TEXT(B7/B32," (##.0%)")

This gives the value in B7 with the dollar sign and two decimal places. If you don't want any decimal places, then you just add a parameter to the DOLLAR function:

=DOLLAR(B7,0) & TEXT(B7/B32," (##.0%)")

Regardless of how you approach this, you need to understand that what you end up with is a text value. This means that you cannot use the result of this formula as the operand in some other formula that expects numeric values. If that doesn't work for your needs, then the best solution will be to not combine the two values into a single cell, but instead place them in adjacent cells and then use formatting to make the values appear as you want.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10253) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.