I have made a Book Archive in Excel 2013 that has the data sorted like this.

Book's Owner ¦ Book Cover ¦ Book Name ¦ Author ¦ Edition ¦ Language ¦ Topic ¦ Index

Since those are technical books only sometimes they have multiple authors so I had the idea to write the authors separated by a comma in the same columns with the idea I could have put a filter to filter them individually, something like Filter Comma Delimited Excel Column.

But I understood it being impossible, so I decided to try another approach. I separated the Author column in 5 different columns and I merged the the header as a single one.

What I want to do now is to be able to filter it by the header with a drop down list with all the authors, but with the AutoFilter function in the criteria it only display the authors that appears on the first column.

How can I make excel extend the criteria to the whole range of cells?

I'll attach some pictures to make you understand better.

enter image description here


I would solve this using the Power Pivot add-in. First I would split your Book Archive into two tables: 1. A new table e.g. Book Authors with just two columns, Book and Author. If a Book has multiple Authors, it would have multiple rows. 2. The current table, with all the Authors removed.

Then I would load those two tables into Power Pivot and set the relationship between them on Name (it might successfully Auto-detect that).

Finally I would build a Pivot Table (or Flattened Pivot Table) filtering on Authors and showing details of their Books.

  • I don't really get what you mean, I started using excel by two weeks so I still miss some basic knowledge. Could you attach a screenshot? – babs5 Aug 15 '14 at 7:40
  • Yeah maybe, but I'm not going to do a whole lot of typing ... can you share some sample data on Onedrive or similar? – Mike Honey Aug 18 '14 at 0:03
  • 1drv.ms/1pxlKAE – babs5 Aug 18 '14 at 9:25
  • Sorry that link doesn't work - it says "Sorry, we can't open your workbook in Excel Online because it exceeds the 5 MB file size limit. You'll need to open this in Excel." But then it wont open in Excel. I only need a sample - cut out most of the rows. – Mike Honey Aug 22 '14 at 4:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.