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 types of tables are well organized data in columns, together with any calculations or functions would help with usage or analysis.
Structuring data well is the most important thing that can be done in Excel (or any system) because it makes manipulation and analysis much more powerful.

Once you have your table, you can also apply Autofilter, which provides a powerful manipulation and analysis tool without any need for knowledge or application of formulae. (and if you can do those, then Autofilter is even more powerful).

Formal tables have Autofilter added automatically, together with other table functions, but these can be confusing, so you may prefer a simple table.

Formal tables automatically expand (together with any formulae) as you add extra rows, which is very useful and also reduces the problem of the actual table not being as big as it looks.

There can be more than one formal table per spreadsheet, whereas informal ones are restricted to one per sheet.

Please note: “formal” and “informal” are terms I have used to differentiate the two types. They are not official terms.

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 if they are altered (eg extended or moved) then the formulae that refer to them are correspondingly updated.

They can be referred to when on a different sheet, where a straightforward link doesn’t work.

Their names appear in the prompt list as a formula is typed, reducing error and simplifying selection.

Named ranges can be general (that is, across the entire workbook (the default) or specific to a particular sheet.
(This also a potential for problems, so it’s best to use the workbook-wide names only).


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 when finished.

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, by allowing you to program Excel. Routines can be written, dialogue boxes displayed, and data manipulated.
You can even write your own Excel functions.

A very popular use is to automate oft-repeated operations, which are generally called macros, introduced by Lotus in the 1980s.

These can be written, but the most straightforward and popular method is have Excel record them for you.

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 statistically valid (which may appear to make sense). It may even be obvious nonsense.

For example if you’ve entered telephone numbers as numbers (not a good idea anyway, because you’ll lose the leading zero, making the number even more difficult to read) Excel can calculate the average number you’ve called to 4 decimal places!

Note that AVERAGE in Excel is the mean. Of the other two kinds of arithmetic average, MODE and MEDIAN, MODE could produce a valid result because it would show which numbers you have called most. While MEDIAN would merely return the middle value.

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 data (STDEV.S) and for the entire population (STDEV.P).

As a rule of thumb, if you don’t understand the difference between STDEV.S and STDEV.P; what a sample or population is or even what a standard deviation is, then don’t use it!

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 various ways of doing this: eg, good naming conventions (making the spreadsheet easier to understand); clear logic (once again clarity triumphs over brevity); restricting allowed data.

These types of controls mean that the spreadsheets are less likely to go wrong; that they’ll produce the correct results; and if a change or development is required then it is much more efficient.

Locking a spreadsheet down, and restricting input can also help with security.

In the next blog I’ll talk about naming.

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 the earliest documented usage by me (I made a note in the Lotus manual). VLOOKUP has been around longer than that. I cannot find when it started but Visicalc (one of the earliest spreadsheets) had a LOOKUP function in 1979.

Well, 1979 is ancient history in computer spreadsheet, so I was rather amused on my recent holiday to Turkey (that’s why there haven’t been any blogs) to visit some sites which had carved tombs going back over 2500 years!

The photograph is of Myra in Antalya, with the carved tombs on the left, and the relatively new Roman theatre (2nd Century AD) on the right.

Even this is new, in a country where there are sites such as Göbekli Tepe (which I didn’t visit), which have remains of man-made structures dated between 9500 and 7500BC (ie 11500 to 9500 years old)!

Carved tombs in Myra

Carved tombs in Myra (Turkey)

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 then be replicated quickly across the whole year.

And most of the time that is how we want it, so we don’t even notice.

However there are times when we want the reference to remain fixed, eg we have today’s date in one cell, and we want lots of cells to refer to that. In that example, we could name the cell DateToday, and use DateToday in any formula, with the added advantage of improved legibility.

Another example is in analysis. Supposing we have a table of prices:

Absolute Reference example

Depending on whether the item in column A is an Apple, Banana or Currant, we want the price in the appropriate column.

We can do this with the formula (for the top row) of =IF($A4=”Apples”,$B4,””). This can be copied down successfully, as normal. The dollar sign indicates that the Column is fixed, but the row can change as normal. This is known as an absolute reference, or anchoring.

If we copy this across, because of the anchoring, it will remain pointing to the first column, and the result will come from the second column. We will have to change Apples to Bananas, but that is all (and we can combine it with an even more powerful use of absolute references to avoid doing that, but that’s for another day!)

In an absolute reference, the column can be fixed (eg $B5), the row can be fixed (eg B$5) or both can be fixed (eg$B$5). The dollar signs can be handtyped,  or you can work your way through the different combinations by pressing the function key .

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 75%, and green for above 75%.
Conditional formatting is a very simple test, which stops testing after a true condition.
In the exam example, if you tested for a score over 50 before over 75, then the second test would never be performed, because the first was true.

From 2007, the default is to test all conditions.

If you don’t want the tests to continue after a condition has been satisfied (ie it’s TRUE), then you should tick the “Stop if True” box in Home, Conditional Formatting, Manage Rules. This has to be applied individually to each Rule.

Even so, you need to check that rules don’t conflict, or you might not get the results you are expecting.

Posted in Uncategorized | Leave a comment