Work with Data and Excel Tables in VB.NET

Generate Code 128 Code Set B in VB.NET Work with Data and Excel Tables

9

You can add rows and columns to an Excel table, or remove them from an Excel table without deleting the cells contents, by dragging the resize handle at the Excel table s lower-right corner. If your Excel table s headers contain a recognizable series of values (such as Region1, Region2, and Region3), and you drag the resize handle to create a fourth column, Excel creates the column with the label Region4 the next value in the series. Excel tables often contain data you can summarize by calculating a sum or average, or by finding the maximum or minimum value in a column. To summarize one or more columns of data, you can add a Total row to your Excel table.

When you add the Total row, Excel creates a formula that summarizes the values in the rightmost Excel table column. To change that summary operation, or to add a summary operation to any other cell in the Total row, click the cell, click the arrow that appears, and then click the summary operation you want to apply. Clicking the More Functions menu item displays the Insert Function dialog box, from which you can select any of the functions available in Excel. Much as it does when you create a new worksheet, Excel gives your Excel tables generic names such as Table1 and Table2. You can change an Excel table s name to something easier to recognize by clicking any cell in the table, clicking the Design contextual tab, and then, in the Properties group, editing the value in the Table Name box. Changing an Excel table name might not seem important, but it helps make formulas that summarize Excel table data much easier to understand. You should make a habit of renaming your Excel tables so you can recognize the data they contain.

See Also For more information about using the Insert Function dialog box and about referring to tables in formulas, see Creating Formulas to Calculate Values in 10, Perform Calculations on Data.

If for any reason you want to convert your Excel table back to a normal range of cells, click any cell in the Excel table and then, on the Design contextual tab, in the Tools group, click Convert To Range. When Excel displays a message box asking if you re sure you want to convert the table to a range, click OK. In this exercise, you ll create an Excel table from existing data, add data to an Excel table, add a Total row, change the Total row s summary operation, and rename the Excel table. SET UP You need the DriverSortTimes_start workbook located in your 09 practice file folder to complete this exercise. Open the DriverSortTimes_start workbook, and save it as DriverSortTimes. Then follow the steps.

1. Select cell B2. 2. On the Home tab, in the Styles group, click Format as Table, and then select a

Options group, select the Total Row check box. A Total row appears at the bottom of your Excel table.

9

CLEAN UP Close the DriverSortTimes workbook. If you are not continuing directly to the next chapter, exit Excel.

cells, selecting the cells, and then dragging the fill handle. To change how dragging the fill handle extends a data series, hold down the Ctrl key.

specify whether to copy the selected cells values, extend a recognized series, or apply the selected cells formatting to the new cells.

Paste Live Preview capability to preview how your data will appear before you commit to the paste operation.

Paste Options action button. You can use its controls to change which aspects of the cut or copied elements Excel applies to the pasted elements.

aspects of cut or copied data, perform mathematical operations, transpose data, or delete blank cells when pasting.