Excel Tip: Protecting cells

One of my ten principles last week was to protect cells with formulae in them, and it occurred to me that it might be useful to do a quick post on how cell protection works.

It is really easy to use and very effective for ensuring that formulae don't get over-typed.

Protecting cells is a two-stage process:

First of all we choose which cells we want to be locked, and then we switch protection on for that worksheet.

Setting which cells are to be protected

We determine the protection status of a cell by selecting a cell, or cells, and choosing "Format Cells". This can be accessed from the right-click menu.

The final tab of the window is Protection:

By default, all cells have the Locked box ticked (as shown above). This means that the cell will be locked (and therefore can't be edited) if the sheet is protected (in the next stage).

If we untick this box, then the cell, or cells, will remain free to edit, when protection is switched on.

We will typically select the data entry cells and unlock them in this way, prior to switching protection on for the sheet.

There is also a second tick box (Hidden) that allows you to hide the formula in the cell (again, only once protection is switched on).

Switch Protection on for the worksheet

In Excel 2007 onwards select the Review ribbon and click Protect Sheet. In earlier versions of Excel, select Tools->Protection->Protect Sheet.

In both cases you will see the following:

The top tick box will always be ticked (you can't click OK if it isn't).

Under that, you can choose to enter a password that will be required to unprotect the sheet. You will be asked to enter this twice to avoid mistyping it.

Below that is a list of tick boxes where you can allow things that would otherwise not be allowed on a protected sheet. By default, the first two are ticked, meaning that you are still allowed to select any cells (whether locked or unlocked). On a data entry screen, I often untick the top box, as this stops the user even clicking in cells that can't be edited - making it easier to navigate the sheet.

Other tickboxes (further down the list) that you may want to tick, are such things as allowing the use of Autofilter, Sort, or Pivot Tables. This form gives quite a lot of control over what the user can or can't do.

When you are happy with the above, click OK. The protection settings will be applied to the active Worksheet.

To unprotect the sheet again, go to the same place as you did to protect it. You will notice that the option now says Unprotect Sheet. When you click this, you will be asked for the password (if you applied one), and then the sheet will be unprotected again.

If you then go to protect it again, the tick box options are remembered, however you will need to set the password again.

And that's cell protection!

Click here for our our exclusive offer on Online Excel Training

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Excel Tip: My Ten Principles for Excel Good Practice

As part of my role on the Institute of Chartered Accountants in England and Wales (ICAEW) Excel Community Advisory Committee, I (along with the other members) have been asked to share my personal principles for Excel good practice - and I thought you might be interested to see them too.

So here goes...

My Ten Excel Principles:

1. Always separate out the three stages - Obtaining the data, Analysing the data, Presenting the data (OAP). This makes your spreadsheet far more flexible and easy to follow.
2. Never include numbers in formulae - always enter the variable in a cell and refer to the cell in the formula. This makes the spreadsheet easy to follow and to update when variables change.
3. Don't fiddle with the data - when using data from elsewhere, don't edit it, report on it in other sheets. This minimises errors and increases efficiency.
4. Use data validation where possible, to minimise data entry errors, and to make it easier to analyse and report on the data.
5. Always think "Is there a way I could do this that would save me time next time?"
6. Lay out data to suit Excel - not to suit the eye. Every heading, subtotal or empty column you place among the data makes it far less useable.
7. Get used to using the dollar signs when you refer to cells. Used well, you can write a formula once and copy it everywhere - Saving time and reducing errors.
8. Protect cells with formulae in them - it can be very difficult to find an incorrect (or missing) formula caused by accidental typing.
9. Use colour and formatting (as well as descriptions) to make it obvious where data entry is required.
10. Avoid merging cells unless absolutely necessary - merged cells make a spreadsheet far harder to edit.

I'm sure I will have missed a few key ones - these are very much my personal ones - and I would love to hear yours in the comments.

Click here for our our exclusive offer on Online Excel Training

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Excel Tip: Don't hold your data in Excel if you've got somewhere better!

Just a short post this week, and a one you might be surprised by. I am going to encourage you not to use Excel!

Yes, after years of telling you how to get the best out of Excel, I am going to tell you to avoid using it!

Well at least for one particular purpose...

Whereas a lot of my work is producing spreadsheets that are self-contained, I work with many clients where I use Excel as a tool to report from their database systems (typically accounting packages or ERP/MRP systems).

Many times, these reports are required because the system won't give them what they want - some of the time because all of the data they need isn't stored in the system.

In this scenario, it is very easy to store the additional data in the spreadsheet report, but I would urge you - DON'T!

Whereas the report will work just as well, if the additional data is stored in the spreadsheet, the information will not be available from within the system, or for other reports required in future. Very quickly, you can find  various different versions of the same data scattered around in spreadsheets all over the business.

If at all possible, find a way to start recording this data in the system - and use Excel to report on it like the rest of the data.

This is usually possible, although inevitably there are some systems that are not flexible enough for the company's true needs. Storing the data in a spreadsheet outside of the system will work as a patch, but is not the ideal solution.

Excel is a fantastic tool that has many applications - but it is important to know when not to use it too!

Click here for our our exclusive offer on Online Excel Training

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Excel Tip: Don't fiddle with the data!

Yesterday I had a conversation with a client that I have had, in various forms, many times before.

It relates to using information in a spreadsheet from an external source.

Almost every time I find a client using data from their accounting system, or provided by a supplier, customer or other third party, they approach it in a way that significantly multiplies the work involved and increases the risk of errors.

It's a shame because all that is required is a different approach, that rarely requires greater Excel skills than they already have.

In most cases the information is received as a spreadsheet - either generated from another software package, or emailed from a third party. Typically this information then needs to be edited and re-formatted to produce the desired end product - which might also have some pivot tables or charts based on the data.

The usual response to this situation is to do just that - take the spreadsheet that has been received and then edit it until it look s how they want it.

Unless this is a truly one-off situation, it is almost certainly the worst route to take, as you will need to do all of this editing again when you receive the next version of the data, next month, next week, or tomorrow!

Applying a little thinking to the situation suggests a much more efficient approach.

You will always receive this data in the same format, and will always require the same output format, so why not use this knowledge.

DON'T EDIT THE SPREADSHEET YOU RECEIVE!

Instead of editing the spreadsheet you receive, paste it into a blank sheet in a new workbook and create the output you want on the other sheets referring to the sheet with the data on it, but not editing the data itself at all.

Doing it this way, means that next month/week/day you can just paste the new data in and your job is done!

You can spend your time getting it right once - reducing the risk of errors - and remove all of the work in future.

Incorrect spreadsheets have hit the headlines in the US this week, with Excel errors discovered in the calculations behind a report by Harvard Economists Reinhart and Rogoff that has been used by Republicans to support budget cuts. Another timely reminder of the potential consequences of not getting these things right.

Click here for our our exclusive offer on Online Excel Training

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".