Excel offers some very powerful tools for analyzing data, among them:
Lookup functions
Sorting
Data filters
Pivot tables
We will cover each of these in upcoming lessons. However, to get the best use of these tools, here are some ideas to get your data in shape. Many of these ideas come from years of consulting with clients and seeing what they had first worked with.
Get your data setup in a block. Your data should be together in a rectangular block. Steps you may need include:
Eliminate any blank rows or columns
Pull together information from different worksheets or files into one file
No blank column headings. Some features may work with blank column headings.
Short column headings. Keep the column headings short and to the point.
Consolidate column headings. You may have column headings that span across two rows. For example, one row may span across several columns and say Sales. The rows below may be each region. You need to collapse the two rows together, so that the headings may read Sales East, Sales Midwest, etc.
Date Fields. You may have a field that signifies the date, such as invoice date or entry date. Ask yourself, how will you be analyzing the data. Perhaps you will be better served if you add two columns, one for the month and one for the year.
Results. What key result will you be looking for when analyzing the data? What information will you need? What questions will you ask? Can you get the answers you want or to you need to reorganize the data. For example, in the sales example mentioned above, it may be better to organize the data with region as a separate column and just one sales column, rather than a sales column for each region.
Size. The last couple versions of Excel are limited to 65,536 rows. Excel 97 and before may have smaller limits. Think about this when setting up your data layout. If you exceed the size limits, you may need to break up your layout or use a different platform like Access.
Eliminate Columns. Are there any columns that should be eliminated? Will the column make any difference in the analysis? If not consider cutting it out.
Eliminate Rows. Are there blocks of records (rows) that could be eliminated from your population?
Updating. How will the data be updated for the future? Can this be easily fit into your layout? This is one great use of the programming language behind Excel, VBA, to streamline these updates.
Abe Lincoln said if he had an hour to cut wood, he would spend the first half hour sharpening the saw. Spend time up front on your data layout and you can save time and get more meaningful results at the back end.