7 ms·

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.

by tgb 6y ago

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?

sokoloff 6y ago
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.
- airstrike 6y ago
  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
  - jacurtis 6y ago
    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.
  - ska 6y ago
    > 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.
- temp667 6y ago
  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.
ant6n 6y ago
- 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.
- linuxftw 6y ago
  I haven't tried it on Excel specifically, but most programs support ctrl+shift+v to clear formatting.
  - ant6n 6y ago
    It clears formatting in incoming data, but doesn't prevent Excel from applying its own formatting afterwards.
- OldHand2018 6y ago
  > 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?
  - marcosdumay 6y ago
    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.
bhandziuk 6y ago
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.
- setr 6y ago
  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
  - bhandziuk 6y ago
    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.
    - tgb 6y ago
      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.
ModernMech 6y ago
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.
- jamses 6y ago
  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.
  - tgb 6y ago
    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).
    - jamses 6y ago
      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.
      - tgb 6y ago
        That works, thanks!
Balgair 6y ago
> - 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.
- myself248 6y ago
  Whoah.
- lifeisstillgood 6y ago
  Mind ... blown ...
- gizmodo59 6y ago
  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
- account4mypc 6y ago
  Is there a mac version? I tried a few combos of cmd/control but no dice
smhenderson 6y ago
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-clearing-copied-data-for-pasting-after-certain-operations-w https://superuser.com/questions/611854/prevent-excel-from-cl... [2] https://web.archive.org/web/20160725070440/http://discuss.fogcreek.com/newyork/default.asp?cmd=show&ixPost=3479 https://web.archive.org/web/20160725070440/http://discuss.fo...
- dragly 6y ago
  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 https://youtu.be/0nbkaYsR94c
fstarship 6y ago
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
pbreit 6y ago
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 https://developers.google.com/apps-script/guides/sheets 2: https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app https://developers.google.com/apps-script/reference/url-fetc... 3: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10374741-stop-excel-from-changing-large-numbers-actually?tracking_code=1160f7d09fd21066059ef9e0e59c3bf0 https://excel.uservoice.com/forums/304921-excel-for-windows-...
- Closi 6y ago
  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).
  - 4ggr0 6y ago
    > 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.
Tagbert 6y ago
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.

by tgb 6y ago

sokoloff 6y ago

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.

airstrike 6y ago
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
- jacurtis 6y ago
  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.
- ska 6y ago
  > 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.
temp667 6y ago
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.

ant6n 6y ago

- 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.

linuxftw 6y ago
I haven't tried it on Excel specifically, but most programs support ctrl+shift+v to clear formatting.
- ant6n 6y ago
  It clears formatting in incoming data, but doesn't prevent Excel from applying its own formatting afterwards.
OldHand2018 6y ago
> 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?
- marcosdumay 6y ago
  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.

bhandziuk 6y ago

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.

setr 6y ago
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
- bhandziuk 6y ago
  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.
  - tgb 6y ago
    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.

ModernMech 6y ago

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.

jamses 6y ago
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.
- tgb 6y ago
  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).
  - jamses 6y ago
    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.
    - tgb 6y ago
      That works, thanks!

Balgair 6y ago

> - 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.

myself248 6y ago
Whoah.
lifeisstillgood 6y ago
Mind ... blown ...
gizmodo59 6y ago
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
account4mypc 6y ago
Is there a mac version? I tried a few combos of cmd/control but no dice

smhenderson 6y ago

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-clearing-copied-data-for-pasting-after-certain-operations-w https://superuser.com/questions/611854/prevent-excel-from-cl... [2] https://web.archive.org/web/20160725070440/http://discuss.fogcreek.com/newyork/default.asp?cmd=show&ixPost=3479 https://web.archive.org/web/20160725070440/http://discuss.fo...

dragly 6y ago
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 https://youtu.be/0nbkaYsR94c

fstarship 6y ago

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

pbreit 6y ago

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 https://developers.google.com/apps-script/guides/sheets 2: https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app https://developers.google.com/apps-script/reference/url-fetc... 3: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10374741-stop-excel-from-changing-large-numbers-actually?tracking_code=1160f7d09fd21066059ef9e0e59c3bf0 https://excel.uservoice.com/forums/304921-excel-for-windows-...

Closi 6y ago
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).
- 4ggr0 6y ago
  > 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.

Tagbert 6y ago

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.