When working in Excel, data management best practices can give you a leg up with your contracting, pricing and forecasting analyses

Matthew Trinh
Jun 2
3 min read

Excel Spreadsheet on a computer screen showing quarterly financial data

For many of the manufacturers we work with on Excel-based planning use cases, obtaining data and setting themselves up for success is a struggle. The following insights are a good place to start when considering data management steps to support solutions for areas like Pre-Deal Assessment, GTN Channel Forecasting and Analytics in general.

By implementing a few smart strategies, you can streamline your processes, reduce errors, and keep your stakeholders happy. In this blog, we will dive into some of the best ways to keep your data clean, consistent, and collaboration friendly.

Data Management Best Practices in the Excel Realm

Excel is frequently the automation selection for emerging and smaller pharma. To maximize its effectiveness, there are some good techniques that make things easier when it comes to utilizing the data within your workbooks.

Use Flat Tables

Flat tables are the workhorses of Excel data management — simple, reliable, and easy to use. They offer:

Effortless querying and analysis.
Compatibility with analytics tools and SQL-based systems.
Reduced complexity in data manipulation and reporting.

Rather than juggling disparate tables or scattered datasets or building complex and difficult to maintain VLOOKUPs, consolidate your information into a single, well-organized flat table. With everything laid out clearly, your data processes will run much more smoothly.

Format Codes as Text Instead of Numbers

Have you ever tried to pivot data by a numeric code and the pivot table automatically summed up the codes thinking they were aggregable numbers? Here’s a quick way to save yourself a lot of trouble: always store codes as text. When treated as numbers, codes can lose leading zeros or fall victim to automatic formatting quirks. To keep your data pristine:

Always designate code fields as text.
Avoid unintended reformatting by spreadsheet software.
Preserve the exact format of your identifiers.

Think of it this way: numbers are for math; codes are for identification. It’s best to keep them in their respective lanes.

Use Customer and Product Codes for Cleaner Matching Criteria

Invest the time to establish good data management capability in the areas of Customer and Product. Customer names and drug names might work for everyday conversations, but without strong data management, they can create unnecessary headaches. How many times have you accidentally mistyped a customer’s name or had to map together a customer’s name coming from two different source systems together? Unique codes provide the standardization and “handles” required for integration of data sources and analyses:

Avoids issues with symbols, special characters, and case sensitivity.
Integrates seamlessly across systems.
Simplifies audits and cross-referencing by keeping things consistent.

Implement Basic Data Validation

Data validation is your first line of defense against errors, and the foundation of trusted and reliable data analysis in any tool, not just Excel. By catching issues early, you ensure that only high-quality data enters your models. Best practices include:

Range Checks: Verify that values, such as quantities or discount rates, fall within acceptable limits.
Mandatory Fields: Ensure critical fields like NDC codes and dates are never left blank. Excel’s conditional formatting to highlight these and other issues is a great way to quickly spot items that need to be addressed.
Consistency Checks: Regularly review your data and confirm logical relationships between fields, such as matching NDC codes with product descriptions.
Format Enforcement: Standardize inputs, such as using YYYY-MM-DD for dates or enforcing uppercase for text fields.

While validations like these might seem like no-brainers, their execution can quickly slip through the cracks when operational teams get busy. Keeping validation operations running on a regular basis will help to avoid significant and time-consuming hurdles during solution design and build projects.

Conclusion

Practical data management is essential for effective Excel modeling solutions for manufacturers. By ensuring the use of fundamental elements like unique codes, universal identifiers, appropriate data formatting, and basic validations, you’ll avoid the unpredictable results and subsequent unproductive time required to fix the same errors over and over. These strategies improve the accuracy of Excel modeling and planning, allows analysts to focus on assumptions, trends and scenarios, and builds confidence in the results across all stakeholders.