This post may contain affiliate links. Please read my disclosure for more info:

The 7 most dangerous Excel features

Most dangerous Excel features

Most dangerous Excel features

“With great power comes great responsibility”, or so we are told in the Spider-man comic book and movies.  Microsoft, in Excel, has given us some very powerful features; features which save time, features which make Excel easier to use, features to produce amazing documents.  However, these features, in the wrong hands (or even in the right hands) can result in disaster.  Have you ever carried out an action which has resulted in your worksheet being useless? Or, have you ever managed to over-type data or formulas without even knowing it?  Of course you have, I’m sure we all have at some point.  Each time it happens we say “well, I don’t know how that happened”.

Today, I want to share with you some thoughts on seven (small but powerful) Excel features which can cause us big problems, if we won’t use them responsibly.

 

Grouping worksheets

Why is it powerful?
By selecting multiple worksheets, we can edit all of them at the same time, it’s a great time saver.

Selecting multiple worksheets is also used for printing more than one worksheet at a time, so it’s a feature which even newbie Excel users are comfortable with.

Why is it dangerous?
We can easily forget to ungroup the sheets.  Every change we make is replicated onto all the selected worksheets, even if the changes are not intended for all those worksheets.  This can result in data being overwritten, or formulas changed.  If we don’t notice for a while, or if we save the file then those changes cannot be undone.  Well done us, we’ve just wrecked our spreadsheet!

 

Password protecting the file

To be clear, I’m not talking about a password to protect changes on a worksheet.  I’m talking about the password required to even open the file.

Why is it powerful?
Protecting sensitive or privileged data is the right thing to do to prevent unauthorized access.  We use passwords for Facebook, Amazon and our bank accounts, so we know from experience that passwords are a good way of protecting information.

Why is it dangerous?
It is easy to forget a password, or maybe somebody has left an organization without leaving the passwords behind.  This is no problem for Facebook, Amazon or bank accounts as there is that little “forgotten password” button, or maybe an emergency phone number.  But not for Excel files, a forgotten password is a forgotten password.  There is no “forgotten password” button or emergency phone number.

There are companies offering services to crack Excel passwords.  However, this can take days, or weeks, or even longer depending on the password complexity.  So, even if the file can be recovered, it will still break even the smoothest process and can cause significant delays to completing work deadlines.

 

Macros & VBA

Why is it powerful?
With Macros & VBA it is possible to automate repetitive tasks and create add-ins to save time and achieve more.  Macros are, in my opinion, the most powerful feature of Excel.

Why is it dangerous?
Macros can delete and over-write data, formulas or even entire workbooks without even showing a warning message.  Even knowledgeable individuals can create a Macro which can cause havoc.

With Macros, there is no undo feature, so even small changes cannot be undone.

A few weeks, a work colleague of mine managed to unintentionally delete 30+ workbooks because the Macro did not work how they expected.  Thankfully, the issue was identified early and the IT department had a backup to restore from.

 

Manual calculation & PivotTable refresh

Why is it powerful?
Whether due to large data sets, complex formulas or bad formulas, some workbooks are just slow to calculate.  Why waste time waiting for Excel to re-calculate each time a change is made?  A quicker option is to change to manual calculation mode and only recalculate when necessary.

Why is it dangerous?
Both manual calculation mode and PivotTable refresh rely upon the user to carry out an action, and guess what . . . sometimes we forget, or sometimes a user doesn’t even know they have to do anything.

If the first Excel workbook opened in a session is set to manual calculation, then all the workbooks are set to manual calculation.  The “manual calculation virus” can inadvertently be applied to other workbooks, even if they are normally used in automatic calculation mode.

Suddenly, workbooks all over the organization are not being recalculated at the right time.  The result of this is error-filled reports are issued to management, upon which they might make an important decision!  Bad! So, very Bad!

 

Defaults for optional function arguments

What?  I hear you ask.  As an example, the last argument in the VLOOKUP function can be excluded, it is optional.  But, if it is excluded it will default to a TRUE.  This is what I’m referring to, the default for optional arguments.

Why is it powerful?
If certain arguments are not required, it is easier for the user to understand the function and the function is faster to write.

Why is it dangerous?
Do most users really know what the impacts of using the default options are?  No, they don’t.  Even experienced users can accidentally exclude arguments. When setting up the spreadsheet the problem may not even be visible, it may calculate everything correctly.  But somewhere down the line the data will be updated and it could produce an incorrect result without us even knowing.

 

Hiding columns, rows or worksheets

Why is it powerful?
We don’t want the end user to see the ugly workings and helper columns we’ve used to create our masterpiece, therefore hiding cells or worksheets is common practice.

Why is it dangerous?
Workbooks evolve over time, and hidden information can be forgotten about.  This creates (a) spreadsheet junk from unnecessary cells which are now hidden and (b) slower calculation times.

Hidden rows and columns can cause formula errors too, especially when the formulas are dragged without knowing that some cells are hidden.

 

Linking workbooks to cells in other workbooks

Why is it powerful?
Having Excel files linked to each other can save huge amounts of time because the workbooks update each time the other workbook is updated.  It’s almost like magic when it happens.

Why is it dangerous?
If the cell’s position of the linked workbook is changed whilst the linking workbook is closed the change will not be updated.  When new rows/columns are added the linking workbook has no idea the references have changed.  Now the workbook is linked to the wrong cells.  The magic updating process has now become a nightmare!  This is where named ranges can be beneficial, however I tend to find that those who link between workbooks don’t use them.

I have seen situations where workbooks can have links to workbooks, which contain links to workbooks, which contains links to workbooks (you get the idea).  The more links to other workbooks the harder the web of links is to follow.

 

What can we do?

I’ve been working with Excel for over 15 years, and even now I can still fall foul of these features from time to time.  Knowing about them certainly helps to reduce the number of potential issues.  “With great power comes great responsibility”.

Here are a few things we can do to:

  • Thoroughly test workbooks and macros, include error checking and validation.
  • Check logic for all different types of data
  • Try to predict what other users might do and make sure your workbook can handle it
  • Use network access right along with memorable passwords to keep sensitive data safe
  • Take regular backups as you work
  • Used named ranges, rather than absolute cell references
  • Self-review, ask yourself, “does the end result match with my expectation?”

What do you think?  What features do you think are the most dangerous?

3 thoughts on “The 7 most dangerous Excel features

  1. David says:

    Hi there,
    Thanks for this posting. I personally have been using Excel since the ’90s but NEVER before have I had it crash/not respond as I am experiencing now with Office 365/as often.

    Office 97/2003 NEVER EVER crashed like this – I have a brand new Lenovo i3 6100 CPU 3.7 Ghz 8G RAM, unbelievable. Crashing mostly on filter/insert on fairly large sheets, but never experienced before on much BIGGER, MORE COMPLICATED worksheets with multiple links to multiple external spreadsheets on ’97/2003 with refresher Macro’s on open etc.. (On which I would suggest much easier to monitor/manage/maintain all links – not so seems on Office 365.)
    Any comments ? Something surely must be done by the consumer ?Much obliged, thanks. David

    • Excel Off The Grid says:

      Hi David,

      I have not experienced the issues you are having with Office 365.

      Perhaps the Microsoft Answers Community may be a better place to receive the support you require.

Leave a Reply

Your email address will not be published. Required fields are marked *