Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How to filter merged cells to get more than the first row

I'm using Excel 2003. I want to be able to filter merged cells (that are in
one column, covering several rows) so that all the data associated with the
merged cell appears and not just the top line.

e.g. set up a table with two columns and an auto filter at the top. In the
left column put certain categories of data, for example say food types e.g.
fruit or meat, and then in the right column the actual food, so for fruit you
could have apples, banannas and pears. Now for presentation purposes I want
to have the left column merged so that the food category appears once
verically centered in the middle of the rows, but I want to be able to filter
this column to pick up all the data i.e. if I filter for fruit I want to get
apples, bananas and pears not just apples.

Help anyone?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: How to filter merged cells to get more than the first row

To filter merged cells in Excel 2003, you need to follow these steps:
  1. Select the merged cells that you want to filter.
  2. Click on the "Data" menu and select "Filter".
  3. Click on the drop-down arrow in the header of the merged cells column.
  4. Select "Text Filters" or "Number Filters", depending on the type of data in the merged cells.
  5. Choose the filter criteria that you want to apply, such as "contains", "equals", or "greater than".
  6. Enter the filter value in the text box.
  7. Click on "OK" to apply the filter.

When you apply the filter, all the rows that contain the filtered data will be displayed, including the rows that are hidden by the merged cells. You can then sort or manipulate the filtered data as needed.

Note that filtering merged cells can be tricky, as Excel treats merged cells as a single cell, which can cause unexpected results. It's generally best to avoid merging cells if possible, and instead use other formatting options such as centering text or using borders to create a similar visual effect.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default How to filter merged cells to get more than the first row

You could try grouping your data. First, go to Data/Group and
Outline/Setting and uncheck Summary rows below detail and Summary columns to
right of detail.

Let's say for example your data looks like

A B
1 Apple
2 Fruit Oranges
3 Pears


Select rows 2 and 3. Then click Data/Group and Outline/Group (do this for
all of your groups). When you filter for Fruit, you will initially see Fruit
in Col A and Apple in Col B, but you can expand the group (by clicking the +
sign on the left side of the screen) and should see the rest of the group.

Not a one step solution, but the only other thing I can think of is to put a
helper column to the right of your data (Column C) and enter "Fruit" in C1:C3
and change the font to white (so it does not appear on screen or print) and
filter on this column.


"Scruffy89" wrote:

I'm using Excel 2003. I want to be able to filter merged cells (that are in
one column, covering several rows) so that all the data associated with the
merged cell appears and not just the top line.

e.g. set up a table with two columns and an auto filter at the top. In the
left column put certain categories of data, for example say food types e.g.
fruit or meat, and then in the right column the actual food, so for fruit you
could have apples, banannas and pears. Now for presentation purposes I want
to have the left column merged so that the food category appears once
verically centered in the middle of the rows, but I want to be able to filter
this column to pick up all the data i.e. if I filter for fruit I want to get
apples, bananas and pears not just apples.

Help anyone?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i link merged cells to a merged cell in another worksheet. ibbm Excel Worksheet Functions 3 April 27th 06 11:40 PM
merged cells into one text cell, size varies dependant on text dat Jazzylady825 Excel Discussion (Misc queries) 0 December 9th 05 09:26 PM
checking that cells have a value before the workbook will close kcdonaldson Excel Worksheet Functions 8 December 5th 05 05:57 PM
Unable to sort in Excel because of merged cells Bruce Excel Discussion (Misc queries) 5 July 23rd 05 12:21 AM
paste special | values should work with merged cells PastingSpecial Excel Discussion (Misc queries) 1 June 20th 05 06:51 PM


All times are GMT +1. The time now is 12:29 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"