How NOT to use Concatenate Function to build Dynamic Text in your Excel Dashboard Templates

It may seem like a weird title, but I will explain.

As you build your Excel Dashboard, you will want to use text for data descriptions, titles, labels, lists of issues or risks and other text based information for your Dynamic Templates.

What is Concatenate?

Concatenate is probably the most used and the most well known function in Excel.  Frequently you will have two cells with text in them and you may want to combine the text into another cell.  For instance, you may want to combine a person’s first name and last name that are in 2 different cells.  You would use the Concatenate Function for this purpose.

Concatenate simply combines text from other cells into a new cell.  Or you can add other text that you directly put into the function like a comma or a space.  In the previous example, you may want to combine first and last name with a space in between or put last name first with a comma in between.   You can also put formulas into the Concatenate Function to combine it with the text as well.  For instance, you
may want to see if another cell has the salutation add it with a space before the first name and if not, put in nothing for the salutation.  This would be necessary if you wanted to not put an extra space in front of the first name when there is no salutation.

Here is what Microsoft Excel Help says about the Concatenate Function: The CONCATENATE function joins up to 255 text strings
into one text string. The joined items can be text, numbers, cell references, or a combination of those items. For example, if your worksheet contains a person’s first name in cell A1 and the person’s last name in cell B1, you can combine the two values in another cell by using the following formula:

Concatenate Excel Dashboard Example

Here is an example of Concatenated text that I have used in an excel spreadsheet in order to choose a location that will be plotted on a map.

Concatenate Sample Excel Dashboard Template
Example of Excel Dashboard with Concatenate

Here where the text that was used for the pick list above was generated:

Excel Concatenate Formula Sample for a Dashboard Template
Concatenate Formula Sample Excel Dashboard Template

I combined the text from columns A, B and C along with an “if” statement and text that I entered to give it a space between the text values.

You may say: “Now wait, I thought this was about the Concatenate Function and you didn’t use Concatenate in your formula?   What is up with that?”  (I did say in the title that this was how to NOT use Concatenate J)

Although Concatenate is a powerful function to combine text, it may be a waste of keystrokes.

You can get the same result simply creating a formula using the Ampersand (&) and let Excel work its magic!

Here is an example:

Concatenate Sample Excel Dashboard Template
Concatenate Formula Excel Dashboard Template

The cell of E6  =  =B1&” “&B2&”, “&IF(B3=”CEO”,”Chief Executive Officer”,B3)&”: “&TEXT(B4,”###-###-####”)

Notice that I didn’t use concatenate?  Use the ampersand just like you would with the commas in the Concatenate function.

These quasi-concatenate formulas can be used in many places throughout your dashboard.  I use them as Dashboard dates like when the report was created or combined with text to show the date of the data, as labels and titles in charts, the possibilities are almost endless.  Anytime I am putting text in a spreadsheet, I typically make some of the text dynamic so that it changes automatically and I don’t have to remember to change the values every time I save or print a new dashboard.

Here is one example that you can use today in your Excel Dashboard Templates:

=”Dashboard Report as of: “&TEXT(TODAY(),”mm/dd/yyy”)

This will display this text:      Dashboard Report as of: 07/15/2011

So that when you print the dashboard, it will show today’s date.

I have also showed you how to save several keystrokes by typing in a normal formula and then using the “&” Ampersand instead of using the
Concatenate function.  Once I found out this technique, I have not used the Concatenate function (not even once).  Why would I want to waste all that typing for such a long word when I can just use = and &?

So I ask you: Do you still use the Concatenate Function in your Excel Dashboard Templates?  Is there a reason to use Concatenate over just a formula with & Ampersands?  I haven’t found one where Concatenate works better than just the Ampersand.  Have you?

Let me know in the comments how you use Concatenate and if you will not use it in the future.