Excel

Notes

Charts and graphs

During your course, you will be expected to create a variety of charts and graphs. Before you begin, you should first identify which data you need to select. This will be the categories and values in your table.

You will then need to work out what type of data you are working with, so you can choose an appropriate chart type.

Bar graphs and column graphs work best when you are comparing the data in a variety of categories or subgroups. The bars can be vertical or horizontal. The height or length of the bar is proportional to the value.

Line graphs work best when you are showing how values change over time. Points on the line move up and down as the values in the data increase and decrease.

Pie charts work best when you are showing the distribution or proportion of values. A circle represents the total value (100% of the data). The circle is then divided into sectors that show how the value is divided between the different categories.

Errors in formulas

During the course, you will use built-in functions in Excel (SUM, AVERAGE, MIN, MAX and so on), but you will also be expected to write your own formulas. This can lead to some unexpected error codes. Make sure you are familiar with the common errors and know how to use the Formula Auditing tools. These make tracing an error much easier, especially if you have nested formulas, where your formula refers to cells that themselves contain formulas.

#DIV/0!

A division by zero error. It shows up if you try to divide by a cell containing zero, or a cell with no content.

#NAME?

Usually shows up if you refer to something Excel doesn't recognise. Check for typos and make sure you are using named ranges correctly.

#N/A

Can also mean you have spelled something incorrectly, or there is a typo in your instruction. Mostly, though, it is telling you that something is missing. Check you aren't trying to get data from a lookup table for a value that hasn't been set.

#NULL!

Another error that usually only appears because of a typo. Check you haven't missed out a colon in a range, or typed the wrong separator.

#VALUE!

Usually returned if there is a problem with a data type. Check you haven't got text in a cell that expects a number (O instead of 0, or I instead of 1, are common culprits).

#NUM!

Should prompt you to check for mistakes in the input data. Excel is trying to make an impossible calculation or work with values that are too large or too small.

#REF!

A referencing error. Check which cells you are referencing, to make sure they haven't been deleted by accident or moved somewhere else.

########

Not really an error, but it pops up often enough to mention. A string of hashes means the contents are too wide for the cell. Widen the column and the contents will reappear.

Even if you're lucky enough not to get any errors, don't let the silence lull you into a false sense of security. Always test your formulas with a range of inputs to check they return the expected output. Part of this might be entering simple values you can check by hand.

Use the Microsoft study guides to build your knowledge or revise topics outside class.