Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to use concatenation to clarify assumptions

Summary 
In this video, we show you how to use concatenation and the TEXT function to clarify assumptions in a breakeven model. You can use the same approach to highlight assumptions in any model.
Video Transcript 

One of the most basic and useful features in Excel is the ability to concatenate values with text. Concatenate is just a fancy word for join. In this video, we'll look a simple way to use concatenation with the TEXT formula, to  highlight assumptions in a breakeven model.

This model calculates the breakeven point for a single product based on  assumptions about price, variable costs, and fixed costs. In addition, it shows operating income at different sales volumes.

If we change an assumption, the model recalculates.

In the sales volume table, it would be nice to show the values used for selling price, variable cost per unit, and unit contribution margin, so that current assumptions are always clear.

We could hard-code these values into the labels directly...But then the values won't change when assumptions are updated.

A better way to expose the values in the table is to add them directly to the labels using concatenation.

We can do this using the ampersand symbol. First, add an equal sign to turn the label into a formula. Then wrap the existing text in double quotes. Next, add an ampersand character and point to the cell with the correct value. The ampersand joins the two values.

We also need to add a space character inside the quotes, to separate the text from the cell value. And the @ symbol will make this more readable.

Now, if we change the selling price, the label is updated to match.

This works pretty well, but we can improve this approach by using the TEXT function to display values in currency format.

The TEXT function converts numbers to formatted text. You can use TEXT to format currency, dates, percentages, and any other number format that Excel understands.

The TEXT functions takes two paramaters: the number to format, and the number format to use.

In this case, we'll use a standard currency format, with commas, and without decimals.

Now we have a formatted value in our label. I'll fast forward here to update the other labels.

Now when assumptions change, the labels in the table are updated to match.

You can use this same approach to highlight assumptions in any model, simple or complex.

Author 
Dave Bruns
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables