New PIVOTBY function, get ready for Spreadsheet Day, and more, in this month’s Excel news.
Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
- New on Contextures
- Quick Tip: Reverse Names
- Spreadsheet Day
- Excel Quiz
- Free Online Conference
- Monthly Photo – Knolling
New on Contextures
If you’re using Excel 365, you should have the new Excel PIVOTBY function now.
It can summarize your data, similar to what a pivot table does, but a PIVOTBY formula updates automatically, if the data changes. A pivot table doesn’t do that!
To learn more about this function, I made an Excel file, where I can quickly test the optional arguments. For example, how does it sort the data, and what are the options for totals and subtotals?
If you’d like to try it too, go to the Excel Sample Files page on my Contextures site. In the Functions section, look for FN0075 – PIVOTBY Function Examples.
Quick Tip: Reverse Names
If names are in Last, First order in a worksheet cell, you can use a MID formula to reverse them, in another cell.
For example, if cell A2 has this name —Smith, Avery — put the following formula in another cell, to get this result – Avery Smith
- =MID(A2&” “&A2,FIND(“, “,A2)+2,LEN(A2)-1)
How It Works
- MID function returns text from a string (Smith, Avery Smith, Avery)
- FIND function gets the starting position (8)
- LEN function gives the number of characters to return (11)
There are written steps, a short video, and other ways to split or reverse names. on the Split or Reverse Names page on my Contextures site.
Spreadsheet Day
Next Monday is Thanksgiving Day, here in Canada, so enjoy the long weekend, if you’re celebrating!
And there’s another special day next week — Thursday, October 17th is Spreadsheet Day. I started this annual event in 2010, and it’s a tribute to VisiCalc, that was released on October 17th, 45 years ago (1979).
You can read more about VisiCalc, the first spreadsheet for personal computers, in this 2019 BBC News article: How computing’s first ‘killer app’ changed everything
This quote from the article was an accurate prediction:
- “What the spreadsheet did to accounting and finance is a harbinger of what is coming to other white-collar jobs”
Quick Excel Quiz
Do you know the answers to these two quick Excel quizzes?
- When were multiple sheets first allowed in Excel files?
- a) Excel 2.0 (1987), b) Excel 4.0 (1992), c) Excel 5.0 (1993), d) Excel 95 (1995)
- How many columns were in an Excel 2003 worksheet?
- a) 256, b) 365, c) 512, d) 1024
The answers are below the monthly photo, and you can find more Excel quizzes and polls on my YouTube channel.
Online Excel Conference
Starts TODAY — Don’t miss this free 2-day online Excel conference, featuring Excel MVPs from around the world.
- Excel Virtually Global 2024 (9th year for this conference)
- Starts: October 8th 12 AM (UTC)
- Ends: October 9th 8 AM (UTC)
For the access links, go to the event site.
- Note: Last year’s event was recorded, and available after the event ended. I hope they’ll do the same for this year’s conference sessions!
Photo: Knolling
Have you heard of knolling? It’s “artful tidiness” – arranging similar objects, so they’re parallel, or at 90° angles. I’ve seen magazine photos with tools or cooking supplies beautifully arranged, but didn’t know there was a special name for this technique.
So here are a few of my art supplies, arranged on a cutting mat grid. It’s like spreadsheet art! You can read more about knolling in this Dwell article.
And yes, things are a bit crooked in this photo, so I’ll do better next time!
Quiz Answers
- c) Workbook files, with multiple sheets, were introduced in Excel 5.0 (1993)
- a) Excel 2003 sheets had 256 columns and 65536 rows
That’s it for this month! If you have any comments or questions, please let me know, in the comments below!
Debra Dalgleish
ctxdebra@gmail.com
P.S. Visit my Contextures site for more Excel tips, tutorials and videos.
Get Monthly Excel Tips!
Don’t miss my monthly Excel newsletter! You’ll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
___________________
Original source: https://contexturesblog.com/archives/2024/10/08/debras-excel-news-oct-2024/?utm_source=rss&utm_medium=rss&utm_campaign=debras-excel-news-oct-2024