Excel in Microsoft 365

Review

What is Microsoft 365?

In simple terms, Microsoft 365 is the new version of the subscription service Office 365.
Microsoft decided to introduce a new era of its subscription service with new connectivity, collaboration, and security features integrated into the Office package. This is another step away from its old standalone software business model towards and improved cloud- and subscription-based product line.
Users that are already subscribed to Office 365 are automatically transformed into Microsoft 365 subscribers, but for every Excel users who is still using an older standalone Office version Microsoft 365 is worth a closer look.

Top 10 new Excel features in Microsoft 365

If you are still using an older Excel standalone version, you are missing out on a lot of new features that improved Excel significantly over the last few years. The subscription model gives you access to the improvements through automatic updates, there is no need to buy a new Office version any more. Let’s have a closer look at the most important improvements.​

Over the last few years Microsoft constantly added new powerful functions to Excel that improved the possibilities and experience of the users significantly. Especially with Office 365 (now Microsoft 365) major progress and additions have been made. Below we give you an overview of the most valuable additions over the last few versions.

Microsoft 365 exclusives

  • XLOOKUP
  • FILTER
  • XMATCH
  • UNIQUE
  • SORTBY
  • SORT
  • RANDARRAY
  • SEQUENCE

Available in Microsoft 365 and Excel 2019

  • IFS, SUMIFS, AVERAGEIFS, COUNTIFS, …
  • CONCAT
  • TEXTJOIN

    For more detailed information about all these functions and their availability have a look at our filterable Excel functions database

    Over the last few years Microsoft started to continually add new powerful chart types to Excel that allow you to create impressive data visualisations. These chart types have been made to Excel users at different points of time, but in Microsoft 365 you will have all of them available instantly. Let’s have a look at the most important new charts types:

    • Map Chart (Microsoft 365 or Office 2019): Compare values and show categories across geographical regions. Excel automatically detects geographical references in your data and visualises the corresponding values in a stunning and customisable map chart. More details…
    • Waterfall Chart (Office 2016 or newer): This is one of the charts heavily used in business environments as it allows to visualise a running total as values are added and subtracted. It is perfect to visualise the decomposition of total values.
    • Funnel Chart (Office 2016 or newer): This is a must have chart for everyone working in a sales and marketing environment. Funnel charts allow you to show values across multiple stages in a process and are the best way to visualise on which stage improvements can be made or customers are getting lost in the sales process.
    • Sunburst Chart (Office 2016 or newer)The perfect chart for displaying hierarchical data. It is a multilevel doughnut chart with each ring representing one level in the hierarchy.
    • Treemap Chart (Office 2016 or newer): This chart types is another great hierarchical visualisation of your data. It allows you to easily spot patterns by displaying each branch in a rectangle and each sub-branch in it as a smaller rectangle with its size based on the regarded values.
    • Histogram Chart (Office 2016 or newer)Previous Excel versions always required additional effort to create this fundamental statistical chart type. Now you can insert it with one click to visualise the distribution of numerical and categorial data.

    Microsoft started this feature with introducing two smart data types: Stocks and Geography.

    With this feature you can convert your plain text into smart data objects and Excel automatically provides you with a whole bunch of live information that you can insert with a few clicks.

    The new Excel feature ‘ideas’ automatically gives you analytical insights into your data and allows you to ask questions in natural language just like your boss would ask you. All of this happens based on a newly developed AI solution (Artificial Intelligence) by Microsoft that aims to save you a lot of time by giving you all the important insights within a heartbeat. It doesn’t replace a full and deep-diving data analysis but it is a perfect starting point to find out where to dig deeper.

    A long awaited feature by many Excel users who use PivotTables on a regular basis. A reoccurring issue has been the big amounts of time you had to spent on fixing the layouts of every new PivotTable you inserted over and over again.

    Now, if you have Microsoft 365 (or Excel 2019), you can finally specify a default layout for all new Pivot Tables you create. 

    Adding Add-ins to Excel has become even easier with Office 365 (now Microsoft 365). While in earlier versions this was still a hassle, you can now insert many awesome add-ins directly via Insert > Get Add-ins and add it with one click.

    Once you experienced this feature for the first time, you never want to miss it again. It allows you to directly insert beautiful and customisable icons from a huge and steadily growing icon library directly from within Excel. The integrated icon library is exclusively available in Microsoft 365 and Office 2019.

    This feature allows you to take a picture of a printed data table with any Android smartphone device and convert it into an Excel spreadsheet within a second. The ‘Insert Data from Picture’ feature is a huge game changer as it is based on image recognition technology and eliminates the need of manually typing in your data.

    Money in Excel is a completely new feature introduced with Microsoft 365 in April 2020. It is a dynamic template that allows you to securely connect your banking institutions via standardised interfaces and import and synchronise your transactional data. Based on these data you can create dynamic charts and alerts to easily keep track of your personal finances.

    With Microsoft 365 a new and game-changing way of data interaction is introduced. Microsoft adds a massive amount of new data types and smart templates providing a deeper meaning for over 100 topics (including food, movies, places, and many more). By converting your text and numbers into one of the new data types, Excel will provide you with interactive data cards, additional information, and visualisations of your data.

    Let’s have a look at an example provided on the official Microsoft blog (link here):

    “How does it work? Say you want to start keeping better track of your nutrition. Simply enter a food like “avocado” and by converting it to a Food data type, Excel will help you access its nutritional information. Or maybe your family is considering adopting a dog. You can evaluate different types of breeds using the Animal data type, which provides you with images, facts, and their temperaments, so you can easily create a table to compare them. With data types, all information can be easily updated, so you always have the latest and relevant data. Anyone can access the Stocks and Geography data types in Excel for the web. And Microsoft 365 subscribers have exclusive access to the over 100 new data types powered by Wolfram Alpha.”

    Is it worth to upgrade my version?

    If you use Excel on a regular basis, an upgrade of your version is something you will not get around as Microsoft not release any new major standalone version in the future (the last one was Office 2019). 

     With that in mind, let’s take a look at the Pros and Cons of an upgrade to Microsoft 365 with regard to Excel: 

    • You get access to all new and advanced features
    • You can be the first to master the new functions & features in Excel
    • You will get every upgrade of Excel automatically
    • You will save money in the longterm as you don't have to buy every new release anymore
    • Microsoft 365 will be most companies' standard in the near future (since there will be no new standalone version release)
    • If you use Excel only on a basic level, you might not need the new features
    • Some of the new features might not be compatible with older Excel versions

    The best Microsoft 365 version for you

    The different versions of Microsoft 365 are suited to the needs of various target groups. The versions mainly differ in terms of amount of apps and services included and the number of users covered by the subscription. For the price comparison, we take a look at the US prices, but if you click on one of the version you be directly led to the Microsoft Online Store of your country with prices in your local currency. So let’s have a look and find the best version for you.​

    Microsoft 365
    Personal

    $69.99 / year

    For one person, across all your devices

    All Premium Office apps included

    1TB OneDrive Storage

    Compatible with PC, Mac, Android, iOS

    Microsoft 365
    Family

    $99.99 / year

    For up to 6 people, across all their devices

    All Premium Office apps included

    6TB OneDrive Storage

    Compatible with PC, Mac, Android, iOS

    If you are still feeling more comfortable with a standalone Office version, have a look at Office 2019 which includes the classic 2019 versions of Excel, PowerPoint, Word, and Outlook.​