How Microsoft Excel Fills in Series of Numbers, Dates, etc.

/* Google Analytic Tracking Code*/ /* End of Google Analytic Tracking Code*/

How to Fill in Series of Numbers, Dates, or other built-in Series Items in Microsoft Excel

In Microsoft Excel, you can Use the fill handle to quickly fill cells in a range with a series of numbers or dates or with a built-in series for days, weekdays, months, or years. The following steps will enable fill such series into Excel cell:

1. Type in the starting value for the series in the first cell.
2. Type a value in the next cell to establish a pattern.
• For instance, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. If you want the series 2, 4, 6, 8..., type 2 and 4. If you want the series 2, 2, 2, 2..., you can leave the second cell blank.
3. Select the cells that contain the both starting values you have just typed.
4. Point the mouse to the lower-right corner of the selected cell(s) and drag the fill handle across the range that you want to fill.

5. To fill in increasing order, drag downwards or to the right. To fill in decreasing order, drag upwards or to the left.
• You can also specify the type of series by using the right mouse button to drag the fill handle over the range and then clicking the appropriate command on the shortcut menu. For instance, if the starting value is a date like JAN-2002, click Fill Months in order to have FEB-2002, MAR-2002, and so on; or click Fill Years in order to have JAN-2003, JAN-2004, and so on.
• If the selection contains numbers, you can control the type of series that you want to create. For instance, click Fill Series in order to have serial numbering in ascending order, or click Formatting Only in order to copy only the format of the selected cell(s).

Other examples of data that Microsoft Excel can automatically fill are shown in the table below: When you fill a series, the selections are extended as shown in the following table. Items separated by commas are in placed in individual adjacent cells.

Initial selection	Extended series
1, 2, 3	4, 5, 6,...
9:00	10:00, 11:00, 12:00,...
Mon	Tue, Wed, Thu,...>
Monday	Tuesday, Wednesday, Thursday,...
Jan	Feb, Mar, Apr,...
Jan, Apr	Jul, Oct, Jan,...
Jan-99, Apr-99	Jul-99, Oct-99, Jan-00,...
15-Jan, 15-Apr	15-Jul, 15-Oct,...
1999, 2000	2001, 2002, 2003,...
1-Jan, 1-Mar	1-May, 1-Jul, 1-Sep,...
Qtr3 (or Q3 or Quarter3)	Qtr4, Qtr1, Qtr2,...
text1, textA	text2, textA, text3, textA,...
1st Period	2nd Period, 3rd Period,...
Product 1	Product 2, Product 3,...

Fill Data in Cells with Fill Handle

Use the Fill Handle to Fill Data

You can use the Fill command to fill data into worksheet cells. You can also have Excel automatically fill series of numbers, number and text combinations, dates, or time periods, based on a pattern that you establish. However, to quickly fill in several types of data series, you can select cells and drag the fill handle. The fill handle is the small black square that usually display when the mouse pointer is placed on at the lower-right corner of the cell-pointer.

Fill data into adjacent cells

1. Select the cell(s) that contain the data that you want to fill into adjacent cells.
2. Point the mouse to the lower-right corner of the selected cell(s). The mouse pointer changes to a small black cross.
3. Drag the fill handle (small black cross) across the cells that you want to fill.
4. After you drag the fill handle, the Auto Fill Options button appears so that you can choose how the selection is filled. For instance, you can choose to fill just cell formats by clicking Fill Formatting Only, or you can choose to fill just the contents of a cell by clicking Fill Without Formatting.

5. To quickly fill a blank cell with the contents of the cell above it; press CTRL+D, to fill to the right of that cell, press CTRL+R.
6. You can suppress AutoFill by holding down CTRL while you drag the fill handle of a selection of two or more cells. The selected values are then copied to the adjacent cells, and Excel does not extend a series.

NOTE that if you drag the fill handle up or to the left of a selection and stop in the selected cells without going past the first column or the top row in the selection, Excel deletes the data within the selection. You must drag the fill handle out of the selected area before releasing the mouse button.

Specifying Response to Invalid Data Entry

How to Specify a Response to Invalid Data Entry in Microsoft Excel

