# What is the most frustrating thing about excel?



## DataBlake (Jan 23, 2020)

For me it has to be it's inability to stop itself from crashing (at least 2016 version). Sometimes when i run a code that infinitely loops by accident i can kiss my progress goodbye because no amount of pause/break will save me ?
Among other things the fragility of my current "program" workbook is like trying to install a stereo system into a live bomb.
What about you? what grinds your gears?


----------



## Joe4 (Jan 23, 2020)

Probably the famed "floating-point arithmetic error": Floating-point arithmetic may give inaccurate result in Excel - Office



> Sometimes when i run a code that infinitely loops by accident


I don't think I have done that one in years, but I don't really write too many loops anymore without ends. 
If you might this happening more than once in a blue moon, you may want to re-consider how you are writing loops.  There are many different ways of writing them, many of them which avoid this issue, and many times loops are not even necessary at all.


----------



## DataBlake (Jan 23, 2020)

Joe4 said:


> may want to re-consider how you are writing loops



if im being honest its usually when i'm real sleepy. I work with a bunch of products and have to use arrays of the sheets and a complex system of dictionaries. sometimes i forget to check a variable like UsedRange.Rows.Count for "ghost rows" and it ends up in a huge unintended loop that i cant let run for the entire day. 

Lately i get crashes from running a lot of worksheet altering codes in my tests and then saving over my file instead of exiting without saving with a notepad of the changed modules. 

tl;dr my IT department deems me too powerful for SQL and MS Visual Studio so i use VBA to create """Databases""" using dictionaries that load up on opening my project. So my workbooks are kind of explosive, but i back them up daily. (i understand excel was not made for these uses and abuses but it still seems fragile to me)


----------



## Jon von der Heyden (Jan 24, 2020)

For me, two things:

I don't like the way I have to scroll sheets using the two arrows, rather than a scrollbar.  I like the right-click option however that provides a sheet navigation dialog, but I miss the old way of scrolling.

I don't like the behaviour of CONTROL+TAB.  If I have three or more books open then it seems to toggle only between two books. It didn't behave this way in pre 2016 versions, if I recall correctly.  Maybe I just need to flip a switch somewhere to change the behaviour?

I will also echo what Joe mentioned.  I recently had to describe the difference between two balances - one being derived in Excel and the other in TM1.  The difference was purely resultant of precision!  It's also not fun trying to explain how the precision difference has come about!

On the plus side - I recall similar topics over the many years that I have been frequenting forums.  Suffice to say that many things that we used to gripe about are no longer issues, such as repeating key values in Pivot Tables and a range of formulas.  In particular, I find TEXTJOIN tremendously helpful.  Excel continues to grow in leaps and bounds!


----------



## DataBlake (Jan 24, 2020)

Jon von der Heyden said:


> I don't like the way I have to scroll sheets using the two arrows, rather than a scrollbar.



yeah i agree with this although i dont work with many sheets anymore



Jon von der Heyden said:


> Excel continues to grow in leaps and bounds!



I agree it has come a looooooong way.


----------



## FormR (Jan 30, 2020)

Jon von der Heyden said:


> I don't like the behaviour of CONTROL+TAB.



 - same here! A very annoying change.


----------



## DanteAmor (Jan 30, 2020)

DataBlake said:


> Sometimes when i run a code that infinitely loops by accident i can kiss my progress goodbye because no amount of pause/break will save me



You can use doevents in your tests, if you enter a loop, you could stop it. After the tests you could remove the line.
Check this:






						VBA DoEvents - Automate Excel
					

In this ArticleVBA DoEvents ExampleDoEvents DangersWhen to use DoEvents This tutorial will discuss how to use the DoEvents command in VBA. The VBA DoEvents function temporarily pauses a running macro, giving Excel a chance to process key presses, mouse clicks, and other operating system...



					www.automateexcel.com


----------



## Oaktree (Jan 30, 2020)

Jon von der Heyden said:


> For me, two things:
> 
> I don't like the way I have to scroll sheets using the two arrows, rather than a scrollbar.  I like the right-click option however that provides a sheet navigation dialog, but I miss the old way of scrolling.
> 
> ...


Agree on all fronts.  I'm glad they finally added TEXTJOIN so we don't have to use MCONCAT from Laurent's morefunc.dll add-in!  I just wish they would have switched the 2nd and 3rd arguments and make the 3rd default to something if omitted.    Maybe it's just me, but I forget about the 2nd argument every time.  

