Join Dennis Taylor for an in-depth discussion in this video Control table formatting with custom formats, part of Excel Tips Weekly.
- [Instructor] The data in this worksheet from columns A through J has been converted into a table. It has that distinctive look that we refer to as banded rows. And by the way, in the Contextual Design tab that appears any time the active cell is within a pivot table, you certainly could consider changing that to be banded columns. As you do this, by the way, you might have noticed something else happening on the screen. The table styles over here to the right, and you can click the arrow on the bottom, reveal these choices now in banded column style. If we go back to banded rows instead, and by the way, almost nobody wants to have both at the same time, back to banded rows.
If we can go back here, and now the previews as we see them show banded rows. Now there's 61 options here, and I think almost anybody's gonna find something out here that he or she likes, but you do have the option of creating a new table style. Take a look at this option at the bottom here, New Table Style. First Row Stripe, I'm gonna assume we want banded rows here instead of columns, First Row Stripe. Stripe Size off to the right, drop arrow. We might make it be three columns. What kind of a format might we want? Well, we can go to Format, Fill, pick a color perhaps, how about a yellow, something like that.
Pale cream color, something along those lines. The preview off to the right tells us what we're about to see. How about a Second Row Stripe? We could apply the format first, maybe we'll mix that with a green. There's a green, okay. Wanna be sure and use light colors here so we can see the data through the colors, and make that be stripe three. There's our preview right there, do we want that? Well, if we click OK, might be surprised if we're not seeing that just yet. Go back to Table Styles, click the drop arrow, and up top you'll see it, up there.
As you slide over it, you get the preview. Now that may or may not be to your liking, so you could create another style. New Table style, it gives a different name here. So we could go back and try it all over again. I think you get the idea here, maybe you'll make this one two. And you can apply the color, or the format first or second, whatever makes sense to you. And again, light colors tend to work best, but you'll decide which ones work best for you. And not everybody, myself included, necessarily has strong design skills.
So you'll make some bad choices along the way perhaps, but nevertheless, you have this ability to mix colors in a way that could be interesting, or more appealing to you than some of the options that are available already. So here's another one, Table Style 2, and of course we could give these more sensible names if we wished, slide over that choice, we see that. I think that might get a bit tiresome, that yellow's awfully bright, but you get the idea. You can design your own table look. And in all cases here, if we insert a row, delete a row, same sort of thing happens as if we'd used one of the standard table styles.
So if I right-click on row five, and Insert, the color scheme automatically kicks in, and of course, I haven't added the data yet, but you see how every two rows are gonna be different, as you would expect. So not the most important thing we do with Excel, but on the other hand, you sometimes wanna have that ability to make the design of a table something that you want, not based on the ones that are built in to Excel.
Author
Updated
3/30/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 37m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Control table formatting with custom formats