- Types of Data You Enter into Excel
- Entering Different Types of Data into a Cell
- Controlling the Next Cell Selection
- Using Copy, Cut, Paste, Paste Special to Enter Data
- Using Text to Columns to Separate Data in a Single Column
- Inserting Symbols and Equations into a Cell
- Using Web Queries to Get Data onto a Sheet
- Using Series to Quickly Fill a Range
- Editing Data
- Working with Tables
- Fixing Numbers Stored as Text
- Spellchecking Your Sheet
- Finding Data on Your Sheet
- Using Data Validation to Limit Data Entry in a Cell
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Using Series to Quickly Fill a Range
The fill handle, shown in Figure 3.12, can speed up data entry by completing a series for you. Excel comes with several preconfigured series, such as months, days of the week, and quarters. You can also add your own series, as described in the “Creating Your Own Series” section later in this chapter.
Figure 3.12. Use the fill handle to quickly fill in a series.
Extending a Series Containing Text
To extend a series containing text, enter the text you want the series to start with and press Ctrl+Enter to exit the cell, but keep it selected. Place your cursor over the lower-right corner of the cell until a black cross appears. Click the mouse button and as you hold it down, drag the fill handle.
You don’t need to start at the beginning of the series. You can start anywhere in the series and Excel will continue it, starting over if you drag the handle long enough. For example, if you begin a series in A1 with Sunday and drag the fill handle to A8, Sunday will appear again, repeating the series.
CAUTION
If the text series contains a numerical value, Excel instead copies the text and extends the numerical portion. For example, if you try to extend Monday 1, you get Monday 2, not Tuesday 2. But if you type in Jan 1 and extend that text, Excel extends the numerical portion until Jan 31, then switches to Feb 1. It does this because dates are actually numbers. Seeing the date as Jan 1 is a matter of formatting.
Extending a Numerical Series
If you try to fill numerical series based off of a single cell entry, Excel just copies the value instead of filling the series. There are four ways to get around this:
- Enter at least the first two values of the series before dragging the fill handle.
- Hold down the Ctrl key while dragging the fill handle.
- If there is a blank column to the left or right of the numerical column, include that column in your selection when dragging the fill handle down. For example, if you enter 1 in A1 then select A1:B1 (B1 is blank); when you drag the fill handle down, the series in column A is filled down.
- Hold the right mouse button down while dragging the fill handle and select Fill Series from the context menu that appears when you release the mouse button.
CAUTION
If you hold the Ctrl key while trying to extend a text series, Excel copies the values instead of filling in the series.
Creating Your Own Series
You can teach Excel the lists that are important to you so that you can take advantage of the series capabilities in Excel. You can take almost any list of items on a sheet and create a custom list for use in filling or sorting.
To create a custom list, follow these steps:
- Create your list on the sheet and select the range.
- Go to File, Options, Advanced, General, Edit Custom Lists.
- The range you selected is already in the Import List from Cells field at the bottom of the Custom Lists dialog box, so click Import.
- The list is added to both the Custom Lists and List Entries list boxes, as shown in Figure 3.13. The next time you type an item from your list and drag the fill handle, Excel fills in the rest of the series for you.
Figure 3.13. Create a custom list for use in filling in a series.
)
){kind=link}