How to Create an Entity Relationship Diagram from Excel (with Quick Steps)

 

Watch Video – How to Create an Entity Relationship Diagram from Excel



 

Step 1: Prepare a Database

  • Create a database containing three tables in separate worksheets. The tables contain customer information, order details, and product info.

Read More: How to Manage Relationships in Excel


Step 2: Create Entities

  • To create the entities, you must create 3 entities for the separate tables.
  • Format a few cells with borders to make them look like rectangular boxes. Each represents a particular entity.
  • Name the entities “Customer,” “Order,” and “Product.”

entity relationship diagram

Read More: How to Create Data Model Relationships in Excel


Step 3: Add Attributes to the Entities

  • You need to add the attributes to each entity based on the database tables. For example, the “Customer Information” table contains “Account_No”, “First_Name”, “Last_Name”, “Email_ID” and “Phone_No”. You need to add these properties as attributes to the entity named “Customer.”
  • Do the same for the other entities.
  • Add Primary Key and Foreign Key notations at the left of each attribute. For example, write PK for Primary Key at the left of Account_No, as each customer has a unique account number. You can also use FK for Foreign Key.


Step 4: Copy Entities as Pictures

  • Copy the range of cells for each entity and paste them as pictures one by one.

Step 5: Show Relationships

  • Select Insert >> Illustrations >> Shapes >> Line to draw the crow’s feet notations to show the relationships between entities.

entity relationship diagram

Read More: How to Create Relationship in Excel with Duplicate Values


Step 6: Group All Objects

  • Select all images and line objects and right-click to group them together.
  • Copy or save the diagram as a picture.

Things to Remember

  • Be careful while determining the cardinality of relationships between entities.
  • You can also create an intermediate entity with a composite key if there are no direct primary foreign key relationships between entities.

Download the Practice Workbook

You can download the practice workbook using the download button below.


Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo