This Blog contains an ever growing set of detailed pages on How to … do things and pages of simple but very useful Tips.

18th August 2017: unlinking two or more Pivot Tables from the same cache. This page shows you how to use one database as the source of two or more pivot tables from which you then create two or more pivot charts. Not intuitive and I show you two methods and mention two more. How to menu, Pivot Tables …

9th August 2017: Here’s a very simple task but it is worth a fortune to the delegate I used it with! In my course in Riyadh this week I had a delegate who had an apparently very simple problem. Smart man but he just couldn’t find the answer. Look at my TEXTJOIN page to see the problem and my solution. You’re welcome!

24th June 2017: Danielle Stein Fairhurst published an interesting list of favourite Excel functions as nominated by 20 Excel MVPs. This page shows you what I had to do to get this list into Excel, to separate out and count all unique instances of the functions listed and then how I used Pivot Tables to finalise my work. Pivot Tables How to Menu. You can download my Excel file, too.

15th June 2017: how about the English Premier League Fixture List for 2017 – 2018 that uses an Excel Table, Power Query, Pivot Table and Slicers and yet is very easy to use? Here it is: Pivot Tables How to … menu.

13th June 2017: Video Time. Every now and again I make a video and today is a day like that. Go to this page, Video Time!, to see a sort of a dashboard by video I have created using data from opinion polls in the days leading up to last week’s ridiculus British general election. The video is there and the spreadsheet can be downloaded.

3rd June 2014: 1 Next week I am presenting a two day course on Financial Modelling and Business Intelligence in Singapore. Whilst it’s not too late to sign up, look here for one of the cases I will be including. There are no solutions presented here except what you see in the case itself: if you want my solution, either come on the course or send me your honest attempt at using Power Query, Power Pivot as well as Power BI for Desktop and we can talk. Talk to me initially by commenting on the page.

2 YDAYDG …You don’t ask, you don’t get. I don’t program in VBA to any significant extent but I do include it in my courses from time to time. Following a a query from a previous delegate, I have put together a free pack on starting Macro/VBA programming and it’s yours … but YDAYDG … call me! Talk to me initially by commenting on the page.

3 Benford’s Law Update I have updated my 2013 page on the Sunday Times Rich List analysed according to Benford’s Law. Again, not what we should expect but what to make of it? Not at all sure. Here is the link

14th May 2017: POWER QUERY example … you don’t have to be rich to use some rich features in Excel! The UK’s Sunday Times published its 2017 Rich List last week and I have used Power Query to take the basic data that comes with the list, included the sub categories, put all of that into an Append query and then started to analyse everything using Pivot Tables. There is no page for this because I am under pressure from work at the moment. That means, if you know Power Query already, you can take the files and see my query and add to them, change them and so on. If you don’t know Power Query yet, you can take the files as a working example of something very useful that you can learn to use over time. Since I am having a membership clearout, you need to ask for the two files you need for this exercise but do please note I can’t offer any support on the files for a couple of weeks or so. Send me an email to duncanwil … at … gmail … com and I will send the two files you need within 24 hours or so. In your email you need to include a subject or message that tells me what you want! I have had completely blank messages in the past that I just delete because I am not psychic!!!

7th May 2017: FIVE ways to create a histogram: from the highly specific to the rough and ready. There are six methods, by the way, but number six is left out! Excel Tips … Charting menu or click here to go straight there.

21st April 2017: My Excel Solutions for Accountants books: I have just completed some updating work on this three volume set and this is what my readers have said about them:

  • Absolute solution to Accountants who have not used the excel experience in doing calculation and graphs on financial data analysis
  • The book is really fantastic, a must read for professional accountants.

The books are available from my page here: www.bookboon.com

21st April 2017: A page on Data Analysis. I have taken data on the human body among other things and analysed them as worked examples. There are four cases to study and few all of them you get the data only excel file, the fully worked Excel file and a PDF with my notes and explanations. There is a further six cases that I have worked through and that you will find in both Excel files but there is no analysis in the PDF file: these are for you to complete. I have put this in the Excel How to… Other … Functions menu but you can go straight to it from here.

6th April 2017: A really useful insight this time: Pivot Charts from Power Pivot even Without Pivot Tables. Take a look at this page and download the PDF file that comes with it. Sorry but I cannot share my Excel file with this one. Excel How to … BI sub menu.

3rd April 2017: This is something new, using Microsoft Sway to format a file for me. I know what it looks like in the software itself but what happens when you go here … EPL Analysis and click the Sway page link … at this stage I don’t know. My analysis is good so let’s see what Sway an do! This page is in the How to … menus too!

2nd April 2017: Rice … some very interesting and useful data gets the Power Query Unpivoting treatment. I have discussed upivoting data before but here is a very practical example that uses international data on rice. You can go straight to the page I have created or go to the Excel How to … BI sub menu and look for Unpivoted rice

If you’d like my help and guidance on something, just ask and I’ll do what I can.

Duncan Williamson

Introduction for Absolute Beginners: Introduction to Excel for Absolute Beginners
Excel Files for Practice:
The basics of spreadsheeting 1
The basics of spreadsheeting 2
The basics of spreadsheeting 3
The basics of spreadsheeting 4

%d bloggers like this: