Should spreadsheets be controlled? If so, how?

Mark Meer

Trusted Information Resource
#1
I'm certain the answer is "it depends"... ;)

...but I'm curious for examples of what circumstances people control electronic spreadsheets (e.g. MS Excel), and how these controls are exercised.

The main concern is that while spreadsheets are great tools for data analysis, they are (in most cases) being continually updated. This, combined with their electronic format, makes it difficult to subject spreadsheets to the usual controls of review, approval and version-control.

Is there some sort of acceptable "relaxed" controls that can be used for electronic spreadsheets?

For example:

Individual supplier records and purchase order records are maintained as paper forms.
These records are reviews/approved, dated, signed and filed.

But in addition, a spreadsheet is maintained to track the status of all purchases and link the suppliers to orders (allows to quickly determine purchase history).
Is this spreadsheet subject to change-control (given that it may be being updated on a daily basis)?
 
Elsmar Forum Sponsor

Steve Prevette

Deming Disciple
Staff member
Super Moderator
#2
Re: Should spreadsheets be controlled? If so, how?

I am working for a project, where we do indeed need to "control" the excel files I make for them. Each time the file is changed, it is saved as a new file, with a date stamp in the file name, such as:

Excel_File_06182014.xlsx

Or if multiple files, we put the file in a folder which includes the date stamp in the folder name.
 

Big Jim

Super Moderator
#3
Re: Should spreadsheets be controlled? If so, how?

This is one of those examples where people will confuse documents and records.

If there is a spreadsheet tool that is often used, as an example, for developing a quote, the spreadsheet template would be controlled as a document. It could be controlled by assigning a form number and listing it on the list of documents or list of forms, depending on how your company does such things. The form number with revision level could be in a header or footer.

When you use the template to create a quote, you don't alter the template (document). Instead, you are creating a record. records don't usually fall into a requirement to revise the form when you make record entries. You get to determine where the record is kept (perhaps in a customer file or in a project file). Unless there is a customer or regulatory requirement otherwise, you get to determine how long you need to keep the record (quote).

So it should be easy to keep track of spreadsheet templates you use in this manner. The concept of needing to update a document (form in this case) just because you have recorded something on it is just not required and is overkill.
 

Mark Meer

Trusted Information Resource
#4
Re: Should spreadsheets be controlled? If so, how?

Steve:
Is there some means of recording approval? In addition to a timestamp, is there any traceability to the author or the changes?

Big Jim:
The complication is the continual update of the spreadsheet, and the maintenance of data history.

For example, if I maintain a spreadsheet that lists the statuses of all purchase orders. There may be a "Status" column that might read, for example, "Backordered", "Received", "Cancelled"...

In a given day, I might change the status of several line items to "Received" for all the shipments received that day. In terms of standard record control, this would be considered bad practice as I'm essentially erasing all traces (overwriting) the previous statuses.

In this case, there is no document template, and (Steve's system notwithstanding), no data integrity/history maintained. It's just a continually updated log.
 

insect warfare

QA=Question Authority
Trusted Information Resource
#6
Re: Should spreadsheets be controlled? If so, how?

I usually see spreadsheets (like Excel) controlled as records, since they can have the potential to be continuously updated. While they can be uploaded into your documented management system as a "controlled" document, this is not always value-added. I have spreadsheets that I have been updating for years, because a need was determined to gather long-term data for trend analysis, and these spreadsheets were not under "document control" as you would think. Everything technically was "controlled", just at different levels, without the introduction of overkill.

Typical methods of spreadsheet control I've used before are:
1) Saving my work every time it is updated
2) Maintaining the electronic file on my local machine
3) Performing weekly backups to a specified location
4) Purging or deleting the old file in the event a better version of it is designed

Depending on where you work, more stringent controls may be necessary (not every industry is judged by the same standards). Nevertheless, your document control procedure can state exactly which levels of control your company deems are appropriate. Hopefully this helps spark your light bulb....

Brian :rolleyes:
 

outdoorsNW

