Microsoft Excel 2007 Tutorial – How To Create a Table

I can’t stress enough how important it is to have really good Excel skills for business. Even accounting departments who have spent millions of dollars on tier 1 ERP systems use Excel for reporting and data analysis. Microsoft Office Excel 2007 is a powerful spreadsheet application that has many, easy to use features to help you get your work done. Here is one of many Excel tips and tricks. How to create a table in Microsoft Office Excel 2007.

Tables are not a new feature in Excel 2007. They were previously known as lists. However, lists weren’t used very frequently. I see more and more people utilizing tables now. Probably for a couple reasons. For one, there is an option on the Home tab in the Styles group labeled Format as Table. And secondly, there are many default styles that automatically format your table so it is presentable and functional.

In this Microsoft Excel 2007 Tutorial, I’ll walk you through creating a table, changing the style, adding rows and columns, sorting & filtering, and using the totals row. Again, just one of many Excel Tips and Tricks.

As in Microsoft fashion, there are multiple ways to create a table. I mentioned the option already on the home tab. There’s also an option on the Insert tab in the Tables group labeled, intuitively Table. Either one works perfectly, but I prefer the option on the Home tab because it’s a drop-down of the styles you can apply.

Let’s create a table.

1. First, open Microsoft Office Excel 2007.
2. On the worksheet, select a range of cells. They can contain data or be empty. As an example, type the word Letters in cell A1 and the word Numbers in cell B1. In cells A2 through A6, type the letters a through e. In cells B2 through B6, type the numbers 1 through 5. You can highlight the range or simply have one of the cells selected.
3. On the Home tab in the Styles group, click the button labeled Format as Table. The button will drop-down, displaying all the existing styles available. Choose one. Since the example has a header row, I prefer a style where the first row is darker than the rest.
4. A dialog box will open showing you the range of cells in the table. You can modify this as necessary, but in this example, Excel highlights the range from A1 to B6. Also, since this example has a header row, ensure the box labeled My table has headers is checked. Click OK.

You have now created a table!

Let’s change the style.

1. Click any cell in the table.
2. On the Home tab in the Styles group, click the button labeled Format as Table. The button will drop-down, displaying all the existing styles available. You can choose a new style. If you hover your mouse over a style, Excel will give you a preview of the style in your table.
3. Select a new style by clicking one.

Let’s add a row.

1. Click in cell A4.
2. Press the [Tab] key a couple times. Notice how you’re tabbing through the table and not straight across row 4 and outside of the table? A cool feature!
3. Continue tabbing one time past cell B6. You should be in cell A7 now. Notice how your table expanded? Simple as that, you’ve added a row. You can type new data now. Add the value “f” to A7 and “6” to B7 and stop there.

Let’s add a column.

1. Click in cell C1, type the word “color”, and press [Enter]. Your cell selection moves to C2 and your table has expanded to include your new column.
2. Type a color in C2 and either tab through your table to add colors to column C or you can use [Enter] or [Down Arrow]. Whichever you prefer. Simple enough?

Let’s sort and filter. These are functions that you’re probably familiar with already, but Tables adds it automatically.

1. You’ve probably noticed the arrow buttons on your header row. Click the button on the “number” column.
2. Click the option to Sort Largest to Smallest and your table is sorted without having to highlight the entire range. That’s a cool feature. Another cool feature is, if you had another table adjacent to, but disconnected from this table, your sort doesn’t affect the sorting in the adjacent table.
3. Click the button on the “color” column and uncheck a couple of the colors. Notice how the table has now filtered those colors out from your display.

And lastly, let’s add the totals row.

1. Ensure you have a cell in the table selected.
2. On the Design tab in the Table Style Options group, check the box labeled Total Row. This adds a row to the bottom of your table.
3. Click the cell in the total row in the “number” column, then click the button on the cell. Notice the functions available.
4. Select Sum from the list and notice the result.

Hopefully you see how effective the use of tables in Excel can be. I’ll give you a real-life example. I was part of a data conversion team. I was responsible for providing Human Resources information from the existing system to the development team who would import the data into the new system. They wanted one row of data per employee with all the necessary fields. We had over 5,000 employees. Formatting my worksheet in a table allowed me to filter and sort as we were doing some validation on the data. And having each row a different shade allowed me to visually scan the row of data without losing my place. Oh, and one last, really cool feature is, when you scroll down your worksheet until your header row is not in view anymore, the column labels change from A, B, C, etc. to the data in your headers!