Discover ways to add a spotlight to show teams in your Microsoft Excel information utilizing a helper column and Excel’s conditional formatting function.
The article, Methods to use a border to discern teams extra simply in Microsoft Excel, reveals you use a conditional formatting rule to use a pink border between teams in an Excel sheet. It’s not as efficient as you would possibly like as a result of the border is so skinny. On this article, we’ll use a conditional format to use a fill coloration to every row in the identical group. This method does include a limitation that I’ll clarify on the finish of the article.
SEE: Software program Set up Coverage (TechRepublic Premium)
I’m utilizing Microsoft 365 on a Home windows 10 64-bit system, however you need to use an earlier model. On your comfort, you’ll be able to obtain the demonstration .xlsx and .xls recordsdata. Excel for the online helps guidelines within the conditional format function, however you’re restricted to the codecs you’ll be able to select.
Methods to group the info in Excel
As a result of we’re formatting teams, the info should be grouped earlier than you start. In case your information is already sorted by the grouping values, then you’ll be able to skip this step. The demonstration file isn’t sorted but, so let’s get began. We need to group by Personnel values. Click on inside any cell within the Personnel column. Within the Modifying group on the Dwelling tab, select A to Z from the Type & Filter dropdown. The result’s that the gross sales and commissions for every personnel are grouped (Determine A).
If the info is already formatted with coloured bars, delete the format. That is simply completed by deciding on the info, on this case, B3:F13 and selecting No Fill from the Fill Colour dropdown within the Font group on the Dwelling desk. If, nonetheless, you’re working with a Desk object, select None within the gallery within the Desk Types group on the contextual Desk Design tab.
Grouping the info by sorting and eradicating current codecs is important for this method to work. Now, let’s transfer on to the helper perform.
Methods to create a helper perform in Excel
As soon as the info is so as, you’re prepared so as to add the conditional format. This is without doubt one of the few conditions during which a single rule can’t get the job executed. Apart from, with the helper column in place, you’ll be able to see the way it works.
Earlier than we do the rest, let’s focus on what the helper perform will do. In a nutshell, it’s going to return the identical worth for every report in a bunch so long as the present Personnel worth equals the worth within the cell above. When not equal, Excel returns the final worth plus 1. Later, the conditional rule will spotlight solely these data the place the results of the helper perform is odd. You would simply as simply spotlight the even-numbered teams. I selected odd as a result of the primary group is 1—an odd quantity and highlighting the primary group appears higher, in my view.
Now, let’s get began. Utilizing the sheet in Determine A, enter into G3 the next perform
and duplicate it to the remaining cells. When you’re utilizing a Desk object, Excel will robotically lengthen the Desk to incorporate this new column if you add this perform to column G; you don’t need that to occur. As you’ll be able to see in Determine B, the helper worth will increase by 1 each time the worth within the Personnel column adjustments.
It’s value mentioning that different expressions will work, however I selected this one as a result of the consecutive numbers are a visible clue to what they symbolize. With the helper column in place, it’s time so as to add the conditional format rule.
An Excel conditional format rule
Lastly, it’s time to create the conditional format rule that fills alternating teams within the Excel Desk:
- Choose the info, B3:F13. (Don’t embrace the column of consecutive values in column H.)
- On the Dwelling tab, click on the Conditional Formatting dropdown (Types group) and select New Rule.
- Within the ensuing dialog, click on the Use a Method to Decide Which Cells to Format choice.
- Enter the perform ISODD($H3) into the system management within the decrease pane.
- Click on Format after which click on the Fill tab.
- Select a coloration—I selected gentle blue. When selecting, be sure that it doesn’t conflict with the sheet’s background, which can function the alternating coloration.
- Click on OK to see the perform and format proven in Determine C.
- Click on OK to return to the Excel sheet.
Determine D reveals the utilized format.
Remember that you will want to re-sort once you modify the data by altering the Personnel worth in an current report or by including a brand new report. When including new data, you will need to lengthen the expression within the helper column.
At this level, you possibly can be executed, as a result of the white is an alternate coloration. Nonetheless, you would possibly need to apply a second coloration.
Methods to add one other coloration in Excel
Proper now, Excel is making use of just one fill coloration. The even-numbered teams stay white—or no matter coloration the sheet background is. So as to add one other coloration as an alternative to fill the unformatted teams, repeat the above steps. In step 4, enter ISEVEN($H3), in step 6 select a second coloration. As you’ll be able to see in Determine E, I selected a lightweight inexperienced, and Determine F reveals the outcomes.
To me, this method appears a bit unfinished as a result of the consumer should develop the helper column as they add new data. As well as, the conditional format rule doesn’t can help you select a distinct group. To take action, you will need to replace the perform within the helper column. Nonetheless, if you would like highlighted teams, that is a straightforward option to get them.