How to make use of a border to discern teams extra simply in Microsoft Excel

If your information is grouped in Microsoft Excel, attempt displaying a border between teams. Learn how one can make your Excel tables simpler to learn.

how to microsoft excel group
Image: monticellllo/Adobe Stock

A big set of Microsoft Excel information with out good formatting is troublesome to learn. Table objects routinely apply alternating colours from one row to the following, which is nice for maintaining your eye on a particular document, however for those who’re working with grouped Excel information, the alternating strains aren’t notably useful. I’ll present you how one can use a conditional formatting rule in Excel to use a brightly coloured border line to divide teams.

I’m utilizing Microsoft 365 on a Windows 10 64-bit system, however you should use earlier variations by 2007. For your comfort, you possibly can obtain the demonstration .xlsx file. Excel for the net will show the conditional format, however you possibly can’t apply a formatting rule but.

SEE: Software Installation Policy (TechRepublic Premium)

How to transform Excel information right into a Table object

The best technique to make Excel information extra readable is to transform the information right into a Table object (Figure A).

Figure A

An Excel Table applies formatting to enhance readability.

To create an Excel Table object, click on anyplace inside the information and press Ctrl + T. In the ensuing dialog, be certain that the vary is appropriate and verify or uncheck the My Table Has Headers choices accordingly.

You’re not caught with the default formatting. You can rapidly reformat all the Excel Table in two methods.

  1. On the Home tab, click on the Format As Table dropdown within the Styles group and choose any of the types within the ensuing dropdown (Figure B).
  2. On the contextual Table Design tab, select any of the types within the Table Styles group. The choices are the identical in each choices.

Figure B

Quickly change an Excel Table’s formatting.

Knowing that you would be able to make this fast change is sufficient; we’re not going to assign a brand new fashion right now. What none of those types supply is a technique to distinguish teams.

How to show a conditional line in an Excel sheet

When you might have a busy sheet with dozens of rows and several other totally different information teams, not solely will the teams be harder to tell apart, however errors are nearly sure.

A fast resolution is to use an Excel conditional formatting rule that can show a pink line between information teams. You can show that pink line alongside the cells’ high border or backside border. If you go along with the underside, you’ll find yourself with a line on the backside of your Table, which you won’t need. If the Table shows a Total row on the backside, the pink border will override the Table’s double line between the information and the Total row. I’ll present you how one can apply the highest border rule and point out the underside border directions once they’re totally different.

You should kind the Excel information by the group column for this conditional format to work. With a distinct kind parameter, the strains haven’t any that means and can add to the sheet’s noise. Subsequently, this system is finest utilized to information that isn’t typically sorted.

Now, let’s add that pink border line.

  1. Select the information set however don’t embody the header row (B3:F13).
  2. On the Home tab, click on Conditional Formatting within the Styles group and choose New Rule from the dropdown.
  3. In the ensuing dialog, select Use a Formula To Determine Which Cells To Format within the high pane.
  4. In the underside pane enter the next formulation: =$B2<>$B3
    1. You wish to reference the header cell. That’s what makes it doable to show the border alongside the highest of the cells. Enter =$B3<>$B4 as a substitute for those who desire the border alongside the underside. The $ character is required: If you omit it, the rule gained’t work as anticipated.
  5. Click Format.
  6. Click the Border tab.
  7. Select the strong line kind on the backside of the Style checklist. Unfortunately, you possibly can’t change the road weight.
  8. Choose pink from the Color dropdown.
  9. In the Border pattern field, click on the highest line (Figure C). If you need the underside border, click on it within the pattern field.
  10. Click OK twice to return to the sheet (Figure D).

Figure C

Select from Excel’s border settings.

Figure D

The conditional format shows a pink line between information teams.

You can change the border coloration and magnificence, however as I discussed, you possibly can’t enhance the load. Remember to kind for those who add new data or change the information for an present document.

Stay tuned

Use Excel’s conditional formatting characteristic to show a border between information teams. In a future article, I’ll present you how one can use a Fill property to tell apart teams.

Source hyperlink

Leave a Reply

Your email address will not be published.