K has an line at the bottom of one cell. How can we remove it?
Other questions here:
Why does AutoFit Row Height not work in Excel?
Why can't I delete this border in Excel?
Deleting drawing objects in Excel?
Borders using Conditional Formatting in Excel?
How to remove page breaks in Excel?
Paste Picture Link in Excel?
Why is there one extra line of whitespace at the bottom of my cell?
How do I manually change row height in Excel?
Thanks to YouTubers: DropMeSort, Finnur Torfi Gunnarsson, Rico S., and Vesa Ruusunen.
Thanks to Debra Dalgleish at Contextures.
Table of Contents
(0:00) Remove the Line at the bottom of a cell
(0:19) Use Chapter Markers
(0:51) Remove Border
(1:53) Remove Conditional Formatting
(2:59) Click on the Drawing Object and Delete
(4:11) Reset all Page Breaks
(5:20) Remove Linked Picture
(6:41) Find a channel e-mail adddress on YouTube
(7:00) Check Print Preview
(7:21) Toggle off the Gridlines
(7:51) Change the row height
(8:59) Row height by dragging
(9:20) Alt+Enter at End of Cell?
(10:05) Wrap Text when too narrow
(11:21) Explicit row height?
(11:35) Debra Dalgleish Explains it
(12:12) Failure in Active Listening
Other questions here:
Why does AutoFit Row Height not work in Excel?
Why can't I delete this border in Excel?
Deleting drawing objects in Excel?
Borders using Conditional Formatting in Excel?
How to remove page breaks in Excel?
Paste Picture Link in Excel?
Why is there one extra line of whitespace at the bottom of my cell?
How do I manually change row height in Excel?
Thanks to YouTubers: DropMeSort, Finnur Torfi Gunnarsson, Rico S., and Vesa Ruusunen.
Thanks to Debra Dalgleish at Contextures.
Table of Contents
(0:00) Remove the Line at the bottom of a cell
(0:19) Use Chapter Markers
(0:51) Remove Border
(1:53) Remove Conditional Formatting
(2:59) Click on the Drawing Object and Delete
(4:11) Reset all Page Breaks
(5:20) Remove Linked Picture
(6:41) Find a channel e-mail adddress on YouTube
(7:00) Check Print Preview
(7:21) Toggle off the Gridlines
(7:51) Change the row height
(8:59) Row height by dragging
(9:20) Alt+Enter at End of Cell?
(10:05) Wrap Text when too narrow
(11:21) Explicit row height?
(11:35) Debra Dalgleish Explains it
(12:12) Failure in Active Listening
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2527. Remove the line at the bottom of one cell,seven ways.
Now, hey look, just a quick note here.
If you got to this video because you found it while searching and you have a line at the bottom of the cell that you're trying to remove.
Unfortunately there's seven different possibilities.
Hover over the bottom of the video to see the chapter markers.
It's hard to say which one is going to solve your problem.
For the people who regularly watch this channel who are helping people with Excel over the phone.
I was amazed at all the possibilities here. Things I had never heard of.
And it's tough when you can't see the worksheet But in this case it was tough even when I could see the worksheet.
So as you watch the video down in the YouTube comments, let me know when you solve the real problem.
And for anyone running a dissertation on communications, boy, what a great case study this'll be. Great question from YouTube.
I have an empty line inside one of the cells, right?
I'm like, "Okay, 80% chance it is a bottom border".
This happens when someone selects a cell, opens this border dropdown, and goes to bottom border.
It can also happen if they select the cell below it and choose the top border, right?
Just to be completely safe, select all of the cells that potentially have a border, press Ctrl + one, which opens format cells.
In format cells, the fourth tab across the top is the border tab.
If you just simply press this big button that says none, and then okay, that should remove all of the borders.
The odds say 80% chance that line is coming from a border.
Try this. If it doesn't work, reply back and let me know.
Yeah, no, it didn't. It's not a border.
Thanks to dropmeshort and Finnur for this possibility I never heard of.
So here I have borders, at least they appear to be borders.
When I come into no border, they don't go away. All right, what happened here?
Someone took the spreadsheet, did conditional formatting, highlight cells that are equal to maybe a one let's say, and then a custom format and applied the border as part of the conditional formatting. Click okay, right?
And see now all of those borders will not respond to no border.
The solution, if this is it, is go to, well first select all the cells that potentially have the border. Conditional formatting.
Clear rules. Clear rules from selected cells.
If you didn't build the spreadsheet, it might be good to go into manage rules and see if there's other rules there that you don't want to delete.
I mean, you could choose a rule one at a time and choose delete rule.
All right, is that it?
All right, my third possibility, is it a drawing object?
Did someone go to insert, shapes, choose a line, and then very carefully using the Shift key to make sure that it's straight and maybe even the Alt key to keep it just the bottom of the cell, create a line like that.
Now this particular line ended up in blue, but it's possible for them to go in, the shape out line and choose black.
And it could be all different kinds of lines, it could be different colors, it could have different weights, it could have an... So it could be a thick line like that.
It could have an arrow at one end, right?
But if it's looking like a border, then it's probably just the normal weight sitting down there.
And of course, remove borders won't do anything there.
Well, just try and click on the line.
If you just click on it and you get those little dots on either end which are the resize handles, then you know it's a drawing object.
At this point you would just press Delete and it would go away.
Okay, that's not it. Could it be some sort of a page break?
Now, page breaks will happen at the bottom of a page, but it's also possible that someone used Alt + I and B or here on the page layouts tab, breaks, insert a page break see, and that creates a line.
And I suppose if you had a really wide cell like that, you would perceive that as a line at the bottom of the cell. If that's it, boy, to get rid of them, let's see.
Do they have remove page break, or reset all page breaks might do it.
To remove the page break is funny, you have to go to the exact right spot, so that page break is above row seven.
Even though you're seeing the line at the bottom of six, the page break lives in row seven and so that's the cell, breaks, and remove page break.
I hate that, you would think you could just choose all of the cells like this and breaks, remove page break, but that doesn't remove it.
You have to go to the cell just below the page break, make sure you're in column A and breaks, remove page breaks.
Is that it? Okay, now we're down to the desperation, right?
If this is it, someone evil is playing with you, right?
Let's just come to any other cell far, far away from the range that you're looking at.
In this empty cell, they apply a top border like this, see?
And then they select that cell, CTRL + C, and then they come here just below your data and they do a special paste called a linked picture, which puts a picture of whatever I copied there in cell A10.
And that would give the perception of a line at the bottom of the cell.
Again, when you try and click on it, you'll tell what's happening here because it's not just a line, it's a little rectangle.
And to get rid of that, you would just press delete.
Well, once you've stumped me, I turned to the smartest people I know, the people at YouTube.
Put this short up and said, "Hey, what could it be?" Rico, it's not screen wipes, you're a funny guy.
Vesa said, "Can we download that file somewhere?" So went back and said, "Hey, can you change any confidential information and email me the workbook?" It's always easy to find me.
If you're watching one of my videos, click on my name here.
"Welcome to my YouTube channel, I'm Bill".
Click on about, and click this button to view email address. I wish more people had this turned on.
I could just write to you directly and see a screenshot of what's going on.
Great, all right, so the workbook arrives and I'm just frustrated because there's no line at all, right?
And I even try to go to print preview, file print to see if I'm getting a line there that is only in print preview.
I'm like, "Oh shoot. What version of Excel are you on?
It's not showing up in mine". I said, "The only lines I'm getting are these grid lines.
You're not talking about those".
If you're talking about those, you can get rid of them with the grid lines. And then finally, the aha moment.
When I heard the word line, I was thinking border, right?
But what we're talking about is that all of the here cells, the text completely fills up the cell, but in this cell, there's one cell where there's an extra line at the bottom, there's white space that shouldn't be there. Oh my gosh.
All right. So it took forever to get here.
But the deal is Excel has always had a problem with row heights when we're using Alt + Enter between the lines.
I still haven't figured out what can set it up, but it's very frustrating that when we come into home, format, autofit row height, it doesn't see it, right?
It thinks it needs to be taller.
And this goes, I mean, I remember reading about this in a Bob Umlas article back in the '90s, so it's been there forever.
How do we get rid of the extra line at the bottom of the spreadsheet?
Well, let's look at this one. This one has five bullet points.
1, 2, 3, 4, 5. This one has room for six.
So we come up here and go to format, row height, and see what the correct row height is.
75, good, click okay.
Come down here, and there's actually two answers to this. Let's make a copy.
The first answer is select the cell, go to format, row height.
Currently set to 90, we'll just go to 75 and click okay, and the line is gone.
Or the other method is right here.
Now right here between the seven and eight, all of these little borders are clickable, right?
So you get there, right there between the seven and eight, and you click.
Just click right there and then very carefully drag up and you can see two things.
The height and the pixels so we're trying to get to a height of 75 or just wherever it looks right and you're good to go. All right.
And then the question, what caused this?
What caused format autofit row height to not work on this cell? We come here.
Type line one, Alt + Enter. Then line two, Alt + Enter, and then Enter, right?
If that was the situation, then we would ask for the code of the right of that, comma one, and it's going to be 10.
When you press Alt + Enter, you're putting a character code 10 in there. And how do we oversee that?
Yeah, when we're I don't know, press F2 to edit the cell I suppose, and then backspace.
As I'm thinking through what could cause this. All right, so what if we did Alt + Enter?
Okay, when I press Enter, the text that I type is too wide for the cell and that's going to spill like that, right? This happens to me all the time.
I make column R wider and then the row height is broken, right?
That actually is the Bob Umlas article from the '90s that I remember.
Although it's leaving the line at the top of the cell instead of the bottom of the cell.
But that would be easy enough to correct there, right? Just going to the top line.
Although I mean, look, that just got fixed with top align.
And that would be fixed with autofit row height.
Then I wondered, okay, what if someone explicitly went to row height and changed the row height to something taller, top align, and then we come back to format, autofit row height.
It still corrects.
I don't have an explanation for why K's cell is stuck at 90. It still doesn't work.
It was Debra Dalgleish from Contextures who figured this out.
She said the problem is that we're too close to the edge, the right edge of the cell, and when that happens, Excel seems to overcompensate and we end up with an extra row.
Debra said we would just make this a little bit wider and then autofit row height would work and we're fine then, how crazy is that?
Still don't know the underlying bug there, but that at least explains it. Got an extra line at the bottom of your cell?
One of these seven things might solve your problem.
If not, let me know down in the YouTube comments below and we'll try and figure it out.
Well, some communications PhD can do their dissertation on my failure to actively listen in this video.
Here's the question, down in the YouTube comments below, when did you realize that the line was talking about the white space instead of a black horizontal line?
Oh, hey, I want to thank K for her awesome patience in trying to help me understand what she was saying. I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
Now, hey look, just a quick note here.
If you got to this video because you found it while searching and you have a line at the bottom of the cell that you're trying to remove.
Unfortunately there's seven different possibilities.
Hover over the bottom of the video to see the chapter markers.
It's hard to say which one is going to solve your problem.
For the people who regularly watch this channel who are helping people with Excel over the phone.
I was amazed at all the possibilities here. Things I had never heard of.
And it's tough when you can't see the worksheet But in this case it was tough even when I could see the worksheet.
So as you watch the video down in the YouTube comments, let me know when you solve the real problem.
And for anyone running a dissertation on communications, boy, what a great case study this'll be. Great question from YouTube.
I have an empty line inside one of the cells, right?
I'm like, "Okay, 80% chance it is a bottom border".
This happens when someone selects a cell, opens this border dropdown, and goes to bottom border.
It can also happen if they select the cell below it and choose the top border, right?
Just to be completely safe, select all of the cells that potentially have a border, press Ctrl + one, which opens format cells.
In format cells, the fourth tab across the top is the border tab.
If you just simply press this big button that says none, and then okay, that should remove all of the borders.
The odds say 80% chance that line is coming from a border.
Try this. If it doesn't work, reply back and let me know.
Yeah, no, it didn't. It's not a border.
Thanks to dropmeshort and Finnur for this possibility I never heard of.
So here I have borders, at least they appear to be borders.
When I come into no border, they don't go away. All right, what happened here?
Someone took the spreadsheet, did conditional formatting, highlight cells that are equal to maybe a one let's say, and then a custom format and applied the border as part of the conditional formatting. Click okay, right?
And see now all of those borders will not respond to no border.
The solution, if this is it, is go to, well first select all the cells that potentially have the border. Conditional formatting.
Clear rules. Clear rules from selected cells.
If you didn't build the spreadsheet, it might be good to go into manage rules and see if there's other rules there that you don't want to delete.
I mean, you could choose a rule one at a time and choose delete rule.
All right, is that it?
All right, my third possibility, is it a drawing object?
Did someone go to insert, shapes, choose a line, and then very carefully using the Shift key to make sure that it's straight and maybe even the Alt key to keep it just the bottom of the cell, create a line like that.
Now this particular line ended up in blue, but it's possible for them to go in, the shape out line and choose black.
And it could be all different kinds of lines, it could be different colors, it could have different weights, it could have an... So it could be a thick line like that.
It could have an arrow at one end, right?
But if it's looking like a border, then it's probably just the normal weight sitting down there.
And of course, remove borders won't do anything there.
Well, just try and click on the line.
If you just click on it and you get those little dots on either end which are the resize handles, then you know it's a drawing object.
At this point you would just press Delete and it would go away.
Okay, that's not it. Could it be some sort of a page break?
Now, page breaks will happen at the bottom of a page, but it's also possible that someone used Alt + I and B or here on the page layouts tab, breaks, insert a page break see, and that creates a line.
And I suppose if you had a really wide cell like that, you would perceive that as a line at the bottom of the cell. If that's it, boy, to get rid of them, let's see.
Do they have remove page break, or reset all page breaks might do it.
To remove the page break is funny, you have to go to the exact right spot, so that page break is above row seven.
Even though you're seeing the line at the bottom of six, the page break lives in row seven and so that's the cell, breaks, and remove page break.
I hate that, you would think you could just choose all of the cells like this and breaks, remove page break, but that doesn't remove it.
You have to go to the cell just below the page break, make sure you're in column A and breaks, remove page breaks.
Is that it? Okay, now we're down to the desperation, right?
If this is it, someone evil is playing with you, right?
Let's just come to any other cell far, far away from the range that you're looking at.
In this empty cell, they apply a top border like this, see?
And then they select that cell, CTRL + C, and then they come here just below your data and they do a special paste called a linked picture, which puts a picture of whatever I copied there in cell A10.
And that would give the perception of a line at the bottom of the cell.
Again, when you try and click on it, you'll tell what's happening here because it's not just a line, it's a little rectangle.
And to get rid of that, you would just press delete.
Well, once you've stumped me, I turned to the smartest people I know, the people at YouTube.
Put this short up and said, "Hey, what could it be?" Rico, it's not screen wipes, you're a funny guy.
Vesa said, "Can we download that file somewhere?" So went back and said, "Hey, can you change any confidential information and email me the workbook?" It's always easy to find me.
If you're watching one of my videos, click on my name here.
"Welcome to my YouTube channel, I'm Bill".
Click on about, and click this button to view email address. I wish more people had this turned on.
I could just write to you directly and see a screenshot of what's going on.
Great, all right, so the workbook arrives and I'm just frustrated because there's no line at all, right?
And I even try to go to print preview, file print to see if I'm getting a line there that is only in print preview.
I'm like, "Oh shoot. What version of Excel are you on?
It's not showing up in mine". I said, "The only lines I'm getting are these grid lines.
You're not talking about those".
If you're talking about those, you can get rid of them with the grid lines. And then finally, the aha moment.
When I heard the word line, I was thinking border, right?
But what we're talking about is that all of the here cells, the text completely fills up the cell, but in this cell, there's one cell where there's an extra line at the bottom, there's white space that shouldn't be there. Oh my gosh.
All right. So it took forever to get here.
But the deal is Excel has always had a problem with row heights when we're using Alt + Enter between the lines.
I still haven't figured out what can set it up, but it's very frustrating that when we come into home, format, autofit row height, it doesn't see it, right?
It thinks it needs to be taller.
And this goes, I mean, I remember reading about this in a Bob Umlas article back in the '90s, so it's been there forever.
How do we get rid of the extra line at the bottom of the spreadsheet?
Well, let's look at this one. This one has five bullet points.
1, 2, 3, 4, 5. This one has room for six.
So we come up here and go to format, row height, and see what the correct row height is.
75, good, click okay.
Come down here, and there's actually two answers to this. Let's make a copy.
The first answer is select the cell, go to format, row height.
Currently set to 90, we'll just go to 75 and click okay, and the line is gone.
Or the other method is right here.
Now right here between the seven and eight, all of these little borders are clickable, right?
So you get there, right there between the seven and eight, and you click.
Just click right there and then very carefully drag up and you can see two things.
The height and the pixels so we're trying to get to a height of 75 or just wherever it looks right and you're good to go. All right.
And then the question, what caused this?
What caused format autofit row height to not work on this cell? We come here.
Type line one, Alt + Enter. Then line two, Alt + Enter, and then Enter, right?
If that was the situation, then we would ask for the code of the right of that, comma one, and it's going to be 10.
When you press Alt + Enter, you're putting a character code 10 in there. And how do we oversee that?
Yeah, when we're I don't know, press F2 to edit the cell I suppose, and then backspace.
As I'm thinking through what could cause this. All right, so what if we did Alt + Enter?
Okay, when I press Enter, the text that I type is too wide for the cell and that's going to spill like that, right? This happens to me all the time.
I make column R wider and then the row height is broken, right?
That actually is the Bob Umlas article from the '90s that I remember.
Although it's leaving the line at the top of the cell instead of the bottom of the cell.
But that would be easy enough to correct there, right? Just going to the top line.
Although I mean, look, that just got fixed with top align.
And that would be fixed with autofit row height.
Then I wondered, okay, what if someone explicitly went to row height and changed the row height to something taller, top align, and then we come back to format, autofit row height.
It still corrects.
I don't have an explanation for why K's cell is stuck at 90. It still doesn't work.
It was Debra Dalgleish from Contextures who figured this out.
She said the problem is that we're too close to the edge, the right edge of the cell, and when that happens, Excel seems to overcompensate and we end up with an extra row.
Debra said we would just make this a little bit wider and then autofit row height would work and we're fine then, how crazy is that?
Still don't know the underlying bug there, but that at least explains it. Got an extra line at the bottom of your cell?
One of these seven things might solve your problem.
If not, let me know down in the YouTube comments below and we'll try and figure it out.
Well, some communications PhD can do their dissertation on my failure to actively listen in this video.
Here's the question, down in the YouTube comments below, when did you realize that the line was talking about the white space instead of a black horizontal line?
Oh, hey, I want to thank K for her awesome patience in trying to help me understand what she was saying. I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
