Lead List Tutorial: Create Your Own CRM in Excel!

Lead management is one of the most important processes that you have to follow in order to successfully get customers. By organizing and tracking their leads, businesses acquire more and more buyers. Therefore, a sales lead means the interest another business, a customer or a buyer shows for your product or service. Sometimes it can be a little confusing for customer service and marketing departments to determine what exactly can be considered as a ‘lead’.

Before going into details, we have a quick tutorial on Someka’s Customer Follow Up Sheet:

A sales lead can be anything. From a small communication with a direct message in any of the social media platforms to a detailed email request for a purchase. You should take every communication from a customer seriously and pursue it as fast as you can. Due to the fact that every sales lead has the chance for a profit for your company.

No matter how small a lead is, it can always end up with a purchase. However, determining criteria for which leads to follow and which leads to prioritize will be helpful and useful for your business and employees. This means organizing and tracking your leads is a process to be considered for every business.

Why Is Lead Management Important for Your Business?

You might be wondering why lead management is important for your company. It is safe to say that without any management and organization of your sales leads, you would be taking the wrong first step in order to make more profit. Or rather, you would not be taking any steps in order to achieve success. Because this organization of your leads helps you to focus on your aim.

Not Wasting Any of Your Resources

Without any management, you would be wasting your resources such as time, money and workers on the wrong people; people who are not interested in your products or services. Tracking your sales leads will get you out of these dead ends and help you focus on customers who do want to buy your products and services.

 Budgeting

Lead tracking not only provides the opportunity for increasing your revenue but also assists you on how to allocate your financial resources. Tracking and organizing sales leads result in the detailed ‘big picture’ of your financial affairs. It includes your revenue, your sales, your marketing budget, your total spending, etc.

Calculating them and organizing every little spending or financial gain will eventually help you understand your business’ patterns, financial performance and also, get better at predicting your company’s financial future. You would be setting sales goals, at the same time, limiting your spending budget.

Improving the Sales Stages

This does not mean lead management will help you to force your products on your customers. But it would show you at what stage your customers refuse to buy your products and services and also, at what stage they become convinced that they want to purchase them. For example, your sales lead management shows that the later you contact your customers, the less they buy the product. By adjusting your contact time and customer service, you would be in the position for more sales, for more customers and -of course- more profit.

How to Organize Your Leads in Excel

The way you choose to track and organize your leads completely relies on you and your company’s needs. Before you gather your leads and put them in a spreadsheet in Microsoft Excel; first, decide on what your company has now and what its needs are. You need to assess the aspects of your business related to your sales leads which are provided below:

Budget

Tracking and organizing your sales leads in Excel can range from a basic spreadsheet to a detailed customer relationship management (CRM). Even though CRMs can help you in the long term, they are the more expensive choice compared to lead management systems.

Ask your supervisors or head of budgeting in order to allocate the necessary and given amount of money on tracking and organizing sales leads. The choice for using either a lead management system or a CRM depends on the budget you receive and the company’s willingness to invest the required amount.

Goal

What is the amount of your annual customers? What is the company’s goal in regard to quote or revenue? How many customers is the company dealing with every year? The answers to these questions will help you to decide on a lead management system. Thus, you can create a plan to track your sales leads after you determine the financial goals of the company.

Employees

While you determine the financial aim of the company, you have to adjust your employees for that aim as well. Tracking potential customers and following through communication is not an easy or a short term task. You need a team of employees that can make constant calls to potential customers, tirelessly keep in contact with emails, analyze information and always track new clients. The people you hire and the number of people in the team determine your willingness to achieve the company’s goal. Make the company’s goal, your goal; then, you would continue in a much more determined and enthusiastic manner.

Analysis

Growing any business relies on the analysis of past mistakes. If you do currently have a lead management system (or a customer relationship management system), look for their weaknesses. By analyzing what can be improved upon, you would pinpoint the problem. In other words, find the problem and then, find a solution for the mentioned problem.

Records

Perhaps the most important part of tracking and organizing your leads in Microsoft Excel is the recording process. But the question becomes: Which information would you want to record? It is an intricate and tricky process to figure out which information to note and keep and which information to do not. It depends on your business’ area and the customer information to write down changes accordingly. For example, you would think that the shoe size of your potential customer would not be important, right? However, what if you are a shoe manufacturer? The shoe size of your potential customers would be the number one information you would keep in your recordings. But regardless of the field of your company is operating in, there are some information and contact details of your potential customers which you should always record and keep:

General Information:

