Viewers: in countries Watching now:
In Excel 2010 Essential Training, Bob Flisser demonstrates the core features and tools in Excel 2010. The course introduces key Excel skills, shows how to utilize these skills with in-depth tutorials on Excel functions and spreadsheet formatting. It also covers prepping documents for printing, working with large worksheets and workbooks, collaborating with others, using Excel as a database, analyzing data, charting, and automating and customizing Excel. Exercise files are included with the course.
In this worksheet, we have people's names split into two columns and you can see here in Column A we have people's last names and in Column B we have people's first names. What we want to do is we want to combine first name and last name in the same cell. Well, we need some space to do that. So before anything else, let's go and insert a new column. So put your mouse pointer here on the header of Column C, so your mouse pointer is that down arrow. Click the right mouse button and from the pop-up menu, choose Insert. You can just click over here on what's now C5, so we have some room. Now there is sort of two ways of doing it.
We can do it with the formula. We could also use a function, and there is a function called Concatenate. I think the formula is a little easier to use so we'll do that. Also, I am going to do the exercise wrong first, and the reason is that you have to be careful with spaces, and I want to show you what happens if you are not careful with spaces. So this is a simple formula. So it's starting in C5, just type your equals sign, and we are going to say equals First Name. Now keep in mind, we are not adding numbers, we are simply stitching things together. We are combining things so you don't want to use a plus.
You want to use an ampersand. You can find the & on your keyboard if you press Shift and the 7 in the top of your keyboard. So I am going to type B5& and then A5. So I am going to say take the content of Cell B5 and append the content of cell A5. And I'll just press Ctrl+Enter, so I can enter and stay in the same cell. We don't have the space. There is a problem. Now put your mouse pointer on that Auto Fill dot, see your mouse pointer becomes a crosshair. When you get the crosshair, double- click and it fills into the bottom.
Well, we certainly appended first names and last names all right, but we don't have any spaces there. So now we are going to delete it and we'll do it correctly. So you can simply drag down to the bottom or again hold down Ctrl+Shift and press the Down Arrow key. So we have the whole column selected and just press Delete. Let's go back up to C5 and now we'll do it with correct spacing. So type your equals sign, again click the Last Name, type an ampersand. Now we need to put in a space and to put in a space, what we do is type a double quotation mark, literally type the space, hit the Spacebar, and close the double quote, and now another ampersand and now click the last name.
So we are saying take the content of B5 and append a blank space and then append the content of A5. Again, hold Ctrl and press Enter so now we have first name, space, last name, put your mouse pointer on that dot in the lower-right corner. When your mouse pointer becomes a crosshair, double click, and now we have them all filled in. That's great but we are not quite done yet, because we have our last name and first name in these columns and now we have the full name here. We don't really need Column A and Column B. But we can't just delete the columns because if you look up here in the Formula Editing bar, these are formulas and if we remove the columns, right now these formulas are going to give us error.
So what we have to do is we have to convert these formulas to the actual text that's displayed and we do that with copy and paste. So once again, let's select this row,. Again, you can drag down or press Ctrl+Shift and the Down Arrow key. I am just copying to the Clipboard. I'll press Ctrl+C and up here in the Home tab click Paste. And from the pop-up menu, under Paste Values, choose this first item here and you can press these to get rid of the marching ants. Now when you click in any of these names, you can see up here in the Formula Editing bar these are actual names.
Now we can safely remove Column A and B. But first let's take that Payroll and just put your mouse pointer on the edge of the border so your mouse pointer is a four-headed arrow. Drag over here. Maybe you take this logo and drag over for safekeeping. Put your mouse pointer in the header of Column A so you get that down pointing arrow. Click and drag over to Column B. Make sure your mouse pointer is still that down arrow. Click the right mouse button and Delete and we have deleted those columns, and now just deselect. Now you could adjust. Move the logo.
You can click on the graphic here and resize the graphic so it looks good. Now, when you click any of those names, you can see up here in the Formula bar these are regular names. So if that's something you ever need to do, you see it's really not that bad. You just got to be careful of a couple of gotchas.
There are currently no FAQs about Excel 2010 Essential Training.
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.