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.
Sometimes for printing reasons, or just for clear visual display, all but the first entry of a series of repeating entries have been deleted. For example, as we look in column A, we don't see the department name ADC repeated in rows 3 through 6. We don't see Admin Training repeated in row 8 downward. And setting up data like this does make the data easier to read. But if we want to sort this data by Status or Benefits or Salary or any other column, once we do that, we don't have any way to return to this layout.
In other words, we actually need to fill in Column A with the data that's missing. So what we like to do is to have ADC appear here, here, here, and here. Now, this might be a huge list. It happens to be about 700 names. It could be 7,000, hundreds of thousands possibly. What we need to do is use a technique not so widely used that essentially allows us, first of all, to select the blank cells here, and then what we are going to do is write a formula for each of the cells that gets data from the cell above.
So let's start the process. And this is a pretty obscure technique, actually, but it's really powerful. I have run into situations like this a number of times where people do get list like this, they're all set to start to use the data, they do sort it, and they've got a disaster on their hands. Let's select Column A first. What we'd like to do here is to select just the blank cells. On the Home Tab in the Ribbon, the extreme-right button, Find & Select the binoculars button, there's a choice beneath it, an unlikely choice if you were looking for blanks, Go To Special. And let's choose Blanks. OK.
We've chosen all the blank cells. Right away some of you might be wondering, well, does it select blank cells below the data? So I am going to scroll down at the bottom here. Nope! Just down to the end of it. And that's a nice bit of built-in intelligence there. In other words, we are not selecting cells A743, 744, and so on. Right now, the only cells highlighted in this worksheet are the blank ones. Now, I am going to write a formula. Equal. I am going to press up arrow. So for this cell here, if I were to press Enter, this cell would contain ADC, cell A3 would.
But if I press Ctrl+Enter, every one of these blank cells is going to have the same relative formula in it. So in cell A4, we are going to be seeing a formula reading =A3, and cell A5 is going to say =A4. So we are putting the same relative formula in all these cells. As I press Ctrl+Enter, we are there. You can see what's happening. Admin Training, Audit Services, they're all filled in. Now before we proceed though, if we want to keep this data here--remember these are all formulas-- we need to select column A. And with the right-mouse button, we'll simply drag this into column B, then right back on top of itself.
Let go the right-mouse button, Copy Here as Values Only. A really powerful technique for getting this data into shape. Now we can print this data. We don't have to worry about sorting the data. No matter how we sort it, the information in each of the rows is together, and we won't have to recreate the information that we did in column A. It's a one-time cleanup, and it's very powerful.
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.