# Things that bother me about excel, and other pet peeves



## Diffy (Jan 18, 2007)

After being a mathematician for many years, and studying matrix theory and other abstract algebra topics, the on thing that bothers me most about excel is how you classify a cell.  D12, in matices we always state the row and then the column, to have excel do this backwards drives me off the wall.  What is wrong with 12D? 

Also many times I wish I had more rows and columns.  You would think that 65k+ rows would be enough, but it isn't.  The number of columns is even more pitiful.

Am I the only one with pet peeves in excel?


----------



## Oaktree (Jan 18, 2007)

Per your concerns, (1) there is always R1C1 referencing for you and (2) Excel 2007 (finally) gives Excel the much needed upgrade from 65K to 1.1MM rows.

You're certainly not the only one with issues with Excel.  I'd put floating point errors on my list.


----------



## Diffy (Jan 18, 2007)

Does 2007 give more columns?  That could be a selling point for me.  Would you recommend 2007 as a "must have" upgrade from 2003?  How is the new interface?


----------



## Oaktree (Jan 18, 2007)

Oodles more columns (10,000+) too.

See: http://www.mrexcel.com/board2/viewtopic.php?t=172620 for a discussion of some of the new features.


----------



## starl (Jan 18, 2007)

Diffy.. Excel 2007 is WAY different from any other version.. it's apples and oranges.
Thing is - I like apples and oranges  So it's not a bad thing.
But it does take a while to get used to. The interface is VERY different. and the file formats don't work backwards.
I've upgraded - but I have to. Else, I would wait a year, maybe two to have more users and to work the bugs out (esp. the latter).
Excel 2007 is great, lots of bells and whistles. But, if you communicate with people who don't upgrade, I don't know how useful it is. 2003 will continue to be my default version for quite a while.


----------



## Felix Atagong (Jan 19, 2007)

In our company it is still the 2000 version. 99 out of 100 users don't need the bells and whistles of Excel and use it mostly instead of the Windows calculator anyway... even copy - 'paste special values' is something most of our users have never ever heard of...

Why not have an Excel Light and an Excel Pro version?


----------



## Oorang (Jan 19, 2007)

You could always write a UDF to interpret just about any addressing scheme you want.


----------



## Joe4 (Jan 19, 2007)

> In our company it is still the 2000 version. 99 out of 100 users don't need the bells and whistles of Excel and use it mostly instead of the Windows calculator anyway... even copy - 'paste special values' is something most of our users have never ever heard of...
> 
> Why not have an Excel Light and an Excel Pro version?


I guess that really depends upon the kind of business you work in.  We use Excel a lot, along with a lot the advanced features, and a lot of VBA.  Heck, my job involves mostly writing Excel VBA and Access applications.  It is very useful in the financial world.

By the way, they do have Excel light...  it is the version of Excel that comes with most new home computers nowadays.  It sucks.  I had to upgrade to the Professional version just to get VBA and Access.  Those used to be standard.


----------



## xld (Jan 23, 2007)

> I guess that really depends upon the kind of business you work in.  We use Excel a lot, along with a lot the advanced features, and a lot of VBA.  Heck, my job involves mostly writing Excel VBA and Access applications.  It is very useful in the financial world.



SO, if most of your work is in VBA, why isn't 2000 sufficiient? VBA hasn't changed.



> By the way, they do have Excel light...  it is the version of Excel that comes with most new home computers nowadays.  It sucks.  I had to upgrade to the Professional version just to get VBA and Access.  Those used to be standard.



So VBA doesn't come with Excel standard? And Access is now an Excel add-on?


----------



## Joe4 (Jan 23, 2007)

> So VBA doesn't come with Excel standard? And Access is now an Excel add-on?


I can't speak for everywhere, but over here in the States, a lot of new computers come with what I call a "dummied down" version of Microsoft Office, you get Excel, but no VBA, and you don't get Access or PowerPoint either (unless you pay a significant price to upgrade).  

BTW, when I was made the reference to Access, I was talking about the entire Microsoft Office package.  Access is its own program and is not as an Excel add-on.


----------



## starl (Jan 23, 2007)

