Excel is used across many areas of business and in many different ways. To some people it is a simple way to store simple data. For others it can be an advanced reporting tool encompassing automatically refreshing charts and advanced forms. Whether presenting or storing data, the Forms toolbar can be used to protect and improve the data itself and create a user-friendly interface for the workbook. This article will discuss some simple ways of enhancing a workbook without needing to use VBA coding.

There are several ways to give a simple workbook a professional look and feel. If you want people to take your work seriously then it is important that data is presented not only with the use of appropriate charts, but also navigation and validation. There are several reasons for this. The first is when creating a worksheet where users are required to enter data; the data must be protected from user error. In order to avoid human error for example, you could create a very simple drop down list with values the designer can set. In this way nobody can misspell an entry and they can quickly and easily see what is required. If for example a sales spreadsheet required a country field and users were able to enter the country manually, they could misspell the country name. If a chart was created on the data to show sales per country, one country may have several pie slices, one for each spelling. The validation prevents this while saving the user time in having to manually enter the data.

As well as validation there are objects useful for navigation. When delivering a large workbook containing many sheets it can be cumbersome for a user to search through the tabs for what they require. As an example, picture a worksheet filled with sales data over an entire fiscal year. Each month has its own tab along with several tabs of summary information including charts based on the monthly and annual figures. There are therefore over fifteen tabs of data. If the user opens the workbook and wants to find an entry on the August tab they have to begin by moving through all the tabs to find August. If the designer of the worksheet has considered how the user can navigate through tabs more simply then a menu tab can be included with navigation buttons and macros placed to ensure a user-friendly workbook.

Always include an extra front tab on any large workbook. Record one macro per tab where you start on the menu tab and go to the month tab. On the first tab it is then possible to use the form buttons with the macros attached. This essentially gives you a menu page with navigation buttons so that a user can easily switch to the tabs they require without the need to search. It is even possible to turn off the tabs so the user does not see them. Using the same method you can create a button on each tab that will return the user to the main menu.

The form toolbar allows the designer to create these useful and professional looking objects very easily. When selecting the chosen form object it can simply be drawn onto the worksheet where it is required and the size adjusted as appropriate. Double clicking on the object brings up the properties for the object. These properties are organised into tabs. This allows the designer to set the data or attach a macro. To access the format controls on any existing object, right click it and double click on the border.

The more advanced user can drill down into these form objects even further by examining the VBA code behind them. This style of a simple design interface for the novice designer with the ability to directly alter the code for the more advanced user covers all bases. It gives anyone the ability and freedom to improve and upgrade their worksheets.