Excel Formulas for Filtered Lists
June 20, 2017
Find the 4th largest number in a filtered list, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
Last week's newsletter had a link to the Data Entry shortcuts on my website. Thanks to Alex B. for his feedback -- he saves time with Paste Special commands on the Quick Access Toolbar (QAT).
My QAT has a Paste Values button, but that "No Borders" option might be useful too, if you copy and paste data from pivot tables or formatted tables. It will keep all the other formatting, and just takes out the borders.
To add that command to the QAT, choose All Commands, then look for "Paste and Remove Borders". And remember to export your customizations when you're done!
Pivot Power Premium: There was a major update to my Pivot Power Premium (PPP) add-in. New features - click a button to quickly get the settings from any pivot table, then 1 click to apply those settings to any other pivot table, or all pivot tables.
To get the update, use your latest download link, and if you need a new link, send me an email.
If an Excel list is filtered, you can use the SUBTOTAL or AGGREGATE (Excel 2010 or later) functions to get results from the visible rows only. See a sum, count or average for just the selected items.
Use AGGREGATE, unless you need to send the file to someone with an old version of Excel. It has 19 functions, while SUBTOTAL only has 11. You can also tell AGGREGATE to ignore errors.
In the screen shot below, the list is filtered to show Binders, and the formula in cell C2 shows the 4th largest amount, even though there is an error in one row. You could type the 4 in the formula, but a cell reference makes it easy to adjust.
There are 4 arguments in this formula
Here are a couple of recent Excel articles that you might find useful.
Data Science - There's an interesting collection of Data Science links on this public Trello board. Scroll across to see different categories, and click on any card to see the details. Point to a coloured label, to see what it means. (Level - All)
Power BI - The videos from the recent Microsoft Data Insights Summit have been released, and Chris Webb has a helpful list of sessions, with links to the related videos. Matt Allington presented two sessions, and posted the videos from those. (Level - Intermediate / Advanced)
Training - Learn more about Pivot Tables or Power Query, in the free 1-hour webinars that John Michaloudis is hosting. Pick a date and time that fit your schedule.
We're counting down to Canada's 150th birthday on July 1st, so here's a photo of a famous Canadian landmark -- Niagara Falls. When I took the photo, we were enjoying a fall vacation, and having dinner, way up in the Skylon Tower. The dining room revolves slowly, so you get an amazing view of the Niagara area. But don't leave your table for too long, or you won't be able to find it again!
That's it for this week! If you found any of these tips especially useful, let me know.
NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: https://www.contextures.com/newsletter/excelnews2017/20170620ctx.html
ddalgleish @ contextures.com
Last updated: July 21, 2017 9:41 AM