Data quality

Consistent CRM data: one set of values every rep uses

Every pipeline report groups records by a handful of fields: status, stage, source, territory. When each rep types those values their own way, the report counts the same thing three times and misses the rest. This article follows one illustrative team through the usual spreadsheet workarounds, looks at what published research and Microsoft’s own documentation say about their limits, and shows how Saleslox keeps one agreed list of values on every record.

The scenario: fourteen statuses where there should be four

Illustrative scenario. Harbourline Facility Services, its people and its numbers are invented to show a common pattern. It is not a Saleslox customer, and no results are claimed for it.

Harbourline sells cleaning and maintenance contracts to offices and warehouses. It has twelve people in sales, a shared “Accounts and deals” workbook, and a few personal copies that reps say they will merge back “at the end of the month”. The workbook has a Status column for each account, a Stage column for each deal and a Source column for where the enquiry came from.

The sales manager, Dana, wants a simple answer for the quarterly review: how many dormant accounts do we have, and which lead source produces the deals that reach proposal? She builds a pivot table. The Status column comes back with fourteen distinct values: “Prospect”, “prospect”, “Prospect ” with a trailing space, “Prosp.”, “Warm”, “Dormant?”, “Inactive - call in Q1” and more. Stage has “Proposal”, “Proposal sent”, “Quote” and “Prop”. Source has “Web”, “website”, “Website form” and “Google”, which may or may not be the same thing.

Nobody did anything unreasonable. Each rep wrote down what they meant. But the review now starts with an afternoon of find-and-replace, and the numbers Dana presents depend on her judgement about which labels mean the same thing. Next quarter someone else will make different calls, and the trend line will move for reasons that have nothing to do with sales.

The problem: reports trust labels, and people type labels differently

Grouping, filtering and counting all assume that one value means one thing. A filter for “Proposal” does not find “Proposal sent”. A count of dormant accounts misses the ones marked “Dormant?”. A forecast by stage is only as good as the stage names behind it. Inconsistent values do not cause a visible error; they cause quietly wrong totals, which is worse, because people act on them.

The damage spreads beyond reporting. Handovers are harder when a departing rep’s idea of “warm” is not written down anywhere. Follow-up lists built on a status filter skip records. And every new hire learns the labels from whoever sits next to them.

What teams use today, and what it costs

Free-text columns and a “how to fill this in” tab

The first fix is usually a tab of instructions listing the allowed values. It helps for a week. Instructions are not enforced, and the people who most need them are the ones who never open the tab.

Excel data validation drop-downs

The better fix is a drop-down list using Excel’s data validation, which Microsoft documents step by step: choose List, type the allowed values and keep the in-cell drop-down ticked [3]. It is a genuine improvement, but Microsoft’s own guidance lists its limits. Validation is designed to show messages and prevent invalid entries only when users type data directly in a cell; when data is copied or filled, the messages do not appear. Validation added to cells that already hold data does not automatically flag the existing invalid entries, so someone has to run Circle Invalid Data. And only the Stop alert actually blocks a bad value; the Warning and Information alerts let users accept it [4]. In a sales workbook, where rows are pasted in from exports, emails and personal copies, the paste is exactly how bad values arrive.

There is also the copy problem. Change the allowed list in the shared workbook and every personal copy keeps the old one, so the drift starts again the next time a copy is merged back.

The monthly clean-up

So someone, usually sales ops or the manager, runs a clean-up before each review: sort, find-and-replace, fix the outliers, and hope nobody edits the file in the meantime. The clean-up treats the symptom. It also makes the person doing it the unofficial owner of what every label means.

What it adds up to

Gartner says poor data quality costs organisations at least $12.9 million a year on average, citing its research from 2020. It names inconsistency in data across sources as the most challenging data quality problem, and reports that 59% of organisations do not measure data quality at all [1]. Those are enterprise-wide figures, not a measure of any one sales workbook, but the pattern is the same: data kept in silos with overlaps and gaps, and nobody clearly responsible for it.

Sales teams feel it directly. In Salesforce’s sixth State of Sales report, a survey of 5,500 sales professionals in 27 countries, only 35% of sales professionals said they completely trust the accuracy of their organisation’s data, and reps reported spending 70% of their time on non-selling tasks [2]. A monthly label clean-up is one more of those tasks, and a report nobody trusts is one more reason to rebuild the numbers by hand.

How Saleslox keeps the values consistent

The fix is not a stricter workbook. It is choosing the allowed values once, in the system where records are created, so reps pick from a list instead of typing. The screenshots below are real Saleslox screens from a demo workspace on the Enterprise plan; where a feature depends on the plan, we say so.

Step 1: agree the list once, in Field options

In Saleslox, an admin sets the allowed values for a field on the Field options screen, grouped by area such as Companies and Leads. The Companies Status field, for example, is described on screen as used across boards, filters and record forms wherever this field appears on that tab. The admin types the options as a comma-separated list, saves, and can restore the defaults at any time. Here the demo workspace has trimmed the default list of active, prospect, incomplete, dormant, inactive and merged down to four values. That is Harbourline’s fourteen statuses, decided once.

