ThisCalculator

Everyday

Auto Borders for Cells With Content in Excel

Updated · Standard formula, shown below

Last row 10
Last column
5
Result
Row start
1
You entered
Column start
1
You entered

This article explains how to automatically apply borders to cells in Microsoft Excel and Google Sheets only when they contain data, using conditional formatting. You’ll learn the exact formulas and steps for both applications, how to handle blank cells, and how to avoid common mistakes.

Formula

For a range starting at row r and column c with rows rows and cols columns, the last row is r + rows - 1 and the last column is c + cols - 1. This is simple arithmetic, but the calculator above helps you quickly find the range boundaries when you have a large dataset.

Worked Example

Using the defaults: start at row 1, column 1, with 10 rows and 5 columns. Last row = 1 + 10 – 1 = 10. Last column = 1 + 5 – 1 = 5. So the range is A1:E10.

How to Apply Conditional Formatting for Borders

Conditional formatting lets you set a rule that formats cells only when they meet a condition. Here’s the step-by-step for Excel:

  1. Select the range where you want borders (e.g., A1:E100).
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose Use a formula to determine which cells to format.
  4. Enter the formula: =A1<>"" (adjust to the top-left cell of your selection).
  5. Click Format, go to the Border tab, and choose your border style.
  6. Click OK twice.

For Google Sheets, the steps are similar: select the range, click Format > Conditional formatting, set “Custom formula is” to =A1<>"", then set the border under “Formatting style”.

Handling Blank Cells and Formula Results

The formula =A1<>"" applies borders to any cell that is not empty. This includes cells with formulas that return an empty string (""). If you want to ignore those, use =ISNUMBER(A1) for numeric values or =ISTEXT(A1) for text. If you want to treat a cell with a formula that returns “” as blank, use =AND(A1<>"", NOT(ISBLANK(A1))) – but note that ISBLANK only checks for truly empty cells, not formula results.

Common Mistakes and How to Avoid Them

  • Wrong cell reference: The formula must use the top-left cell of the selected range. If you select A2:B10, use =A2<>"".
  • Borders not showing: Ensure you set the border in the Format dialog, not just the fill color.
  • Borders on entire row: If you want borders only on the used cells, not the whole row, use a formula like =AND(A1<>"", COUNTA($A1:$E1)>0) – but this is tricky; simpler to apply to a fixed range.
  • Forgetting to adjust range: If you add data beyond the formatted range, extend the range or use a table.

Alternatives to Conditional Formatting

If you prefer not to use conditional formatting, you can:

  • Use a table (Excel’s Insert > Table) – borders are automatically applied to the table range, but they don’t disappear when you delete data.
  • Use a macro (VBA) that loops through cells and adds borders based on content – more powerful but requires coding.
  • Manually apply borders – time-consuming for large datasets.

Frequently Asked Questions

Q: Can I apply borders only to cells with text, not numbers?
A: Yes, use =ISTEXT(A1) instead of =A1<>"".

Q: How do I make the border appear only when the entire row has data?
A: Use a formula like =AND(A1<>"", B1<>"", C1<>"") for the specific columns you need.

Q: Will conditional formatting slow down my spreadsheet?
A: It can if you use it on thousands of cells, but for typical ranges it’s fine. Using whole-column references (e.g., A:A) is slower; stick to a defined range.

Q: Can I copy the formatting to other cells?
A: Yes, use Format Painter or copy the range and paste special > formatting. The conditional formatting rule will adjust relative references automatically.

Q: Why doesn’t my formula work when I use it in Google Sheets?
A: In Google Sheets, the custom formula must be entered without the equals sign in the field, and you must select the range correctly. Also, use semicolons instead of commas if your locale requires it.

Related

Formula used
in r | Row start | - | 1 | 1
in c | Column start | - | 1 | 1
in rows | Number of rows | - | 10 | 1
in cols | Number of columns | - | 5 | 1
out last_row | Last row | - | r + rows - 1 | 0
out last_col | Last column | - | c + cols - 1 | 0

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.