Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In Excel for Mac 2011 Essential Training, author Curt Frye gives a comprehensive overview of Excel, the full-featured spreadsheet software from Microsoft. The course covers key skills such as manipulating workbook and cell data, using functions, automating actions, printing worksheets, and collaborating with others. Exercise files accompany the course.
Many times when you use Excel, you'll have to draw information from more than one cell to get the result you want. That's no problem when you want to add numbers together; you can just use a formula. But what if you want to combine several text values? For example, suppose you have a customer's first name and last name, but you need to combine it into a single cell, so that both the first name and last name are there. Other than cutting and pasting or re-typing, you can use a process called concatenation. Concatenation simply means adding one set of characters onto another. If I were to do it by hand, let's say with the name John Smith, I could just type in 'John Smith,' press Return, and I'd have the name.
But let's say that I want to create a formula that will add the value from A4 to the value in B4 to create the name for me. If I want to do that, I can type an Equal sign to start creating the formula, and then there are two ways to do it, and I'll show you both of those in this movie. The first one is to use the concatenate function. So type in 'con,' and concatenate is first on the list. I'll press the down arrow to select it in the formula Auto Complete list. Press Tab, and I get the entire function, plus a set of open and close parentheses.
Now I can type in references for the text that I want to include. In this case, it's John Smith. John, the first name, is in cell A4. Then I type a comma. But now if I were to type B4, the reference that contains the value Smith, it would be John Smith, and there would be no space, as is traditional when you write names. That means that I need to add a space, and here's how you do it. First, you type a double quote, then you type the space, and then you close it with another double quote, and then a comma. What you just did was tell Excel that I want the literal string of a space to come after whatever value is in cell A4.
Then you can type in the reference to sell B4, which contains the last name. Everything looks good, press Return, and I've got the name. You can also do the same thing using numbers and combine those with text, or with other numerical values. So let's say, for example, that I want to have the address 602 Bainbridge Street in the same cell. I can do that by creating equal, and here's where I'll show the second technique for concatenating values. Rather than to type out concatenate the whole way, you can use a formula such as this.
So we have E4, which is the cell that contains the first value, and then you can type an ampersand, or the and sign, a space, and again, this is a literal string. I have a double quote, a space inside of it, and then a double quote mark again, ampersand, and then F4. When I hit Return, Excel creates that value. I'll create one last example here. So to create this address on a single line, type = A10, ampersand, and then double quotes, a comma, so that I have the comma after Portland and then a space before OR again.
So I'll type comma, space, close the quotes, ampersand, B10, ampersand, space - again, as a literal string, so the state and the postal code don't run together - ampersand and C10. Hit Return, and there you have the entire last line of the US address. So when would you use the concatenate function, and when would you use the ampersand? Well, it's a matter of personal preference.
Frankly, it comes down to that. That's why I showed you both. It seems to be about an equal split and people I talk to. I personally prefer to use the ampersand. The problem is that when you use a bunch of ampersands, and you have all these other spaces, it can take up a lot of space on your formula bar, and after a while it can be tough to track exactly what's going on inside the formula. If you're concatenating a lot of values, you might find it easier to use the concatenate function. So play around with both, see which one you like better, and use that one in the future.
Find answers to the most frequently asked questions about Excel for Mac 2011 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.