Contextures News

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!

Paste With No Borders

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!

paste special no borders

Sum a Filtered List

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

  1. 14 is the code number for the LARGE function
  2. 3 is the options setting, to tell Excel what to ignore
  3. Table1[Units] is the range with numbers
  4. Cell B2 contains the [k] setting - return the kth largest number

Learn more about AGGREGATE on my blog. And if you want to get really fancy, see this AGGREGATE example.

aggregate function

Excel Articles

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.

Also see: My Excel Products || Excel Events || Previous Issues || Excel Humour

Canada 150

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!

weekly photo

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:

Debra Dalgleish
ddalgleish @

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.




About Debra






Last updated: May 24, 2021 8:40 PM