Excel 2010 & 2013

Unpivoting data using the data explorer preview for Excel 2010/2013:

Standard

Introduction:

Data Explorer add-in is amazing! It’s helps you: combine, find and re-shape your data in Excel 2010/2013. I’ve blogged about: 1) How to merge Table Data and 2) How to clean duplicate data and now in this blog post, I want to share a step-by-step on Unpivoting data using the Data Explorer add-in.

Before we begin, If you haven’t downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx 

Problem:

What is un-pivoting? I hear you ask. Instead of explaining it, let me share an Image:

data explorer unpivot excel

BTW, the above data is from my Facebook Page Insights.

So our problem statement is (please refer to above Image): we are given table blue and we need to output table green. In other words, we need to Unpivot the data.

Solution:

Here are the steps:

1) Open Excel, Open Data Explorer add-in. And Connect to your data. Wait when you see the Query Editor.

2) (Optional) In the Query Editor, Rename the query. I renamed it to “Unpivot Data”. And this how my query editor looks:

data explorer unpivot excel 2

3) Now, Select the columns that need to be unpivoted > Right Click > Unpivot Column

Note that I’ve selected all columns that I want to UnPivot:

data explorer unpivot excel 3

4) You’ll see the updated results in the query editor window. I renamed the columns “Attribute” to “Age and Gender” and “value” to “reach”. If you want to rename the columns, select the column > Right click > rename.

data explorer unpivot excel 4

If everything looks OK, click on Done in the bottom right corner

5) There you have it, Unpivoted data in Excel 2010/2013 using Data Explorer add-in!

And then its super easy to create charts, Here’s one I created after I had unpivoted the data:

data explorer unpivot excel 5

Insight: For my blog, my Target Audience seems to Male between the age of 18-24 and then 25-34.

FYI: The Date Range of the Data Set of 1st Jan 2013 – 25th Apr 2013.

That’s about it for this post, Here are some Related articles:

Your comments are very welcome!

 

Merging/Joining datasets in Excel using Data Explorer add-in

Standard

Problem:

Merging/Joining/Combining data-sets in Excel has not been an easy task. There are third-party add-ins that makes it easy but out of the box, excel didn’t have an easy way to merge/join table data. But now with the Data Explorer add-in, we have an add-in that let’s us merge/join data in excel w/ few clicks.

If you haven’t downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx 

Situation:

Input is Table 1 & Table 2. The output we need is merged Table.

Table 1:

DateDaily New numberMonth
1/1/201201
1/2/201201
1/3/201201
1/4/201201
1/5/201201
1/6/201201
1/7/201201
1/8/201201
1/9/201201

………………………………..

Table 2:

MonthMonth Name
1January
2February
3March
4April
5May
6June
7July

Merged Table:

DateDaily New numberMonthMonth Name
1/1/201201January
1/2/201201January
1/3/201201January
1/4/201201January
1/5/201201January
1/6/201201January
1/7/201201January
1/8/201201January
1/9/201201January

………………………………

Solution:

Let’s see how data explorer can help us Join/Merge Table 1 & Table 2.

1) create query that connects to Table 1 & Table 2.

data sources explorer excel

2) Once you have queries that connect to the tables need to be merged, then click on Merge

3) Once you click on Merge, you’ll see a dialog:

Here you need to configure three things:

a) First Table

b) Second Table

c) Columns that will be used to merge/join data

In this case, this is how my merge dialog looks:

merge join excel data explorer

4) Once configured correctly, click on OK. You’ll see a dialog box where you can configure the output of the merged table. click on the new column to see the options that are available to you to configure the output of the merged table:

merge join excel data explorer 2

5) In this case, I’ve selected just one column month name that needs to be merged. You can also explore the aggregate tab in case you’ve numbers that needs merging.

merge join excel table data explorer 3

6) This is how the output looks:

merge join excel table data explorer 4

7) Rename the new column.

Select the new column > Right Click > Rename

8) Click Done if it looks OK.

9) The merged data is now available to you in Excel!

And one can analyze it!

Let’s see before and after. Note that instead of month numbers, we now have month names

merged data join table visualized excel 3

Conclusion:

In this post, we saw how to merge/join/combine data from two different sources in Excel 2010.

Related articles