Inserting, copying, moving, or cutting and pasting rows and columns is something we do every day in Excel. And there are loads of ways we can perform these repetitive tasks.
Inserting Rows, Columns and Cells
We can use the icons in the Insert menu on the Home tab:
Or even the keyboard shortcut:
Ctrl+Shift+= (or simply Ctrl++ using the + on the number keypad)
If you have a column selected then Ctrl+Shift+= will insert a column to the left, if you have a row selected Ctrl+Shift+= will insert a row above, or if you have a cell(s) selected Ctrl+Shift+= will open the Insert dialog box so you can choose where to insert the cells:
Tips:
- Use keyboard shortcuts Ctrl+Space to select a whole column
Or Shift+ Space to select a whole row:
- Hold Shift and use your arrow keys to select multiple rows/columns to insert more than one at a time.
We can also use our mouse to insert a row;
- Select the row(s)
- Hold down the Shift key
- Hover your mouse over the fill handle until the mouse displays the double line/arrow symbol below
- Left click and drag down to insert a row (drag up to delete a row)
Tip: This works the same for columns and cells.
Thanks to fellow Excel MVP, Bob Umlas for teaching me that trick. I learnt it in his book; This isn’t Excel it’s Magic.
Deleting Rows, Columns and Cells
Similarly, we can use the Delete Cells icons on the Home tab:
Or the keyboard shortcut:
Ctrl+-
If you have a cell(s) selected, Ctrl+- will open the Delete dialog box so you can choose how and what you want to delete:
Tip: Select the row(s) or column(s) before clicking Ctrl+- to skip opening the dialog box and delete the entire row(s) or column(s).
Copying and Moving Rows, Columns and Cells
We can easily move rows, columns and cells with our mouse by left clicking and dragging the edge of the cell to a new location.
Hold down Shift while dragging to insert the row, column or cell. Your mouse pointer will change to an arrow and the insert point will be displayed by a thick green line:
Tip: To copy and insert a row, hold down Ctrl + Shift while left clicking and dragging. Your mouse cursor will show a small + symbol beside the arrow:
Share a Shortcut
What’s your favourite Excel shortcut? Share it in the comments below.
Power Query Lists
Peter
F2 – being only a reasonable typist, I often have to go back over what has been typed. I used to jump to the entry line, but since finding F2, even my typing has got better, so I actually use it less. Who knew!
Mynda Treacy
🙂 practice makes perfect. I like F2, too.
Jeff Tibballs
When typing in a cell Alt + Enter creates a new line without having to wrap text.
Mynda Treacy
Thanks, Jeff. That’s one of my favourites too 🙂
Muhammad
CTRL Z is my favorite shortcut
Mynda Treacy
+1 for Ctrl Z 🙂
Bruce Jones
Some mouse shortcuts I’ve learned about:
Right click on the boundary of the selected area and right drag to a new location brings up a context menu specifically for copy/move commands
Right click and right drag on the fill button in the lower right corner brings up a fill-specific context menu.
Double clicking on a selection edge will move the selection, in the direction of the clicked edge, to the end of the used range, same as END+Arrow. (I discovered that one from sloppy mousing.)
Using Control+Enter when entering data or a formula will populate every selected cell. Especially useful when the formulas use relative references as each cell in the selection will change the references appropriately.
Mynda Treacy
Great tips. Thanks for sharing, Bruce.
Pieter Stolk
In addition to the shortcuts already mentioned (by the way CTRL + ‘D’ is really handy, also for copying down a complete row of formulas):
CTRL + ‘.’ will activate another corner in your active array. This is handy if, for example, you want to check wether pasted data fits the way you would like to. Also you can quickly check if there is other data present besides the data you pasted.
CTRL + ‘1’ is a fast way to format data as numbers.
Pieter Stolk
Sorry, I meant CTRL + SHIFT + ‘1’ is a fast way to format data as numbers (not CTRL + ‘1’)
Mynda Treacy
Ah, I see. It’s actually Ctrl + Shift + ! so as not to get confused with the 1 on the number keypad.
Alex B
To convert data to a table
– Ctrl + T (English Language version – won’t work in all countries)
– Ctrl + L (The International version – Tables were lists and in VBA are still list objects)
To create a new workbook
– Ctrl + N
Mynda Treacy
Cheers, Alex. I use Ctrl+T and Ctrl+N all the time, too. I didn’t know about the international version of Ctrl+T though.
ILYAS QASIM
CTRL + 1 on any cell to format, it will bring up format cell Dialogue Box for me!!
Awesome!
Mynda Treacy
I use that one all the time too. Thanks for sharing, Ilyas.
Ilyas Qasim
Your welcome Mynda,
We all get huge keyboard shortcuts because of post you posted!
B.Frassek
CTRL+y repeats last action if possible
Also very useful with other MS products, such as WORD, PowerPoint.
But I guess everyone already knows it …
Mynda Treacy
I use F4 for that, but Ctrl+Y would be good on keyboards that make you press the FN key to activate the function keys. Thanks for sharing.
Kat
I’m shocked that you would completely leave out the right click of the mouse giving a shortcut for inserting and deleting rows and columns. Since most of us have our hands on the mouse – its the fastest way to operate instead of looking for an item in the ribbons.
Mynda Treacy
Shock and awe is kind of my style 😉 How funny. I rarely use the right mouse key for that. Thanks for reminding me.
Laszlo
My new favourite column shortcut: Ctrl + 0 = hide column
Ctrl + Shift + 0 = unhide column
Mynda Treacy
Nice. Thanks, Laszlo.
Stephen
Another, more obscure one, which I often fall back on is the key sequence Alt Down arrow.
When used in a cell under a block of data, this will display a list of unique entries from the column above that can then be selected. I bit like a ‘dynamic’ data validation list. The data doesn’t have to be filtered, or even in a table.
Mynda Treacy
Yes, I occasionally use that too. Great tip.
Stephen
+1 for Lesley’s tip – Ctrl ; to enter today’s date. Something I also use daily. I’ve even taken to including this as a comment in the heading of relevant columns on my worksheets to help communicate this to other users.
I also can’t count how many others I’ve shown the Shift drag trick above for moving rows.
Another one I find handy is Ctrl Shift * to select the current region of data.
But the most useful I find is the End key followed by the Down arrow to move to the end of a data block in a column (and the corresponding Up, Left, Right, arrow keys to move around data blocks). This is great for quickly identifying any empty cells within a data block. When used in conjunction with the Shift key, it helps in quickly selecting a range e.g. for formatting, deleting, etc.
Mynda Treacy
Hi Stephen,
Thanks for your tips. The End key is old school, from Lotus 1-2-3 days. I still use that too but many modern Excel users use Ctrl and arrow keys instead of end.
Mynda
Derek Jones
CTRL+* on a cell with data within a table of data selects that table area (quicker than doing CTRL+SHIFT+DOWN then CTR+SHIFT+ACROSS)
Mynda Treacy
Nice, Derek. I’ll be using that. Thanks 🙂
Greg
Not specific for Excel but use it with Excel a lot.
Windows key +right arrow key: Docks half screen to right half of monitor
Windows key +left arrow key: Docks half screen to left half of monitor
With two monitors, you can have four docked screens great for copying and monitoring email.
Mynda Treacy
Never knew that one, Greg. Thanks.
Bruce Jones
A few more Windows keys:
Win+Up maximizes the active window
Win+Down restores a maximized window or minimizes a restored window
What I use often:
Win+number opens the app pinned to the taskbar or activates that app if it’s running
Win+Shift+Left (or Right) moves the active window to the other screen.
Bill
Control & “; – copies the contents of the cell above.
Mynda Treacy
That’s a new one, Bill. I’ve always used Ctrl+D to copy the cell above. Ctrl+” copies the value without the formatting, whereas Ctrl+D copies the formatting too.
Liesie
Selecting all the cells below a section of a row you want to copy, and then using Ctrl+D, copies the contents of all the cells above. Ctrl+’ copies only the first cell.
Mynda Treacy
Thanks for sharing Liesie.
Lesley
Control & ; – puts the date in a cell – I use this everyday!
Mynda Treacy
Great tip, Lesley. Thanks.