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!

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

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

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

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