Tabular Data & Data Analysis
Analyzing Data in Excel
One of the most common ways to analyze data in Excel is by using Pivot Tables but before we can use Pivot Tables we must learn how to convert our data to Tabular Data.
Tabular Data
Microsoft Excel is designed to work best with tabular data this is true for Pivot Tables, Power Pivot, Tables, and dashboards. Unfortunately, the data we receive is often not in a tabular format. The data is often in a pivot format or some other structure that does not lend itself to easy analysis.
Before learning how to use Pivot Tables one should learn what is tabular data and how to convert data to a tabular format.
Data format for analyzing data in excel (Tabular Data):
1. Column headers in one row
2. Each data record is in one row and each row contains a complete recordset
3. No blank rows in the data
4. No subtotals throughout the data
5. Each field/column houses its own type of data (i.e. Month, Product, amount, geographic region, etc.)
Tabular data needs to follow the above principles if your data is not in this format you will need to fix it. For more on tabular data principles go to the following blog Excel Tabular Data • Excel Table • My Online Training Hub.
Normalizing of the data into tabular format can be done with Excel formulas or with Power Query. Strongly recommend learning POWER QUERY for cleaning and converting your data prior to conducting data analysis.
In the next section I am going to provide some great blogs, and videos for learning more about tabular data and working with tabular data in Excel. Once you master tabular data and converting your datasets to tabular data you will find your analysis will be much easier.
Recommended Blogs
The first recommended blog comes to us from Jon Acampora.
How to Setup Source Data for Pivot Tables - Unpivot in Excel (excelcampus.com)
This guide walks us through how to structure data for analyzing it in pivot tables. The guide is a little dated as it was written in 2013 but it is still relevant today. One thing to note is he mentions Power Query is only available as an add-in but today it is native to Excel no add-in is required.
The blog also includes a video that explains the unpivot process that I walk through in my guide.
The second blog I recommend is from My Online Training Hub and was written by Mynda Treacy and is also from 2013.
Excel Tabular Data • Excel Table • My Online Training Hub
This blog walks through what a perfect tabular data set looks like and what needs to be included for the data to be normalized. The blog explains the different types of data we will often see and what is wrong with each type. These include
Semi-Report - Has some degree of summarizing in the data
Flat Data - A flat data can be used in a pivot table, but it has some summarizing which makes it difficult to conduct analysis
Data Entry Format - This is a format designed for easy entering of data not analyzing data
Report Format - This is when the data already comes in a nice, formatted data type
Multi-Sheet Format - This is when the data is spread out on multiple spreadsheets
After discussing each type, she provides three methods to use for fixing the data.
The third example comes from Jeff Lenning at Excel University. This example walks through converting a crosstab dataset to a tabular dataset by unpivoting the data using the Unpivot functionality in Excel.
Unpivot Excel Data - Excel University (excel-university.com)
Next, we will provide a couple of video resources for learning more about tabular data and unpivoting data in Excel.
The first video is from Myexcelonline.com by John Michaloudis. The video discusses what a tabular data set is and how to put it into a pivot table.
The next two videos are from Leila Gharani. The first shows how to convert columns to rows in Excel using the unpviot function in Power Query. The second video is how to unpivot more complex data with multiple headers using Power Query.
First Video
Second Video
Conclusion
If you hope to be a good analyst using Excel then you must learn how to convert data to a proper tabular dataset. By reading the blogs, watching the videos, and practicing the example you will be well on your way to becoming a better analyst.