- 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.
Welcome to this Blog: Excel with ExcelMaster
21 04 2017
This Blog contains an ever growing set of detailed pages on How to … do things and pages of simple but very useful Tips.
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:
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
22nd March 2017: and here is a page for Power Query lovers and learners. Learn how to use a conditional column to determine labour turnover or what Chandoo calls churn! Go here!
17th February 2017: this is a biffy. Programming linear and non linear formulas using LINEST. The page for that is here and it takes concentration but is otherwise not too bad to do! Menu Excel Tips … Other Tips
3rd February 2017: A bit late but happy new year everyone. Here is a post for anyone who is new to Power Query. I came across question on Quora that is best answered by using Power Query’s merge feature. Sagar Malik answered the question and I reworked Sagar’s effort and added one change … by reading Sagar’s answer and mine you have the full monty! Thank you Sagar for your insights.
30th December 2016: Click on a slice of a pie and reveal a column chart … search the web for this and you will come across some fancy things like chart event … no need for that. Excel How to … Charting menu to see this page: https://excelmaster.co/pie-to-column-chart/
29th December 2016: an analysis of the heights and weights of English Premier League footballers. The main text is contained in a PDF file that is free to download and there is a free Excel file for you too. I put this in the Excel How to … Excel Functions menu and here is the direct link anyway: https://excelmaster.co/epl-player-heights-and-weights/
24th December 2016: How to shrink a formula by 1138 characters in one cell. I just answered a question on Quora. The person wanted to know if this could be simplified: =IF(B2=”IDR”,C2,IF(B3=”IDR”,C3,IF(B4=”IDR”,C4,IF(B5=”IDR”,C5,
IF(B6=”IDR”,C6,IF(B7=”IDR”,C7,IF(B8=”IDR”,C8,IF(B9=”IDR”,
C9,IF(B10=”IDR”,C10,IF(B11=”IDR”,C11,IF(B12=”IDR”,C12,
IF(B13=”IDR”,C13,IF(B14=”IDR”,C14,IF(B15=”IDR”,C15,
IF(B16=”IDR”,C16,IF(B17=”IDR”,C17,IF(B18=”IDR”,C18,
IF(B19=”IDR”,C19,IF(B20=”IDR”,C20,IF(B21=”IDR”,C21,IF(B22=”IDR”,C22,
IF(B23=”IDR”,C23,IF(B24=”IDR”,C24,IF(B25=”IDR”,C25,IF(B26=”IDR”,C26,
IF(B27=”IDR”,C27,IF(B28=”IDR”,C28,IF(B29=”IDR”,C29,IF(B30=”IDR”,C30,
IF(B31=”IDR”,C31,IF(B32=”IDR”,C32,IF(B33=”IDR”,C33,IF(B34=”IDR”,C34,
IF(B35=”IDR”,C35,IF(B36=”IDR”,C36,IF(B37=”IDR”,C37,IF(B38=”IDR”,C38,
IF(B39=”IDR”,C39,IF(B40=”IDR”,C40,IF(B41=”IDR”,C41,IF(B42=”IDR”,C42,
IF(B43=”IDR”,C43,IF(B44=”IDR”,C44,IF(B45=”IDR”,C45,IF(B46=”IDR”,C46,
IF(B47=”IDR”,C47,IF(B48=”IDR”,C48,IF(B49=”IDR”,C49,IF(B50=”IDR”,C50,
IF(B51=”IDR”,C51,IF(B52=”IDR”,C52,IF(B53=”IDR”,C53,IF(B54=”IDR”,C54,
IF(B55=”IDR”,C55,IF(B56=”IDR”,C56,IF(B57=”IDR”,C57,IF(B58=”IDR”,C58,
IF(B59=”IDR”,C59,IF(B60=”IDR”,C60,IF(B61=”IDR”,C61,IF(B62=”IDR”,C62,
IF(B63=”IDR”,C63,IF(B64=”IDR”,C64,IF(B65=”IDR”,C65,IF(B66=”IDR”,C66,
“None Here”))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))). I gave him this alternative: =VLOOKUP(Specific,B2:C93,2,0). From 1166 characters to 28!
19th December 2016: two pages today: sampling every nth row and reverse exponential smoothing. Two screenshot driven pages: one with an Excel file and one without.
19th November 2016: obesity is a disease and this page illustrates the extent o that disease in England.
9th November 2016: I have updated my depreciation model again. You will find the original pages in this blog under the NEW menu sub heading of Accounting. In addition, my new page is there too. In truth, it concerns he depreciation of cars in Bangkok and in Melbourne. I am changing my menu structure by adding new headings … you will see more clearly as I move things around and the menu shrinks in length.
25th October 2016: As promised last week at the end of my seminar in Manila, I have added a multiple worksheet scenario model: it seems to work quite well. Let me know if this meets your needs! The page is here! Excel How to … Other menu …
8th October, 2016: Go to the How to Menu … Others to learn about Flash Fill courtesy of some GIFs. Flash Fill should be a massive time saver for you and it could mean that you never need to sue LEFT, MID and RIGHT again.
4th September 2016: A second page on life expectancies. In this case, I don’t share the file with you, unless you ask for it; and what this page concerns is the interpretation of a correlation matrix of life expectancies by country and then by a series of potentially explanatory factors. How to … Other menu … here
19th August 2016: There is a post on Top Tips from my course this week … that mean it’s a post and not a page so it follows this section, below … scroll down to read it! There is a page on the How to Other menu called CAGR … RRI … what’s the truth? and that is aimed at finance practitioners and students.
19th June 2016: 1 How about finding the IRR of a Perpetuity? I came across a question on that on quora.com and answered it. I know I did not answer the question in the way the questioner was asking but I like my answer anyway! IRR of a Perpetuity in the How to … Other menu … here
2 Secret Graph and Graph Your Name. You can create graphs without having any data on a work sheet … did you know? That means you can put just about anything you like on a graph. It’s a bit dot matrix but it works and with your flair and imagination it could look magical! Excel How to charts menu … here
31st May 2016: You know you want to know how to prepare your own population pyramid in Excel. Well now’s your chance. This new page is on the Excel How to … Charting … … but click here to go straight there! 1st June 2016 … I have UPDATED this page and the Excel file …
25th May 2016: Do you use LINEST? Try this, then, if you want your output to have more flexibility. Combine INDEX and LINEST. See the page of that name under Excel How … Others … menu
2nd May 2016: a page about library fines … from almost 70 years ago … present values and so on. Menu … how to … other!!
11th April 2016: 1 Power BI … if you’ve never heard of it or seen it, you are in for a treat. Here is the first of what I imagine will be many pages on that topic. I give you here a basic example of how to import a CSV file into Power BI and how to analyse the data you import. It’s so simple to use and so powerful at the same time. You will find this page under the Excel How to … Other menu.
2 Here is a page for anyone who has or who is about to upgrade to Excel 2016: Forecasting in Excel 2016. You will probably e surprised by what you read as they have done something different this time. At the same time as including some new forecasting functions, they have included a utility to go with it. You will find this page under the Excel How to … Other menu.
14th March 2016: A Sparkline revelation. Go to the How to Graphs … menu to see a miraculous Sparkline. Sparklines … REALLY … truly exciting!
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
Multi worksheet scenario model
25 10 2016
Tuesday 25th October
This is on its way … as promised … I will upload it later today or early tomorrow.
Duncan Williamson
Someone asked this question in Quora and here is my answer which I think many of you will find useful:
If you use Excel on a Mac the chances are that you are not running Excel 2016 for the Mac and that your Mac does not have the ToolPak at all … I know, older versions have it and I know you can get alternatives!
In that case, I often demonstrate to Mac users how to create and automate the functions in the ToolPak: correlation matrix, regression analysis, moving averages, descriptive statistics … the others as well!
Descriptive statistics, for example, could be, for data in column A:
=AVERAGE(A:A)
=STDEV(A:A)
=KURT(A:A) …
=SKEW(A:A)
and so on.
Other answers have mentioned statistics software packages and that’s fine except they might not be free! Yes, if you are a student, your college or university is likely to have statistics software free for you to use.
How about R and R Studio, however? Open source, free, with massive amounts of support? Of course, it takes time to learn R but here is the code for some descriptive statistics using the psych package in R:
describe(order_sales_profit$Sales)
That’s it! This is what I get from my current data set, sales values: not exactly the same as the ToolPak but my point is, it is very easy to replicate. Look at the screenshot of the output from R.
By the way, as a novice or beginner level user of Excel, there is a lot to learn from manually automating what’s in the ToolPak. Moreover, if you take my next learning point, use this opportunity to set up templates for you to analyse your data sets: that means, you automate the ToolPak elements once and that is it!
Finally, many elements of the ToolPak return non volatile results which means that if you change your data, you have to run the ToolPak again. If you automate it yourself, the formulas you create will all be volatile: change the data, change the answers!
Duncan Williamson
I like such photographs
09 09 2016
I hope you can see this photo.
DW
Top Tips: rules you really should follow
20 08 2016
19th August 2016
I have just completed another very successful Financial Modelling course and as you know, at the end of such courses, I come here and offer something new: a new topic, a new file or some advice. In this case, it is advice: things that you really need to think about when you create and work on any Excel file.
- Tab/Sheet Names
- Links
- Dead Cells 125,433 rows created but only 831 needed/active … files that balloon to many Mb for no real reason
Ever seen a tab name like this: FBU or OPT? I bet you have: short and sweet and probably mnemonic so easy to read and remember. How about L_P_Obasange_receivables_dont_forget_to PRINT_it_out? You think I am joking? I am serious! Just imagine you are working on your file with the large tab name and you want to link to a cell on that tab from another one: this is what will appear in your formula, by way of an example … =IFERROR(AND(A15=45,D26=”Jack”,L_P_Obasange_receivables_dont_forget_to PRINT_it_out!BA154 …
I am sure you see the point now. Keep tab names short and simple! More than that, if you do feel the need to use tabs to give instructions, colour code them to pass such messages: there are many colours to choose from so do that. Have a table of contents too. Give everyone a chance for a simple life!
Links
If you share a file with someone, make sure any links in your file are either live or delete them. If you receive a file with links that you cannot use or update, you know how frustrating it is. Think of the user before you send linked files.
Dead Cells
It is the easiest thing in the world to create a worksheet and as you work and improve what you are doing, to delete cells and ranges. We all do that. We create new ranges too, don’t we! Check your work now and again though and if these happen, take a break and check your file:
- it takes 30 seconds 45 seconds or even longer for the file to open
- what seems like a small file in terms of content and complexity has ballooned in size to 20 or 30 or more Mb
- saving the file takes an age too
If these things happen, go to a worksheet and press Ctrl+End and see where that takes you. You work only in the range A1: CD831 but Ctrl+End has taken you to CG125433 … what? How did that happen?
Even if there are not as many as an additional 1.8 million cells but just 500,000, look in those cells for formulas that are trying to find something from somewhere that is not there … in some of these extra cells for example. Delete all of these extra cells. I did that this week: an extra 1.8 million cells in TWO separate worksheets complete with formulas. File size down from 28 Mb to 0.8 Mb, opening time just seconds, recalculation time hardy noticeable.
They were just some of things to report on from this week. Otherwise, this group of delegates really enjoyed the work and their end of course presentations were interesting and showed that significant learning had taken place!
Duncan Williamson
Business Intelligence
30 03 2016
Are you interested in using BI? Do you already use it?
Did you know that BI is free to create?
I will be adding some BI resources here this week … but there are already some here! Take a look at Power Query and Power Pivot for a start.
Back soon and if there’s something you want me to write about, let me know.
Duncan Williamson
SUMDIVIDE … sort of
08 03 2016
A delegate asked me today if there was such a function as SUMDIVIDE. There isn’t of course, but I found a way to simulate it.
What would SUMDIVIDE do? SUMDIVIDE would have array 1 divided by array 2 and the results then added together: in the same way that SUMPRODUCT multiplies and then adds.
This is how it works: imagine A1:A5 contains array 1 and B1:B5 contains array 2 then the SUMPRODUCT function to divide them will be =SUMPRODUCT(A1:A5,1/B1:B5). Simple, eh? Who’d have thought it would be so simple.
Duncan Williamson
