Spreadsheet Expert Blog

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.

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

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.

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.

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!