Displaying a Value and a Percentage in a Single Cell

Written by Allen Wyatt (last updated January 20, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


3

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Colors and Fonts for Worksheet Tabs

Changing the color used on a worksheet tab is easy. Just follow the three steps in this tip.

Discover More

Embedding TrueType Fonts

If you need to make sure that the fonts in your document can be used by another person or on a different system, you'll ...

Discover More

Changing Colors of Spelling and Grammar Underlines

The red and green wavy underlines used in Word can be a boon for proofing a document, but they are of little use if you ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Counting Groupings Below a Threshold

When analyzing data, you may need to distill groupings from that data. This tip examines how you can use formulas and ...

Discover More

Indirectly Referencing a Cell on a Different Worksheet

Excel includes the powerful INDIRECT function which can be used to assemble references to other cells in your workbook. ...

Discover More

Tracking Down Invalid References

When you discover that there is an invalid reference in a workbook, you can have a bear of a time tracking down the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine minus 5?

2024-01-20 17:11:10

Erik

The DOLLAR() function is risky. Its currency symbol is determined by local language settings so it will show $ in the US, £ in the UK, € in Europe, etc. But of course the function does not do any currency conversion, so sharing the workbook with others could result in erroneous text.


2024-01-20 09:28:08

Tom Bates`

Thanks, Allen, for showing all those alternatives. I never knew about the DOLLAR function! Learn something every day! :-)


2024-01-20 07:07:01

MICHAEL (Micky) AVIDAN

I still think - this is the shortest (and a bit more elegant) solution and above that the use of the DOLLAR function will show € for German users, and £ for Great Britain.

(see Figure 1 below)

Figure 1. 


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.