The Smart Method’s Excel 365 books are the world’s only up-to-date titles. This is achieved by spending many months work producing a new edition whenever there are significant changes to Excel. We’re currently on the sixth edition covering all new features in the January 2022 release.
In July 2020 revolutionary new Dynamic Array functionality was added to Excel – a feature that changed best-practice for many business tasks. It is now more important than ever to learn Excel using an up-to-date book. While Excel 2019 doesn’t support dynamic arrays, they are included in the Excel 2021 release.
This article is an unedited lesson from one of the 21 short focused lessons in Session One of the Excel Expert Skills book/e-book.
- Excel 365
- Excel 2021
- Excel 2019
- Excel 2016
Last updated: 10 Feb 2020
note
Perpetual license versions have different features
Perpetual license holders still receive monthly updates, but these only include security updates and bug fixes (not new features).
An Excel 2019 user running version 2008 will thus see fewer features than an Excel 365 user running version 2008.
note
Windows 11 features are only updated once every year
Windows 11 features will only be updated once every year and the annual update will be released in the second half of the calendar year. This differs from Windows 10 that used to have features updated twice every year.
Starting with version 20H2 windows versions are named as the year of release along with the half of the year they were released in.
The two Windows 10 releases in 2021 were called Version 21H1 and 21H2 (the year, along with a half-year indicator).
For most users, new Windows versions are supported for 24 months. Some Enterprise and Educational users may have this extended to 36 months.
Microsoft does not force users to accept new Windows versions immediately. Users must initiate updates manually.
If your Windows operating system is approaching the end of its supported life, it is possible that Microsoft will automatically update your computer to keep your computer safe from potential security issues.
note
Microsoft changed the name of Microsoft Office update channels on 9th June 2020
This lesson uses the new update channel names that came into effect on 9th June 2020. You may still see the older names referred to in older documentation.
Here are the pre-June 2020 names:
- Beta Channel (old name: Insider Channel).
- Current Channel (old name: Monthly Channel).
- Monthly Enterprise Channel (no previous equivalent).
- Semi-Annual Enterprise Channel (old name Semi-Annual Channel).
note
Home users of Office are not allowed to use the Enterprise Microsoft Office update channels
Lesson 1 1: Understand Update Channels
In recent years most software vendors have transitioned from the concept of perpetual licenses to Software as a Service (SaaS).
Perpetual license software
There are two current versions of Excel: Excel 2019 and Excel 365. Excel 2021 is a perpetual license product. This means the purchasers “pay once and use forever”. Excel 2021 never has new or updated features added during its lifetime. Mainstream support for Excel 2021 ends in October 2026.
There are many disadvantages to the perpetual license model, including:
- Users with different Excel versions can have problems when sharing files with each other. For example, an Excel 2021 workbook containing dynamic arrays will probably not open correctly in Excel 2019.
- Support and training are difficult as different versions often work in different ways.
Many have speculated that Excel 2021 will be the last perpetual licensed Excel version.
Excel 365 is an SaaS product
The modern method of creating and supporting software is called: Software as a Service or SaaS. In the SaaS model users pay a low-cost subscription to the software developer (in this case Microsoft) to use the software and receive support and feature updates.
There are many advantages to the SaaS model, including:
- Every user has the latest version. You will see later in this lesson, however, that Excel 365 users that have different update channels may still have different versions.
- The software changes gradually so that users do not need to be re-trained to adapt to new versions.
The SaaS model does, however, create several development challenges.
How Perpetual Licensed software is developed
The traditional software development cycle involves three stages:
- Alpha test version: This is a pre-release version that isn’t expected to work very well. Alpha versions are expected to have many bugs and issues and are only used for testing, usually by professional testers working for the development team.
- Beta test version: The beta version is feature-complete but many of the features are not thoroughly tested. Beta versions are sometimes given to real-world users who then report bugs to the developer for attention.
- Release version 1.0: This version is expected to be solid and reliable and suitable for real-world use.
- Updated versions 1.01, 1.02 etc Updates do not add new features but fix bugs and security problems that are found after release.
How SaaS software is developed
SaaS software is constantly updated. To ensure that software is thoroughly tested Microsoft have created five Microsoft Office update channels. It is possible for users to decide which channel is most suitable for their needs. You’ll discover the update channel that your copy of Excel is using later, in: Lesson 1‑2: Check that your Excel version is up to date.
- Insider Fast: This channel has no support and should not be used for “real” business. Features are still in development and may be risky to use. This channel should not used for real-world business use.
- Current Channel (Preview) – sometimes called Insider Slow: When new features have been tested in the Insider Fast Channel they are released to the Insider Slow This channel is more stable and has less risk than the Insider Fast Channel. This channel should not used for real-world business use.
- Current Channel: On June 9, 2020 this became the default channel for all new Excel installations. Current channel users have an Excel version that can change at any time. Different groups of current channel users may be using different versions at any one time as features are not released to all users at the same time. Current channel features can be expected to have more bugs than the Enterprise channels.
- Monthly Enterprise Channel (not available to users of Office Home editions): This channel is aimed at business rather than home users. Enterprise channel users only have access to features that have been thoroughly tested by Current Channel users. Features can appear in the Current Channel months before they are considered ready for the Monthly Enterprise Channel. The Monthly Enterprise Channel in only updated with new features once each month.
- Semi-Annual Enterprise Channel (not available to users of Office Home editions): This was the default channel for all business users before June 9, 2020 (when Microsoft changed the default to the Current Channel). Semi-Annual Enterprise Channel users will receive a new, and thoroughly tested, version of Excel in January and July of each year. I would recommend this as the most appropriate channel for serious business use.
Most Excel users will be locked into the Current Channel but, in large businesses or educational users may be using the Semi-Annual Enterprise Channel, (if your administrator has decided to change the update channel away from the default. The sixth edition of our books was written using the Excel 365 Jan 2022 Current version.
If your machine is set to use one of the other Microsoft Office update channels you may notice some small differences between the version that you are using, and the current version used in the book.
This lesson is excerpted from the above book.
This is the only up-to-date Excel book currently published and includes an entire session devoted to the new Dynamic Arrays features.
It is also the only book that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
Lessons in Session 1
| Session One: Tables, and Ranges |
| Lesson 1‑1: Understand Update Channels |
| Lesson 1-2: Check that your Excel version is up to date |
| Lesson 1‑3: Change between touch mode and mouse mode |
| Lesson 1‑4: Change the Office Theme |
| Lesson 1‑5: Apply a simple filter to a range |
| Lesson 1‑6: Apply a top 10 and custom filter to a range |
| Lesson 1‑7: Apply an advanced filter with multiple OR criteria |
| Lesson 1‑8: Apply an advanced filter with complex criteria |
| Lesson 1‑9: Apply an advanced filter with function-driven criteria |
| Lesson 1‑10: Extract unique records using an advanced filter |
| Lesson 1‑11: Add totals using Quick Analysis |
| Lesson 1‑12: Add percentage and running totals using Quick Analysis |
| Lesson 1‑13: Convert a range into a table and add a total row |
| Lesson 1‑14: Format a table using table styles and convert a table into a range |
| Lesson 1‑15: Create a custom table style |
| Lesson 1‑16: Sort a range or table by rows |
| Lesson 1‑17: Sort a range by columns |
| Lesson 1‑18: Sort a range or table by custom list |
| Lesson 1‑19: Name a table and create an automatic structured table reference |
| Lesson 1‑20: Create a manual structured table reference |
| Lesson 1‑21: Use special items in structured table references |
| Lesson 1‑22: Understand unqualified structured table references |
| Session 1: Exercise |
| Session 1: Exercise Answers |
Related Articles
Every lesson is presented on two facing pages
This article explains how Smart Method books efficiently transfer information by presenting every lesson on two facing sheets of A4 paper.
