Hiding grid lines and changing grid line colors


There are times where gridlines in Excel are useful. They provide a visual guide to differentiate columns, rows and cells.


A
B
C
D
1
Item
Units
Price
Total
2
1752
4
7
28
3
4995
6
1
6
4
4067
8
10
80
5
2392
14
6
84
6
3936
12
4
48
7
1697
5
4
20
8
3651
9
8
72
9
2708
12
9
108
10
3764
1
6
6

However there are times when you don’t want to see them (as in a dashboard).

Well you can remove the gridlines in one of two easy ways.

In the first way, you would change the background color of all the cells to white. First highlight the cells you want to remove the gridlines from then from the Home tab select the fill option of White. This is useful if you only want to remove the gridlines from a section of the worksheet. In this example I have changed the cells D2..D10 white. You will still see gridlines in the other cells.





















The second way is to remove grid lines from the entire worksheet. From the Ribbon, select the View tab, then un-check gridlines.









Finally you can even change the gridline color. First highlight the cells you want to change the gridline color in. Then from the Home tab select the borders drop down arrow and select more borders.

In this example I have selected the cells D2..D10 (just like before).























I choose change the color and Inside. (You need to change the color first). When done I click the OK button.














The result is…


A
B
C
D
1
Item
Units
Price
Total
2
1752
4
7
28
3
4995
6
1
6
4
4067
8
10
80
5
2392
14
6
84
6
3936
12
4
48
7
1697
5
4
20
8
3651
9
8
72
9
2708
12
9
108
10
3764
1
6
6




Calculating only when two cells are not blank


Earlier this week I received an email from a reader asking how to add / subtract cells only when there was no blank data between the rows.

I.E…. He only wanted to add / subtract cells in Column A and B when there were values in either cell or just one cell. If both cells did not have a value, then he didn't want to return a value.

Well this can be accomplished by combining an If statement with a test for blank value.
In this example, the formula sums in Column C for Rows 2 and 3 since there is a value in either Column A or B. For row 4, the formula does not return a value since Columns A and B are blank.

The formula checks for a blank value in the cell in Column A and for a blank value in the cell in Column B. If both are blank, the formula returns nothing “”, else it subtracts the cell in Column A from the cell in Column B.



A
B
C
D
1
column 1
column 2
A - B = Answer
Formula
2
1
1
0
=IF(AND(ISBLANK(A2),ISBLANK(B2)),"",A2-B2)
3

1
-1
=IF(AND(ISBLANK(A3),ISBLANK(B3)),"",A3-B3)
4



=IF(AND(ISBLANK(A4),ISBLANK(B4)),"",A4-B4)
5
4
1
3
=IF(AND(ISBLANK(A5),ISBLANK(B5)),"",A5-B5)
6



=IF(AND(ISBLANK(A6),ISBLANK(B6)),"",A6-B6)
7
6
1
5
=IF(AND(ISBLANK(A7),ISBLANK(B7)),"",A7-B7)
8
7

7
=IF(AND(ISBLANK(A8),ISBLANK(B8)),"",A8-B8)
9
8
1
7
=IF(AND(ISBLANK(A9),ISBLANK(B9)),"",A9-B9)

Repeat rows on top when printing, show headers on every page


We have all been there; you have a worksheet that you need to print. However the worksheet is more than one printed page in length and you would like to have the header row printed at the top of each printed page.

Well Excel allows you to easily do this. When you are on the tab that you want to print, Select Page Layout > then select Page Setup.






















This brings up the Page Setup Dialog Window.

Select the Sheet Tab and then fill in the rows to repeat at the top. If you click on the rows to repeat at the top then click on cell A1, Excel will automatically fill in the row for you ($1:$1).























You can also choose to repeat columns to the left using this dialog window.