August 31st, 2010
Tips, Tools and Tutorials
I love the ampersand. I prefer to use it whenever possible, but hitting SHIFT+7 isn’t always as convenient as typing out the word “and”. However, there are some great uses for that lovely little logogram, and not just for saving two more characters in your title tags. In Excel, the ampersand is a very useful tool, but shouldn’t be confused with the “AND” function. Whether you refer to “&” as &, & or U+0026, here are some helpful uses of the ampersand in Excel.
I used to wonder as a child why the ampersand looked more like the letter “E” when the word “and” and “ampersand” began with an “a”. Although technically the symbol does reflect the word “and”, the ampersand gets its funny “E” shape because it is a combination of the letters “E” and “t”, which is Latin for “and”. And the actual name of ampersand derives its background from the phrase “and per se and”. I won’t get into any more etymology so feel free to look up Ampersand on Wikipedia.
It is important not to confuse “&” with the “AND” function. The “AND” function is a logical function that is primarily used in conditional formulas (where you want to evaluate the data to see if it meets multiple criteria). However, there is a long-hand way of using “&” and that is by using the function known as CONCATENATE. Since concatenate is not a very common term, and because it requires more typing than simply hitting SHIFT+7, I prefer using “&”. Additionally, using “&” saves you from having to add parentheses, which can complicate nested formulas. Here’s an example of how to supplement the CONCATENATE function with a simple “&”. On that note, syntax in Excel is crucial, just as syntax is important in writing too, so it is important that you follow syntactic rules in Excel.
In the example above, you will see that the effect of CONCATENATE and “&” are the same. It joins two (or more) strings of text together. With CONCATENATE, you have to include parenthesis, and you can use commas (or “&” if you’d like) to separate each string. With ”&”, you don’t have to worry about spelling out CONCATENATE, or fuss with parentheses or commas. You might notice the “ “ in the formula. This is to designate a space between the two values. This is an important element for readability so don’t forget to use it. The other way around having to include “ “ is to provide a space at the end of each word, but that can get crazy as it can be difficult to see where trailing spaces exist in each cell. Let’s look at how we can include our own text in the formula to create a string of values.
=CONCATENATE(“Deadline for “,A2,” #”,A3,” is “,A4)
=”Deadline for “&A2&” #”&A3&” is “&A4
Since these are formulas, you can duplicate the formula and change the cell references to replicate this effect. In the diagram below, you can use the CONCATENATE function or “&” to join cell values as well as text. If you end up with a result of “Deadline for project #123 is 40306”, it is because the date is being acknowledged as a serial number by Excel. You can either change the format of the cell (A4) to “Text” or you can find a way around it by using a nested formula (see below). Nesting formulas are a lot like they sound, it places one (or more) formula(s) within another. If for instance, I wanted to use two different functions, let’s say CONCATENATE and PROPER, I could produce a string like the one below.
However, I wouldn’t need to use CONCATENATE if I just use “&” instead. There are many reasons to use nested functions, especially when you are working with several different types of number formats. But let’s say you wanted to use the Deadline string above; instead of having the date’s serial number show, you wanted an actual date. Here are a few ways to do it:
=CONCATENATE(“Deadline for “,A2,” #”,A3,” is “,TEXT(A4,”mmmm d, yyyy”))
=”Deadline for “&A2&” #”&A3&” is “&TEXT(A4,”m/dd/yy”)
Notice how the TEXT function is used to designate the format of the date. You can use different formats depending on what it is that you’re working with. This next example shows you how you can add the PROPER function to the string as well.
=CONCATENATE(“Deadline for “,PROPER(A2),” #”,A3,” is “,TEXT(A4,”mmmm d, yyyy”))
=”Deadline for “&PROPER(A2)&” #”&A3&” is “&TEXT(A4,”m/dd/yy”)
So, do you want to take a shot at something a little more complicated? Here’s an example of the use of the AND function as well as using & within a conditional formula.
Formula: =IF(AND(A2=”answer”,B2=1),PROPER(A2)&” “&PROPER(B2) &” Abracadabra!”,”No rabbits here”)
If you read the post about infographics, you might recall that you can assign a cell’s value within a shape. Here’s a way in which you can create a shape that provides you with the cell’s result in a shape.
Formula for the shape: =$B$3
And if you don’t want to see the cells that have the information, you can either place the data in columns or rows that are out of visual range (like placing the data in AA2:AC4), or you can move the shape outside of the columns with the information that you don’t wish to see (move to column D) and hide the columns that you don’t want to see. Like this:
Notice that columns B and C were hidden, and that the shape was moved to the right of columns B and D. This might be useful if you plan to skim through information at a later time and only want to see one bit of information (like a deadline message).
Lastly, here are some of my other favorite uses of the ampersand and nested formulas in Excel:
=”Report printed on “&TEXT(NOW(),”mmmm d, yyyy at h:mm AM/PM”)
The first formula helps to provide a sort of timestamp for your spreadsheet. If you’re making several revisions or drafts, this can be very helpful.
The second formula helps to convert a four-digit time value to the correct value with a colon. This can help to save time when filling out a time sheet, while also helping to provide a more correct time if you’re trying to perform any calculations using those time values. If you need to convert the time to an AM/PM format (i.e. 16:00 to 4:00 PM), make sure to change the cell’s format to the style you prefer in the Time category, or just make a custom number format like this: h:mm AM/PM;@ .
The third formula is helpful when you don’t wish to view an entire account number. Though this is specifically used for credit cards, you can edit the formula for a different number of digits. A telephone number would look like this:
In this example, you can either list the number (8001234567) and keep it in a plain number format (see A2) or you can format the cell to display the parenthesis and hyphen by changing the cell’s format to Phone Number (under the Special category).
There are no comments yet.