In a rapid response to a recent Excel Community article, Microsoft has introduced a useful new keyboard shortcut to allow a copied selection to be pasted as values with a single key combination. In contrast, a longstanding issue with the implementation of the Excel Tables feature is still unresolved after years of user feedback.
Well, we like to think that the articles on the Excel Community are influential, but even we were surprised when, within hours of the publication of our latest ‘speed Excel up’ article, Microsoft announced a small but significant enhancement to the ability to copy and paste using a keyboard shortcut.
In that article, we had included an example of using the Alt access key to navigate through the Paste Special command options to paste a copied selection as values:
To paste a copied selection as Values for example, you could use (sequentially):
Alt,H,V,V
No doubt realising instantly just how cumbersome it was to use 4 keys sequentially, Microsoft came up with a single shortcut (albeit, one that requires three keys to be pressed simultaneously):
Ctrl+Shift+V
When pasting cells containing formulae, these will be pasted as values and, where the source cells contain text or values, they will be pasted to match the destination formatting.
I would have demonstrated this using my desktop version of Excel, but even my Beta Channel edition doesn’t yet include the new shortcut. However, Excel Online does:
In the above screenshot, we have typed the value 123 into cell A1 and the formula = A1 into cell A2. If we copy the two cells and paste them into cell A10 using Ctrl+V, the formula will be preserved and the source formatting will replace the existing formatting. In contrast, using the new Ctrl+Shift+V combination pastes A2 as a value rather than a formula and doesn’t change the source formatting. We have used the FORMULATEXT() function to display the formula in the adjacent cell to the right of each original, and pasted, value. The #N/A error indicates that the cell to the left doesn’t contain a formula.
While we are on the subject of Microsoft reacting to feedback, for many years we have been trying to persuade Microsoft of the need to correct the bug in the implementation of Excel Tables that prevents rows being added when the Table is on a protected sheet. Microsoft used to use ‘UserVoice’ to host feedback suggestions and allow users to vote for the ones that they were particularly interested in promoting. When Excel stopped their use of UserVoice, the Tables bug correction feedback had received over 1,000 votes and over 160 comments. That number of votes was over 5 times higher than the next Tables topic. Microsoft has replaced UserVoice with its own feedback section of the Excel Forum:
The ‘Get Tables working on protected sheets (add rows, sort, filter, etc.)’ topic is now second in the Tables category with 267 votes. If you agree with the importance of this correction, please consider adding your vote:
Related links
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.