• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 4. Edit > Edit Fill

Edit Fill

The Edit Fill options fill the selected range of cells with the appropriate values based upon the value in the first cell in the range. With most of the fill options, Excel is basically just copying the value in the first cell (either the farthest right or top) and pasting it into the other cells in the selected range. These options work great if you want to use the same formula in each cell of a row or column, you can simply create the formula in the first cell and then use Edit Fill to copy it into each of the remaining cells in the range.

If you select the Series option, Excel fills the cells in your selected range with values that create the type of series you specify. For example, you can create a series of dates that are exactly 15 days apart.

Edit Fill Down

Fills the cells in the selected range with the value of the top cell in the range. You can also select Ctrl+D. For example, in Figure 4-6, you can use the same formula in cell G3 to calculate the monthly totals by highlighting the range of cells and selecting Edit Fill Down. As Excel pastes the formula in each cell in column G, the formula is adjusted to sum the appropriate cells. For example, the formula pasted in cell G4 is =SUM(B4:F4).

Figure 4-6. Filling Cells with a Formula


Edit Fill Right

Fills the cells in the selected range with the value of the first cell on the left. You can also select Ctrl+R. This is similar to the example shown in Figure 4-6, but the formula would be copied from left to right instead of down. For example, in row 15 you could sum the totals of each column by creating a formula =SUM(B3:B14) in cell B15 and then use Edit Fill Right to copy that formula into cells C15 through G15.

Edit Fill Up

Fills the cells in the selected range with the value in the cell on the bottom. For example, you could have placed the formula in cell G14 of Figure 4-6 and then used Edit Fill Up to copy the formula to cells G3 through G13.

Edit Fill Left

Fills the cells in the selected range with the value of the furthest right cell in the selection. This is basically the reverse of the Edit Fill Right. If Cell G15 contains the desired formula, use the option to copy it to cells B15 through F15.

Edit Fill Across Worksheets

Copies the selected range of cells across all of the currently selected worksheets. The values are copied to the same cells on all worksheets. When you select this option a dialog provides you the option of copying everything in the cell (including formatting), the contents, or just the formatting. This option is useful if you are trying to set up several different worksheets with the same format.

Selecting More than One Worksheet

You can only fill across multiple worksheets if you have more than one worksheet selected. To select multiple worksheets, hold down the Ctrl key and click on each worksheet tab.

Edit Fill Series

If you need to fill a range of cells with numeric values or dates that follow a specific pattern, you can have Excel create the values for you by selecting the Edit Fill Series option. The Series dialog, shown in Figure 4-7, provides the options to create a series of related values based upon the starting value and the step value. This option allows you to create a series of dates or numbers based upon a specific pattern. For example, if your cell contains the date 6/15/2000 you could fill the cells with dates that increase by one month by selecting the Month as the Date Unit and specifying 1 as the Step value.

