21 05 2015
This Blog was initially called Excel 2007 with ExcelMaster. Time has passed now and we’ve got Excel 2010, Excel 2011 for the Mac and now Excel 2013
Anyway, this Blog contains an ever growing set of detailed pages on How to … do things and pages of simple but very useful Tips.
Books One, Two and Three of my series of three books, Excel Solutions for Accountants, are now online here:
8th October 2015: almost two years ago I wrote this page, Reverse Pivot Magic, well today I am sharing with you the updated version by using the Power Query utility to do the same thing. I am using Excel 2013 to do this. It’s magic; but if you don’t have the power query utility on your machine the reverse pivot magic still works! Pivot Table … how to menu although really it does not below there!4th October 2015: MUST WATCH video on using Power Map in Excel 2013. It’s so easy. How to menu … Other
26th September 2015: Just finished a really successful week in Accra, Ghana: Certified International Financial Reporting Standards. However, the subjet of this post is to tell you about a new technique: getting data from a graph when all you’ve got is an image of the graph. Short video (soory for the poosr sound initially) and an Excel file to download. This is a winner! How to Menu … Others
6th September 2015: WELCOME TO SINGAPORE! I ARRIVED IN SINGAPORE THIS AFTERNOON AND WILL BE HERE ALL WEEK PRESENTING A SEMINAR ON JV ACCOUNTING FOR OIL & GAS COMPANIES
1 Here is a post that should be a page. Never mind, here is an oil tank problem that you can find by clicking here. 2 If you like a challenge, sort this out. What started out as a simple check digit calculation problem became a SUMPRODUCT nightmare. How to … menu Others. See my various strategies to get a problem resolved. They work, SUMPRODUCT doesn’t … I found my error but left it in the downloadable spreadsheet for your to find!
3rd September 2015: I wrote a page for this blog a couple of weeks ago and then lost it … I have no idea how I lost it but I did. here’s a page on templates to help you along, though. How to … Others menu.
7th August 2015: I have given up on trying to get the Tips Others menu to work and by the time you are able to read this and act on it I should have put all of the missing files on the How to Other menu. I hae no ideea what goes wrong but I do everything as normal yet they don’t appear. I did it and checked: there are FOUR new pages at the top of the How to Other menu now … I won’t add anything else to the tips menu again unless I can get it to work properly.
This is my news this week: a delegate this week suggested that since he’d never heard of Sparklines they must be rubbish! I have prepared a page ilustrating a few of the things that Sparklines can do that are a bit different from what I hav written about before: a Sparkline Histogram for example! It’s not on this blog though, it’s on my newly revamped web site at this address: http://www.duncanwil.co.uk/sparklines.html. In fact, it’s the first new technical page on the new version of the site. Apart from the home page, it’s the only page!! I won’t abandon this blog and I won’t put things on the site that aren’t here … I am not sure what the role of the site is yet but I’ve owned that domain since 2001 and I just renewed it for three years so it’s staying put!
29th July 2015: Where does time go? It’s a month since I was last here!! Shocker. Take a look at up arrow red? though. It’s a winner!! Tips menu, other, this time!
28th June 2015: Click this link to go to a tip on the DGET function. The DGET function could well make your VLOOKUP life a lot easier then ever: http://excelmaster.co/dget/
23rd June 2015: Wow! a month away. Time flies!! Here is a fascinating page, of sorts! Following the birth of my daughter I plotted all of the foetal data that I had and found that she developed perfectly normally. However, I found the percentiles generated by the scanning software in the doctor’s scanner might be suffering from badly programmed percetiles. Take a look. Excel How to menu, other! I give you a PDF file and an Excel file this time: nancy_data
20th May 2015: If you want to see how to use learning curves in an interesting way, you will like my new analysis, Life Expectancy … lots of data, combo boxes and learning curves. In the How to … Others menu.
10th May 2015: two files at once: Benford’s Law then Simulation of Coins and Loaded Dice. Really interesting Excel files on which these two summary pages are based. Both pages are in the How to menu and are well worth a read!
29th April 2015: Want to know how Apple makes its money? How does Apple make its money?I t’s not the be all and end all but this page will show you how to interpret regression analysis to identify things like that! Look for Apple Barrel in the Excel Tips … other menu.
19th April 2015: Numbers as Values… this can be a nightmare. I forget some things when I don’t use them very often. Today I forgot how to turn text to numbers … an old, old story. LEN() was my knight in shining armour. Mene: Excel tips … Other.
15th April 2015: Compounding … another PDF based page together with an Excel file. It speaks for itself and if you work with compound interest or would like to, you need to read it! How to … Other menu.
13th April 2015: Get External Data … a page on using that useful utility. I downloaded the tenure of English football club managers to play with this utility … PDF page and Excel file. In the How to … Others menu.
9th April, 2015: As you know now, the new book has been finished and here is a video and Excel file for you on the AGGREGATE Function. Look on the Other How to Menu …
19th February 2015: Take a look at the new page Scottish Soldier: it’s about the normal distribution curve and the chest measurements of 5,738 Scottish soldiers. Genuinely fascinating! How to Other menu …
6th February 2015: two new cases that you can read about in the How to Menu … Other. Not earth shattering in terms of difficulty but one turns about 4100 cells of data into an interactive table of just 9 cells of information. The second case turns a photo of part of a newspaper article into an informative table and graph.
23rd January 2015: Find and Replace Menu with Data Validation: a video, a PDF file and an Excel file … with a job for you to do. You might be surprised at what is hiding in the Find and Replace menu in Excel: take a look. Excel How to … Menu … Other … AND there’s the page on Ctrl+Enter with Correlation that is a real winner: again complete with video and Excel file to download! Excel How to … Menu … Other …
7th January 2015 UPDATE … I have sorted out my video hosting now … take a look at my very brief introduction to Excel Tables
. This is a GIF version without sound
19th December 2014 I have just prepared a video to answer a question I was asked … are Pivot Tables easy to create and is it easy to drill down into the detail of a Pivot Table? The answer is here, in this excellent video! Easy PT
16th December 2014: my new page Going on a Date? Ask for the Number! is for anyone who uses regression analysis on time series data. It relates to the use and understanding of years as dates … read on: tips … other Menu. There is something wrong with the menus at the moment so the URL for this page is … here …
28th November 2014: Here’s an oddity, a pivot table with no data in it and yet it tells a terrific story. How is it done? Take a look in Excel Tips … Pivot Table Tips.
29th October 2014: something very technical this time. Yield curve analysis using NSS and Regression Analysis. Not for the faint of heart. Look on the menu How to … others … or the floating menu item!
27th October, 2014: after a major MacBook Pro catastrophe, I am back. My MacBook died and it has taken me a lot to get things back together again. I didn’t lose anything except time and energy as I rebuilt my folders and files. Anyway, here I am with a new file, on ratio analysis this time. One good thing about rebuilding one’s virtual world is that one rediscovers things that had long been buried and forgotten. Such as my email exchanges with Kenneth from Malta … this page comes from 2004 but deserves to be resurrected, Take a look at the Tips … Other menu (well, there is something wrong with WordPress menus so it is free floating at the moment :() for some ideas on how to calculate financial ratios on a monthly rather than on a yearly basis.
THIS IS TECHNICAL MATHEMATICS … solving simultaneous equations using Excel … I know maths is a challenge for many but here is the light being shed on simultaneous equations … with some neat and nifty functions, formulas, array functions and SOLVER all thrown in! Menu = tips … other … or possibly floating, not sure yet!
25th September 2014: I know, it’s been a while. Are you using SUMIFS yet? Thought not! Take a look at this new article, SUMIFS … from hours to seconds where you will see that SUMIFS has a LOT to offer you. Excel Tips … Others … menu.
4th August 2014: have you ever needed to find the area and/or volume of an unusually shaped structure? Good, then this might be for you. Take a look at the menu How to … Other to see how I set about finding the volume of water in my new outdoor aquarium: Volume of an Aqurium!
28th July 2014: I have been demonstrating this file for years but now you can see it here … How to Create a Trial Balance from a Pivot Table. Look in the How to … Menu under Pivot Tables.
27th July 2014: two UPDATES. Look at these two pages for updates … http://excelmaster.co/sparklines-its-all-a-matter-of-scale/ and http://excelmaster.co/excel-slicer/
26th July 2014: Take a look at the menu Excel How to … Charts for an example of a 2-D Stacked Column Chart.
3rd July 2014: Be careful when you copy and then paste a formula or function into Excel. Read the page Copy Not in the Excel Tips … Others … in the menu to see what I mean.
Just follow the menus above and you will find a richness of content.
Here are the links to my three new books.
The Excel Project: Kindle
The Excel Project: paperback
Excel Work Book: kindle
Excel Work Book: paperback version to follow … this is my latest book and it’s an introductory guide to Excel spreadsheeting for beginners. It’s a short book at just 51 pages but beginners will find it a vital addition to their Excel bookshelf!
Case Studies on mSMEs: kindle
Case Studies on mSMEs: paperback
If you’d like my help and guidance on something, just ask and I’ll do what I can.
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
06 09 2015
Last night I was finishing off some work when I came across a problem on a help site: the problem had been posted in December 2014 and hadn’t been answered. I realise that 9 months is a long time but I wanted to understand the problem and see if I could provide a solution: I did both!
One of the difficulties with the problem as stated was that the questioner asked for a nested IF solution, He insisted. Other people tried to help him and he duly provided a mock up of his worksheet for us all. Again he talked about a nested IF statement solution and even provided one … that he freely admitted didn’t work.
The problem is this:
- there are two oil storage tanks and oil is first measured by means of a dip stick which is then used to estimate the amount of oil in the tanks
- the oil is stored for days at a time and each day new oil is added
- every now and again, though, the tanks are drained
He said that by taking the differences between opening and closing stocks for each day he can estimate the amount of oil added; and by adding together the results for the two tanks he gets overall additions for a day.
Then he says the problem comes when the tanks are drained … his calculation shows a negative result for dranage day!
I translated his graphic into a worksheet and here it is.
The question is: can you solve his problem?
This table starts in B7 and in L11 there is a formula: =E11-E10+H11-H10
That formula is then filled down to the end of the table, day 31 in cell L41
You can see the problem clearly in the second part of the table, day 27 , where they have drained the first tank overnight:
That’s it: I solved this problem … can you? Let me know and I will share my solution. If you ask, you must tell me what you did to try to solve it. No try, no get! ;)
19 07 2015
06 04 2015
That is the title of my new book on Excel and here is the Introduction to the three volume set: a total of about 200 pages.
This book will be published by www.bookboon.com early in June 2015 and of course this introduction is subject to change between now and then.
This is the first in a series of three books with the title of Excel Solutions for Accountants.
The essence of the book is that we have chosen a series of topics that we believe are of direct interest and relevance to accountants: we know that from the work we do every day. That is not to say that everything an accountant ever needs or does is included in this book; rather it’s a general book aimed at the accountant who knows that Excel can be made to do a lot more than it does but he just doesn’t know what that might be.
We have taken a very hands on approach with this book and for everything we talk about there is something for you to do: there is a spreadsheet for you to work with, too, so that you can always check your work and your accuracy at every stage.
One of our over riding ambitions was to make this book both as direct and as easy to use as possible. You will not find massive files with hideously complex formulas in them, each of which might take you an hour or two just to begin to unravel. What we have done is to give you straightforward examples with non complex lists and databases so that you come to learn the techniques and functions rather than worrying about the database.
Part One of the book comprises the following
- Accountant Specific 1
- Excel Tables
- Ratio Analysis 1
- Graphs 1
- Pivot Tables 1
You can see immediately from the titles of these chapters that the accountant is the target here!
Secondly you should see that four of the chapters have the number 1 after them: that tells you that in books 2 and/or 3 there is another chapter or chapters on the same topic. Take a look at the contents of books 2 and 3:
|Part Two||Part Three|
|Accountant Specific 2||Accountant Specific 3|
|Compound Interest and Discounting: the time value of money||Dashboarding|
|Data Validation and Form Controls||Pivot Tables 3|
|Ratio Analysis 2||Forecasting|
|Pivot Tables 2||AGGREGATE, OFFSET and SOLVER|
More than one way to skin a cat! We present one or more solutions to the problems we present in this book and from time to time we say: Excel provides more than one way to solve this problem. If you already know a better way to solve a problem than we are presenting, stay with it. Even if you think our method is better but are happy with your own method, stick with it if it doesn’t mean you are wasting time or being inefficient. Don’t be stubborn though: like the cost accountant who saw our solution to his problem but preferred not to follow our advice: that meant he preferred to wait 20 minutes every morning for his main Excel file to open and then wait a further 45 seconds or so every time he pressed the Enter key. Our solution meant no waiting time at all as we replaced his 30,000 volatile function workbook with a Pivot Table based solution that provided almost instant responses.
Templates: we have tried at every stage of every chapter of this book to provide templates for you to work with and/or create. After all, what’s the point of programming a spreadsheet over and over again when you shouldn’t have to? Excel deals with some of the most predictable things you can do and templates are consistent with that. Therefore, take each of our examples either as a template or as your template in the making. We stress PPP too: paper, pencil, plan. The PPP approach means, don’t just dive in to a spreadsheet problem, take you time and plan it out on paper first. Then develop your solution. Then derive your template if appropriate.
A total of 18 chapters and by the end of all three books we believe you can easily call yourself an intermediate user of Excel. We also feel that you will have unlocked so much potential in Excel that you will want more and more from it.
This book has been written with Excel 2013 for Windows: that will mean that some of the things we have done will be a little bit different sometimes from what any other version of Excel might do. We have kept such differences to a minimum, however. Nevertheless, we would encourage you to upgrade to 2013 soon anyway.
Other sources of help for Excel: there are many sources of help other than Excel itself. Here are just a few examples of where to get help: just search for these online to get there!
- excelmaster.co: my own Excel Blog
- Excel-G: online/email based discussion list for general level questions on Excel. There are about 1,000 members of this group and they are friendly and really helpful people. Please note, this is a general level list and anything too advanced should be directed at …
- Excel-L: online/email based discussion list for Excel Developers. This is the more advanced list and seems to concentrate mainly on VBA problems and solutions.
- OzGrid: this is a free and commercial site offering some brilliant resources.
- chandoo.org: this is another really useful site whose founder, Chandoo, has the objective of making you awesome in Excel! A lot of the materials on this site are free but some of them are commercial. Chandoo also offers online and offline courses.
- Jon Peltier: Jon seems to be the world’s foremost authority on graphs and charts. Again, this is a combined free and commercial site but there are many wonderful free resources on there.
- Mr Excel: Bill Jelen is a prolific Excel materials developer and explanator! Bill provides a lot of free and highly commendable materials, again both free and commercial.
- Charley Kyd: Charley has specialised in the past in dashboarding and whilst he still does that, he offers a lot of free Excel based materials too. Well worth searching out.
- John Walkenbach: John has been around for ages and he writes the Excel Bibles, among other Excel based titles. John is always comprehensive and has a lot of useful things to offer
- YouTube: there are many thousands of videos on YouTube now and more appear every day. Some of them are really very good and others will leave you wondering what on earth they were trying to say. It’s pot luck really but once you find a good YouTube provider, stick with them!
There are many more people/organisations/lists that can help you so these are just a few of the ones we recommend.
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
- Data Validation
- Paste Special Multiply
Here’s an example of my output:
This video summarises the case:
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..
An introduction to measures of central tendency: mean, median and mode. We use Excel to discuss this topic and there are three exercises in this presentation. http://ift.tt/1xS5gnV