Quinton from Jacksonville is searching for a way to have the Default Excel Table format persist from one workbook to the next, but the defaults only apply to one workbook. In Episode 1793, see how to use a custom Book.xltx template to store the table format for all future tables.
Transcript of the video:
Learn Excel From MrExcel, Podcast Episode 1793: CONTROL+T Table The Default Formats Don’t Persist.
Hey. Welcome back to the MrExcel netcast.
I'm Bill Jelen. Well, today's question is actually an idea from Quinton. Quinton's from Jacksonville, Florida. I ran into Quinton last week at my seminar in Daytona and Quinton noted that when you create a CONTROL+T table and create a format, so CONTROL+T, you know, you get this nice little formatting, but you can go in and customize the formatting.
So, right-click, DUPLICATE, and then we can change things. Let's say that we want to change the FIRST ROW STRIPE to be 2 tall, 2 rows tall, with, let's see, what, FILL of this orange, click OK, the SECOND ROW STRIPE 2 rows tall with a FILL of that blue, click OK, click OK. Alright. So, now, we have this new format, we can define it or use it, and, also, we can right-click and say that we want to SET AS DEFAULT, alright?
So, that all sounds good, except for when you do CONTROL+N, go to a new workbook, create a new table, 123, with CONTROL+T, click OK, we don't get the default. In fact, the default isn't even here, and the problem is that that custom table format that we created is only in the workbook that we created it. So, it's only a default for that workbook. Like, if I would create a new table here, Q1, Q2, Q3, Q4, and CONTROL+T it, then it's the default here, but it's not the default in any other workbook. That's a horrible way to go, alright?
So, Quinton had asked this question just after I showed a trick for how to create defaults for all future workbooks that you open. So, this is a horrendous set of steps. It's over here. I'm going to walk through all of these here in the next couple of minutes, but you might want to freeze the video at this point and just kind of, you know, make sure that you go through all of these steps.
If you have my book Learn Excel 2007 through 2010 from MrExcel, these steps are on page 46, horrendous set of steps, with the exception of the table steps.
Alright. So, here's what we do. We're going to create a brand new workbook, CONTROL+N, make sure that workbook has one sheet, temporarily set up some sort of a table, and it doesn't matter how big the table is or what's in the table. We just have to set up a table. So, simple. CONTROL+T. Click OK. Then, we're going to choose the table style that gets close, so if you see any colors here that look remotely like what you're trying to use, by all means, go ahead and select those. So, we'll choose that one, and then right-click, and say DUPLICATE, and now we can go through and customize. So, we want the FIRST ROW STRIPE to be 2 with the SECOND ROW STRIPE to be 2, and we'll change the format of that SECOND ROW STRIPE to something else. Click OK. There's all kinds of things you can customize in here, alright? Click OK, and then, see, it doesn't change the table.
It just creates a new item here in the list.
So, we’ll apply that table, right-click, and say SET AS DEFAULT.
So, we’ve now defined the default table as this format in this workbook, but, at this point, we can get rid of that table. So, I’m going to do ALT+E A A, which is edit, clear, all, and, of course, that is somewhere out here under CLEAR, CLEAR ALL. There we go.
Alright. Make sure that you're up in A1.
Now, optional step at this point, and this is what I usually talk about when we define a new default workbook for all future workbooks, if there's anything you want to change, like, for example, if you always go to PAGE SETUP and say, you know, PAGE 1 OF ?, if that's your standard, or you always put confidential or highly confidential or something like that, by all means, go make those changes here.
FIT to 1 PAGE WIDE BY blank pages TALL, you know, any other settings that you want to use all the time.
For example, I always like to use the narrow margins, 0.25, 0.25. I even go 0.25 at the TOP and then 0.5 at the BOTTOM because the FOOTER is going to be at 0.25. So, these are the changes I make all the time. You make your changes. Other than that, it's going to look like a completely blank workbook.
Alright. Now, we’re going to go into FILE, EXCEL OPTIONS, go to ADVANCED, scroll all the way to the bottom where you have this setting AT STARTUP, OPEN ALL FILES IN, and this is blank on your computer, so you're going to type some new folder in there. I called mine XLSTART. You can call it whatever you want. Make sure in Windows Explorer, you go create a folder. So, WINDOWS E, go to the C drive, and I already have my XLSTART, and it's empty right now. There's nothing in it because, if there was something in here, it would automatically open when Windows opens.
Okay, but we create that folder, and then this workbook, the one that has the default table style, FILE, SAVE AS. We’re going to save it as a template. If you don't use macros, you're going to use xltx, right there, if you do use macros, xltm. I'll pretend like I don't use macros today, and that, then, changes to this horrendous folder deep, deep, deep down in the folder structure. So, we'll go back to C:\XLSTART and we have to use a special name of BOOK, so you use the FILE NAME of BOOK, and then do another save as, FILE, SAVE AS, and, this time, save it as SHEET, so, BOOK and SHEET. There we go. I'll close this. FILE, CLOSE, and this is our steps again, so we went through all of those steps, and then the last step, very important.
When we’re creating a new workbook, you need to use CONTROL+N. This is particularly important if you're in Excel 2013. Do not come here and do FILE, NEW. That takes you to this blank workbook. It’s not the CONTROL+N workbook, which is even worse in Excel 2013 because they force this on us right there on the opening screen. This is not the CONTROL+N workbook. This is something else. So, CONTROL+N looks like a brand-new workbook, looks like nothing is different, but, in fact, if we go into PAGE SETUP, the changes that you made are there, so, PAGE 1 OF, the margins are narrow, FIT TO 1 PAGE WIDE BY blank pages TALL, all that's there, and the added benefit, if you create a table in this workbook, and CONTORL+T, click OK, your default table style from the other workbook that you set up, you know, maybe weeks, months, years ago will apply to this workbook as well. So, an interesting way to actually use default table styles and have them work in future workbooks by saving it in the BOOK and SHEET templates.
Hey, wait. Don't end it yet. Let me reiterate. Excel 2013, they open to the start screen. This BLANK WORKBOOK is like doing FILE, NEW in Excel 2010. It is not the BOOK template or the SHEET template. It is something else. Don't click this. Instead, just click ESCAPE and that will load the BOOK template. Now, you have to go into Excel options here and change that at startup, open all files in. However, once you do that, restart Excel 2013, CONTROL+T, click OK, then your heading Styles will, even if you set it up in 2010, it'll come over to 2013. Just remember, don't use the start screen, which is the same as doing FILE, NEW. You have to just ESCAPE out of that and the version of Excel that…the Excel workbook that you get or the CONTROL+N workbook will have the right settings.
Well, hey. I want to thank Quinton from Jacksonville for that idea and I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Hey. Welcome back to the MrExcel netcast.
I'm Bill Jelen. Well, today's question is actually an idea from Quinton. Quinton's from Jacksonville, Florida. I ran into Quinton last week at my seminar in Daytona and Quinton noted that when you create a CONTROL+T table and create a format, so CONTROL+T, you know, you get this nice little formatting, but you can go in and customize the formatting.
So, right-click, DUPLICATE, and then we can change things. Let's say that we want to change the FIRST ROW STRIPE to be 2 tall, 2 rows tall, with, let's see, what, FILL of this orange, click OK, the SECOND ROW STRIPE 2 rows tall with a FILL of that blue, click OK, click OK. Alright. So, now, we have this new format, we can define it or use it, and, also, we can right-click and say that we want to SET AS DEFAULT, alright?
So, that all sounds good, except for when you do CONTROL+N, go to a new workbook, create a new table, 123, with CONTROL+T, click OK, we don't get the default. In fact, the default isn't even here, and the problem is that that custom table format that we created is only in the workbook that we created it. So, it's only a default for that workbook. Like, if I would create a new table here, Q1, Q2, Q3, Q4, and CONTROL+T it, then it's the default here, but it's not the default in any other workbook. That's a horrible way to go, alright?
So, Quinton had asked this question just after I showed a trick for how to create defaults for all future workbooks that you open. So, this is a horrendous set of steps. It's over here. I'm going to walk through all of these here in the next couple of minutes, but you might want to freeze the video at this point and just kind of, you know, make sure that you go through all of these steps.
If you have my book Learn Excel 2007 through 2010 from MrExcel, these steps are on page 46, horrendous set of steps, with the exception of the table steps.
Alright. So, here's what we do. We're going to create a brand new workbook, CONTROL+N, make sure that workbook has one sheet, temporarily set up some sort of a table, and it doesn't matter how big the table is or what's in the table. We just have to set up a table. So, simple. CONTROL+T. Click OK. Then, we're going to choose the table style that gets close, so if you see any colors here that look remotely like what you're trying to use, by all means, go ahead and select those. So, we'll choose that one, and then right-click, and say DUPLICATE, and now we can go through and customize. So, we want the FIRST ROW STRIPE to be 2 with the SECOND ROW STRIPE to be 2, and we'll change the format of that SECOND ROW STRIPE to something else. Click OK. There's all kinds of things you can customize in here, alright? Click OK, and then, see, it doesn't change the table.
It just creates a new item here in the list.
So, we’ll apply that table, right-click, and say SET AS DEFAULT.
So, we’ve now defined the default table as this format in this workbook, but, at this point, we can get rid of that table. So, I’m going to do ALT+E A A, which is edit, clear, all, and, of course, that is somewhere out here under CLEAR, CLEAR ALL. There we go.
Alright. Make sure that you're up in A1.
Now, optional step at this point, and this is what I usually talk about when we define a new default workbook for all future workbooks, if there's anything you want to change, like, for example, if you always go to PAGE SETUP and say, you know, PAGE 1 OF ?, if that's your standard, or you always put confidential or highly confidential or something like that, by all means, go make those changes here.
FIT to 1 PAGE WIDE BY blank pages TALL, you know, any other settings that you want to use all the time.
For example, I always like to use the narrow margins, 0.25, 0.25. I even go 0.25 at the TOP and then 0.5 at the BOTTOM because the FOOTER is going to be at 0.25. So, these are the changes I make all the time. You make your changes. Other than that, it's going to look like a completely blank workbook.
Alright. Now, we’re going to go into FILE, EXCEL OPTIONS, go to ADVANCED, scroll all the way to the bottom where you have this setting AT STARTUP, OPEN ALL FILES IN, and this is blank on your computer, so you're going to type some new folder in there. I called mine XLSTART. You can call it whatever you want. Make sure in Windows Explorer, you go create a folder. So, WINDOWS E, go to the C drive, and I already have my XLSTART, and it's empty right now. There's nothing in it because, if there was something in here, it would automatically open when Windows opens.
Okay, but we create that folder, and then this workbook, the one that has the default table style, FILE, SAVE AS. We’re going to save it as a template. If you don't use macros, you're going to use xltx, right there, if you do use macros, xltm. I'll pretend like I don't use macros today, and that, then, changes to this horrendous folder deep, deep, deep down in the folder structure. So, we'll go back to C:\XLSTART and we have to use a special name of BOOK, so you use the FILE NAME of BOOK, and then do another save as, FILE, SAVE AS, and, this time, save it as SHEET, so, BOOK and SHEET. There we go. I'll close this. FILE, CLOSE, and this is our steps again, so we went through all of those steps, and then the last step, very important.
When we’re creating a new workbook, you need to use CONTROL+N. This is particularly important if you're in Excel 2013. Do not come here and do FILE, NEW. That takes you to this blank workbook. It’s not the CONTROL+N workbook, which is even worse in Excel 2013 because they force this on us right there on the opening screen. This is not the CONTROL+N workbook. This is something else. So, CONTROL+N looks like a brand-new workbook, looks like nothing is different, but, in fact, if we go into PAGE SETUP, the changes that you made are there, so, PAGE 1 OF, the margins are narrow, FIT TO 1 PAGE WIDE BY blank pages TALL, all that's there, and the added benefit, if you create a table in this workbook, and CONTORL+T, click OK, your default table style from the other workbook that you set up, you know, maybe weeks, months, years ago will apply to this workbook as well. So, an interesting way to actually use default table styles and have them work in future workbooks by saving it in the BOOK and SHEET templates.
Hey, wait. Don't end it yet. Let me reiterate. Excel 2013, they open to the start screen. This BLANK WORKBOOK is like doing FILE, NEW in Excel 2010. It is not the BOOK template or the SHEET template. It is something else. Don't click this. Instead, just click ESCAPE and that will load the BOOK template. Now, you have to go into Excel options here and change that at startup, open all files in. However, once you do that, restart Excel 2013, CONTROL+T, click OK, then your heading Styles will, even if you set it up in 2010, it'll come over to 2013. Just remember, don't use the start screen, which is the same as doing FILE, NEW. You have to just ESCAPE out of that and the version of Excel that…the Excel workbook that you get or the CONTROL+N workbook will have the right settings.
Well, hey. I want to thank Quinton from Jacksonville for that idea and I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
