The first thing that you need to know is that all functions are formulas, but not all formulas include functions.
Let’s start by defining what they are:
Formula
While there are many ways that we use the term ‘formula’, I’m going to focus on the mathematical aspect.
a mathematical rule expressed in a set of numbers and letters
Definition from Cambridge Dictionary
OK – in plain English, a formula in Excel tells the spreadsheet that this is something to be worked out, and then the answer displayed.
How to Recognise a Formula
All formulas need to start with an equals (=) symbol.
However, a number of spreadsheets will also accept a plus sign (+) at the beginning of a formula.
Functions
There are a number of formulas that people use over and over again, for example adding up a range of numbers.
To help, Excel and other spreadsheets have these calculations pre-written, you just need to add the right information.
Due to the number of functions – each one has a name that relates to their use.
For example:
- SUM
- AVERAGE
- IF
- LOOKUP
Because they are a calculation that you want Excel to perform and show the result, they are a type of formula.
How to Recognise a Function
Like a formula, they start with the equals sign, however they continue with the function name – to identify the specific one to use – followed by an open bracket, the details needed for the calculation and ending with a close bracket.
=FUNCTION-NAME(DETAILS)
Many functions can have more than one piece of information inside the brackets, and they are separated by comma (,).
Putting Formulas and Functions to Use
Using a Formula in your spreadsheet
While it’s straight forward to use, do you need to decide what you want to calculate, as that will “tell you” what to enter.
All you really need to do is type = (equals) and then what you want it to work out.
Of course a key question is where are you typing in the formula?
The answer is “where you want the answer to appear”.
Remember to ensure that you have the right cell selected before you start typing.
Simple example: =2+3
Type it into any spreadsheet (Excel, Google Sheets, iWork Numbers) and the answer 5 will be displayed.
Note: An easy way to start – think of what you’d type into a calculator, but put the equals at the beginning.
Generally we either don’t know exactly what the numbers are to be used, or they may be reliant on something else e.g. another formula.
In that case, we replace the ‘number’ with a cell location where the number can be found.
Simple example: =A1+A2
Note: Type in the numbers first, so that you can select the right cell as you enter your formula.
Using a Function in your spreadsheet
In order to be able to use a function, you need to know what you want to do, and then find the ‘matching’ function name.
There are a lot of functions to choose from, and to make it (a bit) easier, they are grouped:
- Financial
- Date and Time
- Math & Trig
- Statistical
- Lookup and Reference
- Database
- Text
- Logical
- Information
- Engineering
Fortunately, a) you don’t need to know or use every single function (phew), and b) there’s a search function to find the right one.
In Excel – at the left of the Formula Bar is the fx button. Click the button or use the shortcut key Shift + F3, to display the Insert Function dialog.
In the dialog box, you can search based on the name (if you know it) or a brief description of what you want to do.
When you choose the function you want, the dialog box will show you the information it’s expecting, in the order required and allow you to enter data or cell addresses.
Recap
Formulas are how you enter the calculations that you want worked out into a spreadsheet.
Formulas have to start with the equals sign (=).
Functions are a sub-set of formulas – as they also work out answers, but are pre-written for a specific calcuation.
Functions also start with an equals sign but then have the function name followed by the required information in brackets.
Related Posts
- Excel Paste Values – Get Exactly What You See, Every Time
If you’ve ever copied something in Excel and ended up with a completely different result than you expected, you’ve probably fallen foul of one of Excel’s little quirks. The good news is that once you understand Excel Paste Values, you’ll know exactly how to handle it – and it only takes a couple of extra… Read more: Excel Paste Values – Get Exactly What You See, Every Time - Excel Sparklines: The Tiny Charts Making a Big Difference
If you’ve ever stared at a column of numbers and thought “I just want to see the pattern” — Excel Sparklines are about to become your new favourite thing. These miniature charts sit inside a single cell, right next to your data, giving you an instant visual snapshot without the faff of building a full… Read more: Excel Sparklines: The Tiny Charts Making a Big Difference - Do You Know All the Excel Flash Fill Tricks?
If you’ve ever exported data and thought “why is everything jumbled together in one column?”, Excel Flash Fill is about to become your new best friend. It’s one of those features that looks impressive but is actually really simple to use — and once you know it, you’ll find yourself reaching for it again and… Read more: Do You Know All the Excel Flash Fill Tricks? - The Complete A-Z Guide to Excel Keyboard Shortcuts: Master Your Productivity
When most people think about keyboard shortcuts, Microsoft Word typically comes to mind first. However, as an Excel enthusiast, I’m here to tell you that Excel has its own powerful set of keyboard shortcuts that can dramatically improve your productivity. Today, I’ll guide you through the complete A-Z of Excel’s keyboard shortcuts for Windows users.… Read more: The Complete A-Z Guide to Excel Keyboard Shortcuts: Master Your Productivity - Complete List Of Options Where You Can Customise the Colour
When it comes to colour in your office files, we rarely move beyond changing the font or fill colour. But there is so much more to play with when you customise the colour. Read through the post, or jump to your favourite office suite – Google or Microsoft. Google Let’s start with Google, as it’s… Read more: Complete List Of Options Where You Can Customise the Colour
