There are some other handy sequences that you can create as well. One is the weekdays sequence which lets you fill a range of cells with weekdays, ignoring Saturdays and Sundays. To do this, type the first date, and this time right-click and drag on the fill handle with the right mouse button.
When you let go the mouse, a dropdown menu appears from which you can select options such as Fill Weekdays to fill the days with weekdays instead of days of the week. Also on this menu are other options such as the ability to Fill Formatting Only and Fill Without Formatting as well as Fill Days, Fill Months, and Fill Years. If you don't want to create a series, use Copy Cells to copy a cell's contents instead.
There are other circumstances in which Excel will create a series for you. For example, if you typically type Q. 1, Q. 2 and so on across a worksheet, Excel can do this for you. Type Q. 1 in the first cell and then drag its fill handle and you will see that Excel recognizes the entry and creates the series: Q. 1, Q. 2, Q. 3, and Q. 4 and then starts over again, repeating the sequence (it recognizes that there can only be four quarters in the series). This also works if you use Quarter 1 for example, but not for First Quarter, Second Quarter and so on, which you'll have to create as a Custom List instead if you need to use it.
»
Dropdown Lists
One of Excel's little known features is its ability to provide dropdown in-cell listboxes for data entry. Consider the situation where you have a worksheet with a column for the employee's name. If you have a list of say, 30 employees, you can simplify the data entry process by adding a dropdown list to every cell in the column that will be used for entering an employee's name.
To do this open the worksheet that you will be using and select a new sheet. On that sheet create a list of employee names, one after the other in a contiguous range of cells. When you are done select the range and name it by choosing the Formulas tab on the Ribbon, choose Define Name and then type a name for the list such as Employees and click OK.
Switch to the worksheet where you will be inserting the employees names and select all the cells in that column that need the dropdown list. Click the Data tab on the Ribbon, choose Data Validation, and choose the Settings tab.
From the Allowed dropdown list, choose the List Entry, and for the Source, type the list name prefixed with an equal (=) sign. For example, if your list is called, Employees, the Source should read =Employees. Click OK.
Now, whenever you click a cell in the range that you had selected earlier, a small dropdown arrow will appear to its immediate right. Click the arrow and a list of employee names will appear from which you can select the employee name to use for that cell. This makes data entry easier and ensures that the entries are all typed the same way.
Adding an employee or removing one is simply a matter of removing a row from the list by selecting the row that contains the name and clicking the Delete button on the Home tab. To add a new employee, click one of the rows in the list other than the first row and click the Insert button on the Home tab. This inserts a new row into the list into which you can type the person's name.
As you can see, you can spare your fingers the work of typing and fast track repetitive data entry in Excel a number of ways. It's all about working smarter and more effectively rather than working harder.
Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. She blogs at http://www.projectwoman.com/blogger.html.
Be sure to check out all of Helen's articles in the Exploring Office 2007 series:
