- Types of Data You Enter into Excel
- Entering Different Types of Data into a Cell
- Controlling the Next Cell Selection
- Using Copy, Cut, Paste, Paste Special to Enter Data
- Using Text to Columns to Separate Data in a Single Column
- Inserting Symbols and Equations into a Cell
- Using Web Queries to Get Data onto a Sheet
- Using Series to Quickly Fill a Range
- Editing Data
- Working with Tables
- Fixing Numbers Stored as Text
- Spellchecking Your Sheet
- Finding Data on Your Sheet
- Using Data Validation to Limit Data Entry in a Cell
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Working with Tables
When you enter information in multiple columns on a sheet, it is often referred to as a table, as in a table of data. But Excel also has a special term for a setting you can apply to a data table, imbuing it with special abilities and rules. This term is also Table. When your data table is defined as a Table, additional functionality in Excel is made available. For example, with Excel’s intelligent Tables, the following additional functionality becomes available:
- AutoFilter drop-downs, shown in Figure 3.16, are automatically added to the headings.
- You can apply predesigned formats, such as banded rows or borders.
- You can remove duplicates based on the values in one or more columns.
- You can toggle the total row on and off.
- Adding new rows or columns automatically extends the table.
- You can take advantage of automatically created range names.
NOTE
Throughout this book, when I am referring to this special kind of Table, the T will be capitalized. A normal table that does not have the additional functionality, will have a lowercase t.
Defining a Table
For your data to convert to a Table, it must be set up properly. This means that, except for the headings row (row 1 in Figure 3.15), each row must be one complete record of the data set—for example, a customer or inventory item—as shown in Figure 3.15. Column headers are not required, but if they are included, they must be at the top of the data. If your data does not include headers, Excel inserts some for you.
)
Figure 3.15. Set up your data properly to define it as a Table. Make sure the Create Table dialog box includes your entire data set and properly reflects the existence of headers.
After your data is set up properly, you can define the Table with one of the following methods. Select a cell in the data set and then do the following:
- Go to Insert, Tables, Table.
- Go to Home, Styles, Format as Table, and select a style to apply to the data.
- Press Ctrl+T.
- Press Ctrl+L.
When you use any one of the preceding methods, Excel determines the range of your data by looking for a completely empty row and column. The Create Table dialog box opens, showing the range Excel has defined. You can accept this range or modify it as needed. To modify it, you can click on the sheet and, holding down the mouse button, drag to create a box enveloping your entire data set. You can also modify it by editing the cell addresses in the Create Table dialog box.
If Excel was able to identify headers, the My Table Has Headers dialog box will be selected, so make sure that Excel has correctly identified whether your data has headers and click OK. If there were no headers, make sure the box is unselected and click OK. Your table will be formatted with AutoFilter drop-downs in the headers, as shown in Figure 3.16.
)
Figure 3.16. A Table automatically has AutoFilter drop-downs in the headers.
NOTE
With the creation of the Table, a new tab Table Tools, Design has appeared in the ribbon. Whenever you select a cell in a Table, this tab appears. It contains functionality and options specific to Tables.
Expanding a Table
After your data is defined as a Table, the Table automatically expands as you add adjacent rows and columns. If you don’t want the new entry to be part of the Table, you can tell Excel by clicking the lightning bolt icon that appears and then selecting either Undo Table AutoExpansion or Stop Automatically Expanding Tables, as shown in Figure 3.17.
)
Figure 3.17. If you don’t want a new row or column to be part of the Table, instruct Excel to undo or stop the autoexpansion.
CAUTION
When adding new rows to the bottom of a Table, make sure the total row is turned off; otherwise, Excel cannot identify the new row as belonging with the existing data. The exception is if you tab from the last data row, Excel inserts a new row and moves the total row down.
To manually resize a Table, click and drag the angle bracket icon in the lower-right corner of the Table. You can also select a cell in the Table and go to Table Tools, Design, Properties, Resize Table. Specify the new range in the Resize Table dialog box that opens.
Adding a Total Row to a Table
When you go to Table Tools, Design, Table Style Options, Total Row, Excel adds a total row to the bottom of the active Table. By default, Excel adds the word Total to the first column of the Table and sums the data in the rightmost column, as shown in Figure 3.18. If the rightmost column contains text, Excel returns a count instead of a sum.
)
Figure 3.18. Selecting the Total Row check box adds a total row to the bottom of the Table. Use the drop-downs in each cell in this row to add or change the function used to total the data in the above column.
Each cell in the total row has a drop-down of functions that can be used to calculate the data above it. For example, instead of the sum, you can calculate the average, max, min, and more. Just make a selection from the drop-down and Excel inserts the formula in the cell.
TIP
The functions listed in the drop-down are calculated using variations of the SUBTOTAL function. For more information on this function, see Chapter 10, “Subtotals and Grouping.”