Video: Daily Events Calendar in Excel

In this short video, I show how to use the daily events Excel calendar, and I give a quick look at how the calendar formulas works.

• Tip: To follow along, you can get the Excel file from the Excel Calendars page on my Contextures site.

Go to a Section:

• Step 1: Prepare the Events Sheet
• Step 2: Use the Settings Sheet
• Step 3: See the Calendar View
• Step 4: Add and Manage Events
• FAQ

Step 1: Prepare the Events Sheet

In the Excel calendar workbook is named Events. It has a named Excel table, and that’s where you list each event in its own row. You’ll enter 3 bits of information for each event:

• Date (the day of the event)
• Time (start time)
• Short detail (what the event is)

For example, enter “5/14/2025” in the Date column, “5:00 PM” in the Time column, and “Dinner” in the Detail column.

The event list also has helper column, with a formula that combines the time and detail into one cell. The calendar view uses that combined data.

Step 2: Use the Settings Sheet

The Excel file also has a sheet named Settings. That sheet has input cells, and formula cells, that control how the calendar view  looks:

• Optional start date for the calendar.
  • Leave it blank and the calendar will start on the week of today’s date.
• How many items to show per day (the sample uses 5).
  • If you need more items per day, you’d need to make changes to the calendar sheet’s structure
• In the event list, which column number has the combined data? (column 4).

The formulas on the Settings sheet calculate the six-week range for the calendar, and the start date for each week.

• Note: The only thing you should change on this sheet is the Start Date cell. Leave the other settings and all the formulas untouched.

Step 3: See the Calendar View

After you enter your upcoming events, you can switch to the Calendar sheet. It shows your events in a six-week layout.

I like this view is great because it gives an ongoing look at upcoming events, for 6 weeks.

Here are a few key features of the calendar view:

• The current date is highlighted, so you can spot today’s events quickly.
• The first day of each month is highlighted to help you see month starts.
• Each day shows up to 5items.
  • If there are more, the cell shows a red date with two asterisks to warn you there are extra items.

The calendar sorts events by time automatically. You can add events in any order on the Events sheet and the calendar will place them at the correct times for each day. This background sorting keeps the Events list simple to update.

Step 4: Add and Manage Events

To add an event, go to the Events sheet and type a new row.

For example, add today’s date, a time like “2:00 PM”, and a short note “Meeting”.

Switch to the calendar and you will see the event in the right spot.

• If you add multiple events for the same day, the calendar view will automatically sort them by time.
• If a day has more than five events, look for the red date with two asterisks.
  • That tells you to open the Events sheet to see the rest of the items for that day.

Get the Excel Calendar File

To download the Excel Events calendar, go to the Excel Calendars page on my Contextures site.

The zipped file is in xlsx file format, and does not contain any macros.

_____________

Build a Daily Events Calendar in Excel

____________

• Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Holiday Planning and Shopping

We’ve already had one big snowfall this month, so it’s time to start planning for the holidays. In Excel, of course!

To help you track your holiday budget, to-do list, and more, get my free Excel Holiday Planner

The workbook also has a holiday dinner scheduler.

I use it to stay organized for all of our family gatherings — things can get chaotic, trying to get everything on the table at the right time!

My Holiday Spreadsheet Mug

To help myself get into the holiday spirit, I bought this festive mug at our local Walmart last week.

There’s a folk art Christmas tree on the outside, and a colourful spreadsheet inside.

• Some people might tell you that it’s plaid, but just ignore those people.

And if you’d like your own “spreadsheet” mug, there’s a nice selection on Amazon!

• This link goes to the spreadsheet (plaid) Christmas mugs on Amazon
• And if you need a gift for someone who loves spreadsheets (or for yourself), this link goes to spreadsheet mugs on Amazon.

Skillwave Training Black Friday Sale 2025

It’s the end of the year, so it’s the perfect time to improve your skills in Power Query, Power BI, Power Pivot, DAX,  and data visualization.

• Head into 2026 with a new set of tools in your data analysis toolkit
• Be ready to take on new challenges at work.
• And make your current work easier too!

The perfect place to learn those skills is with Ken Puls, in his highly-rated courses at Skillwave.

And Ken’s having a Black Friday sale, so don’t miss it!

• Click this link to see the Skillwave courses
• In the checkout, to save 25%, use the code 2025CYBER
• The sale ends Friday, December 5th, 2025

XelPlus Training Black Friday Sale 2025

Excel expert Leila Gharani is also having a Black Friday sale, on her XelPlus site.

You can save up to 45% on her course bundles for Excel, automation, Power BI and Python.

• • Click this link to see the XelPlus courses
  • You get lifetime access to the courses
  • In the checkout, discounts are automatically applied
  • The sale ends Tuesday, December 2nd at 11:59 PM (Pacific time)

More Excel for the Holidays

Here are links for more holiday Excel files and tutorials, on my Contextures site.

Excel Advent Calendars

Excel for the Holidays

Excel Christmas Planner

Excel Christmas Tree – icons

______________________________

Sample Excel Files

In the screen shot below, there’s a list of four Excel files that you could download.

• Employee Time Tracking – This has employee data, with a pivot table and chart to summarize the records
• Employee Scheduling – This has fancy data validation, so you can’t double book an employee!
• Date Calculations – This has date calculations, which are always useful in Excel!
• Daily Walking Record – I still keep track of my steps in Excel every day!

There are hundreds of sample files on my Contextures site now, and you can find them on the Excel Files page.

Data Validation Tutorial

One of the first tutorials on my site was in Introduction to Data Validation.

In the screen shot below, the table of contents shows the five topics that I covered.

• What is Data Validation
• Provide a Drop-down Lis of Options
• Data Validation – Create Dependent Lists
• Hide Previously Used Items in a Dropdown List
• Display Messages to the User

There are lots more data validation pages on Contextures now, and you can see them on the Data Validation Topics page.

Many More Excel Topics

I still love writing about data validation, pivot tables, and Excel functions, and there are many more Excel topics covered on my Contextures site too.

To see some of the Excel topics and tutorials, go to the Excel Tips and Tutorials page.

• Near the top, there’s an A-Z set of links, Excel Topic links, and What’s New.
• Below that, there’s an alphabetical list of Excel topics, to help you find what you need.
• In the screen shot below, the start of the Data Validation section is showing

Thank You for Supporting Contextures

Thank you for stopping by, to see what’s new on my Contextures site, or to find the solution to an Excel problem.

Excel is constantly changing and improving, so there’s always something new for me to learn, and write about.

I deeply appreciate your support over all these years! Your comments, and emails, and site visits, inspire me to keep going.

And Excel is more fun when it’s shared with other Excel lovers!

40 Years of Excel

Since today is the Contextures website’s 25th anniversary, it was a lovely coincidence to get a gift from Microsoft this morning!

This package is for Excel’s 40th anniversary celebration, and it came in this fancy fx box, carefully encased in bubble wrap.

Honestly, that would have been enough of a gift – a “good” box, and nice bubble wrap.

But there were gifts inside the box too!

• First, a green baseball cap, with fx on the front.
• Also, a matching green hoodie, which also had fx on the front.

There was also a nice note, and a fun slogan on the inside cover of the box:

• Making sheet happen since 1985

What’s on the Back?

The Excel fun continues on the back of the hat and hoodie!

• On the back of the hat, it says 40.xlsx
• And on the back of the hoodie, it has that fun slogan: Making sheet happen since 1985

I’ll wear these to Costco from now on, so people will get out of my road, when I roll down the aisle!

And here’s to many more years of exploring, and learning, and sharing, with Excel and Contextures!

___________________

What Is Modern Excel?

On the weekend, I watched a video meetup, “Modern Excel … a Replacement for the Spreadsheet?”

The video was interesting, and I’ve embedded it below, along with the full transcript, so you can watch (or read) it too.

There are lots of online references to Modern Excel, but I’ve never seen a clear-cut description. Maybe you’ve found one.

• Does Modern Excel start with a specific version of Excel?
• Is it any Excel version with dynamic arrays?
• Is there a different definition?
• Or is Modern Excel indefinable?

Excel Vs Jenga Game

Anyway, near the start of the video, the presenter, Peter Bartholomew, compared traditional spreadsheets to the wobbly stack of blocks that you build in the Jenga game.

Here’s a picture of our copy of the game. We’ll probably play next Sunday, after out Thanksgiving dinner!

And here’s a link to the game on Amazon, in case you want to play too!

Video Meetup Notes

The video, that you can see a bit further down the page, is a recorded live stream virtual meetup.

• Title:  Modern Excel … a Replacement for the Spreadsheet?
• Date: The live stream virtual meeting was held on October 1, 2025.
• Host: Excel MVP, Danielle Fairhurst, from Plum Solutions. On her website, go to the Virtual Meetings page, to see the list of upcoming meetings, and watch the recordings from her past meetups.You can find her on LinkedIn.
• Speaker: Spreadsheet expert, Peter Bartholomew. You can find him on LinkedIn.

