0303 Defining Cell Style - For "One-click" Universal Change

4 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$195.61
You save:  A$55.89
List Price:  ৳11,880.48
You save:  ৳3,394.66
List Price:  CA$186.90
You save:  CA$53.40
CHF 91.49
List Price:  CHF 128.09
You save:  CHF 36.60
List Price:  kr886.90
You save:  kr253.42
List Price:  €119.11
You save:  €34.03
List Price:  £107.12
You save:  £30.60
List Price:  HK$1,084.94
You save:  HK$310
List Price:  ₹10,484.85
You save:  ₹2,995.88
List Price:  RM587.32
You save:  RM167.82
List Price:  ₦52,986.21
You save:  ₦15,140
List Price:  kr1,263.20
You save:  kr360.94
List Price:  NZ$212.42
You save:  NZ$60.69
List Price:  ₱6,873.58
You save:  ₱1,964.02
List Price:  ₨23,542.81
You save:  ₨6,727
List Price:  S$192.23
You save:  S$54.92
List Price:  ฿4,355.29
You save:  ฿1,244.46
List Price:  ₺1,020.43
You save:  ₺291.57
List Price:  R2,475.27
You save:  R707.27
Already have an account? Log In


Hello, everybody. This time I'm going to talk about one of the most underutilized techniques in Excel and that is Home Tab style cell styles. Now, you would have seen this button quite often, but this time, let me show you the benefits of this technique. Especially if you are involved in financial modeling and a lot of equity research valuation and such models. Quite often it happens that when you building a financial model, you are providing some cells as the designation of assumptions what assumptions Well, they are the cells where the user end user will provide his value and all the resulting numbers which are linked to it will change. So it could be the solutions based on sales growth, or it could be an assumption based on cost as a percentage of sales.

Now, typically the best of financial modeling practices suggest that you should dedicate the sale with a certain color, so as to indicate whether the assumptions or whether they are formula linked cells. Right now if I have to give certain color Combination I have to do that manually I go to home, I go to that particular cell color which I find appropriate and then I change the font color to blue. And I then replicate this format to all the other cells where I think that assumption has been used. Now, the problem comes up when later point in time somebody tells you look that this kind of color combination has to be changed. For example, the revised color combination for assumption cells is going to be a light gray cell with blue color. Now, you'll have to apply Format Painter apply to all the other cells manually, someplace it may not be a percentage, in which case the problem may exist.

Now, to avoid this kind of a manual updation you will have to take the help of cells type. Let me show you how. First, let me remove all the formatting from these columns by saying no fill. And let me now proceed. I choose one cell. I then go to Home tab, I then go to sales tie and here to towards the bottom of this menu, I have something called new cell style new cell style.

Now here, let me give a name called assumptions, which indicate that this particular cell style is going to be dedicated for assumptions cell in the tab of format, let me click on it, and there let me pick up a light yellow cell. So I pick up more colors, I pick up one of these from the beehive and Okay, and the phone color let me dedicate with a typical blue and it should be as it is no bold. Okay, okay. Now once having defined the cell style, let me dedicatedly choose that cell style, which says assumptions. Now once I click on it, notice this cell has captured this particular format. Now this is not the trick.

The trick is, you can then choose other assumptions cells, Gulu cell style, and do the same assumptions. Let me do that for the rest. So styles, assumptions, Now the grand finale. The final benefit is, in case you want to modify this color from dark blue to light blue, the font color. How do you make a centralized change, and that change should take effect on all these cells. So I go to cell styles.

I right click on this custom assumptions cell style, right click, and I say modify. I go to the tab format, I go to font color, and then I change the font color to light blue. Okay, as soon as I press OK, on this button, notice all the cells which had captured this initial style of assumption has changed automatically. Now this is going to be tremendous time saver in case you are building a model that spans over hundreds of rows and spanning across different worksheets. So try this with a simple technique. And in the next video, I'll also show you a technique whereby if you have defined a set of styles and styles, you can transfer those sales styles to a different workbook as well.

Sign Up