This wikiHow teaches you how to create a data table in Microsoft Excel. You can do it in both Windows and Mac versions of Excel.
Method 1 of 3: Create a table
Step 1. Open your Excel document
Double-click the Excel document or click the Excel icon and then select the name of the document from the start page.
- You can also open a new Excel document by going to the Excel home page Empty workbook but you will need to enter your details before proceeding.
Step 2. Select the data from your table
Click the cell in the upper left corner of the data group that you want to include in your spreadsheet. Then hold down Shift while clicking the lower right cell in the data group.
- For example: if you have data in the cells A1 down to A5 and over to D5 you would A1 and then D5 while holding down Shift.
Step 3. Click the Insert tab
It's a tab in the green band at the top of the Excel window. That shows under the green band Insert-Toolbar on.
- If you're on a Mac, make sure you don't InsertMenu item on your Mac's menu bar.
Step 4. Click Table
This option is in the "Tables" section of the toolbar. Clicking them will bring up a pop-up window.
Step 5. Click OK
It's at the bottom of the pop-up window. That creates your table.
- If your data group contains cells that are designated as column names (e.g. headings), click the "My table contains headings" check box before you OK click.
Method 2 of 3: Change the design of the table
Step 1. Click the Design tab
It's in the green band near the top of the Excel window. This will open a toolbar for designing your table directly under the green ribbon.
If you don't see this tab, click your table to make it appear
Step 2. Choose a design scheme
Click one of the colored boxes in the "Table Styles" section of the design-Toolbar to apply the color and design to your table.
You can click the down arrow to the right of the colored boxes to scroll through the various design options
Step 3. Review the other design options
In the "Table Style Options" section of the toolbar, select or deselect any of the following boxes:
- Heading line - Clicking this box places Column Names in the top cell of the data group. Uncheck this box to remove headings.
- Totals line - When turned on, this option adds a row to the bottom of the table showing the total value in the rightmost column.
- Linked cells - Check this box to color alternating rows, or deselect it to keep all rows in your table the same color.
- First column and Last column - When switched on, these options make the headings and dates in the first and / or last column bold.
- Linked columns - Check this box to color alternate columns or deselect it to keep all columns in your table the same color.
- Filter button - When checked, this box will put a drop-down box next to each heading in your table. This allows you to change the data that is shown in the column.
Step 4. Click the Start tab again
That brings you to begin-Toolbar back. The changes to your table are retained.
Method 3 of 3: Filter table data
Step 1. Open the filter menu
Click the drop-down arrow to the right of the heading for the row whose data you want to filter. A drop-down menu will appear.
- To do this, go to the "Table Style Options" section of the designTabs have ticked both the "Heading line" and the "Filter" box.
Step 2. Select a filter
In the drop-down menu, click one of the following options:
- Sort from smallest to largest
- Sort from largest to smallest
- You may also have additional options, such as Sort by color or Number filter, depending on your data. If so, you can select one of these options and then click a filter in the pop-out menu.
Step 3. Click OK if prompted
Depending on the filter you are using, you may need to select a bandwidth or other data type before you can proceed. Your filter will be applied to your table.
- If you no longer need the table, you can either delete it entirely or return it to a data selection on the worksheet page. To delete the table completely, select the table and press the "Delete" key on your keyboard. To change it back to a data selection, click any of its cells. Select "Table" from the pop-up menu that appears, then select "Convert to Selection" from the Table submenu. The sort and filter arrows disappear from the column headings and all table name references in the cell formulas are removed. However, the column headings and table formatting are retained.
- If you place your table so that the heading for the first column is in the upper left corner of the data sheet (cell A1), the column headings will replace those of the worksheet columns when you scroll up. If you move the table anywhere else, the column headings will scroll out of sight when you scroll up. Then you have to use Freeze clippings to keep them constantly displayed.