Making Excel as CRM Database for small company

To set up an Excel file as a database for managing people's names, organizations, and a CRM system for your growing business, you'll want to organize the data in a way that's easy to manage and scale. Below are the steps to create a functional CRM system in Excel:

Step 1: Plan Your Data Structure

Think about the types of information you'll need to track for each person, organization, and CRM-related data. Here's a possible structure:

1. People (Contacts) Table

   • Name (First, Last)
   • Job Title
   • Email Address
   • Phone Number
   • Address
   • LinkedIn Profile (or other social media)
   • Notes (for additional details)

2. Organizations Table

   • Organization Name
   • Industry
   • Website
   • Organization Contact Info (Main phone, email)
   • Address

3. CRM Activities Table

   • Activity ID (Unique Identifier)
   • Contact Name (Linked to People table)
   • Organization Name (Linked to Organizations table)
   • Activity Type (Call, Meeting, Email, etc.)
   • Date of Activity
   • Notes (For any follow-up actions or key details)
   • Status (Pending, Completed, Follow-up needed, etc.)
   • Follow-up Date (if applicable)

Step 2: Set Up Excel Tables

Use Excel's Table feature to organize the data and make it easy to filter and reference.

1. Create the People Table:

   • Open a new Excel file.
   • Label columns as follows:
     First Name | Last Name | Job Title | Email | Phone Number | Address | Notes
   • Select the range of your data (including column headers) and press Ctrl + T to convert it into a table.
   • Give this table a name: In the Table Design tab, name it PeopleTable.

2. Create the Organizations Table:

   • On a new sheet, label columns:
     Organization Name | Industry | Website | Organization Contact Info | Address
   • Again, convert the data into a table and name it OrganizationsTable.

3. Create the CRM Activities Table:

   • On another sheet, label columns:
     Activity ID | Contact Name | Organization Name | Activity Type | Date | Notes | Status | Follow-up Date
   • Make sure the Contact Name and Organization Name columns will reference data from the People and Organizations tables.

Step 3: Link Tables Using Data Validation

To create relationships between your tables (People, Organizations, and CRM Activities), use Excel's Data Validation feature.

1. Linking Contact Names:

   • In the CRM Activities Table, select the Contact Name column.
   • Go to Data > Data Validation > Data Validation.
   • Under the Settings tab, select List as the Allow type, and in the Source box, type =PeopleTable[First Name] (or use the name of your People table column).
   • This will create a dropdown list with all contacts in your People table for easy selection.

2. Linking Organization Names:

   • Similarly, in the CRM Activities Table, select the Organization Name column.
   • Go to Data > Data Validation > Data Validation.
   • In the Source box, type =OrganizationsTable[Organization Name] (or the name of your Organizations table column).
   • This will create a dropdown list with all organization names from your Organizations table.

Step 4: Add Formulas for Tracking & Automation

To make your CRM more functional, you can add some formulas:

1. Activity ID (Unique Identifier):

   • In the CRM Activities Table, create an Activity ID column that generates a unique identifier for each activity. Use Excel's =TEXT(ROW(), "0000") function or similar.

2. Follow-up Date:

   • Use Excel's date functions to automatically calculate follow-up dates, for example: =IF([Status]="Completed", [Date]+7, ""). This will add a follow-up date if the status is "Completed."

3. Activity Status:

   • Use Data Validation again to create a dropdown for the Status column with predefined values: Pending, Completed, Follow-up Needed, etc.

Step 5: Set Up Filters and Sorting

Once your tables are set up, use Excel's filtering capabilities to sort and view data:

1. Filters:

   • Excel's Table feature automatically adds filter arrows to each column header. You can filter by activity type, contact name, organization, or status to focus on specific data.

2. Sorting:

   • Sort data by dates, status, or contact name to quickly review upcoming activities or follow-ups.

Step 6: Set Up Conditional Formatting

To visually track important data, you can use Conditional Formatting.

1. Highlight Follow-up Activities:

   • Select the Follow-up Date column in your CRM Activities table.
   • Go to Home > Conditional Formatting > New Rule.
   • Set the rule to highlight cells where the follow-up date is today (use the formula =A1=TODAY()).

2. Color-Code Status:

   • You can also add conditional formatting to the Status column to change colors based on status (e.g., green for "Completed", yellow for "Pending", red for "Follow-up Needed").

Step 7: Create Reports Using PivotTables and Charts

For insights into your CRM data, use PivotTables and Charts:

1. PivotTable for Activities Summary:

   • Highlight your CRM Activities Table.
   • Go to Insert > PivotTable.
   • Set up a PivotTable to summarize activities by Contact Name, Organization Name, or Activity Type, and track progress or activity volume.

2. Charts for Visual Data:

   • Create a bar or pie chart to visualize the distribution of Activity Status or the number of activities by Contact Name.

Step 8: Backup and Version Control

Make sure to regularly save and back up your Excel file:

1. Use Cloud Storage:

   • Save the file on cloud services like OneDrive or Google Drive for automatic syncing and version control.

2. Backup Regularly:

   • Regularly create backups of the file to avoid losing important data.

Step 9: Regularly Update the CRM

As your business grows, update the database regularly:

• Add new contacts and organizations to the People and Organizations tables as you acquire new clients.
• Update activities and status in the CRM table as you interact with people and organizations.

Step 10: Transition to More Advanced Tools (When Needed)

As your business expands and you reach the limits of Excel, you may consider moving to a more robust CRM solution (like HubSpot, Salesforce, or a database-driven solution) for greater scalability and features. But for now, this Excel setup should provide a solid foundation for managing your CRM and business data.