Contextures

Contextures News 20200128

Excel Freeze Panes Problems

January 28, 2020

Freeze Panes frustrations, AutoFilter macros, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Freeze Panes Problems

The Freeze Panes feature in Excel is a handy way to lock rows or columns in place, so you can always see your headings. It has an evil side though, which can torment you or your unsuspecting co-workers. If you're not careful, you can freeze more than you wanted to.

Did you know that when you click Freeze Top Row, Excel doesn't necessarily freeze row 1? It freezes the first visible row, at the top of the window. So, if row 7 is at the top of the Excel window, that's what's frozen in place. You can't scroll up to see rows 1 to 6 anymore, and you could waste time trying to "unhide" those rows. (I know, because it's happened to me!)

top visible row is frozen

Freeze First Column and Freeze Panes work in a similar way. Excel always freezes the top row(s) and first column(s) that are visible in the Excel window. So keep that in mind, to avoid problems!

Go to the Freeze Panes feature in Excel on my Contextures site for more tips, and a sample file.

On Contextures

Recent items from my Contextures site and blog.

Split Names Formula: Thanks to Alex Blakenburg, who sent this improved short formula for reversing names, after last week's newsletter. I've updated the How to Split Names page, and the sample file.

  • =MID(A2&" "&A2, FIND(",",A2)+2, LEN(A2)-1)

AutoFilter Macros: There are 2 types of Excel AutoFilters - one for the worksheet, and a different one for Excel named tables (Lists). If you're using macros to change AutoFilter settings, be sure to use a macro that's designed for the AutoFilter type that you're using.

See the AutoFilter differences on my Contextures blog, and examples of List AutoFilter macros on my Contextures site.

autofilter macro shows filter information

Excel Articles

Here are a few Excel-related articles that you might find useful or interesting.

Budget: If you know someone who wants to set up a personal budget in Excel, this free Money Master Class, by Gail Vaz-Oxlade, might help. In the Download Resources section, there are links to 5 Excel workbooks. Below that, click on "The Gail Way", for a PDF file with notes on how to use the Excel files. (Level-Basic/Int)

Excel Skills: On Reddit, someone asked, "I have two weeks to master excel - what should my game plan be?" and there are lots of good suggestions, including the free university-run Excel classes at Coursera. I didn't see any rude comments, but read Reddit at your own risk! (Level -All)

Excel Humour: See what people said about Excel recently. There's a great discussion on the tweet by Sailor Moon Krystle, about learning new Excel skills.

Also see: My Excel Products || Previous Issues

Dangerous Game

Last week, I read Halloween Party, by Agatha Christie, in which there's a game called Snap-dragon. Raisins and brandy go into a dish, and it's set on fire, then children try to grab the burning raisins, and eat them! What could go wrong? A couple of days later, I saw Flame Raisins at the Bulk Barn, so we'll have those instead. Much safer!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200128ctx.html
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
dsd@ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Related Links

Freeze Panes feature

List AutoFilter macros

Excel named tables

How to Split Names

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: January 24, 2020 4:36 PM