Today a question from someone who frequently uses three currency styles in Excel: USD, EGP, and SAR. Excel makes it easy to choose USD, GBP, EUR, CHF but if your currency is not in that drop-down, then you are scrolling through an insanely long list.
It seems that there is no good way to change the five currencies in the drop-down on the Excel home tab.
Instead, I propose a scheme where you can add SAR or EGP from a button on the Quick Access Toolbar and that button will also add the currency to the Cell Styles gallery that travels with the workbook. This means that your co-workers can choose SAR or EGP without having anything in their personal macro workbook.
Table of Contents
(0:00) Difficult to choose most currencies
(2:19) The macro
(3:42) Adding to Quick Access Toolbar
(4:55) Testing in new workbook
(5:25) Adding Styles Gallery to QAT
Here is the code from the Personal Macro Workbook for SAR:
It seems that there is no good way to change the five currencies in the drop-down on the Excel home tab.
Instead, I propose a scheme where you can add SAR or EGP from a button on the Quick Access Toolbar and that button will also add the currency to the Cell Styles gallery that travels with the workbook. This means that your co-workers can choose SAR or EGP without having anything in their personal macro workbook.
Table of Contents
(0:00) Difficult to choose most currencies
(2:19) The macro
(3:42) Adding to Quick Access Toolbar
(4:55) Testing in new workbook
(5:25) Adding Styles Gallery to QAT
Here is the code from the Personal Macro Workbook for SAR:
VBA Code:
Sub ApplySARStyle()
' Attempt to apply SAR style
' If it does not exist, create it.
Err.Clear
On Error Resume Next
Selection.Style = "SAR"
If Err.Number = 0 Then
' The style already exists. Okay to exit
On Error GoTo 0
Exit Sub
End If
' If you get here, SAR does not exist.
' Format the selection and create a new style
ActiveWorkbook.Styles.Add Name:="SAR"
With ActiveWorkbook.Styles("SAR")
.IncludeNumber = True
.IncludeFont = False
.IncludeAlignment = False
.IncludeBorder = False
.IncludePatterns = False
.IncludeProtection = False
.NumberFormat = "[$SAR] #,##0"
End With
Selection.Style = "SAR"
End Sub
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2455 - Taming the Currency drop-down in Excel.
Wow, this is a great question that came in today.
I never realized how difficult this is. You know here on the Home tab we have this currency dropdown that has always had the same 5 currencies: English, GBP, Euros, Chinese and CHF.
And if you need something else, oh God, you're in trouble because it is difficult to get to a lot of these currency symbols.
This particular person is trying to have three available: U.S.
Dollar and then SAR for Saudi Arabia and EGP for Egypt.
I mean, those are at the end of a really really really long list.
And it would just be a pain to have to go in and choose that all the time.
And isn't there some way to come in here and say, “these are not the five that I want, I want some other five”?
That would be really really good.
And in asking the other MVPS and also doing a search on Google, some people have decisively said “No, there's just no way to change that”.
Which seems really short-sighted on the part of Microsoft.
Excel is used in all these different countries.
Why can't we change those symbols that are in that list?
Various countries are going to have several that they're going to use all the time.
It should be available.
So as I was contemplating this, the first thing that I thought of would be instead of the the currency dropdown here to use cell styles.
You could define several new cell styles and add them right up here to the top of the list, which kind of makes it easier to choose those.
But the huge problem with cell styles is there tied to the workbook, and they're not global.
There's no good global way to change this.
And as the person who asked the question was saying, “Most of the time we're working in USD”.
I don't want to change my regional settings settings.
Here's what I came up with.
I said what if we had a little macro and the macro took care of applying the cell style?
So I'm proposing a cell style called EGP.
And when I run that macro, the first thing it's going to do is attempt to apply the EGP cell style.
If that doesn't work, it's then going to go through the steps of adding the EGP cell style to this drop-down.
Let's go take a look at the macro.
I added it to my Personal Macro Workbook.
Let's just deal with SAR first.
Attempt to apply the SAR style.
I'm going to do this by applying the .Style “SAR” to the selection.
Now if the SAR style exists, then the error number is going to be zero and I know that everything is good.
I can just exit the sub.
But if it doesn't exist, then I know that I have to create that, right?
So here we clear out the error, and say “On Error Resume Next”.
In other words, if you get an error, just keep running.
And I'll check the Err.Number.
I know if the code gets here, SAR does not exist.
And then we do ActiveWorkbook.Styles.Add and I give it a name of SAR.
And then with that style, I say we're going to pay attention to the number format.
We're not going to care about the font, the alignment, the border, the patterns, the protection.
And then specify that the style called SAR has a number format of left square bracket dollar SAR right square bracket.
I got that from the custom number section in Format Cells.
And then hash comma hash hash zero.
If you need more decimal places, by all means, add them.
This successfully adds the SAR style to the current workbook.
And then applies that style as the last bit of code.
Making the EGP macro was equally easy.
Just copy the whole thing and change SAR to EGP in a few places.
I customized my Quick Access Toolbar.
Right-click, Customize Quick Access Toolbar.
Go to Macros.
Find ApplyEGPStyle and ApplySARStyle.
When you add those, they end up here with a long tool text.
And now in Microsoft 365 that we have the ability to customize the command string up there.
It's important to come in and click Modify.
Type EGP there and SAR for the other one.
And I just chose these two human figures here, one with a dark shirt and one with a white shirt.
Just because it would be sSomething that I maybe could remember.
Alright, so we're in this workbook applying currency styles.
And I'm going to choose the area that I want to format as SAR.
We'll take a look in cell styles and there's not currently a SAR style.
I choose this macro to run and it formats it as as SAR.
And then when I open it, see, I now have a style called SAR available to me.
And then EGP.
Over here I'll click the button.
It applies the EGP style and I now have a style called EGP.
Now I'm just going to Ctrl+N. Brand new workbook.
A bunch of data and Control+Enter.
Because I'm in a new workbook, those cell styles are gone, right?
But the first time I use it.
And then for the first time I used SAR.
I can now easily apply it by opening cell styles and choosing EGP cell styles.
And the advantage here is that as I send this to other people, they're not going to have my personal macro workbook.
By setting up the style in the workbook, it perhaps will make it easier for other people in my company who have to share this workbook.
If we right click, Add to Quick Access Toolbar.
And then Customize the Quick Access Toolbar.
And move this to be the first item in the list.
Click OK.
Alright then I should be able to choose some cells.
And it Alt, then 1 to open the Cell Styles and then just the Up Arrow for EGP.
Or here – Alt, 1, Up Arrow and choose SAR.
Yeah, it's kind of a little bit easier to choose.
Certainly easier than going into the currency drop down.
Now, again, as I mentioned, this seems to be a huge mistake on Microsoft's part to not have some kind of registry hack to let us change the icons that are here.
That would have been the right thing to do.
But absent that, and who knows how long it will take for them to ever add this?
I think the Cell Styles might be kind of the only viable way to make this easy to use.
So the first time you open a workbook and you need to apply EGP or SAR, you're going to use the Quick Access Toolbar.
Other people that then use that workbook will then be able to open Cell Styles and choose EGP.
And of course, whatever currencies you need to use, you would just customize in a similar fashion.
It's not the best solution.
But, given that there doesn't seem to be any easy way to change this dropdown.
I think the Cell Styles is probably the easy way to go.
Before they added these five new icons out here, the cell styles would actually open up and you could see the first row.
Life would have been really easy back then.
Yeah, OK, so I can report, that computer 2560 by 1440 - the cell styles are open and you'd actually have one click access to the top row of custom styles that you had.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Let's hear you, Nancy!
Have a great holiday.
Wow, this is a great question that came in today.
I never realized how difficult this is. You know here on the Home tab we have this currency dropdown that has always had the same 5 currencies: English, GBP, Euros, Chinese and CHF.
And if you need something else, oh God, you're in trouble because it is difficult to get to a lot of these currency symbols.
This particular person is trying to have three available: U.S.
Dollar and then SAR for Saudi Arabia and EGP for Egypt.
I mean, those are at the end of a really really really long list.
And it would just be a pain to have to go in and choose that all the time.
And isn't there some way to come in here and say, “these are not the five that I want, I want some other five”?
That would be really really good.
And in asking the other MVPS and also doing a search on Google, some people have decisively said “No, there's just no way to change that”.
Which seems really short-sighted on the part of Microsoft.
Excel is used in all these different countries.
Why can't we change those symbols that are in that list?
Various countries are going to have several that they're going to use all the time.
It should be available.
So as I was contemplating this, the first thing that I thought of would be instead of the the currency dropdown here to use cell styles.
You could define several new cell styles and add them right up here to the top of the list, which kind of makes it easier to choose those.
But the huge problem with cell styles is there tied to the workbook, and they're not global.
There's no good global way to change this.
And as the person who asked the question was saying, “Most of the time we're working in USD”.
I don't want to change my regional settings settings.
Here's what I came up with.
I said what if we had a little macro and the macro took care of applying the cell style?
So I'm proposing a cell style called EGP.
And when I run that macro, the first thing it's going to do is attempt to apply the EGP cell style.
If that doesn't work, it's then going to go through the steps of adding the EGP cell style to this drop-down.
Let's go take a look at the macro.
I added it to my Personal Macro Workbook.
Let's just deal with SAR first.
Attempt to apply the SAR style.
I'm going to do this by applying the .Style “SAR” to the selection.
Now if the SAR style exists, then the error number is going to be zero and I know that everything is good.
I can just exit the sub.
But if it doesn't exist, then I know that I have to create that, right?
So here we clear out the error, and say “On Error Resume Next”.
In other words, if you get an error, just keep running.
And I'll check the Err.Number.
I know if the code gets here, SAR does not exist.
And then we do ActiveWorkbook.Styles.Add and I give it a name of SAR.
And then with that style, I say we're going to pay attention to the number format.
We're not going to care about the font, the alignment, the border, the patterns, the protection.
And then specify that the style called SAR has a number format of left square bracket dollar SAR right square bracket.
I got that from the custom number section in Format Cells.
And then hash comma hash hash zero.
If you need more decimal places, by all means, add them.
This successfully adds the SAR style to the current workbook.
And then applies that style as the last bit of code.
Making the EGP macro was equally easy.
Just copy the whole thing and change SAR to EGP in a few places.
I customized my Quick Access Toolbar.
Right-click, Customize Quick Access Toolbar.
Go to Macros.
Find ApplyEGPStyle and ApplySARStyle.
When you add those, they end up here with a long tool text.
And now in Microsoft 365 that we have the ability to customize the command string up there.
It's important to come in and click Modify.
Type EGP there and SAR for the other one.
And I just chose these two human figures here, one with a dark shirt and one with a white shirt.
Just because it would be sSomething that I maybe could remember.
Alright, so we're in this workbook applying currency styles.
And I'm going to choose the area that I want to format as SAR.
We'll take a look in cell styles and there's not currently a SAR style.
I choose this macro to run and it formats it as as SAR.
And then when I open it, see, I now have a style called SAR available to me.
And then EGP.
Over here I'll click the button.
It applies the EGP style and I now have a style called EGP.
Now I'm just going to Ctrl+N. Brand new workbook.
A bunch of data and Control+Enter.
Because I'm in a new workbook, those cell styles are gone, right?
But the first time I use it.
And then for the first time I used SAR.
I can now easily apply it by opening cell styles and choosing EGP cell styles.
And the advantage here is that as I send this to other people, they're not going to have my personal macro workbook.
By setting up the style in the workbook, it perhaps will make it easier for other people in my company who have to share this workbook.
If we right click, Add to Quick Access Toolbar.
And then Customize the Quick Access Toolbar.
And move this to be the first item in the list.
Click OK.
Alright then I should be able to choose some cells.
And it Alt, then 1 to open the Cell Styles and then just the Up Arrow for EGP.
Or here – Alt, 1, Up Arrow and choose SAR.
Yeah, it's kind of a little bit easier to choose.
Certainly easier than going into the currency drop down.
Now, again, as I mentioned, this seems to be a huge mistake on Microsoft's part to not have some kind of registry hack to let us change the icons that are here.
That would have been the right thing to do.
But absent that, and who knows how long it will take for them to ever add this?
I think the Cell Styles might be kind of the only viable way to make this easy to use.
So the first time you open a workbook and you need to apply EGP or SAR, you're going to use the Quick Access Toolbar.
Other people that then use that workbook will then be able to open Cell Styles and choose EGP.
And of course, whatever currencies you need to use, you would just customize in a similar fashion.
It's not the best solution.
But, given that there doesn't seem to be any easy way to change this dropdown.
I think the Cell Styles is probably the easy way to go.
Before they added these five new icons out here, the cell styles would actually open up and you could see the first row.
Life would have been really easy back then.
Yeah, OK, so I can report, that computer 2560 by 1440 - the cell styles are open and you'd actually have one click access to the top row of custom styles that you had.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Let's hear you, Nancy!
Have a great holiday.
Last edited by a moderator:
