Hacker News Reader

Comment by tgb | Hacker News Reader

I hadn't used Excel in years and now that I do, there's some things about it that drive me nuts:

- It forgets what you've copied to clipboard. Copy something. Insert another row so that there's space for it. Paste. Nothing happens. Huh? It lost my copy. It does this for a large number of operations and it drives me crazy. I've never seen any other program do this.

- You can't open two spreadsheets of the same name. This is because spreadsheet formulas can refer to cells in other tables. But I don't use that feature - can't I just open the second spreadsheet with a warning that this feature won't apply to it?

- (This one applies to too many pieces of Microsoft technology.) You can't use common keyboard shortcuts properly. Ctrl-backspace deletes a word in any useful text box. Not in formula editing in excel. And ctrl+delete deletes the rest of the line instead of just the next word. Why?

View on HN

> - It forgets what you've copied to clipboard. Copy something. Insert another row so that there's space for it. Paste. Nothing happens. Huh? It lost my copy. It does this for a large number of operations and it drives me crazy. I've never seen any other program do this.

Try 'WindowsKey + V'.

It'll bring up a list of your previous copies. Not as easy as 'Ctrl+V', of course, but it does save a bit of time. And yes I agree, Excel's Alzheimer's is quite annoying.

One of those things that I wouldn’t google myself as it’s a minor annoyance and yet when I see a tip like this it definitely makes me feel good about the time I spend on HN :D

Mind ... blown ...

Is there a mac version? I tried a few combos of cmd/control but no dice

Whoah.

The thing about the clipboard has been around since the beginning of Excel. I thought I had read an article about it by Joel Spolsky years ago but all I could find now was a quote by Joel answering a question on a forum. A superuser comment quoted Joel as an answer [1]. The archive link for the original discussion is here [2].

[1] https://superuser.com/questions/611854/prevent-excel-from-cl...

[2] https://web.archive.org/web/20160725070440/http://discuss.fo...

Maybe you saw it in his video called "You suck at Excel" [1]? It is in any case worth the watch.

[1]: https://youtu.be/0nbkaYsR94c

- Excel constantly wants to change cell contents, in the name of improving formatting. For example, paste a table with something that excel thinks are dates, it will convert it to dates, and show the data in some format. Formatting it back to text will give you the #days since epoch instead of the original text.

- Excel hates text cells containing numbers. It whines about it all the time and eagerly changes the data to what it thinks it should be.

- Excel doesnt get it if a sheet contains a data table with consistent formatting. Just recognize it and store it internally as a small Infile DB. Often, an Altertx table will blow up 100-fold when exported to Excel.

> Excel constantly wants to change cell contents, in the name of improving formatting. For example, paste a table with something that excel thinks are dates, it will convert it to dates, and show the data in some format. Formatting it back to text will give you the #days since epoch instead of the original text.

This is definitely annoying behavior, but you do know that if you format the cell as text prior to pasting the data in it will keep it as text, right?

> This is definitely annoying behavior, but you do know that if you format the cell as text prior to pasting the data in it will keep it as text, right?

This only works sometimes, and I have no idea when or why.

The most reliable way I've found is to copy more than one column and use the text importer thing, where you can specifically mark columns as being text.

Some times yes, other times Excel will replace the format you set with what it guesses from the data. I have no idea what triggers each behavior.

What's more interesting is reading those dates by the COM interface. Depending on how the user input the data, you'll get formated dates as text or seconds since the epoch as number.

I haven't tried it on Excel specifically, but most programs support ctrl+shift+v to clear formatting.

It clears formatting in incoming data, but doesn't prevent Excel from applying its own formatting afterwards.

I love Excel and it's one program that's always open on my computer. But the thing that still kills me after all these years is that every open Excel process shares the same undo stack.

Say you have have Excel Process A and Process B, and you make an edit in A, then an edit in B, then an edit in A again. If you try to undo the edit in B, it will instead undo the edit you did in A. Infuriating.

