When is a spreadsheet not a spreadsheet?

[GLAbusiness]

It occurs to me that a spreadsheet can take one of two forms:

1. It is a true spreadsheet allowing a user to manipulate formulae and data

2, It is a business application (sometimes very small) written using spreadsheet tools (Excel and VBA).. I this case to user does not get to change formulae and functions. The user just enters data and gets results. This could be a simple data capture form up to a very complex business process.

I suspect that this distinction may resolve some of the tension between poster like @MyAccountantOnline and @Daybooks. I don't know but I think @Daybooks usage probably tends more to type 2 than type 1

Type 2 spreadsheets should be "hardened" to protect their integrity

 

[numbersrule]

This is interesting GL Abusiness. Type 2 as you have defined it would certainly significantly reduce concerns about spreadsheets being prone to error compared with online bookkeeping software, particualrly if the Type 2 has built in cross-checking and double entry facilitation.

 

[GLAbusiness]

The key message is that if you use a type 1 spreadsheet as though it was a type 2 it is error prone The hardening process is not trivial, which is why most business users don't do it.

 

[gpietersz]

I agree somewhat, but type 1 spreadsheets can still get pretty complicated and be error prone and hard to debug. In some ways they are more problematic as a mistyped formula can be very hard to spot. A lot are constructed by copying and pasting rows an columns (e.g. a column for a month or year in a financial model) which means you duplicate stuff which is very bad practice and something you can easily avoid in VBA by using a loop. it can create very hard to spot mistakes (incorrect formula in one cell, rest of row/column is correct).

If you are doing something complex in a spreadsheet, or using it as a datastore (e.g. a database substitute) it becomes very error prone indeed.

 

[GLAbusiness]

Here are the key things needed to convert a type 1 spreadsheet to a type 2

Tie down the interface — users see only what they need to see, and can change only what they need to change

Restrict navigation to controlled processes — no wandering off to “let's see what this does”

Good documentation — both technical and user

Good version history

 

[fisicx]

Question if your sprawling multi sheet, macro driven, sprawling spreadsheet should be a database application.

Just because you can do something with excel or libre calc doesn’t mean you should.

There often comes a point when nobody really understand how a gargantuan spreadsheet even works but they keep adding their bit. Locking down, documenting and verifying is just not possible.

8 of the Biggest Excel Mistakes of All Time

Excel is great for data analysis however it can be easy to make grave errors, leading to bad business decisions. Here are the biggest excel mistakes ever

blog.hurree.co

 

[gpietersz]

@fisicx I very much agree with you and I have seen some monstrosities.

The problem is that people use spreadsheets because they are the only thing they know. It has a very low barrier to entry and you can learn as you go. You will not learn good practice or how to write reliable software, but you will learn to get things done.

In many work environments its the only programmable tool you are allowed so you have no choice at all if you want to get things done.

 

[fisicx]

Airbus is trying to move away from MS products but they have a small problem:

"Finance, for example, still relies on Excel because Google Sheets can't handle the necessary file sizes, as some spreadsheets involve 20 million cells. "Some of the limitations was just the number of cells that you could have in one single file. We'll definitely start to remove some of the work," Jestin told The Register."

Microsoft habit proves hard to kick for Airbus

Exclusive: Google Workspace switch drags on amid Excel dependencies, compliance requirements, and compatibility issues

www.theregister.com

 

[WaveJumper]

Question if your sprawling multi sheet, macro driven, sprawling spreadsheet should be a database application.

Just because you can do something with excel or libre calc doesn’t mean you should.

There often comes a point when nobody really understand how a gargantuan spreadsheet even works but they keep adding their bit. Locking down, documenting and verifying is just not possible.

8 of the Biggest Excel Mistakes of All Time

Excel is great for data analysis however it can be easy to make grave errors, leading to bad business decisions. Here are the biggest excel mistakes ever

blog.hurree.co

Yep I have gone into situations where people on the face of it have "fantastic" spread sheets, you will never need anything comes the cry, they have created the best thing since sliced bread. What I have found though is they have convinced themselves that its the be all and end all to their financial monitoring and forget to question the results when on the face of it the numbers never made sense.

Spreads sheets are great but I feel you need to have a "feel for the numbers* I have found some horrible mistakes which have completely thrown financial planning and reconciliation out the window. And to say the directors were none to pleased as it effected their valuations figures ........ heads did role

 