My Notes & Details

While watching the video, there were a few acronyms, names, and words in the presentation that I had to look up. I wanted to verify spellings and meanings, while proofreading the transcript.

I’ve put my notes here, in case some of these items are unfamiliar to you too.

EuSpRIG: In the Virtual Meeting summary, it mentions that Peter presented a paper at the EuSpRIG 2025 conference.

• EuSpRIG =
  European Spreadsheet Risk Interest Group
• If you visit the EuSpRIG site, be sure to read some of the spreadsheet horror stories!

FAST: At the 4:26 mark, Peter mentions the FAST standards.

• The FAST standard is a set of rules for designing spreadsheets that are:
  • Flexible, Appropriate, Structured and Transparent.
• You can read or download the full guide on the main page of the FAST Standard Organisation (FSO) site.

AFE:
At the 7:52 mark, Peter mentions the AFE (Advanced Formula Environment).

• AFE is a feature in the Excel Labs add-in for Excel.
• It lets you write, edit, and reuse formulas.
• You can get Excel Labs in the Microsoft AppSource

5G Components (Five G): At the 23:26 mark, Peter mentions Excel MVP Craig Hatmaker’s 5G components.

• Learn more, and get the free templates, on Craig’s site, Beyond Excel (BXL)

Carolina Lago: At the 27:28 mark, Peter mentions Carolina Lago, from Tactic Financial

• See Carolina’s work on her Tactic Financial site

Silcrow: At the 45:25 mark, Danielle asks about the silcrow. It’s a section sign, §, that looks like a double “S”.

• Read more about section marks in this Wikipedia article.
• You might be familiar with the pilcrow, ¶, which is a paragraph mark, that looks like a backwards capital “P”.

Video: Modern Excel … a Replacement for the Spreadsheet?

Here’s the video, and the full transcript (slightly edited) is below the video.

• Do you use any of the Modern Excel tools that Peter showed?
• Are you ready to move to this type of spreadsheet construction?

Video Summary (by Danielle Fairhurst)

• The introduction of Dynamic Arrays has fundamentally altered how spreadsheets are built!
• Watch to hear from Peter Bartholomew to explore how ad-hoc end-user practices are giving way to structured, programming-like approaches, thanks to new capabilities like LET variables, LAMBDA functions, recursion, and advanced array handling. The talk will highlight how these innovations enable the creation of robust, algorithm-driven spreadsheet solutions, moving far beyond traditional tips and tricks.
• This presentation draws on insights from Peter’s recent paper delivered at the EuSpRIG conference, where he explored how embracing “array thinking” and the new Excel programming paradigm can reduce risks and unlock unprecedented flexibility for spreadsheet developers and modellers.

Video Transcript

Here is the full transcript, which was auto generated by YouTube, and slightly revised by me (for capitalization, spelling, etc.).

If you spot any errors in the transcript,
please let me know!

• 
  Note: I adjusted some really long lines of text, so a few of the transcript time stamps might be off by a second or two.

‘——————–

‘—–TRANSCRIPT START—–

Virtual Meeting – Start

0:00    Well we’ve got uh Dallas, South Africa, Namibia, Brisbane, Australia,
0:07    Abu Dhabi, Egypt, Nigeria, all over the place.
0:09    Welcome, welcome. My name is Danielle Stein Fairhurst. I’m based in
0:14    Sydney, Australia. I’m a financial modeling specialist and I love talking
0:18    about Excel and financial modeling and I love running
0:21    these virtual meetups where I invite special guests uh relating uh
0:27    that have something to say about the world of Excel and financial modeling
0:33    and I’m very excited to welcome Peter Bartholomew uh here is he’s joining us
0:39    from the UK today so morning his time, evening for me. Peter Bartholomew is by
0:44    his own an admission not a financial modeler but his contributions to modern
0:50    Excel means that his work is of a lot of interest to financial modelers. He’s got
0:56    a lot of uh he talks to a lot of financial modelers in particular with the
0:59    new dynamic arrays and when LET and
1:02    LAMBDA came out and uh he’s been doing a lot of work in the development
1:07    and it means that his work is very relevant to the financial modeling
1:10    community and I’m sure he’ll uh tell us a lot more about that.
1:14    So, I’ve known Peter for many, many years. Uh, and we finally got to meet in
1:17    person in London earlier this year, and I’ve been wanting to have him speak to us
1:24    for quite some time, and I’m really happy that he’s agreed to do it. So, welcome,
1:30    Peter. I’m going to let you take it away.

Peter Bartholomew Presentation Starts

1:34    Okay. Thank you, Danielle. That’s uh that’s yeah, that was
1:38    a a good session, wasn’t it, in London? Yeah, that’s uh the uh
1:44    that was well that’s I I have been campaigning
1:49    for better arrays uh to Microsoft oh at least before uh 1915 that’s 1915
1:59    no not 2015 we’ll make that and uh was overjoyed in 2018
2:06    when I actually got, oh I bet, something that was not just what I
2:12    wanted but but better. So and what I have found is that it changes everything
2:21    to the extent that when I’m asking answering questions on fora
2:27    a common follow up is where do I put the code? The original post poster hasn’t
2:35    even recognized the solution as being Excel. Now that leads me to think that
2:41    possibly something’s changed. So uh um let’s see whether I can uh ah
2:50    down page down should work. Yes.

2:55    So what I’m going to do is try and show you
2:59    why I believe that LET and LAMBDA don’t just enhance traditional methods,
3:05    they actually offer a completely different approach
3:11    within the same package. Um they do share some common features.
3:17    Both are based on the grid and they share a common library of functions.
3:24    Beyond that, not so much. That’s pretty much everything beyond that can be
3:30    different. Um I sometimes think that if the traditional spread spreadsheet
3:38    were a building set, it would be piling up Jenga blocks. They stack to the
3:42    point where the whole edifice becomes ever more wobb wobbly
3:51    sort of built on its own inconsistencies
3:56    and eventually the whole lot comes crashing down

4:01    that uh and I have seen some very unstructured workbooks that uh
4:06    it’s a wonder they ever hold together and come to that only about uh 10%
4:13    of spreadsheets at the most are actually correct and do hold together.
4:20    So uh um say that the standards might help things
4:26    like FAST give you enforce a very regular pattern
4:31    and if I’m trying to uh refactor a an
4:37    analysis starting on somewhere the something that’s quite well defined does
4:43    help. So, but they’re still built on the same
4:48    shaky foundations.

4:51    Now, we’ve got this the new shiny thing
4:56    in town of LET and LAMBDA. And there is a temptation
5:02    to see this as the clever bits, the bits for advanced users that uh the the bit
5:10    that goes as the the pinnacle of creation balanced on top of your existing model
5:17    and I believe that is fundamentally flawed and a wrong way of doing it.

5:25    The LAMBDA is not the crowning glory of an ivory tower. It is something far more
5:34    basic than that. So it’s a more structured setup in a way. You have far
5:41    less flexibility than you do with the traditional spreadsheets,
5:47    but it’s more solid. And what you can
5:52    then do is build from the ground up using LETs, LAMBDAs, and so on.

6:00    Um the the rules of construction are simply different but what you build can do
6:07    solve the same problems and in a better way. So
6:13    if I if I compare that’s the traditional and modern
6:21    um now I would hold all my variables as LET variables.
6:27    They’re somewhere in memory. I don’t know where they are.
6:30    I don’t care where they are. I reference them by name. On a
6:34    traditional spreadsheet, they would be visible in cells and they would be
6:40    referenced by the location of the cell. Um, I believe I can get more meaning but
6:47    with the name. The formula that traditionally there’s the illusion that
6:54    the formula is in the cell. It’s not. There’s no such thing as the grid. It is
6:59    just uh an illusion, an image, graphic image built up. Um

7:04    You are already blowing my mind, Peter, already. What are we eight minutes in
7:09    and I’m like, okay, it’s just what I thought was everything
7:16    I thought was true, you’re telling me is no longer the case.

7:19    Yeah. Yeah. It’s just an illusion. And
7:22    the originators the spreadsheet knew that they were expert programmers
7:29    writing for non-programmers and creating an illusion. So uh it’s uh that
7:38    now the formulas are also in the as the alternating LET parameters where you
7:46    that uh they clearly define the parameter and to view them it’s best in
7:52    the AFE because the name manager is dreadful but uh

