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
2. Type in the dollar sign ($) and press Enter
3. Select cell C2, type in the following formula and press Enter:
=($F$1&” “&B2)
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
5. Select cell F1. Type in R and press Enter
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.