Use Color and Cell Styles to Assist with Organization

Locked lesson.

Upgrade

Quick reference

Use Color and Cell Styles to Assist with Organization

Use color and cell styles to visually indicate which worksheets are related and which cells can be changed.  

When to use

It is recommended that we use color and cell styles to visually group worksheets of similar type and indicate which cells should be changed as part of best practice when creating new workbooks. 

Instructions

Tab Color

A well-organized workbook will have different types of data organized into to separate worksheets in the workbook. We should be separating our sources data from any calculations, tables, charts, and dashboards. 

We can visually indicate which worksheets are related by making the tab color the same for each. 

  • Click on the first worksheet tab. 
  • Hold down CTRL and click on the related tabs. 
  • Right-click and choose Tab Color. 
  • Select a color from the palette. 

We can then add the tab color to the summary sheet so everyone knows that all tabs colored blue contain related information.  

Use Cell Styles

Cell Styles are in-built formatted styles in Excel. They allow us to quickly apply formatting to a cell. The Cell Styles gallery contains default styles but we can create our own custom cell styles if we need to match branded colors or house-style. 

Cell Styles help us communicate visually the type of data contained within the cell. 

For example, if we have a worksheet that contains a 'Quantity' and a 'Price' column, we might also have a 'Total' column that contains the SUM calculation 'Quantity * Price'. 

We can indicate that the 'Total' column contains a calculation and shouldn't be changed using cell styles. We also might want to indicate which cells can be changed by using the 'Input' cell style. 

  • Select the column that contains formulas. 
  • From the Home tab, in the Styles group, click the Cell Styles button. 
  • Choose the Calculation cell style from the gallery. 
  • Select the column/s that contain data that can be changed. 
  • From the Home tab, in the Styles group, click the Cell Styles button. 
  • Choose the Input cell style from the gallery. 

Add the cell styles to the summary sheet so everyone is clear about which cells are calculation cells and which cells are input cells. 

Hints & tips

  • To quickly find all cells in the worksheet that contain formulas, press CTRL+G and choose Special. Select Formulas and click OK. 
