- 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.**

**22nd June 2018**: the leap year effect came as a bit of a surprise to me but it was easy to spot and it’s easy to deal with. Take a look at this page, that contains a video and an Excel file to download to see what I am talking about.

**13th June 2018**: You want to split a column but still have the data in your new columns able to talk to the input columns? Here are two ways of doing that: of the two solutions, the one that uses Power Query is by far the better but the other solution works pretty well too. Here is the page to go to.

**1st June 2018**: Two new pages today: talk to me and a challenge for you: try this case

**14th April 2018**: Did you know you can do all of your regression work using SOLVER? You are in for a treat then because it is possible. There are a couple of warnings to take into account, of course; but in principle, it works. Go here to read the page and download my Excel file too.

**6th April 2018**: New tax year, new section! I have decided to create a new section called Power BI and I have already prepared and uploaded my first article to it. Quick Insights is coming to Excel 2019 but it is ALREADY built into Power BI. Read all about it here, in the Power BI section

**23rd March: 2018****: **There is a question on quora.com where someone asks, *In Excel, how do I add the numeric vale of one cell to another cell containing multiple texts?* Three people say it cannot be done. I have answered the question and will post my answer here soon. Try it yourself and tell me what you think!

**22nd March 2018**: I just read a solution to a problem that I think gives a ludicrous answer: {=INDEX($C$2:$C$11,SMALL(IF(($B$15=$B$2:$B$11)*($A$2:$A$11<=$B$14)*($A$2:$A$11>=$B$13),MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)),””),ROW(A1)))}. Take a look here to see my two suggestions for a MUCH easier way of solving the problem they discuss.

**28th February 2018**: Do you ever filter your Pivot Tables to show the Top 10 (or even Bottom 10) iteems? Does that filter disappear when you use a slicer to take another view of your table? A delegate showed me her problem with this and the video on this page shows the problem and the solution: watch the video

**23rd February 2018**: I think you know that I am using Power BI for the Desktop to a limited extent. Here is the link to one of my Power BI reports … I am more interested in the content than the way they look so forgive my complete lack of artistic ability: here is the link to https://app.powerbi.com/view?r=eyJrIjoiZjhhOGFkMTYtMzA1Ny00NTRhLThhY2ItMGM1YWYyMDBlNWQ4IiwidCI6IjZjNGIzMDYxLTRiNmItNGUwNy05Y2JmLTYwMjE2YTQ5YWNmMCIsImMiOjh9” target=”_blank” rel=”noopener”>take a look … by the way the data are entirely artificial

**23rd February 2018**: The DATEDIF() function is very unusual: it is unlisted in the list of all built in functions and it is largely undocumented. However, take a look at my DATEDIF() page to see how useful it is i finding ages from dates without the pain of other methods.

**31st January 2018**: Microsoft Excel has just been updated and they added some really nice things to Get & Transform (Power Query). I have created four videos and an Excel file for you and here they are. will discuss them in detail when I update this page and blog but in the meantime, consider this:

As a taster … from another of my Get & Transform files, I entered this in a worksheet:

And by using G&T, I got all of this … and I could have had even more, by including a Pivot Chart or two, for example:

I went from a city name with a value for a city to a table that included the city, the country, the value, class intervals, comment on positive, negative or zero and then I used that to create three pivot tables … could have been more.

**NOTE**: not a VLOOKUP in sight, no INDEX and MATCH combination to be seen. No jiggery pokery of any kind. If you’re not excited by this, you never will be an Excel maestro!

**12th January 2018**: Happy new year first of all. Look at this page on Nobel Prizes … as connected to my home town of Todmorden in West Yorkshire, England. It’s fascinating and there is a PDF and an Excel file to download. My best friend from childhood was born on 12th January and he died as a result of an almost freak road traffic accident a few years ago, so hail John Sutcliffe.

**29th December 2017**: Another page on Benford’s Law as I am discussing the law with an email friend. This time: its it possible to fool Benford’s Law? Go here to find out or the Other tips menu!

**27th December 2017**: for Excel fans as well as for football fans and followers of the English Premier League (EPL). I have created an alternative league table that adjusts points for squad values, foreigners per squad and fair play league positions. This file is rich in Excel functionality so look at it from a football point of view AND an Excel point of view. The Excel file is free to download. The page is here.

**17th December 2017: UPDATE **I have updated my Sunday Times Rich List Benford’s Law Analysis page because I have added to the data I have analysed there. Take a look!

**6th December 2017**: STOP USING EXCEL … boring, heard it all before! What was it you were trying to sell me? Tatsuo Horiuchi Excel artist. Two cracking discussions in one short page. Excel Tips … Other menu!

**21st October 2017**: There is a simple page, BP Graphs, in the charting tips menu that asks you to express a preference for one kind of graph over another and for you to suggest how I created those graphs! Take a look!

**14th October 2017**: I have updated the DGET page following a couple of emails trying to tell me how good it is to use INDEX-MATCH …

**6th October 2017**: A video … with music … **Contingency Tables in Excel**. Excel file to come in a week or so. Anyway, take a look here Menu: Excel tips … other tips …

**18th September 2017**: I got involved in writing several articles on http://www.quora.com over the weekend and here they are: Pie Chart sorted: Excel Tips, Charting Tips menu), log linear regression analysis (Excel How to, Excel Functions menu) and AVERAGEIF question that became a Get & Transform (Power Query) question (Excel How to, Excel Functions menu).

**28th August 2017**: I have always called in Power Query up to now but it’s really called Get & Transform now. Take a look at this fantastic page I have just created in which I show you how to take a list with 39,000 rows that need to be split out column by column without using any VLOOKUP or INDEX and MATCH … purely using Get & Transform. Menu Excel How to …

**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:

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

## 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

## Two New Cases

