How to Show "Discount 10%" in Excel Using Formulas & Custom Formatting - Episode 2668

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Feb 3, 2025.
Microsoft Excel Tutorial: Formula To Show Word Next To Discount Percentage. How to Show "Discount 10%" in Excel Using Formulas & Custom Formatting.

Today's question comes from Roo: How can we modify a dropdown list of discount percentages (10%, 20%, 30%) to display as "Discount 10%" on an invoice? This video covers multiple ways to achieve this, whether your percentages are stored as text or numbers.

First, we look at a simple formula approach. By using a basic concatenation formula with an ampersand (`&`), we can combine the word "Discount" with the selected percentage. However, if your percentages are stored as actual numbers, you'll need to use the `TEXT` function to properly format them.

But wait—there’s an even better way! Instead of using a formula, you can leverage Excel's **Custom Number Formatting**. This trick allows you to keep the numbers functional while displaying them exactly how you want—without extra formulas or helper cells. Just modify the number format to include "Discount" before the percentage, and Excel does the rest.

This method keeps your data clean, prevents formula errors, and makes calculations like summing the discounts work seamlessly. It’s a powerful Excel trick that saves time and reduces complexity.

If you found this tip useful, hit **LIKE**, **SUBSCRIBE**, and leave a comment with your Excel questions. Thanks to Roo for the great question—see you in the next episode of **MrExcel**! 🚀

Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel

Table of Contents
(0:00) Problem Statement: Show the word "Discount" next to number
(0:30) If the list is Text percentages
(1:02) Formula to Concatenate "Discount " with the number
(1:26) The math still works with text percentages
(1:50) If your validation list is numeric
(2:04) Using TEXT function to format as percentage
(2:34) Using Custom Number Format to add words to a number
(4:05) Wrap up

This video answers these questions:
Excel dropdown list with text and percentage
How to show "Discount 10%" in Excel
Excel formula to add text to a number
Custom number formatting in Excel
Apply discount percentage in Excel invoice
Concatenate text and percentage in Excel
Excel TEXT function for formatting numbers
Best way to format discount percentages in Excel
Data validation dropdown with formatted text in Excel
Excel tricks for displaying numbers with labels
maxresdefault.jpg


Transcript of the video:
Today's question, how do we have a formula to show the word next to the discount percentage?
Roo in a comment on YouTube.
Hey, I have a dropdown list in Excel, like 10, 20, 30%.
And I want to apply the discount from the dropdown list to the invoice. It shows 10%, 20%, 30%.
But I want the invoice to show the word “Discount 10%” Or “Discount 20%”. Can someone tell me the formula?
Alright, here's a couple of different ways to do this.
Depending on how you set up your list. Over here I have percentages that are text, right?
How did I do this? Typed an apostrophe, and then 45%.
And then here, Alt D, L for data validation. Allow a List.
And the source of that list are these cells. And then that allows us to choose from the list.
And then down here we want to get the word discount and that percentage.
So equal sign, quotation, discount, space, another quotation, and then the ampersand.
That's shift+seven on a US keyboard. And click on that cell where we selected.
And you get something like Discount 40% over here. If I choose 10%?
“Discount 10%”. Alright.
Now, if you have your original data stored here as text.
It's really amazing that even though this is a text 10%.
This formula correctly, I mean it works, even though that's text.
It wouldn't work with the SUM function. Or a lot of other things.
But because we're directly referencing that cell. There's no problem with it being text.
However, there's a chance that you actually have percentages that are 10, 20, 30, 40.
And then in the dropdown when we choose, we're getting 0.2.
See, in that same formula, it's going to give a 0.2.
And you might think, well wait a second, let's just format this as a percentage.
And while that fixes it there. It does not fix it in the formula.
So the answer here. Is we're going to take the Discount with a space.
Quotation, and then around K5 we're going to put TEXT.
And then at the end we're going to put a number format.
So in quotes, 0% close the quotes. Close the parentheses.
And that will get us the words.
So, or up here we choose 30% and then “Discount 30%”. And our formula continues to work.
Now you specifically asked for a formula.
But the really cool way to do this is just to avoid the formula altogether. So here are my choices.
They're stored as actual numbers.
For example, if I total them up, I can see that they add up to a hundred percent there.
And then my Data Validation – Alt+D, L happens down here. And I get it.
This is not how you want it to look, but check out this amazing trick.
I'm going to come into the group that has the number format dropdown.
And of course none of these have the word discount.
But there are more number formats.
And you can get to those either by using More Number Formats here at the bottom.
Or the quick way, It's just this little dialog launcher here.
And we come into, let's say a percentage with zero decimal, places like that. But then we go to Custom, right?
So the code for a percentage with zero decimal places is 0%.
Before that, I'm going to type a quotation mark the word Discount.
Space, quotation mark. See, and whatever is in quotes there.
We'll print before the number. Or we can put it after the number.
Alright?
And this is an amazing thing because now we're actually choosing from the list. And it's actually storing 20% there.
Which means that this formula can directly reference this cell.
We don't have to have the extra cell outside of the invoice.
This is a beautiful trick where we use a number format to get that word in.
No worries about formula.
No needing to have the extra cell outside of the right.
If I were going to do this, Roo, this is how I would do it.
Alright, well, hey, I want to thank Roo, for sending that question in. And I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
 

Forum statistics

Threads
1,226,179
Messages
6,189,476
Members
453,549
Latest member
MBenedikt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top