That's a wrap for today! We'll go through the unanswered questions over the next few days.
THANK YOU for all the great questions, looking forward to our next AMA
Hi, we are the Microsoft Excel team. We build the Excel app you use everyday. Ask us anything. XLOOKUP, Sheet View, Office Scripts, and more. We’ll be taking your questions for an hour starting at about 10 AM PT.Proof: https://i.redd.it/c0xfwo06bre41.jpg
What feature is your team is most proud of creating in the recent past?
Thanks for all you do!
We've worked hard to bring users great new features recently - it's hard to name just one!
A few recent ones that are notable:
Check out more on the Excel Blog! https://techcommunity.microsoft.com/t5/excel-blog/bg-p/ExcelBlog
How do you feel about people using Excel when they really should be using real database software instead? Has this kind of “off-label” use driven a lot of bloat over the years?
Excel is indeed likely the most popular database application in the world, despite that not being its core purpose.
We have mixed feelings. We certainly support "data tracking" scenarios in Excel and do invest in making that experience better, e.g. Tables.
At the same time, Excel favors flexibility over structure that can often violate the rules of a traditional Database application, e.g. cross-row calculations that depend on the sort order.
It comes down to getting the job done. Excel offers are level of ease of use and flexibility that database applications often don't, i.e. a typical user can create a workbook to track their data and may get lost in the complexities of a database app.
Does this flexibility benefit the job more than it hinders it? We don't think there's a one-size-fits-all answer to this question. It depends.
Can we have flight simulator return in Excel as another Easter egg like it was in Excel 97?
Not as an Easter Egg, but there are Excel gurus who work on ray tracing, so we might see it come back as a user workbook soon: https://www.pcgamer.com/amp/the-latest-thing-to-support-ray-tracing-is-excel-apparently/ -- Alex
The older versions of Excel used to allow you to easily click on bars in the margins and drag them vertically and horizontally to isolate panes on the worksheet that you could then scroll independently through back and forth, up and down with. It was way easier and more functional than FREEZE PANES, which is so limiting. Now you have to hide columns or rows and then freeze panes to get the same effect. Why was this functionality removed?? Can I get it back please?
Thanks for your question. It sounds like you are talking about the "split panes" feature. Here's a link to confirm: https://support.office.com/en-us/article/Split-panes-to-lock-rows-or-columns-in-separate-worksheet-areas-516A7001-B3ED-4122-A6BB-FD6D4A9D6434
If this is the feature you mean, it's still available in Excel.
-Johnnie, Excel Team
Are there any plans to bring some improvements to the VBA editor, VBA language itself or the userforms? There are some great addins like Rubberduck that bring in some additional functionality to the editor, but some more love from you guys would be appreciated as many of its aspects feel really dated.
And on a similar note:
Would it be possible to add mouse native scrolling inside the userform elements when scrollbar appears? I was pretty surprised that this functionality is not present and after some googling, it apparently requires a metric ton of code involving calling windows API and what not, and unfortunately, it either does not work, or requires 32bit office. Thank you.
Hi! Thanks for the question. In terms of VBA/User form/ActiveX improvements, we will continue to keep it healthy and ensure it stays current across versions of Excel and Mac/Windows.
AI & Machine Learning are all the rage these days. Any plans to implement a new, smarter, Clippy Office Assistant to leverage these new technologies?
One "feature" that's been bothering me that's been in Excel for years: if you are typing text in a cell, and the text gets close to the right edge of the column, it adds a blank line to the bottom of the cell when you hit enter. It doesn't actually add a newline, but just makes the cell one line taller/lower. Is this intentional, and if so, why?
Text metrics are often a little fiddly and we tend to err on the side of making sure there's enough room. But this may be something we've gotten too used to and bites users more often that we think. I'll make a note to follow up.
edit PIVOT TABLE SOURCE window
1) Why the edit PIVOT TABLE SOURCE window is so small? It cannot be resized in any way, what is very difficult when someone has long paths?
2) Why this window does have some strange edit mode by default? If you press left/right to change path (for example mysource2010 to mysource2019) it does some weird paths. This can be fixed by pressing F2. Why the "F2" behavior is not the default???
Can you talk to what work, if any, has been done to incorporate Python into Excel? Do you foresee a Python IDE in Excel, similar to the VBE, anytime in the future?
Hey! Good question.
We’re still working on a way to enable Python in Excel, and are working with our core Python team at Microsoft to build a plan that we think can majority of the scenarios you told us about and ensure it can run wherever Excel runs. We're also looking into how we can bring this into PowerQuery via a connector.
In the meantime, we encourage folks to check out some great tools our community has built such as OpenPyxl, PyXLL and XLWings that you can use to get Python scripting in Excel today.
Additionally, you can also host your back-end logic via a web service in any language, including Python, and use web add-ins or a custom function (https://aka.ms/customfunctions) to call that web service.
Which excel alternative do you see as your biggest competition (not in terms of market, but in terms of elegance, features, design choices)?
If you could overhaul excel without worrying about the existing user base, how would you approach it differently?
I think the answer depends on the workbook. I've seen people implement Project Management solutions in Excel that would likely have been better off in a dedicated project management app.
It most often comes down to flexibility vs. structure. Database apps make enforcing data quality way easier than Excel, but sacrifice ease of user and ability to quickly change the rules after data has been partially collected.
FWIW our biggest competition is the previous version of Excel.
Please, for the love of school children and engineers everywhere. Why isn't there a setting to make Degrees rather than Radians the default trig result?
Mathematicians have a vicious lobby. -- Alex
XLOOKUP has already been a lifesaver - thank you!
What is next with the new data types function? Stocks and Geography seem like a good proof of concept, but I'd love to see more or perhaps user-customizable data types.
Glad to hear you are liking XLOOKUP. I’m a big fan myself 😊
Love your ideas around new data types. Lots of interesting possibilities in this space. Which data types would you like to see added? I see several entries in the Excel UserVoice site for new data types. Please make sure to vote for your favorite ideas or add a new entry. We use UserVoice as an input to help us prioritize future investments.
Here are some I found: https://excel.uservoice.com/search?query=data+types
-- Carlos (Excel Team)
Hey! We continue to work across and bring more capabilities for OfficeJS.
For Excel and pro-devs: We continue to iterate across a number of fronts. Recent items, we announced at Ignite coming to preview are Dynamic Ribbons APIs, improved Custom Function support with support for errors, expanding on the 2000+ JS apis we have. Learn more here (https://aka.ms/office-add-in-docs). We also have a monthly community call (https://aka.ms/officeaddinscall-join), which we just had one this morning with some new improvements. Hope to see you there next month! :)
For all users: We just announced Office Scripts (powered by OfficeJS apis) that lets users record actions and automate tasks, which is in preview for Excel for web: https://techcommunity.microsoft.com/t5/excel-blog/announcing-office-scripts-preview/bc-p/1095543#M994
Hi, first I want to thank you for working on the amazing tool that it MS Excel. I'm sure you have hundreds of thousands of grateful fans out there. Let me ask a few things that other people probably also wonder:
Will there be a hotkey to close the F1 help menu, which is so often accidentally opened?
When creating chart legends, the colors of each data series is shown in a little square. Will there be an option to change their size?
Right now data validation in a cell can be overwritten by a simple paste into that cell. Will you make data validation harder to overwrite?
What's the most bizarre use of Excel that you've ever heard of?
Several over the years... This recent one comes to mind
- David M
What Easter eggs are in the new versions of excel? I loved the 3D thing back when windows 98 was a thinng
We had to stop doing that. The fun police got us.
Hello MS Excel Gods,
When dragging a cell using the bottom right corner, Excel sees patterns and continues them. Is there a way to do this using a keyboard shortcut rather than using my mouse to drag the box? I know ctrl+d/r go down/right but those don't recognize patterns.
You can use ALT+E+I+S+Enter, which maps to the previous toolbars (Edit, Fill, Series)
Not sure, but I did find some conversations about this topic some SQL forums.
When are we going to see improvements to Power Pivot in Excel that more closely match Power BI. New functions like REMOVEFILTERS for example, or bi-directional relationships?
I've heard it is an issue that when these changes are made it could/would break compatibility with older version of Excel with an older Power Pivot engine, but does that mean it will never get updated for the sake of compatibility? Power Pivot in Excel hasn't seen any improvements since 2016. It is getting harder to use Excel because it is more difficult to replicate measures and the model that are supported in Power BI, and sometimes - sometimes - Excel is the right tool for the job.
There are two parts to this. It depends whether we're talking about features that break compatibility of the data model across Excel versions, or features that do not.
Part #1 - Features that break compatibility: Today the data model format is fully compatible across Excel 2013, 2016, 2019, and Office 365. Newer Power BI features that would break this compatibility include bi-directional cross-filtering, date-to-time relationships, and some others.
Part #2 - Features that don't break compatibility: There are some newer DAX functions that Excel can support without breaking compatibility (except that formulas using the newer functions can't be evaluated on older versions, just like new functions in Excel.)
We are working on some changes that will allow us to pick up newer DAX functions more quickly, so we don't lag as far behind Power BI in this area for as long. I hope we get this tackled relatively soon (but can't give you a date.)
We are also working on a longer term plan to allow Excel users access to the breaking features, but we want to approach that in a way that will not block Excel users from building data models that can be opened in all the different versions of Excel that support data models. I don't see this being addressed quickly - the solution will come later. In the interim, if you have a tabular data model that requires bi-directional cross filtering, and you don't want to handle this with a more complex measure, I recommend using Power BI Desktop for that model.
Hope this helps,
Are you planning to have any way to control localization? There are lots of those "little" things that you forget, because your development team seems to make wrong assumptions.
1) For example new waterfall chart - shows the "increase", "decrease", "sum" labels. The thing is that if you dont have an English version of Excel, then those labels will be called in local language.
That is a big problem when you work in a corporation where you share your files with people from other countries -> by default (that cannot be changed) the chart shows names in a way they cant understand.
2) Same is for "subtotals" in a pivot table. In English it will be a "subtotal", but in other languages something else.
3) there are also those arcane functions like CELL()
The problem is that it does not have standardized parameters - say numbers; but rather it takes parameters as words. In English version the parameter to see contents is "contents", but in German version the parameter is "inhalt". So a file shared between two users with different language versions will not work. Excel will translate the CELLS function (in English) to ZIELE (in German), but the parameter will be hard coded by user -> and the formula will not work. Who allowed this? Why not numbers as parameters?
Are there any plans to change it?
How much functionality do you intend to lock behind Office 365 account login in the future? It appears to be an increasingly annoying theme across the Office suit. ‘Want to use this function that is in no-way related to network access? Log in’
Also - as Ingeborg pointed out, having a subscription does not mean that network access is required to use the apps or features. You can still use Excel across different end points (Windows, Mac, Android, iOS) while offline. The O365 subscription just means that you have access to the latest new features each month.
-- Carlos (Excel Team)
I have an issue sometimes, that when I paste an image it duplicates itself like 1,000s of times somehow. No idea how. And I can sit there deleting them one by one but it's pretty much impossible. So:
Starting over is not an option. This issue is happening with our templates.
To the best of my knowledge this is unknown problem. Please, send feedback from Excel when you see this problem again - this will let us look into the logs and investigate the source.
To remove pictures in bulk, use Ctrl-G / Special / Objects to select all objects, then press Del. -- Alex
I'm super disappointed that I'm on mobile right now.
Is there a way to set a rule so when a cell has a manual entry vs a formula it'll change colors?
When every other row is shaded, is there a way to have it auto fix after deleting a row?
What is Xlookup, and how is it different from Vlookup?
Why does the window slow down/spike CPU usage when your mouse polling rate is high?
For #3 you can learn more details about the benefits of XLOOKUP here https://blog-insider.office.com/2019/08/29/announcing-xlookup/
My personal favorites include that it defaults to "Exact Match", it can look left, it has a simpler signature or set of arguments... and finally it has "XL" in the name. :)
-- Carlos (Excel Team)
How come the language and date management is still screwed up after so many years of development? It feels like there is no rationale behind some formatting decision.
In a filter it shows the dates with the month name in a language which is not the default one in the options, in a pivot table the filter doesn’t have the months and you have to select day by day, wtf?
Sorry that these issues have been frustrating your experience. Please use Send-a-Frown to help us better track these issues you have expressed. -Dan
I run a selection committee for a student program where we have each applicant scored in multiple categories by multiple reviewers. My method of normalizing the scores is, to put it charitably, inelegant and time-consuming. How would you recommend going about this more efficiently?
For reference, let's say I've got 40 different reviewers who each score 30 each applicants in 4 different categories. Each application is scored by two reviewers.
And in case relevant, I'm using MS Excel for Mac (Version 16.16.18)
EDIT LINKS WINDOW
1) Why the EDIT LINKS window is so small? It cannot be resized in any way, so it is very hard to see paths
2) Paths cannot be copied from there.
3) There is a bug in EDIT LINKS window. When you have a file with say 10 other files, click shift to highlight them all + then click the "refresh" button; it only refreshes the first link.
Why this window and many other cannot be resized???
In the main ribbon, in the Font menu, there is this button under the font, that allows to set cell border styles. Why does it change randomly all the time? Why cant it remember the last selection? Why cant one set a default for it?
I'd argue that 99% of users would either want "all borders" as a default.
Why does it show "top border yes, other not really" when I open Excel?
Thanks for your question. When you first open Excel, the button defaults to "Bottom Border". The button will then remember the last selection you made - with that button. But it doesn't remember the last border setting you made in some other part for the app.
Setting your own defaults for the border button is an interesting suggestion. You should feel free to make feature requests at http://excel.uservoice.com.
You might want to upvote this suggestion https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/38922664-default-the-borders-button-to-all-borders-instea
If you are a windows user a couple hot keys that i find helpful are.. CTRL+SHIFT+7 Applies All Outer Borders to your current selection CTRL+SHIFT+_ Removes all borders from your current selection
Thanks! Johnnie, Excel Team
How deep has the rabbit hole gone for "spreadsheets about spreadsheets about spreadsheets abou-"?
How do you widen the columns in the unlicensed version?
Had to edit a CSV file yesterday, it opened in Excel (which came with the PC). The columns were like 8 characters wide, making the entire app entirely worthless. I spent about 15 minutes poking around before finally just saying "fuck it" and using Notepad.
Not exactly enticing me to pay money with the default file association opening a completely useless application. I can't imagine the experience of someone who doesn't know you can edit CSV files in a text editor. It'd probably feel like extortion...
You could open the CSV file in Excel Online (it will convert the file) - then you can edit the column widths.
For some reason, the "Insert Data from Picture" option doesn't appear on my Android phone (Poco F1). I tried emailing the App developers, but did not receive any response. Is there anyone who can assist with Excel Phone app issues?
Which language are you using on your mobile device? Unfortunately this feature is not yet supported in all languages. you can see the list of supported languages here: https://support.office.com/en-us/article/insert-data-from-picture-3c1bb58d-2c59-4bc0-b04a-a671a6868fd7 - Avital
XLOOKUP isnt available on my work computer even though I updated Excel under File->Account->Update Options. Is there something else i need to do?
XLOOKUP is available to all Office 365 Subscribers in the Monthly Channel. Users in the Semi-Annual Channel will see it starting in July. If you are on the Semi-Annual Channel, you may be able to switch to the Monthly Channel under File/Account. [Joe McDaid]
Are there any unknown easter eggs? What is the best easter egg?
We love and our passionate about making our APIs are easy to use as possible. Some of what you have we're thinking about on how to improve, but nothing concrete yet. For now, a great place for this feedback or suggestions is on our OfficeJS uservoice site: https://officespdev.uservoice.com.
Can you clarify the question, please? I've entered =HYPERLINK("[Book1]Sheet"&ROW()&"!B1") in A1:A10 and it works as I'd expect. -- Alex
Does your team have any development in place to fix the quality of exported images?
I find it kind of frustrating that the crispness of the text is immediately impacted.
Try Copy as Picture / As shown on screen / Bitmap. Is this better? -- Alex
I love Excel formulas, but there are still simple processes that take extremely long formulas (both in character length and runtime). Is there any thought to adding functions that enable programming capabilities? Like variables, and Map/Reduce?
Also, indexing into arrays would be awesome, so we didn't need Shift-Enter for ArrayFormulas.
Will ScriptLab ever get its own dedicated window like VBE has? And will it get more advanced features like debugging tools? Or is the tentative plan just to use an external editor like VS code to develop Office Add-Ins?
Pro-tip: If you're using ScriptLab to explore the Office JS apis in Excel Online, you can pop out into it's own window (I love that feature). Scriptlab is a great prototype tool, but to build an add-in we recommend an editor. We have some good tools like the Office Yeoman generator to help you get started (https://aka.ms/office-add-in-docs).
If you're looking for create a set of scripts focused on automating tasks, we have a new feature we announced at Ignite called Office scripts that is now in preview for Excel for web. We'd love feedback on that as well: https://techcommunity.microsoft.com/t5/excel-blog/announcing-office-scripts-preview/bc-p/1095543?search-action-id=150213220158&search-result-uid=1095543#M994
The new dynamic array formulas in Excel are pretty great. Do you have any additional ones planned to what's available so far? Are there any plans to get something like Google Sheet's query function anytime soon?
Thanks we love them too! We continuously release new functions so you can expect some great ones in future. If you have something you'd like to see, please submit a request on Excel Uservoice (or add a vote to an existing request) . Here is an existing request for a QUERY like function.
Why can't I undo renaming my sheets in the Excel web version like I can on my iPhone? How can I enable that?