You’re probably aware that when distributing reports via email, you can reference cells from the layouts you’re sending in your email body. This is useful, for example, if you want to provide a summary of your report for your recipients.
This is useful, for example, if you need to provide a summary of your report for your recipients, and want to make the financials in your report easy to read and understand. In this tip we’ll show you how to use the Excel TEXT function, to change the format of the numbers in your email.
The Excel TEXT function has the following syntax:
=TEXT(value, format_text)
Value is required and can be either a numeric value, a formula that equates to a numeric value, or a reference to a cell containing a numeric value. Format_text is also required and is a numeric format provided as a text string enclosed in quotation marks. To find more information on the TEXT function, take a look here.
In this example I have a monthly Income Statement that I’m sending to a group of managers. I want to summarise the Actual, Budget and Variance values for the month in the body of my email and provide them in a readable financial format.
To do this, follow these steps:
- Find an empty area in your layout that is close to the values you want to reference.
- For each value, enter the TEXT function in an adjacent cell and reference the value in the TEXT function.
- Include the numeric format you would like in the TEXT function.
In my example, the values I want to reference are in cells D34, E34 and F34. I have used the TEXT function in cells D38, E38 and F38 and have referenced the corresponding values. I have also included the format “$# ### ##0”. If you would like more information on formatting options, take a look at the link provided above. To save time, you could also enter your function for the first value, and then use Excel’s autofill feature to copy the function across. If you don’t want your functions to appear in your report, you could color the text white or hide their rows or columns.
- Now, in your distribution instruction, reference the cells with the text function. The values will be pulled through together with the formatting you have specified.
In case you’re not sure how, use the fx button in the formatting menu of your email body to add your cell references. You can also do this by right clicking in your email body and selecting Insert and then Cell Reference. Then use the Preview Cell References button at the bottom of your instruction window to see what the values will look like when the email is sent.
