Using Excel 2016 Relationships to Create a Movie Catalog Spreadsheet

Ed (or anyone who'd be willing to jump in here),

Just one quick question. Please.

I tried making a slicer, and couldn't. I then figured out why I couldn't. My ranges weren't converted to a table. So I converted them to a table. I then created a slicer for Movie Title. Voila, my very first slicer.

Problem: When I made a slicer for Movie Title, and selected a movie title, it did filter the worksheet. But...the slicer promptly disappeared. Vanished! What did I do wrong, and how do I prevent this from happening again?

Thank you! Jd
 
I'll bet I know why - where exactly did you put the slicer?

What I do is move my tables a few lines down the worksheet and have my slicers above them. That way, they are always there when I filter. You can remove the filter using the drop-down in the relevant column heading, of course.

Thanks for your nice words. :)
 
Hello, Ali!

I spent last evening reading and researching, including browsing this forum--and a couple of others, I admit.
I discovered when I converted ranges to a table, I included EVERYTHING...which turned out to be over a million rows. So, I learned how to limit the table to just the rows & columns being used. That solved the problem. My slicers are working pretty well now.

I'll attach my most current version of the spreadsheet to this message. I created only two slicers for the time being, but I can always change that.

There is something I discovered yesterday which is really helpful. I noticed when I use the Find & Select feature to search for a particular actor, each instance of that actor's name is displayed in the dialog box underneath the Find box. The listings in that box are actually links that you can click. If you click one of the links for a cell, it goes to it. That is EXTREMELY valuable, and precisely what I wanted. Thanks again to you and Ed.

Jd
 

Attachments

  • Alpha and Slicers.xlsx
    16.9 KB · Views: 35
Looking better. Couple of suggestions to make the DB look smarter:

1. Get rid of the underlining of column headings - embolden them instead - and reduce the font size.
2. Ditch the different coloured headings - they look very unprofessional.
 
Ali,
Thank you for the suggestions.

I have a minor follow-up question for you or Ed, or anyone else willing to help out.

In the enclosed image, the slicer I created is the one on the left. Ed's version is the one on the right. I much prefer his. I'm not quite sure how to modify mine so it looks like his.

Ed's has 25 letters of the alphabet (the Q is missing because there are no movies whose title begins with it), and mine has fewer than that, because the spreadsheet is a work in progress. But I'd like to know how to make the slicer look more like a keypad, which is what his looks like.

Is there a trick to it that I have yet to learn? Thank you, again. Jack

Slicer Before-and-After.jpg
 
With the slicer selected, look at the Slicer Options ribbon - you will see a columns option - increase the number of columns from one to five.
 
Perfect. Worked just fine. Thank you again. Jd
 
Back
Top