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

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.

**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!

Reference a cell in a macro, and if that cell is blank Excel normally equates that to a zero value. What if you don't ...

Discover MoreIf you have a series of consecutive numbers in a column, you may want to know if it really is consecutive. (In other ...

Discover MoreThe formulas in your worksheet can be displayed (instead of formula results) by a simple configuration change. You can ...

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

2024-01-20 17:11:10

Erik

2024-01-20 09:28:08

Tom Bates`

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.**

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.

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

Copyright © 2024 Sharon Parq Associates, Inc.

## Comments