Excel is an amazing application and there are a large number of excel experts out there that use the programmes full array of functions. However, most of us are not experts and it can be frustrating when you come across errors you have no idea about. Common problems with formulas, functions as well as general little tips are what we will provide to hopefully improve your Excel experience.
Formula Error Problems.
1- #Value!
This error will pop up because the formula contains various kinds of values. You can tackle this error when one or more cells in the spreadsheet contain text instead of numbers for mathematical calculations. Make sure all values are the same type, for example, all values are numbers, or currency amounts.
Mathematical operations do not work with text, so when using any formula make sure the
data inputted is the correlating type. (Number for calculations instead of text)
2 – #####
Difficult to understand? This excel error is following the width of the cell in the spreadsheet. If the data to be placed in cells is exceeding the width range of the cell, Excel is going to display this error instead of the data you want to display. Simply expand the size of the column so that it is big enough to allow the data to be displayed correctly.
3 – Name Error (#NAME?)
This error arises whenever Excel is not able to recognise the text in the formula. Just like the first example using a formula for calculations, there is the function “SUM” this “#NAME?” error can appear if you misspell the function as something like “SU” instead of “SUM.”
4 – Null Error (#NULL! )
This is one of the most common types of error. When you forget to separate the two cell references correctly, then you shall receive this error. For example, =SUM (A2+A3 A4) the error will appear as a symbol is missing between A3 and A4.
5 – Reference Error (#REF! )
Occurs when the referenced value or data no longer exist. If you have the formula =SUM (A2+A3+A4) and you delete the value from A3 this error will occur. So always check that the formula is written correctly and has all the data from the cells that have been referenced
Formula\Function Assistance
The basic thing you need to understand when using formulas and functions are what they are and what they do. In the simplest of terms, a formula is any calculation in Excel. A function is a pre-defined calculation.
Calculations Example.
If you want to perform a simple task like adding multiple values together, you can use a formula for automatic calculations. You can manually write formulas like =A1+A2+A3 in a cell by clicking on the desired cell and finding the little box in the toolbar with the symbol Fx. You can then type in the mentioned formula and the results will be displayed in that cell. Or you can click on the cell and use a formula that is developed to add up values “SUM.” Examples are below.
=SUM (A2:A10) Adds the values of all cells A2 to A10.
= SUM (A2+A10) Adds the values of only A2 and A10
A more advanced example would be =SUM (A2:A10, C2:C10) which adds up all A2 to A10 and C2 to C10 and displays the results in the cell with said formula.
Using AutoSum Option
There is an “AutoSum” option in the task bar, with this you can simply click on a cell, then click AutoSum, highlight the cells and Excel will place the total of the highlighted cells into the cell you selected.
Quick Insert: Use Recently Used Tabs.
If you find yourself retyping the same functions repeatedly throughout your spread sheet there is a tool that remembers all recently used functions. With Quick insert you will be presented will all recently used functions and can simply click a function, highlight cells, and excel will take care of the rest.
Hopefully, these little tips and problem fixes help you reduce the mistakes you make when using excel as well as improve your efficiency when working.
We are not going to claim to be excel experts, although we all use it daily we are not creating big database spreadsheets. Having said that, if you have problems with something you can always ask us. As with any IT team… what we do not know we will find out!
Contact us at www.norfolkcs.co.uk or 01953 857980 for a free audit and friendly discussion on how we can help.