Wouldn't it be handy to enter data into Excel without typing it? What if entering repetitive content was just one click away? How about entering all the names of your coworkers without typing them in each time? The good news: These and other timesaving tricks are not only possible, but simple to set up in Excel. Here's how.
1. Use Custom Lists to Make a Calendar Shortcut
How often have you typed the days of the week or the months of the year in Excel? These entries are so common that Excel stores them as custom lists. To use them, start by clicking in the cell where the first day of the week or the first month of the year should appear. Then type the first entry; for example, type Monday.
You can start with any day or month name, and you can type in abbreviations for either, such as Mon for Monday or Jan for January.
2. Create a Custom List
Choose File, Options, Advanced, General, and click Edit Custom Lists. Click New List in the 'Custom lists' box, and then type the list entries--one per line--in the 'List entries' box. Click Add to create a Custom List of your entries.
In the future you can type any one of your list entries--it does not have to be the first item in the list--and drag the fill handle to fill a range of cells with your list entries.
3. Drag to Fill Cells With Series Data
To enter the dates of the days in April, start by typing 1-Apr-2012 into a cell (or use your preferred date format). Click in that cell, and then click and drag its fill handle down the column or across the row. As you do so, the tooltip over the mouse cursor shows the date that will appear in each cell. Let go of the mouse button when you reach your desired end date.
To enter the numbers 10, 20, 30, and so on, start by typing the number 10 in a cell. Type the number 20 in the cell below it or to its right. Drag over both cells to select them, and then drag the fill handle in the bottom right of the selection down the column or across the row. Stop when the tooltip shows the last number you want to enter. Excel recognizes the numbers 10 and 20 as the first two values in the linear series 10, 20, 30, and so on, and it enters the series values for you.
In a similar way, you could type 2-Apr-2012 into one cell and 9-Apr-2012 in the cell below. Next, select both cells and then drag the fill handle to create a series--namely, the dates of sequential Mondays starting with the first Monday in April.
4. Fill a Range With Consecutive Numbers
When you do that, the tooltip will show the number 1 regardless of how far you drag, so you need to guess where to stop. When you let go of the right mouse button, a menu appears. In that menu, click Fill Series, and the consecutive-number sequence will appear in the selected cells.
5. Enter Days of the Week (and Skip Weekends)
In some situations you will need to fill a range with dates that represent the days Monday through Friday, skipping the weekends.