Tag: Excel Formulas

Posted on

Get more out of formulas in Excel using functions

It’s easy to fall into a habit, and manually type in a formula and copy and paste it within a worksheet. If so, you may not be getting the most out of the Formula tab on the Ribbon. From here, you can save yourself time by using Excel’s ready made formulas using Functions.

The Formulas tab has the regularly used calculations and set them up as functions in 12 different categories.  So you have these at your fingertips to apply to a range of cells that you specify.

The 12 categories include: financial, date and time, maths and trig, statistical, database, text, look up and reference, logical, information, engineering, cube and compatibility.

Each of these categories has a drop down list with a selection of common functions, ready for you to apply to your selected cells.

functions-microsoft-excel-training-course
The Formula tab has functions ready for you to apply.

For example, if you need to calculate the cost of loans, or interest, yield and depreciation, you can access these functions via the Financial Function.

If-then statements are in the Logical Functions – so you can analyse data setting up logical statements, setting out whether a value is true or false, or using AND or OR.

If you regularly want to work out averages, minimums, maximums, or interest rates, these are stored in the Statistical Functions.

To get the most of Excel’s functions, it is worth exploring what the Formulas tab has to offer.  An excellent way to find out more and learn how to use them is to attend a  Microsoft Excel training course, so you can see what is available and practise using them before using them on your own live data.  From Intermediate Excel to Advanced and beyond, our courses cover Formulas and Functions in more detail https://www.stl-training.co.uk/microsoft/excel-training-london.php

Posted on

Introduction to the language of formulas in Excel

One of the best tips for Excel that I’ve ever been given is “if you can automate it…set up a formula”.

There is nothing more frustrating than trying to figure out calculations if you don’t understand the rules –  Excel can do the number crunching, which means I can focus on adding the data and analysing it.  It reduces errors too, as once the formula is set up, and the format for the answer cell is correct, the results are updated automatically.

Terminology is key to understanding how to set up formulas.  This used to be the section that induced mild panic for me, but knowing the language of formulas means I can follow the instructions from Microsoft Help, and know which of the ready made formulas can give me the results I’m after.

Formula operators
Formula operators apply the calculations in your worksheet.  These include the arithmetic operators of addition, subtraction, multiplication and division.  You can also add percent % and caret

To apply these operators you need to know the symbols to use: Brackets ( ), addition +, subtraction -, multiplication *, Division /, and Exponent ^.

Comparison operators
These are used in formulas to compare two values.  The comparison operators are equals (=), greater than (>), less than (<), greater than or equal to (>=) and not equal to (<>)

Concatenation operator 
This is used to concatenate (join) two values to give one continuous value.   The ampersand (&)  is used to join two values to give one value.

Reference operators
Reference operators are used to combine ranges of cells between two reference points.  The colon (:) gives one reference to all cells between two references (for example when adding a column you have the starting reference point and end reference point, B2:B12.  The comma (,) combines a number of references into one.  and the use of space gives one reference to cells, common to two references.

The order of calculations
To set up your formulas, you need to know about the order of calculations.  You may have heard the acronyms Bedmas or Bodmas – this is a useful way to remember which operator comes first

  1. B stands for Brackets
  2. E stands for Exponents, or O for Order
  3. D stands for Division
  4. M stands for Multiplication
  5. A stands for Addition
  6. S stands for Subtraction

Excel formulas can take your work to a higher level, improve your data analysis and speed up decision-making.  Whether you are new to formulas or looking to work at advanced level formulas and functions consider a course excel 2010 london, https://www.stl-training.co.uk/microsoft/excel-training-london.php to help you to get more out of Excel, and let Excel do the number-crunching.