You can open separate Excel instances by holding ALT when starting it up (second time onwards). Not quite the same thing as you want, but think the undo behaviour you describe makes sense if you're working in two sheets that are linked in some way.

Can you elaborate on this? It sounds like it would solve my "I can't open two files with the same name" problem too, but I hold down ALT and double-click my second file in explorer and I get the "Properties" dialogue for that file. If I instead hold ALT and click on Excel itself, it opens a new window but it still seems to be linked to the same process (and gives the same error when trying to open the second file of the same name).

This should work on windows - I'm not sure about Macs: you press and hold the ALT key, then right-click the Excel icon in the Windows taskbar and click the Excel icon above the taskbar, but keep holding ALT down. It should pop up a window saying "do you want to start a new instance of Excel". You can then open your (same named) file in the new instance.

That works, thanks!

The clipboard being cleared when you do some not-paste action is pretty annoying. I assume it is this way though because if you alter the sheet in some way which intersects with the cells being copied paste might not know what to do. Like if you insert rows, or change a value, the thing you copied might have changed. It might not have but it would be more confusing I think to have Excel make than analysis that it changed in some meaningful way then clear the clipboard sometimes than to just always clear the clipboard.

Aren’t things that could change always defined by a formula? You already have two paste operations — by value or by function. It seems to me the reasonable thing is to simply follow through: if you paste by value, you paste whatever it was at the time of copy — if you paste by formula, you paste the formula... and whatever computed result.

Pasting the possibly modified value is, IMO, always undesirable; so you can ignore the possibility altogether

I think if Paste Values pastes something that is no longer on the screen that is weird. How long do you wait to clear the clipboard if that's the case? What if I copy some cells, then change everything about my workbook (changed sheet names, changed named range names, refreshed linked data, inserted rows in the middle of my copied range...), then paste, do you still expect it to paste the original values? Do you expect that most users understand what's about to happen? I think the whole question is avoided by clearing the clipboard earlier than might sometimes be desirable.

Paste Values should paste the values that it had at the time of copying, period. Other programs behave thusly. If I'm making an image in Inkscape, I copy a portion, delete it and then paste it, I get the value I copied even though it had since been deleted. Same as if I edited it after copying, like changing a color. Excel should mimic that.

So if the underlying value changes enough that pasting anything but the values doesn't make sense then what's the UI here? Ctrl+C does nothing but Ctrl+Shift+C gives you the option to paste values with the inappropriate ones greyed out? Does the dotted border on the copied text go away because ctrl+C isn't going to work or does it stay because some portions of paste special can still work?

I think the scenario for pasting formula is the same as for pasting values: paste what was there at the time of copying. Is there a situation where this doesn't make sense? I'd be fine with it being kinda-wrong in situations like the original cells referred to another cell but now there's been a new row inserted and those cells are elsewhere and the clipboard hasn't updated. That's what I would expect a copy to do. Excel allows references to cells in other spreadsheets and if you edit the other spreadsheet, then the references in your first spreadsheet don't update to reflect that, so the clipboard would just act like another spreadsheet.

Edit: the better way to say this is that "copy (do something) paste" should always act the same as "copy, paste on a new blank spreadsheet, (do something) and then copy and paste that onto the original spreadsheet".

It'd be cool if it worked like that but I'm sympathetic to it not

I'm not sure I am; the current behavior is almost always surprising and unexpected. You effectively need to keep a list in your head of everything that will clear your clipboard, which is really a list of everything that could ever cause problems, more often than not a flag that is entirely unrelated to your actual intent/action.

You might as well have it clear the clipboard if your next action isn't immediately paste and be done with it. Just don't bother to set the expectation that pasting will work like elsewhere.

The dotted border is kind of the problem. It represents an active connection between the current spreadsheet and what was copied, and that connection shouldn’t exist.

When implemented normally, ‘copy’ puts a copy of the selection on the clipboard; ‘paste’ takes whatever was on the clipboard and puts it at the current location.

Once you copy, it shouldn’t matter what you do on the spreadsheet, because you’re pasting from the clipboard.

