What’s the difference between a table and a range of columns and rows on an Excel spreadsheet? How do I create and populate tables? And, once a table is created, how do we custom filter, format, and design those tables so they look professional in a report? We’ll show you how it’s done.
What’s the difference between a table and a range in an Excel spreadsheet?
There’s not much difference regarding the data. Both use columns (the database fields) and rows (the database records), and both can be sorted, filtered, calculated, custom formatted, and printed.
Tables, however, have many additional benefits that regular spreadsheets lack. The biggest plus is the option to use data from multiple tables to create queries and reports. For example, say that TABLE ONE (Corp) has the company name, address, city, state, ZIP Code, and phone number; while TABLE TWO (Products) has the company name, product number, product name, price, inventory, discounts, etc.; and Table THREE (Orders) has the product number, number of products ordered, price, extended price, sales, tax, and totals.
TABLE ONE (Corp) is “connected” to TABLE TWO (Products) by the unique field called “Company.” TABLE TWO is connected to TABLE THREE (Orders) by the unique field called “Product Number.” These are called “relationships” and save you from continually duplicating fields/data on three different spreadsheets (or tables). Because each company can have multiple products, and each product can have multiple orders, you really need three separate tables to accurately manage this type of data. We’ve provided this sample spreadsheet so you can follow along.
For deeper dives, check out my earlier articles on creating relational tables and creating relational databases.
How to create an Excel table
1. First, enter the field names in the columns across the top, and then enter some records/data in the rows under each column. You could also open a workbook that’s already created and populated with data, or download the spreadsheet table we provided above.
2. Next, place your cursor anywhere inside the range you want to convert to a table.
3. Select INSERT > Table.
4. The Create Table dialog opens with the table range pre-selected. If this is wrong, enter (or point) to the correct range; however, if correct, check the box that says My Table Has Headers, then click OK.
5. Excel converts the range into a beautiful color table with dark blue column headers and alternating shades of blue on every other row for an easy “at a glance” view of your data.
JD Sartain / IDG Worldwide
Create an Excel table in two seconds
In addition, notice that the filter feature is automatically applied, so sorting by a specific field is accomplished by a few clicks of the mouse.
6. For example, if you want to see which ski resorts get the most snow, click the down arrow beside the column/field called Average Annual Snowfall (inches). Select Sort Largest to Smallest, and it’s done. You don’t even have to click OK.
Notice that Grand Targhee Ski Resort in Targhee, Wyoming tops the list with an annual snowfall of 500 inches. (And I can attest to that, because I have skied there many times. It also has the best powder skiing in the world.)
JD Sartain / IDG Worldwide
Use the Filter to sort + query the table for specific information
Multiple sorts are also easy. What if you want to know which ski resorts in Colorado have the most skiable terrain? Or the most vertical feet?
7. Click the down arrow beside State field. Uncheck Select All, then check Colorado, and click OK.
Notice that Excel shrinks the table so only the Colorado ski resorts are visible. Not to worry, the other ski resorts are still there. Once you change the State field back to Select All, the other ski resorts reappear.
8. Next, click the down arrow beside the field called Skiable Terrain. Select Largest to Smallest and it instantly sorts placing Vail at the top with 5,289 acres.
9. Now click the down arrow beside the field called Vertical Feet/Drop. Select Largest to Smallest again and it instantly sorts placing Telluride at the top with 4,425 feet.
JD Sartain / IDG Worldwide
Sort by State, then sort by Skiable Terrain + Vertical Feet
10. Next, let’s add another field called Snowboards Welcome. Move your cursor to the blank column (L) at the far-right side of your table. Type the name of the new column in row 3 and notice that Excel adds the filtering and style format automatically.
11. It’s also easy to add new records. Just move your cursor to the bottom of your table and enter a new record on the next available row; for example, enter Snowbird on row 17 and Alta on row 18. Excel formats the style as you type.
NOTE: You can also add/insert or delete columns and rows using the HOME tab, Cells group, Insert or Delete buttons.
JD Sartain / IDG Worldwide
Easily add new columns & rows
How to custom-design tables to look professional in a report
You can easily change the colors and style of your table with just a few mouse clicks.
1. Move your cursor anywhere inside your table.
2. Select HOME > Styles > Styles Table and choose a style design from the large drop-down graphical list.
3. You can select New Table Style from the same drop-down list and customize your table design using the features provided in the New Table Style dialog window.
4. You can also customize the individual cells. Just select HOME > Styles> Cell Styles and choose an individual cell design from the large drop-down graphical list.
JD Sartain / IDG Worldwide
Select a custom style format for your new table.