Login to download
  • 00:04 In this lesson we're going to talk about how we can use color and cell styles to
  • 00:09 assist with the organization and readability of our spreadsheets.
  • 00:13 So let's start out with color.
  • 00:15 Now, notice here I have that same workbook open where we have our different
  • 00:20 tabs as source data tab that shows the sales information and
  • 00:24 other information for Marco's Pizza Place.
  • 00:27 I've then created some pivot tables based off of this data, some pivot charts, and
  • 00:33 I've put them all together on a consolidated sales interactive dashboard.
  • 00:38 So effectively, source data, pivot tables,
  • 00:41 pivot charts, and dashboard, all of these worksheets are related.
  • 00:45 So I might want to visually indicate that to anybody who's looking at this
  • 00:49 spreadsheet by using color.
  • 00:51 And this is simply a case of changing the color of the tabs.
  • 00:55 So what I might do here is select the source data worksheet,
  • 00:59 hold down Ctrl and select Pivot Tables, Pivot Charts, and Dashboard.
  • 01:04 And then I can simply right click, go to Tab Color,
  • 01:07 and I'm going to color these in blue.
  • 01:09 So, straight away,
  • 01:11 it's extremely obvious that those are worksheets that belong together.
  • 01:15 I might even go a stage further and add this information into the summary sheet
  • 01:20 because if you don't tell people that that's what you've done,
  • 01:23 then they're not going to know.
  • 01:25 So I might add something in the bottom of the summary sheet here, and
  • 01:28 let's just grab this same formatting.
  • 01:30 I'm going to click in where it says Last Updated up to Format Painter,
  • 01:35 let's select that.
  • 01:36 And we're going to paint that format in this cell.
  • 01:39 So what I'm going to say here is legend worksheets.
  • 01:44 And then I'm going to provide some information,
  • 01:47 Marco's Pizza Place Sales Analysis.
  • 01:49 And I'm going to click in the cell next to it, and I'm just going to color it
  • 01:53 in with the same color as those tabs, which I think was this blue just here.
  • 01:57 Now obviously you can make the formatting a little bit nicer.
  • 02:01 I'll just very quickly apply all borders just so that looks a little bit neater,
  • 02:06 and then what I'm going to do is just add a heading here, we'll call this Tab Color.
  • 02:14 And let's apply the same formatting using Format Painter.
  • 02:18 And I could carry on going.
  • 02:19 So if I had lots of different colored tabs to denote different groups, I could add to
  • 02:24 those all in here, so people truly understand what they're looking at.
  • 02:28 So if I added a few more spreadsheets, so let's say Sheet5, Sheet6, and Sheet7.
  • 02:35 I could select all of these by holding down Ctrl, right click,
  • 02:38 and I can give them a different tab color.
  • 02:40 So let's go for a green just here.
  • 02:43 So once I've done that, I would then come back to the Summary sheet and
  • 02:47 I would add in information for that particular color group.
  • 02:51 Now I'm just going to call this Blank Sheets for Training because hopefully
  • 02:55 you get the idea, we can then add that color into here as well.
  • 03:00 So tab color,
  • 03:00 a really good way of visually grouping sheets of different type.
  • 03:04 Let's take a look at cell styles now.
  • 03:07 Because I find these are something that often get overlooked when people
  • 03:11 are working in Excel, but again, they can be really useful.
  • 03:14 Now for this, I'm going to jump across to the Source Data worksheet.
  • 03:18 And this is where we have a whole heap of information related to
  • 03:22 Marco's Pizza Place.
  • 03:23 Now, maybe within this data,
  • 03:25 I've decided to highlight different cells in different colors.
  • 03:29 Now if I take a look at this data, let's just go to row number 2.
  • 03:34 I can see in column D, I have order date, that's fine, order time.
  • 03:38 I've got the unit price, and then I have the total price.
  • 03:41 Now take a look in the formula bar, the total price column contains a calculation.
  • 03:46 It's basically multiplying the quantity which is 1 in this
  • 03:51 case with the unit price to get that total price.
  • 03:55 And that calculation carries down throughout this column.
  • 03:58 Now it might be that I want to indicate very clearly to people using this
  • 04:03 spreadsheet that column G, which contains all of the formulas, isn't to be changed.
  • 04:09 Because column G will update whenever we add a new quantity and a new unit price.
  • 04:16 So I don't want people going in and messing around with this formula and
  • 04:19 effectively breaking my worksheet.
  • 04:21 Now one way that I could indicate this very clearly is to apply a cell
  • 04:26 style to this column.
  • 04:28 What I could do is click in column G,
  • 04:31 Ctrl + Shift + Down arrow to select everything in that column.
  • 04:36 Now if we go up to the home ribbon, we have a styles group just here, and
  • 04:41 one of them is Cell Styles.
  • 04:43 Now if we hover our mouse over and take a look at the screen tip, it says a colorful
  • 04:48 style is a great way to make important data stand out on the sheet.
  • 04:52 So let's click the drop down and you can see that we have different types of cell
  • 04:56 style that we can apply to our selected cells.
  • 04:59 And if you don't like any of these, you do have the option to create your
  • 05:03 own cell style so you can format it however you want.
  • 05:07 Maybe you have to get everything in your spreadsheet to match company colors,
  • 05:11 branded colors.
  • 05:12 You can definitely do that by creating your own cell style.
  • 05:15 Now I'm not going to go into that in this particular lesson,
  • 05:18 we're just going to use some of the presets that we have up here.
  • 05:20 So what I could do here is I could use effectively formatting to indicate
  • 05:25 that these are calculation cells.
  • 05:28 because notice here we have a section called Data and Model,
  • 05:31 and one of those is calculation.
  • 05:33 So if I select this,
  • 05:34 is going to change the formatting effectively of this particular column.
  • 05:40 Now as I said, if you don't like the default here, you can go in and
  • 05:43 create your own cell style.
  • 05:45 Maybe you just want them to show in bold, or
  • 05:47 maybe you want to apply a different type of background fill.
  • 05:50 That's absolutely fine.
  • 05:51 But it does indicate that this is a calculation column.
  • 05:55 Now, it doesn't do anything else.
  • 05:56 It doesn't protect the cells from being changed.
  • 05:58 I could still go in currently and edit this formula.
  • 06:01 But it does make people visually aware.
  • 06:04 Because what we can then do is, you might have guessed,
  • 06:07 jump back to our summary sheet and maybe add something else down here
  • 06:11 that lets people know that those are calculations cells.
  • 06:15 So I've just quickly added Legends-Cell Styles at the bottom,
  • 06:19 and this is where I can go in and I could say calculation, and
  • 06:23 then I can apply the cell style to this just here.
  • 06:27 So if I type in calculation, You can see how that works.
  • 06:35 So let's jump back to our source data page.
  • 06:39 because I just want to show you a quick trick.
  • 06:41 I'm going to remove the cell style from column G, go to cell styles, and
  • 06:45 I'm just going to put that back to normal, and reapply my dollar formatting.
  • 06:49 If you wanted to do something like this in your worksheet and select all of
  • 06:54 the cells that contain formulas, a quick way of doing it is to use Go To Special.
  • 06:59 So if we press Ctrl+ G, we can then click on Special, and this is where we
  • 07:04 can specify that we only want to select the cells that contain formulas.
  • 07:10 Now for me that's just one column, for you, it might be multiple.
  • 07:14 So, I'm going to say here, formulas, let's click on OK, and you can see it's going to
  • 07:19 highlight column G, and I can then go in and apply my calculation cell style.
  • 07:25 Now, what if I want to apply another cell style for
  • 07:28 all of the cells that people can change.
  • 07:31 So these are effectively input cells.
  • 07:33 But I only want to select the ones where they're inputting numbers, so
  • 07:37 maybe these four columns just here.
  • 07:39 Again, we can press Ctrl+G and go to Special, and
  • 07:43 this time if we want to select numbers which don't contain formulas,
  • 07:48 we can go to constants, and I just want to select constant numbers.
  • 07:52 Let's click on OK, it's going to highlight those columns.
  • 07:56 I'm going to go to cell styles, and I just want to denote these as input cells.
  • 08:01 So I'm going to choose the input cell style, and
  • 08:04 that's going to color those in orange.
  • 08:07 So again, I would then immediately jump back to my Summary sheet and add that in.
  • 08:14 So hopefully there you can see how cell styles and color can be extremely useful
  • 08:19 when it comes to letting people know which spreadsheets are grouped together,
  • 08:24 and also guiding people towards which cells they can change and
  • 08:28 which cells should be left alone.

Lesson notes are only available for subscribers.

Gift this course