How To Organize Excel Data For Reporting And Analysis

Although Excel has excellent tools for extracting information, it's important data is organized efficiently before considering any reporting functions. Because Excel has some impressive presentation features such as graphs and tables, it's tempting to set out the information in the same way as a Word document or Power Point presentation.

However, it's a good idea to consider what you want to use the data for, then structure the data accordingly; in other words, begin with the end in mind.

An Example Of Poorly Structured Data

A common task might be to organize a list of contacts which could include:

CustomersSuppliersVehicle repairersIndustry colleagues

A simple way to organise the list might be to group the contacts together according to the type of contact they are; and record all the information in the one worksheet.

Customers
===================
Organization Name, Contact person, Phone number, Region
ABC LTD, John Smith, 06 25356987 ,UK
XYZ Ltd, Joe Fernando, 03 56523945, Canada

Suppliers
===================
Organization Name, Contact person, Phone number, Region
ABC LTD, John Smith, 06 25356987, Australia
XYZ Ltd, Joe Fernando, 03 56523945, USA

While this format makes the data easy to see at a glance, when your list grows it might become increasingly difficult to extract the information you need.

Structured Data In A Flat File Format

If your information is organized in a flat file format it can be easier to extract the information you need and scale your Excel application when your organization grows. For example, you could organize the data in this format:

Organization, Contact Person, Phone number, Region, CATEGORY
=================================================
ABC LTD, John Smith, 06 25356987, Australia, Customer
XYZ Ltd, Joe Fernando, 03 56523945, USA, Supplier

In this scenario, creating a category (or a term of your own choosing) makes it easier to filter records and find information. From your master list, you could filter the records to select:

Suppliers in the USACustomers in Australia

And if you're planning to expand your database it will be easier to develop other search and reporting functions with your data correctly organized. As you think about the structure of your data, a typical question to ask might be:

Given the design and structure of my data, is it possible the extract the information I need?

Summary

This article has attempted to stress the importance of data design as opposed to reporting and analysis. If you're able to spend more time designing the structure and organization of Excel data then sophisticated analysis and reporting functions are more easily developed.

Andy L Gibson is a former Web Site programmer rediscovering his interest in software applications for small business.

Previously he has used AJAX, XML and ASPX to program Web Sites for restaurants, auction houses and Heath Authorities. He is keen to answer questions and explore business opportunities whereever they might present themselves. You can talk with Andy at his blog at http://solutions4business.wordpress.com/ including links to other articles on VBA issues he has written.

View the original article here