7:59    um the way you handle arrays that uh traditionally
8:05    they are just collections of scalar cells flying in close formation.
8:11    Mhm. That they address to one by one by using the concept of relative referencing.
8:18    If I drag a formula down one cell then the cell everything I references goes down
8:24    by one cell. It’s an absolutely awful way of doing it. But uh it it works for
8:31    non-programmers. It’s um that now it is the array that is the
8:39    object that it has elements. Fine. It’s well its properties are defined in terms
8:45    of elements but the array is the object. You address it by name. I named the
8:51    anchor cell and then put hash to get a dynamic array and that that’s actually a
8:57    range on your sheet. um the user intent
9:04    that’s from a traditional spreadsheet it’s just implicit. You know that if the
9:10    spreadsheet is put together by a financial modeler and it’s offered to
9:15    other financial modelers they can they
9:18    have a shared background experience and can actually see what’s going on. that
9:25    um there’s no no statement of what’s being done at every any stage. Whereas
9:32    now with a LAMBDA function, the function like any function, the built-in
9:38    functions, they should tell you what the process step is, what you’re doing to
9:44    get the new results, that you’re sorting things, you’re filtering them, you’re
9:49    doing something. And it’s that intent that I would uh seek
9:57    The precedence. What what is it that’s being processed?
10:00    You’re doing something, but to what? Well, it’s you click on a
10:05    precedence tree in or use control some square brackets.
10:13    I’m was never very good at the keyboard shortcuts. Um, but now
10:19    you have an explicit argument list in in parentheses following the array, the
10:25    function name. So it’s all there what you’re doing and what you’re doing to
10:30    it, what you’re doing, what you’re doing it to is there explicitly.

10:38    Um so oh that’s it’s built on the concept of
10:45    multi-dimensional arrays. Just embrace it and don’t look back because if you
10:51    try and build in what you already know into the new you’ll get yourself into a
10:58    mess. You’ll keep on thinking oh I could just do like that like and then
11:02    what you do won’t be dynamic. it won’t work with the rest.

11:06    It’s very we we know this, but it’s very hard to do in practice
11:09    because we often have these models
11:14    that have already been built and we’re sort of adapting them and
11:16    I’ve lost sound.
11:18    Oh, you can’t hear me,
11:20    aren’t you’re there again.
11:21    I just said that it’s it’s quite difficult to do
11:24    that in practice because it’s it you know you you inherit these legacy models
11:31    and they’ve already got older traditional formulas there and it’s not
11:38    really practical to replace everything with a dynamic
11:40    It is well that’s I haven’t used the cell reference
11:43    since 2015 when FAST told me I shouldn’t use names
11:51    And I thought, you’re not right for that for that moment. I banned cell
11:59    references from my workbooks.
12:02    I remember you telling me this a long time ago that you never ever
12:05    use cell references. And I was like, what? How can how can you do that?
12:08    And of course, it makes sense once
12:12    you’ve got the dynamic arrays working.
12:15    Yeah. Oh, it was really painful with cse
12:17    arrays and uh defined names but yeah it’s possible

Examples

12:24    right so let’s try and give of just a couple few examples of things that I
12:30    mean when I say life is different and the the the
12:37    algorithms that I implement are now or I implement that’s uh that’s not jump to
12:45    conclusions, um, use array manipulation
12:51    as a as an integral part of how you do how I do things. Take a I’m trying to
12:58    start with a simple problem of uh monthly reports for a various items
13:07    and somebody says okay I want quarterly I’m not I don’t need that
13:10    level of granularity monthly, quarterly will do me fine. Mhm.

Array Shaping

13:14    How do you do it? Well, there there are a number of ways of doing it, but the
13:19    one that I want to illustrate uses array shaping. So, what I want is the same
13:27    figures by quarters. So, that’s sums if
13:32    I can see it down the corner, but should be the one 194 there. And uh so
13:41    what a I have done let’s see if I can get this to work
13:48    is and I will also open that up
13:54    is I first counted that there are four quarters that I’ve got to to reproduce
14:01    and I I’ve then just rounded it up so it’s
14:07    the December that was missing I’ve so I’ve got a round number of quarters.
14:12    That’s just admin. To go to a single column, the the target is to wrap it. So, I’ve
14:20    wrapped the figures across the uh sheet in threes. So, it’s January, February,
14:27    March, April, May, June. And by rearranging the array, the data, I’ve
14:34    now got a simple calculation that all I’ve got to do is sum each sum
14:41    by row and I’ve got the quarter values. So
14:48    that’s summed by row. Mhm. This this quantity by quarter is by row
14:54    arranged by quarter sum. And uh that’s pretty almost the job done
15:02    except that I want to wrap it back under the byproduct.
15:08    And so that’s just another wrap. And then uh if this is working data
15:16    as part of a calculation, it stays as it is. But if it’s for user consumption,
15:21    if it’s part of presentation, then I need to pretty it up with
15:26    possibly a total row and the and a heading row. And dynamic arrays are very
15:34    nice at changing the output formats and uh give you quite a lot of flexibility
15:40    whereas to do this by cell, cell by cell would be quite awkward really.
15:47    M um okay that so that’s so that’s array shaping

Accumulation and Stacking

15:49    Next I want to
15:55    do to show problems that can be solved by um accumulation
16:02    and uh stacking so
16:08    a tax return that this Dennards quoted this at one time those are the
16:16    thresholds to some US tax bands for 2022, I think. And those are the
16:24    associated rates. And what I want to do is given a taxable
16:31    income, I want possibly just to produce
16:36    the amount of tax, but also the possibility
16:42    of a full breakdown in which you look at each band, how much tax is payable, is
16:50    chargeable in that band, and what the tax is. So that that’s that’s the
16:57    challenge. Um you can see my formula is tax calc,
17:03    the thresholds for the US system rates
17:07    and that I want a full print out. If I delete that.
17:13    Don’t know what yes only gets. So I better type true
17:18    again.
17:23    uh and go to so that that’s the uh the
17:29    exercise and
17:34    what I do is these are the the lower the the lower
17:40    thresholds I want to get the upper bounds the limits and
17:45    so I drop the zero at the bottom
17:50    and then the thing drops down. But I’m sneaky. I put the income in, stacked into
17:58    the loop. So the income gets sorted in the correct place.
18:05    The next step is to look at the bands, which is the upper limit minus the threshold.
18:08    Oh, I suppose I ought to be showing you
18:12    a little bit about what’s going on. So let me go back. Those are the upper
18:21    bounds. That is the the 140 is the is the amount you see up there. And uh now
18:31    that is the entire bands, the entire second band, part of the third band
18:39    because it’s limited at the top by the actual taxable income.
18:47    The next step is to multiply it by the tax rate to get the tax due.
18:55    And then I have the uh the the breakdown
19:01    at which I I stack all of the things together. I calculate the total
19:08    and I return a pretty little table with the
19:15    tax due at the bottom and all all of the breakdown.
19:19    I could if I wanted and if I’ve got uh
19:23    if I if I’m the accountant and I’ve got a lot of 150 of these
19:28    things I could just put the incomes in with the client name and just bash out
19:36    the tax as single statements so that I’ve got a summary one but uh
19:42    okay so that’s an example of that

19:45    that’s great there’s a there’s a Paul Clark has just popped another example
19:49    into the chat which is a big long uh sort of code uh which is a similar kind
19:56    of approach but you know more using using formulas um with a LAMBDA formula
20:04    uh personally I just go with the uh close MATCH XLOOKUP which is a a much
20:09    simpler way of doing it but um yeah as always there’s about there’s multiple
20:14    ways of achieving the same result.

20:17    Yeah. Yeah. quite that
20:19    Uh pity I can’t see the uh the discussion that uh
20:26    Oh, that’s okay. I’ll send it to you later.
20:29    Okay, that’d be great.

FIFO Example

20:31    The other one that I didn’t know whether to skip or include
20:35    it depends on how much financial modeling I wanted to do
20:38    today or you wanted me to do. Interesting. Was a FIFO f first in first out
20:46    calculation. which I have a set of inputs, dates which aren’t particularly used but
20:53    could be useful for working out the time that stock is held and the warehouse
20:59    costs and so on. um the volume in the price in and then the customers the the
21:06    the outputs dates volumes and the the one possible goal is for any
21:16    given output batch you you work out the input costs
21:21    um I’ve also done this uh for iron ore
21:27    so that you for any given customer batch you know what the iron the uh content is
21:35    so it’s the same calculation and uh I accumulate the inputs so those
21:44    essentially are serial numbers that act as a flag something happened you ran out
21:51    of a of a supply at that point um I do the same with the outputs that
21:58    you fulfilled an order. At this point, I stack the two and sort them. So now I’ve
22:06    got um divisions with uh each one is a
22:12    tranche. I can work out how many items are in the the tranche, where it came
22:18    from, and where it went to. And that’s is the basis for you know their number
22:25    of possible calculations. So that’s the table that I build up
22:33    and uh so that those are the volumes of each tranche. Those are the customers it
22:40    goes to. That’s where it came from. That’s how much it cost. And uh
22:49    the formula is th this is done as a a Craig Hatmaker 5G component.
22:55    So if I if I want to get something to a
23:01    deliverable standard that’s uh Craig’s work is amazing. It’s what happens when
23:06    you let a professional an IT professional into a spreadsheet and it
23:12    comes out as something looking totally different in concept. Um,
23:18    so that so you know I feed some ideas into
23:26    Craig’s 5G components. He exploits them and occasionally I step
23:33    up to the mark and up my game and produce something that is transferable,
23:38    usable, readable and documents with change control and all the rest of it.
23:45    Um yeah it’s it’s quite a different concept from the normal spreadsheet
23:50    approaches but uh the the bit I’m going to show is uh I I do all the calculation
23:58    won’t take you through that but it’s the same sort of thing
24:02    it is the stacking and sorting and then I’ve got a range of
24:05    options the default table or I can group by to get it group by the customer and
24:14    you you look at the total cost of the inputs. I can pivot them. Let’s go and
24:21    have a look at this. Um that’s the list that’s grouped by by
24:27    customer. And uh if you look that say customer three is the
24:34    28 and 58. Do I see that? Customer 3 28 and 58.
24:41    Now it looks Yes.