• Name of Business
• Name of the Person Contacted
• Business Category
• Communication Progress: This shows the stage which you or one of the employees in your team got into. How much progress they achieved while talking to the customer would be noted. And also, where would the percentage be in regard to the customer’s eagerness to purchase the product and service can be detailed as well.
• Communication Status: This information exhibits the method the customer was contacted. This can be an email, a phone call, a direct message or even a comment reply in any of the social media platforms.

Address Information:

• Address of the Business (or the Personal Address of the Customer)
• Country
• Region
• State
• City
• Zip Code

Contact Information:

• Phone Number
• Email
• Alternative (or Secondary) Email
• Website of the Company
• Fax Number
• Additional Notes

How to You Import Your Data into a Spreadsheet

By determining the aspects mentioned above in the previous chapter, you can start your lead tracking process. However, lead management is all about making decisions and applying them to your work. That is why another important decision for you to make is the way you choose to import your data into a spreadsheet.

Today, in the digital era, there are countless applications, online tools and third-party software for you to import and enter your data onto a spreadsheet. But the most used and useful programs are Microsoft Excel and Google Sheets. While Excel is the more traditional way; Google Sheets offers a lot of different approaches to import your data from external applications and software. Let’s look at the different ways you can import your data into a spreadsheet using Microsoft Excel or Google Sheets.

Methods in Microsoft Excel

Manually import your data into Excel:

– Perhaps the most basic but also the most primitive approach you can follow is the manual way of importing your data. This means manually, one by one entering all of your data from an external source. This is also the most time consuming and energy using the method of importing your data in a spreadsheet. The only way this method seems understandable to use is if you are transferring your data from a piece of paper into a spreadsheet. Otherwise, try to avoid this extensive and burdensome way of putting all of your data into a spreadsheet.

– Microsoft Excel has nearly more than 400 functions in order to help you make your data input process easier and faster. Excel is great at creating basic data processing systems; therefore, it would also come in handy when you are tracking and organizing your sales leads.

Excel Add-Ins or Third-Party Templates:

– Or if you think that Excel’s built-in functions are not sufficient for you to successfully import your data, you can always try using add-ons and other tools such as third party templates.
Imagine a lead tracking template which you can create basic yet professional tables. And also, it provides you with dropdown menus so that you can easily choose and update your sales ‘Status’ and ‘Communication Progress’ with your customer. Now, imagine this template being free. It seems impossible, right? Actually, it is not! This Lead List Template offers to record and keep track of your customer data in order to follow your sales leads. It is compatible with both Windows and Mac devices with no installation needed, immediately ready to use after you download it. And also, it is completely free!

– The other option you can import your data into Excel is to do it via a database. If you click ‘Data’ on the upper right corner in the toolbar, you would see the option for ‘Get External Data’. This is the main tool for you to import your data from a database. You can choose to import your database through Microsoft Access, from any website, from a text based database or from other (third party) sources.

Methods in Google Sheets

Manually import your data into Sheets:

The manual process of entering your data in the application is almost the same with both software; however, after you enter your data, it is simultaneously saved and uploaded into the cloud in Google Sheets. That means without remembering to save or update your document in Google Sheets CRM solutions, it is saved in the Google servers; therefore, it becomes accessible from any digital device you can log in to your Google account.

Third-party tools and add-ons:

One of the most popular ones is called Supermetrics. It is designed for you to import your data from a lot of different social media platforms such as Facebook, Twitter, Instagram, Reddit, etc. and also ad platforms such as Google Adwords and Facebook Ads. And it is super easy to use it. You just install it; select the platform you want to import your data and make queries. And even if it is not opened, Supermetrics schedules automatic refreshes and updates on the data you imported.

Import your data from an established database:

The same as Microsoft Excel. Again, if you use the Supermetrics Sheets add-on, you can also import your data from an SQL server.

Import from different formatted data storages:

If you keep your data in an XML, HTML, RSS or in a CSV format, it is also possible to import the data stored in them into a Google Sheets spreadsheet. This means you can bring in data from blog posts, product descriptions from different online stores and URLs in general.

Write your own custom code:

If you are knowledgeable in the technical side of data importing, you can use the cloud-based scripting tool Google Apps Script (which is also a programming language) to write code in order to automate different tasks and make the process easier for yourself.

These are the ways for you to create spreadsheets consisting of basic tables with your imported data. However, if you want your lead list to be a multi-spreadsheet and detailed list of sales leads, you can turn into creating a customer relationship management (CRM) system as well.

Can Excel Be Used as a CRM?

