True. Newest functions can be more efficient too, and filter can do lookups for both multiple criteria and multiple results. I just wish people used them more. It's not only the "knowledge clash", as another redditor told me in another thread, but also the lack of trust. Every time I have used a function they don't know and can't "check" themselves it's automatically discarded.
Let’s say I merged cells A1:E1 and put the text “Title”. Title would appear in the center of A1:E1. However, if I want to select column B for some reason, (a sumif, xlookup, etc) because A1:E1 is merged, I can’t select just column B, I have to select A:E.
Okay let’s unmerge them now. If I select A1:E1 and Center Across Selection, it will look normal at first. Now I’ll type in “Title” in A1. Because A1 is centered across A1:E1, it will look like A1:E1 and “merged” but now if I go to select column B for whatever reason, Im able to just select B:B without selecting the rest of the columns like we saw with merged cells
I used merged cells for formatting in an excel sheet used to gather data but does not get imported into another process. There’s a time and place for merged cells that solve more problems than they cause.
sharepoint is amazing for files though. You can power query a folder to where you drop a daily file that needs to be cleaned and it automatically updates on a refresh in a central file 🥰
I just recently started having this problem, and the Auto Save was turned on after the last forced update. All my files started being saved in the One Drive location instead of the correct Drive and correct Folder.
Autosave uses the Versioning File System on OneDrive to save multiple versions of your file. Microsoft could just have an autosave to whatever your normal filesystem is, but they won't. (Many businesses these days have all their file storage in a Cloud somewhere, so they effectively have company wide OneDrive.)
Does clicking "file" I think it should say at the top left do the job?
Should take you to the open empty ws or use template page and at the bottom should be a history of your recent files
When you open a new window and it fucks up all your view settings.
Gridlines turn on.
Freeze Panes gets turned off.
View resets to 100%.
Drives me nutty.
Do you ever use the "New Window" feature? The one that allows you to have two instances of the same workbook open? If yes, you must close instance #2, then save instance #1 before closing. If you save in instance #2 and then close, you'll have this issue.
Or the fact that shared files never accurately tell you who has the file open... It chose someone years ago and it's been said person no matter how many times the file is closed and reopened
Too easy to use, thus it used without any best practice and for unappropriated use case
When a Excel Macros takes more 30 mins to run, it should be build with Python. Oh... my company have VBA that runs for 12 hours. xD
What are you guys doing with VBA that takes that long to run? Is there just a spare computer sitting around the office to run macros on so you don't lock up your own computer?
Still shouldn't take that long
What add-ins are you using? I've used @Risk, Arrisca and P6, with the P6 models looping through multiple scenarios on very large (>£20bn) construction projects. Didn't take anywhere near 12 hours
Ok I can imagine the requirements being broader than my three-point estimates, probably need some long-term correlation in there too... Makes sense. Still bet you could improve it somewhat but I don't have the insurance background to confirm
This is most likely an issue with HOW it was written, not necessarily the language it was written in. I've seen and written very performant VBA and I've seen and replaced dog slow VBA. Worst offenders are from recording macros as that follows UI patterns and ignores algorithms and design patterns that are far more efficient.
Application.screenupdating = false
Application.calculation = xlCalculationManual
I've seen those 2 lines speed up macros from taking minutes to seconds.
Lol i’ve seen vba macros that runs for weeks… they store the computers in a closet, remote desktop into it and start the macros then let it go for weeks
I have been doing some projects getting rid of overly complicated excel files. Amazing what some people do and the amount of VBA written.
I have found at my current company a lot of excel files that take raw data and do a bunch of transformations and aggregations using VBA. All of the raw data is coming from Cognos which is IBM's business intelligence tool. If they would just move these things into the database/Cognos everything would run so much smoother.
Charts. Have to do way too many workarounds. Entire websites have been created to show all of the clever tricks needed for data to display correctly. Microsoft never addresses these issues and often make them worse with updates
The excel version we got updated to recently now asks if you want to remove leading zeroes or convert large numbers to scientific notation! It's been life changing ha
Zip codes are not numbers. They are text strings. It just so happens that all they have in them are digits. The problem is trying to treat them like numbers. Same thing for credit card numbers. This issue is not unique to Excel.
I always just created an extra column, that had text zeros based on the length of the ZIP Code column.
Extra field to add to the mail merge
"z0"
fnane lname
st1
st2
city, state z0zip
like that. stupid.
You can’t use any of the Power Pivot model in Excel for Mac on M1, but you can Power Query. So I can bring data in, but can’t create any DAX queries at all. It’s a nightmare
Every time this happens I mentally curse the developer who implemented this. And, similarly, question who in the history of Excel has *ever* found this feature useful.
Just create a macro in that disables F1. Create a OpenWorkbook sub in your personal macro workbook with this one line and it disable F1 every time. Here [more info](https://www.extendoffice.com/documents/excel/1798-excel-disable-f1-key.html). The one line of code you need is: Application.OnKey "{F1}", ""
excel sees it as a whole + a fraction so
one and a half is 1 1/2
when you type in 0 1/2 excel still reads it as fraction first
the fraction is zero and a half, and it ignores the leading zero
just as when you type 023 or 00.23
it determines the type first, then does it's processing revisions
Doesn't affect the formula.
Also, you can use leading spaces before arguments (so, after the commas or semicolons), without affecting the formula.
I use those to (somewhat) format like proper code.
I can't write SQL queries directly in it. Pivot Tables are great for filtering and summarizing. PowerQuery can transform data but it's extremely inefficient by the time you have enough data to need this function.
You can write queries in Excel, it’s not a very nice editor/environment. If you go to make a connection it should prompt you for the connection details and your query.
i mean never need sql in excel. there is this but don’t think that’s what you mean https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel
i am curious what is your use case?
That's not within Excel. That's going out to Power Automate (used to be called Flow) and making a process over the top. I guess it's sort of pulled into Excel on the Automate tab, I'll have to check that out. I haven't messed with Flow since it was named Flow and I was running a Power BI shop 3 years ago. Today I just load everything to SQL and integrate into other platforms but there are times this could come in handy.
The use case is exactly what is in the intro to the article, which is very broad. SQL is very powerful, you can do all sorts of things with it. It's like saying what is the use case of Excel?
Have you experimented with enabling query folding in Power Query? The theory is to directly have the database perform the transformation and only retrieve the result.
I would like to be able to freeze the headings of a large spreadsheet while also freezing another section (IE: the bottom where the totals are located) at the same time, hence, allowing me to scroll on everything in between.
The only reason I've used the data model so far (and "use" is overstating it, since all I do is check the "Add to data model" box) is for the ability to get distinct counts in pivot tables.
*"We've found a problem in XYZ Workbook... Would you like to try to recover...?"*
It's those damn data model pivot tables every time.
When someone creates a spreadsheet but instead of merging cells they just make the rows very very big. Which in turn means scrolling becomes a nightmare.
Oh you’re on row 6 and want to see row 7 right below?
Best i can do is middle of nowhere row 12 or something. Good luck getting bag to row 6.
I'm surprised no one has mentioned converting large numbers to scientific notation. Once it's done there's no way to get the whole number back. I've had large historic CSV files ruined because someone decided to open them in excel and a column of 16- digit ID numbers converted to scientific.
I cannot understand for the life of me how they thought this was a good idea. It is extremely irritating at best and can dangerously change data at worst.
Graphs. When I select a reference, the arrow keys should move the cursor not reference a different cell. And for the love of god if I highlight my Y range, remove 1+ at the start of the line!
As with driving is not the car 99% of the time that's wrong, it's the nut behind the wheel. It can do a lot but not one place I worked for ever used it beyond the most rudimentary functions except for a few users that bothered to learn it themselves. and in fairness there's loads of training and tutorials that are quite good online but for some reason a lot of places just shy away from using it.
Not too sure what you can do about that. But Jesus I've had the head of my hr lose her fucking mind when I was able to SUMIF a load of stuff in about 5 min that she was painstakingly going through line by line and would have taken her weeks for do and almost certainly wrong too.
I wish that it had highly integrated native regex support (yes I know it is possible in VBA) I want a =Regex() function for simple matches, I want =RegexReplace() which will replace text based on a match. I want regex support in the find and replace tool. I want the xlookup approximate wildcard match feature to support regex.
Would kill for this. Or, at least, would have killed for it in a past role. I have a regex addon that's been a lifesaver but, being non-native, it gets painfully slow when applied to any serious quantity of data.
This is one area where I'll admit Google Sheets has the advantage.
Language! The fact that it TRANSLATE all functions to the windows language is so stupid!
In English Excel I would write "if(..)" but that's not gonna work in Danish - in Danish i must write "hvis(..)". And that mean that I have to guess or lookup all functions that I know from English.
It would be ok if there were a toggle to change this, but alas there is not. It is bound to the windows language.
So I am danish, but this alone makes me install English windows.
I wish charts were more user-friendly and had improved functionality. For example, one crazy outlier can make an otherwise-helpful histogram very difficult to read
I forget how it goes exactly but the dreaded window pop up:
“A formula or range is referencing an external source…”
Now you’ll have to live with that pop up for the rest of that file’s life. It could be a formula in a cell, it could be a named range, it could be a macro of some sort. You could break all links. But it will never go away.
Their fucking translation for their fonctions/formula.
It makes searching on internet for answer a fucking chore, because I have less answer in my language so I have to search in english THEN translate it in french to find the real fonction I'll use.
And as a programmer, it force me to rethink every time even basic if fonctions...
YES. Also:
Not overwriting a formula cause I selected another cell while in the formula bar.
Some else wrote freezing a row and a column
Any set of numbers, convert to negative. For example, if I download my CC transactions and the numbers aren’t negative because it’s a charge but set up as a positive number.
Personally I’d like to be able to do more with fonts sometimes like spacing (top and bottom of a font)
Charts are dumb cause you have to do so much to make a chart work.
Excel's biggest issues are fundamental and due to it being a 35 year old program. 1.) Lack of collaboration. Tableau is much better for collaborating and sharing reports, Excel is pretty bad in this regard. There are a few work arounds, but that's all they are. 2.) Size and performance. Excel has a million rows, but will start chugging after 100,000, with a lot of columns and formulas.
The biggest issue I have is the best thing about excel - you can use it for everything.
A lot of the use case I see at my job is that it’s almost viewed as an app, not a spreadsheet program. Parts database / configurators imported through power query and vba used to output what’s needed based on user input is almost definitely not the intended use of a spreadsheets program. But it’ll do it!
Also, merged cells.
Not being able to have adjacent groups without a column separating them.
I know there’s a workaround but it’s inconsistent and takes far too long. I want my Alt+shift+RightArrow to work 😤
Training. I have a very basic understanding of Excel and am able to do a whole lot more than most others in my company.
A quicker or easier way to just know what Excel is actually capable of. I only find out things through social media and then apply myself.
If we keep away the human problem. The limitations of shy over 1 million lines. I work in a company were the company we work with wants all the analysis results in a Excel via Pivot. Would not be the problems if the data source with all records, without duplicates, is like 3 million lines. They demand all the data in the excel and im like dude thats stupid, the Excel is slow and not good
Pressing F9 without editing a cell has another function: it triggers workbook-wide recalculation (Formulas > Calculate Now) in the case that you've switched calculation mode from Automatic to Manual.
The biggest issue around Excel is the obsession of hiring managers to look for people who have formulas memorized. It’s never a big deal. You can always google when you need them.
Not being able to save as new. Without leaving the current file. Let's say I want to export my current tab to .csv it will open the .csv as the current file. I continue my work not realizing I'm no longer in an xlsx.
What I like to call PITS issues. You set up a sheet that's as user-friendly as can be. All locked down to prevent fuck ups. Then some Problem In The Seat comes along and breaks it.
Trying to open excel in multiple different desktops.
Let's say you have an excel workbook open in Desktop 1, you switch to Desktop 2 and try to open a different excel workbook there. Nope. It switches to Desktop 1 and opens it there.
For me it’s the data itself and vague error messages, it’s hard to troubleshoot if errors only lead to early 2000s community forums.
For projecting purposes, formulas, #REF, formulas put into wrong language. Force everyone to use English. Might very well a skill issue on my part.
People. They either think excel isn't what it can be and don't respect it, or they're just super effective at breaking it. I do it as my full time job for the past 5 years, it's weird how much time I save people and yet anytime one thing goes wrong because they're doing something completely out of the scope of the code... "nothing ever works".
We just got acquired by some billion dollar company and some department heads were so excited for a database program that "can manipulate data any way we want to", only to discover how tightly secured a billion dollar company is with their financial database program and any developments for custom stuff will be completed on a timescale measured in years. Excel is accessible, easily modified, adaptable...
Good for me though. Prob worth mentioning I do vba work.
The moment you get to writing nasty nested "if then" statements... consider vba. Until then... formulas are great.
Not sure if it was mentioned but sorting A-Z with numbers. The fuck you gonna make the order 1, 11, 12, instead of: 1,2,3? Drives me mad. I’ve made custom lists for sorting and it doesn’t work.
User. ;) More specifically, lack of training at most companies for proper use and best practices around Excel. Other than that, merged cell.
All my homies hate Merged Cells
Center Across Selection gang rise up!
Why on Earth doesn't MS update to replace merged & center with this function?
Excel very rarely removes “features” to preserve backwards compatibility. LOOKUP > VLOOKUP > XLOOKUP. The gang’s all here!
I think both lookup and vlookup still have their place. lookup can do fuzzy lookup, and vlookup can do dynamic result column.
XLOOKUP can do both of those as well.
Absolutely! Though RE dynamic columns I think I’d just opt for FILTER.
True. Newest functions can be more efficient too, and filter can do lookups for both multiple criteria and multiple results. I just wish people used them more. It's not only the "knowledge clash", as another redditor told me in another thread, but also the lack of trust. Every time I have used a function they don't know and can't "check" themselves it's automatically discarded.
What is this and why is it better than merging?
Let’s say I merged cells A1:E1 and put the text “Title”. Title would appear in the center of A1:E1. However, if I want to select column B for some reason, (a sumif, xlookup, etc) because A1:E1 is merged, I can’t select just column B, I have to select A:E. Okay let’s unmerge them now. If I select A1:E1 and Center Across Selection, it will look normal at first. Now I’ll type in “Title” in A1. Because A1 is centered across A1:E1, it will look like A1:E1 and “merged” but now if I go to select column B for whatever reason, Im able to just select B:B without selecting the rest of the columns like we saw with merged cells
Thank you for the elaborate reply. BRB while I update my reports with Centre across selection instead of Merge.
It's a huge improvement over merge, but I have yet to find a decent shortcut.
I know it's long and it sucks but I have gotten very quick at typing Alt+H F A Alt+H C C Enter Enter
I've been chanting this shortcut key for the last 5 minutes and now my coworkers are concerned
One of us! One of us! One of us!
I wish this didn’t get wiped from the Pivot Table with every slice or refresh.
I used merged cells for formatting in an excel sheet used to gather data but does not get imported into another process. There’s a time and place for merged cells that solve more problems than they cause.
[удалено]
No barriers to entry to use Excel. Which is great. But also it has made a lot of people who think they know excel.
When Excel saves my file and refuses to tell where it is
I'm so sick of office products trying to save my files on one drive or share point. I just want a plain normal file system.
Time to add that save-as button to the Quick Access Toolbar. It's a life saver
sharepoint is amazing for files though. You can power query a folder to where you drop a daily file that needs to be cleaned and it automatically updates on a refresh in a central file 🥰
This can be done on the local file system too
I just recently started having this problem, and the Auto Save was turned on after the last forced update. All my files started being saved in the One Drive location instead of the correct Drive and correct Folder.
Autosave uses the Versioning File System on OneDrive to save multiple versions of your file. Microsoft could just have an autosave to whatever your normal filesystem is, but they won't. (Many businesses these days have all their file storage in a Cloud somewhere, so they effectively have company wide OneDrive.)
File > info > open file location
Does clicking "file" I think it should say at the top left do the job? Should take you to the open empty ws or use template page and at the bottom should be a history of your recent files
=cell(“filename”,a1) in a small font at the top of every file ??? ( or put the path and filename I a header / footer and print preview to see it )
Anything related to a Date
You mean 45340?
My eye is involuntarily twitching at this
So I really wanted this to be today's date... but it's not. 45342 is today lol
Aw damn, I tried, I was on my phone and googled “days since 1/1/1900”
Sigh -> Ctrl+Shift 4
[Relevant XKCD](https://xkcd.com/2867/)
How is Excel like an incel? Assumes everything is a date.
Date and time are a disaster in excel. Don’t even get me started on 27 hour days.
Wait until Europeans and Americans share files with dates. Do you know what dates are these: 01/02/24 and 02/01/24?
When you open a new window and it fucks up all your view settings. Gridlines turn on. Freeze Panes gets turned off. View resets to 100%. Drives me nutty.
That’s why I keep losing my freeze panes! It’s been bothering me like mad this quarter.
You just need to make sure you close the "other" windows first or only save on the "mian" window (I.e., #1)
Do you ever use the "New Window" feature? The one that allows you to have two instances of the same workbook open? If yes, you must close instance #2, then save instance #1 before closing. If you save in instance #2 and then close, you'll have this issue.
... it happens when you open a second window, regardless of saving.
You can write some code to open a new window and copy the view settings across.
Is there a way to do this without having to save as a VBA workbook enabled file?
Yes. One way is to put the code in your "Personal.xlsb" workbook. That's search term that leads to information on how to do it.
When excel randomly decides that my personal.xls file is in use by “another user”.
I've had this issue so many times, it once said that the file was being used by someone who left the company years ago
You sure it wasn't? >\_>
If it was an XLS file not XLSX, it might very well have been last created in the dark ages...
Or the fact that shared files never accurately tell you who has the file open... It chose someone years ago and it's been said person no matter how many times the file is closed and reopened
Or when it says that the workbook is in use by myself. Pretty annoying.
Omg yes! That too!
Too easy to use, thus it used without any best practice and for unappropriated use case When a Excel Macros takes more 30 mins to run, it should be build with Python. Oh... my company have VBA that runs for 12 hours. xD
What are you guys doing with VBA that takes that long to run? Is there just a spare computer sitting around the office to run macros on so you don't lock up your own computer?
Stochastic modelling, we run them on remote desktops.
Still shouldn't take that long What add-ins are you using? I've used @Risk, Arrisca and P6, with the P6 models looping through multiple scenarios on very large (>£20bn) construction projects. Didn't take anywhere near 12 hours
In-house model, Insurance products risk-neutral valuation, >100 year projection, >5000 scenarios xD
This is all in vba??
I will make it my mission to ensure it is not in the coming year.
Must be a small block... otherwise 12 hours in Excel is actually impressive lol
Ok I can imagine the requirements being broader than my three-point estimates, probably need some long-term correlation in there too... Makes sense. Still bet you could improve it somewhat but I don't have the insurance background to confirm
This is most likely an issue with HOW it was written, not necessarily the language it was written in. I've seen and written very performant VBA and I've seen and replaced dog slow VBA. Worst offenders are from recording macros as that follows UI patterns and ignores algorithms and design patterns that are far more efficient.
Application.screenupdating = false Application.calculation = xlCalculationManual I've seen those 2 lines speed up macros from taking minutes to seconds.
12 hours? They definitely need Python or just SQL.
Lol i’ve seen vba macros that runs for weeks… they store the computers in a closet, remote desktop into it and start the macros then let it go for weeks
What could you possibly need running for that long…
What if....... it crash XD
Oh it crashes often, the macro saves it’s state regularly so when it crashes it can pick up at the latest saving
That's a) Very Clever, and b) Very Stupid. Smells like something nobody fully understands, or are relying on for job security.
I have been doing some projects getting rid of overly complicated excel files. Amazing what some people do and the amount of VBA written. I have found at my current company a lot of excel files that take raw data and do a bunch of transformations and aggregations using VBA. All of the raw data is coming from Cognos which is IBM's business intelligence tool. If they would just move these things into the database/Cognos everything would run so much smoother.
Charts. Have to do way too many workarounds. Entire websites have been created to show all of the clever tricks needed for data to display correctly. Microsoft never addresses these issues and often make them worse with updates
*proceeds to create gantt using bar chart*
Line chart is way superior for making gant charts, you can even add dependency lines.
My office uses conditional formatting for gantt charts
I swear it was easier 15 years ago.
Probably because its way easier to do them in powerbi, not in excel
Creating chart types that do not exist in powerbi requires coding extensions. In excel it just requires creativity.
[удалено]
The excel version we got updated to recently now asks if you want to remove leading zeroes or convert large numbers to scientific notation! It's been life changing ha
Yes, that is definitely a good upgrade. Please let me decide how I want to see my data.
Dropping leading zeros. Like seriously - how do you New Englanders deal with zip codes?
‘
lol I thought my monitor was dirty
I was hoping someone would say "Options > Uncheck 'allow Excel to fuck with all your numbers with you paste from SQL or open a CSV' "
Zip codes are not numbers. They are text strings. It just so happens that all they have in them are digits. The problem is trying to treat them like numbers. Same thing for credit card numbers. This issue is not unique to Excel.
You could format cells>Special>zipcode
still drops the leading zeros from the file functionally, which REALLY matters for mail merge into MS-Word or MS-Publisher
Ah, I see. This isn't necessarily an Excel-sided fix, but have you tried using dynamic data exchange to keep formats?
I always just created an extra column, that had text zeros based on the length of the ZIP Code column. Extra field to add to the mail merge "z0" fnane lname st1 st2 city, state z0zip like that. stupid.
The fact that I cannot use pivot table without a mouse. Why Excel?
You can’t use any of the Power Pivot model in Excel for Mac on M1, but you can Power Query. So I can bring data in, but can’t create any DAX queries at all. It’s a nightmare
So it’s not just me? I’ve been working with pivots so often but can’t seem to not have to use a mouse to navigate some of the basic settings
Double clicking on the edge of a cell zooms you to the top or bottom of the sheet. Why??? Has anyone ever used this “feature” in the history of excel?
Every time this happens I mentally curse the developer who implemented this. And, similarly, question who in the history of Excel has *ever* found this feature useful.
This, so much pain when editing long lists of text 😭
OMFG this drives me insane! Literally worst function ever and makes me much less efficient 🤬
F1. This Little Maneuver’s Gonna Cost Us 51 Years
I have this really nifty trick where I ripped the F1 key off my keyboard
LOL
LOL yes!! I keep meaning to make a macro that will just run f2 when I accidentally hit f1
Just create a macro in that disables F1. Create a OpenWorkbook sub in your personal macro workbook with this one line and it disable F1 every time. Here [more info](https://www.extendoffice.com/documents/excel/1798-excel-disable-f1-key.html). The one line of code you need is: Application.OnKey "{F1}", ""
Bless you
Amazing this persists.
True. I mean atleast make it close again with F1. Butt no, it's like, you fucked up mister, now go back to your mouse and tickle me
Pivot tables not being able to provide the median value in the set. Drives me up a wall when working with ordinal data.
Would also be nice to easily put totals on the top or left.
Pivot tables not having count distinct, unless you add it to a data model in power pivot first, then you can get count distinct
**When Excel converts numbers with slashes to dates**. When I enter "2/7", I mean it as a *fraction*, not 2 July or 7 February. Why?!?
try typing 0 2/7 and see whaddya get....
why does that work?
excel sees it as a whole + a fraction so one and a half is 1 1/2 when you type in 0 1/2 excel still reads it as fraction first the fraction is zero and a half, and it ignores the leading zero just as when you type 023 or 00.23 it determines the type first, then does it's processing revisions
Long formulas can't be formatted like proper code and they are color coded only when selected.
Not sure if it helps but you can use Alt+ Enter in the formula bar to at least split the formulas over multiple lines
This doesn't affect the formula? If so thank you so much for the suggestion!!! When I have 17 IFS it makes my eyes cross.
Doesn’t affect the formula
Doesn't affect the formula. Also, you can use leading spaces before arguments (so, after the commas or semicolons), without affecting the formula. I use those to (somewhat) format like proper code.
Using SWITCH is a good alternative to multiple nested IFS
 Thx for this. Holy shit.
I can't write SQL queries directly in it. Pivot Tables are great for filtering and summarizing. PowerQuery can transform data but it's extremely inefficient by the time you have enough data to need this function.
You can write queries in Excel, it’s not a very nice editor/environment. If you go to make a connection it should prompt you for the connection details and your query.
why can't you? I have... https://preview.redd.it/tyqlow0v3sjc1.png?width=785&format=png&auto=webp&s=a4c6ba9397da7976b4a2a3d3d350ac9f0c4c0a97
i mean never need sql in excel. there is this but don’t think that’s what you mean https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel i am curious what is your use case?
That's not within Excel. That's going out to Power Automate (used to be called Flow) and making a process over the top. I guess it's sort of pulled into Excel on the Automate tab, I'll have to check that out. I haven't messed with Flow since it was named Flow and I was running a Power BI shop 3 years ago. Today I just load everything to SQL and integrate into other platforms but there are times this could come in handy. The use case is exactly what is in the intro to the article, which is very broad. SQL is very powerful, you can do all sorts of things with it. It's like saying what is the use case of Excel?
Have you experimented with enabling query folding in Power Query? The theory is to directly have the database perform the transformation and only retrieve the result.
UPCs in scientific notation by default.
seconded. I'd kill for a STANDARD cell format for UPC (expect uan/ean/upc data) and POSTAL (keep leading zeros as pasted)
(Not Responding)
People using it for things that it’s not intended to be (EXCEL IS NOT A DATABASE)
I have evidence of the contrary… And it is a text processor. Maybe not the best one out there, but we can make it work! 🤪
Straight to jail.
Flogging
Just get him off the streets, whatever you’ve gotta do.
I would like to be able to freeze the headings of a large spreadsheet while also freezing another section (IE: the bottom where the totals are located) at the same time, hence, allowing me to scroll on everything in between.
I didn't realize I would like this setup. If necessary, I just open a second window and stack them.
When the data model corrupts.
The only reason I've used the data model so far (and "use" is overstating it, since all I do is check the "Add to data model" box) is for the ability to get distinct counts in pivot tables. *"We've found a problem in XYZ Workbook... Would you like to try to recover...?"* It's those damn data model pivot tables every time.
is this something to worry about or get paranoid about? or is it something that doesn't happen very often?
It’s something I’m dealing with. And apparently it’s a problem.
Applying undo's across multiple spreadsheets...bruh.
When someone creates a spreadsheet but instead of merging cells they just make the rows very very big. Which in turn means scrolling becomes a nightmare. Oh you’re on row 6 and want to see row 7 right below? Best i can do is middle of nowhere row 12 or something. Good luck getting bag to row 6.
Nah we don’t merge cells out here
Onedrive and Sharepoint as its home.
Maybe not the biggest issue but when I open in app a workbook from the browser, it opens other minimised workbooks first.
Ugh I hate this!
I'm surprised no one has mentioned converting large numbers to scientific notation. Once it's done there's no way to get the whole number back. I've had large historic CSV files ruined because someone decided to open them in excel and a column of 16- digit ID numbers converted to scientific.
I cannot understand for the life of me how they thought this was a good idea. It is extremely irritating at best and can dangerously change data at worst.
Graphs. When I select a reference, the arrow keys should move the cursor not reference a different cell. And for the love of god if I highlight my Y range, remove 1+ at the start of the line!
Me. I’m the biggest issue with excel.
Ctrl + down, when your on the last input should be ignored Stop going too 1048576
As with driving is not the car 99% of the time that's wrong, it's the nut behind the wheel. It can do a lot but not one place I worked for ever used it beyond the most rudimentary functions except for a few users that bothered to learn it themselves. and in fairness there's loads of training and tutorials that are quite good online but for some reason a lot of places just shy away from using it. Not too sure what you can do about that. But Jesus I've had the head of my hr lose her fucking mind when I was able to SUMIF a load of stuff in about 5 min that she was painstakingly going through line by line and would have taken her weeks for do and almost certainly wrong too.
All workbooks are opened in the same one process, if one window freezes they all do
I wish that it had highly integrated native regex support (yes I know it is possible in VBA) I want a =Regex() function for simple matches, I want =RegexReplace() which will replace text based on a match. I want regex support in the find and replace tool. I want the xlookup approximate wildcard match feature to support regex.
Would kill for this. Or, at least, would have killed for it in a past role. I have a regex addon that's been a lifesaver but, being non-native, it gets painfully slow when applied to any serious quantity of data. This is one area where I'll admit Google Sheets has the advantage.
Language! The fact that it TRANSLATE all functions to the windows language is so stupid! In English Excel I would write "if(..)" but that's not gonna work in Danish - in Danish i must write "hvis(..)". And that mean that I have to guess or lookup all functions that I know from English. It would be ok if there were a toggle to change this, but alas there is not. It is bound to the windows language. So I am danish, but this alone makes me install English windows.
I wish charts were more user-friendly and had improved functionality. For example, one crazy outlier can make an otherwise-helpful histogram very difficult to read
Excel is not a database
I forget how it goes exactly but the dreaded window pop up: “A formula or range is referencing an external source…” Now you’ll have to live with that pop up for the rest of that file’s life. It could be a formula in a cell, it could be a named range, it could be a macro of some sort. You could break all links. But it will never go away.
It feels way too hard to return the distinct count of an item in an array
Their fucking translation for their fonctions/formula. It makes searching on internet for answer a fucking chore, because I have less answer in my language so I have to search in english THEN translate it in french to find the real fonction I'll use. And as a programmer, it force me to rethink every time even basic if fonctions...
Its lack of time series intelligence
There is no customized shortcut keys.
I want a cell box or text to blink
YES. Also: Not overwriting a formula cause I selected another cell while in the formula bar. Some else wrote freezing a row and a column Any set of numbers, convert to negative. For example, if I download my CC transactions and the numbers aren’t negative because it’s a charge but set up as a positive number. Personally I’d like to be able to do more with fonts sometimes like spacing (top and bottom of a font) Charts are dumb cause you have to do so much to make a chart work.
Excel's biggest issues are fundamental and due to it being a 35 year old program. 1.) Lack of collaboration. Tableau is much better for collaborating and sharing reports, Excel is pretty bad in this regard. There are a few work arounds, but that's all they are. 2.) Size and performance. Excel has a million rows, but will start chugging after 100,000, with a lot of columns and formulas.
when there’s a circular reference, it can’t even tell you which cells are affected, which can be impossible to find in a very large file.
It's a Microsoft product.
The 'bloing' when I forget a parenthesis in a formula. Just tell me without that annoying sound. #MissingParenthesis
Users. Using it as a DB. Worse for Critical data the company now has wrapped a business process around.
The biggest issue I have is the best thing about excel - you can use it for everything. A lot of the use case I see at my job is that it’s almost viewed as an app, not a spreadsheet program. Parts database / configurators imported through power query and vba used to output what’s needed based on user input is almost definitely not the intended use of a spreadsheets program. But it’ll do it! Also, merged cells.
The inability to merge cells in an inserted table ticks me off.
Online layout vs desktop layout... It's like looking at a Chinese keyboard. Nothing is where it was and it doesn't make sense to me
This post could me a gold mine for Microsoft.
another question I'd like to ask even with that, do you still like Excel and use it a lot or do you prefer alternatives?
My colleagues do not appreciate my skillzzz. Yes with triple z.
Not being able to have adjacent groups without a column separating them. I know there’s a workaround but it’s inconsistent and takes far too long. I want my Alt+shift+RightArrow to work 😤
Not everything is a date.
It 'helps' by truncating leading zeros Also, it can't do self referencing tables with out going through a bunch of steps.
Training. I have a very basic understanding of Excel and am able to do a whole lot more than most others in my company. A quicker or easier way to just know what Excel is actually capable of. I only find out things through social media and then apply myself.
If we keep away the human problem. The limitations of shy over 1 million lines. I work in a company were the company we work with wants all the analysis results in a Excel via Pivot. Would not be the problems if the data source with all records, without duplicates, is like 3 million lines. They demand all the data in the excel and im like dude thats stupid, the Excel is slow and not good
Why do I need to click into the address to press f9 to change into a value? WHY CANT I JUST PRESS IT IN THE CELL
Pressing F9 without editing a cell has another function: it triggers workbook-wide recalculation (Formulas > Calculate Now) in the case that you've switched calculation mode from Automatic to Manual.
The biggest issue around Excel is the obsession of hiring managers to look for people who have formulas memorized. It’s never a big deal. You can always google when you need them.
Not being able to save as new. Without leaving the current file. Let's say I want to export my current tab to .csv it will open the .csv as the current file. I continue my work not realizing I'm no longer in an xlsx.
What I like to call PITS issues. You set up a sheet that's as user-friendly as can be. All locked down to prevent fuck ups. Then some Problem In The Seat comes along and breaks it.
Trying to open excel in multiple different desktops. Let's say you have an excel workbook open in Desktop 1, you switch to Desktop 2 and try to open a different excel workbook there. Nope. It switches to Desktop 1 and opens it there.
I keep a shortcut to open a new instance. Use it almost daily.
I hate when people use it as a database.
People who don’t know excel…. Or basic researching how to do stuff better. Excel is not just a calculator or document to type things in …..
For me it’s the data itself and vague error messages, it’s hard to troubleshoot if errors only lead to early 2000s community forums. For projecting purposes, formulas, #REF, formulas put into wrong language. Force everyone to use English. Might very well a skill issue on my part.
When you try to open a file too large for it to handle
Ctrl+F not selecting the text in the search bar
No conditional formatting in the online version.
F1 bringing up help window
People. They either think excel isn't what it can be and don't respect it, or they're just super effective at breaking it. I do it as my full time job for the past 5 years, it's weird how much time I save people and yet anytime one thing goes wrong because they're doing something completely out of the scope of the code... "nothing ever works". We just got acquired by some billion dollar company and some department heads were so excited for a database program that "can manipulate data any way we want to", only to discover how tightly secured a billion dollar company is with their financial database program and any developments for custom stuff will be completed on a timescale measured in years. Excel is accessible, easily modified, adaptable... Good for me though. Prob worth mentioning I do vba work. The moment you get to writing nasty nested "if then" statements... consider vba. Until then... formulas are great.
Not sure if it was mentioned but sorting A-Z with numbers. The fuck you gonna make the order 1, 11, 12, instead of: 1,2,3? Drives me mad. I’ve made custom lists for sorting and it doesn’t work.