24:42    Hang on. Hang on. What are these? What are these? What are these buttons here?
24:45    Is this a slicer with a table?

24:47    Uh, it’s Yes, it’s a one cell pivot table,
24:57    right? It’s one one cell because I’ve selecting one
25:02    item and I’ve got rid of it’s only the row
25:08    headings. I don’t have any values and uh I don’t have any headings or
25:14    buttons or anything else. It’s just one value that sits in a cell
25:19    and that controls the um the uh the switch statement which
25:27    controls what you see here. Nice.
25:30    So, and they they’re they’re nice looking little controls, aren’t they? Uh
25:35    so um that that’s the sort of the basic but if you want to see it’s sp sort of
25:42    arranged so you can actually see which batches went came from where and
25:46    went where you can do a cross tab. Now I wouldn’t recommend that
25:52    if you once you know this is six and four inputs outputs.
25:57    If you’re talking of uh 15,000s and you you’re thinking of a 15,000 by
26:05    15,000 array with just a little straggle of numbers working down diagonal, I
26:11    don’t think that’s much use for anyone that uh I wouldn’t be greatly
26:15    attracted to that. But for small models and seeing
26:19    what’s going on, it’s it’s it’s nice and it’s sort of you can
26:26    actually see um I’ve also a version which tells you the volumes as well as
26:32    the costs. So uh right that was the sort of end of first part.
26:40    How am I doing on time? I’ve got a little bit
26:45    good but uh are there any questions or or comments? Any? No.
26:50    Any any rotten fruits being thrown in? Yeah. Yeah. No, no, that’s great.

26:54    Um, no, just just lots of comments, but um no particular questions.
26:57    So, if people have got particular questions that they’d like Peter to address, just pop
27:03    it into the Q&A. Um, if it’s just a comment, um, just pop it into the chat.
27:06    So, um, I will let you know if questions come through, Peter.

27:10    Okay. Now I go and see what I can find another. Isn’t that I’ve got to find
27:17    another spreadsheet.
27:19    You’re going to go to the other the other file now.
27:23    Yeah. Right.

27:25    Pop that into the chat as well so people can

27:28    Okay. And um I’ve also it was actually Carolina Lago
27:34    that produced a very simple financial model for me that I could work
27:40    with and uh because I I know it’s bread and butter for
27:46    everybody else but I find them massively complicated to understand.
27:53    And really it’s because I don’t I was expecting to see a calculation and what
27:58    I’m actually looking at in the um the model is not a calculation. It’s a
28:05    presentation you know of information. It’s a story in numbers. And uh whereas
28:12    I expected well what what’s the question? What is the answer and what is
28:18    the quickest route from the question to the answer? You know that uh and
28:24    probably the question is um given these flows these um assumptions
28:33    what is the financial state of my company in 8 years time? You know so you
28:38    want uh to know whether you’re in year by year whether you’re
28:43    in credit or debt and once as you know that
28:47    you have the information to present it how you like and this is how financial
28:53    models appear to like it. So, uh but it really does did confuse the hell out of
28:58    me. Um, so we started off with assumptions and
29:05    Carolina put a a an exponential uh geometric series for the uh revenue
29:14    and costs which peaked and that throws the company into debt for a while into
29:21    revolver debt uh called down. Um
29:26    uh the thing is I don’t know what these numbers mean. Are they just assumed
29:33    flows assumed constants over the period and then magically on January the 1st
29:39    they change to a different thing or is there an implied continuous flow and
29:48    you know it’s and according to how you model it you then you’ll implement
29:55    slightly different solution algorithms and you’ll get slightly different
29:59    results, and different accuracy.
30:02    So uh anyway the the model I try to analyze it. The uh green were taken from
30:12    I I took each of these um schedules and uh statements as if it were a function.
30:21    I looked at what the inputs were in green. I looked at what the outputs were
30:28    in um in yellow. And I went down and found, oh dear, I haven’t got a clue
30:33    what the interest is, and neither will I know until I finish the model.
30:36    So I put a a break in there. So I either do revolver interest

30:44    That whole circularity issue where you’ve got your opening, closing,
30:48    and you can’t calculate your interest until you know what your cash flow is,
30:51    and you can’t calculate your cash flow until you know what your interest is.
30:56    Is that what we’re attacking here?

31:00    Yep. That uh Came stripped the problem
31:04    right back and said, “Right, you solve that.” So uh um it turns out that the
31:09    problem is actually comes down to here of beginning sorry further down
31:17    is yeah yes into the uh debts schedule and uh things that you
31:25    don’t know down here um I analyzed it. Oh because I’m going
31:30    to put everything in LET statements I need names. I use defined names here but
31:36    ultimately they move across as LET variable names as well. So it’s the
31:43    first cell of is the long-term liabilities
31:52    which apparently were nil which is very good but um the uh and then put a hash
31:58    after it and you get the the whole row. So that was the way that I started the
32:06    refactoring. Um I to see what was going on, I wrote out all of the formula
32:15    there and I put the names across the top and I looked to see whether the names
32:23    occurred in the formula. And if they did, then I put a one out.
32:27    And these are this is the grid that you get.
32:30    And you’d see that earnings before tax depends on revenue and costs
32:35    and you know those and that’s fine but it also depends on revolver interest
32:41    and you don’t know that because that calc gets calculated much later.
32:47    So that that was what I used to try and get a grip on the problem and try and find
32:55    what I was facing. There’s a little revolver in there that is looks self-contained.
33:02    Um, so if I go back to the model and I
33:10    switch out the uh the break from what I’ve told you, you should be
33:18    expecting the whole lot to crash and uh fail to evaluate. But it doesn’t.
33:24    And the reason it doesn’t is I’ve cheated that uh if you come down
33:32    to beginning balance closing balance
33:39    moments where am I think I’m in the right place um
33:47    the opening cash or debt is not calculated in this model at all
33:54    it’s calculated on the next sheet. So this one won’t go circular because
33:58    I’ve got I’ve given the given it the answer was for
34:02    completely separate calculation. Um, what I did with Carolina’s model was
34:10    I combined the cash and the revolver debt into one variable so that positive
34:17    values of cash, negative are are the revolver debt. And I did that just to
34:24    make the calculations easier. Um, so it’s as if it were one account
34:30    with a a credit facility on it. So
34:37    my calculation is actually here. It’s a SCAN over the revenue minus costs
34:45    and a LAMBDA function which works out cash and revolver.

34:51    The function doesn’t looks
34:56    really it’s not much to it. But then I ask for a problem without much to it
35:02    for you maybe. Um I need to know opening values which are zeros flows which are
35:09    revenue minus costs. The interest is the
35:15    revolver the rate revolver rates times the negative part
35:21    of the combined cash debt figure to the financial state
35:28    earnings before tax. You take the flow and you subtract the interest. That
35:34    gives you the earnings before tax. The tax is the positive parts is related
35:40    to the positive parts of the earnings before tax
35:45    times the tax rate. And then the closing balance is opening plus the
35:52    earnings minus the tax and return the closing. And that’s that
36:00    that is the spreadsheet expressed in a different way in which it sort of just
36:07    go straight for the closing balance. Um the advantage here the horror was
36:15    that the whole band model has to go into one formula and uh you know how on earth
36:23    one does that? Is it’s not quite as bad it’s bad but it’s not quite as bad
36:29    as that because this function only has to deal with one time period
36:37    at a time and SCAN and we’ll run it across. The other thing about it
36:44    that’s a saving grace is I’m only looking for the closing balance. I’m just looking
36:50    for one quantity. Uh everything else that I calculate along the way I discard.
36:57    So there there is some simplicity in this. But uh
37:03    if what I do once as I’ve got oh yeah
37:09    then opening balance is the closing balances which is the
37:14    formula you’ve just seen brought forward one period uh by pushing um the initial
37:21    revolver balance in at the front. And so if I know closing balances, I know
37:28    opening balances. If I know all the opening balances, then I can go back to
37:34    this model and calculate the whole lot in terms of the opening balances, which
37:41    is what is done here. And why I’ve got numbers because down there I’ve brought
37:48    in the negative part of the uh revolver
37:53    which is the debt. And once as I’ve got that that goes up to the top as um
38:03    the as as interest by multiplying by the
38:08    rates that you’re paying. So the whole model then holds together
38:14    given that I’ve got the calculation on another sheet.
38:18    Um I ought to be finished. I I’ll give myself can I have five minutes
38:24    just to uh just throw another couple of grenades into the goldfish ponds?

