Microsoft Reportedly Made Excel Formulas Even Easier to Use, Company Adds Handy Automation Features to Online Spreadsheet

Welcome to the December 2022 update. We’re excited to announce the release of formula suggestions and sample formulas for Excel for the web users. Microsoft has announced that it’s making Excel autocompletion 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 office suite developed and distributed by Microsoft. It is intended to run on Microsoft Windows, Mac OS X, Android, or Linux (using Wine) platforms. The Excel software integrates functions for numerical calculation, graphic representation, data analysis (particularly pivot tables) and programming, which uses macros written in the VBA (Visual Basic for Applications) language which is 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 Example 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 as you use it. Web users also benefit from suggested links, the IMAGE function and a new search bar in the queries pane. For Windows users, a new keyboard shortcut is available to open Power Query Editor, and Insiders on Windows users can now get data from dynamic arrays and create data types.

Formula suggestions

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 not a game-changing feature, admittedly – Google Sheets has had a similar feature for a while, and the feature I am myself Excel has long been a quick way to apply formulas to data – but for some use cases it could be a great time saver.

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

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 floating above 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 alt text. Just type the following text in 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 that describes the image (for accessibility);
• [Facultatif] sizing indicates the dimensions of the image. There are several possible values:
  • 0: Fit the image in the cell and maintain 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 limits of the cell;
  • 3: Customize image size using height and width.
• [Facultatif] height and width set the height and width of the image only when using the sizing 3 option.

Formula for example

When you do manual, repetitive data entry into a column, Excel now prompts you to fill the entire column with a formula in case they identify a pattern. This method is similar to flash fill, but instead of static text, formulas are suggested. The company is also adding data types Power Request nested tables and the ability to get data from dynamic arrays into 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 enable a new data store for workbooks in the cloud that detects when an external link to a workbook in the cloud is broken and suggests a new location to fix the broken link. This feature is currently deployed in production.

Source: Microsoft

And you?

What is your opinion on the subject?

What do you think of adding these two features?

Do you use a spreadsheet? Which ?

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

See as well :

Cisco predicts new wave of Excel-related problems, spreadsheet XLL files would 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 the productivity of spreadsheet users