Creating a good data table for analysis

Changing the old way we organize data helps others downstream to analyze and retrieve the information quicker and easier. Learning what makes a bad table can help you create a better table for analysis.

My job as an Analyst provides me with many opportunities to turn those ugly existing tables that Client owns into great tables for analysis and reports. Uncovering secrets that the data reveals is my passion.

For the rest of this article, we shall refer to the image for illustration.

#1 - Too many Headers' rows

Row 5 to 9 are Headers! This makes it impossible to feed into any of analysis tools that a Analysts owns. Analysis Tools include but not limit to the following: Excel PowerPivot, Tableau, Microsoft Power Bi, Qlik Sense etc.

#2 - Data of different categories should not be merged in a single column

AQ10 and AQ12. AQ10 is the 'Product Category' and AQ12 is the 'Product Item'. Instead, 'Product Category' should have its own column and 'Product Item' should have its own separate column.

#3 - Too colorful

Colors that are used indiscriminately increase the file size unnecessarily. Colors should be used by the originators to highlight important information that they want viewers to see.

#4 - Data shouldn't be pulled from another worksheet or table

The formula bar reveals that data is pulled from 'Partsdownload' worksheet. Imagine data from a single column in one worksheet is replicated in another worksheet within the same workbook. This results in unnecessary increase in file size and it requires more processing power because of Vlookup function. The solution is to use a Data Model to consolidate all the tables.

The list is non-exhaustive. Excel is a product of collaborative wisdom of many wise men and women.

Image courtesy of http://mbwob.com