Mitigating Risks in Hospital Stock Management: A Structured Method for Validating Excel Files

Abstract

Introduction: Validation ensures data integrity, accuracy, and usability, addressing risks such as unauthorized modifications, computational errors, and security vulnerabilities. The validation process includes steps like verifying access controls, formulas, conditional formatting, and cell characteristics to align with predefined specifications. Methods: This study, conducted in the Pharmacy of the Hospital of Specialties in Rabat, focused on validating Excel files (Excel 2013) used for data management to ensure compliance with good practices. The methodology involved collaborative development of a validation protocol, including steps like file identification, access level verification, formula testing, and review of hidden elements, cell characteristics, conditional formatting, and headers/footers. This systematic approach ensured data reliability, transparency, and adherence to regulatory standards. Results and discussion: Results demonstrate that the Excel system meets all validation criteria, with secured data protection, accurate calculations, and user-friendly visual cues for identifying critical stock issues. Conditional formatting enhances usability by prioritizing at-risk items, facilitating prompt action. The validated system supports reliable decision-making and operational efficiency, ensuring consistent and transparent medication management. While this approach provides numerous benefits, including enhanced reliability and compliance, challenges such as resource requirements, dependency on user expertise, and frequent updates pose limitations. Nonetheless, integrating validation principles into hospital pharmacy operations offers a pathway to optimizing digital tools for improved healthcare outcomes.

Introduction

Optimizing processes related to “medication stock management” in healthcare facilities is a critical challenge. Such efforts aim to improve both the quality of care and address logistical and economic efficiency concerns. These processes, including the management of expiration dates, imminent stock shortages, and average monthly consumption, rely on interoperable and communicative hospital information systems. ¹

Digitalizing medication management is therefore a key pathway for improving practices within healthcare facilities. Among these computerized systems, Excel is a spreadsheet software developed and distributed by Microsoft as part of the Microsoft Office suite. It is designed to operate on various platforms, including Microsoft Windows, macOS, Android, and Linux (via compatibility tools like Wine). Excel incorporates a wide range of features such as numerical calculations, data visualization, data analysis (notably through pivot tables), and programming capabilities using macros written in Visual Basic for Applications (VBA), a language shared with other Microsoft Office applications. ²

Using unvalidated Excel files for managing critical tasks, such as stock management in hospitals, presents significant risks:

• Calculation errors: spreadsheets are highly prone to human mistakes, with over 90% of operational spreadsheets containing errors, ranging from formula inaccuracies to copy-paste issues, potentially leading to significant discrepancies in stock levels or financial calculations. ³

• Lack of validation controls: unvalidated Excel files are vulnerable to unauthorized modifications and incorrect entries, which can compromise data reliability and result in operational inefficiencies.

• Version control issues: multiple users working on different versions of the same file can create discrepancies, leading to confusion and mismanagement.

• Security concerns: Excel files are easily shareable and lack inherent security features, increasing the risk of unauthorized access to sensitive hospital stock data, which could jeopardize data confidentiality and regulatory compliance.

• Auditability and regulatory compliance: unvalidated spreadsheets are difficult to audit and may fail to meet regulatory requirements, posing significant challenges in highly regulated environments like healthcare. ⁴

Although the law does not specifically require the validation of Excel files, it is crucial to emphasize the importance of this process. Excel spreadsheets, particularly those that include complex calculations, macros, or other advanced features, may contain errors or inconsistencies that could have serious consequences on the results or decisions based on this data. Validation ensures the accuracy, reliability, and compliance of Excel files with specific requirements, thereby reducing the risk of errors. ⁵

The purpose of validation is to confirm that the computerized system specifications conform to the user needs and intended uses by examination and provision of objective evidence and that the particular requirements can be consistently fulfilled.

Ensuring the accuracy and reliability of Excel spreadsheets is particularly critical in regulated industries, where data integrity directly impacts product quality, compliance, and safety. Two key sectors where validation plays a crucial role are the pharmaceutical industry and manufacturing:

• Pharmaceutical industry: Operating within strict regulatory frameworks, the pharmaceutical sector must ensure compliance not only as a best practice but as a necessity to guarantee patient safety and product quality. ⁶ Inadequate validation of Excel spreadsheets used for data management can lead to errors in quality control reports, regulatory documentation, and batch records, potentially compromising product integrity and regulatory compliance.

