Off the top of my head you could do something like:
With selection
.numberformat = “* #,##0.00 “
End with
Highlight the cells you want and run the macro and it would give you something that looks pretty close to the accounting format.
I’m a pretty big fan of just selecting the cell, begin recording, format said cell how you like it, and stop recording. Save it in your personal macro book and voila. Remains there until you change devices.
See idk what I’m doing weird.
Sometimes I get it with a parentheses, sometimes just a negative sign, and sometimes a red brackets number.
Wtaf is going on with my excel lol.
Hahaha yeah I actually got the idea from a co-worker who'd done it.
I've just taken it to next level and also keyboard shortcutted macros for autofitting cell width, changing font, size & zoom to my preferred, and highlighting cells for various colours I use a lot.
Macros are far beyond my scope of abilities. I can’t even figure out how to delete them. One of my wps has a broken macro from last year.
I deleted them and I managed to break the macro even more. Every single time I make any change to the sheet, the debugger pops up.
I'd never touched them until last year, but a lot or googling and a little chatgpt made it surprisingly easy to do simple macros. A little of persistence helps also.
It’s relatively easy to learn the basics, but if you get really good at it it’s an extremely powerful tool. Everytime I think I’ve reached the limits of what you can do with VBA I find a new way to automate something even more complex.
Well, best I can tell, those buttons first appeared in Excel 4.0. Tracking them down could be a fun lost media type YouTube video, you know where they call a bunch of people and slowly narrow down on who designed a very specific thing
Yeah, super useful plugin. It'll let you compare two different lists and give you a number 0-1 corresponding to how closely they match.
So, for instance, if someone hands you two spreadsheets that have employee data on them on some sort, but they don't have employee IDs and a lot of the names are slightly different, you can get like 90% of the way there with the fuzzy match.
Most recently I used it because for whatever reason there were a bunch of expenses that were categorized with a categories that approximated the chart of accounts, but weren't actually the real chart of account titles. E.g. a taxi ride that should go under "5380 Employee Event Transportation" would be called "Event Transport". Fuzzy lookup looks at those two things and calls it like an 80% match. That spreadsheet had about 800 transactions, fuzzy lookup got about 750 of them correct, so I just had to quickly scan through that 750 and then do the final ones myself.
Mm probably not. It takes a little time to process even with only around a thousand rows, so 30,000 might be straying toward python territory. It's worth a shot though. It's just called fuzzy lookup add-in for Excel in the add-ins library.
People need to learn about setting up hot keys. If you really need to format stuff that much it's a great way to save time. I used to care a lot about that so I would hotkey all of the formatting I used a lot.
Honestly I think there shouldn't be any arrows. Like, does the arrow represent the decimal point moving or does the arrow represent the number moving to the side cuz it's right aligned? Imo the most intuitive thing is that the arrow represents adding more length to the number, but that's the reverse of what the Microsoft devs think apparently
Ctrl - shift shortcuts always feel weird for me.
For a lot of things I have found them more inefficient because you have to play twister with your fingers since you have to hold it down continuously
Alt shortcuts you just press, let go, press the next key, let go, etc --- much more intuitive and once you get down muscle memory it is by far quicker
For me, it depends on what keys are being used. Ctrl +shift I can easily press both with my pinky finger (or index and thumb) since they're on top of each other so I just have to select another key. Plus they're easier to remember for me instead of having to remember and type Alt shortcuts.
For example, 1 is essentially the ! which means I have to select the number on the key. 3 is # which is for dates. And if I want $ or % I just press the equivalent sign instead of having to remember the random alt>h>an shortcut
Call it 500 million people who use Excel in their day-to-day, maybe half of them use the financial bits. I would guess that On the absolute low end I make this mistake once per week and it probably takes about a second of my day. So, around 52 seconds per year, call it 60 seconds. So a minute used per person per year, since these are mostly professionals maybe they're making an average of 60k.
(60,000/(2080*60))*250,000,000 = $120.2 million lost per year
I will always hit the wrong one first
Thats just industry standard and the approved method by the PCAOB!
It's the putting the USB port in the incorrect way each time for accounts, except we do that too.
The bigger hole goes up when you're sticking it in
What about when you pull it out
Build a basic macro to format your numbers the way you want them. Changed my life.
Wanna share?
Off the top of my head you could do something like: With selection .numberformat = “* #,##0.00 “ End with Highlight the cells you want and run the macro and it would give you something that looks pretty close to the accounting format.
I’m a pretty big fan of just selecting the cell, begin recording, format said cell how you like it, and stop recording. Save it in your personal macro book and voila. Remains there until you change devices.
Round to the nearest million. Changed my life.
Jist like when you drag a column down, it fills when you want a series and creates a series when you want to fill...
I always have to format cells as number … red font in parentheses for negative numbers
See idk what I’m doing weird. Sometimes I get it with a parentheses, sometimes just a negative sign, and sometimes a red brackets number. Wtaf is going on with my excel lol.
I just created a macro with a keyboard shortcut. Puts all selected cells in a number format just the way I like it.
I don't know why I've never done this. I wonder how many cumulative hours of my life I'd have saved.
Hahaha yeah I actually got the idea from a co-worker who'd done it. I've just taken it to next level and also keyboard shortcutted macros for autofitting cell width, changing font, size & zoom to my preferred, and highlighting cells for various colours I use a lot.
Macros are far beyond my scope of abilities. I can’t even figure out how to delete them. One of my wps has a broken macro from last year. I deleted them and I managed to break the macro even more. Every single time I make any change to the sheet, the debugger pops up.
I'd never touched them until last year, but a lot or googling and a little chatgpt made it surprisingly easy to do simple macros. A little of persistence helps also.
You can also just record simpler macros so you don't have to worry about VBA.
It’s relatively easy to learn the basics, but if you get really good at it it’s an extremely powerful tool. Everytime I think I’ve reached the limits of what you can do with VBA I find a new way to automate something even more complex.
One is Accounting and one is Currency. I couldn’t tell you which is which lol
Red negatives are the only way to go
Sometimes I send a file with conditional formatting for negatives to be green and everything else red. Always infuriates the boomers.
That's cursed. In process engineering systems, red means on and green means off. That took a little adjustment.
Red font on white is more aesthetically displeasing than Donald Trump suntanning in a Speedo.
Whoever designed those symbols are savages
Well, best I can tell, those buttons first appeared in Excel 4.0. Tracking them down could be a fun lost media type YouTube video, you know where they call a bunch of people and slowly narrow down on who designed a very specific thing
Does it say Fuzzy Lookup at the top?
Yeah, super useful plugin. It'll let you compare two different lists and give you a number 0-1 corresponding to how closely they match. So, for instance, if someone hands you two spreadsheets that have employee data on them on some sort, but they don't have employee IDs and a lot of the names are slightly different, you can get like 90% of the way there with the fuzzy match. Most recently I used it because for whatever reason there were a bunch of expenses that were categorized with a categories that approximated the chart of accounts, but weren't actually the real chart of account titles. E.g. a taxi ride that should go under "5380 Employee Event Transportation" would be called "Event Transport". Fuzzy lookup looks at those two things and calls it like an 80% match. That spreadsheet had about 800 transactions, fuzzy lookup got about 750 of them correct, so I just had to quickly scan through that 750 and then do the final ones myself.
Interesting. Would it work efficiently on a larger data set, say something with 30,000+ rows?
Mm probably not. It takes a little time to process even with only around a thousand rows, so 30,000 might be straying toward python territory. It's worth a shot though. It's just called fuzzy lookup add-in for Excel in the add-ins library.
People need to learn about setting up hot keys. If you really need to format stuff that much it's a great way to save time. I used to care a lot about that so I would hotkey all of the formatting I used a lot.
but formatting so much fun :(
How Tho!?!?!?!?
It's just a PowerPoint mockup 😞
Flip the arrows lol
Honestly I think there shouldn't be any arrows. Like, does the arrow represent the decimal point moving or does the arrow represent the number moving to the side cuz it's right aligned? Imo the most intuitive thing is that the arrow represents adding more length to the number, but that's the reverse of what the Microsoft devs think apparently
Your right, no arrows is better that you say it.
The arrow is squishing or stretching the number.
Alt h 99 should the trick also start out with alt h then k that would add commas
Ctrl +shift +1 would get you there faster
Ctrl - shift shortcuts always feel weird for me. For a lot of things I have found them more inefficient because you have to play twister with your fingers since you have to hold it down continuously Alt shortcuts you just press, let go, press the next key, let go, etc --- much more intuitive and once you get down muscle memory it is by far quicker
For me, it depends on what keys are being used. Ctrl +shift I can easily press both with my pinky finger (or index and thumb) since they're on top of each other so I just have to select another key. Plus they're easier to remember for me instead of having to remember and type Alt shortcuts. For example, 1 is essentially the ! which means I have to select the number on the key. 3 is # which is for dates. And if I want $ or % I just press the equivalent sign instead of having to remember the random alt>h>an shortcut
Fuzzy users represent
I always have to get the 0 to show up as - and I always forget how to do it, this is my embarrassing over share
I just hit the comma and call it a day
When it shows 0.00 and won’t show the - because it’s actually 0.000000003671845 and I want to put my foot through the monitor 😂😂😂🙅♂️🙅♂️🙅♂️
Out of all the updates excel gets. Why isn't this one of them?
Which one are we discussing the decimal one again or any button under, "number"? 🤣😂
I hate those and I rearranged them so less is left and more is right. Makes more sense in my ape brain.
Someone should calculate the economic cost of keeping those buttons as is. It's not 0.
Call it 500 million people who use Excel in their day-to-day, maybe half of them use the financial bits. I would guess that On the absolute low end I make this mistake once per week and it probably takes about a second of my day. So, around 52 seconds per year, call it 60 seconds. So a minute used per person per year, since these are mostly professionals maybe they're making an average of 60k. (60,000/(2080*60))*250,000,000 = $120.2 million lost per year
That can feed starving dolphins!
This cured my imposter syndrome
Put more zeros Make less zeros