Anyway (quick?) to automate a repetitive number formatting?

Topic

New Reply

I have one type of number formatting that I use quite often and I would like to know if there is anyway to automate the process, or make it quicker/shorter.  Here is what I do for many of my numeric formatting in Excel 365:

Select cell(s) -> right click on the Selection -> Select <Format cells> —>Number —>            2 decimal places —> Use 1000 Separator —> (Select) the 2nd Selection Under “Negative Numbers” —> <OK>

I use this many, many times in Excel as I have to reconcile and reformat a bunch of financial sheets in various workbooks every week.  If there was some way to automate this process, or speed it up, this would be a great help to me.  My thanks in advance for any assistance anyone can provide.

 

Reply | Quote

Replies

https://www.accountingweb.com/technology/excel/excel-tip-change-excel-default-settings#:~:text=To%20create%20new%20default%20settings%20in%20Excel%2C%20follow,of%20the%20Excel%20folder.%20…%20More%20items…%20
You can make it a template.

Susan Bradley Patch Lady/Prudent patcher

Reply | Quote

Make a Cell Style for the formatting. That will achieve everything you want.

https://support.microsoft.com/en-us/office/apply-create-or-remove-a-cell-style-472213bf-66bd-40c8-815c-594f0f90cd22

Reply | Quote

Ron,

You could also place this macro in your Personal.xlsm file.

Sub MyNumberFmt()
'
' MyNumberFmt Macro
' Set number format to 2 decimal places, thousands separator and red negative numbers.
'
' Keyboard Shortcut: Ctrl+t
'
    Selection.NumberFormat = "#,##0.00;[Red]#,##0.00"
    
End Sub

Then all you would have to do is to set the macro to use Ctrl+t to activate. Once done you just select the cells to format and press Ctrl+t.

HTH 😎

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

WOW, this is what I love about the Lounge:  you ask a question and get three great answers, all of which reveal new things to me.  Thanks to you all for your input.

Susan Bradley, I tried your approach and it didn’t work, or more rightly, I did not get it to work.  My question is:  If I try it once and for whatever reason, it does not work, how do I start over, or can I just restart it and what ever I do, will just replace what was there from previous attempts?

WSMartinM, you opened my eyes to an aspect of Excel that is new to me and I thank you for that.  I have read through the reference and it looks like something I can use.  My question is that if I introduce a new “Custom cell style” to Excel, is it there  “for all time” until I remove it?  Is it present in every new workbook and not just in the one in which it was created?

RetiredGeek, thanks for the Macro advice, however, I am not used to working with Macros and I don’t really know how all the Macro stuff works.  For example, I am not aware that I have, or should have a “personal.xlsm” file.  I also do not know how to assign CTRL+t to the Macro to make it work.  All that said, one of my goals for 2021 is to learn about the basics of Macros, why I need a “personal.xlsm file” and how to assign a keyboard combination to a macro.  When I have that under may belt, I will definitely revisit responses that recommend macros as their solution for my problems…I may even be able to solve my own problems with my own macros 😊.  Having said that, do you have any sites that you might recommend for those of us wishing to upgrade their macro skills, starting with the basics?

Ron M

Reply | Quote

Ron,

Sorry, I don’t have any specific recommendations having been programming spreadsheet macros since VisiCalc I’ve learned by the seat-of-my-pants so to speak. However, I just googled Excel vba tutorials and came up with several likely candidates. Of course, I  several here (@Zeddy, @Maudibe and myself come to mind) are always willing to help and offer advice.

I did a little digging on my machine and found this very good Getting Started Guide written by our own Maudibe with a few comments by yours truly. Maybe, Maud will see this and post his final version. Commented-Starting-with-Visual-Basic-for-Applications-in-Excel-V2
HTH 😎

 

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RetiredGeek, thanks for the information.  I will definitely track it down.

I know what you mean “by-the-seat-of-my-pants” as I have been doing that since the days of Fortran in ’64.  I have tended to move more towards the “systems/business analyst” side of the IT world, “fakin’ it ’til you make it” kind of learning there, so the techie side has been a bit, well, techie, to me.  I was thinking of looking to see if there was a “Dummies” book as there is one on just about everything else.

Ron

Reply | Quote

Susan Bradley and WSMartinM, I must be doing something wrong.  I can setup the new style for the cell(s) that I want, but it doesn’t seem to continue beyond that one spread sheet.  When I open a new instance of Excel, the Style is not there.  There must be a way to ensure that it is present in every Excel spreadsheet that I open, because I use it quite a bit.