• Manufacturing industry: In process quality control, poorly validated Excel spreadsheets can result in mismanagement of critical production parameters, leading to defective products and production inefficiencies. This can have significant financial and operational consequences, especially in industries requiring high precision and compliance with stringent quality standards.

When such technology is involved in high-risk public safety projects or the production and control of life-saving medicines or devices, businesses and regulators must ensure that it is reliable, quality-assured, and validated. ⁷

Methods

Study Framework

This study was conducted in the Pharmacy of the Hospital of Specialties in Rabat during November 2024. The pharmacy plays a critical role in the management and distribution of medications and medical devices within the hospital. It is responsible for maintaining an efficient inventory system that ensures timely and appropriate supply of pharmaceuticals and medical devices across various departments.

The Hospital of Specialties in Rabat serves a diverse range of medical specialties, including Neurology and Neurosurgery, Ophthalmology, Otolaryngology, and Maxillofacial Surgery. These departments require a precise and well-coordinated supply of specific medications and devices, making the role of the pharmacy essential in supporting patient care and treatment outcomes. The pharmacy’s responsibility extends not only to stocking and dispensing but also to ensuring compliance with regulatory standards, managing medication safety, and coordinating with healthcare providers to optimize therapeutic strategies.

The Hospital of Specialties in Rabat has a total capacity of 316 beds and can accommodate a high volume of patients across various specialties. It provides approximately 30 951 outpatient consultations and performs around 5680 surgical interventions (both emergency and scheduled) highlighting its extensive medical and surgical activity. ⁸

The Hospital of Specialties uses an electronic management system. This system is designed to manage all aspects of healthcare facilities or networks through an integrated and comprehensive approach to various processes and workflows. It also enables interactions between different healthcare establishments, serving as a tool to facilitate the daily work of healthcare professionals.

However, the system has several limitations that impact stock management in the pharmacy. It does not provide average monthly consumption figures, fails to account for stockouts when calculating needs, and lacks alerts for expired or soon-to-expire products. These gaps create challenges in effective inventory management.

To address these issues, the pharmacy decided to use a complementary stock management tool—an Excel file—to bridge these gaps. To ensure data reliability, it was decided to validate the Excel file before its implementation.

Study Steps

The methodological framework for this study is based on the structured protocol detailed in Supplemental Appendix 1 designed to validate Excel files (Excel 2021) used for data handling, storage, or generation, ensuring compliance with good practices.

The validation protocol was developed in collaboration with the pharmacy team, consisting of pharmacists and technicians, through brainstorming sessions aimed at gathering critical data. The participants, who are proficient in Excel, provided essential expertise throughout the process.

The pharmacists, responsible for validating the file in accordance with best practices, also oversee its ongoing management. Meanwhile, the technicians play an operational role by entering data into the validated file. This collaborative approach helped establish a solid reference for the validation of the Excel file.

The process (Figure 1) includes the following stages:

Figure 1.

Validation and verification workflow for Excel file integrity.

Excel file identification

Each Excel file undergoes initial identification to confirm its conformity with the specification document, which provides a detailed description of its contents, including the data fields, formulas, and functions used. This includes:

• Verifying the file name and version with the specification document.

• Checking the existence and validity of the file’s specification document.

• Ensuring that the file path matches the defined criteria.

Verification of access levels

The integrity of file protection mechanisms is assessed by:

• Testing the necessity of a password to modify the file.

• Confirming that only designated “Input” cells are editable with the appropriate password.

• Ensuring full access to all cells after unlocking the file.

Formula verification

The protocol includes:

• Displaying and evaluating all formulas in the Excel file for consistency with the specification document.

• Testing the file using validated datasets which consist of actual data from the pharmacy, to confirm that outputs match expected results. The formulas are tested against these real results to ensure they produce accurate and reliable outcomes

Verification of hidden cells or pages

To ensure transparency and compliance:

• Hidden cells and pages are reviewed for alignment with the specification document.

• The presence of additional unauthorized hidden elements is checked and reported.

Verification of cell characteristics

