Number a Filtered Excel List
June 27, 2017
Number the visible rows 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. We're on our summer schedule now, so the next newsletter will arrive on July 11th.
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
Mynda Treacy has just opened registration for her highly-rated Excel Dashboard Course and Power BI Course. Get 20% off, if you register by Friday, July 7th. I highly recommend these courses, so don't miss this chance, if you're interested.
If you register for either of the full courses, you can get a bonus from me -- my data entry popup kit (DESP or DVMSP). Just use the course links above, and enter DebraD in the coupon code box when you pay for the course. Then, email me your receipt and I'll send you my bonus.
Free Webinars: If you're not ready for the full courses, attend one of Mynda's free one-hour webinars on Power BI or Excel Dashboards. They're only available for a limited time -- June 27th to July 13th.
Last week we used AGGREGATE to get totals for a filtered list. Another trick is to use AGGREGATE to number the visible rows in a filtered list.
In the screen shot below, the list is filtered to show Paper and Pens. Even though some rows are hidden, there are sequential numbers in column B -- 1, 2, 3, 4. Here is the formula in cell B2:
There are 3 arguments in this formula
NOTE: If you have a problem with the last row remaining visible, add two minus signs after the equal sign. I added spaces, to make it easier to see the minus signs:
= - - AGGREGATE(2,3,C$1:C2)
This is Workaround 2 on the SUBTOTAL link above, and you can read more about it there.
Here are a couple of recent Excel articles that you might find useful.
Clean Data - In this month's Excel Table Talk video, Carlos Otero shows how to import and clean up data from a text file, with Get & Transform. Then, instead of building VLOOKUP formulas, he connects two tables and creates a quick report. This might convince you to try Get & Transform, if you're not using it already! (Level - All)
Excel Tables - Mike Alexander has a free add-in that you can download. Just select a cell in an Excel table, then use this tool to get a list of the fields, and build a SQL Server table with the data. The code is unlocked, so you can see how it works. (Level - Intermediate / Advanced)
It's Canada's 150th birthday this Saturday, July 1st, so I planned a week of Canadian-themed suppers. Last weekend, I sat out on the patio, going through my cookbooks, then put the selected recipes into my Excel weekly meal planner. Everyone does that, right? ;-)
For inspiration, I found a list of iconic Canadian foods. Apparently we like maple-y, salty, sweet and fatty foods! We also love fresh local ingredients, so don't believe everything you read about Canadian food. (But some of it is true!)
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/20170627ctx.html
ddalgleish @ contextures.com
Last updated: July 21, 2017 9:41 AM