Author Archives: spreadsheetexpertblog

About spreadsheetexpertblog

Working as a computer professional since 1983, on projects varying in size from one person to multi-million pounds. Working on Excel projects since the mid 1990s.

Tables in Excel – introduction

There are two main types of table in Excel: the formal and the informal. Formal tables are part of the Excel “toolkit”. To add one, go to the Insert tab, and Table can be found in the Tables group. Both … Continue reading

Posted in Uncategorized | Leave a comment

Good design – Naming

Ranges, which can be single cells or multiple cells can be named. They can make the logic of the spreadsheet easier to understand. This is useful for the initial design and for subsequent development. Named ranges are more robust, because … Continue reading

Posted in Uncategorized | Leave a comment

Array Functions – Introduction

A group of six children do a test. How many achieve higher than the threshold (in the example, 15)? This is a simple example of the powerful array formulae. Don’t forget to press CTRL + SHIFT + ENTER rather than ENTER … Continue reading

Posted in Uncategorized | Leave a comment

Macros: Making Excel even more capable

There are several hundred functions on Excel, which can mostly be combined to produce thousands more, but sometimes even they cannot cope with requirements. Excel’s programming language, VBA (Visual Basic for Applications) is there to help you achieve the missing capabilities, … Continue reading

Posted in Uncategorized | Tagged , , | Leave a comment

Care when using Statistical Functions in Excel (Part 1)

Excel is a powerful tool for analysing data, and as part of that has a wide range of statistical functions. However these must be used with care: the fact that Excel allows you to do something doesn’t mean it is … Continue reading

Posted in Uncategorized | Leave a comment

STDEV.S vs STDEV.P observation

Excel is a very powerful product, but particularly when using statistical functions, you need to take care. One example is standard deviation, which gives information on the spread of the data. Excel allows you to do it for a sample of the … Continue reading

Posted in Uncategorized | Leave a comment

Good design – introduction

Spreadsheets mean that things can be tried out very quickly; and changes and improvements made (almost) immediately. However, in a ‘mature’ spreadsheet, the only changes should be to the data, with the functionality locked down and well documented. There are … Continue reading

Posted in Uncategorized | Tagged , , , , , , | Leave a comment

There’s history and history!

The other day I saw an article on the web by a writer who seemed to think that VLOOKUP was new to Excel 2013. Strange, I thought: I was using it in Lotus 123 in 1990. Now that is only … Continue reading

Posted in Uncategorized | Tagged , , | Leave a comment

Relative and Absolute References

One of the great attractions of spreadsheets when they started was their ability to quickly replicate complex calculations, once those calculations had been developed for one category. An example might be calculations worked out for one month , which could … Continue reading

Posted in Uncategorized | Tagged | Leave a comment

Conditional formatting – Hierarchy

In 2003, the hierarchy is very important in Conditional Formatting: if you have that wrong some conditions may never be tested. Supposing you want the cell to go red if less than 50% in an exam, amber between 51 and … Continue reading

Posted in Uncategorized | Leave a comment