Uneven battle: CRM versus Excel. Creating a database in Excel: step-by-step instructions

Good afternoon, colleagues!

In today's video, I want to introduce you to the most essential Excel function for purchasing, logistics and category managers - the VLOOKUP function. Microsoft Excel is a program for working with spreadsheets. Excel is used daily for calculations, for analysis, and for collecting data necessary for convenient form. But here interesting fact, while this tool is used on a daily basis, there are not many professionals who can say that they are very proficient with this tool. There are many reasons for this, and perhaps in other materials I will talk about this, but today I want to introduce you to the most necessary Excel function - the VLOOKUP function.

How is it useful?

For those who want to become an Excel master and master all the necessary Excel functions to improve the quality and speed of my work, I have prepared a practical online courseThe power of Excel for purchasing, logistics and category managers." Online course is 16 lessons in video tutorial format, 20-30 minutes each.

Note: You can download the Excel spreadsheet from the video tutorial. Leave yours in the form email address and get a table with an example.

READ THIS ARTICLE:

Calculating an order to a supplier based on intuition: good or evil?  For those who want to quickly and efficiently pay orders to suppliers, taking into account future sales, order fulfillment time, frequency of order placement, warehouse balances, balances in transit, I have prepared a practical online course “Managing...

To forecast demand or not – that is the question. Part 1 Perhaps this is exactly how the English classicist would pose the question if he were now engaged in inventory management. Indeed, in my opinion, this question is the cornerstone issue in modern planning. Why exactly this question? ...

Large inventories in a company: is it good or bad? Can you say - are stocks good or bad? when a company has large inventories, is this a positive thing or not? It is very difficult to answer this question unambiguously - this is where the duality of reserves manifests itself. On the one hand - co...

Do you know the name of the worst CRM system in the world? MS Excel. This joke goes around among Western and Russian enterprise software vendors. Excel, a legendary product in in a good way This word is surrounded by dozens of myths: everyone knows Excel, you can do everything in it, you don’t need a CRM system with it, etc. Time after time, on professional forums, from future clients, and from companies, we hear a persistent comparison between CRM and Excel. Of course, from the developer’s point of view, this is an unequal battle, but we still decided to make a detailed comparison.

Spreadsheets vs DBMS

What is Excel? By its class, it is a table processor or, more simply, a spreadsheet - an application program for solving computational problems. Spreadsheets (and here we are talking not only about Excel, but also many analogues and competitors) were doomed to success in business and in general in any office work, because initially most calculations were performed in tabular form. Literally back in the 90s, on tables one could see printed ledgers, balance sheets, various time sheets, estimates, reporting forms, calculations, etc. All this documentation was nothing more than tables.

The spreadsheet as we know it today has replaced a fairly large block of programming - what was previously solved on a computer only using code can now be done using various formulas, macros, functions. Tables reflect relationships, allow you to save and reuse calculations, build diagrams, etc. One of the main advantages of spreadsheets in general and Excel in particular is the recalculation of formulas “on the fly” for any change in input data. Accordingly, this provides modeling, forecasting, planning and analysis capabilities. Moreover, all of the above capabilities become available to users who are far from computer science and mathematics.

An added value of Excel is the ability to create custom functions and scripts in Visual Basic for Application. The necessary code is written in a third-party editor and then runs in the table, processing the input data. In fact, the table becomes an interface to the VBA code. To create such functions, programming skills are required - the average user will not be able to do it.

But, as you know, the devil is in the details - and real hell can happen if you overestimate tables and start using them as the only storage of information, or, in other words, as a database. At first glance, it seems that everything is just fine: data can be written and rewritten, you can search for information using necessary conditions, sort, make selections using filters. However, compared to relational DBMSs, which underlie most CRM systems, data manipulation operations in Excel seem insignificant. In addition, tables are not as secure as a DBMS.

By the way, it’s worth mentioning separately about mastering tables. Of course, they make life a lot easier for many managers, accountants and even engineers, but if someone tells you that he is an “Excel guru,” then most likely the person is mistaken. Mastering Excel at the advanced user level is hardly easier than mastering a new programming language. But such confidence exists in the ranks of the companies, and this is where the roots of the whole group grow typical mistakes working with table processors.

