Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Sometimes when you're working with lists, the information that's included in a particular column could really be broken out into two columns to make it a little bit easier to sort through. We have an example of this here in column A, where our item description is really a mixture of the exterior category and the item type together. What I'd like to do right now is to break those two out into two separate columns, so that I can sort all the information in a much easier fashion. to do that I need to move over to my Data tab, and a I need to allow for an additional column in my data.
First thing to do is I want to add a column right here in column B. I select column B, to identify where I want to put that that new column, right-click and select Insert. And the information is pushed aside so that I have now a nice, new column in order to work with. The next thing I want to do is I want to select the column that I'm interested in working with, and I go to the Text to Columns command. By clicking on it, it opens up a Wizard that will now help me break the information included in that column out.
I identified that the information is Delimited, and what that means is it's broken up by a particular common character that I'm going to identify to the Excel spreadsheet, in order to chop up the data. I also have the option of using a Fixed width break down, where I will identify where I'm going to break the data. But in this case, because I have a common hyphen between most of my items, I'm going to use a delimited character. Click on the Next tab, and in this screen, I'm going to identify what kind of delimiter I'm working with. I'm not working with tabs so I'm going to deselect that, and I'm going to select Other.
And in the other column, I'm going to put in a hyphen. Notice, as I do that, the preview box below breaks out my data for me and this is breaking it out exactly the way that I want to. It's breaking out one area for the item category and the next for the item type. And if you look down in this preview area, it's going to give you a good idea of whether or not you've made the right delimiter choices. I click on the Next button to move it along, and on this particular page, it's asking me how I want to format the information. I'm going to just leave it at General, and it's going to identify where the information will go.
I then click finish, it asks me, do I want to replace the contents of the destination cells, and I'm going to say, yes. This is also something that you want to be aware of if you haven't included an additional column, you will overwrite the information that would be previously sitting in column B. So take a moment when you see this message to make sure you've got the space to put information. If you do, click OK. And in one click of a button, the information is broken out. The last thing we need to do here, is add a new column header, up here in column B. I'm going to grab that easily by going over to column A1, using my arrow key to move over to column B1, and type in Item Type.
Clicking on the check box secures that information in that column. We're done! Now we're on to the next piece, where I'm going to actually group the data and really see the analysis I can use with Excel 2007.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 86596 Viewers
80 Video lessons · 135941 Viewers
59 Video lessons · 54684 Viewers
52 Video lessons · 68576 Viewers
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.