recaps of the top 'ask me anything' interviews from reddit and more...
We are the Microsoft Excel team – Ask Us Anything!

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


40
questions
178
score
February 12th 2020
interview date
purplepajamas

What feature is your team is most proud of creating in the recent past?

Thanks for all you do!


MicrosoftExcelTeam

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

-- Avani

ouij

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?


MicrosoftExcelTeam

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.

Thx!

IXIFr0stIXI

Can we have flight simulator return in Excel as another Easter egg like it was in Excel 97?


MicrosoftExcelTeam

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

Sunrain92

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?


MicrosoftExcelTeam

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

bugfestival

Hi guys!

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.


MicrosoftExcelTeam

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.

Right now, as are focus is around building great experiences across platforms, we've been investing on our Office Javascript APIs (learn more: https://aka.ms/office-add-ins-docs, which let you build add-ins across platform. Hope that helps!

--Keyur

Senipah

AI & Machine Learning are all the rage these days. Any plans to implement a new, smarter, Clippy Office Assistant to leverage these new technologies?


MicrosoftExcelTeam

BTW, if you really miss clippy, you could build an add-in that brings clippy back, via a javascript library called "Clippy.js", hehe

--Keyur

frogstein

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?


MicrosoftExcelTeam

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.

-Matty

vba7

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???


MicrosoftExcelTeam

Hi there,

These are all good suggestions. I encourage you to share them on Excel User Voice portal at https://excel.uservoice.com/. It helps us to prioritize our investments.

Guy - Excel Team

pheeper

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?


MicrosoftExcelTeam

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.

-- Keyur

coryrenton

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?


MicrosoftExcelTeam

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.

wkdintent

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?


MicrosoftExcelTeam

Mathematicians have a vicious lobby. -- Alex

nostrovia

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.


MicrosoftExcelTeam

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)

decimalturn

What are the next big improvements we should expect for OfficeJS (Office Javascript APIs)?


MicrosoftExcelTeam

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

-- Keyur

sizarieldor

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?


MicrosoftExcelTeam

Thanks very much! We use https://excel.uservoice.com/ to prioritize feature requests - we'd love to hear from you there with these great ideas!

BTW we've got hundreds of millions of people using Excel ;)

-Sharon

mookler

What's the most bizarre use of Excel that you've ever heard of?


MicrosoftExcelTeam

Several over the years... This recent one comes to mind

Functional Drum Machine: https://www.theverge.com/2020/2/6/21126465/xl-studio-music-making-drum-microsoft-excel-daw-dylan-tallchief

- David M

troll_detector_9001

What Easter eggs are in the new versions of excel? I loved the 3D thing back when windows 98 was a thinng


MicrosoftExcelTeam

We had to stop doing that. The fun police got us.

-Matty

adequateatbestt

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.


MicrosoftExcelTeam

You can use ALT+E+I+S+Enter, which maps to the previous toolbars (Edit, Fill, Series)

-Eric (Excel)

[deleted]

[deleted]


MicrosoftExcelTeam

Not sure, but I did find some conversations about this topic some SQL forums.

https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=193945

Useful?

ehansalytics

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.


MicrosoftExcelTeam

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,

-Howie

vba7

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?


MicrosoftExcelTeam

These are great observations, and we will take a closer look. You may want to share these on Excel.UserVoice.com. There's a related suggestion there you may want to vote for, which is Show functions in English. -Steve

HeartyBeast

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’


MicrosoftExcelTeam

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)

goldenporsche

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:

  1. Why is this happening?
  2. Without a script, how can I more efficiently mass-delete?

Starting over is not an option. This issue is happening with our templates.


MicrosoftExcelTeam

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

nohpex

I'm super disappointed that I'm on mobile right now.

  1. Is there a way to set a rule so when a cell has a manual entry vs a formula it'll change colors?

  2. When every other row is shaded, is there a way to have it auto fix after deleting a row?

  3. What is Xlookup, and how is it different from Vlookup?

  4. Why does the window slow down/spike CPU usage when your mouse polling rate is high?


MicrosoftExcelTeam

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)

cyberbooster

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?


MicrosoftExcelTeam

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

Pomerantz

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)


MicrosoftExcelTeam

You might try forms.microsoft.com which would allow to send a form with a field for each applicant to each reviewer and the data is all collected into an excel file in onedrive.

-Matty

vba7

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???


MicrosoftExcelTeam

This is great feedback! We use https://excel.uservoice.com/ to gather input on feature requests. Concerning the third point (bug for refresh multiple), it works well in my version - there is an option in the app to send feedback so we can have the details of your app.

- David M

vba7

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?


MicrosoftExcelTeam

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

TheInnsanity

How deep has the rabbit hole gone for "spreadsheets about spreadsheets about spreadsheets abou-"?


MicrosoftExcelTeam

Calculating.....................................................................................................................................................................................................................................................................................................................................................................................................................................................................................42

gruntothesmitey

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...


MicrosoftExcelTeam

You could open the CSV file in Excel Online (it will convert the file) - then you can edit the column widths.
-Tom

dhavalcoholic

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?


MicrosoftExcelTeam

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

AggressiveToken

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?


MicrosoftExcelTeam

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]

ChalkbotPm

Are there any unknown easter eggs? What is the best easter egg?


beyphy

I've been playing around with the javascript API and wrote some code to see what I could do with it. I noticed that some properties returned arrays (e.g. values) while others returned a static value (e.g. address). Has there been any thought to whether you'll implement properties like values in general? It would be nice to have a property like addresses returned an array I could pair with the value for example. Doing this, I could iterate through the values array, for a particular for and column, and find the address for a row and column. I know that I can do it without it. But I currently have to iterate through the values array, load the address properties for every value I want, and then do another context.sync() call. It seems like a lot of extra and unnecessary steps.


MicrosoftExcelTeam

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.

--Keyur

[deleted]

[deleted]


MicrosoftExcelTeam

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

AmphibiousWarFrogs

Does your team have any development in place to fix the quality of exported images?

Original on top (using Windows+Shift+S) vs JPG on the bottom

JPG

BMP

I find it kind of frustrating that the crispness of the text is immediately impacted.


MicrosoftExcelTeam

Try Copy as Picture / As shown on screen / Bitmap. Is this better? -- Alex

AMWJ

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.


MicrosoftExcelTeam

We are considering things in this space. I'd recommend you describe any specifics you want on https://excel.uservoice.com

- Josh

beyphy

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?


MicrosoftExcelTeam

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

-- Keyur

beyphy

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?


MicrosoftExcelTeam

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.

[Joe McDaid]

bugapest

Why can't I undo renaming my sheets in the Excel web version like I can on my iPhone? How can I enable that?


MicrosoftExcelTeam

Great suggestion. We are aware of it but it's somewhat complicated from the technological perspective. We are looking into it but in a meantime please vote for this idea on Excel User Voice: http://excel.uservoice.com -Avital.

Card image cap
MicrosoftExcelTeam