Excel Table Based Formulas – have you ever created a formula through an Excel table before?
This week’s hint and tip is on Excel table based formulas. Formulas are a very useful feature in Excel and are used by many every day. Most people know the ‘standard’ way to create a formula but there is also a way to create them through an Excel table. Creating formulas are covered in our Intermediate Excel training course but creating one through an Excel table isn’t so decided to do a hint and tip on it. We are going to go through it now below.
Characters used in creating the table based formulas
In this blog we shall have a brief look at a table based formula as opposed to the usual cell based ones you are used to. In the video we shall use the attached worksheet as an example. You can download the spreadsheet in the video by clicking here (clicking here will download a copy to your computer so you can try it out!). The spreadsheet with the formulas filled in can be found at the bottom of the post.
We shall use 2 characters not normally used in Excel:
[] Square Brackets
@ At Symbol
Above is a typical normal based example of a formula in D2 which gives Bill’s new salary.
We will now do the same using a Table based formula.
How to create a table based formula
Steps:
- First create a Table from the range of cells (Use Insert Tab, Table)
- Then in cell D2 you type =[@ and a range of fields appears from the table in a drop down list. This list contains options like Name, Salary, Increase, New Salary). From here double click on the one you want, in our case initially Salary, and then continue the mathematical calculation in the same way obtaining the formula below
When you press enter on D2 you get populated not just D2 but also D3 and D4.
An addition to this, we want a formula which sums the new salaries together and puts the answer in D6
(Note: I’ve named the table Salary_Table) – see below
For the formula for this SUM see below which refers to the salary table and the field New Salary
The video below goes through how to create the first lot of formulas in the spreadsheet attached here. (this spreadsheet is the same above but with the formulas filled in it). We hope that you find the video useful and enjoy learning about it!
Take a look below at the video to find out more!
We hope you liked this hint and tip on Excel table based formulas, why not take a look at our previous one on a searching for emails in Outlook?
For more information on Excel visit our website here.