Search Not Just Numbers

Tuesday 8 October 2013

Excel Tip: Don't do this with your table headings!

Another quick tip this week. This time it is a little bugbear of mine that I often see done by people not realising the problems it can cause them later.

It is really simple to get right if you know you need to!

See if you can guess what it is from the image. If you can't, I would recommend that you read on.

The problem is entering multi-row titles on any table of data.

I am not just talking about the table feature in Excel, but any table of data, such as the one in the picture.

In an earlier post I wrote about how to lay out data to make it useful.

The table format above fits all of these requirements, but falls down on the headings. Simply because you have used more than one line for the heading, it won't work properly if you decide you want to use AutoFilter, or a PivotTable!

This is really simple to avoid.

You can type the heading on one row and then apply word wrap to the cells on that row (select "Wrap Text" from the Home ribbon). If you want specific line breaks in the text, you can use Alt+Enter to insert a line break within the cell.

The data then fits all of the criteria to be used in a PivotTable, or to enable AutoFilter.

That's it - I'll get off my soap box now!

Don't forget that Mynda Treacy's Excel dashboards course will be available again soon for a short period of time. If you're not subscribed to the blog, do so now (at the top right of the blog) so that I can let you know when it's available - and to be notified of future Excel Tips.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

9 comments:

  1. "merge and center" is nearly as bad, too.

    ReplyDelete
  2. I would agree. I did a post on this before:

    http://www.notjustnumbers.co.uk/2013/04/excel-tip-do-you-really-need-to-merge.html

    ReplyDelete
  3. Dear Sir,

    I want to learn Macros in excel,, Please help me ....

    ReplyDelete
  4. Dear Sandeep

    I'm afraid that I don't offer anything at the moment. Another Excel blogger, Chandoo, does offer a course at:

    http://chandoo.org/wp/vba-classes/

    I've not tried the course, but his blog is excellent. I hope that helps.

    ReplyDelete
  5. Great post. Something as simple as keeping your column fields in one row is often overlooked and leads to huge problems down the road. I agree with the first commenter, doing a Merge & Center causes just as much problems as putting your data fields in multiple lines.

    ReplyDelete
  6. Hi Glen,

    great tip, this and merged cells are not my favourite things.

    Phil

    ReplyDelete
  7. Don't even talk to me about such cock ups as multiline titles and merged/centred cells. I find merged centred cells are worse as often the client wants to keep them because they "Look Great!". Grrrrr.

    ReplyDelete