Microsoft Excel as a Reporting Tool

Coming from a reporting background, one of the most important and frequently used reporting tools is Microsoft Excel. Even if a business uses a reporting package to grab their data from a database, excel is often the way it is presented. Almost every reporting software utilizes Excel as an output type. For those businesses that do not use a reporting tool, Excel becomes even more important as a way of presenting and analyzing data.

Microsoft Excel combines reporting functionality such as charting and summarizing tools along with a huge array of formatting capabilities. You can analyse data and present it in a well formatted, professional way, even managing large amounts of data with navigation. Yet much of this simple, user friendly functionality goes unused. For a small amount of effort and time, the way data is presented can be completely transformed. Let's examine a few simple methods.

Pivot tables and charts are of course the data analyst's dream. Pivot tables can take large amounts of data and summarise it. They are flexible in the way they present it and what information they are delivering. As an example, let's take a sales spreadsheet where all the sales are given per office, per sales person. This is the data a manager would want to see, however managers are busy people and they do not want to spend hours wading through the details. They want efficient summaries at a glance, preferably in pretty colours. Give them this along with the data in detail and your manager is no longer rolling his/her eyes at the data you've emailed and is instead inviting you out for lunch.

In order to achieve this, add some pivot tables or charts which will give a simple breakdown of the total sales value or number of sales by office or sales person. It's not static either as the user can easily select the data they want to include or exclude from the table with the automatically populated drop down list. A pivot pie chart can show a manager which sales office is bringing in the most cash. Both objects can be extensively formatted, even using company branded colours and logos for extra visual brownie points.

Once you've got all your data organized on different tabs with appropriate charts, navigation can be used so the user can easily find their way around your workbook. Nobody likes getting an email on a Friday afternoon to see a huge 14 tabs of data ready for them to plough through at their leisure. Instead, realize the difference it makes when they open the same workbook to be presented with a menu page, branded in company colours and sporting the company logo with a selection of buttons allowing them to easily pick what data they want to see.

A simple macro can be recorded to take you from the menu tab to the other tabs. Each macro can be assigned to a separate button on the menu page. A manager now opens the workbook and chooses the data he/she wants to see first. On each tab they go to is a button which directs them back to the menu page. This is about the user never having to search for the data they want regardless of how well you named your tabs. Now you've been out for a second lunch in which you are discussing future promotion prospects and are well on your way to becoming the managers pet.

Once you have added the navigation and summarization, it can't hurt to take a look at the detailed data to see if it can be presented in a way that helps people identify what they are looking for more efficiently. For example, you can format an entire column with condition formatting from the format menu. You could change the font colour of any sales value greater then