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 determine what type of data you are working with, in order to 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 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 will 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 is used to represent the total value (100% of the data) The circle is then divided into sectors that represent how the value is divided between the different categories.

Errors in Formulas

During the course, you will be using built in functions in Excel (SUM, AVERAGE, MIN, MAX, etc.) 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 will make tracing an error much easier, especially if you have nested formulas i.e. your formula refers to cells which themselves contain formulas.

#DIV/0! is a division by zero error. This will show up if you are trying to divide by a cell containing zero (or a cell containing no content).

#NAME? will usually show up if you try to refer to something that Excel doesn’t recognise. Check for typos and make sure you are using named ranges correctly.

#N/A can also indicate that you have spelled something incorrectly, or there is a typo in your instruction. But, in the main, it is telling you that something is missing. Check you aren’t trying to obtain data from a look up table for a value that hasn’t been set.

#NULL! is another error that will usually only pop up in the case of a typo. Check you haven’t missed out a colon in a range, or typed an incorrect separator.

#VALUE! is usually returned if there is a problem with a data type. Check that 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! errors 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! is a referencing error. Check what cells are you are referencing to make sure they haven’t been accidentally deleted, or the values haven’t been moved to a different location.

######## isn’t really an error, but it pops up often enough to warrant a mention. A string of hashes like this simply indicates that the contents are too wide for the cell. Resize the column and the contents will reappear.

PS 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 and values to check they are returning the expected output. Part of this process might be to input simple values that you can manually check

Use the Microsoft Study Guides to expand knowledge or revise topic areas outside the classes.