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 SELLING REALLY WELL:
- Book One http://bookboon.com/en/excel-solutions-for-accountants-book-1-ebook
- Book Two http://bookboon.com/en/excel-solutions-for-accountants-book-2-ebook
- Book Three http://bookboon.com/en/excel-solutions-for-accountants-book-3-ebook
2nd November 2015: a new video and a new Excel file for you. This time a detailed review of a Monte Carlo Simulation: throwing three dice this time. I have discussed this topic before, Dice and Coins, but this one is presented in a self contained video that I have hosted on YouTube this time. Excel How to Charting menu.
30th October 2015: one and two way data tables, NPV Profiles? They are ALL to be found in my new page: NPV Profile, How to menu … Others. I don’t know why I didn’t think of this before!
23rd October 2015: Ever heard of Bump Charts? Neither had I until last night: here’s one now complete with my magic touch. How To Menu … Charts to see a Basic Bump Chart and a Highlighted Bump Chart.
17th October 2015: Did you know that you need a combined Candlestick and Volume Graph … good! Here it is, just for you: Menu How to … Charting …
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!
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
01 11 2015
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