# 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

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

Spell Checking with Text Boxes

Text boxes are a common design element in a document. You may wonder if the text you place in a text box can be spell ...

Discover More

Moving from Sheet to Sheet

Need to move quickly through the worksheets in a workbook? Learn the keyboard shortcuts and you can make short work of ...

Discover More

If you need to apply a common formatting change to all the headings in your document, a quick way to do it is to use the ...

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)

Returning Zero When a Referenced Cell is Blank

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 More

Identifying Missing Numbers in a Consecutive Series

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

Discover More

Pulling Formulas from a Worksheet

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

Discover More
##### Subscribe

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

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 more than 3?

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.