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.

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

A VERY brief introduction to Excel Tables ...

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

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

Where am I?

09 03 2015

I am writing another Excel book.

I will share some things with you next week.

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:

petrol_costs

 

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:

sleep_graph

sleep_table

 

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

 

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

The Find&Replace menu in Excel contains some hidden gems. Things that you might never have seen before. Take a look here to see what you’ve been missing. http://ift.tt/1JrisW3

This video shows you how to use the =CORREL() function in Excel with the Ctrl+Enter technique. This is especially useful for Mac users who do not have access to the Data Analysis Toolpak http://ift.tt/1yWlzFO

Happy New Year

31 12 2014

Hello everyone!

It is 20:35 on 31st December where I am and that means new year celebrations have started or are very shortly about to start somewhere.

I wish all of you a happy and peaceful new year. It is my resolution to post more pages here in the coming year. As you know, I post something as often as I can and I try to ensure everything is of high quality.

I have upgraded my screen capture video production software and intend to create and post more of them.

Do feel free to write to me at any time, by the way: I always reply to my messages and comments.

Best wishes

Duncan Williamson

Follow

Get every new post delivered to your Inbox.

Join 170 other followers

%d bloggers like this: