Uncle S is trying to have a formula copy to new rows. So a table seems like a great way. But he also has merged cells and the table unmerges those.
In this video, two surprising Table tricks:
First, a table can contain Center Across Selection, which is better than Merging Cells in Excel.
Second, a surprising discovery from the Excelforo channel about new headings in a table.
Table of Contents
(0:00) Auto-copying formulas to new rows in Excel
(2:31) Can a table have a merged cell
(4:00) New table columns using custom lists in Excel
In this video, two surprising Table tricks:
First, a table can contain Center Across Selection, which is better than Merging Cells in Excel.
Second, a surprising discovery from the Excelforo channel about new headings in a table.
Table of Contents
(0:00) Auto-copying formulas to new rows in Excel
(2:31) Can a table have a merged cell
(4:00) New table columns using custom lists in Excel
Transcript of the video:
I have two surprising facts about Excel tables that I've learned today.
Can an Excel table include a merge cell?
This question from Uncle S on an old video 2349: Insert a Row in Excel and Formula Automatically Copies.
That showed two different methods to have a formula copied to a new row.
One is using a Table and Uncle S says I can't create a Ctrl+T table because it would unmerge the cells and I need to keep those. So let's try method one.
Why is that not working? We have Extend Data Range Formats and Formulas.
That's File, Options, Advanced.
And what is supposed to happen - we have here 1-2-3-4 identical formulas. To make this work I type the new row.
I type the value 140.
As soon as I press enter, that formula copies down. So why is that not working for Uncle S?
Well, If we come in here and type Chris and 120, it doesn't work because there's only two in a row. So I copy the third one down.
Now I've always thought it's supposed to be three in a row.
When I try and do the 4th one it doesn't work.
But if I have four in a row then it starts working.
The other reason that it could happen See, if the formulas aren't all the same, if we have a plug number here.
I come here and type flow 150. Even then, even then it's working.
I would have thought that with a wrong formula it would have not worked, so that's not it.
Now Uncle S has mentioned that he has a merged cell.
I don't use merged cells at all. I hate merged cells.
Is that causing the problem? So I type Flo Smith and 150, right?
It's not that kind of a merged cell that would cause a problem.
However, what if he's merged cells vertically?
So what if Jack and Jill are a team and their sales are reported together? Now what we have is a formula in T4.
The same formula in T5. A formula in T6.
But there's nothing in T7 because T7 doesn't exist.
Does that cause it to stop working? So we come here, Flo, 150.
So that that's essentially a blank cell.
And my work around for this would be not to put those two people on two rows, Jack and Jill.
If you really want them to appear to be on two rows, right here between Jack and Jill press Alt and then Enter.
And that creates a cell that's double high and it will give you the same look as your merged cell.
All right. So that's all troubleshooting method one.
But the real question today is can a table, can a control T table include a merged cell?
So if I come here and do control T? See it unmerges that cell.
It's no longer a merged cell and we don't have the same look that we had.
Again, I'm not sure why you're trying to merge cells, but here is an interesting workaround.
So let's take this cell that's a merged cell.
And I am going to unmerge the cell, but have it have the same look. So I have both columns selected.
Come here to the Alignment Dialog Launcher.
Uncheck Merge Cells, but replace it with this great setting under Horizontal called Center Across Selection.
Click OK.
See how we get the exact same look, even though there's really nothing in that cell? So Cher appears there.
It has the same look as the merged cell. And then Ctrl+T.
And it continues to center, right? So can a table include a merged cell?
No, but can a table include center across selection?
Amazingly, it can.
But yet another reason why the hard-to-find Center Across Selection setting runs circles around the very easy to find, Merge and Center.
So my first surprising thing is center across selection is allowed in tables.
Here's another one that I actually learned last week.
This is a great Spanish language site Ismael Romero, who runs the ExcelForo site, discovered this one.
So we have an existing table here and of course we all know that if we type 140, that 140 becomes part of the table - the table will grow vertically. That's not surprising, I knew that.
But if you add more data to the right, the table is also going to grow.
And again, I conceptually knew that. But here's the wild thing.
The heading that's right above the 100 is something that's in the custom lists.
So, File, Options, Advanced. Scroll all the way down to the bottom.
And Edit Custom Lists.
Of course we have Jan, Feb , Mar, Apr, May as a built-in custom list.
Or even it works with these custom lists down here: East Central West.
If the last heading cell is from a custom list and I type a new value here, the table is going to grow.
But it automatically picks up the next custom list item, which is pretty amazing here.
Let's even try this.
I'll put East, which is one of my custom list items. One go to the right.
I get Central and then West.
What a crazy feature that they programmed in.
Apparently it's been there for quite some time. Thanks to Ismael for that awesome trick.
If you speak Spanish, check out his ExcelForo - great tutorials in Spanish.
Well hey I want to thank Uncle S for sending that question in.
And I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey check out my new book MrExcel 2022, Boosting Excel - lots of great new tips.
Click the I in the top right hand corner For more information about that.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
Now if you love Excel, check out my new courses on the Retrieve platform.
They are video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages.
It 's a super fast way to learn.
Can an Excel table include a merge cell?
This question from Uncle S on an old video 2349: Insert a Row in Excel and Formula Automatically Copies.
That showed two different methods to have a formula copied to a new row.
One is using a Table and Uncle S says I can't create a Ctrl+T table because it would unmerge the cells and I need to keep those. So let's try method one.
Why is that not working? We have Extend Data Range Formats and Formulas.
That's File, Options, Advanced.
And what is supposed to happen - we have here 1-2-3-4 identical formulas. To make this work I type the new row.
I type the value 140.
As soon as I press enter, that formula copies down. So why is that not working for Uncle S?
Well, If we come in here and type Chris and 120, it doesn't work because there's only two in a row. So I copy the third one down.
Now I've always thought it's supposed to be three in a row.
When I try and do the 4th one it doesn't work.
But if I have four in a row then it starts working.
The other reason that it could happen See, if the formulas aren't all the same, if we have a plug number here.
I come here and type flow 150. Even then, even then it's working.
I would have thought that with a wrong formula it would have not worked, so that's not it.
Now Uncle S has mentioned that he has a merged cell.
I don't use merged cells at all. I hate merged cells.
Is that causing the problem? So I type Flo Smith and 150, right?
It's not that kind of a merged cell that would cause a problem.
However, what if he's merged cells vertically?
So what if Jack and Jill are a team and their sales are reported together? Now what we have is a formula in T4.
The same formula in T5. A formula in T6.
But there's nothing in T7 because T7 doesn't exist.
Does that cause it to stop working? So we come here, Flo, 150.
So that that's essentially a blank cell.
And my work around for this would be not to put those two people on two rows, Jack and Jill.
If you really want them to appear to be on two rows, right here between Jack and Jill press Alt and then Enter.
And that creates a cell that's double high and it will give you the same look as your merged cell.
All right. So that's all troubleshooting method one.
But the real question today is can a table, can a control T table include a merged cell?
So if I come here and do control T? See it unmerges that cell.
It's no longer a merged cell and we don't have the same look that we had.
Again, I'm not sure why you're trying to merge cells, but here is an interesting workaround.
So let's take this cell that's a merged cell.
And I am going to unmerge the cell, but have it have the same look. So I have both columns selected.
Come here to the Alignment Dialog Launcher.
Uncheck Merge Cells, but replace it with this great setting under Horizontal called Center Across Selection.
Click OK.
See how we get the exact same look, even though there's really nothing in that cell? So Cher appears there.
It has the same look as the merged cell. And then Ctrl+T.
And it continues to center, right? So can a table include a merged cell?
No, but can a table include center across selection?
Amazingly, it can.
But yet another reason why the hard-to-find Center Across Selection setting runs circles around the very easy to find, Merge and Center.
So my first surprising thing is center across selection is allowed in tables.
Here's another one that I actually learned last week.
This is a great Spanish language site Ismael Romero, who runs the ExcelForo site, discovered this one.
So we have an existing table here and of course we all know that if we type 140, that 140 becomes part of the table - the table will grow vertically. That's not surprising, I knew that.
But if you add more data to the right, the table is also going to grow.
And again, I conceptually knew that. But here's the wild thing.
The heading that's right above the 100 is something that's in the custom lists.
So, File, Options, Advanced. Scroll all the way down to the bottom.
And Edit Custom Lists.
Of course we have Jan, Feb , Mar, Apr, May as a built-in custom list.
Or even it works with these custom lists down here: East Central West.
If the last heading cell is from a custom list and I type a new value here, the table is going to grow.
But it automatically picks up the next custom list item, which is pretty amazing here.
Let's even try this.
I'll put East, which is one of my custom list items. One go to the right.
I get Central and then West.
What a crazy feature that they programmed in.
Apparently it's been there for quite some time. Thanks to Ismael for that awesome trick.
If you speak Spanish, check out his ExcelForo - great tutorials in Spanish.
Well hey I want to thank Uncle S for sending that question in.
And I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey check out my new book MrExcel 2022, Boosting Excel - lots of great new tips.
Click the I in the top right hand corner For more information about that.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
Now if you love Excel, check out my new courses on the Retrieve platform.
They are video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages.
It 's a super fast way to learn.