no VBA? or it's just not installed? By default, VBA has never been included in any install I've done (I've installed standard, business and pro) - I always have to turn it on - select it manually. But it's an option in the setup...


----------



## xld (Jan 23, 2007)

I have not come across this dummied down version of Excel without VBA, seems odd to me. Sounds like you are talking about Microsoft Works to me, which is another poriduct again.

But my other point was exactly that, Access is separate, so to talk about a Lite vresion of Excel as one without Access is ... well you can fill the rest in.


----------



## TinaP (Jan 24, 2007)

Could the dummied down version of Excel be part of Microsoft Works?  

I've run into more people that work with MS Office at work then buy a home computer following the advice of a salesperson who tells them that MS Works will do anything you need to do at home.  Next thing you know, they're begging me to install Office on the home computer because Microsoft left out one or two of the most useful functions in Works.


----------



## Oorang (Jan 24, 2007)

For my part VBA is what seperates Excel from every other product out there. There are several excellent spreadsheet product out there that I will never bother with because at the end of the limit of the program is the prgrammers imagination. And when have a robust scripting language like VBA then you are limited only by your OWN imagination not someone elses. It's pretty hard to hit the wall with Excel. You'll hit the point of diminishing returns long before you ever come into an "it can't be done situation." In fact I can't think of anytime.. ever... that I have wanted to do something in Excel and not been able to find a way to "get er done". Sometimes I have needed help, but there is always a way. 
So when I heard about excel "lite" I just laughed and said "there is a product that I will not be needing".


----------



## ExcelChampion (Jan 24, 2007)

> ...to have excel do this backwards drives me off the wall.  What is wrong with 12D?



Might as well mention  that in math, proper syntax is 2+2=4, but in Excel it is backwards, =2+2, which reults in 4.  Does this bother you as well?


----------



## Oorang (Jan 24, 2007)

> > ...to have excel do this backwards drives me off the wall.  What is wrong with 12D?
> 
> 
> 
> Might as well mention  that in math, proper syntax is 2+2=4, but in Excel it is backwards, =2+2, which reults in 4.  Does this bother you as well?


Well except that in math you anything in * = * form is an equality. And you can write an equality a = b + c or c + b = a :wink:


----------



## ExcelChampion (Jan 24, 2007)

…em ot ko skool ti…sdrawkcab yb snaem eh tahw wonk t'nod I


----------



## Oorang (Jan 24, 2007)

.noops on si ereht oslA  ."sdrawkcab" on si ereht taht saw tniop elohw yM .esreverrts.abv htiw detsurt eb t'nac tsuj elpoep emoS *hgis*


----------



## ExcelChampion (Jan 24, 2007)

> .noops on si ereht oslA



I thought that was moops...or at least proclaimed bubble boy.


----------



## Joe4 (Jan 24, 2007)

Yes, you guys are right, I was referring to Microsoft Works that has the dummied down version of Excel with no VBA.  

I ordered my computer from Dell a few years ago, and that is what comes standard with it.  They advertised this great price on the whole system, but I had to upgrade (for a significant price) to get VBA and Access.

They should call it "Microsoft Works, Except for Serious Programmers"...


----------



## steve case (Jan 25, 2007)

<ul><tt>Why not have an Excel Light and an Excel Pro version?
</tt>[/list]


There should be a "Lite" version of a lot of software.  Especially the remotes for DVD players.
.
.
.
.
.


----------



## xld (Jan 25, 2007)

Shame you didn#'t check all that first, your intervention effectively destroyed a valid point from Felix.


----------



## Joe4 (Jan 25, 2007)

> Shame you didn#'t check all that first, your intervention effectively destroyed a valid point from Felix.


I disagree.  Microsoft Works is a "lite" or dummied down version of Excel.

Also, this is a discussion thread.  I do not believe I "destroyed" anyone's point.  I was merely contributing to the discussion, just like Felix was.


----------



## steve case (Feb 7, 2007)

How about those toggle keys that can really screw you up until you figure out what's wrong.  They are:

Caps Lock - This has to be the number one offender, how many times during the day is it set wrong and you have to go back and re-type a sentence or two because you accidently pressed it?

Num Lock - Right up there with Caps Lock.  Type in a few numbers and all of a sudden you’re at line 65536 and you don't know how you got there. 

Insert - Very annoying when you look up at the screen to find out you've just typed over some very important information.    And there is no little green light to tell you it’s turned on. 

Scroll Lock - Doesn't happen very often, but very annoying when it does.  What’s it for anyway?

My solution?  I'd like a keyboard where all four of those little buggers were up in the corner out of the way where they couldn't be toggled by accident.   Like how about where those little green lights are?   

If such a keyboard is on the market I'd like to have one.


----------



## just_jon (Feb 7, 2007)

My problem isn't with the software, alas, but the operator.

He's gone and gotten old!


----------



## Oorang (Feb 7, 2007)

StACase:  I think your idea has some momentum behind it already http://capsoff.blogspot.com/


----------



## MrByte (Feb 19, 2007)

> In our company it is still the 2000 version. 99 out of 100 users don't need the bells and whistles of Excel and use it mostly instead of the Windows calculator anyway... even copy - 'paste special values' is something most of our users have never ever heard of...
> 
> *Why not have an Excel Light and an Excel Pro version?*



Don't give MS another idea to make $$ from us. That would be my petpeev in this forum, WG has too much $$.


----------



## steve case (Feb 24, 2007)

Follow this link:

http://www.mrexcel.com/board2/viewtopic.php?t=259418

I've noticed this one too.  I download  data, several hundred lines worth, a large portion  of which I delete, and the scroll bar continues to take me to the last row where data used to be.  

Doesn't matter if I hit the delete button or use Edit and delete the rows.


----------



## Richard Schollar (Feb 24, 2007)

> Follow this link:
> 
> http://www.mrexcel.com/board2/viewtopic.php?t=259418
> 
> ...



Use Edit>Clear>All instead


----------



## SydneyGeek (Feb 24, 2007)

Once you delete the rows, save the file. That resets the Used Range. You can check by hitting Ctrl + End. 

Denis


----------



## steve case (Feb 24, 2007)

> Use Edit>Clear>All instead



Didn't work


----------



## Richard Schollar (Feb 24, 2007)

Are you downloading directly onto the sheet? If so, you will probably need to delete the Name in which the download query is saved into (if you have this setting applied).  You should be able to see if the name exists via the usual Insert>Names>Define route.


----------



## steve case (Feb 24, 2007)

I stuck a "1" in A1, selected A1:A2500 or so [Shift + Down arrow] Then Edit, Fill, Series Step Value 1 OK

Then Selected Row 200 and Shift + Page down to the end and then Edit, Clear, All.

The side bar still goes all the way to 2500 or so. 

Doesn't work

,
,
,
,
,


----------



## Richard Schollar (Feb 24, 2007)

1. Make sure you go to the end of the document (ie to row 65536 or 1million+ if on xl2007+).
2. Save afterwards (this is critical), close and reopen


----------



## steve case (Feb 24, 2007)

> 1. Make sure you go to the end of the document (ie to row 65536 or 1million+ if on xl2007+).
> 2. Save afterwards (this is critical), close and reopen



Huh?

Way too much trouble!  Wasn't my issue anyway, sour grapes as the say. 

Usually when I notice this I'm downloading some pile of data from text and sorting and text to columning [is that a verb?]  for a one time deal. 

But hey, thanks for the input *(-:*
.
.
.
.


----------



## ExcelChampion (Feb 24, 2007)

Haven't read through all of the suggestions, but let's see:-

1. Select the first blank row of data.
2. Press Ctrl+Shift+Down Arrow
3. Right click in the selection and choose Delete.
4. Save

That *should* do it.


----------



## steve case (Mar 2, 2007)

Here's one that bit me smartly on my fanny today:

I take most everyone has seen this dialog box:



> This workbook contains links to other data sources.
> 
> *If you update the links, Excel will attempt to retrieve the latest data.
> *If you don't update the links, Excel will use the previous information.
> ...



I've stopped trying to rectify the situation and just click on cancel.  So does everyone else. Big mistake.  If there is a file that changes from time to time, and you're relying on a link to it to make your data correct, and some one moves or removes it! you get the above message.  But you have no idea it's your very important file that has been deleted or moved.   And Excel as the message says, uses the last data it had access to.   

Anyway, and I'll post this on the main board, is there a way to force Excel to display the #N/A error for the broken link instead of telling the lie?  Well really, if the file’s been deleted or moved, Excel has no idea what the true value of the "Link" is and is lying when it puts up that last known value and really doesn't tell you except for that opening message. 

Very frustrating, the Bill Of Material was moved and then changed, and my Excel file reported the old value just like that.    

I've got a huge file with lots of pages and all sorts of people do lots of things including making links to files that after a while get moved or deleted.  I can't chase them all down. 

So, is there a way to force Excel to "Crash" and put up an error message in the cell where the link is supposed to be?


----------



## Von Pookie (Mar 2, 2007)

For the record, your other post with this I just moved to the Excel Questions forum--you had posted it in the MrExcel Books area 
http://www.mrexcel.com/board2/viewtopic.php?t=260689


----------



## respree (Mar 7, 2007)

I'm peeved that 2+2 to Excel gives the answer "2+2," instead of 4.

It seems pretty instinctive that you want to add TWO to TWO, but Excel makes you specifically place a plus or equals sign before the addition (peeve=wasted keystroke).

I'm also peeved by the fact that =PROPER makes SAM'S into Sam'S.  Seems pretty clear what I meant. 

Okay, I'll stop complaining now.


----------



## ExcelChampion (Mar 7, 2007)

> I'm peeved that 2+2 to Excel gives the answer "2+2," instead of 4.
> 
> It seems pretty instinctive that you want to add TWO to TWO, but Excel makes you specifically place a plus or equals sign before the addition (peeve=wasted keystroke).
> 
> ...


Yes, it seems like it should take 's into account.  But, until it does, you'll just have to deal with it with perhaps:-


```
=IF(ISERROR(FIND("'",A1)),PROPER(A1),PROPER(LEFT(A1,FIND("'",A1)-1))&"'s")
```


----------



## RichardS (Mar 8, 2007)

Dunno about a peeve, but I would estimated 99% of the times that I have used a VLOOKUP or SUMIF formula, the formula will be copied down a number of rows. Naturally, the table_array, or the range and sum_range, depending on the function, is a constant. If these ranges are in the same file, I have to manually change the references to absolute. I would have thought having absolute ranges for these types of formulae as the default would make sense. Probably applies to a number of other functions as well. I know I can name the range, but if it's just a working file which won't be used again, that's just one more step, no faster than changing the formula to absolute.

My 2 bobs worth.

Richard


----------



## Lewiy (Mar 8, 2007)

> Dunno about a peeve, but I would estimated 99% of the times that I have used a VLOOKUP or SUMIF formula, the formula will be copied down a number of rows. Naturally, the table_array, or the range and sum_range, depending on the function, is a constant. If these ranges are in the same file, I have to manually change the references to absolute. I would have thought having absolute ranges for these types of formulae as the default would make sense. Probably applies to a number of other functions as well. I know I can name the range, but if it's just a working file which won't be used again, that's just one more step, no faster than changing the formula to absolute.



Richard, personally I don't think that this is a bad thing.  How would someone determine which functions would automatically use absolute ranges and which would not.  And for the user, this would be inconsistant and possibly frustrating as absolute references are not always appropriate for such functions.  I think having to remove the $ would be more irritating than having to put them in.


----------



## mortgageman (Mar 8, 2007)

> > Dunno about a peeve, but I would estimated 99% of the times that I have used a VLOOKUP or SUMIF formula, the formula will be copied down a number of rows. Naturally, the table_array, or the range and sum_range, depending on the function, is a constant. If these ranges are in the same file, I have to manually change the references to absolute. I would have thought having absolute ranges for these types of formulae as the default would make sense. Probably applies to a number of other functions as well. I know I can name the range, but if it's just a working file which won't be used again, that's just one more step, no faster than changing the formula to absolute.
> 
> 
> 
> Richard, personally I don't think that this is a bad thing.  How would someone determine which functions would automatically use absolute ranges and which would not.  And for the user, this would be inconsistant and possibly frustrating as absolute references are not always appropriate for such functions.  I think having to remove the $ would be more irritating than having to put them in.



How someone would determine which functions (and which function arguments) default to absolute would depend on usage.  Richard's point - which I certainly agree with - is that in a v (or h) lookup, the lookup table will not change.  Thus by intended usage, the first argument should default to absolute.  Removing the $ would never be irritating in that case, since no one would ever remove it.


----------



## Lewiy (Mar 8, 2007)

> > > Dunno about a peeve, but I would estimated 99% of the times that I have used a VLOOKUP or SUMIF formula, the formula will be copied down a number of rows. Naturally, the table_array, or the range and sum_range, depending on the function, is a constant. If these ranges are in the same file, I have to manually change the references to absolute. I would have thought having absolute ranges for these types of formulae as the default would make sense. Probably applies to a number of other functions as well. I know I can name the range, but if it's just a working file which won't be used again, that's just one more step, no faster than changing the formula to absolute.
> >
> >
> >
> ...



I often use VLOOKUP to determine whether, say the value in column A exists anywhere further down the list (ignoring cells above) for this purpose, I don't want the absolute values in there.  I appreciate that often this is not the case with most people but it would create an inconsistancy in the way Excel interprets what you enter.

When you miss a bracket off the end of a formula Excel says "I think you've made an error here, do you want to use this amended suggestion".  Perhaps if when you used VLOOKUP, it could say "I think you might want to use absolute values in your lookup range".  That way, whether you want them or not, you don't have to go back and change all the ranges, just click Yes or No.


----------



## klb (Mar 8, 2007)

Richard,
When you are typing in your lookup formula, just remember to hit F4 after you have entered your lookup range and your reference will change to  absolute.


----------



## Oorang (Mar 8, 2007)

I'd add match and countif to that list as well.


----------



## RichardS (Mar 8, 2007)

> Richard,
> When you are typing in your lookup formula, just remember to hit F4 after you have entered your lookup range and your reference will change to  absolute.



May be my version, but F4 doesn't work with VLOOKUP, and with SUMIF, only changes the last cell ref in the sum_range.

I like Lewiys' idea of asking which one you want though.

Cheers

Richard


----------



## Domski (Mar 9, 2007)

If you press F4 straight after selecting the range it should make both cell references absolute however if you click on the range as an afterthought it will only apply it to the cell ref you've clicked on.

Dom


----------