Key characteristics of cells are validated, including:

• Cell colors, formats, and decimal precision.

• Other specific attributes as outlined in the specification document.

Verification of conditional formatting rules

Conditional formatting rules are examined for:

• Presence and consistency with the specification document.

• Absence of unauthorized formatting rules.

Verification of headers and footers

The headers and footers in the Excel file are checked by:

• Printing the document to confirm the inclusion of file name, version, date, and page numbers.

• Verifying that these elements comply with the specifications.

Final conclusion

The protocol concludes with an evaluation of compliance results, documenting whether the Excel file meets all specified criteria.

This structured approach ensures a comprehensive and systematic validation process, contributing to data reliability and adherence to good practices.

Results

The validation of an Excel file used to manage the medication stock yielded the following results:

Protection of Data Integrity

The dashboard was tested to ensure that all critical data cells were locked and unmodifiable without proper authorization, safeguarding against accidental or unauthorized changes.

During testing, all sheets were protected, and sensitive data was inaccessible without a password, confirming compliance with the specified access rights (Figure 2A and B).

Figure 2.

(A) Verification of protection and access levels and (B) verification of protection and access levels.

Formula Compliance

A detailed comparison between the formulas embedded in the Excel file and the specifications outlined in the documentation revealed complete alignment. All formulas matched the predefined requirements, with no missing or additional calculations detected (Figure 3).

Figure 3.

Verification of formula accuracy.

• For instance, the formula used to compute the “Total Stock” was thoroughly verified and found to be consistent with the specified standard.

• The verification of the half-life formula (Dvie) of the drug against the stock and its comparison with the average monthly consumption demonstrates the conformity of validation of the formula.

This ensures that all calculations performed by the dashboard are accurate and adhere to operational expectations. The validation of formula compliance plays a critical role in maintaining the integrity of the system, minimizing the risk of computational errors, and supporting reliable decision-making processes.

There were no discrepancies between the validated data and the expected results, confirming 100% accuracy in all tests conducted.

Verification of Cell Characteristics

The colors, formats, and conditional formatting rules applied to the dashboard were carefully reviewed to ensure full compliance with the documented specifications. This step was critical to guaranteeing that the visual representation of data aligns with the operational needs and enhances user experience.

For example (Figure 4):

Figure 4.

Verification of conditional cell formatting.

• Cells highlighting items at risk of expiration were automatically formatted with a red background, providing an immediate visual cue for urgent action.

• Similarly, cells representing items nearing expiration were formatted in orange to indicate a moderate level of priority.

These conditional formatting rules not only adhere to the predefined standards but also improve the dashboard’s usability by facilitating quick identification of critical stock issues.

Such meticulous attention to cell characteristics ensures that the dashboard is both functional and intuitive, allowing hospital pharmacy staff to manage inventory efficiently and mitigate risks effectively.

Verification of Formulas

The Excel file includes five different types of formulas used to manage a total of 311 products. To ensure the accuracy and reliability of the calculations, a validation process was implemented. Specifically, one-third of the formulas (33.33%) were selected for verification. This sampling approach was chosen to provide a representative assessment of the file’s overall accuracy while maintaining efficiency in the validation process.

Each of the selected formulas was verified three times to ensure consistency and reproducibility of the results. This repetitive checking aimed to identify any discrepancies and confirm the reliability of the calculations. All verifications were carried out by the pharmacists, leveraging their expertise in Excel and their understanding of the inventory management requirements. This rigorous validation process contributed to ensuring the integrity of the data before implementing the Excel file as a complementary stock management tool.

Impact of Excel File Implementation on Stock Monitoring

Since the implementation of the Excel file, pharmacists have significantly improved the monitoring of medications nearing expiration. The current hospital pharmacy information system does not generate automatic alerts for soon-to-expire products, requiring pharmacists to manually search for each product to identify those approaching their expiration date, a tedious and time consuming process. With the Excel file, this task is now automated, resulting in a 50% reduction in the time spent identifying near-expiry medications.

Moreover, another limitation of the current system is the lack of an automatic calculation of the average monthly drug consumption. The Excel file now provides access to this information, offering pharmacists a more precise decision-making tool for supply forecasting. Thanks to this functionality, discrepancies between forecasts and actual consumption have decreased, optimizing stock management and reducing the risk of shortages or overstocking.

