Applying colors to Subtotal rows according to the Subtotal level in Microsoft Excel 2010

In this article, we will learn how we can apply the colors to subtotal rows according to the subtotal level in Microsoft Excel 2010.

Subtotal: - This option is used to calculate rows of related data quickly by inserting subtotals and totals.

Before applying the colors to subtotal rows according to the subtotal level, we create the subtotal in data.

Let’s take an example to understand how we apply colors to subtotal rows according to the subtotal level.

We have data in range A1:E18 where column A contains month, column B contains year, column C contains Date, column D contains Customers' name and column E contains Revenue ($).

Follow below given steps:-

Sort the data as per the year, newest to oldest.

To apply colors to subtotal rows according to the subtotal level, follow below given steps:-

By using 2 techniques:-

1. By changing Style.
2. By Conditional Formatting.

Changing Styles:-

Select the data, by pressing the key Ctrl+Shift+* on your keyboard.
Go to Data Tab, and click on the below bottom right corner on the arrow key.

Settings dialog box will appear.

Click on Apply Styles, and then click on OK.

Conditional Formatting:-

Select Cell A1, press Ctrl+*, and select the data table.
From the Format menu, select Conditional Formatting.

In the first argument, select Formula Is.
In the formula box, enter the formula =ISBLANK($D1).
Click Format, and select the desired formatting.
Click OK.

This is the way we can apply colors to subtotal rows according to the subtotal level in Microsoft Excel.

Hayden
August 27, 2013 at 6:51 am

I followed your instructions here and tried to apply it to several worksheets. However, sometimes the "Row level 2" option would not appear in the drop-down style list. What step am I missing?

Reply ↓
Clerk
August 27, 2013 at 6:51 am

I like this tip...however, I found two things not explained...I needed to select the entire list area before I instructed Excel to Apply styles. Also, when I removed subtotals and then reapplied them, my shading always came out black, rather than the light color I applied. Any suggestions there?

Reply ↓
Mike
August 23, 2013 at 9:30 am

I followed your instructions here and tried to apply it to several worksheets. However, sometimes the "Row level 2" option would not appear in the drop-down style list. What step am I missing?

Reply ↓
Mike
August 23, 2013 at 9:29 am

I like this tip...however, I found two things not explained...I needed to select the entire list area before I instructed Excel to Apply styles. Also, when I removed subtotals and then reapplied them, my shading always came out black, rather than the light color I applied. Any suggestions there?

Reply ↓