[GLAbusiness]

Let me be clear. I would never use Excel for accounting - FreeAgent is so much better. A type 2 spreadsheet is for situations which need a bespoke solution.

To harden an Excel workbook into a type 2 I do the following.

Disable the Excel UI - menu, ribbon, formula bar, row and column headers. Hide the tabs. Navigation is now achieved by buttons so that only the appropriate routes are available to the user.

Disable function keys

Password Protect all worksheets. Only the cells which require user input are unlocked.

Changes to structure and logic require a "Developer" password

Maintain a technical audit log to provide instant visibility (in developer mode) of the process which led to any error.

One of these models has been running in productions for 10 years (with regular updates as requirements change. The client investigated using it as the basis for a bespoke add-on to an existing application system. They found that it would be too expensive and too inflexible.

 

[fisicx]

Earlier this year I had a project to create a calculator for an HR company.

They did everything on a spreadsheet with dozens of filters and some quite extraordinarily complicated formula. They had conditional fields and a bewildering array of colours to denote various paths through the data.

Everything ended up with some outputs they could then use to write some report about manning or whatever.

I untangled the mess and reworked everything into a simple php form. You selected the criteria, entered a couple of numbers and up popped the answers. One of these was the cost of an employee - how much averaged over the year they spend and included training, uniform, benefits etc.

They had been using the spreadsheet for years and entered the numbers into a big graph thing.

We had quite a protracted discussion about the results from my new application. Turns out their spreadsheet was wrong. They had a cost of £15/hour for an employee but the correct figure was around £21/hour. The size of the business meant they were underestimating something like £200,000/year.

All because they were endlessly tweaking a spreadsheet. The new app was lighter, simpler, saved to a database table and could produce the graphs and a whole range of reports.

Of course a type 2 spreadsheet can be locked down sometimes an application is a better solution.

 

[GLAbusiness]

Of course a type 2 spreadsheet can be locked down sometimes an application is a better solution.

I agree - horses for courses

 

[MyAccountantOnline]

It occurs to me that a spreadsheet can take one of two forms:

1. It is a true spreadsheet allowing a user to manipulate formulae and data

2, It is a business application (sometimes very small) written using spreadsheet tools (Excel and VBA).. I this case to user does not get to change formulae and functions. The user just enters data and gets results. This could be a simple data capture form up to a very complex business process.

I suspect that this distinction may resolve some of the tension between poster like @MyAccountantOnline and @Daybooks. I don't know but I think @Daybooks usage probably tends more to type 2 than type 1

Type 2 spreadsheets should be "hardened" to protect their integrity

I think that's a very fair distinction.

I like Excel a lot, I use it daily and it's a fantastic tool when used appropriately.

What I see is clients trying to use a basic spreadsheet as a bookkeeping system. Used in this way they are incredibly prone to errors: accidental overwrites, broken formulas, missing entries, duplicated transactions, and so on.

Where a spreadsheet has effectively become a business application (your Type 2), I completely agree that it should be hardened and controlled to protect its integrity.

I truly have no issues with others who dont share my view

 

[gpietersz]

Airbus is trying to move away from MS products but they have a small problem:

"Finance, for example, still relies on Excel because Google Sheets can't handle the necessary file sizes, as some spreadsheets involve 20 million cells. "Some of the limitations was just the number of cells that you could have in one single file. We'll definitely start to remove some of the work," Jestin told The Register."

Why do they not use Libreoffice or similar. Moving to Google Sheets increases reliance on the US which is not a great idea IMO either (at least governments should be discouraging this).

 

[fisicx]

Why do they not use Libreoffice or similar. Moving to Google Sheets increases reliance on the US which is not a great idea IMO either (at least governments should be discouraging this).

Apparently because of the size of the dataset. There is an article on the register about it.

 

[gpietersz]

Apparently because of the size of the dataset. There is an article on the register about it.

Sorry, to clarify my point, Libreoffice can deal with datasets that size, its only Google Sheets that cannot.

The Register article also says this:

There's also a regulatory hurdle that has "nothing to do with the functionality of Google." Military-classified documents can't be stored in the cloud, forcing those teams to stay on on-premises Microsoft software for the time being.

So a European company has to use MS for security reasons?

It looks like Google Sheets was a terrible choice for them. LibreOffice or Only Office/Euro-Office would be a better one.