Additionally, the implementation of this tool has also benefited the technicians responsible for data entry. When filling out the Excel file, they can now immediately compare the theoretical stock quantities with the physically available quantities, enabling them to detect and correct inventory errors more quickly. This process has led to a 30% improvement in inventory accuracy.

Discussion

Optimizing medication stock management in healthcare facilities is crucial for improving both the quality of care and operational efficiency.^9,10 Digital tools like Excel play an important role in this process by enabling complex data management and analysis. Microsoft Excel is one of the most widely used spreadsheet programs in the world. Its widespread presence reflects a high level of familiarity and comfort among users, providing an opportunity to enhance the ability of diverse global audiences to utilize it for analyzing data related to healthcare challenges, such as disparities in access to resources or healthcare stock deficiencies.

However, using unvalidated Excel files for critical tasks poses significant risks, including human errors, lack of data validation, and version control issues, which can lead to discrepancies in stock levels and mismanagement. Additionally, the lack of robust security measures in Excel files raises concerns about data confidentiality and regulatory compliance. Therefore, the lack of regulatory requirements for Excel file validation has resulted in a shortage of similar studies within healthcare institutions.

To address these issues, the implementation of an Excel file validation protocol was introduced to secure the data and ensure its reliability. This approach aims to minimize errors, enhance data integrity, and provide greater confidence in the accuracy of inventory management, thereby mitigating the risks associated with unvalidated Excel usage.

During the study period, the validation of the Excel-based medication stock management system demonstrates a meticulous approach to ensuring data integrity, computational accuracy, and usability. These results underline the robustness of the dashboard and its alignment with operational and regulatory requirements.

Protection of Data Integrity

Data integrity is the most sensitive concern for today’s healthcare industry. Data integrity describes how to ensure the quality, effectiveness and continuity of data throughout its lifecycle. ¹¹ The successful implementation of access controls, with sensitive data locked behind password protection, is a critical achievement. This measure not only safeguards against accidental modifications but also ensures compliance with best practices in data security. The comprehensive testing of sheet protections reinforces confidence in the system’s ability to uphold data integrity.

Formula Compliance

The verification process confirmed that all embedded formulas adhere strictly to the predefined standards. This level of scrutiny is crucial, particularly in a healthcare setting where computational errors can lead to significant risks. The validation of the half-life formula (Dvie) and its application to stock and consumption data further exemplifies the dashboard’s precision. This alignment supports reliable decision-making and enhances operational efficiency by providing accurate, actionable insights.

Verification of Cell Characteristics

The attention to detail in visual formatting, particularly through conditional formatting rules, reflects a strong emphasis on user-centered design. Highlighting at-risk items in red and items nearing expiration in orange provides an intuitive visual framework that facilitates prompt and prioritized decision-making. This design choice enhances the usability of the dashboard, empowering pharmacy staff to address critical stock issues swiftly and effectively.

Verification of Formulas

The validation of formulas ensured the accuracy of calculations, enabling pharmacy staff to manually analyze stock levels. However, as Excel does not provide automated trend analysis or real-time alerts, the identification of stock risks relies on the user’s ability to interpret data.

For example, the half-life formula was particularly useful in determining the validity period of each product, which is critical for monitoring expiration dates and maintaining product safety.

Additionally, the formulas calculating average monthly consumption and product validity periods allowed the pharmacy team to assess stock coverage more effectively. By understanding both the consumption rate and the remaining shelf life, the team could accurately estimate how long the current stock would last. This enhanced visibility enabled better planning for replenishments and minimized the risk of stockouts.

Moreover, the ability to accurately calculate stock coverage increased vigilance against shortages, as it provided early warnings when product levels were approaching critical thresholds. This proactive approach significantly improved stock management efficiency and ensured a more reliable supply of essential products.

Overall, the validation of the formulas played a crucial role in optimizing inventory control, enhancing data reliability, and supporting better decision-making in stock management.

Impact of Excel File Implementation on Stock Monitoring