A story from life. In one company there was that same “Excel guru”. He held a leadership position, and because of this privilege, he decided to transfer all his subordinates to automation. The department's budget, plans, work reports and KPIs began to be calculated exclusively in Excel. As long as he collected and then aggregated the information himself, everything was relatively good. When everyone started working with Excel workbooks in folders shared on the server, unusual data like KPI +370% to wages or a six-figure sales target. What they got burned on:

• on formulas - stretched formulas across columns without thinking about absolute and relative cell references
• on the connection of tables - an error in one table led to the propagation of errors throughout the entire improvised database
• on data formats - although attempts were made to unify the input, copy-paste and crooked hands did their job, and as a result, due to different formats, some of the calculations turned out to be incorrect
• on hidden rows and columns - someone hid them, while others did not notice and used these ranges in calculations
• on sorting - when sorting, they forgot to select the entire array and, as a result, the values ​​of one field were assigned to another
• on pivot tables - some managers had problems with presenting dependencies
• on rounding - someone liked the numbers without kopecks, he customized the tables for himself, and these kopecks began to add up to discrepancies in rubles.

In general, we set up a CRM system, transferred the remaining “surviving” data and began to work calmly, because the program itself knew which formulas to calculate, what data to accept, how to sort. In addition, DBMS tables turned out to be much more difficult to break and lose, and with backups this is generally impossible. Such an IT happy ending.

CRM from the inside

Now about the CRM system, which consists of logic + DBMS + interface. The relational database used in CRM is a set of interconnected tables. Each row (called a record) describes one object (for example, a customer), and each column contains attributes of that object (for example, contacts, accounts, purchases, discounts, etc.). To identify records (rows), a primary key is used - a set of fields, the combination of which uniquely identifies a record in the table.

For example, let's say you use a CRM. When you upload a report on which customers made a purchase in the current month, something like the following happens inside CRM: the “Customers” table is linked by a unique identifier (key) to the “Purchases”, “Payments”, “Items” table, etc. ., depending on what information you want to receive in the final report. At the same time, the DBMS should ideally work as quickly as possible, so that the user does not even notice the process. It is thanks to this architecture that CRM can quickly and accurately build samples in any context, allowing you to instantly find calls, transactions, and documents related to the client. It is clear that such functionality cannot be implemented in Excel. Even a combination of two or three tables already gives noticeable “brakes” when working with documents, and online it often simply does not allow you to complete the operation.

In addition, relational databases, when properly managed, ensure information security, protection from unauthorized access, data integrity and consistency. They support multi-user operation without collisions or conflicts. All actions are logged and you can almost always find the person whose steps led to an error or intentional harm.

Again, the separation of access rights provides protection against the novice, the fool, and even bad intentions. So, for example, an employee (a simple manager) can be given rights only to write data, and editing and deleting are not available to him. Thus, the maximum harm from an inexperienced employee is entering erroneous information, but not deleting someone else’s important information.

Sketches on napkins

In general, in business there are a huge number of ways to accumulate and store information about clients, and all of them coexist in our reality, although not always peacefully. So, the golden top “information carriers” of Russian small and medium-sized businesses.

In general, it is no coincidence that we raised the topic of working with data and customer relationships in CRM against tables - there are too many shortcomings in spreadsheet processors that want to become a CRM system.

Let's unequal battle begin

Spreadsheets limit employee and management access to information. And this is a significant drawback. You can say that there are spreadsheet editors with online collaboration capabilities, however, firstly, even Microsoft’s versions have weaker capabilities, and secondly, access can be closed at any time just by changing the link.

CRM systems allow you to fine-tune access rights at the level of groups and individual users. At the same time, the manager can easily view the profile of work with each client, as well as set tasks, generate reports, etc.

Tables are nearly impossible to keep track of. full path relationships with clients. You cannot add interaction steps (the most popular way for managers is to add official notes as notes), conversation records, or correspondence history. If some issues were resolved through mail or negotiations, information on them may be included in the notes or not included at all.

The CRM system tracks the customer's journey from cold lead to upsale, without missing the slightest interaction. For example, in RegionSoft CRM all calls with call recordings, all correspondence with the client and all actions of managers are stored in the client’s card and this information can be accessed almost instantly. Based on the results of client development, you can generate and print a report on the dynamics of the relationship, which will display all the impacts.

In Excel and other spreadsheet editors, it is impossible to track the work of managers and adequately respond to crisis situations. KPI matrices, of course, exist in tables, but there is no need to think about flexible configuration - everything is limited to a few written formulas. In CRM, the dynamics of task performance by managers is available to line and senior management, shortcomings and failures are immediately visible, you can quickly respond to them without waiting for the end of the month and filling out the “table of key coefficients.”

- Ed, according to my chart, you did a terrible job.
- Because your tables are poorly designed and have nothing to do with complexity real world. And yes, let me remind you that the formula points to the wrong cell.
- The numbers don't lie.

It is difficult to evaluate potential deals, stalled deals, and stages of working with a client in tables. At the core of customer relationships from a commercial perspective is the sales funnel. It can be drawn in Excel; dozens of templates for this are described on the Internet, but analysis of the funnel in various sections (by manager, dates, companies, items, etc.) is possible only in CRM systems. The biggest challenge of working with deals in Excel is the need to enter all the data manually, from the cost of the order to the status and development stage of the client. Naturally, sooner or later the manager stops this work or forgets about it. In CRM, after correct initial setup, statuses and stages change automatically, and costs are “adjusted” when creating and posting an invoice.

Excel provides rather weak reporting capabilities; a lot of time is spent on data processing, making selections and analysis. Here it is worth describing the real situation. The client sent a sales report for the period to the head office. The file size was about 50,000 lines. Irrelevant and unnecessary data was included in the upload. Sorting and cleaning data took the manager exactly one working day, and this was with special formulas and macros. CRM allows you to make such selections using flexible filters with many criteria, in addition, custom filters can be saved and used as needed (in any case, this is how this function is implemented in our RegionSoft CRM).

In CRM you can set up several levels of access to customer data, but this is impossible in Excel. Maintaining a customer database in spreadsheets is fraught with security problems for a business: data can be lost both for technical reasons and when an employee leaves. What cannot be taken away from Excel is its easy “transportation” outside the business, including into the hands of competitors.

To take data from desktop CRM, you need to try. All operational information about clients, contacts, plans, projects, tasks should be in the CRM system, and not “in the employee’s head.” This CRM system must be secure, i.e. have technical capabilities in which an employee will not be able to remove information from it (unless, of course, he has been given administrator rights for the data server). For example, RegionSoft CRM has a deep security design, starting with integration with ActiveDirectory and ending with control of IP addresses, MAC addresses from which the user is allowed to connect to the system, connections to the corporate database via alias (when the user does not know the real location of the database) and etc. The combination of these measures provides a safe environment for business. But there is always a human factor that cannot be avoided. Today a person is loyal, but tomorrow the devil hits him in the ribs - and off we go - your competitors are already selling to your customers. Therefore there must be exactly set of measures, so that even if there is a desire to leak data, make it as difficult as possible technically.

Tables provide only a minimal set of functions for customer segmentation and personalization of influences. One of the principles of successful interaction with customers is personalization. Probably, this can even be called the main factor of survival in the growing information noise. Spreadsheets allow you to create selections and groupings, but it is already difficult to conduct mailings and other communications based on them. In a CRM system, you simply make the desired selection, save the segment and launch a newsletter specifically for the group you need to inform - from the system interface (in RegionSoft CRM, the newsletter can be launched using your own email client).

In Excel, data unification problems constantly arise - format settings set by the document owner are confused, changed, or ignored. The result is collisions, most often associated with monetary and text formats, and percentages. In a CRM system, the developer does everything to maximize the unification of data: for example, when entering, you can select a value from the list (so that there is only “Moscow” and not “Maskva”, “Moskva”, “MSK”, etc.) or enter a value that is checked by the program as you enter it. If the input is incorrect, the CRM system returns an error message.