Saleslox Field options screen showing the Companies Status field with the options active, prospect, dormant and inactive, the default values listed underneath, and Save and Restore defaults buttons
Field options for company status. Screenshot from the Enterprise Plan

Step 2: decide which fields each record shows, in App Designer

App Designer lets an admin choose the fields on each record type, from Companies and Leads to Opportunities, Quotes and Orders. The Available fields list shows each field’s type, such as text, select, lookup or phone. Status is a select field, so reps choose from the agreed options rather than typing, and Territory is a lookup, so it points at a real territory instead of a spelling of one. Tick a field to add it to the record, preview the layout, and save it for the whole team. App Designer is on the Enterprise plan.

Saleslox App Designer for Companies with an Available fields list showing field types such as native text, select, lookup and phone, a live preview of a sample company record, and Field options, Restore default and Save layout buttons
App Designer with field types and record preview. Screenshot from the Enterprise Plan

Step 3: stages are a board, not a column of text

Deal stages are where free text does the most damage, so in Saleslox they are not typed at all. The opportunity board shows the pipeline as columns, Lead In, Qualifying, Proposal, Negotiation and Closed, each with its total value and deal count. Every card carries a stage selector, and a deal moves by dragging its card into the stage that fits. There is no “Prop” or “Proposal sent” to reconcile, because there is only one Proposal column. The board can be grouped by salesperson, company or contact, and searched by deal name, company, contact, salesperson or deal source.

Saleslox opportunity board with Lead In, Qualifying, Proposal, Negotiation and Closed stages, each showing total value and deal count, and deal cards with value, probability, a stage selector and days in stage
Opportunity board with fixed stages. Screenshot from the Enterprise Plan

Step 4: the review starts from the numbers, not the clean-up

Once every record uses the same values, the board totals, filters and dashboards count each record once. Dana’s two questions become a filter on company status and a look at which deals have reached the Proposal column, with no afternoon of find-and-replace first. The dashboard designer, on Pro and Enterprise, can turn the same fields into tiles such as pipeline by stage and leads by status, built once for every review. And because the list lives in Field options rather than in someone’s head, changing it is a decision the team makes on purpose.

Spreadsheet workaround vs Saleslox

How field values are kept consistent in a spreadsheet setup compared with Saleslox
TaskSpreadsheet workaroundSaleslox
Where the allowed values liveAn instructions tab, or a validation list in one workbookField options, set once by an admin
Pasted or filled-in rowsValidation messages do not appear for copied or filled dataReps pick from the agreed options on the record form
Existing bad valuesNot flagged until someone runs Circle Invalid DataDecide the list, then enter records against it
Changing the listPersonal copies keep the old listOne saved list, used across boards, filters and forms
Deal stagesA text column with many spellingsFixed board columns with a stage selector on each card
Which fields a record showsWhatever columns each copy hasOne saved layout per record type in App Designer (Enterprise)
Before each reviewFind-and-replace clean-upFilter the board or open the dashboard
“What does warm mean?”Ask whoever cleaned the file lastRead the agreed options on screen

How to test this on your own data

  1. Open your current workbook and build a pivot table of the distinct values in Status, Stage and Source. Count them.
  2. Agree the short list each field should have, and write down which old labels map to which new value.
  3. In a Saleslox trial, set those options in Field options and add a handful of live accounts and deals using them.
  4. Run your usual review question against the board and filters, and compare the time with your last clean-up.

The trial is 30 days of Pro, with no card required. For the weekly numbers that depend on these fields, see sales reporting without the Friday spreadsheet or running the Monday pipeline meeting.

Frequently asked questions

What are field options in Saleslox?

They are the allowed values for a field, such as company status or lead status, set by an admin as a comma-separated list. On screen, Saleslox describes them as used across boards, filters and record forms wherever that field appears on its tab.

Can we go back to the default values?

Yes. Each field on the Field options screen shows its defaults and has a Restore defaults button.

Will Saleslox clean up the values in my existing spreadsheet?

No. Decide the target list first and map your old labels to it before you enter records. Once the list is set, new records use the agreed options.

Which Saleslox plans include this?

App Designer is on Enterprise, and the dashboard designer is on Pro and Enterprise. The screenshots are from Enterprise; see compare plans for what each plan includes.

Is Harbourline Facility Services a real Saleslox customer?

No. Harbourline Facility Services and every person and number in its story are invented to illustrate a common pattern. No customer results are claimed.

Sources

  1. Gartner. “Data Quality: Best Practices for Accurate Insights.” gartner.com
  2. Salesforce (25 July 2024). “Salesforce Report: Sales Teams Using AI 1.3x More Likely to See Revenue Increase”, summarising the sixth State of Sales report. salesforce.com
  3. Microsoft Support. “Apply data validation to cells.” support.microsoft.com
  4. Microsoft Support. “More on data validation.” support.microsoft.com

Give every rep the same list to pick from.

Start your trial now to get UP TO 50% off Black Friday specials, sign up to receive your code.