In my last post, I showed how to make data entry easier, or limit entries to certain items that you define and ensure the correct data is entered in Excel worksheet. Additionally, you can specify how you want Microsoft Office Excel to respond when invalid data is entered. Take these the following steps to create Response to Invalid Data Entry in Excel:

• First, you have to create a list of valid entries for the drop-down list, so type the entries in a single column or row without blank cell as can be seen in this picture below.

• You can sort the data in the order that you want it to appear in the drop-down list.
• If you want to use another worksheet, type the list on that worksheet, and then define a name for the list. (Click Defining Name Reference to read my post on that).
  1. Select the cell where you want the drop-down list.
  2. Click on the Data tab, in the Data Tools group, click Data Validation. The Data Validation dialog box will be displayed.
  3. Click the Settings tab.
  4. In the Allow box, click List.
  5. To specify the location of the list of valid entries, do one of the following:
  • If the list is in the current worksheet, enter a reference to your list in the Source box. For example, enter =Depts or =$C$5:$C$25.
  • If the list is on a different worksheet, enter the name that you defined for your list in the Source box.
  6. Make sure that the In-cell drop-down check box is selected.
  7. 2) Click the Error Alert tab, and make sure that the "Show error alert after invalid data" is entered check box is selected.
  8. 3) Select one of the following options for the Style box:
  • To display an information message that does not prevent entry of invalid data, click Information.
  • To display a warning message that does not prevent entry of invalid data, click Warning.
  • To prevent entry of invalid data, click Stop.
  9. Type the title and text for the message (up to 225 characters).
  10. Click Ok.

Creating Drop-Down List in Excel

Create a Drop-Down List from a range of cells

In Microsoft Excel, to make data entry easier, or to limit entries to certain items that you define and ensure the correct data is entered, you can create a drop-down list of valid entries that is compiled from cells elsewhere in the workbook. When you create a drop-down list for a cell, it displays an arrow in that cell. To enter information in the cell, click the arrow, and then click the entry that you want.

In this way you don't have to type the data again and again at every occurrence, you simply select it from the drop-down list as picking items from a list. The Data Validation command in the Data Tools group on the Data tab can also be used to achieve this.

• To create a list of valid entries for the drop-down list, type the entries in a single column or row without blank cell as can be seen in this picture below.

• You can sort the data in the order that you want it to appear in the drop-down list.
• If you want to use another worksheet, type the list on that worksheet, and then define a name for the list. (Click Defining Name Reference to read my post on that).
  1. Select the cell where you want the drop-down list.
  2. Click on the Data tab, in the Data Tools group, click Data Validation. The Data Validation dialog box will be displayed.
  3. Click the Settings tab.
  4. In the Allow box, click List.
  5. To specify the location of the list of valid entries, do one of the following:
  • If the list is in the current worksheet, enter a reference to your list in the Source box. For example, enter =Depts or =$C$5:$C$25.
  • If the list is on a different worksheet, enter the name that you defined for your list in the Source box.
  6. Make sure that the In-cell drop-down check box is selected.
  7. Click Ok.

Create a Calculated Column in Excel

How to Create a Calculated Column in Excel

One of the most beautiful things about Microsoft Excel is the ability to automatically perform calculation on data in tables. You can create a calculated column that uses a single formula that adjusts for each row, i.e. as you enter more data in the succeeding rows, the formula automatically extends to them without you having to enter the formula over and over again. You only need to enter a formula only once and don't need to use the Fill or Copy command. NOTE that this will only work in an Excel table not on range of data. To learn how to create a Table in Excel, read my earlier post on Creating Tables in Excel. Take the following steps to create Calculated Column:

1. Click a cell in a blank table column that you want to turn into a calculated column.
2. Type the formula that you want to use.
3. The formula that you typed is automatically filled into all cells of the column — above as well as below the active cell.

Insert a Table Row or Column

Select the Table and do one of the following:

• To insert one or more table rows, select one or more table rows above which you want to insert one or more blank table rows.
• If you select the last row, you can also insert a row above or below the selected row.
• To insert one or more table columns, select one or more table columns to the left of which you want to insert one or more blank table columns.
• If you select the last column, you can also insert a column to the left or to the right of the selected column.

