Tuesday, September 8, 2015

Using Ampersands in Excel


If you provide only numbers without much text in your spreadsheets, you risk poor communication.  With all of the (unfortunate) poor communication that we all experience in our business lives, it certainly behooves us to make our Excel masterpieces as clear and concise as possible!

One way to do this in Excel is to Mix Text with Formulas.  By using the Concatenation (via Ampersands) feature along with Formulas, you can build interactive messages within your reports, making them easier to understand, and eye-catching in the process.


To see where I’m going with this, Click and Enlarge the spreadsheet image below.  In this instance we are presuming 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, (Use Validation to make a quick dropdown list), to display the 4th Quarter Sales by Rep.

 
Now, let’s make it Really Cool!  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&“:”

This produces an Interactive Message that works with the dropdown box and the results of the formula used in cell G4Be sure to leave a Space after the end of the text string to create a proper sentence (you don’t want it to be clunky).

 Pretty cool, eh?  Try using Ampersands with Text Strings combined with formulas in your next report.  The result will be a Highly Interactive and Sophisticated report (that may even impress your boss…).  



No comments: