Thursday, June 12, 2014

Text + Formula

Better and more powerful communication is always a worthy goal.  This is, of course, just as true in Excel as in other business venues.  One way to do this is to Mix Text with Formulas

By using the Concatenation (simply done with Ampersands) feature along with Formulas, you can build interactive messages within your reports, making them easier to understand and less likely to incur human error.

Using the example illustrated below (click on it to enlarge it), let’s say that you have a table of data and a formula that returns the sales for the 4th quarter in cell G6.  The formula in G6 interacts with the dropdown box in cell C4, (dropdowns are a snap to create using a Validation list…), to display the 4th Quarter Sales by Rep.

You could stop there and hope that the users of the report understand what is being shown, or you could create a piece of Excel Magic in cell F6

As you can see in the formula bar, a Text string is used in conjunction with the name being shown in the dropdown box in C4=“4th Quarter Sales for ”&C4&“:”

The outcome is an Interactive Message that works with the dropdown box and the results of the formula used in cell G4Note:  Leave a space after the end of the text string so as to mimic a proper sentence.

As illustrated, try using ampersands with text strings combined with formulas in your next report.  Your users will thank you!

No comments: