Excel with Excel Master

Visitors to this Blog come from all over the world and all are equally welcome. I abhor what has happened to George Floyd and I stand in solidarity behind the movement for greater peace and equality

22nd August 2020: Dates in Excel can be a right royal PITA, can’t they. Here is a sorry tale with a happy ending. Dates downloaded that were formatted either as General or Date … both formats in the same column. Read here how to solve such a problem.

31st July 2020: Today I did something really interesting with Data Tables/Dynamic Array Functions. I was answering  question about compound interest and then decided to answer it by using a Data Table and then I used the new SEQUENCE() function, a dynamic Array Function, too. That gave me three different ways of answering the question. 

I tried an experiment and bulk copied some of my work from Quora … bad idea as they all copied over as pages and had no title … not helpful.

16th June 2020: There is no page on this blog for this, just follow this link for a fantastic Excel resource. There are links that are good for Excel and links that point out weaknesses and dangers of Excel. As far as I know, all links are free of charge and be ready, some of the links are dead now.

5th June 2020: as promised, I have uploaded some specimen materials to my course outline page for my online courses. See the link in the 30th May entry … my slides and my working Excel file. More to follow soon.

30th May 2020: I have been busy over the past month running online classes for a client and they have gone very well. The course was an Introduction to Financial Modelling and was a comprehensive guide to many of the tools available in Excel as well as reviews of modelling best practice and many other insights along the way. Here is my course outline and invitation to attend my other offerings online. I will be sharing some of my course materials here over the next few week and that will help you to make your decision to attend or miss out!

27th April 2020: the RANDARRY() is a huge leap forward for random number generation in Excel. I have created an extensive review of how to use the function here.

26th April 2020: The GESTEP function … you use it every day, right? Me neither. I had never heard of it until an hour ago, so I created an example to show what it is and how it can work. Go to my page here.

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