Spreadsheet software is supposed to make life easier, not torture
you with endless hours of data entry. Before you curse Excel and wish
that you were working back in the good old days of paper ledgers,
imagine how much better it could be.
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.
Excel can enter months of the year and days of the week automatically.When
you click in this cell, you will see a small black square, called the
fill handle, in the bottom-right corner. Click, hold, and drag the fill
handle in any direction, and Excel will fill in the accompanying cells
with the days of the week, in the proper order. As you drag, a
tooltip--a small, gray text box--will appear by the mouse cursor,
showing which entry will appear in the cell you are hovering over.
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.
You can create your own custom list in Excel for filling in a range of cells later.If
you have to type in certain sets of data repeatedly, such as the
locations of your offices or the names of coworkers, put those items in
a Custom List that Excel can fill out for you. Your custom lists work
the same way as the day and month lists that ship with Excel.
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.
Seed a cell with the starting date, and Excel can fill a range with consecutive dates.From
time to time when you are working in Excel, you will want to enter a
series of dates or numbers. You might need to enter the dates of the 30
days in April, for instance, or the numbers 10, 20, 30, and so on. You
can use the fill feature in Excel to do this automatically.
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.
When entering consecutive numbers, you need to drag the fill handle with the right mouse button.When
you need to fill a range of cells with a series of consecutive numbers,
you will find that Excel behaves unexpectedly. If you type 1 into
a cell and drag its fill handle, Excel will fill all the cells with the
number 1, not the series 1, 2, 3, 4, and so on as you might expect it
to. To create a series of consecutive numbers, type 1 into a cell, but drag its fill handle using the right mouse button this time.
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.
Use the Fill Weekdays feature to fill a range with dates minus the weekends.To do that, type the first day of the week, such as 2-Apr-2012,
into a cell. Then click in that cell and drag the fill handle with the
right mouse button down the column or across the row. You will have to
guess about where to stop; the tooltip won't show the correct value
because you haven't yet chosen a fill option. When you let go of the
mouse button, in the shortcut menu that appears, click Fill Weekdays. You will see the cells fill with the appropriate Monday-through-Friday dates.
Similar Articles:
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.
Excel can enter months of the year and days of the week automatically.When
you click in this cell, you will see a small black square, called the
fill handle, in the bottom-right corner. Click, hold, and drag the fill
handle in any direction, and Excel will fill in the accompanying cells
with the days of the week, in the proper order. As you drag, a
tooltip--a small, gray text box--will appear by the mouse cursor,
showing which entry will appear in the cell you are hovering over.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
You can create your own custom list in Excel for filling in a range of cells later.If
you have to type in certain sets of data repeatedly, such as the
locations of your offices or the names of coworkers, put those items in
a Custom List that Excel can fill out for you. Your custom lists work
the same way as the day and month lists that ship with Excel.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
Seed a cell with the starting date, and Excel can fill a range with consecutive dates.From
time to time when you are working in Excel, you will want to enter a
series of dates or numbers. You might need to enter the dates of the 30
days in April, for instance, or the numbers 10, 20, 30, and so on. You
can use the fill feature in Excel to do this automatically.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 entering consecutive numbers, you need to drag the fill handle with the right mouse button.When
you need to fill a range of cells with a series of consecutive numbers,
you will find that Excel behaves unexpectedly. If you type 1 into
a cell and drag its fill handle, Excel will fill all the cells with the
number 1, not the series 1, 2, 3, 4, and so on as you might expect it
to. To create a series of consecutive numbers, type 1 into a cell, but drag its fill handle using the right mouse button this time.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.
Use the Fill Weekdays feature to fill a range with dates minus the weekends.To do that, type the first day of the week, such as 2-Apr-2012,
into a cell. Then click in that cell and drag the fill handle with the
right mouse button down the column or across the row. You will have to
guess about where to stop; the tooltip won't show the correct value
because you haven't yet chosen a fill option. When you let go of the
mouse button, in the shortcut menu that appears, click Fill Weekdays. You will see the cells fill with the appropriate Monday-through-Friday dates.
0 comments:
Post a Comment