Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Let's say you have a worksheet like this that contains rows, columns of numbers, maybe some drawings and a chart, and you want a quick way to format everything all at once, so that it looks consistent and unified. Maybe looking like other documents that you have created in Word or PowerPoint for example. Using a theme you can apply consistent and professionally designed formatting to all the parts of your worksheet. Now keep something in mind though. What themes cannot do is they can't read your mind. So you have to give this worksheet a bit of a headstart by indicating where you want the formatting applied and here's how we will do it.
Click anywhere inside this number area, and we're going to format this as a table. Don't worry too much about that. Over here in the Home tab we want to go here under Styles, click Format as Table. Choose anything here. It doesn't really matter because we're going to change it in a bit. What this is simply doing is saying okay, this is where we're going to have a table and the table has special meaning in Excel and this guesses correctly where we have the data. You can see the marching ants. The table has headers checked because that's verified, and that's great. Click OK, and we have these little dropdowns and don't even worry too much about what those are.
What we will do here is we're in the Table Tools section in the Design tab. Click over here where it says Convert to Range. Choose Yes and now we have a regular worksheet again and you can just deselect. Now let's apply some themes. Go over here to the Page Layout tab and in the left side here in the Themes section, click Themes and we have all of these different named themes. Roll over them, and you can see that your worksheet is changing, the SmartArt in the lower left is changing, the chart in the lower right is changing.
I am going to go over here to this Austin because that has kind of a green olivey sort of theme to it, but not quite. We want to change it a little bit. So go up here, still in the Themes section, choose Colors, and if you roll your mouse over any of these, you notice also that the numbers and the SmartArt and Chart are changing. Well, let's say I don't want any one of these out of the box. I want to have my own custom color. So down over here choose Create New Theme Colors. Let's move this out of the way and here we have a sample of how it's going to look and we can change any of these.
So maybe I want some of these greens to be a little darker, maybe a little bit more olivey in color. And instead of naming it Custom 1, let's select it and let's call it Olive Oil, and click Save and now it's changed a little bit. Now, let's say we decide we want to edit it some more. Go back up here to the Color drop- down and there is our Olive Oil and right-click it, choose Edit, and we're right back where we were and we can continue editing, applying green colors or whatever other color you like, and you can simply click Save and save the color over it.
If there's a color palette that you don't like, let's click this here, maybe we don't want this Two Trees, right-click it and choose Delete, confirm and it's gone. While we're at it, let's change some of the fonts. Go up here and choose Fonts and roll over and you see also you can change the fonts on your worksheet dynamically. I think this is a really great feature and once you have a theme customized the way you like, remember that you could also use it in Word and PowerPoint, so all of your documents will have the same consistent, unified look and feel to them.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 76755 Viewers
80 Video lessons · 130908 Viewers
52 Video lessons · 64796 Viewers
59 Video lessons · 50622 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.