How to create relational databases in Excel 2013
Excel used to be the poor schmuck’s database,
Not anymore: Excel 2013’s table tools include features that make it easy to link charts and cells,
How Excel makes a relational database
Relational databases—databases structured to recognize relations among the information stored in them—are essential for working with large amounts of business data. They let you quickly search and retrieve specific information, view the same data set in multiple ways, and reduce data errors and redundancy. Try doing that with a spreadsheet.
To show you how Excel makes it easier, we will create two tables: the master table and the detail table. The master table is the primary table, which generally contains unique records (such as name, address, city, state, etc.). This table rarely changes except to, say, add or delete individuals.
For every record in the master table, there can be many records in the detail tables (also called slave or child tables) that link back to the master table. This is called a one-to-many relationship. The data in the detail tables—such as daily sales, product prices, quantities—usually changes constantly.
To avoid repeating all the master information in every detail table, you create relationships using one unique field, such as the Sales ID, then let Excel do the rest. For example, you have 10 sales people who all have unique, demographic information (master table). Each sales person has 200 products that he/she sells (detail or child table). At the end of each year, you need a report that provides the total yearly sales by person, but you also need a report that provides the total sales by city.
For this tutorial, we’ll create a master table with the salespersons’ information and a second table that provides their total sales, by quarter, for the year. The Sales ID is the relational field that connects the tables. Then, we’ll create a report (or pivot table) that shows which cities had the highest sales.
Open Excel and select a new, blank worksheet.
Create the master table
1. First, double-click the tab at the bottom of the screen (above the green bar line) and type
Master over the tag line Sheet1.
2. In cell A1 type:
Master. In cells A3 through F3 type these column headers:
3. In cells A4 through A13 type the sales ID numbers—in this case,
110. The Sales ID is the unique data value that’s used to create a relationship between your two tables.
4. Enter names, addresses, cities, states, and zip codes in the remaining cells. You can copy the information from this sample worksheet or create your own data. Since we are looking for the highest sales by city, be sure to create multiple cities in your table. For example, we have three salespeople in Los Angeles, two in Hollywood, two in San Francisco, and three in San Diego.
5. Once the data is entered, highlight A3 through F13, including the column headers. From the Styles group, select Format as Table. From the dropdown, choose a color and format you like. A Format As Table dialog box appears with the table range displayed in the white box. Ensure that the My Table Has Headers box is checked, then click OK.
6. With the table still highlighted, select the Design tab under the text that says Table Tools (this option is available only when the table is highlighted). In the Properties group (far left), in the box under Table Name, type
Create the detail table
1. At the bottom of the screen beside the Master tab, click the '+' sign to insert a new sheet. Double-click the tab and type
Sales over the tag line Sheet2.
2. In cell A1, type
Total Sales for 2013. In cells A3 through E3, type
3. In cells A4 through A13 type the sales ID numbers:
4. In B4 through E13, enter 40 random numbers that represent sales dollars or copy the data from this example table.
5. Once the data is entered, highlight cells A3 through E13. From the Styles group, select Format as Table. From the dropdown, choose a color and format you like. A Format As Table dialog box appears with the table range displayed in the white box. Ensure that the My Table Has