The common cliché that “Time equals money” seems a bit out of place in the non-profit world. Yet when money is not the end goal, the effective use of time can actually be our stock in trade. Entering lots of data into a spreadsheet can not only be tedious and mind-numbingly boring, it can also be fraught with errors. Using one or more of these tips speeds up the process and may also reduce errors. Of course, the speediest data entry method is likely importing data from another source, such as a contact management system or general accounting system or even linking directly to an external source through an API or ODBC. When that’s not an option, I love to make use of several Auto-fill methods in Excel.
Repeat data that already exists in earlier cells
Begin typing an entry of letters or letter – number combinations. When Excel recognizes the new entry as the same as an earlier one, it will begin auto filling. In this example, typing the letter “A” in the sixth row prompts the autofill of Apples. Press Enter to accept Apples or continue typing for a different value, such as Apricots.
Note that Auto Fill will not work in cells containing only numbers, dates, and time.
Fill Down, Fill Up, Fill Right, Fill Left
Copying and pasting from one cell to one or more other cells is often the default action to duplicate data or formulas. However, if the source data or formula is adjacent to the recipient cells, the Fill option is much quicker. The Fill option is in the Editing group of the Home tab.
Select the source cell and then the recipient cells. Then select the following options:
- Source cell on top. Recipient cells below — Use Fill Down (shortcut – CTRL-D).
- Source cell on bottom. Recipient cells above — Use Fill Up
- Source cell on right. Recipient cells to the left — Use Fill Left
- Source cell on left. Recipient cells to the right — Use Fill Right (shortcut – CTRL-R)
Using a Cell’s Fill Handle
The Fill Handle actually provides 3 options in one. First, hovering over the bottom right corner of a cell displays a small cross + which is called the fill handle.
Click on the Fill handle and drag down or to the right and the additionally selected cells will be filled with the originally selected cell (either data or formula).
Releasing the mouse and clicking on the small auto-fill option box on the bottom left will reveal several options: Copy Cells, Fill Formatting only, or Fill without Formatting.
Second, with multiple rows of data, often a formula is created on one row that will be copied to all rows. Copy/Paste or Fill Down are my go-to methods, but I recently learned a trick that is even quicker. In the example below, the extended price formula in row 1 needs to be filled in every row. Hover over the bottom right of cell D2, then double click on the Fill Handle (+). The formula in that cell will Auto Fill through all of the rows of data. Super quick!
Third, often a series of data needs to be created in a column or row of cells. This series might be days of the week, months of the year, sequential years, or even a unique series of your own, such as general ledger categories. Begin with two cells completed as the first two entries of the series. Hover over the bottom right corner of the second cell. Click on the fill handle (+), drag up, down, right or left and the series will fill as many cells as are selected.
Excel’s known series include days of the week and months of the year. Numbers, dates, and time will increment in just about any normalized pattern, such as bi-weekly payroll dates or end of quarter tax deadlines. To create a unique series such as budget areas (Sales, Marketing, Operations, Finance, Human Resources):
1. Select Options from the File Menu. Then select Advanced, scroll way down to the General section. Click on the Edit Custom Lists button.
2. Now begin typing in the List Entries your unique series, such as Sales, Marketing, Operations, Finance, Human Resources. Click Add and then OK. This list is now available to Auto Fill, not just in this worksheet but in other worksheets created in the same installation of Excel.