Figure 4-7. Series dialog


  1. Series in. Indicates whether you want to create the series by filling across the selected row, or down the selected columns. If you select Columns the values in the first row are used as the starting values for the series; otherwise, the values in the first column are used to fill across rows.

  2. Type. Indicates the type of fill you want to use for the selected series of cells. There are four different types of fill available:

    1. Linear. Fills the selected cells with a linear series based on the values in the first cells of the selected range. The way the series is created depends on whether or not the Trend checkbox is selected. If you do not select the Trend checkbox the series is created by adding the value in the Step field to the value in the previous cell. For example, if the first cell contains the value 2, and the step value is 1.5, the series becomes: 2, 3.5, 5, 6.5, etc.

      If you select the Trend checkbox a linear trend is determined based on the values in the selected range of cells. Typically, all values in the range will be replaced with the appropriate trend values. For example, if the first cell contains a value of 2 Excel calculates the trend (or best-fit line) to fill the cells in the range with values that are evenly spaced so that they would create a line if placed on a graph. When you select the Trend checkbox, the values in both the "Step value field" and the "Stop value field" are ignored, and Excel calculates the trend based on the values that are currently in the range of cells. The values will be calculated so that they are evenly spaced and form a straight line if placed on a graph, where m is the slope of the least-squares regression line using the formula y=mx+b, as shown in Figure 4-8.

      Figure 4-8. Excel Creates a Series Based Upon the Selections on the Series Dialog

    2. Growth. Fills the selected cells with a growth series based on the values in the first cells of the selected range. The way the series is created depends upon whether or not the Trend checkbox is selected. If you do not select the Trend checkbox, the series is created by multiplying the value in the Step field by the value in the previous cell. For example, if the first cell contains 2 and the step value is 2 the series becomes 2, 4, 8, 16, etc.

      If you select the Trend checkbox, a geometric growth trend is determined based on values in the selected range of cells. Typically all values in the range of cells will be replaced with the appropriate growth trend values. When you select the Trend checkbox, any value that exists in the Step field is ignored and Excel calculates the trend values based on the values that are currently in the range of cells, as shown in Figure 4-8.

    3. Date. Fills the selected range of cells with dates. The way the dates increase in each cell is determined by the options selected in the Date Unit section. The date values are incremented based on the value specified in the Step field. You will want to format the cells as dates using the Format Cells option prior to selecting Edit Fill so that the dates display properly.

    4. Autofill. Fills the blank cells in the selected range of cells with the same data that exists in the selection. In other words, if the first cell contains the value 13.5 and the remaining cells are blank, that value is copied into all cells within the range; whereas, if the cells contained a series of data such as 2, 4, 6, that series is continued with the value of 8, 10, 12, etc. Any values in the Step field are ignored with the Autofill selection.

  3. Date unit. Indicates how the series of dates will be incremented within the cells; by days, weekdays, months or years. This selection is only available when the Date option is selected in the Type section.

    For example, if you wanted to, you could create a column that started at 1/15/2000 and increments one month at a time. You would type the date in the first cell of the range and then select the Month radio button for the Date Unit, specifying 1 as the increment value in the Step value field.

  4. Trend. Select the Trend checkbox to create a trend of data within the selected series of cells based on the existing values in the cells. The type of trend is based on whether the Linear or Growth option is selected in the Type section. When the Trend checkbox is selected, the Step Value field is ignored.

    When you indicate that you want to create a trend, Excel determines the appropriate values needed to create the type of trend selected. For example, if you specify the linear trend with a start value of 15 and a Stop value of 63, Excel will determine the step value needed to evenly space the numeric values out so that the cells are evenly incremented between the start and stop value.

  5. Step value. Indicates the value that should be used to step, or increase, the value in each cell within the selected range.

    For example, if you have a value of 12 in the first cell and you want to increment the cell values by 2.5, you would place 2.5 in the Step value field.

  6. Stop value. Indicates the value where you want the series to end. This can be either a positive or negative number, depending upon the type of series you are creating. If the selection of cells is filled before reaching the value specified in the Stop Value field, the series ends. If the series reaches the Stop Value prior to filling the selecting cells, the remaining cells in the selection are left blank. It is not necessary to specify a value in the Stop Value field; if it is blank the selected range of cells is filled with the specified series.

Sidebar 1. Using the Fill Handle

Excel provides the option to quickly fill a series of cells without selecting the Edit Fill menu option. This is accomplished by using the fill handle. The fill handle is the black square that appears in the bottom right corner of a selected range of cells, as shown in Figure 4-9. If you drag the cursor across that location, the cursor changes to a + sign. If you drag the + with the left mouse button, the series is continued when you release the mouse button, similar to selecting the Autofill option on the Edit Series command. If you drag the + with the right mouse button, when you release the button a menu displays allowing you to select the type of fill you want to use for the selected range of cells.


Figure 4-9. Using the fill handle option


Edit Fill Justify

Used only with text, it justifies the contents of the selected range of cells within the cells. In other words, the contents of the cells are merged together, if they fit within the width of the selected cells.

Merging Cells with Edit --> Fill --> Justify

If you select Edit Fill Justify and cells are merged together, the characters will be merged with no spacing between. For example, if the first cell contains the word "the" and the second cell contains the word "dog" the justification would join the two cells if the column is wide enough and the results would be "thedog."

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint