50 Fill Cells with Data
Calc often predicts what data you want to enter into a sheet. By spotting trends in your data, Calc uses educated guesses to fill in cell data for you. Calc uses data fills to copy and extend data from one cell to several additional cells.
Before You Begin
39 Create a New Spreadsheet
45 Edit Cell Data
52 Enter Calc Functions
| || |
Fills ” The automatic placement of values in sheet cells based on a pattern in other cells.
One of the most common data fills you perform is to use Calc's capability to copy one cell's data to several other cells. You might want to create a pro forma balance sheet for the previous five-year period, for example. You can insert a two-line label across the top of each year's data. The first line would contain five occurrences of the label Year , and the second line would hold the numbers 2004 through 2008 . After entering all the data in year 2004's column, you only need to select that column and drag to fill in the remaining columns .
| || |
Calc fills in not only numbers in sequences but can also determine sequential years and other sequences (such as extending cells containing 3, 6, and 9 to new cells that hold 12, 15, 18, and so on). Calc also extends days of the week and month names . Type Monday in one cell and drag the fill handle to let Calc finish the days of the week in every cell you drag to.
Even if the only fill Calc performed was this copying of data across rows and columns, the data fill would still be beneficial. Calc goes an extra step, however: It performs smart fills, too. Calc actually examines and completes data you have entered.
Using Calc's fill capability to enter the years 2004 through 2008 across the top of the sheet requires only that you type 2004 under the first Year title and type 2005 under the second title. Select both cells and then drag the fill handle right three more cells. When you release the mouse button, Calc fills in the remaining years.
Type the Initial Label
Type your first label, such as Year
. This will be the value you will fill succeeding cells with. Although you could copy the value to the Clipboard with Ctrl+C
and then paste the value to other cells with Ctrl+V
, the fill handle is quicker to use.
| || |
Fill handle ” A small black box, at the bottom-right corner of a selected cell or range, that you drag to the right (or down or up) to fill the range of data with values related to the selected range.
Drag to Other Cells
Click and drag the cell's fill handle to the rest of the cells in which you want the label to appear. As you drag the fill handle to the right, Calc highlights each cell that will receive the filled data.
| || |
Combine text and numbers for more advanced fills. For example, if you extend Qtr 1 with the fill handle, Calc continues with Qtr 2 , Qtr 3 , and so on.
Calc Automatically Fills Cells
When you release your mouse button, Calc fills the remaining cells in the range with your label. Calc fills with numeric data, too, not just text inside cells. When you drag integers , Calc extends the range by increasing the integer by one.
To see Calc's smarter fill capability, you can type a month name and drag that month's fill handle across or down the sheet to fill in the rest of the months.
| || |
Integers ” Numbers without decimal points such as 0, “52,164, and 435 (also called whole numbers ).
Calc Fills in Month Names
When you release your mouse, Calc fills in the remaining month names for you.
| || |
If you drag the fill handle fewer than 11 additional months, Calc only fills in those months. Therefore, you would drag the January cell's fill handle down only five more cells if you wanted to show the months January through June only.
Type the initial year. Any single number, such as a year or any other number that does not have a decimal point, whose fill handle you drag will increment by one in each cell you drag the fill handle to.
Calc Fills in Remaining Years
When you release your mouse, Calc fills in the remaining years by incrementing the years for you throughout the range.