Excel does not have adequate integrations with other business systems and services, for example, planning, business processes, telephony, 1C. Someone will say that in Excel, maybe not, but in Google Docs tables there has long been a whole store of applications, add-ons and add-ons. Let's not argue, Google offers a good tool, but for a private user or a very small beginner team. Firstly, a more or less loaded file hangs the application (it couldn’t cope with the budget of one of the companies for 16,000 lines of formulas). Secondly, again there are no integrations listed. And the reason rests on the same backend: Spreadsheets are still the same calculator tables.

CRM integrations vary greatly from system to system and can be implemented using APIs, connectors, special software, and third-party plugins. We have implemented in our CRM system integration with the website, telephony (including virtual PBX Asterisk), Skype, 1C. Part of the integration work falls on RegionSoft Application Server, which provides more fine settings and ample opportunities for automatic data exchange between the CRM system and third-party applications.

It is impossible to carry out competent personal and collective planning in spreadsheets. Here even comments are unnecessary - no planner, no reminders, no interactive work With tasks, you shouldn’t expect spreadsheets. While, for example, among RegionSoft CRM clients, schedulers and task management tools are perhaps the most popular functions of the system: everyone uses them, everyone appreciates the capabilities of reminders and interactive calendar management. Among these tools, the most favorite is the three-week planner, which combines task management, work calendar and at the same time allows you to work with the planning horizon.

There are problems with attaching files in tables. The most convenient and safest implementation of this function for Excel and other spreadsheets is to write links to necessary documents. In the CRM system, all documents are attached to the client card, are easily “picked up” and are not lost.

No table can withstand rapid scaling and business growth: firstly, the books will be overloaded and the likelihood of errors will increase significantly, and secondly, adding new rows, columns, links can simply damage the carefully accumulated file. Progressive growth must be achieved while maintaining productivity, and this can only be achieved by using special means automation. For example, CRM systems. For the program, any expansion or reduction in the number of users, names, adding divisions and even a new business structure is painless.

The tables do not provide for automatic filling of information, for example, from a website or for primary documentation. Although, there is a PHPExcel library that allows you to import and export data to Excel from information systems written in PHP for any platform, but this does not make Excel a full-fledged accounting system or CRM.

The process of updating information in tables must be controlled, while CRM is a single, up-to-date source of information. In general, there is too much human factor in managing tables, entering and processing data. It is believed that all sales managers, accountants, marketers and other guys from the commercial service know Excel. This is a very illusory idea: as we have already said, mastering Excel “like an adult” requires huge amount time.

The tables do not show deep connections between the data, since this is not a relational DBMS, but a “flat” representation of the data (like one slice). Accordingly, the lack of relational tables and interface restrictions make spreadsheets just an imitation of a means of storing and managing a client base. CRM is a well-thought-out interface on top of a relational DBMS. Using the interface, data is entered, processes are managed, telephony is maintained, etc. All business information is securely stored in a database, for which, let us remind you once again, it is worth creating backup copies.

Do you know what your client expects from you at the sales and service stage? He believes that if he turned to one specialist from your company, he turned to everyone, and any manager is ready to solve his problem. And the manager must know about contact communications. This means you need to have a tool to instantly display the required information - and CRM is just such a tool. But Excel and other spreadsheets were not originally created for customer relationship management; they were created for calculations and primary analysis of information. So maybe you shouldn’t wait for customer complaints and accumulate risks that sooner or later could lead to collapse. Moreover, according to the law of meanness, at the most inopportune moment.

Tags:

Add tags

In the HR service, Excel occupies its, albeit not the main, but definitely noticeable place. However, for HR managers who are still far from sophisticated Excel tools, working with it often feels like torture. Let's fix this. And let's start with this...

About the author

Nikolay Pavlov, professional IT trainer for MS Office programs. Microsoft certified trainer, Microsoft most valuable professional in Excel (MVP).

We will look at several typical situations where Excel comes to the rescue—the ones that HR managers most often ask me about.

Training schedule

If you have encountered in your work the planning and organization of trainings for employees, then you will agree that choosing the right time for them is very important. Expensive and necessary training, but conducted at the wrong time, when employees are overwhelmed seasonal work or, on the contrary, they went on vacation - wasted company money and wasted resources.

