visit our website www.pbatraining.co.uk

Formulas in Excel perform calculations such as addition, subtraction and multiplication. They use the familiar arithmetic operators (+, – etc) however, formulas in Excel always begin with an equals sign (=).

Arithmetic Operators in Excel

+ Addition

- Subtraction

* Multiplication

/ Division

% Percent

Formula Construction

An example of a formula in Excel might be =2+2, which of course will return the answer 4. However, this would be a very restricted formula as it only returns one possible value. In practice, when we input formulas into an Excel spreadsheet we substitute raw data for cell references.

So an example formula might be =A1+B1. If the values in A1 and B1 are both 2 then the formula will return the answer 4 as before. However, we can now change the values in cells A1 and B1 and get different results without having to edit the formula.

As a general rule, we should try never to insert raw data into Excel formulas; it is almost always better to reference cells instead.

Here are some examples of Excel formulas:

• Subtract B1 from A1: =A1-B1
• Multiply A1 and B1: =A1*B1
• Divide A1 and B1: = A1/B1

Editing Formulas

Formulas can be edited in the cell by double-clicking with the mouse. They can also be edited in the formula bar by selecting the cell, then clicking inside the formula bar.

Combining Arithmetic Operators

If we wish to combine operators in the same formula it is sometimes necessary to separate them with brackets. this is especially true if we mix multiple & divide with addition and subtraction. This is because the operators of multiply and divide take precedence. Consider the following example:

We wish to add A1 and B1 then multiply the result by 20%. If we enter =A1+B1*20% we get the result 2.4. But this is incorrect, we should get 0.8.

The reason we don’t is that because multiplication takes precedence over addition, Excel will first multiply B1 by 20% and then add A1 to the product. In order to add A1+B1 first we need to wrap them in brackets, so our formula becomes =(A1+B1)*20%. This gives the correct result of 0.8.

Self-Test Exercise

1. First enter the data shown into cells A1 and B1.
2. Next add, subtract, multiply & divide the data in separate cells in column C.
3. Finally, in C5 add the data and calculate 10% of the product.

Attending a Microsoft Excel Training Course

Our 1 day Foundation Microsoft Excel course covers formulas and many other essential skills. It is suitable for people with no previous experience of using Excel and also those self-taught users who would benefit from a structured training course to ‘fill in the gaps’ and extend their working knowledge of the application. We run onsite Microsoft Excel training courses throughout the UK, please call free on 0800 2922842 to discuss your IT training needs. Alternatively you can email us on info@pbatraining.co.uk.

Throughout 2009 our most popular courses have been Advanced Microsoft Excel, with Microsoft Project following a close second. Advanced Word is quite a way down the list and Foundation Word courses are near the bottom. Part of the reason for this is that Microsoft have done a pretty good job of making Microsoft Word very easy to use at a basic level and many people feel confident in their ability to teach themselves. However, there is a fundamental problem here best summed up by the old adage:“you don’t know what you don’t know.” In other words, you cannot teach yourself about features which you don’t know exist!

This is a pity as it means that many people are not getting the most out of the software for which they have invested a considerable amount of money. Many of the little know features can save considerable time on tasks, precisely the areas we should be learning about in order to be most productive.

What features am I referring to? Well, few people are really taking advantage of styles, which is one of the most powerful Microsoft Word tools you can use. A style is essentially a collection of formatting attributes which are named and saved. Styles have been around since the earliest incarnations of Word, but Microsoft Word 2007 really pushes the boat out with a large gallery of pre-defined styles on the ‘home’ ribbon. The big advantage to using styles over simple text formatting is the facility to modify them and in so doing, automatically update every instance where it has been used throughout the document.

Another underused feature of Microsoft Word is the Level. Levels give a document structure which can be used to drive other tools such as the Table of Contents generator. This feature alone saves me countless hours when writing training courses, yet few people take advantage of it.

These features and many more are covered in our 1 day Advanced Word training course. If you would like more information please call 07833 543272 or email paul@pbatraining.co.uk

We are pleased to announce that we are now able to provide laptops for onsite computer training. For a daily onsite rate of £400 we can and train up to 10 employees in any suitable sized office. This includes all the Microsoft Office suite of applications including Access, Excel, Project & Word. Email info@pbatraining.co.uk or telephone 07833 543272 for more details.

2009 has been a difficult year for many and training is often one of the first casualties when budgets are tight. With this in mind we will be making training more affordable in the New Year by offering all our IT courses at half price throughout January. This offer includes all the Microsoft Office applications including Project, Excel, Word, Access and PowerPoint as well as our popular Email Marketing and Search Engine Optimisation programmes.

Please email me directly on paul@pbatraining.co.uk or phone 07833 543272 for a no-obligation quote. I would like to wish all our clients a very happy and prosperous New Year and look forward to being of service to you in 2010.

In response to a growing number of clients’ needs, we have updated our Advanced MS Project course to include further training on issues such as resolving over allocation, resource levelling, applying budgets, grouping data and producing detailed visual reports in Excel. For more details please visit the website at http://www.pbatraining.co.uk/computer-training-courses.html or phone 07833 543272 

Project managers, here is an excellent opportunity to learn how to use the powerful Microsoft Project application. More and more project managers are realising that they can save time and get the job done more efficiently using this industry standard software. MS Project 1 day courses frequently cost £300 or more; we are currently offering expert tuition for just £125 including lunch and all day refreshments. For more details please the visit the Microsoft Project Training section of our website. Alternatively email bookings@pbatraining.co.uk or phone 07833 543272.

We have scheduled an Advanced Microsft Word course for Tuesday 8 December 09. During this 1 day practical workshop you will learn how to properly structure a document using styles and levels, how to build a table of contents and index, how to insert bookmarks and cross references, how to create editable forms amd also how to save time with macros. Course fee is £125 including lunch and all day refeshments. For more details please visit the Microsoft Project Training page on our website. Alternatively email bookings@pbatraining.co.uk or phone 07833 543272.

We are running a 1 day Foundation Excel course on Friday 4th December 09 in Scunthorpe, North Lincolnshire. There are still a couple of places available, email bookings@pbatraining.co.uk or phone 07833 543272 to reserve your place.

We are conducting a Foundation Microsoft Access training course in Scunthorpe, Lincolnshire on Tuesday 24 November 2009. This course is ideal for beginners to Access and includes how to create a database, setting up tables and forms and how to query data. For more information please visit the Microsoft Access Training Courses  section of our main website or call 07833 543272, email paul@pbatraining.co.uk.

We will be running an Advanced Excel training course on Friday 20th November 09 at Kingsway Consulting in Scunthorpe. For more details or to book you place please email paul@pbatraining.co.uk or phone 07833 543272.

We still have places on  a number of IT training courses in Lincolnshire including Microsoft Project, Word and Excel at various levels. Please see our IT Training website for more information about these 1 day computer courses.