My biggest gripe for this list though is the common crusade Bill Jelen unsuccessfully led a few years ago to try to make Pivot Tables default to Tabular Form.  The number of pivot tables I've ever wanted to be in something other than tabular form?  Still zero.


----------



## Jon von der Heyden (Jan 31, 2020)

Oaktree said:


> I just wish they would have switched the 2nd and 3rd arguments and make the 3rd default to something if omitted. Maybe it's just me, but I forget about the 2nd argument every time.


Regarding argument order, I suspect it's because the join range argument is a paramarray.  Still, I share your frustration.   

Now, I'm looking forward to TEXTSPLIT {hint hint}


----------



## DataBlake (Jan 31, 2020)

i crashed excel.......again
but this time i was working on someone's help request so i didnt lose anything.

i rarely use formulas these days, but the advancements with them seem pretty nice.


----------



## Rick Rothstein (Feb 3, 2020)

My pet peeve is the Find/Replace dialog box. Why is there an "Options>>" button? I mean, once you click the "Options>>" button, the dialog box is quite small so there is no screen "real estate" issues requiring some of the dialog box to be compacted. Also, given that some of the items on that expanded dialog box are remembered from previous uses (whether those uses were made manually in a previous use of Find/Replace or via VBA code), one pretty much has to click the "Options>>" button anyway just to make sure one of those remembered settings doesn't conflict with the current needs. I know this is a small thing, but it really irks me, especially when answering a question and having to tell the OP to click the "Options>>" button in order to check the setting for (usually) the "Match entire cell contents" checkbox.


----------



## Jon von der Heyden (Feb 4, 2020)

I'll echo that Rick.  I've been caught out before where I used VBA to change the settings and then become immensely frustrated trying to find a value using the UI - not clicking that it has changed the settings in the dialog too.


----------



## J.Ty. (Feb 6, 2020)

1. Small window of "Evaluate Formula" (would it cost more to make it bigger or resizeable?)
2. Small and awkwardly unconvenient formula editors in Data Validation, Name Manager, Conditional Formatting and the like.


----------



## Fazza (Feb 26, 2020)

Merged cells - not that I use them, but I sometimes get spreadsheets from other that have them in rows above a header row.


----------



## Jon von der Heyden (Feb 27, 2020)

Fazza said:


> Merged cells - not that I use them, but I sometimes get spreadsheets from other that have them in rows above a header row.


Agreed


----------



## Peter_SSs (Feb 27, 2020)

Rick Rothstein said:


> My pet peeve is the Find/Replace dialog box.


Agreed



J.Ty. said:


> 1. Small window of "Evaluate Formula" (would it cost more to make it bigger or resizeable?)
> 2. Small and awkwardly unconvenient formula editors in Data Validation, Name Manager, Conditional Formatting and the like.


Agreed to both - especially the Evaluate Formula box. It is ridiculous!

I'm not so against merged cells. Whilst they are sometimes a problem, I believe they do have their place and mostly can be managed adequately in my experience.


----------



## steve case (Apr 6, 2020)

Jon von der Heyden said:


> For me, two things:
> 
> I don't like the way I have to scroll sheets using the two arrows, rather than a scrollbar.  I like the right-click option however that provides a sheet navigation dialog, but I miss the old way of scrolling.
> 
> ...



Oh isn't that ever the truth.  If you're copying data from somewhere and you need to
 ---- Uh oh! You said [Ctrl-Tab]  not the same as [Alt-Tab] which from some change that 
maybe wasn't to Excel but maybe windows. When I want to  [Alt-Tab] from Excel through 
various tabs that I have open I have to [Alt-Tab][Alt-Tab] twice to get out of Excel and 
into the rest of the tabs that I have open.  It ruins your rhythm it does. 

Getting rid of blank data that appears as zeroes in your graph is another annoying chore 
when you deleted the no data place holder -9999 or whatever the originator of the data 
decided to use.

Recently I find that the equation displayed on your graph i.e., y = 0.123x²- 456x + 789, 
doesn't have a function, ƒx,  for those three terms.  Maybe on the next version (-: 

And those G**D*** little yellow flags that cover up your data to tell you something about 
some formula some where and you can't turn 'em off.