For the right choice dates need clarity. Let's assume that two training sessions are planned for each employee per year. Let's summarize the preliminary start and end dates of training for each employee in the following table:

There is only one formula here - in cell G4 - then copied to the right until the end of the year. It will allow you to quickly change the time scale step to any desired values, thereby scaling the graph.

Now select all the empty square cells, starting from F5 and to the end of the table, moving down to the right and selecting the tab Home – Conditional formatting – Create Rule. In the window that opens, specify the type of rule to be created - Use a formula to determine which cells to format and enter the following formula:

It is a combination of two logical AND functions connected to each other by a logical OR. Each of the AND functions checks whether the date corresponding to a given cell falls within the range between the start and end dates of the training. Since we want to fill both trainings with color, the two AND functions are connected by one OR on top.

As a result we get:

Moreover, if we change the time scale step in the yellow cell E1 to, for example, a week, we get a more general picture:

Calculation of bonuses or additional payments for length of service

Suppose that wise management has burdened us with the pleasant task of calculating additional payments to the salaries of our employees for length of service (this sometimes happens in state-owned companies) or additional bonuses to salaries. Both require a complex multi-stage system of additional payments depending on the length of service of our colleagues. Let's present this in table form:

That is, if an employee has worked for us for less than 12 months, he does not receive anything. If you have worked for one to two years, you receive a 10% additional payment (or bonus). If from two to three - 15%. If from three to five - 25%, etc. The maximum bonus of 100% is given only to old-timers - those who have worked in the company for more than 10 years.

You can go the classic route and use the check function IF. Moreover, we will have to nest one IF into another several times, since we need to check if it falls into several ranges:

Brrr... Horrible, isn't it? The problem can be solved much more elegantly if you use the function known in narrow circles of financiers and analysts VLOOKUP:

The essence of the solution is that the function VLOOKUP looks for the nearest smallest value in the first column of our bonus table and returns the value from the second column next to the one found. The function has four arguments:

Search value— the value of the employee’s length of service for which we determine the bonus

Table- our bonus table. If you plan to copy the formula down to other employees, then the link to the table will need to be made absolute, that is, add dollar signs so that the link to is shifted when copied. This can be done using the F4 key, having previously selected the address in the Table line.

Column number— the serial number of the column in our bonus table, from where we take the amount of the additional payment (we have only two columns and the number is obviously 2).

Time-lapse viewing- this argument must be set to 1 for Excel to search for the nearest smallest number in the first column of the table. For an exact search, a value of 0 is used.

Competency Radar Chart

Any HR who has ever been involved in personnel selection knows firsthand how difficult it can sometimes be to select the right people for vacant positions. I think everyone can remember the consequences of a bad choice, when employees then either “cannot cope” or quickly “outgrow” their position and the process has to be repeated again, wasting the company’s time, resources and money. How to visually and qualitatively assess how suitable a given candidate is for a certain position?

In such a task, it makes sense to use, although not very common, but very convenient in this case, a type of chart in Microsoft Excel - Petal (Radial). In English terminology, this type of chart is sometimes also called Spider Chart - for its external resemblance to a web.

Let’s make a list of 5-10 key competencies (skills, requirements) for our vacant position. In this case, 0 means no requirements, 10 means maximum need. For example, for the position of Sales Director, the list might look like this:

oral and written communication skills - 8;

presentation skills - 7;

knowledge/understanding of English - 5;

knowledge of goods production technology - 2;

knowledge of finance and accounting - 7;

computer and software knowledge - 4;

Based on the results of communication with candidates (review of their resumes, interviews, testing), we can create similar list their qualities and skills with similar ratings, standardized on a scale from 0 to 10.

Now we can combine all our data into one table and, having selected it, build a radar chart based on it by selecting on the tab Insert in the group Diagram team Petal:

Additionally, to visually display the scores in the range B2:D10, I used conditional formatting with histograms (Home - Conditional Formatting - Histograms), and in the range C12:D12 - color scales (Home - Conditional Formatting - Color Scales).

What conclusions can be drawn from the diagram?

