What is the difference between a table and a range of columns and rows in an Excel spreadsheet? How do I create and fill tables? When a table has been created, are we filtering, formatting, and designing these tables to look professional in a report? We show you how it works.
What is the difference between a table and a range in an Excel spreadsheet?
There is no big difference in the data. Both use columns (the database fields) and rows (the database records), and both can be sorted, filtered, calculated, formatted, and printed.
Tables, however, have many additional benefits that are missing from regular spreadsheets. The biggest plus is the option to use data from multiple tables to create queries and reports. For example, suppose TABLE ONE (Corp) contains the company's name, address, city, state, zip code, and phone number. while TABLE TWO (Products) contains the company name, product number, product name, price, inventory, discounts, etc. and table THREE (Orders) contains the product number, the number of products ordered, the price, the extended price, the sales, the tax and the total quantity.
TABLE ONE (Corp) is "linked" by the unique field with TABLE TWO (Products) "Company". TABLE TWO is linked to TABLE THREE (Orders) via the unique "Product Number" field. These are called "relationships". It saves you the constant duplication of fields / data in three different spreadsheets (or tables). Because each company can have multiple products and each product can have multiple orders, you need three separate tables to manage that type of data accurately. We have provided this sample table so you can follow.
For deeper dives, read my previous articles on creating relational tables and creating relational databases.
Creating an Excel spreadsheet
1. First enter the field names in the columns above and then enter some records / data in the rows under each column. You can also open a workbook that is already created and populated, or download the spreadsheet above.
. 2 Then position the cursor within the range you want to convert to a table.
. 3 Select INSERT> Table .
. 4 The Create Table dialog box opens with the preselected table space. If this is wrong, enter (or point to) the correct area; If so, select the My Table Has Headers check box, and then click OK .
. 5 Excel converts the area into a beautiful color table with dark blue column headings and alternating blues on every other line, so you can see your data at a glance.
Also note that the filter function is applied automatically. So sort by a specific field is achieved with a few mouse clicks.
. 6 For example, if you want to see which ski resorts have the most snow, click the down arrow next to the column / field Average Annual Snowfall (Inches) . Select Sort Largest by Smallest and you're done. You do not even have to click OK.
Note that the Grand Targhee Ski Resort at Targhee, Wyoming, tops the list with an annual snowfall of 500 inches. (And I can confirm that because I've been skiing there many times and that's where the world's best powder snow skiing takes place.)
Multiple sorts are also simple. What if you want to know which Colorado ski resorts are best accessible? Or the most vertical feet?
. 7 Click the down arrow next to the State field. Disable . Select All then enable Colorado and click OK.
Note that Excel reduces the table so that only the Colorado ski areas are visible. Do not worry, the other ski resorts are still there. If you change the status field back to Select All the other ski resorts will be displayed again.
. 8 Then click the down arrow next to the field Skiable Terrain . Choose Largest to Smallest and immediately rank Vail at the top with 5,289 acres.
. 9 Now click on the down arrow next to the field Vertical Feet / Drop . Select The largest to smallest again and immediately sort the Telluride with 4,425 feet.
10. Next we'll add another box called Snowboards Welcome. Move the cursor to the empty column (L) on the far right of the table. Enter the name of the new column in line 3, and note that Excel automatically adds the filter and style format.
. 11 It's also easy to add new records. Just move the cursor to the bottom of your spreadsheet and enter a new record in the next available line. For example, type Snowbird on line 17 and Alta on line 18. Excel formats the style as you type.
NOTE: You can also use the HOME to add / insert or delete columns and lines tab, Cells group, insert or Delete Buttons.
How to make tables look professional in a report
You can Change the colors and style of your spreadsheet with just a few mouse clicks.
. 1 Move the cursor anywhere in your table.
. 2 Select HOME> Styles> Style Table and select a style template from the large drop-down list.
. 3 You can select New Table Style from the same drop-down list and customize the design of the table using the functions of the New Table Style 4 dialog box. You can also customize the individual cells. Just select HOME > Styles > Cell Styles and choose a custom cell design from the large drop-down list.