Quite Involved in Discussions
#7
Re: Should spreadsheets be controlled? If so, how?

It sound like Mark needs a database and not a spreadsheet.

While a database is likely more expensive to set up, a database can save prior entries, time stamp changes, log who made the changes, etc. A database can restrict who can do what, reducing errors in the information.

When you need to analyze trends, you run a report and analyze the data in Excel, Minitab, etc. Most off the shelf databases have built in reports and instant calculations (purchase order total, for instance) for common functions.

Otherwise you are creating a new version several times per day, and how does someone know which version to look up?

How much more expensive initially depends on if you can buy it off the shelf, how much customization is needed, how much your IT people can do, and how much training is needed. Many specialty computer programs (for accounting, inventory management, etc) are special purpose databases.

In the long term, a database will likely save time/money.

Mark mentioned purchasing functions. I believe Quickbooks (look at the more expensive versions) and several other brands (whose names I can't remember) have software that may meet your needs for not much money.

You may want to talk to sellers of more expensive programs in case a more expensive package automating more functions is more cost effective in the long run.

Take your time evaulating software. Sales people often gloss over the areas where their software does not really meet your needs. The one downside of the lower cost programs is that customization is normally more limited. If you do a websearch, I reccomend looking at (not necessarily clicking on) at least the top 50 entries, and trying several sets of search terms.

Unless your needs are very specialized, developing your own database is probably more trouble than buying a package. You need to think about support, security, etc.

Tom
 

Mark Meer

Trusted Information Resource
#8
I guess the concern is more generally regarding electronic logs (ongoing, perpetual, continually updated), for which spreadsheets are particularly convenient.

If these are considered records then, at least according to FDA regulations, there needs to be controls to ensure that "record changes shall not obscure previously recorded information." (see FDA 21 CFR Part 11.10(e)).

I guess the only way to do this is to "save as" a new file everytime the log is updated (e.g. see post #2 by Steve Prevette), and maintain a gigantic archive of files.
...good thing file-sizes are typically small and space cheap! :tg:

Edit: Or setup a database system like Tom suggests above...
 
Last edited:

Raffy

Quite Involved in Discussions
#9
Hi
In my previous company I work with, what we do is that we document the format and how it will be saved in our hard disk.
Example:
Excel123_0614001.xls
Where:
Excel123 is the file name
06 is the current month
14 is the current year
001 is the series number
Hope this helps.
Best regards,
Raffy :cool:
 
Thread starter Similar threads Forum Replies Date
Sidney Vianna Interesting Discussion Should ISO 9004 be changed from a guidance standard to a requirements standard? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 5
A Should I take an online course for a career in Occupational Health and Safety? Career and Occupation Discussions 2
J Should a Class 1 medical device with an option to measure body weight be considered Class 1m? EU Medical Device Regulations 0
K Should APQP/PPAP has its own section in a QM? Quality Management System (QMS) Manuals 1
S What should i choose for "testing procedure" characteristics? (N95) General Information Resources 0
P Should eIFU link per ISO 15223-1:2016 be added to labels out of scope of Reg 207/2012? EU Medical Device Regulations 1
S Which Sampling Plan(s) Should I Use? Inspection, Prints (Drawings), Testing, Sampling and Related Topics 7
A Document release vs its related training. Which should come first? ISO 13485:2016 - Medical Device Quality Management Systems 18
S Which department should prepare the control plan? could you show me a standard regarding to this matter. FMEA and Control Plans 17
J Help settle a disagreement: Should external providers of preventive maintenance be on your ASL? AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 5
N Master Samples - What should we be keeping? IATF 16949 - Automotive Quality Systems Standard 9
G Supplier delivered recent PPAP, should he deliver yearly layout inspection? IATF 16949 - Automotive Quality Systems Standard 4
John Broomfield Vote - Should ISO9004 Become a Requirements Standard? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 11
A Capability Study - in the beginning of your career what should you have known about the tool Quality Tools, Improvement and Analysis 11
J Should Loading and Unloading be Included in Cycle Times? Lean in Manufacturing and Service Industries 14
E Manufacturers should develop a testing device for covid19 Service Industry Specific Topics 0
T 510(k) submission - Which name should I use in the submission? Other US Medical Device Regulations 3
N ISO 19011:2018 - 5.4.2 "...audit program should engage in appropriate continual development..." Training - Internal, External, Online and Distance Learning 4
G Should I perform Gage R&R only at the beginning of a new project? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 6
DuncanGibbons Should the requirements FAA/EASA Part 21 be addressed within the QMS and AS9100D quality manual? AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 5
M Should 510(k) Predicates be Actively Listed Devices? Other US Medical Device Regulations 12
B Why the Greek god Hephaestus should have done a design FMEA (DFMEA) on his giant robot APQP and PPAP 1
J On PFMEA for danger labels - Label always should be assigned severity 10 ? FMEA and Control Plans 3
H Who should be listed as the manufacturer/distributor on the box? 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 15
M MDR, RED and LVD - Should our device comply with them? EU Medical Device Regulations 3
BeaBea How Many Processes should be created for each Department? Process Maps, Process Mapping and Turtle Diagrams 5
M Should volume of sales be factored into risk probability assessments? ISO 14971 - Medical Device Risk Management 33
MrTetris Should potential bugs be considered in software risk analysis? ISO 14971 - Medical Device Risk Management 5
S Should safety checks be included in the Control Plan? IATF 16949 - Automotive Quality Systems Standard 5
M Which incubation condition should be selected to recover both bacteria and fungus effectively Miscellaneous Environmental Standards and EMS Related Discussions 3
D Is there a specific location for PPE such as safety glass holders and glove dispensers should be mounted Occupational Health & Safety Management Standards 10
Robert Stanley Which Registrar Should I Choose for ISO 9001:2015 registration? Registrars and Notified Bodies 10
M Who should receive the bills from suppliers and vendors, account payable or procurement? Consultants and Consulting 4
V IATF 16949 8.4.1 Control of externally provided processes, products and services - Should the CB be on our Approved Supplier List? IATF 16949 - Automotive Quality Systems Standard 10
A We are ISO 13485:2016 should we be audited to ISO 14971 ISO 13485:2016 - Medical Device Quality Management Systems 16
E Received a Major finding during IATF Surveillance audit for loss of BIQS Level 3 (more than 6 SPPS in 6 months)...how should we address SYSTEMIC CA? IATF 16949 - Automotive Quality Systems Standard 11
J Organization merger. Should we keep two separate ISO 13485 certificates? ISO 13485:2016 - Medical Device Quality Management Systems 6
S Companies that maintain your machine should be in ASL? AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 2
S Use of "Shall" versus "Should" in Procedures ISO 13485:2016 - Medical Device Quality Management Systems 26
D Class II medical device - When should a complaint be closed? Customer Complaints 6
Sidney Vianna IATF 16949 News Presentations from the latest IATF Stakeholder Event - Expectation that IATF 16949 certification should equate with product quality. Misguided? IATF 16949 - Automotive Quality Systems Standard 7
L Clause 0.4 of ISO 9001 and EHS - Where should I stop the inclusion of EHS in my QMS ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 8
Ed Panek Part 11 Self Certify Memo - What else should it cover? Qualification and Validation (including 21 CFR Part 11) 5
H Should I mention machine/Equipment password In SOP? Qualification and Validation (including 21 CFR Part 11) 4
D How long should we keep the spare parts available for our medical device, after we have stopped the production? ISO 13485:2016 - Medical Device Quality Management Systems 0
H Statistical Techniques Procedure - What should be included Document Control Systems, Procedures, Forms and Templates 4
Q How should I analyze measurement correlation between me and customer? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 12
Sidney Vianna Interesting Discussion ISO 9001:2024 - What should be changed in the next Edition of ISO 9001? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 113
N Should it even be on the hazard analysis (software)? FMEA and Control Plans 2
V Which batches should or could be considered for design validation and design verification? 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 0

Similar threads

Top Bottom