This is a regular question or issue that seems to arise with Excel users, (well it does for me anyway).
How to insert a blank line in every other row of your data set?. Lots of the solutions that are available require the user to write some code or macro, which is all good, but some users are not comfortable with VBA. There is an easy way to achieve the same result in Excel without the requirement to write VBA. But I can give you some code to do the same thing as well… if you want it.
OK let’s get some blank rows inserted into our data set. Our data set is set up as the below extract.
Date: Week: Day: WeekDay: User
01/06/15 1 1 11 AmyH
02/06/15 1 2 12 JoeP
03/06/15 1 3 13 PeterH
05/05/15 1 5 15 SuseM
First we need to insert a new column to the left of the existing Column A. (we are assuming our existing data begins in A1), so our data now is contained in column B by inserting a new column A.
The next step is to enter the number one 1 in cell A1 and highlight Column A all the way to the last row of your data set.
You can hit CTRL+SHIFT+DOWN arrow once to quickly do this. ( Dow this twice to select the whole column including cells with no data. You are welcome for the extra Excel shortcut tip!.
- Select the Home Tab – go to the Edit Group select Fill Series
- On the Fill Series Hit OK.
- Column A should now be populated with numbers from 1 to the total number of rows to the end of your data set.
- CTRL+C to copy the inserted values
- Go to the next free cell under your data series
- Hit CTRL+V to paste the values
- Highlight the entire data area, including the new rows with just a number appearing in Column A
- Home Tab – Sort and Filter- Smallest To Largest on Column A
Your blank lines will be inserted- in a Flash!
You can then just delete the helper Column A created in the first steps of the process and your job is done. Excel has used the numbering of rows to enable blank rows to be inserted into the work sheet all without any VBA programming.