To scream stop messing up my spreadsheets?

[ClaudiaWankleman]

I'm not even that good at Excel. I can't do VBA, I often have to Google things. Nonetheless, on my team I'm the only person of capable of more than a SUM function and so I have to help maintain 3 moderately complex spreadsheets that serve different purposes in the team.

At least every two weeks someone will completely mess one up. I don't even know how - most recently they've undone half of some moderately complex data validation formatting that I was asked to add by the spreadsheet owner. I assume someone came along, thought 'this doesn't look the same as it did before' and instead of using a modicum of common sense to fill out the new options, deleted a large number of cells so that they could input whatever shite they wanted to. I could find out who using the version history, I'm sure, but I don't want to.

I can normally rescue the spreadsheet by restoring a previous version, but it's a disruptive thing that takes me away from whatever task I'm otherwise doing, because I normally suddenly receive a barrage of messages of 'it's not working' and 'can someone assist' in a group chat. I think sometimes it makes me look bad.

I might be able to solve some of the issues by locking/ protecting some cells, but that does come with its own issues. Not least that I'd need to look through each workbook to work out exactly what permissions general users need and what they don't - at least a few hours + testing to make sure there's nothing else people need.

In a previous role we worked 100% from Excel collaborating across lots of worksheets. I'd never before had anyone treat documents with such disregard. I almost definitely won't scream, but AIBU to feel like I need to?

Protect your spreadsheets from accidental or deliberate editing by putting a password on it and set ot on "Read only" access.

Surely your coworkers know how to filter and hide rows and columns in order to input data without deleting anything else on it?

If not, then maybe there should be some training on using spreadsheets in a collaborative way.

Thanks. I'm aware of read only access but, as explained, everyone needs editing rights to the spreadsheets in order to use them. They're not reference sheets, they're for the collection of data. I could go through each workbook and work out exactly what specific editing rights are needed, but that is a waste of my time and probably wouldn't fix the problem.

What has happened recently is that the data which needs to be collected has changed slightly, and my updates are being eroded.

I worked in a school for 10 years.

It's very easy to mess excel up if you are not used to it.

If you have people who aren't capable of using them without making more work for you then realistically you are going to need to protect them.

Humans are stupid.

I feel your pain. Whenever I have a shared spreadsheet I always “save as filenamedate.xls” a copy with my last update on my own drive as well as in the shared space, so that if someone bollocks it up I can recover a clean version.

Yeah I own a spreadsheet at work which people use to track attendance for a project. It’s got really basic formula to add up entries in each column. I always stress that you should just delete any errors with the backspace rather than delete the entire cell/row, but at least once a week someone doesn’t listen and deletes entire chunks of it, rendering the formula useless. And we all use it all the time. I don’t know how to make it clearer.

I think it would be worth your time to protect it, if this happens regularly. The other thing you could do is give some guidelines to your colleagues - so never delete or move rows/cells, only enter data. You could tell them that it will automatically format data according to pre set rules so don't change any formatting etc etc. Maybe try that first before going through the process of protecting.

You can change the protection so that they can only add information / remove information in certain cells, but not change formatting / other cells. I use it in work a lot! It's really useful.

I use excel sheets to run various applications through VBA.

The power of excel is you can prototype an interface really quickly and implement it without doing much work.

The great thing about excel sheets is you can change stuff really quickly. The bad thing is you can change stuff really quickly.

That is why most people don't code end user applications in excel, but hard code them in a way that is difficult to change.

You probably can work harder to stop people messing with the sheets. For example you could "store" the sheets in a load of hidden sheets. Then when excel opens you can copy the hidden sheets to the main sheets, ensuring they are fresh each time. Or you can lock off cells. Or password protect the VBA code.

But bear in mind excel is actually designed to make it easy for people to change so you are fighting an uphill struggle.

I would just plonk a version on the server and say "use this". And if someone comes and moans to me and says it isn't working the first question should be, did you start with the fresh sheet off the server.

You can protect to stop people from deleting rows. Appreciate it's a faff though to work out which permissions they need

