Note: Google has added this functionality to Sheets, so this Add-on isn’t necessary anymore.
Note #2: if you get the error “We’re sorry, a server error occurred…” you probably have merged cells that need to be split for this Add-on to work. Â Please see this post.
This Add-on provides the ability to:
- Insert a cell or block of cells, shifting the existing cell contents down or to the right
- Delete a cell or block of cells, shifting in content from below or the right
Here’s a link to the Add-on in the Google store. Â Or just search for “insert cells” when browsing Add-ons from Sheets.
After you install the Add-on, the Add-on menu will contain a new “Insert and Delete Cells” menu with the following items:
- Insert – Shift Down
- Insert – Shift Right
- Delete – Shift Up
- Delete – Shift Left
The following video demonstrates this functionality:
The code for this add-on is available in github.
Thank you, thank you, thank you!
You’re very welcome!
Great Tool
Works excellent!
Pingback: How to Insert and Delete Cells from Google Spreadsheets - Karl's Place
Oh, my gosh, thank you so much! I was just about to give up on using Google Docs for my spreadsheets because I was baffled–absolutely baffled–as to how I was not possibly able to shift only certain cells. You have saved Google from receiving a strongly-worded email from me (and me the effort of writing said email. I’m lazy). 😀
Thank you, Natalie!
Yes, thank you for this. I hope google has paid you handsomely for this essential addon. A question; are there any shortcuts for a mac?
I don’t think it’s possible to program shortcuts into these Add-ons – sorry.
And it sure would be nice to make some money from it, but the only way would be to charge you for it 😉
First, I’m really glad you made this. I’ve been frustrated by the omission of this feature from Google Sheets for as long as I’ve been using them, and now that I’m letting my Microsoft 365 subscription expire I’m about to be using them a lot more.
That said, I have a question about permissions that I’ve always wondered about, and you seem nice enough and knowledgeable enough that you might actually be able to answer it: the script says it needs permission to “View and manage your spreadsheets in Google Drive,” which More Info describes as the ability to:
* Create new spreadsheets
* View and modify existing spreadsheets
* Share spreadsheets with others
Does this mean that you as the script creator automatically now have access to all of my data that I have in my spreadsheets, and so I have to trust you from now until eternity not to look at it? Or only that you *could* see those things *if* you put such code into the script, so I only have to trust that you have not at the time I install it, unless you pushed that functionality in an update that I installed? (what is Google Apps add-ons update model anyways? Chrome extensions are updated automatically, unless they require new permissions, but I can’t find documentations for Google Apps add-ons).
Thanks!
Jordan,
Those are great questions! I wrote the add-on to solve a need that I had, so I can honestly say that I wrote it without completely studying the security model. But I think that add-ons that you enable could steal data from you. And the way I understand the update model, updates that I publish become effective for everyone who’s installed the add-on, so you could install a benign add-on and later it could be updated to be unsafe. Google does review the add-ons when first published, but I don’t know if they review updates. For this reason, I’m very choosy about what add-ons I install.
It’s hard to imagine a useful add-on that doesn’t change some info (or add some info) to your spreadsheet, and those actions trigger the permissions query that you saw. My add-on only manipulates the data that you highlight and the cells that must be moved for the functionality advertised – I can’t even tell who has installed the add-on.
Karl
Karl, thanks for the detailed answer.
If I may make a feature request, I would love to see “Insert Paste” work the same way it does in Excel, both with copy (where it is just an insert), and with cut (where it inserts to the new location while removing from the old one). Thanks!
That’s a great idea. I’m not sure I knew that Excel had that feature. I’ll look into adding it, but don’t know when I’ll be able to get to it.
It is awesome. From the Microsoft support page on inserting and deleting in Excel: “You can insert cells that contain data and formulas by copying or cutting the cells, right-clicking the location where you want to paste them, and then clicking Insert Copied Cells or Insert Cut Cells.
Karl,
Thank you for creating this add-on, are we not longer able to have these in the tool bar like you had in the older version of sheets?
Thank you again
With an Add-on, I can only put menu entries in the Add-on menu (or perhaps in a sidebar, which I might add).
In a day or two I’ll provide my code that creates custom menus, but you’ll have to add that code to every sheet individually, whereas the Add-ons are automatically available in all of your sheets once you install them.
You can manually paste the code from this github repo into the Script Editor to get back the top-level menus: https://github.com/karlkranich/gapps-insert-delete
re: the pasting function:
https://productforums.google.com/forum/#!topic/docs/P890onLgsMs
Sorely needed!! This add-on is a great start!
Thanks,
Great add on!
Thank you very much for providing a basic function that should have been part of Google Sheets in the first place. I think it’s ridiculous and kind of pathetic that Google’s developers did not think that this function might be essential in spread sheet operations.
Unfortunately circumstances have forced me to use this product as a shared experience with people from around the world and I was just about to abandon it when I found your add-on.
Once again many thanks Karl and I can stop pulling my hair out until the next daft problem.
Very nice!
good tool.
Thank you very much.
Great add-on! so useful! any chance of having a shortcut or a repeat function to this?
Thanks, Darius!
I haven’t been able to figure out how to create a shortcut, but a repeat function is an interesting idea. I’ll try to see how hard that would be.
Karl
Thank you …
You’re welcome!
great little add-on with appropriate permissions! saved me a lot of time:) thanks for the effort to create it and share it! ++ karma
You’re welcome!
Unfortunately in the Google Sheet, we can not delete single cell (or some cells) and make adjacent moving up or moving left .
This adds-on really helps. Recommended for who are using Google sheets.
And, thank you the developers.
Nice day to all.
good tool.
Thank you very much for you the developers .
You’re welcome! But I should tell you that Google is now adding these functions to Sheets. It’s in the free Google accounts right now, and hopefully it will be in G Suite (Google Apps for Business) soon.
What a relief! This works superb. Thank you, Karl.
You’re welcome! But I should tell you that Google is now adding these functions to Sheets. It’s in the free Google accounts right now, and hopefully it will be in G Suite (Google Apps for Business) soon.
Can I assign a value to random cells (vs. entire rows or columns?) using this add-on??
No, this add-on doesn’t assign values to cells. It moves around cells when you want to insert blank cells.
Very useful! Could you please suggest how to do it with shortcuts? It is cumbersome and not reliable to do it with mouse often.
Hello Andrey,
I have not figured out how to create shortcuts for Sheets plugins. Also, Google has added this functionality into the Edit menu, so I’m not supporting the plugin anymore. I should probably pull it from the plugin store…