Format Numbers

[praf007]

Is there any way I can add this format permanently in Excel/Format/Custom instead of manually adding open/close brackets

 

[praf007]

Micron,

I did some testing, and what you said is true, but only within that particular workbook.
It does not apply/show in other workbooks you may have open (or if you create a new one).
I think maybe they are looking for a permanent solution that is always available in all workbooks.

praf007,

If that is indeed the case, one way would be to create a short little macro that does that, and save it in your Personal Macro Workbook.
The code is a real simple one-line procedure, like this:

Sub MyCustomFormat()
    Selection.NumberFormat = "#,##0.00;[Red](#,##0.00)"
End Sub

So all you would have to add after you add it to your Personal Macro Workbook is first select the range you want to apply it to, and then call/run it from the Macro Listing (View -> Macros -> View Macros), i.e.
View attachment 120107

(as you can see, I have a lot of other macros in there - if this is your first, it would be the only one listed).

Macros stored in your Personal Macro Workbook are always available to you while you are logged in to that particular computer.

See here for information on the Personal Macro Workbook: Excel Personal Macro Workbook: how to create, use and share

Hi Joe 4 i will try the macro. thank you

 

[praf007]

If you do not have one, you can easily create one by doing the following steps:
Go to View -> Macros -> Record Macro
and then select the "Personal Macro Workbook" option, as shown below:

View attachment 120114

Then you can go back to View -> Macros -> Record Macro and click "Stop Recording".

Then if you view the VB Editor, you should be able to see the Personal Macro Workbook.

Hi Joe4, I have a macro set up. But when i want to run macro in a new file I have to open the macro file as well. How can I run macro without opening the file where the macro is in? I know this is a silly question

 

[Micron]

In what workbook did you create the macro? If in Personal.xlsb did that file get created in the xlstart folder, or did you happen to move it from there? I ask because personal.xlsb should automatically open hidden and make the macro available to you. You should not have to open any other file to use the macro.

 

[MARK858]

should automatically open hidden

It doesn't normally open hidden when created, it normally needs hiding manually and saving the first time as it is just a .xlsb file with the name Personal (it should automatically open though if still in the XLSTART folder)

 

[Alex Blakenburg]

I don't believe you need to use a macro.
Create the Custom format in a New Workbook and then save it to your XLStart folder as an "Excel Template (*.xltx)" file named Book.xltx and then also as Sheet.xltx.
(the option is about the 9th option in the save as drop down box)

The format should then be available when you create a new workbook and when you create a sheet in an existing workbook.

I would recommend using something that looks closer to this though:
#,##0.00_);[Red](#,##0.00)
The trailing "_)" on the positive should ensure that the numbers align by leaving a space with the size of ")" to the right of the positive number.
Note: When you copy it in to the Custom box make sure that to delete the space after the ")", when I tried to copy it in, it seemed to pick up an additional space which throws out the alignment.

 

[Micron]

#,##0.00_);[Red](#,##0.00)

I would wager that is already in the list as part of a regular install of Excel. It is in mine, and I can assure everyone that there's no way I added it to the list of custom formats (or any other list).

 

[Peter_SSs]

I don't think that you should need any special workbook or template but try this Windows (not Excel) setting.
First close out of Excel then ..

It may be in a different place for you but for me
Windows settings -> Time & Language -> Region -> then at the top right:

Additional date, time & regional settings:

Change date, time, or number formats: -> Formats tab -> Additional settings -> Numbers tab -> Negative number format: -> Choose (1.1) -> Apply/OK until you have closed back out

Now open Excel again and I believe the wanted option will be there in the Custom number formats area of Excel.

 

[Alex Blakenburg]

I would wager that is already in the list as part of a regular install of Excel. It is in mine, and I can assure everyone that there's no way I added it to the list of custom formats (or any other list).

FYI - I opened my Excel without the Book.xltx in the start up folder and a red with parentheses is not in my custom formats.

 

[Sergius]

Add the necessary number format, for example, as in the photo

Then save the book as the "Excel Template (*.xltx)" template "in the Exel Templates folder under the name Book.xltx. I have a folder of templates, for example, at C: \ Users \ User \ Documents \ Custom Office Templates.
After the opening of the exel on the start page, we go to the "New" menu and there we select the "Personal" tab, and on it is our template (see the photo).

As a result, in the new book we have our previously created number format.

 

[praf007]

I don't believe you need to use a macro.
Create the Custom format in a New Workbook and then save it to your XLStart folder as an "Excel Template (*.xltx)" file named Book.xltx and then also as Sheet.xltx.
(the option is about the 9th option in the save as drop down box)

The format should then be available when you create a new workbook and when you create a sheet in an existing workbook.

I would recommend using something that looks closer to this though:
#,##0.00_);[Red](#,##0.00)
The trailing "_)" on the positive should ensure that the numbers align by leaving a space with the size of ")" to the right of the positive number.
Note: When you copy it in to the Custom box make sure that to delete the space after the ")", when I tried to copy it in, it seemed to pick up an additional space which throws out the alignment.

ThanksAlex, Will try that