Are you looking for a way to add mathematics and other formulas to tables in Microsoft Outlook?
You may be aware that formulas let you carry out calculations using cell values, operators, and functions.
But did you know you could use these same formulas within your Outlook table too? It’s an easy process once you know how.
In this article, we’ll explain “how to add formulas to tables in Microsoft Outlook” so that even the toughest calculations become simple.
Insert a Table in Microsoft Outlook
Once you have set up your table of data in your email, you can proceed to the next section where you can add the formula to the table of data.
If you have not inserted the table yet, there are a couple of methods to insert table in Outlook email body.
Add Formula to a Table Cell in Outlook
Let’s take an example to add a formula in the Outlook table to total, click inside the cell where you want the formula to go.
The next step is Go to the layout tab, some of us are unable to find the layout, click anywhere in the table and the layout tab appears on the top ribbon.
Click on the layout and from the data section, pick “Formula.”
Now, you’ll see the Formula window pop open to create your formula. We all usually walk this dialog box in Excel.
In Outlook, you may see a commonly used formula set up for you. In parentheses, you will see the positional argument. In our case, Outlook identifies that we want to SUM all the cells above.
In case you are wondering to calculate or use a different formula, Outlook allows you to figure out your required formula from the list below: and paste it into the Formula section.
| Type this in the Formula box | To get This |
|---|---|
| =SUM(ABOVE) | Will sumup the above the cell |
| =SUM(BELOW) | Will sumup below cell |
| =SUM(ABOVE,BELOW) | Get Total of both above and below |
| =SUM(LEFT) | Get Sum results of figures on the Left of the cell |
| =SUM(RIGHT) | Get Sum results of figures on the right of the cell |
| =SUM(LEFT,RIGHT) | Will get sum of both left and right |
| =SUM(LEFT,ABOVE) | Will get sum of left and above |
| =SUM(RIGHT,ABOVE) | Will get sum of right and above |
| =SUM(LEFT,BELOW) | Will get sum of left and below |
| =SUM(RIGHT,BELOW) | Will get sum of right and below |
Not only SUM you can also use basic operators like MIN, MAX, AVERAGE, or COUNT, the position of the cells in the formula should be indicated in parentheses.
Positions such as ABOVE, BELOW, LEFT or RIGHT can be used for these cells. Additionally, you can use ones like LEFT, RIGHT for cells to the left and right, LEFT, ABOVE for cells to the left and above the cell, and RIGHT, BELOW for cells to the right and above.
Check Out the Number Format in Outlook
By default, you will get the result in a simplified Number Format. From the drop-down list, you can select a specific number format to use, such as a percentage, a currency, or a decimal place, if you wish.
Paste Function in Outlook Table
The paste function consists of multiple functions in the drop-down list in place of typing a formula. You will get options like ABS for absolute value and TRUE for evaluating an argument.
In the case of using a function, it will be populated for you in the Formula box. Then you will need to end the formula with your positional argument.
The paste function also allows you to add multiple formulae at once.
Updating all fields
When you add a formula to an Outlook table and then make changes to the data in the table, you will need to update the formula manually to reflect the new data. This is different from Excel, which automatically recalculates formulas based on the changes you make.
There are a couple of methods to do it:
The first method is to remove the formulae and reprocess to add once again that will consider the changed data into consideration.
Second method update all fields in all tables, Press Ctrl + A to select all or select the figure, which is the result of the calculation, and then Press F9.
The third method to update the formula in a table, select the figure, which is the result of the calculation, and then click the mouse right button and click the update fields.
It is worth noting that in Outlook, the result of a formula is only calculated when the formula is inserted and will not be available for the recipient of the email to edit once it is inserted.
Table Formula Syntax
Each column and row of an Outlook table is identified the same way cells in Excel worksheets are, so the cell reference for each column and row is the same.
As a result, you can create calculations that refer to cells (such as =B2+B3) when you insert a table in Word. The first column in a Word table is column A and row 1 is row 1, so the first cell is identified as A1.
Few Examples
Enter AVERAGE (ABOVE) and click Ok, you will get the final average of all the cells above the formulae, you can also specify the cell in case you don’t want to take an average of the entire above entries [=average(B1,B2,B3)].
Similarly, COUNT(LEFT) and click Ok, you will get the count figure of all the cells on the left of the formulae, you can also specify the cell manually in case you don’t want to take the entire row entries [=COUNT(B4,C4,D4)].
Next is MAX Function, which will calculate and show you the maximum figure as result.
There are numerous parameters you can insert formulae in Outlook Table and get results accordingly. Most users use Microsoft Excel to create a table and copy the table directly using CTRL+C and paste it into the Outlook body.
It’s also possible to do more complex calculations in Outlook tables by using the IF function =IF(test,true,false). You can use =IF(B7>=1000,0,70) to calculate a shipping cost of 70 if orders are less than 1000. With the IF function, you can also use the AND and OR functions.
Wrap Up
In conclusion, this article has shown you how to add formulas to a table in Microsoft Outlook. You can use these formulas to perform various calculations on your data. You can also use them to create charts and graphs that visually represent your data.
With a little bit of planning and care, you can use formulas to make your Outlook tables more efficient and effective.