The New Rules of Work: The Modern Playbook for Navigating Your Career, by Alexandra Cavoulacos and‎ Kathryn Minshew (2017)

I stumbled upon this book after reading a positive review and was very impressed. It is a well-organized and focused presentation of some of the content of themuse.com, an online career resource.

The authors understand the great challenge of the modern career: with more options and tools than ever, many people find themselves without a playbook for this complicated, non-linear career game.

This book is exactly what the subtitle states – a playbook. This means you can select from it what you need without having to read the whole thing. You can also use it as a reference from which to dive deliberately into the content at themuse.com. This may be helpful if you are like me and sometimes lose focus in glossy, bottomless websites brimming with multimedia and links.

First, read “What Color is your Parachute?”

If you have not read the classic career book “What Color is Your Parachute,” read that first. This classic career book was originally published in 1970 but the extremely dedicated author, Richard Bolles, has revised it every year since. Make sure you are reading the most recent edition! Alternatively, read one of the many spinoffs (http://www.jobhuntersbible.com/books) that may fit you more precisely.

“What Color” is wide-ranging in scope and covers pretty much every practical thing you need to do in the job hunt or career pivot. This includes self-assessments, a guide to interviewing, and many other resources. Read this and then return to “The New Rules of Work.”

Read only the sections that are most relevant to you (or, read only the gray emphasis boxes)

Just like “What Color is Your Parachute,” some sections will be more relevant than others. Read only these. For an even more abridged experience, read only the gray emphasis boxes. These boxes contain the most concise, bulleted content. In fact, some of them are simply brief articles from themuse.com. But they are the curated ones.

You’ll find highly actionable and practical advice

The authors drive home the point that you must develop your own personal brand to present to potential employers. This brand will stay with you much longer than your average employment stint. I appreciate this well. In fact, this blog is my way of developing a brand and sharing knowledge with people in my exact situation and with potential career connections. However, this blog was originally inspired by “What Color is Your Parachute” and from my 11 years writing a personal blog.

The résumé writing section stood out to me as particularly practical and concise. This section also points you to resources (such as templates) at the website that you may find useful. It may motivate you to replace your Microsoft Office standard templated résumé (guilty!). One tip: get rid of “References available upon request” line.

Tap into the resources at themuse.com

This site is rich with new career content each day from a network of freelancers and from paid staff. There are portals to paid courses and coaching. And the site partners with employers who recruit through the site.

One problem is separating between sponsored content and professional advice. Since much of this this site is free, you are not necessarily the customer – the advertisers and employers are. The site is rife with embedded content. If you find a way to use this site in a focused way, or if you have a success story from one of their paid modules, I would love to hear about it!

One criticism: the emphasis on LinkedIn

Authors such as Cavoulacos and‎ Minshew seem to believe that having a detailed, up-to-date LinkedIn profile, and being an active user of this site, is a necessity in being considered for an interview. As members of the recruitment/talent sphere, they are no doubt heavy users of this LinkedIn. They seem to think that since it’s free, there is no downside.

But there are many reasons not to join LinkedIn, including the following:

• It will mine your email addresses and spam your contacts.
• It was hit with at least one major data breach (in 2012). Your information there may not be secure and is definitely not private.
• Garbage content, meaningless endorsements, and fake profiles abound.
• Most important for me: it is a potentially bottomless timesuck! With a career blog (such as the one I am writing right now), I know how much effort I put in and I can see the results. With LinkedIn, you might feel you need to actively cruise the site and hit up your network for many hours a month. You many never know how much is enough, and you may never know what benefit you are getting from it.

If you don’t want to join LinkedIn, don’t! It is not the necessity that so many authors claim it is. And keep in mind that you can access a lot of articles and other content on LinkedIn without creating an account.

Some great advice that I highlighted:

1. Chapter 4 covers the absolutely essential task of building your personal brand. The authors walk you through the five steps for building a successful brand: determine your brand attributes, draft your branding statement, refine your profiles, create your personal website, and activate your brand.
2. The résumé editing checklist in Chapter 7. “Does this sell you as the perfect candidate for the types of roles you’re seeking? Does the top third of your résumé serve as a hook to get the hiring manager to read more? Could anything benefit from examples? Does the page look visually appealing?” Lastly, submit the résumé to the employer as a PDF, not a Word document!
3. Chapter 7 addresses how to rework not-so-relevant experience into something tailored to the job.
4. Chapter 8 provides tried-and-true interviewing advice, along with some good information on video/Skype interviews and a worksheet and checklist. The authors provide advice on behavioral interview questions and an expanded section on video/Skype interviewing. I find that the tactile activities are the ones that help me to reflect and to retain information the best.
5. Chapter 9 goes into detail about salary negotiation, which is a mysterious and anxiety-inducing topic for many people. The authors detail tactics as well as other considerations besides salary such as scheduling flexibility, job title, continuing education, vacation time, lifestyle perks, and moving expenses.
6. The last four chapters are a guide to the modern workplace. The content mirrors what you will find on the website: “The Trick to Communicating Hard Messages,” “21 Unwritten (New) Rules of Running a Meeting,” “Really Struggling to Cross Off Those To-Dos? Use Your Feelings (Yep, Seriously).” Many of these are just blog-like articles and listicles from the website. But I happen to love this stuff and I added the blog to my Feedly news feed. And each topic, such as inbox management, skill development, and collaboration, is a world unto itself. You know where to go for more: the million Youtube videos, blogs and books that flesh out these concepts in exhaustive detail.

What to read next

If you have a suggestion for my next career or Quality book, let me know! Or stay tuned for my next review.

Reviewing a scientific report is a great privilege. You are among the first to see new knowledge without having to do the sometimes-tedious investigative work itself. And if you’re like me, you might feel the joy of learning something new.

When reviewing, I encourage you to do two things. Firstly, commit to applying a method, just as the investigators applied a method when conducting their study. Secondly, consider the review, your comments, and the investigator’s response as a conversation between the two of you. This conversation has a subject. It may go off in an unexpected direction. And it may expand to include other people.

In this article I will describe how to approach the important role of reviewer.

Who you are

My primary assumptions about you are twofold:

• You do this for work (you are not a scholar doing peer review for free).
• You follow your organization’s standard operating procedures (SOPs) and national or international regulatory guidance.

I also assume that you are the only reviewer. If you are the Quality reviewer, but the report has already undergone a thorough technical review described in your SOPs, then your review will be greatly reduced in scope (and probably much easier!).

Lastly I assume you are properly trained. You must not only be familiar with the procedures of your reviewer or Quality unit, but also with the procedures the investigators followed and with the science underlying their methods. If some calculations were done in Excel, you should be familiar with Excel. If some statistics were done in Minitab, you should be familiar with Minitab.

Gather your materials

You should have four things at a minimum:

• The draft report
• The protocol (plus applicable SOPs)
• The raw data
• Your checklists and procedures and your form or software for documenting findings

Organize

The following setup is optimal to me: the draft report is on the computer screen in front of me. The raw data and the protocol are on my left. My materials (such as a checklist, inspections from during the study, and any other Quality forms) are on the right. These Quality materials may be software-based, in which case it should be open onscreen so you can document findings in real-time, while reviewing.

Check for completeness

Before beginning, make sure everything is there so that you can plan for a thorough review with minimal handoffs (handoffs are where mistakes often happen).

Read the intro/summary/abstract

Even if this section is at the end of the report, it is a good thing to check before getting deep in the weeds.

Read the body of the report and make sure all the sections specified in the protocol/SOPs are present

Title Page/Cover Page

This should include a unique identifier, the name of the study, and the sample name and lot number (if applicable). Your organization’s logo or letterhead may be required as well. The investigators, the dates of Quality inspections, the date of finalization, and the Quality reviewer (if applicable) may appear here or on a signature page. The client (if applicable) and testing facility are listed here as well.

Table of Contents

Depending on the length of the report, a table of contents may be helpful. Ideally this will automatically update in Word. If changes are anticipated, consider verifying the table of contents at finalization to avoid duplicating your effort.

Abstract

Peer-reviewed reports will require an abstract. The requirements are very specific to the journal. Your organization probably does not require one.

Introduction/Purpose

The introduction should basically be a high-level summary of what is in the protocol. If the writer briefly stated the scientific background, the goal of the current study, and the methods in simple language while still being descriptive, he or she has done a great job!

Some studies may state the overall conclusion in this section as well. If so, check it against the conclusion section below. If this section is labeled Purpose instead of Introduction it should correspond to the Purpose section of the protocol.

Materials and Methods

This section should mirror the protocol because the protocol is basically a promise or agreement about what will be done. The materials, such as reagents, instruments, and suppliers, may be in their own section or in tables interspersed with the text. Check each against the raw data as you go.

Pull up applicable SOPs as you review. Although these documents often read like work instructions, they may provide greater detail than the protocol about what was done. Try to avoid “filling in the blanks” though. The report should stand on its own.

Read for flow and for accuracy to what is described on the lab notebooks or worksheets. Check that all specifics, such as temperatures, weights, and incubation durations match what was recorded. If there are any deviations, these may need to be addressed in the report. The need for a retest or an investigation may first be discovered here.

Raw data and report disposition

This section is important! The retention period of the report and raw data must follow regulations of the FDA, EPA, or other agencies. The report should describe where the raw data will be held so that clients or regulators can compare the report to it during an audit or investigation.

In general, the Quality review documentation you are completing right now will be stored with this package and for the same retention period.

Validity criteria

I like to see validity as a standalone section because it is so important. This and the following (evaluation/acceptance) deserve your full attention.

Validity is the soundness of the entire study. In an animal test for irritation, a valid determination may require that animals in a positive control group developed irritation as expected. In a chromatography study, a valid identification of a compound may require that the signal-to-noise ratio be below a certain threshold. In an ELISA test, a valid assay may require that the calibration curve is linear and not just a jumble of dots.

In routine assays that seldom have validity problems, this section can be easy to overlook. The text for this section may be canned. In your review, take a moment to look at validity. There may be one validity criterion or there may be several.

Without a valid assay, the investigator cannot make an evaluation (below). Or, he or she cannot make an evaluation without a compelling justification.

Evaluation/acceptance criteria

Evaluation/acceptance criteria are very important as well. They might be something specific such as “to pass, the white blood cell count must be within 4,500-10,000 white blood cells per cubic millimeter.”

In a larger study, the evaluation may be much broader. The investigator may look at the totality of health observations, bloodwork, feed consumption, histopathology, and necropsy observations. In this case, a lot of judgement is involved on the part of the investigator. As a reviewer comparing the raw data to the report, you are verifying that the evaluation broadly matches what you see in the raw data.

In both cases, the evaluation should be based on what was described in the protocol. Because of its deciding nature, to alter it or introduce other evaluation criteria requires a protocol amendment or a deviation.

Results

Reviewing results may take up the bulk of your review. Depending on your SOPs you may check 100% of transcription or just do a spot check. The report may present every data point collected, or just summary tables. You should be checking that all of it is traceable to the correct place in the raw data, that any gaps are labeled so in the report tables, and that the text matches what is in the tables and what is in the raw data.

If there are voluminous data tables to check, you may choose to review these last so that you can read the report in one sitting while it is fresh in your mind.

Discussion

The discussion section is where the results are interpreted. It should connect to the introduction or purpose of the study and then move beyond. You should check it against everything that is applicable: the protocol, the results you have just reviewed, the evaluation and validity criteria, and the relevant citations. This section is free to be lengthy but it should not restate too much from elsewhere in the report. Instead, it should lean heavily toward context, synthesis and interpretation.

Depending on your training and role in the organization, you may be responsible for assessing the scientific rationale of the report. But a typical Quality reviewer will not be responsible for this. Instead, questions of scientific judgement are left to the investigator and his or her peers, who will have already reviewed the report or at least the associated raw data.

Conclusion

The conclusion is best kept brief! If it is lengthy, suggest in your comments that some of it be moved to the Discussion section.

Both short-and-simple and long-and-complex studies are free to have a one-sentence conclusion. As a reviewer, you should double-check that the conclusion matches the interpretation arrived at in the Discussion section and does not go beyond it.

Although the conclusion is one sentence, a word on scope and a qualifier are always scientifically justified. Consider the following one-sentence conclusion:

“Under the conditions of this protocol, the levels of endotoxin in the sample were found to be below the limit of detection.”

The investigator referenced the scope of the study (the current protocol and its limitations), provided a scientifically cautious qualifier (there could be endotoxin, but not enough to detect), and clearly stated the conclusion, all in one sentence.

References

For a non-peer-reviewed study, this is not as important. It is the responsibility of the investigators to base their study on sound science. However, you should check that the resources cited exist, are cited correctly, and are accessible. If your organization is issuing reports based on papers from obscure journals from the distant past that can’t be found online or in your reference library, you might have a problem.

Check transcription

A 100% transcription check is an unambiguous requirement. It is clear for the reviewer. It is clear to the investigator that their transcription will be verified. After establishing a 100% transcription check requirement, you will start getting higher-quality raw data by the time of review. This means less back-and-forth and less reprocessing of data (data transcribed incorrectly into Excel or statistical software causes incorrect results).

Spot-check calculations

Your organization may require spot-checks or that a certain percentage of calculations be checked, or that 100% be checked. Generally, you will not need to check calculations done within statistical software. If possible, you should have a digital copy of any Excel spreadsheets used so that you can display the functions and check the calculations the easy way.

See the investigator if needed

Consulting the investigator before your review is finished is a matter of judgement. Here’s what I mean: if you can’t find what you are looking for or can’t understand a calculation, you will definitely want to ask the investigator to clarify. However, if you identify a major deviation or some other issue, you want to ensure that this is documented and winds up in your quality system. You want each major finding to be documented and to have a documented response.

The same goes for minor errors. You don’t want to hand the report and raw data back and forth, for multiple review cycles, while they address issues that come up during your review. Instead, there should be one efficient but thorough review of the complete package, followed by a write-up. Subsequent reviews should be brief, involving only signing and sending.

You might also consult a third party such as a statistician or manager.

Write it up

Organize your findings cohesively and fill out any checklists you may have. If applicable, categorize your findings by major and minor. If possible, each of your findings should describe what was found, reference the standard it is being compared to (the protocol, an SOP, etc.), and the consequences of not meeting the standard.

If the report is not acceptable or not acceptable without additional experimental work, make this clear. If a major deviation was identified, you should let the investigator know in person so they can resolve it quickly. Any required investigations should be closed and addressed in the report when the report comes back to you for finalization.

At finalization, see that all findings are addressed

When the report comes back to you for finalization, you will ideally only be reviewing minor changes and the addition of finalization dates and cover pages, logos, etc. You will want to ensure that all investigations are closed and that all your findings are addressed. Each finding should be closed out individually.

Do a final look-over

Finally, check the boring stuff! Make sure the pagination is correct and that all the pages are there. Be ready for third and fourth reviews as minor printing or electronic signature issues are sorted out.

Sign off and then thank the investigator for their hard work.

Some further reading

An excellent and much-shared article geared toward peer-reviewed papers. There is good advice in the comments section as well:

https://violentmetaphors.com/2013/12/13/how-to-become-good-at-peer-review-a-guide-for-young-scientists/

 

A well-organized breakdown of the elements of a scientific report from the University of Waikato (New Zealand):

http://www.waikato.ac.nz/library/study/guides/write-scientific-reports

 

A detailed guide to writing a discussion section from the University of Southern California:

http://libguides.usc.edu/writingguide/discussion

 

The FDA regularly issues warning letters regarding data integrity. Now, the agency is encouraging companies involved in GMP to get on top of these issues with regular audit trail review. You may have read some FDA or EU publications relating to data integrity and audit trails and wondered how they may apply to your organization. This article will demystify audit trail review and provide you an outline for your organization’s data integrity initiative.

What is an audit trail? What are some other data integrity terms?

Audit trail means “a secure, computer-generated, time-stamped electronic record that allows for reconstruction of the course of events relating to the creation, modification, or deletion of an electronic record. An audit trail is a chronology of the ‘who, what, when, and why’ of a record.”

The usefulness and power of the audit trail can vary quite a bit by system. In an older hemocytometer for example, you may find that there is no audit trail – the printout containing the time, date, result, and sample name is the extent of the data produced by the machine.

In the case of a newer analytical instrument such as a mass spectrometer, you will find that the audit trail is exhaustive and essentially unalterable. It will be a dynamic, all-inclusive record that displays information about every user and instrument action, in a human-readable format. It may be searchable by date, user, type of action, etc. It may even be in its own view-only, data manager module for ease of use. It may be exportable as an Excel file or printable as a PDF.

Another instrument may be intermediate in user-friendliness: you can display a page or two of printable, static information surrounding a specific file or sample, but cannot easily search or scroll.

Audit trails are part of a broader concept called metadata. “Metadata is the contextual information required to understand data.” For example, a set of data may be a list of compounds and their concentrations in a sample. The metadata surrounding this data would include the username, time and date, the data path (where the files are automatically saved), any error messages that came up, and any changes to the sample name. As outlined above, the extent of the metadata collected by the system can vary quite a bit.

An even broader concept is data integrity. This term refers to “the completeness, consistency, and accuracy of data. Complete, consistent, and accurate data should be attributable, legible, contemporaneously recorded, original or a true copy, and accurate (ALCOA).”

A concrete analogy

To make data integrity a little more concrete, consider how you treat crossouts on a paper record. Most crossouts are justified (mistakes happen). But when someone does make a crossout to update a raw data entry, your organization’s SOPs require them to address it. They do this by drawing a single line through the original entry (leaving it still legible). They then need to provide an explanation (such as “incorrect entry”) along with the identity of the person who did the correction, and the date of the correction.

In this way the date and recorder of the original entry, the date and recorder of the correction, and the reason for the correction are all documented. In addition the original entry remains legible for comparison to the corrected entry.

Data integrity refers to these same controls but in a digital format. And reviewing audit trails helps the Quality unit to review these deletions and changes in the same way they review them on paper.

Everyone in your organization plays a part in ensuring data integrity. Reviewing audit trails is part of that effort.

Where do I start?

Start by looking at the list of all computerized systems in your organization. If you have no such list, make one! A spreadsheet is adequate.

Classify each system by what regulated activity it is used for (GMP, GLP, etc.) and by its criticality. Document who is the system administrator (i.e. who controls access and sets up and deactivates users). Identify the subject matter expert who can answer your questions (see below). Sort the systems by criticality. Audit trail review is resource-intensive, so you will want to start with the critical ones.

Do a brief, informal review with a regular user of the system. See if you can easily access the audit trail from the main screen of the program or if you need to log into a separate module such as a data manager, security module, or report mode.

Audit trail functionality may have been documented in the validation. Check the validation paperwork for hints on how to access it. Check the manufacturer’s materials as well.

Consider the feasibility and resource-intensiveness of audit trail review for each system. Does reviewing it mean booting someone off the terminal? Is there a limited number of user licenses? All these considerations will influence the requirements you define in your data integrity SOP.

Take notes and write down any questions to address during a more detailed review to follow. Update the spreadsheet you started.

An assessment of each system is needed

This involves a more detailed assessment. A member of the Quality unit will sit down for about an hour with one subject matter expert in the software/instrument. In advance, you will want to come up with a template Word document with items to address. The idea is to get accurate information, including screenshots, to help with training and crafting the SOP. Topics may include the following:

What type of audit trail is it?

• Is it a dynamic event log, capturing every action automatically? Or does it just display the parameters of the individual run?
• Can the reviewer select a date range?
• Can he or she search by user, batch or project number?
• Are there any error messages for the subject matter expert to look into? Are there error messages that qualify as red flags for the reviewer?
• Does the audit trail capture when a user edits a sample name or lot number? When critical parameters are changed? When background scans are re-run? When the results from standards are rejected?
• What do manipulations and deletions look like? Is there a way to compare versions of a file?
• In analytical chemistry, is there an audit trail for acquisition of data, but no audit trail for processing of data? In chromatography, is there a way to compare integrations of peaks? What does excessive integration look like, and is this something the Quality unit should review?
• Compare records from a recent project or run to what you see onscreen in the audit trail. Go step by step and write down some typical audit trail events.

Don’t duplicate the effort of the original validation. But do look at the system through a data integrity mindset.

You may find that routine audit trail review opens new conversations about a system. Although an audit trail cannot be “corrected,” review of the audit trail may point to deficiencies elsewhere in the project’s documentation.

For example, if one person signed the batch record, lab notebook or worksheet, but the audit trail shows a different user (such as a trainer) performing that procedure, then both signatures should be in the documentation.

Another example is retesting or reprocessing: if the audit trail clearly shows a retest, but this was not documented, then this should be addressed in the documentation before Quality unit approval of the record.

For legacy systems, make sure any deficiencies are documented and that alternative data integrity measures are in place

You may look at an older instrument or software and find it is not 21CFR11 compliant, or not compliant in the way you thought it was. You can add a brief risk assessment to the validation paperwork with a memo referencing the new audit trail review procedure that is being put in place.

Look to the manufacturer’s compliance software

If the software is older, ask the vendor about updates. They may have released a data security module that you can add to the software. If there is no audit trail functionality, the system may still be Part 11 compliant. Don’t worry – audit trail functionality is not an absolute Part 11 requirement.

Get access for the QA reviewer for each system

Once this assessment is complete, get the Quality reviewer access to each system.

Some software will have a reviewer (read-only) mode. This is ideal because they will not be able to accidentally delete a file or alter a method. If the Quality reviewer is a standard user, that’s fine too!

Efficiency side note: to avoid duplication of effort, the periodic review can be reduced somewhat in scope.

Although audit trails will now be reviewed routinely, the periodic review is still important because this is where failed login attempts, periodic vendor maintenance, and changes to the overall method are captured. Keep in mind the risk-based approach.

Write the SOP!

Reference information technology SOPs, validation SOPs, ethics and training SOPs, and Quality review SOPs. Make sure it has caveats that address the range of software at your company. This procedure should involve organization-wide training. Having an executive or director champion it would be very valuable. Everyone should know what is expected of them with respect to data integrity.

Revisit this procedure in a year

To follow up, continue reviewing FDA warning letters for the agency’s thinking on data integrity matters. Distribute the pertinent letters to your team. Connect the audit trail reviewers with those involved with equipment/software validations so that audit trails are set up and understood proactively. Even better, ask for the reviewers’ input on the next set of compliance software that the vendor is trying to sell you.

A year after effectivity, revisit this procedure and see how it’s going:

• Is it too impractical? Are reports being delayed because the reviewer can’t get into the system while others are logged on?
• Is system access an issue? Does only one person in the Quality unit have the needed access?
• If you have technical data reviewers and a Quality reviewer, are they duplicating each other’s review? It can be hard to separate a technical review from a procedural review. Perhaps only one group should review.
• Look at the overall value of the project. If you find that reviewing audit trails in the way recommended in the FDA’s draft guidance is not a value-added step, let the FDA know! Now is the time to comment before the draft guidance becomes a requirement.

Lastly, take the long and broad view. Consider audit trail review to be one of many tools in your organization’s data integrity efforts. Keep in mind that other organizations are grappling with these issues as well, and there are no experts out there who have all the answers. You will have to treat data integrity as an ongoing commitment, with every data integrity procedure open to change, optimization and improvement.

If you have had successes, failures or questions in your audit trail efforts, I’d love to hear about them!

Explore further

Data Integrity and Compliance With CGMP Guidance for Industry (draft guidance). The FDA published this in April 2016 as draft guidance. But as we know, you need to get ahead of the guidance!

(https://www.fda.gov/downloads/drugs/guidances/ucm495891.pdf)

 

This FDA slideshow, released a year later, provides some helpful elaboration on the guidance:

(https://www.fda.gov/downloads/AboutFDA/CentersOffices/OfficeofMedicalProductsandTobacco/CDER/UCM561491.pdf)

 

This article provides an concise summary of the challenges of starting an audit trail review process, and the importance of a risk-based approach based on an assessment of each system. The link opens a PDF:

(https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=5&ved=0ahUKEwj63Lvzu5nXAhUk4YMKHch_BmgQFgg-MAQ&url=http%3A%2F%2Fwww.ivtnetwork.com%2Fprintpdf%2Farticle%2Faudit-trails-reviews-data-integrity&usg=AOvVaw197_k-WCTeF3Ju5zL45gPY)

Over the years, the spreadsheets circulating in a testing or manufacturing company can become a real zoo. This presents a regulatory and client-relevant problem. It is important that you take a risk-based approach and validate your spreadsheets according to their use.

A spreadsheet used for dosing, reporting data or in dilutions may be a “legacy document”: a digital file developed years earlier by someone who may not even be employed with you anymore. It may have been developed in a previous version of Excel.  Its workings may include macros that the current users only vaguely understand. Or it may have functions and hidden rows that have not been examined in years.

Although configurable off-the-shelf spreadsheet software such as Excel has many good security and data integrity features, you must validate spreadsheets according to a defined procedure. Regulators and clients increasingly expect this. This article will show you how to get started.

Define the three responsibilities involved: developer, verifier, and quality assurance

Developer

The developer is the person who creates the spreadsheet or who initiates the validation of an existing spreadsheet. This person will be familiar with the use of the spreadsheet and with Excel (or whatever spreadsheet software you use).

The developer is responsible for understanding the requirements in the SOP that defines the development, use and control of spreadsheets. He or she will complete a form, ideally a checklist, that refers to each of these requirements. He or she will also document the spreadsheet’s inputs, processing, and outputs for verification (see below).

This is the lengthiest and most involved step of the three. Making it easier is your job. You can help by making sure the checklist is user-friendly and flows nicely with the SOP!

Verifier

Like the developer, the verifier will also be someone who is familiar with the context the spreadsheet will be used in and with Excel. For example, this person may a technician who uses the spreadsheet every day, a peer of the developer, or an Excel expert within the company.

To save space on the form and to show the verification clearly, you can simply include an additional column where the verifier adds a check mark indicating each requirement was met.

Another term for verifier is simply technical reviewer.

QA

When this verification step is done, QA can serve as the final sign-off. This may include spot-checks, format review, a wiping of personal identifying information and comment history, and administering a final document password that will only be distributed to authorized users.

If problems come up during the spot check, the QA reviewer will take a closer look and possibly reject the document, kicking it back to the developer, who must address it and get the spreadsheet verified again. The QA reviewer should not sign until all issues have been resolved.

When these three people have done their work, you have a validated spreadsheet. And the overall process is called the spreadsheet validation. (You may use different terms than what I have described, of course.)

Break it down into three steps: inputs, processing, and outputs

I recommend this approach because it reminds you to visualize the data flow in three parts. On the left is raw data such as a column of animal weights in grams.

On the right is the outputs, such as the required dose of a test article extract in milliliters.

In the middle is the processing. In our example, the individual animal’s weight is multiplied by a specific factor (according to the protocol or SOP) and then presented in the correct unit. The spreadsheet may also provide the group’s average weight, highlight any animals whose weight is outside a defined range, and present the highest and lowest weights for inclusion in the final report.

What goes on during the processing is like a black box. The spreadsheet validation explains, verifies and documents this processing so that independent reviewers can understand the spreadsheet’s purpose and be assured of the quality of the data that comes out. It brings the black box out into the light!

Define any additional requirements

The above are very general requirements that apply to all spreadsheets. Depending on use, some more specific requirements might include:

• All cells except those needed to enter data or sample information are locked down. This one is very important!
• Data validation is enabled. In other words, nonsensical entries are not permitted or will result in an error message.
• Conditional formatting is appropriate and user-friendly. It may help in the study interpretation or trigger validity issues per protocol, so getting correct conditional formatting is important. Note that conditional formatting rules are not displayed in Excel’s Show Formulas function.
• Drop-down lists are in the desired order
• Rounding, significant figures, and display of digits is appropriate. Only the final value is rounded (there are no averaging of averages, for example).
• Input cells display the value exactly as entered. This helps reviewers who must check transcription.
• There are places to enter sample information. There is a place to enter the user’s name and date (or, better, a function that does this automatically).
• The spreadsheet has been checked for robustness. Expected and extreme values were tried. You attempted to “break” it.
• Personal identifying information has been wiped using the “inspect document” function in Office 2016.
• The print preview was inspected. Saving and copying and pasting data works fine. The font matches the final report table where the results will be copied and pasted, if applicable.
• Pagination, logos and the form identifier are included and correct.
• Printouts are included in the validation paperwork with all the formulas, row numbers, and column numbers displayed.
• This is optional: an additional sheet has been included that helps users understand and use the spreadsheet. (e.g. What raw data do I enter and when? When do I print it? What secure network folder do I save it in?) We like to think every user will read and understand the SOP. But in reality this sheet may be what they rely on!

Create a form where the three responsible groups document their work.

There should be a space for free text where the developer can describe the inputs, processing, and outputs. There should be a checklist for any additional requirements, and space to address the ones that are not applicable. There should be a clear separation of the developing from the checking.

The form should be comprehensive in case the developer and verifier do not actually read the spreadsheet validation SOP (we all know this happens). Finally, in exchange for asking busy people to do this validation, you should make sure the form is easy to use!

Some further reading

Surprisingly, the FDA provides very little guidance on spreadsheet validation:

(https://www.fda.gov/ScienceResearch/FieldScience/ucm174286.htm)

 

However, the FDA has issued warning letters that cite spreadsheet deficiencies, including spreadsheets whose functions are not locked down:

(https://www.fda.gov/iceci/enforcementactions/warningletters/2013/ucm369409.htm)

 

Two more good resources:

(http://www.cbinet.com/sites/default/files/files/Workshop%20B_Soto_pres.pdf)

(https://learnaboutgmp.com/validation-of-ms-excel-spreadsheets/)