I have to use protection on the spreadsheets I manage or people do all kinds of weird stuff with them. I lock certain areas of the sheet so people can't edit those but they can still filter it and add new rows.

You can protect individual cells on a sheet and only allow editing of those cells that people really need to access. It's fairly easy to do. You can lock things down to a surprising degree. I can dig out a link if of interest...

Agree re how time consuming protecting different parts is (although might be worth it here!), as a quicker fix could you colour code cells/rows/columns they should adjust? E.g., if all changes that are "real" belong in column C, highlight that column and put a big note saying "only edit these values"?

support.microsoft.com/en-gb/office/lock-cells-to-protect-them-cb7835f6-9c37-4161-bb53-d1c410acaf21

Not sure if the link will work, fingers crossed. The important thing to remember is that the 'locked' status of a cell only kicks in once the worksheet is protected.

I agree that protecting certain cells can definitely be time consuming and prone to issues.

I mostly write code, but occasionally provide spreadsheets. i have taken to designing whatever I do so that the guts of the calculations and the user editing sections are completely separate. For a spreadsheet, that would mean that users can enter information on a particular tab, but all the equations and calculations happen elsewhere, often on a hidden tab. Then the results are on a third. I tend to use a lot of vlookups and matches since my equations have to adapt to entries. Also range naming.

when I write code, I give users a single text file that they can edit and only that file. There may be 1000s or 10000s of lines of code in the background, but unless they are experienced, they don’t need to be there.

Can you set up a seperate tab, with the same column headings on it, where they input the data, along with initials of who input it, and you can copy it over onto the master once a week after it has been checked?

You can protect your master tab, and not theirs, and get them to correct it before it comes across?

It was a while ago, but when I was a project manager there would be lot of shared files. It was easy in Word to track changes and the rule with Excel was that the PM 'owned' the file and others had to make a copy of a read-only master before making any changes.

Use version control software, such as Git, so you can easily track and control changes. What you're doing is tipping into software development now; use some software development procedures to keep it safe.

Protect the cells with the formulae they are messing up.

As much as I love MS Teams for collaboration on docs, I hate MS Teams for collaboration on docs! 🤣. 1 person who doesn't know what they're doing, with autosave on and they can very easily bugger it up. I have resorted to screaming....

This has happened to me many times, v frustrating. It's usually when someone doesn't realise a cell has a formula in it and they start entering data manually instead of letting it do it's thing.

To prevent it again I highlighted in the brightest yellow all the cells that they could add figures in with an 'add data in yellow boxes ONLY' message in big letters at top of sheet.

Could that help?

Have a data input tab so they aren’t working in the main sheet

Do you have a common manager?

In addition to the technical suggestions above I would be suggesting to your manager that you send a team email explaining the impact of people messing with the spreadsheets and setting ground rules for what they can and can’t do. This could go in your name and be endorsed by your manager or drafted by you and sent by him or her. It can be drafted in a pleasant way.
You might also see if it could be raised verbally at a team meeting and included in new joiner training.

After all, it’s in everyone’s interest to make sure that data integrity is maintained, surely? Nobody is doing this out of malice, it’s just ignorance and lack of training.

This really.

As a developer you have to assume the general principle end users are like toddlers.

If you put a glass vase on the edge of a table and a toddler pulls the tablecloth of, whose fault is it ? The toddler for pulling the tablecloth or you for putting the vase there in the first place ?

The good news is, the more you stop the end users being able to act like idiots, the more power and knowledge in the organisation you accumulate (evil laugh).

If you lock the thing down hard enough then noone will be able to change anything without asking you to build the functionality in.

That's a good time to ask for a pay rise.

As an easy fix can you have your formulas on the main sheet, then create a 2nd sheet which the staff work on - just do = A1A2 etc - the exact same cell on sheet 1. It will grab all your data from sheet 1, they can just write over the information if they do wish but they core data will remain on sheet 1

You can hide that sheet

Protect them or save a master version of each which only you have control over which you can use to restore anything broken

1. Use version control to track changes, both who made them, what was changed, and when
2. Have a staff meeting about the importance of not screwing it up