But the biggest annoyance is the Date Function.  Down load some data off the net and 
there in the middle a date instead of your number shows up.  That has caused some 
serious errors for me.      

Not an Excel issue but those key board toggles [CapsLock], [NumLock], [ScrollLock] 
and [Insert] can really screw things up.  And the [Home] key doesn't go to [A1]. Oh 
that is an Excel issue.


----------



## ashakantasharma (May 30, 2020)

Large Excel Files gets easily corrupt.


----------



## J.Ty. (Jun 5, 2020)

It seems that there are people for whom automatic conversion of strings into dates and/or numbers is a serious problem:

Mark Ziemann, Yotam Eren, and Assam El-Osta
Gene name errors are widespread in the scientific literature
Gene name errors are widespread in the scientific literature

Abstract tells it all:
The spreadsheet software Microsoft Excel, when used with default settings, is known to convert gene names to dates and floating-point numbers. A programmatic scan of leading genomics journals reveals that approximately one-fifth of papers with supplementary Excel gene lists contain erroneous gene name conversions.


----------



## Scott T (Jun 5, 2020)

Fazza said:


> Merged cells - not that I use them, but I sometimes get spreadsheets from other that have them in rows above a header row.


The report writer my work uses will merge cells when you export the report as Excel so it looks just like the PDF. Really annoying to have to unmerge  and clean up the data all the time.


----------



## ashakantasharma (Jun 6, 2020)

Excel seems to give you a two-second window at the start of a full workbook calculation cycle to instruct it to abort.  If you miss that window, any further attempts to abort cause Excel to lock-up and white-out, and then you're stuck waiting for it to finish the full calculation cycle. This can be especially frustrating if you happen to have multiple workbooks open when Excel suddenly decides to auto-save and recalculate all formulas across all workbooks.  It's even more frustrating if any of those open workbooks have calculation-intensive sensitivity tables or self-referencing cells.  I've had to manually terminate Excel more times than I can count when this issue has cropped up in the middle of a time-sensitive analysis. 

Incorrect reading of a date in dd/mm/yyyy format and then inability to change it through just one click: I had a csv file which had all dates in dd/mm/yyyy format, but when I copied it into excel, it read dates like the 1/12/2001 as 12-Jan-2001 and left dates like 14/5/2001 as is. Frustratingly, there is no one click solution to correct this, and I had to write long formulas to make excel read all the dates correctly.


----------



## B___P (Jun 9, 2020)

@*ashakantasharma *to import date I use this
cells(r,c).formulalocal=_yourvalue_
This way I let excel to translate from USA standard

I have a background in economics and I hate not to use directly Financial formulas in excel.
When you calculate a monthly installment at a interest rate of 10%, excel uses 10%/12  and not 
(1+0,10)^(1/12)-1 as the monthly interest rate. I hate to use extra cells to calculate input values.


----------



## JonXL (Jul 27, 2020)

J.Ty. said:


> Mark Ziemann, Yotam Eren, and Assam El-Osta
> Gene name errors are widespread in the scientific literature
> Gene name errors are widespread in the scientific literature



Interesting to see a study backing what many of us already knew: the way Excel handles things that look like numbers is a problem indeed. 

I've never had the need to enter a 15-digit number _ as a number _- ever. The application needs some place to adjust defaults on this so I can specify business-specific settings on what is and what isn't to be treated as a number automatically.


----------



## MARK858 (Jul 27, 2020)

ashakantasharma said:


> Frustratingly, there is no one click solution to correct this, and I had to write long formulas to make excel read all the dates correctly.


You can do it using the date settings in Text to Columns.


----------



## limfoo (Aug 7, 2020)

Sometimes cell formats automatically default to "date" instead of "general."


----------



## diddi (Mar 28, 2021)

i absolutely hate the way excel handles dates.  it is hopeless in genealogy applications because dates prior to 1/1/1900 dont exist.  also it does strange things when working with dates. they can swap formats without reason and confuse d/m with m/d and then the genealogy data is unreliable.  i store all dates for genealogy in 3 cells ie d... m... y...  because i do not trust excel to not misbehave.  i have had million record data sets ruined because of excel randomly deciding that some dates dont look right and changing stuff.


----------



## MARK858 (Mar 28, 2021)

Write the dates as yyyy-mm-dd (ISO 8601), you won't get issues with the locale in that format.


----------

