27 07 2014
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.
Special Mention and congratulations to Abdullah Al Yahya of SABIC who has programmed the Payback Period with a very smart formula … go and look at it in the Payback Period post: Excel How to … Other menu. Very clever!
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.
28th June 2014: It’s a simple thing but it’s not something you see every day … the Pie in a Pie Chart. Well, here it is now! Look at the Charts … How to Menu
24th June 2014: Go and see something fantastic. The Fortune 500 companies, all 84,000 data points of them, on one web page at one time. More than that, you can scroll up and down and highlight individual companies. All courtesy of fathom.info and a video I put together for you! Menu, Charts, Tips!
7th June 2014: Take a look at this new page, another graphical analysis page: Council Tax Analysis. It’s in the tips menu … charts.
6th June 2014: automated reports for management using Excel. Go to the How to Menu … Other to see the page Automatic Report in Excel
ARE YOU INTERESTED? A while ago I prepared a very comprehensive bookkeeping case study: mainly concerned with the bookkeeping cycle, loads of debits and credits, trial balance, adjustments, final accounts. I programmed a spreadsheet to cope with a two year cycle for an imaginary business. There are also very comprehensive notes to go with it. If you would like a copy of notes and spreadsheet, let me know … send me an email. This case is aimed at teachers of bookkeeping and accounting as well as students and is really level two work, not introductory.
27th April 2014: I joined a discussion on LinkedIn, Financial Modelling Group where there was a question about the Payback Period method for Capital Investment Appraisal. They asked if it’s possible to program it without VBA … well, I did this years ago but wrote this page specially for them. There is my template file to download too. Excel How to Menu, Others.
26th April 2014: Scroll down this page to see the post entitled Another Satisfied Customer (Bora Kim and boyfriend in this case) and then go to the Excel How to Menu Charting … to download and learn how to create panel charts that are controlled by a Spinner control.
11th April 2014: English Language 2 Not sure how long I will work on this aspect of spreadsheeting but in this post I highlight and demonstrate the functions and formulas I have used this time. Look at the Other Menu in Excel Tips for Learning English 2
2nd April 2014: Where does the time go? Anyway, here’s an unusual Excel opportunity that some of you might appreciate. Setting up exercises to help someone to learn English, or any other language really. Take a look at the Other Menu in Excel Tips for Learning English. All comments welcome and please appreciate these are works in progress so they don’t look spectacular yet!
21st March 2014: Goodness, it’s been a month since I posted anything! In my inbox yesterday there was a link to a video of a young boy completing a 5 x 5 Rubik cube and he completed it in 51.09 seconds. I can’t complete any Rubik cube that I have ever seen so well done that boy. Take a look at Rubik Times in the Excel Tips Other Menu to see how I created a chart and a POWER function using Trendline to make predictions of Rubik completion times!
18th February, 2014: two pages today, 1 COUNTIF and COUNTIFS and 2 RANDBETWEEN and RAND. Look in the Excel How to … Others menu. Each page has a file to download.
12th February 2014: A simple one for you: using the =TRUNC() function with CONCATENATE and ROUND() to convert and show, for example, 3.464783 hours into 3 hours 27.89 minutes. Excel Tips Menu … Others
1st February 2014 A Basic Modelling Case on labour cost accumulation. Just go to the Excel How To Menu … there is no Excel file to download but just write to me and ask for my file if you feel the need to see it.
16th and 17th January 2014 the shopping list file DONE! Other … How to menu. YES, THE DASHBOARDING EXAMPLE IS ONLINE NOW … 4 Charts Same Data … Excel Charts Tips Menu … thanks to Mynda Treacy again! I have UPDATED this file following Mynda’s really useful insights!!
10th January 2014 I was just responding to something on another teacher’s blog when I decided to share with you my shopping list file. What? Shopping list? ExcelMaster’s blog? You’ll see, there’s more to it than meets the eye! Coming later!
5th January 2014: happy new year! I have completed my explanation of Skewness, including several examples to help you to understand it. I have included examples using random numbers that you can recalculate over and over to test your understanding. You will also see box plots and dot plots that should also help you to appreciate the Skewness technique. This page applies to all versions of Excel, including Excel for the Mac 2011. Free file to download.
20th December 2013: three ways to create a histogram with two =FREQUENCY() functions and the COUNTIFS() function. Fantastic! See the how to menu, charts! Look at the page under Charts How to … for Population Pyramid v In Cell graphs: it’s deadly!
2nd December 2013: Merry Christmas, first of all. Secondly THREE really important posts for you today. Reverse Pivot UPDATE: I am learning more and more about reverse pivot and how to use it. Confidence Intervals: for anyone using statistics, hypothesis testing and so on, look at this page. Histograms using the REPT function: I THINK you might not find this anywhere else but do correct me if I am wrong. In any case, it’s great! All new pages/updates are in the Excel How To … menu.
12th November 2013: This blog now contains videos … I upgraded my subscription and can now upload and share videos. Number one follows … read this … you MUST read this. It’s not mine but there’s a video with it. The reverse pivot technique for turning a table into a list. It’s fantastic and so simple.
11th November 2013: read my new page on Wildcards and Numbers as Text. Very useful for you I am sure.
13th October 2013Oops! I used the Word 2013 automated blog uploading utility yesterday and the two posts on Beetlemania and Sports Day at the Primary School should have gone to another blog. Not my fault as I triple checked everything before I posted them. Then again, the power of Microsoft knows no limits. Enjoy them anyway!
15th September 2013: recently I set up a file to calculate the Piotroski F Score from accounting data. A bit involved but it looked fine and I used and demonstrated it. As I demonstrated it for the third or fourth time, I noticed an error. I THINK I know how it happened but I had not spotted it for a while. Corrected now 😁
20th August 2013: If you’re a bit nerdy you might love this page, in How to … Charts … it’s called The Moon: plotting the dates and time of a full moon … read on!
7th August 2013: a Then v Now Chart based on the work of chandoo.org. Look in the menu under Excel How to … Charting …
MY ONE HUNDREDTH POSTING ON THIS BLOG! … 4th August 2013: How to create a pattern for the tiles on a column in a building … including random tiling. See the menu Tips … Other.
27th July 2013: a basic page on stem and leaf diagrams … not really needed I don’t think but some people still talk about them! Other menu, … how to!
17th July 2013: back from my latest trip and I come bearing gifts … two mini projects and a nice little formula to learn. By the way, my first mini project was another one concerning a VERY slow spreadsheet. I watched in horror as this work book took at least 20 minutes to open. I was allowed to root round the file and very quickly ended up in a cell far far away from A1. In summary I was able to delete around 22,000,000 cells that each contained a formula that was being updated when Excel recalculated and opened. These formulas were created accidentally when the user clicked to fill down and for some reason it filled all the way down to row 1048576 instead of stopping at row 64800 where it should have stopped. The file opened normally after that! Go to the How to Others in the menu to see this new page: Mexican Projects!
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
25 04 2014
Bora wrote to me last week and asked for my Excellent help. I responded by saying, ok, I can do this on Monday. She replied to say, Wow! I didn’t expect you to reply!!
It was an interesting problem: a database with over 41000 rows needs to be interrogated using four variables. I offered two separate solutions: use and sort on an Excel Table and use SUMIFS()
Both solutions worked a treat and Bora was delighted I am happy to say.
21 03 2014
I have seen snakes around the house, on the road, in the bushes. Yesterday I saw TWO snakes around the house. One small, grey/brown, slithered under the gardening things at the back of the house and I left it alone.
A much larger, grey speckled snake, was meandering around the house and I couldn’t ignore that one. I have tried to identify both snakes but without success so far. With this one, whilst appreciating that it eats mice and rats and so forth, I took the view that it could be poisonous and a threat to the family. I dealt with it.
Duncan and Namwan
22 11 2013
Another course completed and another post for the blog just suggests itself naturally.
This week a delegate wanted to know how to find the weighted average cost of capital for his company: not just an academic view but a real WACC value. One of the aspects of WACC that is often a bit tricky is to find the value of Beta. It’s easy to find Beta values for listed companies in the UK, Europe and the USA but this company is in Saudi Arabia and I could not find the Beta value on the Saudi stock exchange web site.
What I did was to go and find a sample of the changes in the index of the entire stick exchange and the changes in the price of the share. I found the information I needed at bloomberg.com and here is the table I prepared in the Excel file:
All we need to do then is to create this formula: =SLOPE(C5:C12,B5:B12) … in cell E16 and the estimate of Beta this gives is 0.8314 … with a market Beta of 1, this tells us that Savola is rather a conservative company as far as the Saudi stock market is concerned.
If I have time over the next week I will try to expand this table to 70 prices and changes to give us a more reliable estimate of Savola’s Beta.
Watch this space!
14 11 2013
I had a delegate this week whose Pivot Tables would take any of his numerical data and by default count them rather than sum them. When he asked me why that happened I went through the normal explanation of data v numbers … which is true. However, in his case there seemed to be no reason behind this phenomenon: his data was clean as he was using my files and no one else had the same problem. He was using a good quality company laptop too.
We both checked on the internet last night and gained nothing from the many discussions on this problem: most of which related to data v numbers I have to say.
This morning I suggested that we look at his Windows Regional etc settings. His default language etc was US English and that seemed to be in order; but I said, let’s try this, change it to UK English and see what happens.
Ta daa! For some reason, this change did the trick and now this delegate’s Pivot Tables sum his data rather than counting it.
Another success story fromExcelMaster!
11 11 2013
UPDATE: 16th January 2014: I have been using this technique since I last reported and I have found some limitations AND solutions to this reverse pivot technique. I will prepare a page and video to demonstrate what I have found.
2nd December 2013
Watch the video and then imagine that you have downloaded a five year set of financial statements for a company. Nothing wrong with the data or the database they came from but imagine you want to set up a pivot table from them. Probably not possible unless you are lucky enough to have a data provider who has designed their output with this in mind.
Let’s cut a long story short: open this file amazon_case_reverse_pivot and try to apply the reverse pivot technique in my video to the data you will see there. Good luck and it will be worth any effort you put into it! PLEASE READ THE NOTES in this work book re copyright …
I didn’t invent this technique but I learned it a couple of weeks ago and used it in earnest just now … watch the video!
I had a table that had months across the top, years down the left hand side and data in between. What I wanted was the data in a list in three columns:
year month value
transposing, rotating, copying and pasting can give you what you want but it will take a long time. My original table covered all twelve months and five years …
Reversing a Pivot table is what it’s called and this is how to do it:
- Press Alt + D then P to call up the pivot table wizard and select Create a “Multiple Consolidation Ranges PivotTable.”
- Select “I will create my own page fields”.
- Select your data range and choose 0 page fields
- When you see your pivot table, double click on the intersection of Row Grand and Column Grand, in the bottom right hand corner of your pivot table
- You will be presented with your new table in the form of a list … that is, from pivot table style to list style … it’s magic!
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.
AGGREGATE(function_num, options, ref1, [ref2], …)
AGGREGATE(function_num, options, array, [k])
The AGGREGATE function syntax has the following arguments (argument:
Required. A number from 1 to 19 that specifies which function to use
Required. A numerical value that determines which values to ignore in the evaluation range for the function
Required. The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value
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:
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.