In this blog post I’m going to show you how to use one of the new add-ins from Excel (Power Query) to quickly pull in your data from Facebook and then summarize it.
Note that if you have found this useful that I cover both pivot tables and Power Query in my book Your Excel Survival Kit – a Guide to surviving and thriving in an Excel world (available at Amazon.co.uk and Amazon.com)
First of all you will need to download Power Query (only available for versions 2010 and later in Excel). You can download it here. Full installation instructions are available here at that link as well.
It will now appear in your Ribbon.
Step 1 – getting your data from Facebook into Excel.
You can also view a video below
[fvplayer src=”https://s3-eu-west-1.amazonaws.com/excel-expert-lesson-files/Power_Query_Facebook_Part_01.mp4″]. (note that in the video I include a bit extra on cleaning up your dates)
- Click on Power Query
- Click on From Other Sources.
- Click on From Facebook
- You will now be prompted for your Facebook username and password. When you have entered this you will also receive a request about allowing a BI (Business Intelligence) app to access your data.
- When you have accepted this, then you will see a screen like the following. From the lower drop down box, choose the data set you want to analyse.
- I’ve chosen Likes for the demo in the video below but you could obviously choose whatever you want to analyse.
- Then click on Load.
- This pulls the data into Excel. You now have your data in Excel.
Step 2 – Analysing your data in Excel
For this step, we are now going to use Excel’s primary analysis tool : Pivot Tables. [fvplayer src=”https://s3-eu-west-1.amazonaws.com/excel-expert-lesson-files/Power_query_with_facebook_part_02.mp4″]
Click anywhere in the Facebook data you have just imported.
Click on Design – then click on Summarize with Pivot Table. Accept the default setting it gives you in the next setting (just click OK)
You will get the following screens:
- Drag ID down to Values (you enter whatever it is you want to add/get the average of etc) in the Values part of the pivot table
- Drag Category down to Rows
If you look on the left hand side you will now see your data has been summarized….
You can also add a chart to that by clicking in the pivot table, then clicking on Pivot Chart. (Note that this ribbon will NOT be visible unless you have clicked in the pivot table)
I have not included the file with this because it’s my own Facebook data but if you want to get a picture of your interactions on Facebook, this is a great tool to start with.
In the video I’ve also added a bit about how you could summarize your interactions across time as well…
As always if you have any ideas or suggestions, please feel free to add them…