38:30    Absolutely. Absolutely. Throw away. Grenade away.

38:34    Um so I then went to make the calculation a bit more complicated.
38:40    There’s you know if something if something is complicated already there’s
38:46    no harm in making it really complicated. uh the the problems of circularity
38:55    is that occur when these you see models in which the you have an average of
39:02    beginning and closing balances but you don’t know the closing balance until you
39:07    you know the closing balance depends on the average and
39:10    the average on the closing balance and you get yourself into a real mess.
39:13    You invoke circularity, it converges to something. It converges
39:20    to something more accurate. Maybe if it converges, I’ve seen problems which go
39:26    on for a hundred steps and then just meander away from the solution because
39:32    the uh you know the the um convergence
39:37    isn’t being run at that step. Um the the chaos theory is based on formulas
39:45    like this. So you don’t necessarily get decent behavior.

39:48    But there is an alternative which is predictor corrector approaches.
39:55    So the interest was the revolver rate uh times the negative part of the cash
40:01    date. And but that is only a first guess. The earnings before tax flow
40:09    minus interest is again first guess. No more than that. I’m not going to put it
40:15    on my sheet. I’m not going to overwrite it. I that it is just a first guess.
40:20    It’s a different quantity. Okay. The average is the opening balance plus
40:27    half the earnings. Mhm. And that’s again it’s just an estimate
40:32    but it improves the uh the calculation. So I recalculate interest as the revol
40:41    as the with a revolver rate the negative part this time of the average balance
40:48    over the period and uh then hence the earnings but
40:53    before tax gets recalculated the I haven’t included the t the tax in
41:01    this averaging process I rightly or wrongly assume that the tax is payable
41:07    at the end and the fact that you’re t pay you’re building up a tax commitment
41:15    doesn’t make your your financial state worse. You’re not drawing on more
41:20    revolvered debt because at the end of the year you’ll have to pay tax. So I
41:26    didn’t want to merge the tax back in. So I I just left that till later. that
41:32    gives the closing balance and again that uh return the closing balance gives
41:41    that’s the calculation but with just a different LAMBDA function
41:46    the same as the one before I’ve got the uh estimates
41:51    as well as the refined thing if I just output the variables
41:57    from there and then I tidied it up And I was sort
42:03    of kind and I I separated out what is cash, what is revolver debt. You see
42:09    them switch as you as you go into debt and those periods.
42:15    Um similarly with the closing. So and I
42:20    chucked away the estimates. So that that would be a solution.
42:27    And you you can see the figures sort of going from closing back to opening.

42:33    Can I just ask about how this differs to the uh the one that Craig Hatmaker
42:40    showed us uh a little while ago? I think the um you know it does a similar thing,
42:45    but I think he said the the way that it calculates is slightly different. Is that right?

42:51    Yeah. that uh I need to go back and have a further look at uh what Craig has done
42:57    that uh in fact I’m working with work with Craig that know him well that uh

43:04    similar it’s a similar kind of a similar kind of thing

43:07    yeah some of my methods have feed feed into his components and more a case that I
43:12    ought to learn and do more with my work to put it in the form of his components

43:20    it’s great to see the my understanding yeah the collaboration is good for for
43:24    the industry as a whole.

43:28    My understanding is what Craig did with the the circularity problem was to emulate
43:32    what’s the uh the um built-in uh circularity does of just repeating the
43:40    calculation and he put that into a recursion rather
43:45    than uh um an Excel iteration
43:50    and uh did so many loops around um by keeping on averaging the beginning
44:00    flow and the ending flow over a period and taking the average that that is a
44:06    relationship which works exactly if the flows are quadratic.
44:13    Typically this type of equation has exponentials as its solution not quadratics.
44:19    So you’re putting a lot of effort into converging to something that isn’t the
44:26    solution. It’s a it’s it’s a good estimate. It’s an improved estimate,
44:32    but it isn’t the solution. So uh I I
44:35    just I think u Diarmuid Early about two years ago. I’ve only just come across
44:41    it, but did quote uh a workbook that does the same calculation as I’ve done
44:49    essentially, but you you just see the estimate and then the second revised
44:55    estimate appearing on the spreadsheet. So, it’s worksheet formulae rather
45:02    than uh inlet LAMBDA. So, wow. Right. Yeah.

45:08    Hi, can I just if if anyone else has got any I don’t think we have any
45:111    outstanding questions right now. So, if anyone has
45:14    any, please get them in. Um, I just wanted to ask about that symbol which
45:19    I’m not really I haven’t seen used that often. You’ve got that uh I can see it
45:22    just there in front of the nil. It’s like the double S. I think it’s called
45:25    a silcrow. Um, is that like a like a placeholder or why do you use that symbol?

45:32    Where where are you? Uh where will uh

45:35    So I can see it over on Oh, I can’t see your rows.
45:39    Um it’s like the double S. Uh like the the the sort of Latin character.

45:46    Oh, yeah. Yeah, that’s it. The the I think it’s called the silcrow there.

45:51    That one. Yeah, that’s the one. Yeah. Yeah. that
45:56    basically um BBA is not tolerant of it but
46:02    spreadsheet formulas you can um insert symbols.
46:08    Mhm. And uh although they tell you that names must just be Latin letters and
46:17    numbers then uh it’s not really true that if you
46:22    are Greek then uh all of these things are part of your natural language. So,
46:29    uh, you can pick quite a lot out of the symbols. And what I did was to pick
46:36    somewhere the section markers, the double S to indicate series assumptions
46:45    there, that’s it as series assumptions. And I picked Oh,
46:52    dogs are going berserk, um a currency marker
46:57    as constant assumptions just so that I
47:05    would know what’s the uh you know understand that the names I
47:11    know immediately that’s a series assumption

47:20    yeah so it’s like a marker, is that a commonly used technique?

47:20    it just suited me that the other things that I’ve got uh backspace
47:29    lambda. Yep. And that turns into a Greek lambda.
47:35    That that’s just the um uh oh they call it in words there’s the uh correction
47:44    sort of uh spell that does spell ch checking and correction.
47:48    you put in symbols like that and because
47:53    um Excel annoyingly thinks that that is a cell reference
48:01    or possibly let’s do something a little bit more significant
48:06    tax 2020
48:13    this year it Excel will think that’s a cell and
48:18    everybody knows it’s an amount of tax you’re going to be paying next year.
48:25    And I find bits like that frustrating.
48:28    I want x1 as a variable. So what I tend to do is x underscore one.
48:36    Mhm. And it turns it into a subscript. Oh, nice. So uh so as I I managed to get
48:46    over the fact that uh Excel has squandered
48:52    um how many variable names it’s the uh you know it’s hundreds of thousands oh I
48:59    suppose this number of cells isn’t it it’s 16,000 rows times uh million
49:09    you know columns and So, it’s an awful lot of names that it has squandered
49:13    on meaningless on, in on imaginary cells.

49:16    Yeah. Yeah, that’s true. But now, um, we’ve got a question from John.
49:23    Um, he says, “Is frozen?” Oh, you can Can you hear me?
49:31    So, we’ve got a question from John and he’s saying,
49:34    “Can everyone else hear me? Hear me?” Uh, I can hear you. Okay, you can hear me.

49:37    All right. Uh so he says is it practical so this concept of creating this
49:43    uh you know the circularity is it practicable when a model has multiple
49:49    circularities for example tax interest debt sculpting it seems that the
49:54    complexity of such a big LAMBDA formula would be exponential with circularities
50:00    and secondly how flexible are these so how easy it is it
50:04    to add new elements into the big formula
50:07    formula such as adding tax circularity to an existing formula.
50:13    So you would be able to add more circularities, wouldn’t you?

50:21    It’s yeah where you add them might uh might vary slightly but uh something
50:29    like um uh what they call senior debt
50:35    that that’s got its own little circularity as you integrate along
50:39    and it’s self-contained and it just comes in
50:42    as the result comes in as a number that that would be
50:47    quite easy to do. Mhm. Um the
50:54    you’re you’re you’re working with formulas like
51:00    I’m I’ve lost my mouse. There it is. It’s on the wrong screen. Um like this.
51:06    I I had actually made a mistake and uh had to confess to Danielle this this
51:13    morning that uh the average I put in as the opening plus earnings over two and
51:23    I’ of course I’d missed out another opening. It’s the opening
51:29    over two plus the closing. So, uh, that’s the opening
51:33    plus the average earnings before tax. And
51:39    it’s reasonably straightforward to edit these formula. If you’ve if it’s
51:46    meaningful, you can follow through. I believe they should be a lot more
51:51    modular. Um so that uh
51:56    you know each sort of business decision or law ought to be
52:04    captured in its own uh LAMBDA function. So this itself ought to be built up from
52:11    LAMBDA functions. Um and you would be trying to construct the
52:18    things so that it is uh editable so that
52:24    you can take one of these to your client and say is that your company procedure. This is
52:30    what I’ve implemented. I think it’s what you described to me.
52:33    Take that little LAMBDA function away. Test it. See
52:37    whether it is what you want or I’ll do it with you. Um so there is a level of
52:47    comprehension and communication that’s going on which is not involved in the
52:53    numbers. It’s involved in the formula and the the numbers just follow the
52:58    logic. So uh but you’ve got to have a client that understands the logic.

