subject: Excel Validation Made Easy [print this page] Nowadays, the number of functions and features to validate spreadsheets is a fantastic work; and not only do mathematicians, statisticians and scientists of all kinds use them for their calculative work; but accountants, economists and business people are major users of them too. In our spreadsheet, then we can simply type things: letters, words numbers and symbols: such as headings, instructions, directions, explanations.
By qualifying a spreadsheet for the things it was best designed to do- store values and then carry out calculations based on those values and relationships, we can save time and effort with lesser risk and mistakes. Both computers and humans are not perfect but since the first is programmed, only minimal mistakes happen by chance and they are easier to trace due to consistency of the program.
With the aid of modern technology breakthrough such as the use of computerized systems (CVS Validation) and equipment validation comes the easier task of validating spreadsheets. Data in a spreadsheet are interrelated and therefore in case of changes, all related values are updated automatically. The same scenario can be applied to mistakes, the correction can be done also in one specific entry and voila- the adjustments would be automatic. The MES systems export data to a spreadsheet making the task to validate simpler and a lot easier. In qualifying a spreadsheet, we need to weigh the advantages that the process would bring to our system.
The truth is, by exporting something to a spreadsheet a whole new world of opportunities will appear. Data is just data, plain and simple. It's pretty useless and almost everything has a load of it. Information on the other hand is: USEFUL DATA; it is informative and valuable. Can you see where this is going? Yes, get your data into a spreadsheet like MS Excel, validate the process, do some 'magic' and some validation and hey presto. The QA department won't accept this I hear you calling - present this: What level of confidence does their system have for manual entries and checks (that probably aren't audited)? Do it anyway and demonstrate your efforts. So far, we haven't failed with our QA departments.
The main question is, how do we validate the spreadsheets? Let us start with a simple spreadsheet. The data validation function is an excellent tool when entering repetitive data into a spreadsheet. This is a good way to prevent incorrect data entry. Using a validated system will allow the user to specify exactly what sort of data is allowed in a cell. This will also allow the addition of an error alert message for every incorrect data entered. It can also lock numbers and formulas so they are not accidentally erased during data entry. As the level of complexity increases, the GxP data constitution should be taken into consideration if the system electronically generates Gxp regulatory records. Let us take the lab equipment data export to a spreadsheet as an example. In this scenario, it would be expected for the master GxP data to exist in within LIMS, MES or a Production Batch Record, therefore, the validation would permit the printout to be used to support GxP activities while the original data is retained in another, already-validated system.
I have more than basic formulae in my spreadsheet (Yes, I heard you when I was explaining the above). In this case let's assume a VBA macro is used in conjunction with some input data on a spreadsheet. This brings us into the realm of computer systems validation, but don't be scared! This means that the SDLC must be followed (to an extent). A risk based approach is now required (GAMP5 considers this to be at level 5). Remember that duplication of effort doesn't yield many business benefits (if any), nor does quality generally benefit either. Most organisations facilitate the scaling of the suite of validation documents in order to suit the job-in-hand. Would it really be beneficial to have a URS, FDS, Test Plan, etc PLUS all of the associated reports for a small(ish) validation effort when all of the same people are required to sign-off anyway?
Identify the Gxp Data- it is not important if it is manual or electronic but would it be the spreadsheet alone or some other system?
To establish the level of complexity, do a risk-based approach. The outcomes would range from full-validation, source code review, and produce new documents in slight alterations here and there of the existing set up. Documents. These can be tailored and concise depending on the size of the project to produce better and more efficient results.
