Excel Table Based Formulas – another way of creating a formula in Excel

Excel Table Based Formulas – have you ever created a formula through an Excel table before?

Excel table based formulas: Excel iconThis 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

Excel table based formulas: screenshot of a standard symbol based formula

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:

  1. First create a Table from the range of cells (Use Insert Tab, Table)
  2. 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

Excel table based formulas: screenshot of the filled in formulas

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

Excel table based formulas: screenshot of tab with table name shown

For the formula for this SUM see below which refers to the salary table and the field New Salary

Excel table based formulas: screenshot of table with SUM formula for all salaries

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.