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 G4.  Be 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…).  

What Day Is It?

There are often times when making your data a bit more human is a desirable goal.  For instance, by including the Weekday, as well as the Date in your database, you can bring a social element into your information you are providing.  After all, the difference between Monday and Friday may have great significance (especially if you have the weekend off…).

By combining the WEEKDAY function and VLOOKUP function with a simple lookup table, you can easily provide this additional element of information in your reports or analysis.

The WEEKDAY function extracts the weekday out of the date as a number from 1 to 7, with options of which weekday to start on.  The syntax of the WEEKDAY function is quite elementary:

=WEEKDAY(Date,[Option]) 

Without the optional second argument, the function will return a number 1 to 7 (the same if you had entered the number 1…) for the Weekday of the date in the first argument, (Sunday being 1). You can, however, change the results by using any of the following as the second argument:

1:	Numbers 1 (Sunday) through 7 (Saturday).
2:	Numbers 1 (Monday) through 7 (Sunday).
3:	Numbers 0 (Monday) through 6 (Sunday).
11:	Numbers 1 (Monday) through 7 (Sunday).
12:	Numbers 1 (Tuesday) through 7 (Monday).
13:	Numbers 1 (Wednesday) through 7 (Tuesday).
14:	Numbers 1 (Thursday) through 7 (Wednesday).
15:	Numbers 1 (Friday) through 7 (Thursday).
16:	Numbers 1 (Saturday) through 7 (Friday).

Now let’s look at a straightforward example by combining WEEKDAY and VLOOKUP.  In the illustration below, we have put the following formula in cell C3 and copied it down to cell C9:

=VLOOKUP(WEEKDAY(B3, 1),$F$4:$F$9, 2, FALSE)

This unpretentious combo formula can add the Weekday to your data, and that may just be Good Information!