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:
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.”
You then get what I actually wanted:
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:
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.
M.L. Humphrey 