### 06 02 2015

Here are two new Excel based cases I have just prepared that I know some of you will appreciate.

**UK Petrol Prices**

I downloaded a file of all retail petrol and diesel prices for the UK from 2003 to 2014. The task is to prepare an interactive table so that the user just enters a date in one cell and the table then shows the

- retail pump price per litre
- duty per litre
- VAT percentage
- calculation of the cost of the petrol net of duty and VAT

This case involves the use of a variety of techniques including

- VLOOKUP()
- Data Validation
- IFERROR()
- Paste Special Multiply

Here’s an example of my output:

This video summarises the case:

**Sleep Requirements.**

In today’s *Borneo Bulletin* newspaper there is an article on the sleep requirements of people of all ages. They give the requirements such as, a new born baby needs from 14 to 17 hours a day … someone over 65 years of age needs from 7 to 8 hours sleep a day.

The task is to turn a photo of the data into a table and a chart that communicates as effectively as possible: I prepared the following:

Please feel free to write to me at any time to ask for the spreadsheets that accompany these cases. At the moment they are live cases and I won’t release them generally until the course has finished in a week’s time..

Duncan Williamson

## AGGREGATE Function Templates

### 11 11 2013

I know I learned this function a while ago but then I forgot about it. The aggregate function was newly created for Excel 2010 and it provides us with some interesting functionality: overcoming shortfalls in the performance of such functions as AVERAGE, MIN, MAX, LARGE, SMALL and a few more … 19 of them altogether. The trouble is, good as it is, it is not that simple to apply. What I have done then is to create templates that will help you to use the aggregate function in one or both of the formats in which it might be used.

From the Excel Help File:

*Returns an aggregate in a list or database. The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.*

*Syntax*

*Reference form*

*AGGREGATE(function_num, options, ref1, [ref2], …)*

*Array form*

*AGGREGATE(function_num, options, array, [k])*

*The AGGREGATE function syntax has the following arguments (argument:*

*Function Number*

*Required. A number from 1 to 19 that specifies which function to use*

*Options*

*Required. A numerical value that determines which values to ignore in the evaluation range for the function*

*Ref 1*

*Required. The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value*

*Ref 2*

*Optional. Numeric arguments 2 to 253 for which you want the aggregate value*

*For functions that take an array, ref1 is an array, an array formula, or a reference to a range of cells for which you want the aggregate value. Ref2 is a second argument that is required for certain functions. The following functions require a ref2 argument:*

*LARGE(array,k)**SMALL(array,k)**PERCENTILE.INC(array,k)**QUARTILE.INC(array,quart)**PERCENTILE.EXC(array,k)**QUARTILE.EXC(array,quart)*

*The AGGREGATE function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using option 1, such as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value. But, hiding a row in vertical range does affect the aggregate.*

My templates will tell you if you are trying to use the wrong format of aggregate and has then been set up to ensure you don’t make any other mistakes when using it. This includes providing combobox guidance for choosing which functions and options you need to use.

The Excel file is available here: aggregate_function … just click!

All feedback is warmly received as are suggestions for improvement.

Duncan Williamson

## Wildcards are Great … Until THIS Happens!

### 11 11 2013

**Wildcards**

As I am working away this week, I can be found looking at, opening up, working on a spreadsheet or two. Last night I came across yet another excuse to build an Excel data set. I found the TopTrack 250 and just had to have it: all seven years’ worth of day for the top 250 companies according to the Top Track definition. Look here for the 2013 data set.

So I duly copied and pasted the data for 2013 into a spreadsheet and edited it. Having seen that I liked what I had done I then copied over the other six years’ worth of data from Top Track. Because the data copied over in a very standard and predictable way, I was able to select all six sheets of the additional data and work on them all concurrently: moving data from one row to another, setting up columns in a better way, lining up the data to make it consistent with what I had already done with the 2013 set. Easy and not too time consuming.

But now the wildcards. On the web site that the data came from they had symbols such as asterisks that pointed to a note that said … average, estimate … or something like that. I needed to get rid of them all! As it was late and I wasn’t looking carefully, I carried out an edit and replace of * and having pressed Replace All, found that ALL of my data disappeared … it was doing what a wildcard must do … in this case, any character was being replaced by nothing! A very hasty Ctrl+Z put everything back to the way it was but I still had to get rid of these symbols.

What to do? Delete every symbol manually? No, there were too many of them. Given that the symbol was in the same place in the cell it was in, at the beginning, I ended up with a formula solution that I entered in a new blank column that I would eventually use to replace the current data:

=IF(LEFT(D6,1)=”*”,RIGHT(D6,LEN(D6)-1),D6)

Why D6? That’s where the first of my data points is in each of the tables I set up. I then copied the formula across to the next cell on the right and then down to the bottom of the table … it worked a treat.

I remembered that I should have added that there were other symbols in the data in addition to the * and I amended my formula to cope with the ‡ symbol:

=IF(OR(LEFT(D6,1)=”*”,LEFT(D6,1)=”‡”),RIGHT(D6,LEN(D6)-1),D6)

Now I had to copy and paste my edited values as text rather than leaving them in formula format, otherwise I have to leave the original data in the file AND have the edited version. So I just copied and pasted the edited values over the original data and my data were now clean and tidy … but read on!

**Numbers as Text**

One problem remained: having replaced these symbols, the result was numbers as text rather than number as number. In this case I did this:

- enter 1 in any available cell
- copy that cell
- select the entire range of numbers … all of them so you miss nothing and don’t waste your time selecting just the affected ones if your data set is large like mine
- paste special, multiply
- ta daa! your numbers as text have now become numbers as numbers
- Delete that cell with 1 in it

There you are wildcards and paste special to set up a nice, clean table of data copied from the web.

Duncan Williamson