This article explains how to create a data table in Microsoft Excel. You can follow these steps on both the Windows version of the program and the Mac version.
Steps
Part 1 of 3: Create a Table
Step 1. Open an Excel document
Double-click the file, or the Excel icon and then select the file name from the home page. The document will open within the program.
You can also open a new Excel document by clicking on Blank workbook from the Excel home page, but you must enter the data before continuing.
Step 2. Select the table data
Click on the cell in the upper left corner of the dataset you want to include in the table, then hold down Shift and click on the cell in the lower right corner of the dataset.
For example: if the data is from the cell A1 up to A5 below and at D5 on the right, you should click on A1, then on D5 holding down Shift.
Step 3. Click the Insert tab
It is located on the green ribbon at the top of the Excel window. The section will open insert under the green ribbon.
If you are using a Mac, be sure not to click on the item insert in the computer menu bar.
Step 4. Click Table
You will find this entry in the "Tables" section of the toolbar. Click it and a window will open.
Step 5. Click OK
The button is located at the bottom of the window. The table will be created.
If the data group contains cells dedicated to column titles, check the "Table with headings" box before clicking OK.
Part 2 of 3: Changing the Format of a Table
Step 1. Click the Design tab
It is located on the green ribbon at the top of the Excel window. The table design toolbar will open.
If you don't see the indicated tab, click the table and it should appear
Step 2. Choose a scheme
Click one of the colored boxes in the "Table Styles" section of the bar Design, in order to apply the color and layout to the table.
You can click on the down arrow next to the colored buttons to scroll through the various style options
Step 3. Check other design options
In the "Table Style Options" section of the toolbar, you can check or uncheck the following boxes:
- Header line: by checking this box, the names of the columns are placed in the first cells of the data group. Uncheck to remove headers.
- Total Row: When enabled, this option adds a row at the bottom of the table, showing the total values of the rightmost column.
-
Alternate lines highlight.
: check this box to assign alternating colors to the lines, or leave it blank to give the same color to all lines.
- First column And Last column: if checked, these options make the headings and data of the first and last columns bold.
-
Alternate columns highlight.
: Check this box to assign alternating colors to the columns, or leave it blank if you want all the columns to be the same color.
- Filter button: if checked, this box creates a drop-down menu next to each header of the table, which allows you to change the data displayed within each column.
Step 4. Click the Home tab again
You will return to the toolbar Home and the changes will remain.
Part 3 of 3: Filtering the Data in a Table
Step 1. Open the Filter menu
Click on the drop-down menu to the right of the header of the column whose data you want to filter. The menu will open.
To do this, you need to check the "Header Row" and "Filter Button" boxes in the "Table Style Options" section in the tab Design.
Step 2. Choose a filter
Click on one of the following options in the drop-down menu:
- Order from smallest to largest
- Order from largest to smallest
- You may see other options such as Sort by color or Filters by numbers based on the data type. In that case, you can select the option you prefer and then click a filter in the menu that opens.
Step 3. Click OK if asked
Depending on the filter you choose, you may also need to select a different range or type of data before continuing. The filter will be applied to the table.
Advice
- If you no longer need a table, you can delete it entirely or transform it back into a range of data in the spreadsheet. To delete it completely, select it and press "Delete" on your keyboard. To bring it back to a range of data, click with the right mouse button on one of the cells, select "Table" from the menu that appears, then click "Convert to range". The filter sorting and arrows will disappear from the column headings and all references to the table name in the cell formulas will be removed. However, the column names and table formatting will remain.
- If you position the table so that the first column header is in the upper left corner of the spreadsheet (Cell A1), the column header will replace the sheet header when you scroll up. If the table has a different origin, the header will be hidden when you scroll and you will need to use the cell lock option to make sure it is always displayed.
