2015/02/23
EXCEL - Skipping Rows in AutoFill
In Excel, you can use the Autofill feature to automatically fill in the contents of a row or column of cells with either the same value or a range of values. For example, you may wish to have the cell range filled with 1, 2, 3, etc., or with 5, 10, 15, etc., In order to do this you "teach" AutoFill the pattern you wish the range to follow.
Using the first example above, type 1 in the first cell, and type 2 in the 2nd cell, select both cells, and using the small "handle" which appears in the bottom right corner of the selected cells, click and drag either across the rows or down the columns you wish to fill. The cells will automatically appear with the sequential range of values you have chosen.
But what if you wish to have Excel fill every other row with a value, leaving every other row blank, such as 1, blank, 2, blank, 3, blank, 4, etc.? You have to "trick" Excel to follow a different sequence.
Enter 1 into the first cell, leave the 2nd cell blank. Highlight both cells and drag across or down using the AutoFill handle as explained above. Excel assumes you want to increment the value and leave every other cell blank.
EXCEL - Custom Lists for AutoFill
If you have any repetitive information you frequently need to enter onto a spreadsheet, such as employees' names, practice groups, application software lists, etc., then setting up a custom list to use with the AutoFill feature saves so much time.
Click on File, Options, Advanced and near the bottom of the window, under General, look for Edit Custom Lists.
In the window that opens on the left-hand side, you will see all the existing lists. In the right-hand column enter the information that you require (e.g., a list of practice groups or department names). Once you have typed in the data you wish to include in your list, click Add. Click OK button at the bottom of the window and then OK on the previous Excel Options window.
To use your new Custom List with AutoFill:
- Type the first name on the list into a cell and press Enter. You can type any name in the list, but if it needs to be in that specific order then it is faster to enter the first one.
- Go back to the cell by clicking on it once.
- Hover your mouse over the selected cell until the black cross (AutoFill handle appears in the lower right hand corner)
- Click and drag the AutoFill handle across the row of cells to fill or downwards in the column. You will see the words in your list appearing as you drag.
- When you have dragged as far as you need to go, simply let go of the AutoFill Handle and your custom list appears.