Written by Allen Wyatt (last updated September 7, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Let's say you have a list of company transactions. Each transaction includes a department number, a title, and other information (amount, date, authorizer, etc.). As you get more and more of these items in your list, you may want a way to automatically add "dividing lines" based on the department number. For instance, when the department number changes, you may want to include a line between the two departments.
To add this type of formatting to your list, start by sorting your data table by department. For the sake of this example, I'll assume that your data is actually in columns A:F, with the department numbers in column A. (See Figure 1.)
Figure 1. The data to be divided.
To add the automatic dividing lines, follow these steps:
Figure 2. The New Formatting Rule dialog box.
That's it; you should now see a line that appears across the entire width of your data every time the department changes.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6863) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Automatic Lines for Dividing Lists.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Sometimes the hardest part of getting your conditional formatting rules to work properly is figuring out the proper way ...
Discover MoreWhen you compare dates in a conditional formatting rule, you need to be careful how you put your comparisons together. Do ...
Discover MoreExcel provides a great conditional formatting capability that allows you to change how a cell appears based on critiera ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-09-07 08:31:43
Alex Blakenburg
Sadly although in standard formatting you have the option of Thin Medium & Thick borders, the conditional formatting only allows the Thin option.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments