Contextures

Contextures News

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!

Excel Training

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.

Number a Filtered List

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:

=AGGREGATE(2,3,C$1:C2)

There are 3 arguments in this formula

  1. 2 is the code number for the COUNT function -- it will count numbers in the visible rows
  2. 3 is the options setting, to tell Excel what to ignore
  3. C$1:C2 is the range with numbers (dates) to count. The starting point is locked at row 1 (C$1), and goes down to the current row (C2).

aggregate function

Learn more about the Sum Functions on my site, and you can also number a filtered list with SUBTOTAL, in earlier versions of Excel.

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.

Excel Articles

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)

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

Canada 150 Food

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!)

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: http://www.contextures.com/newsletter/excelnews2017/20170627ctx.html

Debra Dalgleish
ddalgleish @ contextures.com

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.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: July 21, 2017 9:41 AM