Need to display various currencies on your report? Use the CONCATENATION formula

Formatting cells allows you to change the way data is presented, without affecting the contents of the cell.  Say for example you need to create a sales report to display the value of sales in various countries in each country’s currency, you can apply the CONCATENATION formula, instead of having to manually change each cell to display the relevant currency for each country

Download the sample workbook to practice.

Applies to: Microsoft Excel 2007, 2010 and 2013.

In this example, we’ll apply the CONCATENATION formula to help change things. We’d like to add different currency symbols to our regional sales numbers, however, to avoid manually changing each cell in column B, we’ll use the CONCATENATE function to link a specific cell to the sales values, allowing us to update one cell with a new symbol without changing the sales values.

1. Select cell F1

Microsoft Excel Concatenation Formula _EOS 1

2. Type in the dollar sign ($) and press Enter

Microsoft Excel Concatenation Formula _EOS 2

3. Select cell C2, type in the following formula and press Enter:

=($F$1&” “&B2)

Microsoft Excel Concatenation Formula _EOS 4

NB: there is a space between the two inverted comma characters

4. Select cell C2. Use the Auto Fill handle to drag the formula to C5

Microsoft Excel Concatenation Formula _EOS 5

Microsoft Excel Concatenation Formula _EOS 6

5. Select cell F1. Type in R and press Enter

Microsoft Excel Concatenation Formula _EOS 7

Note that the symbol in front of the numbers in column C has now updated.

While conditional formatting and custom format syntax rules are effective, there are times when these features can’t help with the representation of data. Clever use of the CONCATENATE option can help you get around this.