Normal behaviour can be emulated by copy+pasting into a blank spreadsheet when you want to copy, and then copy+pasting from there when you want to paste.

But then you’ve just turned that spreadsheet into what the clipboard is supposed to be.

Had you seen this link elsewhere in this thread? https://superuser.com/a/623142/85273

The one that drives me insane is that while editing a cell formula, that using the arrow keys move cells (and update [read: wreck]) the formula rather than moving the cursor within the formula you're editing.

Just hit F2 while editing a formula to toggle between Edit and Enter modes, one of which will behave as you expect. The other mode, which you hate, is very useful when you want to add references to other cells into your formula

> is very useful when you want to add references to other cells into your formula

I really wish the default is the other way around. When I do want to add other cells, more than half the time there is a better way than using arrow keys.

Yes, the edit mode (using arrows to create formulas) is actually something I really find convenient.

To me it is a feature, not a bug. But yeah, F2 is fairly easy to use once you get used to it. Its like using VIM, the shortcuts seem annoying to the uninitiated, but once you get understand how everything works, the power can really speed things up and you appreciate it.

If something is driving you nuts, ask on a forum, because excel is EXTREMELY flexible and used in some pretty high volume environments and the original developers really paid attention to user input (for a long while that stopped).

In many cases you are just not fully up to speed with the features in windows / excel.

I haven't used Excel heavily in at least 5 years. Google Sheets is much better in most ways, especially sharing and collaboration.

Google Sheets has a killer feature that few know about: you can attach Apps Script (JavaScript) to a sheet [1] including a "fetch" function to make API calls [2].

While Excel has a show-stopping preposterously ridiculous behavior: auto-converting long number strings to "scientific notation" (and even data loss rounding!!) [3].

1: https://developers.google.com/apps-script/guides/sheets

2: https://developers.google.com/apps-script/reference/url-fetc...

3: https://excel.uservoice.com/forums/304921-excel-for-windows-...

Google sheets also has some large limitations and doesn’t contain anywhere near the feature richness of Excel, so it’s a little tit for tat.

For Power Users of excel, google sheets misses the following:

* Inability to import large datasets - number of cells limit is much smaller and there is no ability to support larger datasets (eg powerquery in excel). This means while sheets can typically handle 100k rows (with 10 columns), excel can handle well over 10 million (with hundreds of columns).

* No dynamic array formulas - in google sheets they are all “drag down” while in excel formulas can be created which expand/shrink according to the data

* No ability to handle relationships and measures - Excel has the capability to define relationships between data similar to a relational DB allowing for more powerful querying.

* You can’t send a google sheet on email - important for big companies!

In terms of a JavaScript API - excel has one for their web app (also called app script!), it’s just not supported on their desktop app yet (although this is being planned).

> You can’t send a google sheet on email - important for big companies!

Why send an Excel via mail? If you make changes, you have to send the file again and you can't collaborate with others.

That's what the share function in Google Sheets is for :) If that doesn't work, you can still download the file as .xlsx and send it via mail.

You've been able to share Excel Sheets online for a while now...

Then my point still stands, why the need to send Excel files via mail? :)

Because it’s easy, convenient and an accepted standard in business. Many orgs also have policies that disallow direct sharing outside the org, or are blocked from using google docs, but all these people can receive an excel file on email.

So that they can use it without having to request access.

Well Excel has VBA which makes your API calls mute bud.

Excel does not actually do a simple clipboard copy. It copies the cell references and when you paste, it adjusts the cell references and formulae and puts them in the destination. If something changes the structure of the spreadsheet between copy and paste, it cannot guarantee that the pasted formulae will still work. For that reason, it clears the copy buffer.

It’s a complex problem to manage all of the cell references in a dynamically changing spreadsheet and early on the Excel team chose to keep it simple.

You can see a version of this if you do a cut > paste. The source does not move to the clipboard, it just gets highlighted. When you paste, Excel does a move operation that adjusts the references on the fly.

You can open two spreadsheets of the same name if you hold the alt key while opening them.

This makes excel open in a separate instance

Reply on news.ycombinator.com