Excel does some really great things when it comes to managing data. It also does some great things when it comes to formatting data. But out of the box, it may not always format the data in the way to what you *need* or "want" it to.
As an example, you can easily format your data such that every other row is shaded a different color. Basic Excel functionality. But what if that's not exactly what you want? What if you want your data to be shaded based upon logical groupings/rows of data? In this case, out-of-the-box functionality will not work for you. But here's a way you can do this leveraging a Helper Column and Conditional Formatting.
Example
Let's presume you're managing an auto salvage yard and you keep inventory of the different vehicles on your lot. You keep the data in an Excel workbook and want to color code the data sheet based upon the Make of the vehicle. In our sample sheet, the Make is in Column A.
Helper Column
First we setup a Helper Column. This is a column with a calculated value which will be used to determine if a group of rows should be shaded or not. It's easiest to do this in the first column.
1. Insert a new column and label it "Helper" (Note that the Make column is now Column B)
2. In Column A Row 2, put the Number 1
3. Enter the following formula in Column A Row 3: =IF(B3=B2,A2,A2*-1)
4. Copy Column A Row 2 down to the final data row in your sheet
This Helper Column formula will now put either a 1 or a -1 in each cell of Column A based upon the the Make of the vehicle in a given row, and based upon whether or not it is the same as is the Make of the vehicle in the prior row.
Conditional Formatting
Now we format data in the sheet using Conditional Formatting, and use the value in Column A to determine the shading.
1. Select the entire sheet by clicking the square at the top-left of the data sheet
2. Select the Conditional Formatting menu
3. Add a New Rule
4. Select "Use a formula to determine which cells to format"
5. Enter the following in the "Format values where this formula is true" box: =$A1=1
6. Click the "Format" button and select the formatting you wish to use
7. Enter the following in the "Applies to" box: =$1:$1048576
8. Click OK through all prompts
Once this is complete, your sheet will be shaded based upon the type of vehicle listed.
You can adapt this to suit the needs of your data sets. Let me know how it works out for you.
Comentarios