Microsoft reportedly made Excel formulas even easier to use, company adds handy automation features to online spreadsheets

Welcome to the December 2022 update. We are pleased to announce the release of formula suggestions and sample formulas for Excel to web users. Microsoft has announced that it’s making Excel autocomplete even smarter, at least in the web-based version that comes with Microsoft 365 (formerly Office 365).

Microsoft Excel is spreadsheet software from the Microsoft Office suite developed and distributed by Microsoft. It is intended to run on Microsoft Windows, Mac OS X, Android or Linux (using Wine). The Excel software integrates functions for numerical calculation, graphical representation, data analysis (especially pivot tables), and programming using macros written in the VBA (Visual Basic for Applications) language common to the other Microsoft Office software.

A few months ago, Microsoft added the ability for team members on a SharePoint site to easily collaborate, view and run team-owned scripts on their Excel workbooks. However, Office Scripts, a set of automation features, is currently limited to Excel for the web. Last week, Microsoft announced Formula Suggestions and Sample Formulas, both of which can help automate certain operations.

Two interesting features designed to save time and allow you to learn more about Excel formulas while using it. Web users also benefit from suggested links, the IMAGE feature, and a new search bar in the query pane. For Windows users, a new keyboard shortcut is available to open the Power Query Editor, and Insiders on Windows users can now retrieve data from dynamic arrays and create data types.

Formal proposal

When you type the “=” sign in a cell or in the formula bar, Excel automatically suggests the best formula based on the context of your data. The formulas that can be suggested are SUM, AVERAGE, COUNT, COUNTA, MIN and MAX. According to Microsoft’s blog post, the feature currently only works in English, and it’s admittedly not a game-changing feature – Google Sheets has had a similar feature for a while, and the feature Auto sum Excel has long been a quick way to apply formulas to data – but for some use cases it can be a big time saver.

Then there’s the formula example, which is similar to the flashfill function, which can automatically detect patterns in the data and fill in the rest of a column. It’s a little hard to explain this feature succinctly, but this Microsoft video gives you an idea of ​​what it’s all about: discovering a pattern where you combine information from cells, then automatically generating a formula that will save you writing.

PICTURE function

The IMAGE function inserts images into cells from a source location along with alt text. Images can now be part of the spreadsheet instead of hovering over it. It is possible to move and resize cells, sort, filter and work with images in an Excel table.

How does it work?

The IMAGE function inserts images into cells from a source location along with alternative text. Just type the following text into a cell: =PICTURE(source, [alt_text], [sizing], [height], [width])where:

  • [Source est le chemin d’accs URL du fichier image, en utilisant un protocole “https”. (REMARQUE : les formats de fichier pris en charge sont BMP, JPG/JPEG, GIF, TIFF, PNG, ICO et WEBP) ;
  • [Facultatif] alt_text is the alt text describing the image (for accessibility);
  • [Facultatif] size indicates the dimensions of the image. There are several possible values:
    • 0: Fit the image in the cell and keep its aspect ratio;
    • 1: Fill the cell with the image and ignore its aspect ratio;
    • 2: Keep the original size of the image, which may exceed the borders of the cell;
    • 3: Adjust image size using height and width.
  • [Facultatif] height and width only set the height and width of the image when using size 3.

Formula for example

When you do manual, repetitive data entry in a column, Excel now prompts you to fill in the entire column with a formula if they identify a pattern. This method is similar to flash fill, but instead of static text, formulas are suggested. The company also adds data types Power request nested tables and the ability to retrieve data from dynamic arrays for the Insider version of the Windows app for testing.

Another potentially useful (and thankfully easy to understand) feature coming to the web is “suggested links,” which will automatically help you fix broken links to other workbooks stored in the cloud. Suggested Links enables a new data store for cloud workbooks that detects when an external link to a cloud workbook is broken and suggests a new location to fix the broken link. This feature is currently implemented in production.

Source: Microsoft

And you?

What is your opinion on the subject?

What do you think about adding these two features?

Do you use a spreadsheet? Which one ?

What do you think about Excel? Some users claim that it is the most successful software. What do you think ?

Also see:

Cisco predicts a new wave of Excel-related problems, spreadsheet XLL files will add custom features, including malware

Excel users on Windows and Mac will soon be able to automate repetitive tasks, Microsoft believes this feature will improve productivity for spreadsheet users

Leave a Comment