There is a feature in Excel that makes dealing with related data easier…it’s called Excel Tables. If you mark a collection of cells as an Excel table, there are several things that happen automatically. It allows you to manage the data separately from other rows and columns, too.
Let’s start with some sample data that contains sales data. Although it contains some useful information, we can make it better!
Select the entire range, from A2 through E11. On the Home tab, check the Styles group for a button called “Format as Table” and click it. Left click on any of the colors. Because you selected the data ahead of time, the data range will be filled in for you. Your data now has alternating colored rows (called banded rows) to make it easier to read. Filters have been added to each column. These filters let you select only certain values to view and also sort the columns.
That’s not all an Excel table does for you. Move the cursor to cell F3 where we want to add a grand total for Sally Struthers. Click on the auto sum button found in the Editing group and hit [Enter]. Not only do you get the total for Sally, Excel places the total formula in all the rows of the table. You can rename the column headings, as I have done below in A2 and F2.
Adding a total row is slightly different. Right click anywhere on the table and choose “Table” from the context menu that appeared. Add a Totals Row. This will automatically add a row for totals, but you will have to choose which function you want to include. Notice that each column total cell has a drop down that lets you choose functions including sum, average, etc.
An Excel table also lets you add/remove rows and columns in your table without any impact on the rest of the worksheet.
When your cursor is in the table, you see a new tab on the ribbon called “Table Tools. Here you can modify the look and behavior of the table. Checkout all the different options, including adding the Totals Row!
Special tip for those that read the entire post
On the Table Tools tab, there is a Tools group. You can use this to remove duplicates in any of the columns.
Special tip #2 for those that read the entire post
You can add a new column to the table, for example you might want to add a calculation. Type the column heading and hit [Enter] and a new column will be included in the table. Start your formula with the equal sign (=) and hit the square bracket ([). You will get a list of the fields so you don’t have to choose the cell reference. It’s much easier to write a formula with words than the cell references.