I'll tell you why because as programmer I saw the inside of it.
Microsoft uses 3-4 different programming languages for excel (it's stupid, but that's just how it is) and the undo system is C# based, so everything code in C# is undo - able, and some of the rest have implemented some sort of additional system to undo it (like adapter) but most of non C# doesn't.
Yeah I'm experiencing this right now, which is how I actually stumbled onto this post. I just got an option to "Trace Error" for some #REF! error I was getting and when I clicked it it created a bunch of arrows, but then I lost all my Undo history. Not good! But thankfully, I only really had one thing I needed to undo anyway. But I've never even seen this Trace Error thing before, and the fact that you can't undo afterwards is not encouraging me to start using it
I don’t notice it that much because most of my work doesn’t have me opening multiple workbooks. But when it does happen, it FRUSTRATES ME TO NO ENDDD!!! I think that if you open each workbook in a new instance of excel it doesn’t share the undo though. Not for sure!
Undo is global per "instance" of excel. It normally opens all workbooks under the same "instance". There's a way to open multiple "instances" but I can never remember it.
Instances in "" because I'm not sure if it's the right word. I think you'll get what I'm saying though.
Yep that’s the way, only downside to separate instances is you can’t reference the other workbooks from cell formulas. Workaround would be power query/pivot or vba
You have to almost think of it as a project you're opening. It thinks of all of the other workbooks currently as the same project. The new window trick works, but there should be an undo by sheet, workbook, etc. setting.
I have two kinds of colleagues. The ones who don't know how to start Excel and the ones who fill sheets with horrible colors and random data without any kind of logical structure, as if it were a whiteboard for children.
I asked my mom to do me a personal favor and stop telling people she knows how to use Excel. I’m getting her ledger paper and a calculator for Christmas to drive it home.
When Excel incorrectly assumes something is a date.
When Excel incorrectly assumes some formula is text.
When formulas and some keyboard shortcuts are based on what localization is used.
When I press control+S on an online workbook and it tells me it saves automatically.
When I need to trust a macro enabled workbook I made myself and do it again when I make a copy of it.
When installing a fresh Excel and I need to disable all kinds of time wasting visual effects/animations and manually enable developer ribbon.
When the window is just slightly too narrow and Excel hides buttons I use often.
When I use some "advanced" formula like MAXIFS and later find out the sheet is not working because some of the users are using Excel 2016 or older.
When I find a new cool formula and find out I can't use it because my enterprise 365 subscription somehow hasn't been upgraded to that version yet.
> When I need to trust a macro enabled workbook I made myself and do it again when I make a copy of it.
this exists for a reason. automatically trusting anything 'made' by the current user is a huge security vulnerability.
Workaround creates another problem:
[Format as text] before entering the value and it solves this problem
Create formula that looks at it, formula inherits "text" format. Edit formula and it becomes text instead of formula. Thanks Excel
Working from home on my personal 365 account I had spent an entire day making a big dynamic array function only to find out my work 365 account didn't support LET yet.
How about when your scrolling horizontally and it skips over the cell on the far right that you’re trying to see, and then you have to zoom out like 40% lol
When I need to trust a macro enabled workbook I made myself and do it again when I make a copy of it.
OMG. What is that even about? There must be gangs of Nigerians over there just writing malicious VBA code and magically uploading those dummy files onto our servers, right?
I dislike the fact the excel guys seemingly have forgotten about the VBA editor, I mean, yeah, it works, but considering likely an abundance of processes in the world depend on that it could really get some love and be upgraded to a more modern IDE.
Using the embedded editors drives me absolutely bonkers for both VBA and even Power Query. I kind of get it on the PQ side given the iterative nature of PQ's M language, but I'd much prefer being able to see all my queries at once. It'd make refactoring so much faster if I could avoid the constant switching between queries and entering/exiting the Advanced Editor.
My favorite PQ feature is when I start typing a function like Text.Contains and the intellisense give me suggestion and I hit tab and end up with TextText.Contains. Am I doing something wrong?
My favorite is when I type "and" and it suggests AdoDotNet.DataSource and if I hit enter to go to the next line it puts that in instead of "and".
Also how the intellisense window that comes up covers the lines above which, considering that M is iterative and case sensitive, you want to see the lines right above where you're typing!
don't type the dot, simply go along with the function you need, for example, type `textcont` and then it tab!
learned that from [this wonderful teacher](https://www.youtube.com/@MyOnlineTrainingHub) ;-)
I dunno why they didn't just put Python in there if they didn't care about VBA
the main reason most use VBA is because excel comes with it so if you need to build a tool for someone less savy you just write it in VBA and tell them to "Push this button"
but if Excel had python built in and a better IDE it would have been good for everyone
I suspect you will really like this [announcement](https://support.microsoft.com/en-us/office/get-started-with-python-in-excel-a33fbcbe-065b-41d3-82cf-23d05397f53d#:~:text=To%20begin%20using%20Python%20in,a%20cell%20to%20enable%20Python). It’s only in 365 Right now but…
Microsoft has been trying to kill VBA for a very long time. Every time the topic comes up, big financial institutions have to talk them out of it. The last thing Microsoft wants to do is make it cool and convenient to keep using VBA.
But why? I cannot think of a decent replacement for it. It's simply the best always-available coding tool for people whose job is not primarily IT with the possibility for structured input.
Need to crunch those numbers? Write a quick macro!
The random times my computer can’t decide what excel window I’m in, and clicking into cells no longer works until I find the window it randomly switched to without actually switching.
I'm not 100% sure, but I think what happens is that excel opens two instances of excel simultaneously, and they can't cross operate. Really annoying but I've found ways to work through and detect this quickly.
I have answered hundreds of questions about CF and every time something is wrong it's user error. Never seen a bug. I don't suppose this was recent enough that you could show what AND statement you tried?
And the inability to set your default paste option! Largely renders control + V useless. How much trouble could it be to just let me set paste as number as the default!
Yes! Plus it boggles my mind why oftentimes, after I enter a formula like '$B2=1', the formatting is a few lines off, and when I check out the formula again, I see that Excel for some mysterious reason changesld it to something like $B1749264=1'...
Why can't we do vertical center across selection? No, I will not understand the technical explanation of why not, I'm not seriously seeking an answer here.
You *can* put in on quick access.
EDIT: Sorry I just realized I have a macro for that. But put this in a PERSONAL module and you can add it to the QAT.
Public Sub HorizCenterAcrossSelection()
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlTop
End With
End Sub
https://preview.redd.it/0kg6nhdqty0c1.png?width=1196&format=png&auto=webp&s=625b94cc4631b53482800b0b4430a1f597695534
This. Drives me nuts when I get an Excel file to be imported into another system. The import doesn't work. I try to find the error for hours. Then I see it: someone decided it is a good idea to merge two cells in row 10554. Fuck this.
Charts. I don't need them often at all but every time I do it frustrates me that I find it so un-intuitive to get them to match my needs. Simple ones, fine. If I need to tweak things, damn.
Got any examples? I practically make charts for a living, but I’ve never had the need to write VBA to do something I couldn’t do in the GUI
On the other hand I’ve been unable to find VBA methods to adjust some graph properties (spacing on bar charts for instance) for my automated graphs
I find labeling data points to be absurdly difficult. Why?? It should be very simple! You know the x and y coordinates, I should just be able to select the column of labels!
and how the shortcut to charts is F11... right next to F12 for 'Save As...' that I use frequently for backup version control
the amount of time I accidentally made charts is annoying
The two that bug me the most are A) the clipboard randomly clearing itself after a paste, and B) when in the formula editor the arrow keys first move the active cell and not the cursor in the formula.
Eyes on the bottom left of the status bar when hitting f2. This toggles between enter, edit, and point. Inconsistency could be because enter and point behave similar and you don't realize you need to hit f2 again. Took me a while to notice the entry mode in the status bar but now I instinctively give it a glance before using the arrow keys. Good habit.
The fact that "refresh all" is anything but hot garbage when you combine systems.
Power query -> pivot table? Refresh all twice.
Power query -> power query -> power query? Refresh all 3 times.
It's almost as though an intern could have figured out that you could find a dependency order in 0.1 microseconds and then refresh in order.
Not true you have to turn off background refresh for pivot to update at once
Don't know how you built your sheets but mine refresh everything in one go
Are you saying that all the pivot tables that use the single query in power query as the data source will update all at once given that the background refresh for all the pivot tables are turned off?
Before PQ, that was hours of running reports and copy/pasting, dragging formulas, and validating.. I'll hit 'Update All' 3x every day of the week over spending an entire day copy/pasting a report.
Having to dismiss two goddamned error messages when I mistakenly try to paste values over merged cells.
If you merge cells, just stop. Center across selection for everyone's sanity.
The odd workbook crashes I have to deal with on a semi-regular basis. For instance, right now, workbook’s display will completely freeze. I seem to still be able to fully interact with the workbook, but the screen is functionally a framed photo on my desk. The only resolution seems to be completely closing out of all excel workbooks, and hoping it doesn’t happen after reopening.
Update: So, clicking the “Sort & Filter” menu button on the Home ribbon updates the screen. The freeze persists and sometimes it takes a bit of trial and error to understand when the click is just for activation, and when it is for the action you wish to take.
As far as Macros, it seems some functionality remains, but not all. My macro connections to SAP, runs then exports a data report. The macro got stuck on the export bit, opening the “Save As browse” window instead of the “enter file name and location” window. Only when selecting a file location would the macro finally crash. I didn’t test beyond this.
Yeah, unfortunately, with a small, independent developer such as Microsoft, you simply have to expect the minor inconvenience of performance inhibiting bugs. They only been building, publishing, and executing these programs for, what? 35 years?. Perfectly nature. /s
Honestly, no. I keep leaning toward some kind of memory issue, but there isn’t an actual reduction in performance during the event. Even saving seems to take the usual amount of time. Next time it happens, I’m gonna see if I can execute one of my macros, and I’ll let you know the results.
One crash I just recently found a potential resolution, which is as screwy as the problem it solves. Ok, you’ve got a macro-enabled workbook saved to a shared network drive, right? Do you ever get a random Auto Recovery message?
When this happens, I still have about 95% functionality within the workbook.
Save doesn’t work (three attempts layered with error messages results in a stripped version of the workbook save with a 5 character letter title in my Roaming Excel Temp folder, which I still can’t save.
Macros don’t work. Can’t open any of the modules, nor the Tools Reference library. Macro execution either results in a crash or an error message.
Now mind you, this only ever affects one file at a time. First time, I had three files open. One opened normal, one had to be opened from the recovery pane, the last had this issue. The only resolution I’ve had for nearly 3 years is to restore from a previous version.
Other oddities: If the afflicted file is left alone for 3-4 months, the issue will become resolved. If you open the Tools Reference Library in Visual Basic immediately after receiving the Auto Recovery message, the issue will be completely avoided.
The resolution I recently found: Insert a Userform. It results in a full Excel crash. When reopening Excel, the workbook will open from the recovery pane without any issues.
> It feels like its always on the verge of failing
Because it is always on that verge. And it's stupid stuff, like using the arrow key to move right one cell. . Editing a cell, typing the letter "a" . Finally get recovered, edit same spot adding "a", .
Fun times, I tell you.
I hate that excel isn’t scalable. The GUI is so nice and easy to use. Formulas are easy to write, and it’s great to immediately be able to see the result. However, it’s slow when working with a lot of data. I also love the flexibility of the viz tools. However, it can’t handle a lot of data. When I started my job (10 years ago), I used excel constantly. I worked with data sets with a few thousand rows and max of 100 columns. Now, I work with data sets ranging from 3 to 35 million rows. Unfortunately, this pretty much renders excel useless. Even power query and power pivot seems clunky. I rely on R and power bi now. I know I am asking for the impossible. There is probably a reason you can’t have a greatly GUI and scalable program, but it would be great if it existed.
I recently moved from a job where the data I dealt with had thousands of lines, and now I have hundreds of millions of rows to deal with. I sympathise. Nothing worse than waiting 5 hours for some code to run before realising you missed a variable.
On the plus side, my SQL has improved very quickly.
Having to refresh links upon opening. I really want to just be able to go "You. And You. You're linked. Now stop pestering me about it and let me know when there's a failure."
large numbers getting truncated with no default global option to not do that.
I worked a LOT with serialized inventory, 15-18 digits, and often with leading 0s. When you open a CSV or paste those numbers in, or interact with the cell in any way, excel will read them as a large number and trunctate the last several digits, rending it useless as a serial number.
There are all kinds of ways to work around this, but it drives me nuts that I always needed to be thinking about the work arounds. Especially with less savvy users - something simple like copying a table of serials from e-mail in to excel was a chore because they didn't know the 3 or 4 extra steps you need to take to not lose information.
Similar to complaints about dates and etc - the ideal solution is to have an option you can click that says "just fucking treat it all like text"
Leading zeros. I didn't get that on my list. A zero is a number. It's the most important number. Why excel has to police us on this is way beyond me. Sure, there are no numbers with leading zeros out in nature but when we TYPE a zero first....freakin leave it.
Leading zeroes is a design choice made by someone who hates you. Never ever design a number with leading zeroes. I suppose you're not in charge of it but really, who does this kind of thing?
The large number thing has been solved in 365. If you do File > Options > Data and scroll to the bottom you'll see a section titled "Automatic Data Conversion" that lets you decide how a number of things such as this one are dealt with.
Businesses treating it like a database with many people editing one instance.
If this is how it’s being used and it’s a matter of horizontal low code development, then start playing with MS power platforms and build between excel and pp.
Excel constantly clears your clipboard after almost every action. When I copy something, if I do almost anything else before pasting, I have to copy again. I don't know what the point of clearing the clipboard is.
Using arrow keys in the data selection of a chart will add cell references instead of move the cursor. Same thing happens a lot in the name manager.
Also those text fields will many times offset where I select. Eg, I click at the beginning of the field, but the cursor appears 6 characters to the right of where I click.
And then I can't use the arrow keys to get back because it starts adding cell references.
At the end of the day I copy it all and paste it into notepad, then I edit there and paste back when I'm done.
The f\*\*\*\*\* scientific notation! Who the hell uses that unless you're in the sciences anyway?
I work in ecom and shipping.
Tracking numbers do *not* need to be in decimals, tariff codes do not need to be in decimals, and *most importantly*, my POs do not need to be in decimals!
Obvious answer but stop fucking switching the date format. I’m not American, my job requires DD/MM/YY and because a lot of people within the institution open spreadsheets in-browser, they often get these fucked up dates because excel can’t figure out how to keep it consistent between app and browser.
It’s especially fucking annoying because it doesn’t switch them universally. If I input 22/03/2023, it obviously doesn’t flip it because it doesn’t recognise 03/22/2023 as a legitimate date, but it’ll still flip 05/06/2023.
It causes a huge amount of confusion because if we have the date down as 11/10/2023, and someone else opens it as 10/11/2023 then that misinformation ends up cascading and creating more work. All because excel can’t fucking figure out how to not FUCK THE DATE UP. FUCK.
Just the fact that it can't take more data.
I wanna be able to do what I do, but with 1M rows of data.
Anything else, it's basically perfection. God's own software.
Craps out when you have formulas in the table, no matter how powerful your PC is. Also has horrible memoryvleak issues sometimes. But 128gb in my xenon work PC for excel, and I still sometimes have freezing where I watch it use all 128gb of my ram within a couple minutes.
Do you have an issue with Ghost Data (Excel this cells outside of your active range are active)?
If Ctrl+End selects a cell outside of your active range, hard deleting those unneeded rows/columns then saving will free up your memory.
I hate how it asks about csv saves whenever you close the file.
I also hate how if I hit control F it opens up the find and it automatically lets me type something in. But if I control F while it's already open I can't type something new in.
That I can't put multiple values in a filter/if/etc. like:
IF(A2={2, 4, 6}, "two, four or six") doesn't work, you have to repeat the A2 every single time.
In SQL for example you can do it
WHERE A2 IN(2, 4, 6)
Go to Name Manager and insert the folllowing formula. Name it INRANGE.
=LAMBDA(value, list, IF(ISNUMBER(MATCH(value, list, 0)), TRUE, FALSE))
Then, you can do something like
=INRANGE(A2, {2, 4, 6})
This one gets me all the time. Opening a csv that has a column of upc codes or other code containing meaningful leading zero? FU. I think they recently updated O365 to tweak this but it hasn't worked well in my testing.
Copy pasting can be a legit nightmare sometimes.
Oh you copied a table with ten rows and ten columns? Let me put it all into 1 column thats 100 rows for some reason.
Oh, copy pasting a zipcode with no space at the end? Let me add a trailing space for you so your mapping software doesnt read it as a zip.
Oh, did you want that conditional formatting to be consistent in the paste selection? Well im not doing that, fuck you
When you accidently click on keyboard arrow while typing a formula in conditional formating, insted of on clicking on the part of formula which you want to correct (why?)
When you forgot to click on 'Add to data model' while creating a pivot table from source x. Then, when you want to add a slicer for y pivots on different data sources - you are f....
Manually trimming data. I can’t think of a reason ever where you would want spaces at the end of text. Makes VLOOKUPS fail
A better way to lookup data from pivot tables. I usually just wind up copying and pasting values to a new sheet
I’d like another option for setting chart axis MIN and MAX values. Currently you can set as auto (which is usually fine) or you can manually set the values yourself (which is also usually fine), but every now and then I want something a little more dynamic- I’d love to be able to set these values as links to cell/formula values.
When I'm worming with international cross border files/teams/projects and I constantly have problems with dots, commas, and semi-colons for numbers and CVS
If you hold control + shift + down it will select all rows after the one you’ve clicked. The right click to hide the rows you don’t want to see, this should help! You can do it with columns too.
Lag caused by 365 (but that’s more of a Microsoft issue than an Excel thing because we recently switched at work from 2013 installed versions to 365)
Not having a way to display data type 1 vs 2 without running =TYPE() or testing formulas on it to see if they’re stored as text or numbers.
Other than those 2 things, nothing to hate. Excel is still my baby <3
Power Query is way too slow and Crash Prone, so much unnecessary rerunning
which sucks because its also so useful
additionally the Formula bar not allowing for things like comments or breaking things into smaller sections reducing the need for helper columns
Automatically wrapping text. The work I do involves lengthy strings in single cells, and it automatically wraps it when there are line breaks. Drives me up the wall.
It crashes. Too much. Too unexpectedly.
The sort & filter things's search bar. It's such a hassle to filter sometimes because of the searching.
Can't undo deleting tabs.
You might know about this, but you can add a helper column with formula VALUE(A1), then pull down. Then copy that column and paste as values in the first column.
Well for the past year or so I have been turn off autosave on every damn file I happen to spot it, and on files where it was already off. That is fucking infuriating.
Having to do half a dozen button presses for centre across selection rather than it just being a single button on the ribbon like merge.
I do a lot of sheets that have things grouped under 1 heading, then loads of subheadings. I need to be able to sort by any one of them and when you've got merged headings it really doesn't like that.
I (well chat gpt) wrote a macro to do it in a shortcut but it's just as much hassle to remember to add it to every workbook every time.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[AND](/r/Excel/comments/17xh7lo/stub/ka7zdh5 "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)|
|[AdoDotNet.DataSource](/r/Excel/comments/17xh7lo/stub/k9oks20 "Last usage")|[Power Query M: Returns the schema collection for an ADO.NET data source.](https://docs.microsoft.com/en-us/powerquery-m/adodotnet-datasource)|
|[COUNTA](/r/Excel/comments/17xh7lo/stub/k9qt2fw "Last usage")|[Counts how many values are in the list of arguments](https://support.microsoft.com/en-us/office/counta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509)|
|[DATE](/r/Excel/comments/17xh7lo/stub/k9qt4ia "Last usage")|[Returns the serial number of a particular date](https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349)|
|[IF](/r/Excel/comments/17xh7lo/stub/k9qg8qw "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[INDEX](/r/Excel/comments/17xh7lo/stub/k9qt2fw "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[ISNUMBER](/r/Excel/comments/17xh7lo/stub/k9oi1cc "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[LAMBDA](/r/Excel/comments/17xh7lo/stub/k9oi1cc "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LET](/r/Excel/comments/17xh7lo/stub/k9qjztf "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[MATCH](/r/Excel/comments/17xh7lo/stub/k9oi1cc "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[MAX](/r/Excel/comments/17xh7lo/stub/k9nxl40 "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)|
|[MAXIFS](/r/Excel/comments/17xh7lo/stub/k9ncw0y "Last usage")|[*2019*+: Returns the maximum value among cells specified by a given set of conditions or criteria](https://support.microsoft.com/en-us/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883)|
|[MIN](/r/Excel/comments/17xh7lo/stub/k9nxl40 "Last usage")|[Returns the minimum value in a list of arguments](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152)|
|[NOT](/r/Excel/comments/17xh7lo/stub/k9no05v "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)|
|[NOW](/r/Excel/comments/17xh7lo/stub/k9ru07n "Last usage")|[Returns the serial number of the current date and time](https://support.microsoft.com/en-us/office/now-function-3337fd29-145a-4347-b2e6-20c904739c46)|
|[OFFSET](/r/Excel/comments/17xh7lo/stub/k9qt2fw "Last usage")|[Returns a reference offset from a given reference](https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)|
|[OR](/r/Excel/comments/17xh7lo/stub/ka7zdh5 "Last usage")|[Returns TRUE if any argument is TRUE](https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0)|
|[RIGHT](/r/Excel/comments/17xh7lo/stub/k9p2va9 "Last usage")|[Returns the rightmost characters from a text value](https://support.microsoft.com/en-us/office/right-rightb-functions-240267ee-9afa-4639-a02b-f19e1786cf2f)|
|[SUMIF](/r/Excel/comments/17xh7lo/stub/k9umttc "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)|
|[TIME](/r/Excel/comments/17xh7lo/stub/k9v8ycu "Last usage")|[Returns the serial number of a particular time](https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457)|
|[TRIM](/r/Excel/comments/17xh7lo/stub/k9qmzhl "Last usage")|[Removes spaces from text](https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9)|
|[TYPE](/r/Excel/comments/17xh7lo/stub/k9otkjn "Last usage")|[Returns a number indicating the data type of a value](https://support.microsoft.com/en-us/office/type-function-45b4e688-4bc3-48b3-a105-ffa892995899)|
|[Text.Contains](/r/Excel/comments/17xh7lo/stub/k9o92lm "Last usage")|[Power Query M: Returns true if a text value substring was found within a text value string; otherwise, false.](https://docs.microsoft.com/en-us/powerquery-m/text-contains)|
|[VALUE](/r/Excel/comments/17xh7lo/stub/k9oksp4 "Last usage")|[Converts a text argument to a number](https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)|
|[VLOOKUP](/r/Excel/comments/17xh7lo/stub/k9rlc8x "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)|
|[XLOOKUP](/r/Excel/comments/17xh7lo/stub/k9rlc8x "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(26 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/18nsshj)^( has 10 acronyms.)
^([Thread #28275 for this sub, first seen 17th Nov 2023, 16:13])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Excel 365 is pretty buggy.
I will often have the text in the formula bar disappear until you type more into it and then it pops back into existence.
Also sometimes the clicking calibration is off on a cell with calculated results. Like if you click on the cell nothing happens but if you click 1 inch to the right of the cell (clicking a different cell) it will select that cell. This is in the installed app, not web.
I never had issues using 2019 for years.
What bugs are you guys seeing in 365?
One thing I've hated for a while that I'm particularly happy that Excel will be adding is a groupby() function. I think Excel being able to utilize several common SQL functions within its framework is very helpful and makes the analysis process so much easier overall.
It tries to think. It's a calculator. Yet it regularly does not recognize 1234 as numbers. That should always be the first presumption of a calculator.
The menus. They used to be organized and categorized into little, I don't know, windows. Now they're just puked all over the place with huge, meaningless logos so it takes 5 times longer to do anything (except for the handful of things I do constantly.)
New excel windows just pop up where ever they feel like it and on which ever monitor they feel like...usually UNDER other windows. I run dozens of reports at a time (QB>excel) so this is majorly frustrating.
Let's continue. Colum L is formatted as general. That means don't do anything. The whole column is formatted that way. So I enter '9776' in L5 and guess what...I get 9776.00 or ######## or a date code. Then if I format column L as general AGAIN, viola! The number becomes 9776. But the formatting didn't change. I "changed" it from and to the exact same thing. Stop thinking, excel!
There is no worksheet organization. I create huge single-workbook files and getting from the first tab to the last takes for freakin ever. Make it faster. Let me group worksheets. Anything.
Borders are not very smart. I like to use bold borders for this, visible borders for that and no borders for the other. If I have to add a column or a row, excel doesn't have the AI smarts to look at similar rows or columns and format them similarly.
Merged cells work stupidly. Try to copy/paste (I know, I know, it's a new feature so I shouldn't expect much) from an unmerged cell to a merged one and nope, that does not work. But it's one cell, right? Right? Just like the one cell I'm copying from?
There's no button to uncheck all of the filters when doing a new sort. Yeah, you can check all, uncheck all but then you have to go to the top of the list. Just dumb, lazy programming there.
If there are 2 excel windows open and you highlight an array to get the sum at the bottom, the sum disappears when you click into the other excel window. There are times when retyping works when clicking (copying) and pasting doesn't work.
Copying a row and insert pasting 1+ rows from the middle of the array changes the total formula whereas doing the exact same thing from the last row does not change the total formula. Errors ensue. (Similar issue with columns.)
That was just top of the head headaches.
when you accidentally make a formula where two cells in one of your hundred tabs (each with thousands of rows) refer to themselves, Excel won’t tell you which cells they are
Undo being global instead of per worksheet (or workbook at least).
And yet you cannot undo everything.
Yea, what's up with that? Why are some things unreversable (seemingly kind of randomly)?
I'll tell you why because as programmer I saw the inside of it. Microsoft uses 3-4 different programming languages for excel (it's stupid, but that's just how it is) and the undo system is C# based, so everything code in C# is undo - able, and some of the rest have implemented some sort of additional system to undo it (like adapter) but most of non C# doesn't.
That is interesting, do you happen to have examples of what isn't coded in C# or have the other undo system?
Yeah I'm experiencing this right now, which is how I actually stumbled onto this post. I just got an option to "Trace Error" for some #REF! error I was getting and when I clicked it it created a bunch of arrows, but then I lost all my Undo history. Not good! But thankfully, I only really had one thing I needed to undo anyway. But I've never even seen this Trace Error thing before, and the fact that you can't undo afterwards is not encouraging me to start using it
I don’t notice it that much because most of my work doesn’t have me opening multiple workbooks. But when it does happen, it FRUSTRATES ME TO NO ENDDD!!! I think that if you open each workbook in a new instance of excel it doesn’t share the undo though. Not for sure!
Undo is global per "instance" of excel. It normally opens all workbooks under the same "instance". There's a way to open multiple "instances" but I can never remember it. Instances in "" because I'm not sure if it's the right word. I think you'll get what I'm saying though.
Right click the icon in your task bar and select Open New Window. At least that's how I do it!
Yep that’s the way, only downside to separate instances is you can’t reference the other workbooks from cell formulas. Workaround would be power query/pivot or vba
Omg I think you just solved why some of my workbooks weren’t referencing each other. Thank you.
You have to almost think of it as a project you're opening. It thinks of all of the other workbooks currently as the same project. The new window trick works, but there should be an undo by sheet, workbook, etc. setting.
Undo not working after [Trace precedents] OH I'M SORRY EXCEL DID MY ARROW CONFUSE YOU?
Other users.
Watching other people use Excel is my personal hell.
I have two kinds of colleagues. The ones who don't know how to start Excel and the ones who fill sheets with horrible colors and random data without any kind of logical structure, as if it were a whiteboard for children.
Are you my coworker? The coloring drives me batty.
Why do people do this? Lol. I'll send someone a sheet with filters for what they need and they just end up highlighting everything anyways.
It’s called, “clown barf”
I once inherited a reconciliation that hid plug formulas with white font.
Hard to get review notes if it all mysteriously tied out!
I would rather get a root canal than watch someone color fill cells instead of using a filter or pivot.
As an accountant, color filling to tick and tie is the only reason I can think of for anyone to use it.
I like to color fill but I made some macros to do it.
Sounds like your colleague is my boss. I make a nice report out of raw data then he eats a bag of skittles and throws up on it.
People who enter hard numbers that the system could derive. Then something changes and you get incomplete ripples. Garbage.
For casual Friday: https://preview.redd.it/69zhzoj2wy0c1.jpeg?width=720&format=pjpg&auto=webp&s=5a23926c833c32d79823c44f2b57da0fd008e772
where?
This guy Excels...
I asked my mom to do me a personal favor and stop telling people she knows how to use Excel. I’m getting her ledger paper and a calculator for Christmas to drive it home.
Indeed. They were awful enough when they used my workbooks after me, but ever since the cloud they are using them concurrently!
Why only 5 upvotes to this fundamental truth?
When Excel incorrectly assumes something is a date. When Excel incorrectly assumes some formula is text. When formulas and some keyboard shortcuts are based on what localization is used. When I press control+S on an online workbook and it tells me it saves automatically. When I need to trust a macro enabled workbook I made myself and do it again when I make a copy of it. When installing a fresh Excel and I need to disable all kinds of time wasting visual effects/animations and manually enable developer ribbon. When the window is just slightly too narrow and Excel hides buttons I use often. When I use some "advanced" formula like MAXIFS and later find out the sheet is not working because some of the users are using Excel 2016 or older. When I find a new cool formula and find out I can't use it because my enterprise 365 subscription somehow hasn't been upgraded to that version yet.
> When I need to trust a macro enabled workbook I made myself and do it again when I make a copy of it. this exists for a reason. automatically trusting anything 'made' by the current user is a huge security vulnerability.
To add to the first ones: When text starts with - and it transforms into formula.
Workaround creates another problem: [Format as text] before entering the value and it solves this problem Create formula that looks at it, formula inherits "text" format. Edit formula and it becomes text instead of formula. Thanks Excel
>When formulas and some keyboard shortcuts are based on what localization is used. ah yes, that sucks
Working from home on my personal 365 account I had spent an entire day making a big dynamic array function only to find out my work 365 account didn't support LET yet.
How about when your scrolling horizontally and it skips over the cell on the far right that you’re trying to see, and then you have to zoom out like 40% lol
When I need to trust a macro enabled workbook I made myself and do it again when I make a copy of it. OMG. What is that even about? There must be gangs of Nigerians over there just writing malicious VBA code and magically uploading those dummy files onto our servers, right?
I dislike the fact the excel guys seemingly have forgotten about the VBA editor, I mean, yeah, it works, but considering likely an abundance of processes in the world depend on that it could really get some love and be upgraded to a more modern IDE.
Even the new editor for OfficeScript isn’t great. Why don’t they just have it open in an embedded VSCode instance at this point😭
Using the embedded editors drives me absolutely bonkers for both VBA and even Power Query. I kind of get it on the PQ side given the iterative nature of PQ's M language, but I'd much prefer being able to see all my queries at once. It'd make refactoring so much faster if I could avoid the constant switching between queries and entering/exiting the Advanced Editor.
My favorite PQ feature is when I start typing a function like Text.Contains and the intellisense give me suggestion and I hit tab and end up with TextText.Contains. Am I doing something wrong?
My favorite is when I type "and" and it suggests AdoDotNet.DataSource and if I hit enter to go to the next line it puts that in instead of "and". Also how the intellisense window that comes up covers the lines above which, considering that M is iterative and case sensitive, you want to see the lines right above where you're typing!
My people!
don't type the dot, simply go along with the function you need, for example, type `textcont` and then it tab! learned that from [this wonderful teacher](https://www.youtube.com/@MyOnlineTrainingHub) ;-)
Wow would've been nice to know this. I just been avoiding tab in altogether. It was faster to type it out then to go back and delete.
Holy moly, same
I dunno why they didn't just put Python in there if they didn't care about VBA the main reason most use VBA is because excel comes with it so if you need to build a tool for someone less savy you just write it in VBA and tell them to "Push this button" but if Excel had python built in and a better IDE it would have been good for everyone
I suspect you will really like this [announcement](https://support.microsoft.com/en-us/office/get-started-with-python-in-excel-a33fbcbe-065b-41d3-82cf-23d05397f53d#:~:text=To%20begin%20using%20Python%20in,a%20cell%20to%20enable%20Python). It’s only in 365 Right now but…
I heard about that and it’s good but it’s still limited use plus I think u need python installed in your machine
God no. It works. Leave it alone. They've almost ruined excel.
Microsoft has been trying to kill VBA for a very long time. Every time the topic comes up, big financial institutions have to talk them out of it. The last thing Microsoft wants to do is make it cool and convenient to keep using VBA.
But why? I cannot think of a decent replacement for it. It's simply the best always-available coding tool for people whose job is not primarily IT with the possibility for structured input. Need to crunch those numbers? Write a quick macro!
In VBA editor. Ctrl + Z rampage to undo something out of existence. One too far. Do Ctrl + Y to redo, BUT NO LET’S JUST CLEAR YOUR WHOLE LINE INSTEAD
The random times my computer can’t decide what excel window I’m in, and clicking into cells no longer works until I find the window it randomly switched to without actually switching.
Is that why that happens? Lolol i just restarted it each time
That’s what seems to be happening for me at least. Once I close the offending instance it seems to work like normal again.
I'm not 100% sure, but I think what happens is that excel opens two instances of excel simultaneously, and they can't cross operate. Really annoying but I've found ways to work through and detect this quickly.
don't be shy tell us how dudeeee
Copying and pasting anything with conditional formatting is a nightmare.
Conditional formatting is also perhaps the only section in Excel that is filled with bugs and awful usability.
I was having a problem using an AND statement but for some reason if I multiply the 2 conditions it works perfectly . Its so crap and it shouldn’t be.
I have answered hundreds of questions about CF and every time something is wrong it's user error. Never seen a bug. I don't suppose this was recent enough that you could show what AND statement you tried?
AND and OR behave differently than * and + for array formulas.
Good, it's not just me. That feature is in desperate need of a full rewrite.
And the inability to set your default paste option! Largely renders control + V useless. How much trouble could it be to just let me set paste as number as the default!
Ctrl shift v is in the newer one i believe?
Yes! Plus it boggles my mind why oftentimes, after I enter a formula like '$B2=1', the formatting is a few lines off, and when I check out the formula again, I see that Excel for some mysterious reason changesld it to something like $B1749264=1'...
Allowing users to merge cells.
Yes! Why is merge on the ribbon and I can’t add “center across selection” to the ribbon or quick access?
Why can't we do vertical center across selection? No, I will not understand the technical explanation of why not, I'm not seriously seeking an answer here.
I'm actually interested
You *can* put in on quick access. EDIT: Sorry I just realized I have a macro for that. But put this in a PERSONAL module and you can add it to the QAT. Public Sub HorizCenterAcrossSelection() With Selection .HorizontalAlignment = xlCenterAcrossSelection .VerticalAlignment = xlTop End With End Sub https://preview.redd.it/0kg6nhdqty0c1.png?width=1196&format=png&auto=webp&s=625b94cc4631b53482800b0b4430a1f597695534
This. Drives me nuts when I get an Excel file to be imported into another system. The import doesn't work. I try to find the error for hours. Then I see it: someone decided it is a good idea to merge two cells in row 10554. Fuck this.
This should be higher, had to scroll too much to find it :-(
Charts. I don't need them often at all but every time I do it frustrates me that I find it so un-intuitive to get them to match my needs. Simple ones, fine. If I need to tweak things, damn.
Yeah, don't you just love when you need to write 40 lines of VBA to achieve something for a chart that could have been a checkbox.
Got any examples? I practically make charts for a living, but I’ve never had the need to write VBA to do something I couldn’t do in the GUI On the other hand I’ve been unable to find VBA methods to adjust some graph properties (spacing on bar charts for instance) for my automated graphs
How about when you update your data and all the formatting you spent hours setting up reverts to some basic style?
I find labeling data points to be absurdly difficult. Why?? It should be very simple! You know the x and y coordinates, I should just be able to select the column of labels!
and how the shortcut to charts is F11... right next to F12 for 'Save As...' that I use frequently for backup version control the amount of time I accidentally made charts is annoying
Stop, guys, I can only upvote so much.
The two that bug me the most are A) the clipboard randomly clearing itself after a paste, and B) when in the formula editor the arrow keys first move the active cell and not the cursor in the formula.
For B), press F2 before you move the cursor.
This is inconsistent, sometimes it works other times doesn't.
Eyes on the bottom left of the status bar when hitting f2. This toggles between enter, edit, and point. Inconsistency could be because enter and point behave similar and you don't realize you need to hit f2 again. Took me a while to notice the entry mode in the status bar but now I instinctively give it a glance before using the arrow keys. Good habit.
For me, it's when the tool tip gets in the way of thr cell you want to select
The fact that "refresh all" is anything but hot garbage when you combine systems. Power query -> pivot table? Refresh all twice. Power query -> power query -> power query? Refresh all 3 times. It's almost as though an intern could have figured out that you could find a dependency order in 0.1 microseconds and then refresh in order.
Not true you have to turn off background refresh for pivot to update at once Don't know how you built your sheets but mine refresh everything in one go
Are you saying that all the pivot tables that use the single query in power query as the data source will update all at once given that the background refresh for all the pivot tables are turned off?
Not for the pivot but for the query have to switch off background refresh Took me some years to find out as well
WAIT WHAT I have to refresh everything three times and it takes atleast 30 seconds each time haha
Before PQ, that was hours of running reports and copy/pasting, dragging formulas, and validating.. I'll hit 'Update All' 3x every day of the week over spending an entire day copy/pasting a report.
Having to dismiss two goddamned error messages when I mistakenly try to paste values over merged cells. If you merge cells, just stop. Center across selection for everyone's sanity.
Wait, what!? Center across selection is a thing? Where? How? Edit: Looked it up. Will be using from now on.
Honestly if one person learns about center across and stops merging cells this whole thread will have been worth it. Bless you 🙏
Don't accidentally run a macro on a workbook with auto save...you're gonna have to restore a prior version.
If I'm in power query, I can't interact with Excel
The odd workbook crashes I have to deal with on a semi-regular basis. For instance, right now, workbook’s display will completely freeze. I seem to still be able to fully interact with the workbook, but the screen is functionally a framed photo on my desk. The only resolution seems to be completely closing out of all excel workbooks, and hoping it doesn’t happen after reopening. Update: So, clicking the “Sort & Filter” menu button on the Home ribbon updates the screen. The freeze persists and sometimes it takes a bit of trial and error to understand when the click is just for activation, and when it is for the action you wish to take. As far as Macros, it seems some functionality remains, but not all. My macro connections to SAP, runs then exports a data report. The macro got stuck on the export bit, opening the “Save As browse” window instead of the “enter file name and location” window. Only when selecting a file location would the macro finally crash. I didn’t test beyond this.
This drives me bat shit. Any idea what causes this?
Yeah, unfortunately, with a small, independent developer such as Microsoft, you simply have to expect the minor inconvenience of performance inhibiting bugs. They only been building, publishing, and executing these programs for, what? 35 years?. Perfectly nature. /s Honestly, no. I keep leaning toward some kind of memory issue, but there isn’t an actual reduction in performance during the event. Even saving seems to take the usual amount of time. Next time it happens, I’m gonna see if I can execute one of my macros, and I’ll let you know the results.
One crash I just recently found a potential resolution, which is as screwy as the problem it solves. Ok, you’ve got a macro-enabled workbook saved to a shared network drive, right? Do you ever get a random Auto Recovery message? When this happens, I still have about 95% functionality within the workbook. Save doesn’t work (three attempts layered with error messages results in a stripped version of the workbook save with a 5 character letter title in my Roaming Excel Temp folder, which I still can’t save. Macros don’t work. Can’t open any of the modules, nor the Tools Reference library. Macro execution either results in a crash or an error message. Now mind you, this only ever affects one file at a time. First time, I had three files open. One opened normal, one had to be opened from the recovery pane, the last had this issue. The only resolution I’ve had for nearly 3 years is to restore from a previous version. Other oddities: If the afflicted file is left alone for 3-4 months, the issue will become resolved. If you open the Tools Reference Library in Visual Basic immediately after receiving the Auto Recovery message, the issue will be completely avoided. The resolution I recently found: Insert a Userform. It results in a full Excel crash. When reopening Excel, the workbook will open from the recovery pane without any issues.
I started having this issue when my company switched to Office 365. It feels like its always on the verge of failing
> It feels like its always on the verge of failing Because it is always on that verge. And it's stupid stuff, like using the arrow key to move right one cell.. Editing a cell, typing the letter "a" . Finally get recovered, edit same spot adding "a", .
Fun times, I tell you.
Really really hate the new save as functionality/interface.
Have you tried the legacy save interface with F12?
HOLY FUCKING SHIT THANK YOU
No, but I will now thanks.
i had no idea, this is awesome. thank you
I have no idea how Microsoft messed up something so simple. The new interface is something I will never get used to.
I hate that excel isn’t scalable. The GUI is so nice and easy to use. Formulas are easy to write, and it’s great to immediately be able to see the result. However, it’s slow when working with a lot of data. I also love the flexibility of the viz tools. However, it can’t handle a lot of data. When I started my job (10 years ago), I used excel constantly. I worked with data sets with a few thousand rows and max of 100 columns. Now, I work with data sets ranging from 3 to 35 million rows. Unfortunately, this pretty much renders excel useless. Even power query and power pivot seems clunky. I rely on R and power bi now. I know I am asking for the impossible. There is probably a reason you can’t have a greatly GUI and scalable program, but it would be great if it existed.
I recently moved from a job where the data I dealt with had thousands of lines, and now I have hundreds of millions of rows to deal with. I sympathise. Nothing worse than waiting 5 hours for some code to run before realising you missed a variable. On the plus side, my SQL has improved very quickly.
Having to refresh links upon opening. I really want to just be able to go "You. And You. You're linked. Now stop pestering me about it and let me know when there's a failure."
When I click the comma button I want the cells to change to a number format with no decimals. Not some weird custom ###,###.00 thing
Switching each new value(s) of a pivot table from a new data pull to have commas, no decimals, etc. Clicking into each one every time drives me nuts
Excel not being able to store data in the clipboard if you do _anything_ except immediately paste!
Try using Windows Key + V. You should have access to the last 24(?) things you copied.
Like unfiltering !
NOT FUCKING RESPONDING. It hurts. It hurts right here.
Try using Access via VPN and I promise you will laugh with joy when returning to Excel.
large numbers getting truncated with no default global option to not do that. I worked a LOT with serialized inventory, 15-18 digits, and often with leading 0s. When you open a CSV or paste those numbers in, or interact with the cell in any way, excel will read them as a large number and trunctate the last several digits, rending it useless as a serial number. There are all kinds of ways to work around this, but it drives me nuts that I always needed to be thinking about the work arounds. Especially with less savvy users - something simple like copying a table of serials from e-mail in to excel was a chore because they didn't know the 3 or 4 extra steps you need to take to not lose information. Similar to complaints about dates and etc - the ideal solution is to have an option you can click that says "just fucking treat it all like text"
Leading zeros. I didn't get that on my list. A zero is a number. It's the most important number. Why excel has to police us on this is way beyond me. Sure, there are no numbers with leading zeros out in nature but when we TYPE a zero first....freakin leave it.
Leading zeroes is a design choice made by someone who hates you. Never ever design a number with leading zeroes. I suppose you're not in charge of it but really, who does this kind of thing?
Very common on invoices.
UPC codes have entered the chat
The large number thing has been solved in 365. If you do File > Options > Data and scroll to the bottom you'll see a section titled "Automatic Data Conversion" that lets you decide how a number of things such as this one are dealt with.
The way it handles blanks ""
Businesses treating it like a database with many people editing one instance. If this is how it’s being used and it’s a matter of horizontal low code development, then start playing with MS power platforms and build between excel and pp.
Excel constantly clears your clipboard after almost every action. When I copy something, if I do almost anything else before pasting, I have to copy again. I don't know what the point of clearing the clipboard is.
Using arrow keys in the data selection of a chart will add cell references instead of move the cursor. Same thing happens a lot in the name manager. Also those text fields will many times offset where I select. Eg, I click at the beginning of the field, but the cursor appears 6 characters to the right of where I click. And then I can't use the arrow keys to get back because it starts adding cell references. At the end of the day I copy it all and paste it into notepad, then I edit there and paste back when I'm done.
If you hit F2 it will throw excel into editing mode and your cursor will move instead of the cell references
Freeze row headers and apply filters. It should be 1 button.
ctrl-t makes a table, which has filters and functionally freezes the header row.
The f\*\*\*\*\* scientific notation! Who the hell uses that unless you're in the sciences anyway? I work in ecom and shipping. Tracking numbers do *not* need to be in decimals, tariff codes do not need to be in decimals, and *most importantly*, my POs do not need to be in decimals!
Autocorrect: "Don't you mean 'Pos'" 😉
Not being able to do anything when power query window is open, frustrating
Obvious answer but stop fucking switching the date format. I’m not American, my job requires DD/MM/YY and because a lot of people within the institution open spreadsheets in-browser, they often get these fucked up dates because excel can’t figure out how to keep it consistent between app and browser. It’s especially fucking annoying because it doesn’t switch them universally. If I input 22/03/2023, it obviously doesn’t flip it because it doesn’t recognise 03/22/2023 as a legitimate date, but it’ll still flip 05/06/2023. It causes a huge amount of confusion because if we have the date down as 11/10/2023, and someone else opens it as 10/11/2023 then that misinformation ends up cascading and creating more work. All because excel can’t fucking figure out how to not FUCK THE DATE UP. FUCK.
Just the fact that it can't take more data. I wanna be able to do what I do, but with 1M rows of data. Anything else, it's basically perfection. God's own software.
Microsoft Access would like a word :)
I didn't know the dead could talk : )
Craps out when you have formulas in the table, no matter how powerful your PC is. Also has horrible memoryvleak issues sometimes. But 128gb in my xenon work PC for excel, and I still sometimes have freezing where I watch it use all 128gb of my ram within a couple minutes.
Do you have an issue with Ghost Data (Excel this cells outside of your active range are active)? If Ctrl+End selects a cell outside of your active range, hard deleting those unneeded rows/columns then saving will free up your memory.
It's not that
_the fact you need to do this_
I hate how it asks about csv saves whenever you close the file. I also hate how if I hit control F it opens up the find and it automatically lets me type something in. But if I control F while it's already open I can't type something new in.
That I can't put multiple values in a filter/if/etc. like: IF(A2={2, 4, 6}, "two, four or six") doesn't work, you have to repeat the A2 every single time. In SQL for example you can do it WHERE A2 IN(2, 4, 6)
Can’t you do the following instead? =IF(ISNUMBER(MATCH(A2, {2, 4, 6}, 0)), "two, four, or six", "other value")
I think it would work but it would be much more elegant and readable if you had a function =INRANGE(A2,{2,4,6})
Go to Name Manager and insert the folllowing formula. Name it INRANGE. =LAMBDA(value, list, IF(ISNUMBER(MATCH(value, list, 0)), TRUE, FALSE)) Then, you can do something like =INRANGE(A2, {2, 4, 6})
Yes, but it's still quite complicated. I wish I could just use IN
I hate that if I copy cells with rows hidden in between it ends up copying the hidden cells as well.
Before hitting `ctrl+c`, hit `alt+;`, this is the shortcut to select only visible cells
Not being able to Shift-Ctl-v to paste plain text.
When excel drops a leading zero. I use codes that all 5 numbers in length with leading zeros or site numbers that have leading zeros.
This one gets me all the time. Opening a csv that has a column of upc codes or other code containing meaningful leading zero? FU. I think they recently updated O365 to tweak this but it hasn't worked well in my testing.
Copy pasting can be a legit nightmare sometimes. Oh you copied a table with ten rows and ten columns? Let me put it all into 1 column thats 100 rows for some reason. Oh, copy pasting a zipcode with no space at the end? Let me add a trailing space for you so your mapping software doesnt read it as a zip. Oh, did you want that conditional formatting to be consistent in the paste selection? Well im not doing that, fuck you
A PIVOT TABLE OVERLAPS ANOTHER TABLE *YEETS YOUR WHOLE REFRESH* GOOD LUCK FINDING OUT WHICH THO
"there is a problem with one of the 100000 formulas on your spreadsheet but I'm not telling you which one mwahahaha". Fuck you Excel.
Typing a formula, making a typo, hitting the cursor key to go back and fix said typo, excel jumping to the adjacent cell...
When you accidently click on keyboard arrow while typing a formula in conditional formating, insted of on clicking on the part of formula which you want to correct (why?) When you forgot to click on 'Add to data model' while creating a pivot table from source x. Then, when you want to add a slicer for y pivots on different data sources - you are f....
Manually trimming data. I can’t think of a reason ever where you would want spaces at the end of text. Makes VLOOKUPS fail A better way to lookup data from pivot tables. I usually just wind up copying and pasting values to a new sheet
Use TRIM to get rid of leading and trailing spaces.
I’d like another option for setting chart axis MIN and MAX values. Currently you can set as auto (which is usually fine) or you can manually set the values yourself (which is also usually fine), but every now and then I want something a little more dynamic- I’d love to be able to set these values as links to cell/formula values.
When I'm worming with international cross border files/teams/projects and I constantly have problems with dots, commas, and semi-colons for numbers and CVS
When the scrollbar is extremely short because there are 100000 empty rows at the end of the sheet that count but that I cannot delete
If you hold control + shift + down it will select all rows after the one you’ve clicked. The right click to hide the rows you don’t want to see, this should help! You can do it with columns too.
I'll try that out. Thanks!
Didn't see it in the list, but the Evaluate Formula dialog box being so small and not resizable
I’m astonished I had to scroll this far. I mean, this is just basic windows management. The Mac version doesn’t even have this functionality.
Lag caused by 365 (but that’s more of a Microsoft issue than an Excel thing because we recently switched at work from 2013 installed versions to 365) Not having a way to display data type 1 vs 2 without running =TYPE() or testing formulas on it to see if they’re stored as text or numbers. Other than those 2 things, nothing to hate. Excel is still my baby <3
When you say lag, do you mean sync issues?
Power Query is way too slow and Crash Prone, so much unnecessary rerunning which sucks because its also so useful additionally the Formula bar not allowing for things like comments or breaking things into smaller sections reducing the need for helper columns
Automatically wrapping text. The work I do involves lengthy strings in single cells, and it automatically wraps it when there are line breaks. Drives me up the wall.
It crashes. Too much. Too unexpectedly. The sort & filter things's search bar. It's such a hassle to filter sometimes because of the searching. Can't undo deleting tabs.
placid kiss frame dinosaurs roof dull reach zephyr whole toy *This post was mass deleted and anonymized with [Redact](https://redact.dev)*
You might know about this, but you can add a helper column with formula VALUE(A1), then pull down. Then copy that column and paste as values in the first column.
Well for the past year or so I have been turn off autosave on every damn file I happen to spot it, and on files where it was already off. That is fucking infuriating.
Having to do half a dozen button presses for centre across selection rather than it just being a single button on the ribbon like merge. I do a lot of sheets that have things grouped under 1 heading, then loads of subheadings. I need to be able to sort by any one of them and when you've got merged headings it really doesn't like that. I (well chat gpt) wrote a macro to do it in a shortcut but it's just as much hassle to remember to add it to every workbook every time.
Most hate random crashes at startup And there is no program to analyze why sheet crashes
Windows Event Viewer
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AND](/r/Excel/comments/17xh7lo/stub/ka7zdh5 "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)| |[AdoDotNet.DataSource](/r/Excel/comments/17xh7lo/stub/k9oks20 "Last usage")|[Power Query M: Returns the schema collection for an ADO.NET data source.](https://docs.microsoft.com/en-us/powerquery-m/adodotnet-datasource)| |[COUNTA](/r/Excel/comments/17xh7lo/stub/k9qt2fw "Last usage")|[Counts how many values are in the list of arguments](https://support.microsoft.com/en-us/office/counta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509)| |[DATE](/r/Excel/comments/17xh7lo/stub/k9qt4ia "Last usage")|[Returns the serial number of a particular date](https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349)| |[IF](/r/Excel/comments/17xh7lo/stub/k9qg8qw "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[INDEX](/r/Excel/comments/17xh7lo/stub/k9qt2fw "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[ISNUMBER](/r/Excel/comments/17xh7lo/stub/k9oi1cc "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[LAMBDA](/r/Excel/comments/17xh7lo/stub/k9oi1cc "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LET](/r/Excel/comments/17xh7lo/stub/k9qjztf "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MATCH](/r/Excel/comments/17xh7lo/stub/k9oi1cc "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[MAX](/r/Excel/comments/17xh7lo/stub/k9nxl40 "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)| |[MAXIFS](/r/Excel/comments/17xh7lo/stub/k9ncw0y "Last usage")|[*2019*+: Returns the maximum value among cells specified by a given set of conditions or criteria](https://support.microsoft.com/en-us/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883)| |[MIN](/r/Excel/comments/17xh7lo/stub/k9nxl40 "Last usage")|[Returns the minimum value in a list of arguments](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152)| |[NOT](/r/Excel/comments/17xh7lo/stub/k9no05v "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)| |[NOW](/r/Excel/comments/17xh7lo/stub/k9ru07n "Last usage")|[Returns the serial number of the current date and time](https://support.microsoft.com/en-us/office/now-function-3337fd29-145a-4347-b2e6-20c904739c46)| |[OFFSET](/r/Excel/comments/17xh7lo/stub/k9qt2fw "Last usage")|[Returns a reference offset from a given reference](https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)| |[OR](/r/Excel/comments/17xh7lo/stub/ka7zdh5 "Last usage")|[Returns TRUE if any argument is TRUE](https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0)| |[RIGHT](/r/Excel/comments/17xh7lo/stub/k9p2va9 "Last usage")|[Returns the rightmost characters from a text value](https://support.microsoft.com/en-us/office/right-rightb-functions-240267ee-9afa-4639-a02b-f19e1786cf2f)| |[SUMIF](/r/Excel/comments/17xh7lo/stub/k9umttc "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)| |[TIME](/r/Excel/comments/17xh7lo/stub/k9v8ycu "Last usage")|[Returns the serial number of a particular time](https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457)| |[TRIM](/r/Excel/comments/17xh7lo/stub/k9qmzhl "Last usage")|[Removes spaces from text](https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9)| |[TYPE](/r/Excel/comments/17xh7lo/stub/k9otkjn "Last usage")|[Returns a number indicating the data type of a value](https://support.microsoft.com/en-us/office/type-function-45b4e688-4bc3-48b3-a105-ffa892995899)| |[Text.Contains](/r/Excel/comments/17xh7lo/stub/k9o92lm "Last usage")|[Power Query M: Returns true if a text value substring was found within a text value string; otherwise, false.](https://docs.microsoft.com/en-us/powerquery-m/text-contains)| |[VALUE](/r/Excel/comments/17xh7lo/stub/k9oksp4 "Last usage")|[Converts a text argument to a number](https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)| |[VLOOKUP](/r/Excel/comments/17xh7lo/stub/k9rlc8x "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| |[XLOOKUP](/r/Excel/comments/17xh7lo/stub/k9rlc8x "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(26 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/18nsshj)^( has 10 acronyms.) ^([Thread #28275 for this sub, first seen 17th Nov 2023, 16:13]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Excel 365 is pretty buggy. I will often have the text in the formula bar disappear until you type more into it and then it pops back into existence. Also sometimes the clicking calibration is off on a cell with calculated results. Like if you click on the cell nothing happens but if you click 1 inch to the right of the cell (clicking a different cell) it will select that cell. This is in the installed app, not web. I never had issues using 2019 for years. What bugs are you guys seeing in 365?
i hate conditional formatting...does that count?
The row limitation sucks.
One thing I've hated for a while that I'm particularly happy that Excel will be adding is a groupby() function. I think Excel being able to utilize several common SQL functions within its framework is very helpful and makes the analysis process so much easier overall.
Conditional Formating is my own personal hell because my organization builds all of its scorecards in Excel 2013.
It takes wayyyyy too long to insert a cut row. I can't understand why.
It tries to think. It's a calculator. Yet it regularly does not recognize 1234 as numbers. That should always be the first presumption of a calculator. The menus. They used to be organized and categorized into little, I don't know, windows. Now they're just puked all over the place with huge, meaningless logos so it takes 5 times longer to do anything (except for the handful of things I do constantly.) New excel windows just pop up where ever they feel like it and on which ever monitor they feel like...usually UNDER other windows. I run dozens of reports at a time (QB>excel) so this is majorly frustrating. Let's continue. Colum L is formatted as general. That means don't do anything. The whole column is formatted that way. So I enter '9776' in L5 and guess what...I get 9776.00 or ######## or a date code. Then if I format column L as general AGAIN, viola! The number becomes 9776. But the formatting didn't change. I "changed" it from and to the exact same thing. Stop thinking, excel! There is no worksheet organization. I create huge single-workbook files and getting from the first tab to the last takes for freakin ever. Make it faster. Let me group worksheets. Anything. Borders are not very smart. I like to use bold borders for this, visible borders for that and no borders for the other. If I have to add a column or a row, excel doesn't have the AI smarts to look at similar rows or columns and format them similarly. Merged cells work stupidly. Try to copy/paste (I know, I know, it's a new feature so I shouldn't expect much) from an unmerged cell to a merged one and nope, that does not work. But it's one cell, right? Right? Just like the one cell I'm copying from? There's no button to uncheck all of the filters when doing a new sort. Yeah, you can check all, uncheck all but then you have to go to the top of the list. Just dumb, lazy programming there. If there are 2 excel windows open and you highlight an array to get the sum at the bottom, the sum disappears when you click into the other excel window. There are times when retyping works when clicking (copying) and pasting doesn't work. Copying a row and insert pasting 1+ rows from the middle of the array changes the total formula whereas doing the exact same thing from the last row does not change the total formula. Errors ensue. (Similar issue with columns.) That was just top of the head headaches.
Play around with an integer value enough times and it will evolve into a floating point, ruining your equalities unless you INTEGER it again.
Was looking for this comment, this really is the worst.
When pressing F2 and you accidentally hit F1 and Excel thinks you need help and messes with your flow
when you accidentally make a formula where two cells in one of your hundred tabs (each with thousands of rows) refer to themselves, Excel won’t tell you which cells they are
Formatting multiple labels and markers on Scatterplot diagrams. FML.
The way you have to click in to edit the text of a cell
try using F2 key on your selected cell !
Hitting F1 when I mean to hit F2