MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Filter to 27 Invoices

November 15, 2017 - by Bill Jelen

Filter to 27 Invoices

Excel filters are great, but what if you need to find all records that match a long list of criteria? You need to find all of the line items on 27 invoices. Rather than click click click over and over in the filter, the Advanced Filter will quickly solve the problem

Watch Video

  • Tad asks: How can I filter to 27 specific invoice numbers?
  • With a large data set, scrolling to find the 27 invoices will not be fun.
  • Searching for each invoice and remembering to click Add to Selection will not be fun.
  • Instead - use the Advanced Filter
  • Set up a criteria range... First cell contains the Invoice heading. List the invoices vertically below.
  • Choose one cell in the data set. Data, Advanced Filter
  • Filter in Place
  • Use a Criteria Range
  • Select the criteria range including the heading
  • Click OK

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2175 how to filter to 27 invoices hey
  • welcome back to MrExcel net cast I'm
  • Bill Jelen today's question from tad
  • her in and to add in my Springfield
  • Illinois power excel seminar and he says
  • I have a large data set here how many
  • rows we have 15,000 rows and I need to
  • filter to 27 specific invoice numbers
  • and that is tedious boy tedious it is
  • I'm gonna speed up here and show one way
  • that you could do this then we'll come
  • back and show the faster way to do this
  • you
  • alright geez clearly that method is
  • never going to work it's going to be too
  • - whoo tedious let's just clear clear
  • that filter and actually turn the filter
  • off what we're gonna do instead instead
  • of going through one by one by one and
  • choosing those or searching for them
  • either way we're gonna set up something
  • called an advanced filter' and advanced
  • filter' has a data set this will be the
  • data set here and then optionally it has
  • a criteria range and the criteria range
  • has to have a heading from the original
  • data set so I take that invoice heading
  • from a1 and I paste it above my list of
  • invoices now watch how simple this is
  • gonna be come here to data and then
  • filter nope not filter advanced advanced
  • filter and we're a filter of the list in
  • place and my criteria range is going to
  • be this list over here with the invoice
  • heading make sure to include that
  • invoice heading and then click OK and
  • bam we get all of the line items for all
  • 27 of those invoices really really
  • awesome awesome
  • fast fast way to go all right well
  • filters and advanced filters recover my
  • book power excel with MrExcel - 2017
  • edition click that eye on the top right
  • hand corner all right tad from
  • Springfield how to filter 227 specific
  • invoices well with a large dataset
  • scrolling to find the 27 invoices will
  • not be fun even searching for each
  • invoice and remember to click ads the
  • selection will not be fun instead we're
  • gonna use the advanced filter you have
  • to set up a criterion range it's simple
  • it's a one column first cell has to
  • contain the heading from the original
  • data set and then a list of invoices you
  • choose one cell one data set data
  • advanced filter choose filter in place
  • specify the criteria range and then
  • click OK and you're done
  • I want to thank tat tad for coming to my
  • seminar in Springfield Illinois and one
  • thank you for stopping by we'll see you
  • next time for another net cast from mr.
  • Excel

Download File

Download the sample file here: Podcast2175.xlsm

Title Photo: nessaja99 / Pixabay

Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.