Mar 17 23

Developing Good Reports in Excel – Factors to Consider – Part 2


It feels great to be writing again about developing reporting in Microsoft Excel. Before we continue let’s recap the salient points from part 1;

  1. Making your users the focus of the report is paramount.
  2. Emphasis on navigation and codes resulting in easy usability
  3. Defining relationships well so that the report is organized properly and easy to relate the various parts.

In this discussion, we will focus basically on data integrity and the different ways to protect data in cells through to protecting the workbook. As this discussion is intended for beginners to intermediate users, we shall restrict it to only User Interface (UI) capable functions without veering into visual basic.

As usual, depending on what you intend to achieve there are various available options to choose. The key issue to note while shuffling between the options is flexibility. The last thing you would like is for users to be calling you every minute to grant them access to pertinent areas of the workbook which may effectively lead to unbundling all your work and could lead to various versions being sent back to you. This discussion is also mainly intended to explain the various options but not describe how to use those options. You may post a comment if you need me to walk you through some of them.

Before you proceed to restrict any aspect of your report, you need to list all the intended users and the level of input they will require. It is easier if it is a read-only workbook in which case you are free to restrict almost any aspect with your only worry limited to formatting and output choices including printing. Next you need to identify all the areas and possibly name all the ranges using the name manager. This basically may come in handy whiles you are deciding to apply your restrictions.  

Menu Path: Formulas→Defined Names→Name Manager

With these basic steps done with, let’s proceed to discuss the various options we have.

1. Data Validation Function

This is my favourite function especially when I am preparing a quick report. As the name suggests, data validation may be used to ensure that data input in defined areas are valid. This is mainly achieved through restriction. If you are familiar with Microsoft Access or databases, you can easily relate with this. Data validation in Microsoft Excel works with the same logic.

Menu Path: Data→Data Tools→Data validation

What this function really does is to let the analyst choose what kind of data is acceptable in any given cell. For example if you define a cell to accept a rate in percentage, you may not want the user to enter anything above 100% in that field. If the cell is left as default, the user can easily enter any value. By default all cells are allowed to contain any character available on your keyboard. You will realize that data validation function allows you to set a range of specific formats or customize your input criterion. The upside of using this function is that it allows for some level of interactivity by allowing the analyst to choose customized messages when the cell is selected, or set error alerts and guidance to inputting the acceptable information. The disadvantage of using the function is that it doesn’t truly provide control because the cells can be changed by the user. It is easy to copy and paste a virgin cell with ‘any value’ characteristic over a restricted cell forcing the properties of the new cell to take over. One can also easily drag over the cell of range.

2. Sheet and Workbook protection

This is the easiest way to protect a worksheet or data. It is simply putting passwords to the specific sheets or the entire workbook to preserve the structural integrity.

For worksheets, you are allowed to choose which specific structural part/function of the worksheet you would want to allow users to edit. In other words you mainly password protect the sheet but give exceptions to the protection. This is to allow for flexibility but I must emphasize again that this is really good if you truly need to present a read-only report devoid of any changes.

 Menu Path: Review→Changes→Protect worksheet

For workbooks, it is more straightforward. This is because the intention is to lock the entire structure of the workbook. This allows the user to open the workbook and work within the cells unrestricted. The user can however not make structural changes such as adding new sheets, moving sheets or deleting sheets.

 Menu Path: Review→Changes→Protect workbook

The advantages of these options mainly lie with the fact that they are somewhat flexible. They allows some exceptions thereby allowing for some interactivity with the user. The major downside is that, when the user needs access to work within the file, you will be called to unbundle the workbook and therefore expose the entire report to manipulation.

3. Allow users to edit ranges

This is the most effective way to truly make your report flexible. Your named ranges as discussed in the introduction becomes important with this option. The reason for my association of flexibility with this option is that it truly allows the analyst to choose which ranges are not to be touched. This is the same as protecting worksheets right?………………………………………………………………………. Wrong! The key difference is that worksheet protect allows the user exceptions to some functionality such as adding new rows or deleting them, inserting columns or selecting only particular kinds of cells. The edit ranges option specifically works with ranges, which effectively is a cell, an array or group of cells. This allows the analyst to set passwords for specific cells/ranges. The user may be allowed to see cell content and select or navigate but cannot change the content of the cell unless the correct password is input. The flexibility here is that the same report can be sent to different teams who have separate password to separate portions of the same report to make contributions. Collaboration in teams cannot be overemphasized and are rapidly becoming the focus of complex teams.

The upside of this option is that users are allowed the freedom to navigate every portion of the report. This is important as to allow the report to be seen holistically. Remember our emphasis on relationships in part 1? The disadvantage of this option is that it is highly time consuming. You can spend hours on restricting cells and ranges when dealing with large reports.

Menu Path: Review→Changes→Allow Users to Edit Ranges

4. Read-only and total lockdown

This simply means you can password protect your report so that only authorized persons can edit or open the report. This option is included as a Microsoft office option rather than specific to Excel. It is set while saving the report.

 Menu Path: Save As→Tools→General Options→Password to open/modify

The advantage of these options is that the report cannot be tampered with. The user can either edit or not or open the workbook or not. This means that data integrity is left intact as was done in production and only authorized persons may make changes. The downside is that passwords must be kept well otherwise any unauthorized person who accesses the password has total control of the report. Also lost passwords are impossible to recover.

Et voilà! These are some of the ways to ensure data integrity of your reports. It is important to note that the options are not mutually exclusive. Excel allows you to mix the various methods to get the best out of your report. The rest is artwork. If I skipped anything, kindly share your views in comment.