Tag Archives: excel courses

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.

 

 

 

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 http://www.microsofttraining.net/excel-2010-intermediate.php

 

Applying a background to an Excel worksheet

This is a quick function to try out and can add a bit of fun to a worksheet.

I’m creating a worksheet listing different forms of marine life for a quiz for some children visiting the local aquarium.

It will make the screen look more inviting if I have a photo behind the worksheet to go with the theme.

I create a new file, and go to the Page Layout tab, and select background.

page-layout-tab-excel-courses

The Page Layout tab has the Background button…

I can select one of Microsoft Pictures Library to make this super-quick (the kids are waiting…)…brilliant, I’ve found a jellyfish.

jellyfish-excel-courses

Jumping jellyfish – that looks fun!

The background is quite dark, so I will change my font colour to silver using the Home tab 

font-colour-excel-courses

Use the Home tab to change the font colour

The children may be a tad young to come along to Excel courses, but we can still help them learn about marine life by using Excel with our helpful little jellyfish. http://www.microsofttraining.net/excel-training-london.php

 

 

 

Adding a Calculated Item to a Pivot Table in Excel 2010

Above is an example of a standard pivot table in Microsoft Excel 2010.  It is set up with financial quaters as column headers and products as Row labels.  I’m interested in seeing the results for the combined sales for the first half and the second half of the year. As you can see I have colour coded these two halves and now I am going to add two “calculated items” showing a total for Q1+Q2 and Q3+Q4.

TAKE ACTION: 

  1. Ensure your cursor is placed onto the Q4 column header as in the image.
  2. Select the “PivotTable tools” tab and click on “options”
  3. In the “calculations” box” select “fields, items, & sets” and then “calculated items”

When this box appears follow these instructions:

  1. Click into the “name” field and enter the new name Qtr1+Qtr2.
  2. Click into “formula” field, remove the 0, double click on Qtr1 in the “Items” field, add + then double click on the Qtr2 from the “Items” field.  Here you are entering a formula which is Qtr1 + Qtr2.
  3. Click the “Add” button and then OK

You will now see that this new column has been added to your PivotTable in Microsoft Excel 2010.

Repeat this process for Qtr3 + Qtr4 and adjust the background colours to match those already on the pivot table.  All going well you should have a pivot table that resembles the one I have pasted below:

You now have a pivot chart showing you the totals for both halves of the year.  Take note that your grand total includes your two new columns so its best to remove that. To learn how to remove the total column in Microsoft Excel 2010, well that’s for the next blog.

Good luck!