Microsoft Excel 2007 tutorial: format data with fill handle or fill series


Format data with fill handle or fill series

Format data is set data in specific types of formatting that you like. For example, display number from 1 to 30 by entering the number only in the first cell, or display date time as 30-12-2013. You can also format data in selected cells to other formats such as aligning text to left, right, center, justify, setting text to bold, italic, underline, applying shading...etc.

I. Fill handle

When you enter student id, employee id or product id, you do not need type all id numbers. You can use the Fill handle to display the number, text or date time automatically. For instance, you want to display 1 to 9 in the ProductID column as the figure below:

Excel 2007 Product List

To do this task, do the following:

1. Enter number one in the cell A2 (under ProudctID column).

2. Click cell A2, then move mouse pointer to the lower right corner of the cell, and then drag down the Fill handle to the target cell.

Excel 2007 Fill handle tool

You will see the ProductID column displaying only number one in all cells that you filled.

Excel 2007 fill number in the product id column

3. Click drop-down arrow of the Auto Fill Options, and then select Fill Series.

Excel 2007 Fill Series option

You will get the result that you want.

You can use another way as the following:

1. Enter number one and number two in the cell A2, and A3 (under ProudctID column).

2. Click the two cells, then move mouse pointer to the lower right corner of the selected cells, and then drag down the Fill handle to the target cell.

II. Fill Series

You can use the Fill Series to increase or decrease the number or date time automatically. To use the Fill Series, do the following:

- Select the cell that you enter data.

- On the Home tab, in the Editing group, click drop-down arrow next to Fill, and then click Series.

Excel 2007 Fill Series tool

The Series dialog box appears:

Excel 2007 Series dialog box

On the dialog box, there are the following options:

- Rows: Select this option if you want to fill data in rows.

- Columns: Select this option if you want to fill data in columns.

- Linear: Increase or decrease values in the filled cells by adding the first value (the value that you enter in a cell) and the value in the Step value box continuously to the last value. The values that increase or decrease depending on the value that you enter in the the Step value box. The last increasing or decreasing value base on the value that you set in the Stop value box. For example, you enter 1 in a cell, select Rows option in the Series in group, select Linear option in the Type group, and fill 1 in the Step value box and 7 in the Stop value box.

Excel 2007 use Linear option

As a result, Excel fills 1 to 7 in the cells of a row as the figure below:

Excel 2007 fill number with linear type

- Growth: Increase or decrease values in the filled cells by multiplying the first value  with the value that you set in the Step value box continuously to the last value. The last value is limited in the Stop value box. For example, you enter 1 in a cell, use Fill Series to fill the number in a column, select Growth option in the Type group, and fill 3 in the Step value box and 30 in the Stop value box.

Excel 2007 use Growth option

You get the result the as the figure below:

Excel 2007 fill number with growth option

- Date: Select this option to sort date time by day, weekday, month, or year.

- AutoFill: If you select this option, Excel fills the filled cells with same value of the original value (the value that you enter in a cell).

- Day: Increase or decrease date by day.

- Weekday: Increase or decrease date by days of week.

- Month: Increase or decrease date by month.

- Year: Increase or decrease date by year.

- Trend: If you select this check box, some options in the Type group and the Step value and Stop value boxes are disable. See the figure:

Excel 2007 select Trend option

This option allows you fill data in rows or columns, and you can set data to increase or decrease in Linear or Growth type. To use the Trend option, you need to select the original value cell and the cells that you want to fill. For example, fill date time in a column as the figure below.

Excel 2007 Trend option example

To do this task, follow the following the steps:

1. Enter the first date time value in the cell.

Excel 2007 enter date time in a cell

2. Select the date time value cell (A1) to the cell (A7) that you want to fill with date time.

Excel 2007 select cells to fill date time

3. On the Home tab, in the Editing group, click drop-down arrow next to Fill, and then click Series.

4. On the Series dialog box, select Trend check box, and then click Ok button.

- Step Value: This box is used to set the starting increase or decrease value in the filled cell.

- Stop Value: This box is used to limit the last increase or decrease value in the filled cell.

Note: You can enter only integer or decimal number in the Step value and Stop value boxes.

 


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.