Use These Relational Databases in Excel to Accelerate the Recording of Your Important Business Data

The latest version of Microsoft Excel is a great addition to any office as it is now more efficient, functional and intuitive than ever before. 

Excel’s table tools make it easy to link charts and cells, perform searches, and create dynamically updated reports. In this sense, it functions somewhat like a relational database.

A relational database is essential for working with large amounts of business data.

They are structured to recognize relations among stored items of information. They allow you to quickly retrieve and search for specific info, view the same data set in multiple ways, and reduce data errors and redundancy.

In this article, we will show you how to make two tables: the master table and the detail table.

The master table will show the salespersons’ information, and the detail table will provide their total sales. To begin, open up a new, blank worksheet on Excel.

Create the Master Table

1. Double-click Sheet1 at the bottom of the screen and type, Master
2. In cell A1 type Master.
3. In cells A3 – F3 type these column headers: Sales ID, Sales Person, Address, City, State, Zip Code.
4. In cells A4 – A13 type the sales ID numbers – for experimental purposes type 101 – 110.
5. Now continue by filling in names, addresses, cities, state, and zip codes in the remaining fields. In order to find the highest sales by city, make sure type the same city in multiple cells.
6. Once all the data is entered, highlight the table, and from the Styles group, select Format as Table. From the dropdown menu, choose the color and format you’d like. When the dialog box appears, make sure that My table has headers is checked.
7. With the table still highlighted, select the Design tab. On the far left, type Master in the Table Name box.

Create the Detail Table

1. At the bottom of the screen beside the Master tab, click the plus (+) symbol to insert a new sheet. Double click the tag line at the bottom left that says Sheet2, and make it say Sales.
2. In cell A1 type, Total Sales for 2014. In cells A3 – E3, type Sales ID, Quarter 1, Quarter 2, Quarter 3, Quarter 4.
3. In cells A4 – A13 type the sales ID numbers: 101 – 110.
4. In B4 – E13, enter 40 random numbers that represent sales dollars
5. Once the data is entered, highlight cells A3 – E13. Select Format as Table, and choose the colour and format you want from the dropdown menu. When the dialog box appears, ensure that My table has headers is checked.
6. With the table still highlighted, type Sales in the Table Name section on the left.

With these tips your office is on its way to a more organized and efficient recording of data. Excel is a great tool to present and list important data in a clear and easy-to-understand way.

For more information on relational databases in Excel, please feel free to contact our team. You can reach us at (518) 459-6712 or send us an email at info@pnjtechpartners.com. Here at PNJ Technology Partners, we want to ensure the organization and success of your company.