Dealing with the Freeze Panes Line

August 1st, 2013 by datapig Leave a reply »

This morning in the DataPig secret lair, I got an email from Nathaniel who writes:

"…is there any way to hide/format/remove freeze pane lines?"

What Nathaniel is referring to is the black line that Excel places on your worksheet when you apply Freeze panes.

.

In this example, I clicked on Row 3 and applied freeze panes. This ensures that rows 1 and 2 remain in a fixed position as I scroll down the worksheet. This is particularly useful when building fixed position menu labels on dashboards. Unfortunately Freeze Panes also adds this ugly black line which severely damages my calm.

.

As far as I know, there is no manual or code-based way to hide or remove these lines.

I suppose Excel does this by design, to ensure that some visual clue tells users that freeze panes are applied. Fair enough, but it's still ugly and annoying.

.

The only option you have is to camouflage the black line in a bevy of border formatting, or hide them with some shapes. Here's how I've handled these in the past.

After applying freeze panes:

.

Step1: Expand the Row

.

Step2: Set the Alignment for the row to Top Align, so that header labels move up.

.

Step3: In the space under the headers, then add a rectangle shape and position it appropriately.

.

Step4: Add a Shadow to the Inner-Bottom of the newly created shape.

.

Step5: Adjust the shape so that it just hides the freeze panes line, giving the sheet an effect of an embedded header and hovering data table.

.

Step6: Add color to match the theme of the dashboard or report.

.

In the end, you'll still have a freeze panes line, but it will be camouflaged by a shape that you can control and incorporate in the theme of your dashboard or report.

.

I will admit that this is a kind of crappy workaround. The entire charade is blown if your customers apply any kind of AutoFilter.

Excel really should give us the ability to at least format the Freeze Panes lines.

.

Well Nathaniel...there's your shitty answer.

Advertisement

5 comments

  1. jason says:

    hey man, how do you apply the underlines in row 2? they fill the cells separately and yet do not connect together as a whole, fascinating!

  2. datapig says:

    Jason: This is called Single Accounting Underline

    1. Highlight the cells
    2. Right-click and select Format Cells
    3. Click the Font tab
    4. In the Underline dropdown, select Single Accounting
    5. Press the OK button to apply

  3. Aron says:

    If you make your list a “Table” (Insert->Table), the column headers will stay visible when you scroll–giving you the same effect as freezepanes, without the line. In fact, it’s even cleaner – your Table Headers eclipse the Column Letters until you’ve scroll back up. Pretty slick.

  4. Steve says:

    Thanks for this. I’m a real fan fo the shadow effects. Charts with interior shadows can be great.

    It’s a perpetual annoyance if you enjoy the graphic design-side of building perpetual spreadsheets that the options for freeze panes are so inflexible (yet the feature so necessary!). That they are always ‘in front of shapes’ (as opposed to behind them, like gridlines and cell text/formatting) means there’s just no reasonable work-around. I like you’re solution here but it sucks that the formatting style has to bow to a technical restriction.

  5. Bryon Smedley says:

    “Damages my calm”

    You’re obviously a Firefly/Serenity fan.

    After reading some of your blog posts…

    “I’ll be in my bunk!”

Leave a Reply

Powered by sweet Captcha