53:05    It’s uh so yeah and I think that the the challenge with this is always having the client
53:13    client understand and be able to to follow your logic. Um because we are so used to
53:20    using Excel and the grid uh and being able to trace back every single cell and
53:26    this is just such a different way of working. Um I think it’s just going to
53:29    take a while for people to get used to it, for sure.

53:33    Yeah. Yeah. Agreed.

53:40    Okay. Well, we are just about to time. Uh so were there any was there anything
53:40    else you wanted to sort of leave us with, Peter?

53:47    Um that is a very good question. I did have a PowerPoint,
53:54    but uh let’s have a um what happens when the OP doesn’t
54:00    recognize the code? Another issue. How do you teach it? Do you leave LET LAMBDA
54:05    to the end? And I say no, they’re foundational elements.
54:08    You you they’re the first things you do. You don’t even
54:11    need to teach A1 notation. That’s this is where you start.
54:17    um should modern Excel be regarded as cutting edge enhancements or is it
54:23    better to of a spreadsheet or is it its replacement within the same code?
54:29    It’s as different as if you had Python but uh you know it’s doesn’t have Python’s
54:37    libraries but uh Oh, I just shouldn’t put that up, should I?
54:41    It’s different enough. I didn’t say
54:44    good riddance, honest, but so uh
54:51    right on that if anyone does have queries or
54:58    or followups then uh I am accessible and I hope to be
55:05    working further with uh say Andrew Craig and so on that some work and No, what I
55:14    would like to see is to get some core steering data into a model and then it
55:21    shouldn’t be necessary to test whether your balance is balance because the
55:28    that’s mere output and the the functions that create the uh the um
55:37    flows should automatically put in the both entries of the double entry. It
55:45    shouldn’t be up to the user. They shouldn’t be able to make a mistake of that sort.
55:50    But, uh, that’s that is hazy futures. That’s it’s not my territory. And, uh, yeah.

55:56    Yeah. Yeah. I think we’ve got a got a bit of a got a bit to go before that’s going to
56:02    be the reality. But, wow, you have certainly certainly given us a lot to think about.
56:08    So, thank you so much, Peter. That was um absolutely uh mindblowing which I uh
56:16    I knew it would be. So any conversation with you is always uh uh an absolute uh
56:22    is I know it’s always going to be absolutely fascinating and challenging
56:25    and and really make you think. So thank you so much Peter.
56:30    Um I will uh get the files and the recording out to everyone
56:33    who registered um as well as the links that Peter mentioned. So, thank you so
56:40    much everyone for your contributions and uh I’ll see you again soon.
56:45    Thanks everyone. Bye.

‘—–TRANSCRIPT END—–

‘——————————————————-

Do Your Excel Files Collapse Like Jenga Blocks?

And what were you doing on September 30, 1985, when the first version of Excel was released, for the Macintosh?

40 Days of Celebrating

Leading up to today’s birthday party for Excel, Microsoft’s Excel team had a 40-day-long Excel-ebration, starting on August 6th.

At that link, you can see each of the daily videos, created by Excel MVP and Excel creators, who enthusiastically shared their Excel tips.

There are old features, that we still depend on, and new features that  we’re just getting started with!

Excel Memories

In 2015, for Excel’s 30th birthday, I asked people to share their early memories of working with Excel.

There were lots of wonderful responses, and you can read them all in my Happy 30th Anniversary Excel post.

See if any of the stories are close to what you remember!

• Maybe you started with Excel on the Mac Plus, like I did, and Alberto Almoguera did too. Those 9” black and white screens were awesome!
• Do you remember installing Excel from floppy disks?
• Was Excel your first spreadsheet program, or did you migrate from something else, like VisiCalc, or Lotus 1-2-3? Or did you start with Multiplan, like I did?
• Were your first Excel projects for work, or personal tasks?

There are a few more stories of getting started in Excel, in the comments in this “How Long Have You Been Using Excel” article, on my Debra D’s blog.

And you can see some of my early Excel files in my blog post Back in Time with Microsoft Excel.

Do you remember the days when a Microsoft Office application was just over a megabyte in size?

Excel Team Memories

For some behind-the-scenes memories, take a look at GeekWire’s interview with 4 original members of Microsoft’s Excel team — Mike Koss, Jabe Blumenthal, Doug Klunder and Jon DeVaan.

You’ll learn what they almost called Excel, and why Excel’s motto is “Recalc or Die”.

More Microsoft News in 1985

Lots of other things were going on at Microsoft in 1985, and you can see the highlights in this History of Microsoft 1985 summary.

There’s a 10-minute video at the top, with the Excel release at the 1:48 mark.  Below the video, there’s a written list of the year’s highlights

This Canadian tidbit was my favourite non-Excel news:

• May 3, 1985: Rich MacIntosh, General Manager of Microsoft Canada, Inc., was recently called to the Canadian Department of Agriculture to retrieve his Microsoft “Mouse” after four weeks of quarantine

Excel’s 40 Best Features

What are your favourite features in Excel? Or is it impossible to choose?

In honour of Excel’s 40th birthday, Bill Jelen (Mr Excel) published a new book:

• Excel at 40: The 40 Best Features in Microsoft Excel as of September 2025

Here’s what you’ll find in the book:

• Step-by-step tips for mastering Excel’s most powerful functions
• Real-life stories from Excel fans and insiders
• Excel jokes and puzzles to test your spreadsheet wit
• A unique mix of nostalgia and forward-thinking tools

You can visit the Mr Excel bookstore (Holy Macro Books), to buy a digital copy of this book.

And there are lots of other helpful Excel books there too!

What Were You Doing On September 30, 1985?

Next, let’s think back to the day that Excel was released – Monday, September 30, 1985.

If you weren’t born yet, you can skip this section!

• Were you a young kid?
• A student learning about computers
• Working at a job, with or without computers?

We got a Macintosh in 1984, and I was self employed, using that Mac. No Excel till a few years later though.

On Television in 1985

After work or school, back in 1985, did you watch television in the evening?

Here were the 8:00 PM shows, in the USA, on September 30, 1985.

Do you remember any of them?

And if you were allowed to stay up a bit later, here are the shows from 9:00 to 11:00 PM that night.

It was a rare event to see Johnny Carson so early in the evening. His show was usually at 11:30 PM, after the nightly news.

And they sure had a lot of shows with an ampersand (&) in the title!

Where Were You in 2005?

And finally, have you celebrated other Excel birthdays/anniversaries?

In 2005, Microsoft had a 20th Excel anniversary celebration.

I wasn’t invited to that official party, but I was at a different Microsoft event that night, during an MVP Summit in Seattle!

The poster below is my souvenir from that party.

It was at Seattle’s Experience Music Project, and we formed a band – Excelerongs – for a special one-night appearance.

• That’s Dick Kusleika at the far left, and I’m 3rd from the right, in the red jacket.
• Other band members were from the Excel team, and I’m sure they’d like to remain anonymous!

So take time to celebrate Excel today, with song, or interpretive dance, or whatever makes you happy!
___________________

Happy 40th Birthday Excel September 30 2025

___________________

Video: Make Weekday Lists in Excel

In this quick video, I show two easy ways to make an Excel weekday list, with no weekend dates.

Both of these tricks use AutoFill so they’re quick and easy to use.

There are written steps below the video, and the full transcript is further down the page.

Trick 1 — AutoFill Weekdays (skip weekends)

Use this technique when you want a list of workdays, from Monday to Friday, and then skip the Saturday and Sunday dates.

• First, type your starting date in a worksheet cell.
• Select the start date cell (cell B3 in the screen shot below)
• Next, point to the Fill Handle,  at the bottom-right of the start date cell.
  • Tip: The pointer changes to a 4-headed arrow when it’s over the Fill Handle

• Press and hold the right mouse button
• Drag down the column, to the cell where you want the weekday list to end
• Let go of the right mouse button
• In the pop-up menu that appears, click on the Weekdays option

List of Weekdays

On the worksheet, Excel fills the cells with dates from Monday to Friday.

Then it skips the weekend dates, and continues with the next week’s weekdays.

Weekday Names in Column A

In the video, and the screen shot above, the weekday names appear in column A, after the dates are listed in column B.

Those names make it easier to confirm that the date list is showing only weekdays.

To show those day names, here’s the formula that I entered in cell A3, and copied down to cell A12.

