Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Format Problem

0

 Is it possible in Excel 2007 to have a permanent format option for currency showing a $ and giving 2 decimal places? And following on from this is it possible to have a permanent percentage option of 1 decimal place ? Any help would be truly appreciated.

Answer
Discuss

Discussion

Do you mean like a button that you can click to have that applied to a cell without having to tweak it?
don (rep: 1989) Jun 14, '18 at 3:27 am
Hi, when I format a cell I right click on the cell and select the format from the drop down list. I am usually wanting to show a number as a dollar and cents amount with the dollar sign showing. Invariably when I select currency I see no dollar sign and then I have to go and select that. I just want a currency option as a drop down that gives me a $xx.xx format. Similarly with percentage, I am always offered 2 decimal places and I always have to change this to one decimal placde. I was hoping that I could add a format in the custom list but I cannot see how to do that and I do not know if it is even possioble or not.
Khaliter Jun 14, '18 at 3:38 am
Add to Discussion

Answers

1

You want to use the "Accounting Number Format" or the "Currency Number Format" number format to get the dollar sign and two decimal places automatically added.

By default, both of these will add 2 decimal places and the currency symbol. However, you could have changed this option by accident - when you go to Format Cells and choose Currency or Accounting, look to the Symbol drop-down menu and make sure that None is not selected - there you choose the desired currency symbol.

Also, the button for currency formatting is the dollar sign button on the Home tab in the Number format box. This allows you to quickly apply the formatting.

If you want a custom number format that you can apply with the click of a button, you can use a macro and then have it run when you click a button on the Quick Access Toolbar

Macro:

Public Sub NumberFormat_1()
Selection.NumberFormat = "$#,##0.0"
End Sub

This macro should put a dollar sign in front of a number and give it 1 decimal place.

Put Button on Quick Access Toolbar:

  1. Click the drop-down arrow or right-click the quick access toolbar (in upper left part of Excel) and then click More Commands...
  2. In the Choose commands from drop-down menu, choose Macros
  3. Select the name of your macro and click the Add button and then Ok

You will now have a new small button in the upper-left of Excel that, when clicked, runs the macro you made and applies the format to the cell or cells that you selected.

Discuss

Discussion

Thank you for this, but I still cannot achieve what I would like to. First  when I go to Format Cells and select currency and select Decimal places 2 and Symbol $, the decimal places always remains at 2 which is good, but the $ symbol is not permanently retained. It might remain for say 5 or more applications, then it reverts to no symbol without me consciously having done anything to cause that. So I have to go back and reselect the dollar symbol, but that is not retained. But after 5 or more instances of reselecting the $ symbol, it stays in place for again 5 or more applications and then reverts to no symbol. It is  really annoying having to keep reselecting the $ symbol which is why I am really grateful for you trying to help me.It almost seems that it toggles from Symbol $ to no Symbol after a certain number of uses or applications and then toggles back to the $ symbol again after so many instances of no symbol appearing.

With regard to your macro suggestion, I take it that you have given me the code I need  to give me a $ and 1 (?) decimal place, but how do I set up this macro, name it, enter it and have it appear in the drop down list under macros so it then can be added to the Quick Access Toolbar?

Again I am very grateful for your help.
Khaliter Jun 14, '18 at 6:30 pm
I don't know what's going on with your settings, so try the macro.

Here's how you install it: Alt+F11 > Insert > Module > paste code.
Here is our tutorial for that: Install Macro in Excel

To add the button to the quick access toolbar, follow the instructions given in the answer.
don (rep: 1989) Jun 16, '18 at 1:53 am
Basically, Excel saves cell formats in each workbook, not in the Excel application itself. Therefore, if you wish to have the same formatting for all your workbooks you should develop a template from which you can then create new workbooks having the required settings. That would be simpler and more straight-forward than having a macro. I wonder if your problem was solved. If so, please mark the answer as "Accepted".
Variatus (rep: 4889) Jun 17, '18 at 8:17 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login