Latest Articles

Most Read

  1. You are here:  
  2. Home
  3. RSS News Feeds
  4. Microsoft Excel
  5. Excel Blog - Microsoft Tech Community

The good news about computers is that they do what you tell them to do.
The bad news is that they do what you tell them to do.
- Ted Nelson

Top Ten Tags

Who's Online

Excel Blog - Microsoft Tech Community

Excel Blog articles

  1. What's New in Excel (April 2024)

    Welcome to the April 2024 update.


    Generating multiple formula columns, creating complex formula columns that span across multiple tables, and new ways to engage with Copilot in Excel are now available to Excel users on web and Windows. Copying and pasting improvements and sharing links to sheet views are also available in Excel for the web, and the ink to text pen is rolling out to Insiders running Excel for Windows.

    Excel for Web:

    1. Generating multiple formula columns with Copilot in Excel
    2. Creating complex formula columns that span multiple tables with Copilot in Excel
    3. Copying and pasting Improvements #FIA
    4. Sharing link to sheet views #FIA

    Excel for Windows:

    1. Generating multiple formula columns with Copilot in Excel
    2. Creating complex formula columns that span multiple tables with Copilot in Excel
    3. New ways to engage with Copilot in Excel
    4. Ink to text pen (Insiders)

    Excel for Web

     

    1. Generating multiple formula columns with Copilot in Excel

    Copilot in Excel now supports generating multiple formula columns from a single prompt. Ask one question, and Copilot can return two formula columns simultaneously. For instance, you can extract both the first name and last name from a single prompt, neatly separating the information into distinct columns. Read more here.

     

    2. Creating complex formula columns that span multiple tables with Copilot in Excel
    Additionally, now you can use Copilot to create complex formula columns that span across multiple tables, utilizing functions like XLOOKUP and SUMIF. This capability streamlines data processing and empowers you to handle more complex data analysis tasks efficiently, even when using multiple tables. Read more here.
    Generating Complex Formula Columns from Multiple Tables with Excel CopilotGenerating Complex Formula Columns from Multiple Tables with Excel Copilot

     

    #FIA

    3. Copying and pasting improvements

    Based on feedback we’ve received from many of you, we’ve made the following improvements to the copying and pasting experience:

    • Drag and Drop Enhancements: Provides a more seamless and intuitive way to manipulate data.
    • Autofill: Enables you to drag data to fill series, copy cells, and split data.
    • Paste options improvements: Allows you to choose the right paste option for your needs by right-clicking or accessing the auto-recovery paste options.

    Read more here >

    Copying and Pasting Web ImprovementsCopying and Pasting Web Improvements

     

    #FIA

    4. Sharing link to sheet views
    You can now share a link to a Sheet view that captures a subset of a workbook’s contents. This new capability can help you more efficiently collaborate on large, complex workbooks and more easily gather feedback and input from others. Read more here >
    Share links to Sheet views in Excel for the webShare links to Sheet views in Excel for the web

     

    Excel for Windows

     

    1. Generating multiple formula columns with Copilot in Excel

    Copilot in Excel now supports generating multiple formula columns from a single prompt. Ask one question, and Copilot can return two formula columns simultaneously. For instance, you can extract both the first name and last name from a single prompt, neatly separating the information into distinct columns. Read more here.

     

    2. Creating complex formula columns that span multiple tables with Copilot in Excel
    Additionally, now you can use Copilot to create complex formula columns that span across multiple tables, utilizing functions like XLOOKUP and SUMIF. This capability streamlines data processing and empowers you to handle more complex data analysis tasks efficiently, even when using multiple tables. Read more here.
    Generating Complex Formula Columns from Multiple Tables with Excel CopilotGenerating Complex Formula Columns from Multiple Tables with Excel Copilot

     

    3. New ways to engage with Copilot in Excel
    We’ve enhanced Copilot in Excel to make the experience even more seamless and efficient. Now, with Copilot in Excel, you can use the Microphone feature to prompt Copilot verbally, eliminating the need for manual typing. In addition, for fresh prompt ideas tailored to your specific data, now you can select the View Prompts icon to explore the Prompt Guide in the chat pane.. This feature is currently rolling out to Windows users. Read more here.

     

    (Note: Copilot in Excel is currently in preview. Learn more about Copilot in Excel here.)


    An image showing the prompt guide button selected with prompt options displayed.An image showing the prompt guide button selected with prompt options displayed.
    4. Ink to text pen (Insiders)

    The Ink to Text Pen lets you use your digital pen (or stylus) to automatically convert your handwriting into text as you go, helping you quickly enter content into cells. Additionally, you can use pen gestures to select and delete cell content.

    NOTE: The Ink to Text Pen replaces the functionality of the Action Pen in Excel.
    Read more here >Ink to Text Pen selectorInk to Text Pen selector

     

     

    Check if a specific feature is in your version of Excel

    Click here to open in a new browser tab

     

     

     

    Your feedback helps shape the future of Excel. Please let us know how you like a particular feature and what we can improve upon—"Give a compliment" or "Make a suggestion"..  You can also submit new ideas or vote for other ideas via Microsoft Feedback.

     

    Subscribe to our Excel Blog and the Insiders Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on X, formerly Twitter.

     

    Special thanks to our Excel MVPs David Benaim and Bill Jelen for their contribution to this month's What's New in Excel article. David publishes weekly YouTube videos and regular LinkedIn posts about the latest innovations in Excel and more. Bill is the founder and host of MrExcel.com and the author of several books about Excel.

     

  2. What's New in Excel (March 2024)

    Welcome to our March 2024 update. 

    This month, export to CSV and show details in PivotTables are now available in Excel for the web. Also, creating Power BI-connected tables is available in Excel for the web users, as well as Office Scripts for Office 365 E1 and F3 licenses.

    Excel for Web:

    1. Export to CSV
    2. Create Power BI Connected Tables in Excel
    3. Show Details in PivotTables #FIA
    4. Office Scripts for Office 365 E1 and F3 Licenses

    Excel for Web

    1. Export to CSV

    The ability to export to CSV is now available to all Web users. This experience was already supported for Windows, and Mac users and we’ve now expanded this service to all Web users. Take advantage of this lightweight file format in Excel for the Web to exchange and use data between apps and across platforms. Read more here >
    Export to CSVExport to CSV
    2. Create Power BI Connected Tables in Excel
    A new Insert Table option is now available for the Power BI Datasets pane in Excel. It enables users to create a connected table directly in Excel. This streamlines the workflow of adding data and is friendlier to more Excel users. Create Power BI-connected tables is now available in Excel for the web – and Excel for Windows (Current Channel). Read more here >

    Power BI Connected TablesPower BI Connected Tables



    #FIA

    3. Show Details in PivotTables
    We've added a new button on the ribbon under the 'PivotTable Analyze' tab called Show Details, this button allows you to drill into your PivotTables. Previously, you were able to double-click a value cell in the PivotTable and create a new table to further analyze the details for that value. This button makes this existing capability easier to find and use. This functionality was already available for Web users. 

    4. Office Scripts for Office 365 E1 and F3 Licenses
    Starting today you can find the Office Scripts feature set in the Automate tab on the Excel ribbon. Office Scripts is an automation platform in Excel that allows users with all levels of programming experience to automate their repetitive workflows. To get started, use the Action Recorder to record the actions you take in Excel. These actions are then translated into a script that you can run at any time. No programming experience required! Read more here >
    Office ScriptsOffice Scripts

     

     

    Check if a specific feature is in your version of Excel

    Click here to open in a new browser tab

     

     

     

    Your feedback helps shape the future of Excel. Please let us know how you like a particular feature and what we can improve upon—"Give a compliment" or "Make a suggestion"..  You can also submit new ideas or vote for other ideas via Microsoft Feedback.

     

    Subscribe to our Excel Blog and the Insiders Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on X, formerly Twitter.

     

    Special thanks to our Excel MVPs David Benaim and Bill Jelen for their contribution to this month's What's New in Excel article. David publishes weekly YouTube videos and regular LinkedIn posts about the latest innovations in Excel and more. Bill is the founder and host of MrExcel.com and the author of several books about Excel.

    Last edited on April 3rd: Moved Office Scripts under Excel for Web.

  3. Office Scripts is now available for Office 365 Enterprise E1 and Office 365 F3 licenses

    We are happy to announce that Office Scripts in Excel is now available for customers withOffice 365 Enterprise E1 andOffice 365 F3! Starting today you can find the Office Scripts feature set in theAutomatetab on the Excel ribbon.  

     

    What is Office Scripts? 

    Office Scripts is an automation platform in Excel that allows users with all levels of programming experience to automate their repetitive workflows. To get started, use theAction Recorder to record the actions you take in Excel. These actions are then translated into a script that you can run at any time. No programming experience required! If you need to modify your script, you can use the Code Editor, a TypeScript-based editor directly within Excel. You can use it to edit your existing scripts or to create new ones using the Office Scripts API. If you want to see more examples, see our numerous sample scripts based on real-world scenarios. 

     

    How it works  

    1. To start a recording, selectAutomate > Record Actions, and then select stop once you’re done. scritps with box good 1.gif

       

    2. You can edit your script using the Edit button.scripts recording 2.gif

       

    3. Click the Run button to run your script or any of our samples.  

    scritps with box good 3.gif

     

    Learn more about Office Scripts  

    Are you new to Office Scripts and wondering how to get started? Check out the following resources created by our team and community. 

     

    Share your feedback  

    We'd love to hear your feedback as you try out Office Scripts! Below are a few ways you can connect with our team:  

    • Give us feedback by selecting the Feedback button located in the overflow menu when you select a script.   

    Thank you for using Office Scripts!  

  4. What's New in Excel (February 2024)

    Welcome to the February 2024 update.

    This month, we are excited to share that the Chart data task pane and the ability to use images and data types in your PivotTables are now available in Excel for the web. The new default theme for Office is generally available in Excel for Windows and Mac now as well. 

    Excel for Web:

    1. Chart Data Task Pane 
    2. Images and Data Types in PivotTables

    Excel for Windows:

    1. New Default Theme for Office

    Excel for Mac:

    1. New Default Theme for Office

     

    Excel for Web

    1. Chart Data Task Pane

    You can now use the data task pane to edit your chart's data on the web. The data task pane supports the following:

    • Change a chart's source data range via range picker UI (mouse selection)
    • Expose new web-first UI to determine how data series are displayed on the chart (including which axis they are displayed on)
    • Provide controls for configuring how hidden/empty cells appear and whether the source data is split by rows or columns

    *This capability is already supported for Mac and Windows users

     

    Chart Data Task PaneChart Data Task Pane

     

    2. Images and Data Types in PivotTables
    Create richer and more visually appealing PivotTables that include images and other Excel data types like Stocks and Geography. While data types and in-cell images are a powerful part of modern Excel, PivotTables could only show a text description of these modern content types. Now, images and data types come intact to your PivotTable rows and columns. This feature has now been released to all Excel web users. Read more here >
    Use Images and Data Types in your PivotTablesUse Images and Data Types in your PivotTables

    Excel for Windows

    1. New Default Theme for Office
    We’ve refreshed the Office theme with a new default font, color palette, style, and line weights that help you make your documents more modern and accessible. This feature was previously released to Insiders users and is now generally available to all Windows and Mac users. Read more here >

     

    Excel for Mac

    1. New Default Theme for Office

    We’ve refreshed the Office theme with a new default font, color palette, style, and line weights that help you make your documents more modern and accessible. This feature was previously released to Insiders users and is now generally available to all Windows and Mac users. Read more here >

     

     

    Check if a specific feature is in your version of Excel

    Click here to open in a new browser tab

     

     

     

    Your feedback helps shape the future of Excel. Please let us know how you like a particular feature and what we can improve upon—"Give a compliment" or "Make a suggestion"..  You can also submit new ideas or vote for other ideas via Microsoft Feedback.

     

    Subscribe to our Excel Blog and the Insiders Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on X, formerly Twitter.

     

    Special thanks to our Excel MVPs David Benaim and Bill Jelen for their contribution to this month's What's New in Excel article. David publishes weekly YouTube videos and regular LinkedIn posts about the latest innovations in Excel and more. Bill is the founder and host of MrExcel.com and the author of several books about Excel.

  5. What's New in Excel (January 2024)

    Happy 2024!! Welcome to our first update of the year, the January update.

    This month, we are excited to share that users can now sync form data to Excel for the web. Insert pictures into cells is available to users of Excel on Windows and Mac, and PivotTables has expanded functionality in Excel for iPad.


    Many of these features are the result of your feedback. THANK YOU! Your continued Feedback in Action (#FIA) helps improve Excel for everyone.

    Excel for Web:

    1. Sync Forms Data to Excel
    2. Date Picker #FIA

    Excel for Windows:

    1. Insert Pictures in Cells #FIA
    2. Check Performance (Insiders Beta)

    Excel for Mac:

    1. Insert Pictures in Cells #FIA

    Excel for iPad:

    1. PivotTables on iPad

    Excel for Web

    1. Sync Forms Data to Excel

    With just one click, you can now easily access all your form responses in Excel for the web and take advantage of Excel’s rich functions to analyze and visualize your data. With automatic syncing of new responses in real-time, you can keep working on your existing spreadsheet without missing a beat. This feature is currently rolling out to Web users. Read more here >
    Data Sync to Microsoft FormsData Sync to Microsoft Forms

    #FIA

    2. Date Picker
    The Date Picker feature in Excel allows you to quickly insert a date from a calendar within a cell. The Date Picker pop-up is automatically enabled if the cell is formatted as a date or a date value is entered in a cell. Just double-click on the cell to add ormodify the date. This feature is currently rolling out to Web users.

     

    Excel for Windows

    #FIA

    1. Insert Pictures in Cells
    We've enabled the ability for a picture to become the actual cell value. It remains attached to the data even when the sheet’s layout is modified. You can use it in tables, sort, filter, include it in formulas, and much more! Read more here > or watch this YouTube instructional video from one of our Excel MVP's Leila Gharani.
    Insert Pictures in CellsInsert Pictures in Cells

    2. Check Performance (Insiders Beta)

    When you open your workbook, Excel can detect whether your workbook contains unwanted formatted cells that can slow down your workbook. If so, Excel suggests launching “Check Performance". You can also manually launch the feature from Review > Check Performance. Read more and try it yourself >. This capability is already available for all web users, and now available to Windows Insiders Beta users.

     

    Excel for Mac

    #FIA

    1. Insert Pictures in Cells
    We've enabled the ability for a picture to become the actual cell value. It remains attached to the data even when the sheet’s layout is modified. You can use it in tables, sort, filter, include it in formulas, and much more! Read more here > or watch this YouTube instructional video from one of our Excel MVP's Leila Gharani.
    Insert Pictures in CellsInsert Pictures in Cells

    Excel for iPad

    1. PivotTables for iPad

    PivotTables allow you to calculate, summarize, and analyze data. This powerful tool has now been tailored for the iPad's smaller screen and touch interface. Read more here >

    PivotTables on iPadPivotTables on iPad

     

     

    Check if a specific feature is in your version of Excel

    Click here to open in a new browser tab

     

     

     

    Your feedback helps shape the future of Excel. Please let us know how you like a particular feature and what we can improve upon—"Give a compliment" or "Make a suggestion"..  You can also submit new ideas or vote for other ideas via Microsoft Feedback.

     

    Subscribe to our Excel Blog and the Insiders Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on X, formerly Twitter.

     

    Special thanks to our Excel MVPs David Benaim and Bill Jelen for their contribution to this month's What's New in Excel article. David publishes weekly YouTube videos and regular LinkedIn posts about the latest innovations in Excel and more. Bill is the founder and host of MrExcel.com and the author of several books bout Excel.

    Last edited on April 3rd: We clarified the release timing for Sync Forms to Excel, Date Picker, and Check Performance.

  6. Introducing PivotTables on iPad

    We are excited toannounce support forPivotTablecreation and editing on iPad. PivotTables allow you tocalculate, summarize, and analyze data. We havetailored this powerful tool for the iPad's smaller screen and touch interface. Now, you have the flexibility to move seamlessly betweendesktop, web, and iPad whilemaintaining a consistentexperience across the board.Unleash the full potential of PivotTables, making every calculation and analysis simple on the go. 

     

    1. Create a PivotTable 

    To get started, navigate to theInsert tab, selectPivotTable, and choose a Source and Insertion location. Insert your PivotTable with a single tap. 

    create_a_PT.png

     

    2. Use the Field List 

    Tailor a PivotTable to your exact needs with ease using the field list. Theareas section at the bottom empowers you to rearrange fields with ease by dragging them across the different sections to achieve an insightful data representation. 

    using_field_list.png

     

    3. Change the Source Data  

    Adjust your PivotTable’s source data seamlessly by navigating to thePivotTable tab and engaging theChange Data Sourceside pane. It's a straightforward process that ensures your analysis remains dynamic and up to date. 

    change_source.png

     

    4. Change the Settings 

    Fine-tune your PivotTable effortlessly by accessing theSettings side pane. Make the desired modifications and save your changes with a simple tap to make your PivotTable work precisely how you want it to. 

    change_settings.png

     

    5. Move the PivotTable 

    Move your PivotTable within and across worksheets through cut and paste in the context menu. 

    cut_pivottable.png

     

    Learn more 

    Check out our documentation below for more information about how to use PivotTables on iPad: 

     

    Availability 

    To use this feature, run Excel on iPad version 2.82.205.0 and above. 

    Don’t have it yet? It’s probably us, not you. Features are released over time to ensure things are working smoothly. We highlight features that you may not have because they’re slowly releasing to larger numbers of users. Sometimes we remove elements for further improvement based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product even if you have had the opportunity to try it. 

     

    Sharing feedback 

    We hope you like this new addition to Excel on iPad and we’d love to hear what you think about it!Settings >Help & Feedback, then selectTell Us What You Like orTell Us What Can Be Better. 

     

  7. 2023 in Review

    As we wrap up 2023, we on the Excel team want to express our heartfelt gratitude to you all across our Excel community. We greatly appreciate each and every one of you for your continued contribution to the community, helping others learn more about Excel, and giving us feedback to make Excel even better.

     

    2023 was filled with excitement for Excel, from Excel esports on ESPN8: The Ocho to the Excel Collegiate Challenge, the Excel World Championship, and more.

     

    2023 was also an incredibly exciting year of innovations and delights, from Python in Excel and Copilot in Excel to formula suggestions and formula by example and inserting pictures and checkboxes into cells, and more! Below are only a few highlights – see our monthly What's New in Excel series for more.

     

    • Copilot in Excel. Helps you do more with your data in Excel tables by generating formula column suggestions, showing insights in charts and PivotTables, and highlighting interesting portions of data. Read more >
    • Formula Suggestions. Helps you create formulas more quickly and accurately by offering relevant suggestions based on your data and context. Type "=" at the beginning of a cell, and Formula Suggestions will show you different formulas that you can apply to your data, along with a specified range. Read more >
    • Formula by Example. Looks for patterns as you enter data in the worksheet. When it recognizes a pattern, Formula by Example offers a formula to fill the rest of the column with the recognized pattern. Read more >
    • Checkboxes. Quickly visualize and set TRUE and FALSE values in a cell using checkboxes to simplify data entry and reduce errors. Read more >
    • Insert pictures into cells. Insert local pictures, directly into cells, from your device or from the stock image libraries. Read more >
    • GROUPBY & PIVOTBY. Perform data aggregations using a single formula. Read more >
    • Python in Excel. Natively combine Python and Excel analytics within the same workbook - with no setup required. Type Python directly into a cell, the Python calculations run in the Microsoft Cloud, and your results are returned to the worksheet, including plots and visualizations. Read more >

     

     

    Thank you for your continued contribution to the community. We look forward to 2024 with even more excitement, innovations, and delights with you all.

     

    Subscribe to our Excel Blog and the Insiders Blog to get the latest updates, and join our Excel Community to stay connected with us and other Excel fans around the world.

  8. A new way to debug formulas from Excel Labs

    Have you ever found yourself tangled in a web of complex Excel formulas, desperately trying to pinpoint the source of an error? Fret no more! The latest experiment from Excel Labs is a new formula debugger, available within the Advanced Formula Environment (AFE).

     

    The AFE debuggerThe AFE debugger

    Getting started

    The debugging capabilities are activated using the new button on the Grid page in AFE. When the button is activated the debugger will be visible in the same editing pane, and you are ready to go!

     

    Key features

    Here are some of the key features to look out for:

     

    - Live debugging

    The debugger updates as you type, making it quick to explore how different variations of a formula are evaluated.

    Live debugging as you edit the formulaLive debugging as you edit the formula

    - Evaluation steps

    Each evaluation step is shown, with highlights and underlines making it easy to see what changed at every step.

    Evaluation steps show each change in the formulaEvaluation steps show each change in the formula

    - Range preview

    View a preview of the grid when hovering over a reference. Previews show the surrounding context to make it easier to navigate.

    Preview referenced grid rangesPreview referenced grid ranges

    - LAMBDA debugging

    Debug LAMBDA formulas, such as SalesForBestCategory, shown below. Every function call is contained in an expandable card. This lets you dive into the particular functions you are interested in, to see how they evaluate.

    Debug LAMBDA formulasDebug LAMBDA formulas

    Function support

    Not all functions are supported just yet. If a function or scenario is blocking your work, we would love to hear about it!

     

    In case you missed it: Sheet-defined functions

    Another experimental feature in the advanced formula environment is the ability to automatically convert a multi-cell calculation into a function, also known as sheet-defined functions. This capability has been part of AFE for a while, but in case you missed it – here is a brief overview.

     

    Often, we find ourselves spreading a complex calculation over many cells, like this one, that extracts an ID from a piece of text.

     

    Multi-cell calculation for extracting an IDMulti-cell calculation for extracting an ID

     

    After writing the calculation we are either faced with many intermediate cells, or we must roll the formulas into a single mega-formula. With AFE and the “Add function from grid” feature, we can automatically convert these formulas into a tidy function using three simple steps:

     

    1. First, select the range containing the formulas that will make up the function.
    2. Then click the “Add function from grid” button and provide the references that will define the inputs and outputs. Sometimes, AFE can automatically detect the inputs and outputs based on the dependencies.
    3. Finally, AFE will present you with the generated function that combines the formulas in the range. If you use labels adjacent to formulas, AFE can also use those to generate friendly parameter names, like Text or After.

    Define a custom function from cells in the gridDefine a custom function from cells in the grid

     

    How to access

    The debugger will be pushed to Excel Labs automatically and there is no need to explicitly update the add-in. If you do not have Excel Labs, you can install the add-in from the Office store. Follow this link.

  9. What's New in Excel (November 2023)

    Welcome to the November 2023 update. This month we are excited to share a number of new features rolling out across Excel for the web, Windows, and Mac. GROUPBY and PIVOTBY, and Export Loop Tables to Excel are both available to Windows and Mac Insiders. Trendline Equation Formatting is now out to all web users, and Workbook Links are now live for Windows users.

     

    Many of these features are the result of your feedback. THANK YOU! Your continued Feedback in Action (#FIA) helps make Excel better for everyone. 

     

    Excel for web:

    1. Trendline Equation Formatting

     

    Excel for Windows:

    1. Workbook Links #FIA
    2. GROUPBY and PIVOTBY Functions (Insiders)
    3. Export Loop Tables to Excel (Insiders)

     

    Excel for Mac:

    1. GROUPBY and PIVOTBY Functions (Insiders)
    2. Export Loop Tables to Excel (Insiders)

    Excel for web

    1. Trendline Equation Formatting

    Additional trendline equation formatting controls are now available in the chart format task pane. This includes number and font/fill/outline formatting.


    Excel for Windows

    #FIA

    1. Workbook Links

    The Workbook Links task pane improves the experience and reliability for working with linked files including the long asked for Find feature to help you figure out where the links are used. Read more here >

    Workbooks Links RefreshWorkbooks Links Refresh

    2. GROUPBY and PIVOTBY Functions (Insiders)

    GROUPBY and PIVOTBY allow you to perform data aggregations using a single formula. All you need is just 3 arguments: i) What to group by, ii) the values to aggregate, and iii) the function you'd like to use for the aggregation. Read more here >

    GROUPBY FunctionGROUPBY Function

     PIVOTBY FunctionPIVOTBY Function

     3. Export Loop Tables to Excel (Insiders)
    When you’re collaborating as a team on a project, you may choose to add Loop tables to gather data and perform calculations. You can now export the tables to applications such as Excel so you can leverage its capabilities to perform any needed calculations. Read more here >
    Export Loop Tables to ExcelExport Loop Tables to Excel

    Excel for Mac

    1. GROUPBY and PIVOTBY Functions (Insiders)

    GROUPBY and PIVOTBY allow you to perform data aggregations using a single formula. All you need is just 3 arguments: i) What to group by, ii) the values to aggregate, and iii) the function you'd like to use for the aggregation. Read more here >

    GROUPBY FunctionGROUPBY Function

     PIVOTBY FunctionPIVOTBY Function

    2. Export Loop Tables to Excel (Insiders)
    When you’re collaborating as a team on a project, you may choose to add Loop tables to gather data and perform calculations. You can now export the tables to applications such as Excel so you can leverage its capabilities to perform any needed calculations. Read more here >
    Export Loop Tables to ExcelExport Loop Tables to Excel

     

     

    Check if a specific feature is in your version of Excel

    Click here to open in a new browser tab

     

     

     

    Your feedback helps shape the future of Excel. Please let us know how you like a particular feature and what we can improve upon—"Give a compliment" or "Make a suggestion"..  You can also submit new ideas or vote for other ideas via Microsoft Feedback.

     

    Subscribe to our Excel Blog and the Insiders Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on X, formerly Twitter.

     

    Special thanks to our Excel MVPs David Benaim and Bill Jelen for their contribution to this month's What's New in Excel article. David publishes weekly YouTube videos and regular LinkedIn posts about the latest innovations in Excel and more. Bill is the founder and host of MrExcel.com and the author of several books bout Excel.

  10. New aggregation functions: GROUPBY and PIVOTBY

    November 14th 2023 Status
    These functions are currently rolling out to users enrolled in the beta channel for Windows Excel and Mac Excel 

     

    I’m excited to announce Excel's new GROUPBY and PIVOTBY functions. These functions allow you to perform data aggregations using a single formula. And while these functions are extremely powerful, they are also simple to get started with. In fact, you can do a data aggregation with just 3 arguments, the same number as a simple XLOOKUP. 

     

    Note: These are preview functions, their signature and results may change substantially before final release based on user feedback. Until final, we do not recommend using them in important workbooks.


    GROUPBY

    To do a simple GROUPBY, you need just 3 arguments: i) What to group by, ii) the values to aggregate and iii) the function you'd like to use for the aggregation. 

     

    Aggregate Sales by CategoryAggregate Sales by Category

     

    PIVOTBY

    PIVOTBY is the same as GROUPBY but it let's you group by column too. 

     

    Largest Sale by Category and YearLargest Sale by Category and Year

     

    Functions as arguments

    In the examples above, we pass the SUM and MAX functions to GROUPBY and PIVOTBY respectively. These are lambda functions, “eta reduced lambda” functions to be precise. And while they have a complex name, they are extreme easy and intuitive to use – you don’t even need to know anything about lambda functions you can just pick them from a list.

     

    Function autocomplete listFunction autocomplete list

     

    We also added eta lambda support to our all our functions that accept lambdas, greatly improving formula readability for simple scenarios. Consider the BYCOL example below. Previously you'd have to have write: =BYCOL(C3:F6,LAMBDA(x,SUM(x))

     

    SUM by ColumnSUM by Column

     

    Function Signature

    GROUPBY and PIVOTBY have additional arguments that give you control of their outputs. For instance, you can control how they sort, whether your data has headers, and whether you want to filter out some values.

     

    You can see their full signatures below.

     

    GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])
    PIVOTBY(row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],
             [col_sort_order],[filter_array])

    Detailed information on these arguments can be found on the function help pages. (link, link)

     

    Why use GROUPBY and PIVOTBY?

    As functions, GROUPBY and PIVOTBY are lightweight, fully descriptive, automatically update and can depend on the result of other calculations. They also aggregate using lambda functions which unlock new aggregation capabilities.

     

    Consider the following example where I aggregate text values. Here I make use of a lambda to sort the unique items before joining them together using ARRAYTOTEXT. This type of aggregation was previously impossible in Excel. 

     

    Items sorted alphabetically by CategoryItems sorted alphabetically by Category

     

    Bonus function

    We added an additional function: PERCENTOF. It's a simple function that SUM's its first argument and divides by the SUM of its second argument. While you can use it on its own, it's particularly useful with GROUPBY and PIVOTBY as it allows you to return percentages. 

     

    PERCENTOF(data_subset,data_all)

     

    Percentage Sales by CategoryPercentage Sales by Category

    More information on the PERCENTOF function can be found on the help page. (link)

     

    Don’t have it yet? It’s probably us, not you.

    Features are released over some time to ensure things are working smoothly. We highlight features that you may not have because they’re slowly releasing to larger numbers of Insiders. Sometimes we remove elements to further improve them based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as an Insider, have had the opportunity to try it.

     

    Feedback

    If you have any feedback or suggestions, you can submit them by clicking  Help > Feedback. You can also submit new ideas or vote for other ideas via Microsoft Feedback.

     

    Want to know more about Excel? See What's new in Excel and subscribe to our Excel Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on Twitter.

     

    Joe McDaid (@jjmcdaid)
    Program Manager, Excel

     

     

 
 
 

My Wishlist

© Azli Hassan

Sample code and files are provided as-is. No guarantee is made that they are working correctly nor that they do what they were intended to. USE AT YOUR OWN RISK.



As an Amazon Associate I earn from qualifying purchases.
Bootstrap is a front-end framework of Twitter, Inc. Code licensed under MIT License. Font Awesome font licensed under SIL OFL 1.1.