• =IF(B3=””,””,TEXT(B3,”ddd”))

How The Formula Works

First the formula uses the TEXT function, to format the date as a 3-character day name.

• TEXT(B3,”ddd”)

Next the IF function checks cell B3.

• If the cell is empty , then the formula result is an empty string
  • IF(B3=””,””,
• If the cell is NOT empty, then the formula result is the day name, returned by the TEXT function

Trick 2 — Every Second Weekday

You can get even fancier with Excel AutoFill, when you use the Series settings.

Here are the steps to make a list with every second weekday date, instead of all the weekdays.

• First, type your starting date in a worksheet cell.
• Select the start date cell (cell B3 in the screen shot below)
• Next, point to the Fill Handle,  at the bottom-right of the start date cell.
• Press and hold the right mouse button
• Drag down the column, to the cell where you want the date list to end
• Let go of the right mouse button
• In the pop-up menu that appears, click on the Series option
  • Tip: Series is at the bottom of the pop-up menu

Series Dialog Box

When the Series dialog box opens, make the following 2 changes to the settings:

• For Date Unit, choose Weekday
• Change the Step value to 2

Leave the other settings unchanged, then click the OK button

List – Every 2nd Weekday

On the worksheet, Excel fills the cells with dates for every second weekday.

• In the screen shot below, Monday, Wednesday and Friday are listed for the first week
• The next week has Tuesday and Thursday dates

The rest of the list repeats those weekly patterns, to show every second weekday’s date.

More AutoFill Tips

1. You can find more Excel AutoFill tips and examples on my Contextures site.
   • There’s an AutoFill examples file that you can download on my site too!
2. The Microsoft website also has details on creating a series of numbers, dates, or other items.

Video Transcript

Here’s the full video transcript for the Fill Weekdays video.

• Note: I’ve added a couple of headings, and some formatting, to make the transcript easier to read..

———-START OF TRANSCRIPT———

Weekday List

Here are a couple of quick tricks for creating a list of weekday dates.

• I’ve got a starting date and I’m going to fill down.
• I’ll start by pointing to the fill handle at the bottom right corner here and I’m pressing the right mouse button while I drag down.
• And when I let go, this menu pops up and I can see weekdays.

A formula here shows the code for the weekday name.

• So, we go Monday to Friday.
• Then, it skips Saturday and Sunday and continues the next week.

Every Second Weekday

On this sheet, I just want every second weekday.

• I’ll start again here.
• Point to the fill handle, press the right mouse button, and drag down.
• This time, I’m going to choose Series.
• In here, we’re going to choose Weekday.
• And instead of a step value of 1, I’m entering a 2.
• Click OK.

And now it’s Monday, Wednesday, Friday, and then Tuesday and Thursday the next week, and so on down the list.

———-END OF TRANSCRIPT———

_______________________

You can enter a formula in one cell, and the results spill down onto the worksheet, to show all the results. And unlike other filters in Excel, the FILTER result list will update automatically, if you change the criteria.

Video: Get Started With Excel FILTER Function

In this video, see how to use the new FILTER function to create a list of cities in a specific region, sorted A-Z.

• Then, see how to create a list of cities and sales reps in a specific region, with both items sorted A-Z
• Next, create a filtered list using 2 criteria – region and sales price

The video transcript, with screen shots, is below the video.

Get Started With Excel FILTER Function

• This is the full transcript for the video above, if you’d rather read, than watch!
• I’ve added a few screen shots, to help you understand the steps
• You can get the sample file, and more details, on my Contextures site.

Video Transcript

In this video, we’ll get started with the new Excel FILTER function, which is available in Office 365.

You can enter a formula in one cell, and the results just spill down onto the worksheet, sheet, and the list will update automatically if you change the criteria.

This is Debra Dalgleish from Contextures.com.

Cities in Selected Region

In this workbook, we have a food sales list that shows region, city, and several other fields, and you can get this file on my Contextures website.

First Formula

And our first FILTER function will get us a list of the cities, after we select one of the regions.

• I’m going to this sheet, and I’ve got a drop down list here where I can select one of the five regions.
• And in this formula, I’m going to create a FILTER formula.
• I’ll start with equals, FILTER.

Now you should see FILTER, and if not, your version of Excel doesn’t have these functions yet.

Once I see that, I can press tab, and I’m ready to fill in the arguments.

FILTER Function Arguments

And the first argument is the array, which is where we want the information to come from.

• So I’m going to my food sales list, and I would like the cities.
• So I’ll click that, and it puts the name of this table and the name of this field.

And I’ll type a comma and go to the next argument, which is include.

• This is the filtering that we want this to do.
• We want it to only show the cities where the region matches our criteria cell.
• So I’ll click on the region column, equals, and then I’ll go back to my sheet here.
• And I want this cell where I’m selecting a region.
• Now I don’t need the sheet name here, so I’m just going to delete that.

Then I’ll type a comma, and the last argument is if empty.

• So if it can’t find any values that match our criteria, what should this cell show?
• And I’m going to use double quote and then two dashes double quote.
• Close that bracket and press enter.

List of Cities

So now it’s given a list of all the cities.

• If we scroll down, it goes down quite a way.
• So every row here that was in that region is showing up.

So it’s a good start, but it’s not exactly what we want.

• We just want a list of the city names.
• We don’t want each one repeated several times.

UNIQUE List

So we’re going to use another function with this, another one of the new Excel functions, which is UNIQUE.

And I’ll put a bracket at the end and now when I press enter, there’s our list of cities, but each one is only in that list once

SORT the List

And there’s just one more thing we could do to improve this.

Right now, the lists are showing up in the order in which they’re first found in that sales data.

I’d like them in alphabetical order instead, so I’m going to use one more of the new functions, which is SORT.

Bracket at the end, and there’s our list in alphabetical order.

Spill Area

Now this cell is where we put the formula, and we can see that up in the formula bar.

• And then there’s this thin blue border around the other cells in the list.
• This is the spill area.

If I click on one of these other cells, I can see the formula up in the formula bar, but I can’t edit it.

• If I click up there, it just disappears.

So this cell has the formula, and if I move that cell somewhere, the whole list comes with it.

Now if I typed something in here that doesn’t exist, now there’s nothing found, and then I see my two hyphens in this cell.

Cities and Sales Reps

For the next example, we’re going to start with the same formula that we just finished on the other sheet.

But this time, instead of just a list of cities, I would like to see the sales reps from those cities in whatever region we’ve selected.

• To do that, we’re going to change the first argument in the FILTER function, which is the array.
• So I’ve highlighted the current argument, which is SalesDataCity.
• I’m going back to FoodSales, and I’m going to select City and Last Name.

And when I press enter, it shows us all the cities, and they’re still in alphabetical order.

And then for each city, it shows the reps, and those names are not in alphabetical order.

Add Another SORT

So we’re going to make one other change to the formula to put these in order.

So I’m going back to the cell that has the formula because that’s the only place I can make a change

The SORT function – the first argument is array and this is our array that we’re sorting

• Then it’s got some optional arguments, which we didn’t have to use last time, the sort index.
• So if things are being sorted, should this be the first thing sorted or the second?

And then the sort order, which would be ascending or descending.

• So I’m going down to the end here, and this is the bracket that’s closing the SORT.
• I’m going to type a comma and then the index.
• This is going to be the list of rep names and they should be sorted second.
• So I’m going to type a two, then comma, and now do I want this ascending or descending?
• So I’ll double click on ascending and press enter.
• So now it’s just sorting these names.

We’re going to put another SORT in, to sort those city names again.

• So type SORT, open bracket, and at the end, we’re going to type a comma, and the sort index for the city names is one, comma, and we want that in ascending order as well.
• Close the bracket and press Enter.

So now the city names are alphabetical, and then the secondary sort is the rep name,

And now the rep names are sorted alphabetically within each city.

FILTER with 2 Criteria

In this example, we’re going to use two criteria.

We’re going to continue to use Region, but now we’ve added Price.

We’re looking for sales where the amount was greater than whatever number we’ve typed in this cell.

I’ve put our first formula into this cell where we were just looking for region.

• And in that example, we wanted the city returned.
• But this time, we want product, so I’m going to select that, go back to my sales data, and click on the product column, and press enter.

So now we can see all the products that we sold in that region.

• We’re going to add criteria for this price now.
• So if we come back to the formula cell, here we can see the region has to be equal to B4.
• We also want the price column to be greater than whatever’s in B7.

To use two criteria, we’re going to put brackets around each one.

• So I’ll start with a bracket here, and then at the end of that first criterion.
• Before we start the next one, we’re going to be multiplying these.
• So I’ll put in the asterisk, and my next criterion, I’ll start with the open bracket.
• And for this, we want to check the price column in our sales table, which is our sale price.
• And that should be greater than and back to the sheet with our formula.
• And I’m going to click on the price cell.
• And again, it puts the sheet name in for us, and I don’t want that.
• So it has to be greater than that number.
• Close the bracket and press Enter.

And now we’ve got two criteria.

So our products are those from the Southwest, where the total order price was greater than this number.

If I change that to a hundred, then we get a different list of products here.

Thanks for watching this video.

Get the Sample File

You can go to my website, contextures.com, to get this workbook, and please subscribe to my Contextures YouTube channel so you can see the latest videos as I post them.

–END OF TRANSCRIPT–

More FILTER Function Examples

Excel Filter for List Items

FILTER Function Lookup

FILTER Function Reports

Spill Function Examples

Dynamic Drop Down Lists

_____________

Video: Excel RANK function

In this video, I show how to make a RANK formula in Excel, to compare numbers in the same list.

Which student did the best? Which golfer won the tournament? See how to rank the results.

The written steps are below the video.

Student Scores

In this example, I’ve got a list with 10 student names, and each person’s score on the latest test.

I’d like to see where each student ranked, but I’d also like to keep the names in alphabetical order.

Add a RANK Formula

To see the student rankings, without sorting the list, I can add a new column, with a RANK formula.

• In cell C1, I typed the heading, Rank.
• My data is in a named Excel table, which automatically expanded, to include the new column.
• In cell C2, I started the RANK formula:  =RANK(
• Next, I clicked on cell B2, which has the first student’s score
• Excel added a structured table reference to that cell, [@Scores]
  • In the video, the data is not in a table, so Excel used a normal reference to the cell: B2
• Next, I typed a comma, to start the second argument in the formula

Compare to List of Numbers

For the second argument, tell Excel which list of numbers to use, for the rankings.

• I clicked at the top of the Scores column, to select all the numbers.
• In the formula, Excel added a structured table reference to that column, [Scores]
  • In the video, the data is not in a table, so selected cells B2:B11, then pressed the F4 key, to create an absolute reference – $B$2:$B$11

Finish the RANK Formula

Then, to complete the formula, I typed a closing bracket, and pressed the Enter key.

• In the Excel table, the formula automatically filled down, to the last row in the table.
• In the video, I copied the formula down, from B2 down to B11.

With the Ranks listed in column C, it’s easier to compare the test results.

• Al had the top score, 46
• Flo ranked 10th, with a score of 20

It’s easier for our brains to compare the numbers 1 to 10, than it is to compare the 2-digit scores in column B!

Get the Sample File

For more RANK formula examples, and to get the sample Excel file, go to the RANK formula in Excel, page on my Contextures site.

And if you need to break ranking ties, there are formulas on my Contextures website page, Excel RANK With Ties, and a sample file to download.

Video: Show Rank in Pivot Table

If you’re summarizing data in an Excel pivot table, you can use a built-in feature, to show the ranks for any column of numbers. This video shows the steps, and there are written steps on my Excel Pivot Table blog.

More RANK Articles

Excel RANK Function Examples – Contextures site

RANK Function – Microsoft Support Page

Show Rank in Pivot Table – Pivot Table Blog

________________

Quick Quiz: Which version of Excel had a hidden Flight Simulator game as its Easter egg?

•      a) Excel 5.0
•     b) Excel 95
•     c) Excel 97
•     d) Excel 2000