The short answer is resounding yes! However, in order to use Microsoft Excel as your CRM, you need to understand what CRM is and does. Simply put, a CRM is no different than an agenda or a planner that you used in the 1980’s. Although the concept is the same, with the developments in the digital age, the functions and the usability of the concept is very practical.

CRM strives for accessibility from any digital device, connectivity with your team of employees, accurate and detailed analysis reports, vast customization catalog to ensure you meet the company’s requirements. It also offers security for your sensitive data and automation for an easier process.

Now, it is possible to use Microsoft Excel as a CRM; however, there are pros and cons to this integration:

+ Excel is the most familiar spreadsheet program around the world. Every accounting firm and every seller records and tracks their customer and company details in same shape or from in Excel.

+ You can download and integrate endless amounts of templates in order to improve your CRM system. You can find some of these templates to help you to create your CRM system below.

+ Although it is originally a spreadsheet tool, it is much cheaper especially compared to its competitors such as complicated CRM software.

+ As it is mentioned, other CRM applications and tools are complex and almost all of them are built for professionals in the business. Excel is built for everyone to easily learn and start using it, even if you want to use it as a CRM.

– Perhaps the only and the most important feature Microsoft Excel lacks is the ability for collaboration. You have to save and send the .xls or .xlsx files in order to collaborate between your team of employees. Also, it lacks the simultaneous option of live editing and collaborating on the same file.

Best Excel CRM Templates

Vertex42’s CRM Template offers a wide range of tables. You can enter and adjust your data in the Contact Log, Sales Log and Contact Details. It also provides a table for only your Team Members, so that you can easily assign your employees to different roles and detail the number of customers they deal with. The template is also free, you can download it now and use it in Excel as your CRM!

Chandoo’s different Sales Dashboards templates from different developers offer multiple versions of the same concept. Some of the templates have dynamic and automatically updated dashboards; some of them offer fewer visual charts and focus on the readability of the data. Read their descriptions, select which one you prefer and download it. The majority of them are also free!

TemplateLAB also offers different CRM templates; however, their design is mostly based on the simplicity of data importing. You can only enter your data in columns and rows without the option of integrated charts of graphs.

Someka’s sales lead tracking Excel Template Lead List has the framework for tracking your contact list as well as customization for each dropdown menu you encounter with. Clean yet professional design is also exportable as a PDF as is the case with every Someka template. We also provide a Customer Follow Up Google Sheets Template for those you prefer an online file to track their lead info. And it is completely free to download now!

If you only need a simple contact list, you can also download Contact List Excel Template or Contact List Google Sheets Template from Someka portfolio.

How to Create a CRM in Excel

Although the Excel templates provided above in the previous chapter will help you a lot, there are some components of a CRM you should include in Excel.

– Task Log: In order to track your meetings, tasks, employees and -of course- your leads, you need a list. Think of it like a ‘to-do list’. This will help you, first of all, to engage with your employees and be on the same page as them. And then later, when dealing with customers, you can easily track what to do next. If you are in search of an Excel template for a task log, try using Someka’s Team To Do List Template. It is filled with functions such as the ability to monthly track your employees’ tasks and achievements. It also features ready-to-use assignments and a visual progress chart. And the best part? It is free!

– Calendar Sheet: As it is mentioned in this blog post, CRMs can be looked at as a calendar as well. Someka’s Automatic Schedule Planner offers a jobs view and a full calendar view in its unique automatic schedule generator in Excel. It is time saving because nearly every action in it is automated and it is also a money saver as well due to the fact that it is cheaper compared to other calendar tools.

–  Payment Tracking: Payment tracking section can be similar to a Enterprise Resource Planning (ERP) system. It can be a little complex and too detailed for you to include in your already crowded CRM system. If you want to add an ERP system into your CRM as well, you can use Someka’s extensive Small Business ERP Template. It offers future sales targets and leads for your company as well as product costs, invoices and purchasing records.

– Kanban/Project Management Sheet: You have to track your projects and important events in order to create a detailed CRM system. Someka already has a free Kanban Board Excel Template for you to download and use. It has an advanced analysis reporting accompanying a detailed tracker for your different processes.

– CRM Template: You can also download Someka’s CRM Excel Template or CRM Google Sheets Template to try all those features in all-in-one file.

FINAL WORDS

You can use Microsoft Excel as your main lead management system. On the other hand, you can also use Excel as your customer relationship management system. Or even you can maintain your customers’ data as a database in your Excel spreadsheets. The best part about using Microsoft Excel on your accounting and financial processes is that you have the option to do whatever you want. You should plan and base your processes on your company’s requirements and needs. And Excel will guide you along the way.