Make Tables Using Microsoft Excel

In addition to its other spreadsheet features, Microsoft Excel offers you the ability to create tables within a spreadsheet. Known as “lists” in Excel 2003, they can be managed separately from data you have elsewhere on that spreadsheet page or any other page in the spreadsheet. See Step 1 below for instructions to make and manipulate tables in Microsoft Excel.

Steps

Creating a Table

  1. Select a range of cells. The cells can contain data or they can be empty, or a combination of both. You don’t have to pick your cells before you create the table if you aren’t sure yet.
  2. Insert the table. To start the table creation process, you will need to insert a table into your spreadsheet.
    • In Excel 2003, click the Data menu and select List.
    • In Excel 2007, 2010, and 2013 select either "Table" on the Insert menu ribbon or "Format as Table" in the Styles group on the Home menu ribbon. (The former option applies Excel's default table style, while the other lets you choose a style when you create the table. You can later apply or change the table style by selecting one of the options from the Table Styles group in the Table Tools Design menu ribbon.)[1]
  3. Provide a data source for your table. If you did not previously select a group of cells, you will need to select the cells now. After you pick your range, a dialog box will appear, either the Create Table dialog (Create List dialog in Excel 2003) or the Format As Table dialog.
    • The "Where is the data for your table?" field displays the absolute reference(s) for the current cell(s) selected. If you want to change this information, you can type in a different cell or range reference.
  4. Indicate whether your table has headers. If your table has headers, check the "My table has headers" box. If you don't check this box, the table will display default header names ("Column 1," "Column 2," etc.).
    • You can change a column name by selecting the header and typing in your own name in the formula bar.

Enlarging and Reducing Table Size

  1. Grab the corner of the table. Move your mouse cursor over the sizing handle at the lower right corner of the table. Your cursor will change to a 2-sided diagonal arrow. Click and hold to “grab” the corner.
  2. Resize the table. Drag your cursor inward to reduce the table size, outward to enlarge it. Dragging your cursor adds or reduces the number of rows and columns.
    • Dragging your cursor up toward the column header reduces the number of rows in the table, while dragging your cursor down increases the number of rows.
    • Dragging your cursor to the left reduces the number of columns in the table, while dragging it to the right increases the number of columns. A new column header is created when a new column is added.

Inserting and Deleting Table Rows and Columns

  1. Right-click the table cell where you wish to insert or delete a row or column. A popup menu appears.
  2. Select "Insert" from the popup menu. Choose one of the Insert submenu options.
    • Select "Insert Columns to the Left" or "Insert Columns to the Right" to insert a new column in the table.
    • Select "Insert Rows Above" or "Insert Rows Below" to insert a new row in the table.
  3. Select "Delete" from the popup menu. Choose one of the Delete submenu options.
    • Select "Table Columns" to delete the entire column(s) containing the selected cell(s).
    • Select "Table Rows" to delete the entire row(s) containing the selected cell(s).[2]

Sorting Table Rows

  1. Click the down-arrow to the right of the header of the column you wish to sort by. A dropdown menu will appear.
  2. Choose one of the sort options displayed. The sort options appear at the top of the dropdown menu.
    • Choose "Sort A to Z" (or "Sort Smallest to Largest" if the data is numeric) to sort items in ascending order.
    • Choose "Sort Z to A" (or "Sort Largest to Smallest" if the data is numeric) to sort items in descending order.
    • Choose "Sort By Color" and then select "Custom Sort" from the submenu to set up a custom sort. If your data is displayed in multiple colors, you can select one of the colors from this submenu to sort your data by.
  3. Access additional options. You can access additional sort options by right-clicking on any cell in a column and selecting "Sort" from the popup menu. In addition to the options above, you can also sort by cell or font color or by cell icon.

Filtering Data in Tables

  1. Click the down-arrow to the right of the header of the column you wish to filter. A dropdown menu will appear.
  2. Choose one of the filtering options displayed. Three sets of filtering options are available: "Filter by Color," "Text Filters," and "Number Filters." (The "Text Filters" option is displayed only when the column entries contain text, while the "Number Filters" option is displayed only when the column entries contain numbers.) Below that is a set of check boxes.
    • The "Filter by Color" option is enabled when the text or numbers are show in multiple colors. Select the color you wish to filter the data by.
    • The "Text Filters" option includes the options "Equals," "Does Not Equal," "Greater Than," "Begins With," "Ends With," "Contains," "Does Not Contain," and a "Custom Filter" option.
    • The "Number Filters" option includes the options "Equals"," Does Not Equal," "Greater Than," "Greater Than or Equal To," "Less Than," "Less Than or Equal To," "Between," "Top 10," "Above Average," "Below Average," and a "Custom Filter" option.
    • The check boxes below these options include a "Select All" and "Blanks" option to display all data meeting the filtering criteria or all rows with blank cells in that column, as well as a listing of each unique data element (such as the same name) in that column. Check or uncheck the combination of boxes that will let your display only those rows with a cell that meets your criteria, such as checking the elements "Smith" and "Jones" to display sales figures for only those 2 individuals.
    • Excel 2010 and 2013 offer an additional filtering option: enter text or a number in the Search field and the display will be restricted to only those rows with an item in the column that matches the contents of that field.
  3. Remove the filter when finished with it. To restore the original display, select "Clear Filter From [Column Name]" from the dropdown menu. (The actual name of the column is displayed in this option.)

Adding a Totals Row to a Table

  1. Right-click any cell in the table. This displays a popup menu. Select "Table" from the popup menu.
  2. Select "Totals Row" from the Table submenu. A totals row will appear below the last row of the table, displaying a total of all the numeric data in each column.
  3. Change the displayed value. Click the menu on the Totals line for the value you want to adjust. You can choose what function you would like displayed. You can show the Sum, the Average, the Count, and more.

Adding a Calculated Column to a Table

  1. Select a cell in a blank column. If necessary, you'll have to add a blank column first. See "Enlarging and Reducing Table Size" and "Inserting Table Rows and Columns" for the methods to do this.
  2. Type the calculation formula into any blank cell, other than the header. Your formula is automatically copied into all the column's cells, whether above or below the cell you entered the formula in. You can also copy the formula into the column cells manually.
    • You can enter the formula in any row of the spreadsheet beneath the table, but you can't refer to any cells in those rows in the table reference.
    • You can type or move the formula into a column that already has data, but to make it a calculated column, you'll need to click the "AutoCorrect Options" button to overwrite the existing data. If you copy the formula, however, you'll have to manually overwrite the data by copying the formula into those cells.
  3. Create exceptions. After creating the calculated column, you can later go back and create exceptions by typing data other than a formula in any of the cells, deleting the formula from 1 or more cells, or copying a different formula into some of the cells. Any exceptions to the calculated column formula, other than a formula deletion, will be clearly marked.[3]

Changing the Style of the Table

  1. Select a preset style. You can pick from a variety of preset color combinations for your table. Click anywhere in the table to select it, and then click the Design tab if it is not already open.
    • Choose from one of the presets available in the Table Styles section. Click the More button on the right side to expand the list and see all the options.
  2. Create custom styles. Click the More button on the right side of the list of preset styles. Click “New Table Style” at the bottom of the menu. This will open the “New Table Quick Style” window.
    • Give your style a name. If you want to easily access this style again, give it a name that you can remember or that describes the style.
    • Choose the element you want to adjust. You will see a list of table elements. Choose the one you want to edit and click the “Format” button.
    • Choose your formatting options. You can choose the font style, fill color, and border style from the Format menu. This formatting will be applied to the element you selected.
  3. Change your table back into a regular spreadsheet. If you are finished working with the data in a separate table, you can revert the table back into the spreadsheet, maintaining the data. Click anywhere in the table to select it.
    • Click the Design tab.
    • Click Convert to Range and then click Yes.
    • The table formatting will be removed, but the style will remain. You will no longer be able to sort and filter data.

Tips

  • If you no longer need the table, you can either delete it entirely or turn it back into a range of data on the spreadsheet page. To delete the table entirely, select the table and press your keyboard "Delete" key. To change it back to a range of data, right-click any of its cells, select "Table" from the popup menu that appears, and then select "Convert to Range" from the Table submenu. The sort and filter arrows disappear from the column headers, and any table name references in the cell formulas are removed. The column header names and the table formatting remain, however.
  • If you place your table so that the header for the first column is in the upper left corner of the spreadsheet (Cell A1), the column headers will replace the spreadsheet's column headers when you scroll up. If you place the table anywhere else, the column headers will scroll out of view when you scroll up, and you'll need to use Freeze Panes to keep them constantly displayed.

Related Articles

Sources and Citations

  1. ↑ http://office.microsoft.com/en-us/excel-help/create-or-delete-an-excel-table-in-a-worksheet-HA010013764.aspx
  2. ↑ http://office.microsoft.com/en-us/excel-help/add-or-remove-table-rows-and-columns-in-an-excel-table-HA010013765.aspx
  3. ↑ http://office.microsoft.com/en-us/excel-help/overview-of-excel-tables-HA010048546.aspx
Retrieved from "https://kipkis.com/index.php?title=Make_Tables_Using_Microsoft_Excel&oldid=274832"