How To: 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.