Excel: Five Issues to Consider When Developing an Excel Template

Excel templates are a great way to start a spreadsheet for any given project.  Since most excel templates give you the basic elements needed, you may want to add additional features to give your spreadsheet more personality and depth.  The article below demonstrates the power of a few tweaks to make your excel template more yours instead of a canned excel template.

Excel: Five Issues to Consider When Developing an Excel Template

Microsoft Excel Templates are fantastic tools that allow you to get your team to put in their data for your business quickly and easily whilst still allowing for the consolidation of the data with very little work. There is however five key issues you need to consider when developing the Excel Template.

Issue 1: Protect the Worksheet and Cells

The first issue when creating a template is to ensure that your worksheet and cells are protected. For example the cells you want people to put data into should not have protection applied and the headlines you need to use to explain where to put the data into should in fact be protected.

Further to this in Microsoft Excel 2003 you can set that cells that are protected cannot be selected and I recommend that all headline areas are set in this way..

Issue 2: Lock the Workbook against Structural Changes

There are different levels of security in Microsoft Excel, such as security at the cell level, worksheet level and workbook level. Previous to this, I recommended that you use protection at the worksheet and cell level to restrict the access people can have to certain cells. The workbook also should be protected to ensure that your users cannot modify the underlying template. You can set two levels of security which are the Modify option and Read Only Option. The only time you would use the Read Only option is if your template was only being used to print out the data.

Issue 3: Use Formatting to make it easy to know where to put the data

There are some basic fundamentals I always follow when it comes to formatting in Microsoft Excel. The background of the area where I want my team to enter the data is always a lighter color, with the headings backgrounds being formatted with a darker color. This strategy is in line with that used in software development. You will notice in applications like Microsoft Word, you always type the text in the lighter area and the darker area is the headings.

The flip side is that if your background is a light color then the text being entered must be a dark color and for the headings, if your heading backgrounds are dark then the text should be light.

Always be carefully when using the colors Red, Green and Blue on templates as people with color blindness can find those colors confusing. The other issue to watch with formatting is to ensure that your colors print out correctly, especially if you have used color on the screen but it prints out in black and white. Some colors do not print out well on black and white printers.

Issue 4: Hide Unused Cells

One of the biggest mistakes I see people do with templates is to not hide any unused cells. Having unhidden cells runs the risk that your team may put in information in the wrong position or they may believe they need to put more information in than they need to. Always hide your unused cells.

Issue 5: Use Formatted Gridlines

When ever you are setting up a spreadsheet where a range of data needs to be entered always use dark gridlines to indicate exactly where the data should be entered. Generally I will use gridlines only where I want my team to enter the data. I do not generally use them around the headlines.

These five key issues will help ensure that your team uses the template in the manner that you intended. To finish off I would like to outline in one list the Five Key Issues you need to consider when developing a Microsoft Excel template:

Issue 1: Protect the Worksheet and Cells

Issue 2: Lock the Workbook against Structural Changes

Issue 3: Use Formatting to make it easy to know where to put the data

Issue 4: Hide Unused Cells

Issue 5: Use Formatted Gridlines

If you are looking for specific templates we have available Microsoft Excel Timesheet and a Microsoft Excel Invoice Template.

One thought on “Excel: Five Issues to Consider When Developing an Excel Template

  1. Pingback: Tweets that mention excel templates, excel template, create excel template, excel template creation, -- Topsy.com

Leave a Reply

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

Time limit is exhausted. Please reload CAPTCHA.