Answer: You can find the answer at the end of this article.

Fun Fact : Microsoft stopped adding Easter Eggs to its products in 2002, and you can see the full list of hidden goodies in this Wikipedia Easter Egg article.

Flight Simulator Video

Here’s a a choppy recording of that built-in flight simulator game, posted on the Tom.K YouTube channel.

• The video starts with Tom opening Excel 97.
  • As a bonus, you get to see Clippy, ready to help with any problems!
• In Excel, to launch the Easter egg, Tom enters the secret address in the Go To window (X97:L97), and clicks the OK button
• Next, on the Excel toolbar, Tom clicks the Chart button, to start the Flight Simulator.

When Is Easter?

If you’d like to plan for Easter celebrations in the upcoming years, you can use the Excel Easter Calculation file on my Contextures site, to find the Easter date for any year.

The file has Excel formulas, and user defined functions (UDFs), with details on how they work. Thanks to Excel MVP, Jerry Latham, who contributed the tutorial and Excel workbook!

This screen shot shows a sheet in the file, where you can enter a year number, to see the Easter date that year.

Find the Hidden Treasures

If you don’t have any candy Easter eggs, you can use my Excel Treasure Hunt game, to find different kinds of hidden treats, like rabbits and kangaroos!

Here’s a screen shot with the Hoppy Hunt theme selected, and there are 4 other themes to choose from..

Video: Treasure Hunt Game

In this 3-minute video, I show how to play the game, and how it works. There are formulas, conditional formatting, and a data validation drop-down list.

• Get the Excel file on the CODE and CHAR function page, in the download section

Easter Egg Quiz – Correct Answer

Here’s the quiz again, as a reminder:

Quick Quiz: Which version of Excel had a hidden Flight Simulator game as its Easter egg?

•      a) Excel 5.0
•     b) Excel 95
•     c) Excel 97
•     d) Excel 2000

Answer: The correct answer is c) Excel 97.

Did you get it right? In the chart below, there’s a summary of the votes on my YouTube channel.

• •     a) 61% – Excel 95
  •     b) 17% – Excel 97   ← Only 17% got it right!
  •     c) 15% – Excel 5.0
  •     d)   8% – Excel 2000

_____________________________

More Excel Fun and Games

Here are a few fun activities to help keep you entertained!

Excel Games and Easter Eggs

Print Bingo Cards in Excel

Excel Sing Along

_____________________

I’ll show you two examples, and you can scroll down to watch the video demo.

What is TEXTJOIN?

TEXTJOIN is an Excel function that combines values from two or more cells, and you can choose what to put between each piece — like a comma, space, or dash. It’s great for pulling together data quickly, into a single cell.

• Tip: See the TEXTJOIN help page on the Microsoft site, for information on which versions this function is available in.

In the two examples below:

• I’ll show a basic TEXTJOIN formula first, to combine data from several cells.
• After that, add a condition to the formula, so it combines specific items from the list.

Sales Reps and Regions

In both examples below, the data is a small list of sales reps and their regions.

• Headings are in cells A1:B1
• Names are in cells A2:A5
• Regions are in cells B2:B5

TEXTJOIN Syntax

The TEXTJOIN syntax has 3 required arguments, and you can include optional text arguments:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

• delimiter: Character(s) to use between the text items, such as a comma or semi-colon
• ignore_empty: To ignore empty cells, use TRUE. To include empty cells, use FALSE
  • Tip: TRUE is the default, so you can leave this argument empty, if you want to ignore empty cells
• text1, [text2], …: Text items you want to join.

Simple TEXTJOIN Formula

First, I’ll use TEXTJOIN to make a list of all the rep names – all in one cell, separated by comma-space.

Here’s the formula in cell A7:

• =TEXTJOIN(", ", TRUE, A2:A5)

The delimiter is a comma-space, ignore_empty is TRUE, and the text is in cells A2:A5

Names Combined

In cell A7, the four names are listed, in the same order they appear in the original list.

TEXTJOIN with Conditions

After you know the basics of the TEXTJOIN function, you can enhance it, by adding a condition to the TEXTJOIN formula.

In this example, I’ll  make a list of only the reps who work in the “East” region, all in one cell.

Here’s my revised formula in cell E5, with an IF function creating the condition:

• =TEXTJOIN(", ", TRUE, IF(B2:B5="East", A2:A5, ""))

How the Formula Works

Here’s how the revised TEXTJOIN formula works:

• First, the IF function checks each region name
  • IF(B2:B5="East", A2:A5, "") checks each Region.
  • • If the region is “East”, IF returns the Rep Name.
  • • If the region is not “East”, IF returns an empty string.
• After that, the TEXTJOIN function combines the results of the IF function
  • TEXTJOIN(", ", TRUE, ...)
  • • It separates the rep names, with comma-space
    • It skips the blank cells, if there are any.

Tip: Put Condition on Worksheet

Instead of typing East in the TEXTJOIN formula, you could put East in a worksheet cell.

Then, in the formula, refer to the cell where you entered the region name.

For example, with the region name in cell D1, use this formula:

• =TEXTJOIN(", ", TRUE, IF(B2:B5=$D$1, A2:A5, ""))

This makes it easy to change the condition, and show results for a different region.

Watch the Video

In this 3-minute video, Sarah shows how to add a condition with TEXTJOIN, so only the weekdays that are marked with X are included in the combined text.

Get the Workbook

To try the TEXTJOIN function, you can get the workbook that Sarah used in the video.

• Go to the TEXTJOIN page on my Contextures site,
• In the Download section, click the link to get the TEXTJOIN Examples workbook.

There are several other files on that page too, and more examples and videos to explore!

More Text Functions

There are many more text function pages on my Contextures site. Check these out, for tutorials, videos, and Excel files to download for practice.

Combine Text/Numbers

Names, Split/Reverse First and Last

FORMULATEXT Function

_________________