It is clearly visible that Candidate2 although he has a higher total score compared to Candidate 1 (61 versus 52), he is less suitable for this position, because he has high knowledge and skills not where needed (knowledge of technology or finance), but according to the necessary parameters ( negotiation and presentation skills) are just far behind. Candidate1 on the contrary, in terms of all the competencies required for this position, it fits into the requirements very well. If you improve him a little in presentations and negotiations, which can easily be done by sending him to appropriate training, then he will fit perfectly into this vacancy.

You can use the following formula (for cell C12) to calculate the final numeric value of “getting into position”:

SUM(IF(C2:C10<$B$2:$B$10;C2:C10-$B$2:$B$10;0))/СУММ($B$2:$B$10)+1

Please note that this is an array formula, meaning it must be entered using a non-key Enter at the end, as usual, but using the keyboard shortcut Ctrl+Shift+Enter. Array formulas differ from regular Excel formulas and allow you to work with entire data arrays at once. In the formula bar, they are displayed in curly brackets (but you cannot enter them from the keyboard). This array formula calculates the deviation of the candidate’s qualities from the requirements of the vacancy and presents it as a proportion (100% is taken to be a perfect match for all requirements). Moreover, overkill, i.e. the situation when a candidate exceeds the requirements, is not taken into account and does not give him an advantage.

All described examples can be downloaded as a file

P.S. If you have questions about the article or working in Excel, send them to [email protected]. I'll be glad to answer.

Many users actively use Excel to generate reports and their subsequent editing. For convenient viewing of information and gaining full control when managing data while working with the program.

The appearance of the program workspace is a table. A relational database structures information into rows and columns. Despite the fact that the standard MS Office package has a separate application for creating and maintaining databases - Microsoft Access, users actively use Microsoft Excel for the same purposes. After all, the program’s capabilities allow you to: sort; format; filter; edit; systematize and structure information.

That is, everything that is necessary to work with databases. The only caveat: Excel is a universal analytical tool that is more suitable for complex calculations, calculations, sorting, and even for storing structured data, but in small volumes (no more than a million records in one table, for the 2010 version).

Database structure - Excel table

Database – a set of data distributed in rows and columns for easy searching, systematization and editing. How to make a database in Excel?

All information in the database is contained in records and fields.

A record is a string in a database (DB) that includes information about one object.

Field is a column in the database containing the same type of data about all objects.

Database records and fields correspond to the rows and columns of a standard Microsoft Excel table.

If you know how to make simple tables, then creating a database will not be difficult.



Creating a database in Excel: step-by-step instructions

Step-by-step creation of a database in Excel. Our task is to create a client database. Over the course of several years of operation, the company has acquired several dozen regular customers. It is necessary to monitor the terms of contracts and areas of cooperation. Know contact persons, communication details, etc.

How to create a customer database in Excel:

The main work - entering information into the database - has been completed. To make this information convenient to use, you need to select what you need, filter, and sort the data.

How to maintain a customer database in Excel

To make it easier to find data in the database, let's organize it. The Sort tool is suitable for this purpose.

The data in the table is distributed according to the period of conclusion of the contract.

Now the manager sees with whom it is time to renew the contract. And what companies do we continue to cooperate with?

During the course of the company's activities, the database grows to incredible sizes. Finding the information you need is becoming increasingly difficult. To find specific text or numbers, you can use one of the following methods:

Through data filtering the program hides all information that is not of interest to the user. The data remains in the table, but is invisible. They can be restored at any time.

There are 2 filters most often used in Excel:

• Autofilter;
• filter by selected range.

Autofilter prompts the user to select a filtering option from a ready-made list.

Let's experiment with filtering data by selected cells. Let's say we need to leave in the table only those companies that operate in Belarus.

If the database contains financial information, you can find the amount using various parameters:

• sum (sum data);
• count (count the number of cells with numeric data);
• average value (calculate the arithmetic mean);
• maximum and minimum values ​​in the selected range;
• product (the result of data multiplication);
• standard deviation and sample variance.

The procedure for working with financial information in the database:

Tools on the Data tab allow you to segment your database. Group information in terms of relevance to the company's goals. Identifying groups of buyers of services and goods will help the marketing promotion of the product.

Ready-made sample templates for maintaining a customer base by segment.

Templates can be customized, shortened, expanded and edited.