Duncan Williamson: Spreadsheet home page spread.htm

Visitor Frank Sloan wrote and told me that he had found it difficult to open the PowerPoint Presentation on designing spreadsheets and the introduction to Pivot Tables... I took a look and found that it could only open in an Internet Explorer browser. I had tested this presentation of course but it was 11 years old and I was unaware of the problem. Anyway, I have now brought the presentation up to date and included a PDF file for you to download of it as well as a PPT version for you to see in IE.

A new page: this time, Box and Whisker Charts using Excel 2007. Fully comprehnsive with many screenshots.

I have corrected the page on =MINVERSE() and =MMULT(): there was one column of a table missing and the formatting of the tables on that page was a bit off. Look in the menu: Statistics ... Simultaneous Equations with MINVERSE and MMULT.

A file on calculated fields in Pivot Tables: this refers to a large Excel file you need to request from me, for copyright reasons.

I have put together a few elements of the debate on good and bad spreadsheet modelling practice. Not everything but well worth reading just to make sure! Look in the Excel section of the menu on the left.

Over in the Book Reviews section there is the review of a book by the brilliant Bob Umlas ... go to the Book Reviews Section and take a look at what I mean.

I have added some new charting pages: waterfall charts and a chart using dataselected by using a combobox. You have probably seen the waterfall chart from time to time. Well, here is a page showing you how to make one. Both charts are deceptively simple but you will need to concentrate! Full instructions given. Look under the Excel menu on the left.

Click on the Excel link in the menu on the left of this page to look at my PDF files on Templates in Excel ... it's all explained in the introductory page. Here also is my first demonstration, of sorts, using Excel 2007. I have taken a graph that's been produced by someone else using Excel 2003 or even earlier; and changed it by using Excel 2007. I have only changed the appearance really although I did tweak the axes too.

You might need to use the XNPV and XIRR functions some day so here's a page that shows you why and how. This page also reveals a minor Excel error and a potentially more serious one. This page is also of use to OpenOfficeOrg spreadsheet users.

An example of the application of the Chi Squared test: data and analysis with conclusion but no discussion.

A small update to page one of my Lorenz Curves mini series: I added the UK Gini Coefficient data by way of a graph for the period 1979 to 2002.

Two page series on plotting and analysing trends using Excel: these are relatively large pages as I had to code them using MS Word's ballooning Web Page option, otherwise I would have been coding for weeks. Aimed at the introductory Excel level.

Here's a new page that shows how we can use a spreadsheet to take a list of accounting transactions and build a Trial Balance from it ... automatically ... using the magic of a spreadsheet!

If you're interested in Permutations and Combinations, look no further. A page of maths with a bit of spreadsheeting thrown in.

A list of the shortcuts that I find most valuable plus some examples of how to use the REPT function, conditional formatting and custom formatting. All great stuff!

I don't now why I've ignored them until now but DATA TABLES are a wow! I strongly recommend them ... read about them here!

a page on using Excel's SOLVER function: we've used a simple example to demonstrate SOLVER but it really is a very powerful function that's well worth working on.

I have put together a page on drawing box and whisker diagrams, boxplots, using MS Excel. A fairly simple routine that draws on other people's work but I added a bit of formatting to help it all along!

These pages are still here, of course: SUMIF used to generate a profit & loss account; and Import External Data utility used to take data from the Internet

Using SUMIF to generate a Profit & Loss Account ... or any other account for that matter!

SUMIF is one of Excel's built in functions and even though it's been around for a long time, I think it's day has finally come!

The page on using SUMIF to generate a profit & loss account may shock and amaze you; but you'll certainly find it more than useful.

I've presented it in accounting terms but I can imagine anyone who needs to manipulate any data will find this page more than useful.

Import External Data with Microsoft Excel

What this page describes is how Excel XP/2002 allows us to import data directly from the Internet without ever having to go to the sites we are downloading from.

The Import External Data page takes us through a series of examples that explore the main parts of this excellent utility.

Work through Excel's default examples and then work through real live examples that will help to encourage you to start using and benefiting from this utility immediately.

With practice we will find that this utility is both simple to use and will quickly take us the point of believing that we cannot live without it any more!