Excel Visible Rows Only
March 10, 2020
Find the Nth weekday in a month, visible rows only, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Some events occur on the Nth weekday of a specific month each year. For example, US Thanksgiving is the 4th Thursday in November, and here in Canada, we celebrate Victoria Day on the 3rd Monday in May.
You can calculate those dates in Excel, with a couple of date functions -- DATE and WEEKDAY.
Here's the formula in cell C9 -- it uses names, instead of cell references:
+ ((SelN-(SelWD>= WEEKDAY(DATE(SelYr,SelMth,1)))) *7)
+ (SelWD- WEEKDAY(DATE(SelYr,SelMth,1))))
The formula caluclates the 1st of the month date, with the DATE function. It also calculates the weekday number for that date, with the WEEKDAY function:
For more details on the formula, and a sample file, go to the Nth Weekday page on my Contextures site.
When you build a pivot table, it includes all the data from your list, even if some of the rows are hidden by a filter. Sometimes though, you'd rather not have those hidden rows included, for privacy reasons.
In the past, you could use macros to copy the visible data to a different sheet, and build your pivot table from that new data. But now, with the new Excel function, FILTER, you can get the visible rows only -- with NO macros. Then, build the pivot table from the dynamic array.
Here are a few Excel-related articles that you might find useful or interesting.
Don't Worry: We knew this all along, but Tyler Folkman explains why you shouldn’t be embarrassed to use Excel, as a data scientist. (Level-All)
Programming: If you'd like to get started with Excel macros, the Think Like a Coder animated video series, by TED-Ed, is a fun way to learn some of the key concepts. Eight of the 10 episodes are available on YouTube, and kids might enjoy the series too. (Level-Int)
Excel Humour: See what people said about Excel recently. Did you start using Excel on a Mac or on Windows?
We had beautiful weather on the weekend, and lots of outdoor family fun on Sunday, with ball hockey, football tossing, and climbing trees at the park. Later in the day, we headed indoors, and made zines ("zeens") with happy themes -- we figured the world could use a smile or two! Zines are like tiny magazines, made from a folded sheet of letter-sized paper, with one cut in it. Make an Excel-themed zine, and give it to a friend!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200310ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: March 9, 2020 3:11 PM