Debra’s Excel News–October 2024

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.

pivotby function test optional arguments

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)

mid formula to reverse names

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?

  1. 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)
  2. 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!

monthly photo

Quiz Answers

  1. c) Workbook files, with multiple sheets, were introduced in Excel 5.0 (1993)
  2. 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

Close Menu