Any insight on how to make it universal?  Thanks.

Ron

Reply | Quote

Here’s how to save a Cell Style so that  you can use it in all Workbooks.
Just follow the instructions step–by-step and you’ll find it is easy.

https://support.microsoft.com/en-gb/office/save-cell-styles-to-use-in-all-new-workbooks-0b3905c5-45d0-4ad4-be39-571f445d30cb#:~:text=Open%20the%20workbook%20that%20contains,New%20and%20select%20Blank%20Workbook.&text=next%20to%20the%20cell%20styles,Click%20Merge%20Styles.

Reply | Quote

WSMartinM, thank you for the reference – so far so good. Is it possible to update the “Book” file created by this process or do I have to make a new one to replace the old one. Also, is it possible to replace one of the existing Style boxes that are visible when the spreadsheet opens, rather than have to Select down to find the “new” format? Thanks.

Ron

Reply | Quote

You CAN update the BOOK template but it is not totally straightforward.

1. Update the Styles as you wish
2. “Save As” a template with another name but in the same location
3. Delete the BOOK template
4. Rename the new template as BOOK

I didn’t quite understand “Also, is it possible to replace one of the existing Style boxes that are visible when the spreadsheet opens, rather than have to Select down to find the “new” format?”

Are you seeking to edit an existing Style but keep the name, or change the way in which they are displayed ?

Reply | Quote

WSMartinM, I was actually wondering if it was possible to assign this new formatting to one of the visible style boxes as right now, to get the “new” style, I have to scroll down to the “Comma”  style and apply it.  If I could assign this new style to one of the existing, ‘visible without scrolling’, Style boxes, e.g., “Bad” and rename it to “Number Format”.  That way, I would only have to click on the new, renamed Style box instead of having to scroll down to the “Comma” style. (I know, lazy me!) 😁

Ron

 

Reply | Quote

Yes you can.

Just open the Template, right click on the Style you want to change and select Modify – and take it from there.
Then go through the procedure I posted earlier for saving a modified Template.

This is odd though – the Styles I have ccreated for myself display <span style=”text-decoration: underline;”>above</span> the standard Styles.

Having said all that, I you want to be really lazy (and why not ?), write a macro to apply one of your new styles to the current cell, then put it on the Quick Access toolbar with an appropriate icon. Then its just one click to apply it – that’s as good as it gets !

Post back if you don’t knkow how to do this.

Reply | Quote

WSMartinM, thanks for the instructions on updating an existing Style, that I created, and modifying an existing, visible one on the main Toolbar.

As far as writing a macro and putting it on the Quick Access tool bar, I am afraid that is a bit beyond my capabilities right now, well,  maybe more than a bit.  I have just set out to learn VBA for Excel and while it probably will not be that much of a problem as I started my computer life in Fortran, Assembler and ended the programming side with C and a bit of C++, so I can read the code when I encounter it.  My problem is trying to understand what it is doing. 🙃  The rest of my IT career was centred more around the Systems/Business Analyst side of that world and then I retired!

Nevertheless, I will give it a go – first – Update the Book Template.

Ron

Reply | Quote

I now have a new box that I can click in the Styles area that applies the number formats the way I need them, so thank you WSMartinM, that works fine on new spreadsheets.  Is there a way to make it appear on all spreadsheets, old or new?

Ron

Reply | Quote

If you follow the instructions in my post which starts “Here’s how to save a Cell Style so that  you can use it in all Workbooks.” you will be find the new style to be in all new spreadsheets.

To get the your new Style into an older workbook, open either the template (book.xltm) that you made as per the above instructions, or any other saved workbook which contains you new Style. Then open the spreadsheet where you want to add the new Style and click on:

• Home
• Cell Styles

Right down at the bottom left click on Merge Styles and select the file you want to copy the Style from. When it prompts you “Merge styles that have the same name ?” probably click on “No” and only your new style will be copied.

PS You must open the two spreadsheets in the order given or this won’t work. The “template” first, the spreadsheet that is to receive the new style second.

Reply | Quote

WSMartinM, I followed your instructions, except, Excel did not ask me “Merge styles that have the same name?”, it simply went ahead and “did it”, what ever “it” was.  The result was the same though, the new style appeared on the old spreadsheet and none of the other styles were changed as there were no other ones to change.  Oh well, magic happened?  Thanks for your help with this aspect of getting new styles to work with old spreadsheets.

Ron M

Reply | Quote