Category Archives: Excel Training

BODMAS Using Brackets

Save yourself from errors with the BODMAS rule

A question we often hear is “Why do we need to put Brackets in Excel Formulas?”

Some formulas that you use won’t need brackets.

If you write the formula =5+2+10,

Bodmas1

It will calculate the result as 17.

Bodmas 1b

There were no brackets in that formula, and it still calculated the correct result.

If we write the formula =5+2*5 we would expect the result of 5+2 which is 7, multiplied by 5 equalling 35.

Bodmas2

When we write the formula, the calculation returns 15 not 35.

Bodmas3

This has occurred because Excel doesn’t calculate the formulas left to right, it follows another set of rules.

Bodmas4

The priority order is referred to as BODMAS. Exponent or Power classifies as an Order, hence the O. It’s clear that Excel prioritises Divide and Multiplication over Addition and Subtraction. When we wrote =5+2*5, Excel calculated the 2*5 which is 10 and then lastly it added 5.

A pair of brackets has the highest calculation priority. If you wanted to force Excel to calculate different parts of your formula first you could place it withing a set of Brackets.

If we correct our formula by adding the brackets =(5+2)*5 we should now expect it to calculate the brackets first.

Bodmas5

Bodmas6

Adding the brackets has worked!

The 5+2 in the brackets was calculated first, and then it was multiplied by 5.

This explains why people use Brackets in Excel Formulas:

  • They are used to make Excel Calculate different parts of a formula in a specific order
  • Brackets work in pairs, if you open a Bracket you must always close it to complete the pair

 Additional resources:

What is a Bodmas formula and how to use it

BODMAS

Remove Duplicates in Excel

How to remove duplicates from a column of data in Excel

It’s not uncommon when adding or importing records that you may need to remove duplicates in Excel. Whilst filtering and deleting records to make a list of unique items is an effective solution, there is an even easier way.
Simply follow these steps to remove duplicates in Excel.

Step 1 Copy the column of data containing the repeated or duplicate items to a blank column.

Step 2 Highlight the copied column of data and select Data, Remove Duplicates.

RemoveDuplicates

Step 3 From dialog box remove the tick next to ‘My data has headers’ if you did not include a header at the top of your data.

RemoveDuplicatesDialog

Remove Duplicates in Excel

 

Step 4 Press OK and all the duplicates will be removed.

A message displays at the end telling you how many duplicates have been removed.

RemoveDuplicatesResult2

  Continue reading