A Quick Excel Copy & Paste Trick

It’s been a while since I shared an Excel trick on here and this is one that didn’t make it into the books although I do find it useful.

As a refresher: If you want to copy an entry and paste it into other cells, an easy way to do so is to click on the cell with the information you want to copy, use Ctrl + C, then highlight the cells where you want to paste that information and use Ctrl + V or Enter.

Another option if the cells where you want to copy the information are located next to the cell with the information you want to copy is to highlight the cells you want to copy, left-click on the bottom right corner and drag down or drag to the right.

(If there’s already data in other columns and you want to copy downward you can just double left-click instead of click and drag.)

I tend to use the click and drag option a lot, but it fails me sometimes, especially when I want to copy date information because it gets too clever.

For example, in a lot of my sales tracking spreadsheets I add a column for month and a column for year and then need to copy that down however many sales entries there are for that vendor for that month.

When you click and drag with month and year information, Excel treats the data as a series by default and does the following:

Excel Copy Paste Default

I wanted every entry to be April 2019, but Excel in its wisdom advanced the month and the year by one for each row.

It turns out you can fix that by clicking on the Auto Fill Options image at the bottom right corner and changing it from “Fill Series” to “Copy Cells.”

Auto Fill Options

You then get what I actually wanted:

Excel Copy Paste 2

Another way to copy the exact same value to multiple cells in Excel is using Ctrl+Shift+Enter. Highlight all of the cells that you want to have a specific value, then enter that value in the first cell of the highlighted range, like so:

All Cells Highlighted

Then instead of using Enter, use Shift+Ctrl+Enter. The value you input into the first cell will be copied to all of the highlighted cells.

It only works for one value at a time, though. So in my scenario above I’d have to do the month first and then have to do the year separately.

Both of these tricks work in Excel 2013, which is what I’m working in, so I’d assume they work in all versions of Excel beyond 2013 as well. They may or may not work in prior versions of Excel.

Edited to add that now through June 29, 2019 you can get a 15% discount on any of the ebook versions of the Excel Essentials titles on Barnes & Noble by using promo code BNPEE15.


Author: M.L. Humphrey

M.L. Humphrey is a former securities regulator, registered stockbroker (although only briefly), and consultant on regulatory and risk-related matters for large financial institutions with expertise in the areas of anti-money laundering regulation, mutual funds, and credit rating agencies. Since 2013 M.L. has also been a published author under a variety of pen names and across a variety of subjects and genres.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s