When you copy and paste data from a web browser to Excel, a number of maddening settings come along with the data. In Episode 362, I will show you how to turn off the wrap text feature and get the columns wide enough to fit the data.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Lots of times I'll take data from a webpage and move it to Excel, either using just copy and paste, or a Web Query.
Today I'm going to talk about some problems with a copy and paste method.
When you take the data from the web page and copy to Excel, if the web page is set up using tables, which most web pages are, those table settings will really kind of screw up the data when it comes into Excel.
Here, I’ve copied and pasted some information from my bank webpage into Excel, and it has turned on Wrap text for all of the cells.
Very frustrating kind of thing, I'd really rather be able to make the columns wide enough and have everything fit, you know, in a normal sized row.
So what we have to do is select all of the data, and then from the Format Cells dialog box, on the Alignment tab, there's a couple of settings there that will help us.
The first thing is the Wrap text setting, and you'll see that right now it's grey check-marked, which means that there's some cells where it's turned on, some cells where it’s turned off.
So the first click will turn it on for everything, and then a second click will turn it off for everything.
This is called a 3-state setting, and it can be either On, Off, or Mixed, so the first click turns it on, second click will turn it off everywhere, Click OK.
And now, the only problem is that our columns aren't wide enough, of course, we'll go to Format, Column, AutoFit, and it will make the columns wide enough.
So there you go, when you copy data from the website, it’ll often have Wrap text turned on.
Easy enough to turn it off, going to Format Cells, on the Alignment tab.
Great news, in Excel 2007 Microsoft realized that so many people are doing this all the time, the Wrap text button has been promoted, and it's right there on the ribbon.
It’ll just be one click away, you won't have to go to the dialogue anymore.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Lots of times I'll take data from a webpage and move it to Excel, either using just copy and paste, or a Web Query.
Today I'm going to talk about some problems with a copy and paste method.
When you take the data from the web page and copy to Excel, if the web page is set up using tables, which most web pages are, those table settings will really kind of screw up the data when it comes into Excel.
Here, I’ve copied and pasted some information from my bank webpage into Excel, and it has turned on Wrap text for all of the cells.
Very frustrating kind of thing, I'd really rather be able to make the columns wide enough and have everything fit, you know, in a normal sized row.
So what we have to do is select all of the data, and then from the Format Cells dialog box, on the Alignment tab, there's a couple of settings there that will help us.
The first thing is the Wrap text setting, and you'll see that right now it's grey check-marked, which means that there's some cells where it's turned on, some cells where it’s turned off.
So the first click will turn it on for everything, and then a second click will turn it off for everything.
This is called a 3-state setting, and it can be either On, Off, or Mixed, so the first click turns it on, second click will turn it off everywhere, Click OK.
And now, the only problem is that our columns aren't wide enough, of course, we'll go to Format, Column, AutoFit, and it will make the columns wide enough.
So there you go, when you copy data from the website, it’ll often have Wrap text turned on.
Easy enough to turn it off, going to Format Cells, on the Alignment tab.
Great news, in Excel 2007 Microsoft realized that so many people are doing this all the time, the Wrap text button has been promoted, and it's right there on the ribbon.
It’ll just be one click away, you won't have to go to the dialogue anymore.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
