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.
22nd March 2020: Along with many other Excel bloggers, I am spending a lot of time on making Excel the go to software to gather, store and communicate the covid-19 data. I have done something that no one else is doing, again. I am demonstrating how to turn text into tables, pivot tables and pivot charts. You will hardly believe it’s possible but you can see what I did and then download my Excel file and do it yourself. This will take you time to learn and set up but it takes just a minute a day to update. Here is my page on Data From Text
17th March 2020: If you are Irish, have as happy a St Paddy’s Day as you can! Otherwise, two pages on the Append function in Power Query. Brilliant pages for everyone with page two being relatively advanced.
16th March 2020: Three new pages for you: How MIRR really works, Z Charts and COVID-19 and Long mathematical equations in Excel
7th March: This is a treat for anyone who is using Power Query and needs some relatively advanced insights into using them to create Pivot Tables and Pivot Charts, when using Connection Only Queries. There is neither video nor Excel file but the page is so good and has such good graphics that the keen reader will not mind that they are not here! Go to this page … Pivot Table for a Connection Only Query?
6th March: [Data.Format.Error]We couldn’t convert to number … If you ever receive this error message when creating a Query in Excel or Power BI, you might find there is not enough help on the internet to help you … until now! Read this page for the solution.
5th March 2020: this is a moderately interesting problem you might come across. See the page entitled Percentages Disappeared from Power Query to see what can happen when Excel chooses the wrong Data Type for you. It also shows you how to correct that mistake.
3rd March 2020: this is epic! Sorting a Pivot Table by TWO columns. It cannot be done … until you find Helgi and http://www.stackoverflow.com and she helps you to achieve the impossible. Read on!
7th February 2020: Here is a fascinating article: fascinating for two reasons. Reason one is that I have used some of the latest new Excel functions, RANDARRAY() AND XLOOKUP(). Reason two is that I caught someone out in a porky pie by means of programming a spradsheet. Go and have a look at Infinite Monkey or Porky Pie. You will also learn about the infinite monkey theorem while you are there!
23rd January 2020: I have previously written three pages on Sparklines and here is a fourth: it’s fascinating and so easy to do. However, if Sparklines are new to you, do note the links to the other three pages at the bottom of the page of this latest post. Have fun with Sparklines! Sparklines with the Camera Tool.
16th January 2020: I started this blog in 2011 and since then, this is where you all come from … a map showing where my blog is viewed from and I have highlighted the top ten countries. Looks like I cover the world!
15th January 2020: You know that I am a regular contributor to www.quora.com. A couple of weeks ago, I was invited to open and use what they call a Space: it’s essentially a blog inside Quora. You will see that I put things there that could easily go here. So, do yourself a favour by following this link and signing up with me there as well as here … meaning you will miss nothing!
5th January 2020: Happy new year to everyone, first of all! Here is something you might not have seen yet. If you take a look at the Review Ribbon in your version of Excel you might see something new: in the Proofing section on the left of the ribbon there is something called Workbook Statistics … click on it and see what it tells you about the file you are currently in. Is it useful? Will you use it? Who will use it? Also today, I have added a new page, on the very important topic of Accessibility in Excel: that page is here
12th December 2019: A video … a video and a file to download. How to use a checklist to solve the problem of dirty data. It’s a smaller problem than I have dealt with before, so we did not need to use Power Query. Still, solving these problems is a skills that we all need. The file is here where the video and Excel file are waiting for you!
6th December 2019: Today’s contribution to this blog is a page I have written on the XOR function. This is something of an odd function since I don’t think I have seen it in action. Never mind that, it is not difficult to use and understand and there are clearly many uses for it. As a bonus, I have included the ISODD() and ISEVEN() functions in this article and, hot on the heels of yeseterday’s post on IF, IFS and CHOOSE, this is a totally topical page! You can find the page here and there is an Excel file to download, too.
5th December 2019: If you have ever wondered about the use of Nested IF, IFS and CHOOSE, look no further because I just created a page for you. Two of the examples are easy and the third example is much more of a challenge. Go here to see the page https://excelmaster.co/nested-if-ifs-and-choose/
5th November 2019 For the benefit of all Brits … have a happy and safe Bonfire Night. Secondly, look out for my NEW page, Quora Questions. Just scroll down from here and you will see the heading, just click it to open and read what I am doing. Simple concept with some very useful ideas.
26th September 2019: I have created this page to show how I went from an image on a web page, via OCR software, to a relatively sophiticated Excel file creation that uses Data Types … Stocks, Excel Tables, Pivot Tables, Conditional Formatting and more.
13th September 2019: a fantastic page on corporate bonds and their analysis. How to program a table using dates, the IF(), AND(), MONTH(), YEAR() functions and arithmetical calculations. If you find difficulty using dates, consider this page.
11th September 2019: a friendly introduction to the setting up and use of a Training/Testing Model in Excel. It is an introduction but it is a good one. The page does not, however, carry out any detailed analysis of the validity of the model you will read about. The data are real and you can replicate what I have done very easily. The page is here.
22nd August 2019: As you all know, this site is free of charge and always will be. Every now and again, I announce something like the publication of one of my books. Sometimes my books are not free of charge because they are professionally published. Whilst that is true of my latest book, it IS free of charge, at least for a while. The title of the book is EXCEL POWER QUERY, AN INTRODUCTION: BUSINESS INTELLIGENCE PART I.Click on the title to go to the site where you can download it free of charge.
1st August 2019: I have already created two pages (Flash Fill, Flash Fill and Quick Analysis) on Flash Fill, both for Excel and for Power Query and here is another one. In this case, I am introducing even more ideas on how Flash Fill (FF) can be used very effectively. Firstly, by using FF to extract, combine, extract and combine; and secondly, I watched a video of a highly respectable Excel practitioner who tried to sell us the idea that there are times when FF is just not good enough and neither is Power Query: in that case, he said, use a formula … he demonstrated a relatively complex formula. The formula worked but I am showing here that that presenter is wrong and that FF will do exactly what we want without programming anything.
14th July 2019: A different approach but the same quality. I have just posted my latest two videos to YouTube. Video 1 is about Flash Fill and Quick Analysis in Excel. Video 2 is about Flash Fill in Power Query and Quick Analysis in Excel. The aim is to illustrate how it is possible, even with dirty data, to create a basic dashboard within 10 minutes! Video 1 … Video 2
19th June 2019: BOOKS … Since I was last here, I have completed writing two books on Excel and I will announce publication details here when I have them. The first book is the fourth in my Excel Solutions for Accountants series and the second book is the first in the series on Power Query, Power Pivot, Power BI. These books are the only things I talk about here that are not free of charge.
CASES … in addition to books, I create case studies and for courses I am running at the moment, I am sharing four such cases with you, in a PDF file. There are two cases on financial analysis, one case on budgeting for buying and running a private jet and a case on exchange rates. Look at the page to see the outline of each case and what you need to know about Excel to work through them.
5th May 2019: If you are serious about learning VLOOKUP in the Power Query and Power Pivot environments, look no further. I have created two videos, one of PQ and one for PP to help you. Just go to this page and take a look at my detailed explanations.
20th March 2019: One file, two videos. This page outlines what I did after downloading some World Health Organisation data on DTP3 vaccinations (Diphtheria, Tetanus and Pertussis), covering the period 1980 to 2017. From a health point of view, what you see here is fantastic. From an Excel point of view, what you see is intermediate level but there are enough features illustrated to make it well worth your while to take a look at it! The page is here
6th March 2019: This page ends with an Excel file but it is really a discussion of how I created some Power BI files that I then used to create the Excel files by copying M Language code from one to another. What I describe here is magical and I have used the departures and arrivals information for both of Bangkok’s two international airports as the basis of my work. The page you want is here and there are two bare bones Excel files to download … Word Press will not allow me to share Power BI files, I am sorry to say.
15th February 2019: Let’s watch a video! I have created a video showing you how to use Data Types Stocks to pull in share prices, Beta values, market capitalisations of potentially thousands of different companies from around the world. There is a video to watch and a spreadsheet to download. The page is here: Data Types … Stocks
7th February 2019: I know you have read my pages on Sparklines and therefore you know how clever they can be and how you can format the cells they are in, you can add text/formulas to the cell they are in. Well, now, you are about to learn to use Conditional Formatting Icons in them. As far as I know, you can find this technique nowhere else on the web! Here is the link to the page Sparklines with ICONS
9th January 2019: This is a fabulous page … not because I am so brilliant but because I have just learned how to combine array constants with a VLOOKUP function. It has been around for years but it just filtered down to me. You can thank me later! Here is the page you need: https://excelmaster.co/vlookup-with-array-constants/
8th January 2019: Happy New Year Everyone. Secondly, the purpose of this update is to share with you my solution to creating a histogram for any one of àbout 90 possible line items in a ratio analysis worksheet. The data relate to Netflix and comprise a ten year view of the ratios that come from the morningstar.com web site. The file also includes my Positive Skewness Test that I have recently started to program and use. There is a file to download too and if you find it useful, please tell me how you are using it. the file is here: https://excelmaster.co/one-histogram-90-choices/
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
Quora Questions
05 11 2019
I write answers on various Excel topics at http://www.quora.com and this page provides links to some of my answers. They are not normally massively complex questions but I answer the ones that I think might be interesting to my visitors and readers. The pages are probably in reverse order: lastest pages first; but that should not matter!
Update 5th December 2019
How do I convert F to C in Excel?
How can you take a profit and loss statement in CSV format and easily load it into PowerBi?
Why does Microsoft Office make it so difficult to guarantee your work is saved?
How do I extract consecutive capitalized words from a body of text in Microsoft Excel?
Why doesn’t Excel recognise comma separated values as separate and filterable?
How do you graph 10,000 data in an Excel chart?
What is paste special in Excel?
What is the x-intercept and y-intercept of two lines x-3y=7 and 2x-5y=4?
https://www.quora.com/How-do-I-make-a-line-graph-in-PowerPoint/answer/Duncan-Williamson-1
How can one have two things on the same axis in Excel?
This page is on my facebook page and not Quora! The page shows a Power BI image of the flight path of LY1747 of El Al … it is unusual, to say the least. https://www.facebook.com/william.williamson.144/videos/1600460606762146/
What are some real life examples and applications of multiple regression?
Which duplicate does Excel remove?
How do I paste a chart from one Excel to a blank Excel?
What are the most unused excel formulas?
Journalizing records data chronologically in accounting. What is the explanation of it?
FUNCTIONS … top 100
22 12 2018
I just came across a couple of pages on hbr.org that you need to know about and forgive me if these are behind a paywall: I subscribe to the HBR so I went straight to every page.
Firstly, 10 Excel Functions Everyone Should Know by Adam Lacey and Deborah Ashby. I prefer DGET to their INDEX-MATCH function but otherwise, this is a good start to learning the top 10. Top 10 Excel Functions
Then there is a REALLY useful PDF file The Definitive 100 Most Useful Excel Tips at https://excelwithbusiness.com/blog/definitive-100-most-useful-excel-tips/
Finally and following on from the 100 must useful Excel tips, Marc Zao-Zanders gives us this, A 2×2 Matrix to Help You to Prioritise the Skills to Learn Right Now, here: https://hbr.org/2017/09/a-2×2-matrix-to-help-you-prioritize-the-skills-to-learn-right-now This 2×2 matrix puts the 100 tips on a grid/graph to illustrate four quadrants:
- Decide whether you need to learn it
- Learn it as the chance arises
- Schedule a block of time for learning it
- Learn it right away
Marc says this, “You’ll find the quickest wins in the bottom-right quadrant, which we’ve labeled “Learn it right away.” In here we have time-saving shortcuts that can be applied frequently, like Ctrl-Y (redo) and F2 (edit cell) and a nice combination formula that cleanses your spreadsheet of errors (IF(ISERROR)).” Of course, IF(ISERROR) is way out of date and should be replced by IFERROR()
Duncan Williamson
22nd December 2018
Singapore Airlines Superb Result
14 08 2018
Introduction
If you have stumbled across this page hoping to find a cheap Business Class seat on Singapore Airlines (SIA), sorry! What you have arrived at is a page that explains how I found a superb result to my analysis of the last 19 years of SIA’s financial results.
The Data
As I was using Power Query to help me to analyse SIA’s financial results, I downloaded their last 19 years’ worth of Annual Reports and Accounts and from there I downloaded everything you see here:
The question is, what can I do with all of that? What I often do as part of my financial analysis is to create a Net Income model. That is, I make Net Income the dependent variable and have one or more independent variables.
The First Model
My first model comprises Y = Net Income = All Other Variables in the above table, all six of them. I used the Data Analysis ToolPak for this and here is my output:
A Correlation Matrix of all variables and I have conditionally formatted the results to highlight the extreme values and the mid range values. For example, Revenue and Fuel Costs are very highly correlated as are Passengers and Rev Pax Km (Revenue Passenger Kilometers). Rather oddly, there is virtually no correlation between Staff Costs and the Number of Employees.
The Regression output are as follows:
Let’s note that the Adjusted R Squared value is high at 0.6755, the F statistic is significant at 7.25 but of the six independent variables only Staff Costs are significant, with a t statistic of 3.3287 and a P Value of 0.0060.
I think this is a superb result: Staff Costs being the only significant variable as its coefficient shows that a unit change to Staff Costs leads to a 2.5913 million SGD increase in Net Income.
The residuals plots show that everything is probably fine although we are only dealing with 19 data points or years of data:
Conclusion
On the one hand, this model fails because only one variable is significant and using just Staff Costs to predict Net Income is not rational. This says that we need to refine or replace the model to find something better. That being true, why do I think I found something superb?
SIA is famous for its recruitment of and investment in high quality cabin crew and other members of staff. Whilst they don’t pay the highest salaries, they do reward well overall, training is high level and frequent, they use more cabin crew per flight than most, if not all, of its competitors to maximise customer satisfaction.
Out of all of that, the data contain the relationships that I just mentioned and regression analysis has brought them out! That is superb in my opinion!
I want you to replicate my work here so, whilst there is a spreadsheet to download, it only contains the data I have extracted and used. Feel free to ask for advice and guidance here but I am not providing the full spreadsheet file.
Download the file of data from here sia_data_blog
Duncan Williamson
14th August 2018
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
