Viewers: in countries Watching now:
In this course, Dennis Taylor explores the functions, commands, and techniques in Excel that restructure data, remove unwanted characters, convert date data into the desired format, and prepare data for efficient analysis. This course helps get data from a business management system file, other database software, a text file, or a poorly designed Excel worksheet into optimal shape for working with in Excel.
Trailing minus signs, although common in some accounting software, just don't work in Excel, and you need to get rid of them. And just as important of course is not only getting rid of them, but making sure that those values are turned into negatives. And there is a fast way to take care of this. Select the data, or in this case here let's select all of the data in Column T, and simply use a feature that's available on the Data tab in the Ribbon, Text to Columns, a great tool for splitting text into columns.
But here, we're going to take care of the data within the existing cells. We don't need an extra column to the right necessarily. First step in this wizard, simply choose Delimited. Move on to step two. Nothing really to do there. Go onto step three and click the Advanced tab. And the setting we're talking about might already be in place, but check it out anyway, trailing minus for negative numbers. Click OK, Finish, watch Column T, and you see what's happening.
That's a value now. I want to undo here and contrast the two in the following way as I press Ctrl+Z here. What if we had worked with this data and we simply tried to use math with it, this value times 2? Nothing happens. How about the rest of these in here? Wherever we don't have the trailing minus, it works just fine. So let's go back and do this again, what we've done earlier. Let's adjust these, get rid of the leading minuses. Back to the command Text to Columns, Next, Next, Advanced. The setting is all there anyway. Click OK, click Finish. There we go.
We've turned these into negative values, and the math calculations do work. And that could be a huge savings of time if you've got thousands and thousands of these. There are other techniques, too, involving the use of text functions, but this is by far the best way to take care of that problem of trailing minuses.
Find answers to the most frequently asked questions about Cleaning Up Your Excel 2010 Data .
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.