This is a common question or problem that seems to come up with Excel users (well, it does for me anyway).
How to insert a blank line into every other row in your dataset? Many of the available solutions require the user to write some code or macro, which is fine, but some users are not comfortable with VBA. There is an easy way to achieve the same result in Excel without needing to write VBA. But I can also give you a code to do the same…if you want.
Ok, let’s insert some blank rows into our data set. Our data set is configured as the following extract.
Date: Week: Day: Day of the week: User
06/01/15 1 1 11 AmyH
06/02/15 1 2 12 JoeP
06/03/15 1 3 13 PedroH
05/05/15 1 5 15 SuseM
First, we need to insert a new column to the left of existing Column A (we assume our existing data starts at A1), so our data is now 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 to the last row of your data set.
You can press CTRL+SHIFT+DOWN arrow once to do this quickly. (Sweet this twice to select the entire column, including cells with no data. Welcome to the Excel Shortcut Bonus Tip!
-
Select Home tab – go to Edit group select Fill Series
-
In the Fill series, click 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 inserted values
-
Go to the next free cell in your data series
-
Press CTRL+V to paste the values
-
Highlight the entire data area, including new rows with just a number appearing in Column A
-
Home Tab – Sort & Filter – Lowest to Highest in Column A
Your blank lines will be inserted in an instant.
You can then simply delete the Helper Column A created in the first few steps of the process and your job is done. Excel has used row numbering to allow blank rows to be inserted into the worksheet without any VBA programming.