1. On the Home tab, in the Cells group, click the arrow next to Insert.
2. Do one of the following:
   • To insert table rows, click Insert Table Rows Above.
   • To insert a table row below the last row, click Insert Table Row Below.
   • To insert table columns, click Insert Table Columns to the Left.
   • To insert a table column to the right of the last column, click Insert Table Column to the Right.
3. You can also right-click one or more table rows or table columns, point to Insert on the shortcut menu, and then select what you want to do from the list of options.

Delete Rows or Columns in a Table

1. Select one or more table rows or table columns that you want to delete.
2. On the Home tab, in the Cells group, click the arrow next to Delete, and then click Delete Table Rows or Delete Table Columns.

Delete a Row or Column

1. To delete a row or column, select the row or column you want to delete.
2. On the Home tab, in the Cells group, click Delete or press DELETE key on the keyboard.

Convert a Table to a Range of Data

1. Click anywhere in the table. This displays the Table Tools, adding the Design tab.
2. On the Design tab, in the Tools group, click Convert to Range.

You can also right-click the table, point to Table, and then click Convert to Range.

Delete a Table

1. On a worksheet, select a table.
2. Press DELETE on the keyboard.

 

How to Create Table in Excel

Creating a Table in Microsoft Excel

When you create a table in Microsoft Office Excel, you can manage and analyze the data in that table independently of data outside of the table. For example, you can filter table columns, add a row for totals, apply table formatting, and publish a table to a server that is running Microsoft Windows SharePoint Services 3.0.

When you don't need a table anymore, you can remove it by converting it back to a range or you can delete it.

To Create a Table take the following steps:

1. On a worksheet, select the range of empty cells or data that you want to make into a table.
2. On the Insert tab, in the Tables group, click Table.
3. If the selected range contains data that you want to display as table headers, select the 'My table has headers' check box.

Table headers display default names that you can change if you don't select the 'My table has headers' check box. After you create a table, the Table Tools become available, and a Design tab is displayed. You can use the tools on the Design tab to customize or edit the table. After you create a table, the Table Tools become available, and a Design tab is displayed. You can use the tools on the Design Tab to customize or edit the table.

 

Introduction to Microsoft Excel Tables

Microsoft Excel Tables

To make managing and analyzing a group of related data easier, you can turn a range of cells into a Microsoft Office Excel table (previously known as an Excel list in earlier versions). A table is a series of rows and columns that contains related data that is managed independently from the data in other rows and columns on the worksheet.

By default, every column in the table has filtering enabled in the header row so that you can filter or sort your table data quickly. You can add a total row, which is a special row in a list that provides a selection of aggregate functions useful for working with numerical data to your table that provides a drop-down list of aggregate functions for each total row cell. A sizing handle in the lower-right corner of the table allows you to drag the table to the size that you want just like Microsoft Office Word table.

To manage several groups of data, you can insert more than one table in the same worksheet. However, you cannot create a table in a shared workbook.

You can use the following features to manage table data:

Sorting and filtering. Filter drop-down lists are automatically added in the header row of a table. Then you can sort tables in various orders and options, or you can create a custom sort order. You can also filter tables to show only the data that meets the criteria you set. For more information on sorting and filtering data, see Data Sorting and Filtering in Section Two of this book.

Formatting table data. You can quickly format table data by applying a predefined or custom table style. You can also choose Quick Styles options to display a table with or without a header or a totals row, to apply row or column banding to make a table easier to read, or to distinguish between various columns in the table. For more information on how to format table data, see Formatting Worksheet in Section Five of this book.

Inserting and deleting table rows and columns. You can use one of several ways to add rows and columns to a table. You can quickly insert table rows and table columns anywhere that you want. You can as well delete rows and columns as needed. You can also quickly remove rows that contain duplicate data from a table.

Using a calculated column. To use a single formula that adjusts for each row in a table, you can create a calculated column. A calculated column automatically expands to include additional rows so that the formula is immediately extended to those rows.

Displaying and calculating table data totals. You can quickly total the data in a table by displaying a totals row at the end of the table and then using the functions that are provided in drop-down lists for each totals row cell. Exporting to a SharePoint list. You can export a table to a SharePoint list so that other people can view, edit, and update the table data.