The Excel Expert

Debra Dalgleish (http://www.contextures.com) is one of my Excel heroines and the pivot table guru. She is someone whose work I regularly reference in class.

I recently asked her to help me answer a question that regularly comes up in class when people ask about pivot tables – how do I know what to put where?  She answers the question comprehensively in this blog entry.

http://blog.contextures.com/archives/2016/09/15/how-to-plan-a-pivot-table/ 

However three tiny additions I would put with this is the following:

1. Generally put whatever it is you want to do the calculations on in the Value area
2. I tend to put dates in the Row Labels section, group them and then move them around.
3. Get started, put in something – you can always move and re-organise it if you don’t like it…

Enjoy!

Continue Reading

Well, had a bit of a weird experience. I had written a full blog post on how to use Solver to identify what numbers make up a specific number. Let me give you a specific example. Many years ago – when I was working in an office – I’d have a difference in my bank reconciliation e.g. 101.80 so I’d want to see what numbers in my list of bank transactions made up that number. It was a tedious thankless task – especially since I didn’t have Excel at the time…

However it turns out that it completely disappeared..so here I am writing it again…

So recently I was giving a course and one of the participants told me that she had come across a way – using Solver – to sort this perennial problem for accounts. I was immediately intrigued and went off to research it. Turns out that Solver was the key…

Can’t find Solver?

Solver is not installed by default. So to get it to appear on your Data ribbon…here is what you need to do..

1. Go to File – Options – Add-ins (this is for both Excel 2010 /2013)
2. Find Solver in the list (it will probably be under the Inactive Applications Add-ins)
3. Click on the Go button beside the Manage Excel Add-ins box at the bottom of the screen
4. Tick the box for Solver…
5. It will now appear on the right hand side of your Data ribbon

 

Using Solver to solve your “what numbers make up this number?” dilemma

The original solution was developed by Tom Ogilvy MVP

 

Continue Reading

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

. (note that in the video I include a bit extra on cleaning up your dates)

1. Click on Power Query
2. Click on From Other Sources.
3. Click on From Facebook 
4. 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.
5. 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.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1. I’ve chosen Likes for the demo in the video below but you could obviously choose whatever you want to analyse.
2. Then click on Load.
3. 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.

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:

1. 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
2. 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…

Continue Reading