Ever wondered what the most used command is in Excel?
I have. In fact a few years back I searched for the answer on Google. I had thought that the answer would surely be ‘Undo’, especially when I think about how often I use the fabulous Ctrl + Z key combination. Amazingly I found a page (which sadly I’m unable to find now) that claimed that Undo wasn’t the most used…it was Paste, Ctrl + V.
When I thought about it this made sense. Just today I have copied and pasted information from documents into emails, from a web site to an email, from one document to another and even when creating this blog post.
However, what I do find is that many people don’t know that the Paste command offers more options that just a right-click, Paste or Ctrl + V.
In this blog I want to share with you 5 of the best Paste tips I know. Start implementing them now and you won’t believe how much time you save.
Tip #1 – Pasting without the column widths resizing
Perhaps one of the most frustrating things about copying a range of data from one worksheet into another is when you paste the data only to find all of the columns don’t resize with the data. You may then find yourself having to do a quick column-width fix just to be able to see the pasted content.
If you would like to paste your data and keep the column widths as well:
- First select the data to be moved or copied and then Cut or Copy the data.
2. Move to the new worksheet and select the cell you want to paste to BUT don’t right-click, Paste or press Ctrl + V. Instead go to the Home tab and click the drop-down arrow on the Paste button.
3. Select the Keep Source Column Widths option.
Excel will now paste your data, keeping the same column width as the original source data. So good!
Tip #2 – Using Paste, Transpose to change the list from vertical to horizontal
The Paste, Transpose command allows you to take a vertical (or horizontal) list and switch it.
To do this:
- First select the data to be transposed and then copy the data. Note: cut doesn’t work for this command but you can copy the data and then delete it after you have transposed it.
2. Move to the cell you want to transpose the data to and then from the Home tab and click the drop-down arrow on the Paste button.
3. Select the Transpose option.
Excel will now paste your rows into columns or vice versa.
Tip #3 – Pasting, excluding hidden columns or rows
Copying a data range that contains hidden data can be challenging. You may have tried this and found that the hidden data travels with the copied range.
Follow the steps below to copy and paste only the visible data – hidden cells are excluded. To copy and paste only the visible data:
- First select the data range, including the hidden rows or columns.
2. Press F5. This will open the Go To Special dialog box.
3. Click Special and then select Visible Cells Only. Click OK.
4. Now copy the selected cells and then paste them where required. Only the visible cells will be pasted.
Tip #4 – Pasting, without having to insert empty rows first
Copying or moving data normally requires you to create a space for the data in the destination area . This normally involves inserting new rows or columns prior to moving or copying the data. However using the Insert Paste command you can insert AND paste at the same time.
To do this:
- First select the data range you want to move or copy.
2. Cut or copy the data.
3. Now right-click the cell that will hold the top left cell of the pasted data. In the example above we want to move the data for Wednesday and Thursday into rows between Tuesday and Friday. Therefore we will right-click cell A3.
4. Now select “Insert Cut (or Copied) Cells”.
5. From the Insert Paste dialog box select Shift cells down.
The pasted data will be inserted into your worksheet and the existing data will be moved down to accommodate it, eliminating the need to insert rows prior to pasting.
Tip #5 – Using Shift + drag to move data
This is a fabulous trick that will have you moving data without having to use Paste.
- First select the data you want to move.
2. Press SHIFT and hold it.
3. Now take your mouse pointer to the edge of the selected area and click and hold on the outside edge.
4. Drag the selection to where you want to move the data. As you move a bar will be displayed.
5. Move the bar and place it where you want to move the data.
Now release your mouse. The selected data will move. An amazing time saver!
Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.
She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.
If you enjoyed this you may also enjoy...
Let me help you learn how to subtract in Excel. It’s simple when you’re shown how, but read to the end to make sure you understand what happens when you multiply and subtract, or divide and subtract in the same formula. This is a tricky one, so please don’t skip over it!
Need a hand to unhide columns in Excel? There are different methods or unhiding available to you, depending on the version of Excel you are using. I’ll take you through the method that will work on any version.
Learn the BEST Excel keyboard shortcuts. Perfect for Beginners and for those who want to save time and work smarter. It doesn’t matter if I’m delivering a Stage 1 or a Stage 3 Excel training course – when I’m demonstrating to the class I whip around the screen, format, select and modify and update using shortcut
If you share Excel files outside of your organisation you’ll enjoy this post where I share with you how to remove Excel formulas and keep the results so that you can confidently send a file knowing you aren’t disclosing confidential information inside of your formulas.
Learn how to move, copy and paste information in Excel. Once you have mastered Cut, Copy and Paste you will find yourself shifting data around easily and reusing copied data, saving you the need to recreate the same information again. All Windows applications have access to the Windows Clipboard. This is a special area used for
Learn how to add colour, borders and alignment settings to your Excel data to give your worksheet a more professional look and to make it easier for others to read.