How To: Use Excel data analysis filter & dynamic ranges

Use Excel data analysis filter & dynamic ranges

We're on sheet, tab, filter. We want to talk about filter, but we also want to talk about an Excel table or list. It is simply a data set with field names at the top, records in rows, no blanks anywhere and no other data sets touching this data set. When we convert to a table, it does a lot of amazing things. The new keyboard shortcut is "Ctrl T". If data is set up correctly, it will work. Click Ok. You can build a function based on a column of data, i.e. "Alt =" which is the shortcut for sum. Click in the sales. Use "Ctrl Shift" to highlight that whole column. Press enter. The formula is G2-G350. That 350 will update, if you start adding new records to the bottom. Press "Ctrl Down arrow". The way a table works is you can add data to the bottom. Press "Tab" and a new record pops up. 7/22/2004, northeast, Jerry, Pete's, KSG, 200, 500. See if it updates. Yes. Let's go back to filtering. Filter. Open drop down. You can filter today, next week, tomorrow, between two dates, next quarter, last year. You can expand these and select any single month in a particular year you want. Filter by region. Press "Filter =". Select "North". It's not sorted, but watch what filter does. Click Ok. Row headings are blue which means there are some hidden rows, but it instantly shows "North". That's different than sort. Sort brings records to the top. Filter hides the rows that are not equal to that "North". The other weird thing about filter is if you copy it, "Ctrl C, it automatically copies just the visible cells and you paste it. It pastes just those values. Get rid of filter by clicking the filter icon. Say "Clear Filter". Clicking escape gets rid of the dancing ants. Another nice aspect of filter is that you can filter numbers. Let's say > = 500. It will instantly filter on that criterion. Filter in top 10. There's also above average and below average. Clear the filter. You can filter by color. The viewer learns about using Excel data analysis filter.

Want to master Microsoft Excel and take your work-from-home job prospects to the next level? Jump-start your career with our Premium A-to-Z Microsoft Excel Training Bundle from the new Gadget Hacks Shop and get lifetime access to more than 40 hours of Basic to Advanced instruction on functions, formula, tools, and more.

Buy Now (97% off) >

Other worthwhile deals to check out:

Join the Next Reality AR Community

Get the latest in AR — delivered straight to your inbox.

Be the First to Comment

Share Your Thoughts

  • Hot
  • Latest