Tag: excel courses

Posted on

Use the Error checker tool in Excel

Error checking is part of Excel’s auditing tools.  It is set up to check your formulas for errors.

Like spellcheck, the Error checker tool won’t necessarily find all errors, but it is a useful auditing tool as it works through your document to flag up the errors it spots.

In the Formula tab, you have the Formula Auditing section. The Error checking tool  is included here.

error-checking-excel-courses
Formula auditing in the Formulas tab

The and it has options available to you, to tick what error checking rules you wish to use.

tick-box-error-checking-excel-courses
Here, you can tick the boxes to apply the rules you want the error checker to apply.

In this dialogue box you can select the Enable Background Error Checking, which, like spellcheck, will run in the background as you work.

You’ll know it has found an error as you get a green triangle in the corner of the cell.  A discreet reminder that you need to check something. This is called a smart tag, and by clicking on this you can now address the issues that the error checker has found.

When it finds an error, It comes up with a help menu (similar to Spell check when it suggests options) so you can work through systematically.

sample-error-checking-result-excel-courses
Options available to you, once an error has been spotted

If you find spellcheck useful, it doesn’t take too much of a leap of faith (perhaps a mere shuffle) to make the most of the background help with your formulas.Tick the boxes and let it do the work.  For more advanced techniques covered in our Excel courses take a look at what we can help you with.

 

 

 

Posted on

Convert Function in Excel – how to convert more than just dates

The convert function is used to change data from one unit type to another in Excel.
It’s often used to convert dates, but it can do so much more as there is a  range of conversion units that you can use to make your life easier.
The convert function is an engineering function.  In Excel 2010 it is found in the Formulas section of the ribbon as follows; More functions>Engineering>convert.
convert-formulas-excel-course
The convert icon is located in the Formulas tab.

Converting dates and times
Dates and times often require converting and Excel can do this for you automatically, you just need to set it up, to do so.

For example, if you add or subtract calculations between two dates, Excel will automatically give the result in days unless you tell it otherwise.  So you need to perform another calculation to get the result in the format you require (years, months or hours).

For example, a call centre keeps a worksheet with the start and end times and dates for calls received.  The manager wants to find out how long each call takes. To show the duration in the most appropriate unit, the answer needs to be presented in minutes rather than hours, however, the format of the data means that the result is shown in days.  So the manager can use the following convert formula to show the results in minutes instead:

=CONVERT(G5,”day”,”hr”)

Converting weights
An example would be converting ounces to grams in sales data for a family butcher, and he uses the the following formula:

=CONVERT(A1,”ozm”,”g”)

Length conversion
An online fabric shop may need imperial and metric measures for selling material. In the UK, many people still ask for a yard of fabric rather than a metre, and data can be stored on a stock spreadsheet easily in both units. This way those people who think in yards can see the yards, those who want metres can see metres. The following formula would achieve this:

=CONVERT(G5,”m”,”yd”)

There are more units that you can convert.  To explore this function and get more out of  Formulas consider attending one of our Excel courses. Find out more on https://www.stl-training.co.uk/excel-2010-intermediate.php