Excel – How to Consistently Insert Rows from Top of Page

microsoft excel

I am trying to setup a workbook that will contain many rows of data. In order to keep things chronological I would like to consistently (or always) insert a new row of data at the top of the spreadsheet, instead of having to scroll down to the last entry. I should end up with the most recent data on top.

The question is, can I do this without having to right click on the first row (in between the header row) and chose Insert Row or right click on the cell Choose Insert, Entire Row. I'm envisioning here a way to setup like a blank form row that I could enter my info for the entire row and hit a button (or look for an Enter key in the last column's cell) and this would insert that row information into that top row that I keep referencing. Is this possible? Am I dreaming here?

Best Answer

Yes sir. You can indeed. All you need to do is to record a macro in excel that will do this job for you and paste the data to the first row.

Copy the data you want to paste and start a macro. you can assign a macro to the custom button on the ribbon. this way it will only be single left mouse button click.

Macro should look similiar to this:

Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveSheet.Paste

You will find a tips on how to use buttons in excel here: click And here is a guide to add it to the quick access toolbar : click

Related Solutions

Excel – How to insert a row into an Excel spreadsheet and update the referenced cells in an external workbook

Option 1: Skip the requirement that column G must be alphabetized. Delete rows by marking them as deleted and always insert rows at the end of the sheet. This of course to keep the external references stable.

Option 2: Change the external references to a key value (unique identifier for a specific row) instead of a specific cell and use VLOOKUP or similar to retreive values from column G.

=VLOOKUP("uniqueId";[WorkbookA]Sheet1!A1:G1000;7;FALSE)

Excel – How to insert empty rows in Excel when I have copied rows in the clipboard

Unfortunately it is not really possible: you can achieve insert blank rows, even if you have copied some rows by clicking on Home->Insert->Insert Sheet Rows:

enter image description here

This will insert the blank lines as you wish. However, it also voids your selection, i.e. the source rows are not in the clipboard anymore.

Overall however, inserting the copied rows/columns instead of blank rows/columns makes a lot of sense, as this is what one usually does right after copying. And adding blank rows afterwards is always easily doable.

I personally found it most efficient to do this with keyboard shortcuts and a few advanced tweaks:

Step 1: Select source

Ctrl-Space - Select column
Shift-Space - Select row
Shift-Space - ArrowKey - Extend selection to get multiple rows/columns

Step 2:

Copy (Ctrl-C) or
Cut(Ctrl-X) or
Delete (Ctrl--)

Step 3: Insert

Select target row/column similar to step 1
If only one column/row is selected, Excel will insert the source as selected. However, multiples of the selected rows/columns can be selected and will then be filled, e.g. if you copy 3 rows and select 12 rows, the it'll be filled 4 times
Either insert (Ctrl-+) new columns/rows, or overwrite existing one (Ctrl-V)

Esp. Ctrl-Space, Ctrl-X and then Ctrl-+ is extremly usful to quickly move columns/rows!

Best Answer

Related Solutions

Excel – How to insert a row into an Excel spreadsheet and update the referenced cells in an external workbook

Excel – How to insert empty rows in Excel when I have copied rows in the clipboard

Related Question