The implementation of the Excel file has significantly improved pharmacy workflow by addressing key limitations of the existing system. Automating the identification of near-expiry medications has reduced manual search time, enhancing efficiency and minimizing the risk of expired stock. Additionally, providing average monthly consumption data has optimized supply forecasting, reducing discrepancies and preventing both shortages and overstocking. However, manual data entry remains a constraint, posing a risk of errors for technicians and requiring regular verification. Furthermore, since the information is not updated in real-time, pharmacists must analyze and manually update the data periodically to ensure accuracy.

The adaptability of the pharmacy staff to the new Excel file system has improved over time. Initially, the team faced some challenges, but through targeted training sessions and continuous support, they gradually became more comfortable using the tool leading to smoother workflow integration and a noticeable reduction in errors over time.

The advantages of this approach include increased reliability through the early detection of errors in formulas, which reduces the risks of decisions based on incorrect data. It also improves efficiency by simplifying the management and maintenance of complex files, thereby saving time and minimizing errors. With solid and credible data on the pharmacy’s stock, it becomes easier to monitor near-expiry medications and prevent stockouts, ensuring uninterrupted patient care. Additionally, the traceability and transparency of the process, with a clear historical record of modifications and tests, support accountability, facilitate audits, and ensure compliance with healthcare regulations.

However, full compliance with regulatory standards typically requires a higher degree of automation, real-time data processing, and system integration. While a validated Excel file can improve data reliability and minimize errors, it does not replace an integrated inventory management system that offers automated reporting, batch traceability, and regulatory compliance features. Therefore, this approach should be seen as an interim or complementary solution rather than an optimal one.

This rigorous framework, defined by guidelines such as Good Manufacturing Practices (GMP), ensures that computerized systems meet the necessary standards for managing critical data.^{12 -14} In hospital pharmacy, while this practice is less systematic, it is increasingly seen as essential to secure and optimize the management of medication and medical device workflows. Inspired by the validation principles applied in the industrial sector, its integration aims to enhance the reliability of digital tools and meet the growing demands for quality and safety in healthcare.

The limitations of this approach include the time and resources required for development and validation, especially for complex Excel files, which demand skilled personnel with advanced knowledge of Excel tools. ¹⁵

While Excel can serve as a practical tool for inventory management in hospital settings, specialized stock management software and automated systems offer more advanced functionalities. These systems typically include real-time inventory tracking, automated alerts for stock levels, expiration monitoring, and seamless integration with procurement and financial departments. Unlike Excel, which relies on manual updates and user-dependent analyses, specialized software provides data-driven insights and predictive analytics, reducing the risks associated with human intervention. However, in resource-limited settings where access to such systems is constrained, validated Excel spreadsheets remain a viable alternative for improving stock management efficiency.

Even though using a validated Excel file is a better alternative to paper-based inventory management, it still presents several risks. Understanding these risks is essential to mitigate their impact and improve overall inventory management efficiency.

One major limitation of using Excel for inventory management is its reliance on manual data updates. Unlike integrated inventory management systems, Excel does not provide real-time tracking of stock levels, which can lead to outdated or inaccurate data. This necessitates frequent manual interventions, increasing the risk of human errors such as data entry mistakes, failure to update stock levels on time, and discrepancies in reported inventory. These issues could lead to stockouts or overstocking, ultimately affecting patient care. Implementing automated synchronization mechanisms or transitioning to a real-time inventory management system would mitigate these risks and enhance efficiency.

Excel, as a standalone tool, lacks integration with other hospital departments such as finance, pharmacy procurement, and patient billing systems. This disconnect can create gaps in traceability, particularly in tracking batches from supplier to patient, which is crucial in case of recalls. Additionally, segregation of duties is not well enforced in Excel, as access control mechanisms are limited compared to dedicated inventory management systems. Without automated traceability features, batch tracking must be done manually, increasing the likelihood of errors. A more robust inventory system with seamless integration across departments would significantly improve traceability, reduce risks, and enhance compliance with regulatory requirements.

Unlike specialized inventory management systems, Excel does not provide automated stock control features such as safety stock levels, reorder points, and ABC stock classification. These features are essential for optimizing inventory turnover and preventing shortages or excessive stock accumulation. The absence of automated cost management and stock valuation further limits financial oversight. Although our validated Excel tool helps reduce errors and improve reliability, a fully automated inventory system would be more effective in ensuring accurate and timely stock management.

