Author: Oscar Cronquist Article last updated on April 16, 2009

Here is my first attempt to create some kind of  useful macro.

Question: How do I create a list were I have a blank cell at the top, where I can enter data. After i press enter it will drop down 1 cell. So newer entries on top and older entries farther down the list.

Answer: Instead of inserting a new line every time a value has been entered, I have created an extremely simple macro.

How to create macro in Excel 2007
Enable developer tab in ribbon:

  1. Press Office button
  2. Click Excel options
  3. Click Popular
  4. Click "Show developer tab in ribbon"
  5. Click OK!

Create macro

  1. Go to developer tab in ribbon
  2. Click Record Macro
  3. Type a Macro name and press ok
  4. Select the first cell A1
  5. Right click on A1
  6. Click Insert...
  7. Click "Shift cells down"
  8. Click "Stop recording" on ribbon

Connect macro to a button

  1. Click Insert on Developer tab
  2. Click Button (Form Control)
  3. Draw a button on excel sheet
  4. Assign your macro to your button
  5. Press ok!
  6. Right click on button and click "Edit text"
  7. Change text to something like "Insert new row"

Now try your new macro.


Click Button and see how a new cell is inserted above A1.

Download excel sample file for this tutorial.
(Excel 97-2003 Workbook *.xls)
If your list is bigger and you want more than one cell to be inserted. Select more cells, see "Create a macro" , number 4.