Microsoft ExcelThe 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 (415) 294-5250 or send us an email at info@ontimetech.com. Here at On Time Tech, we want to ensure the organization and success of your company.

On Time Tech is your One Click & Fixed San Francisco Managed IT Services & IT Support Company.

We’ve Got the “One Click & Fixed” IT Solutions Your California Business Needs

These days, people are looking for convenience in computer support services that don’t waste their time [..]

Read More

The Benefits of Using IT Managed Services 

Discover the many benefits of partnering up with an IT Managed Service Provider. Today, many businesses a[..]

Read More

On Time Tech Makes List of Top 100 Small Business Managed Service Providers Worldwide

The team of IT experts at On Time Tech is thrilled to announce that they have been recognized as one of th[..]

Read More