While validation ensures data accuracy within the Excel file, it does not provide preventive security measures against unauthorized modifications. Excel’s access controls mainly serve to detect unauthorized access after it has occurred rather than preventing it. In contrast, dedicated inventory management systems include built-in security features such as user authentication, audit trails, and role-based access control. These features enhance data security and reduce the risk of intentional or accidental data tampering.

Managing inventory using both an Excel file and an electronic management system (EMS) introduces risks related to data discrepancies. If stock levels recorded in Excel differ from those in the EMS, it becomes necessary to define a “single source of truth” to ensure inventory accuracy. Discrepancies can arise due to delays in manual updates, different data entry practices, or technical issues such as file corruption. To mitigate this risk, regular reconciliation between the two systems should be implemented, and ideally, automated data synchronization should be considered.

Another limitation of using Excel for inventory management is the lack of real-time reporting capabilities. In the absence of automated dashboards and trend analysis tools, users must manually extract and analyze data to identify risks and opportunities. This manual process increases the likelihood of errors and delays in decision-making. Implementing an integrated system with real-time reporting would facilitate proactive inventory management, enabling pharmacy staff to anticipate stock shortages and optimize purchasing decisions.

Another important limitation of using Excel for inventory management is its scalability. While it can be an efficient tool for managing small to medium-sized inventories, it becomes increasingly impractical for large hospital warehouses with thousands of stock-keeping units (SKUs). Excel lacks automated real-time tracking, seamless integration with other systems, and efficient batch traceability, making it difficult to scale beyond a certain threshold. Furthermore, relying on printed documents for verification not only increases paper waste but also adds an extra step that can slow down operations and introduce additional risks related to outdated or misplaced records. To address these challenges, transitioning to an integrated hospital inventory management system would ensure better scalability, enhanced traceability, and reduced reliance on manual processes.

The integration of computerized systems represents a major opportunity to enhance information sharing among various hospital departments, such as the pharmacy, care units, and administration, while addressing interoperability challenges. Moreover, validation directly contributes to risk management by reducing human errors in stock management and medication distribution. To sustain this performance, regular audits and proactive regulatory monitoring are essential, particularly in light of evolving local and international requirements.

Beyond its technical implementation, the validation of stock management systems in hospitals carries strategic implications for healthcare institutions. A validated system ensures more accurate medication tracking, reducing risks related to stockouts and expired drugs, which directly impacts patient safety. From a regulatory perspective, validation aligns with hospital accreditation requirements and international standards, reinforcing good governance in pharmaceutical management. Moreover, integrating validation into hospital operations fosters a culture of continuous quality improvement, where inventory control is not only a logistical necessity but also a critical component of risk management and financial sustainability. As hospital digitization progresses, future strategies should aim at transitioning from validated spreadsheets to fully automated and integrated systems, minimizing human intervention and maximizing data-driven decision-making.

Conclusion

This study highlights the importance of validating Excel-based systems in hospital settings, particularly for critical applications like medication stock management. By implementing a robust validation protocol, the study ensures data integrity, computational accuracy, and adherence to regulatory standards, supporting reliable decision-making and efficient inventory control.

While the validation process requires substantial time, expertise, and resources, its benefits far outweigh the challenges. It serves as a proactive measure to safeguard against potential errors and inefficiencies, offering a model for integrating best practices from the pharmaceutical industry into healthcare operations.

Ultimately, this study demonstrates that investing in the validation of digital tools like Excel is a critical step toward achieving excellence in healthcare delivery to mitigate risks associated with errors and improve operational efficiency, ensuring that technological advancements directly contribute to patient safety and operational excellence.

While the validation of an Excel-based system enhances data reliability and minimizes errors, it remains a complementary tool rather than a replacement for a fully integrated hospital ERP system. A well-designed ERP with automated workflows, role-based access control, and minimal manual intervention would significantly reduce risks associated with human errors, ensuring better data integrity and compliance. However, in settings where such systems are not yet available, validated Excel spreadsheets can serve as an interim solution to improve inventory management while reducing operational risks.