Hello from the Microsoft Excel team! We are the team that designs, implements, and tests Excel across each of our platforms; e.g. Windows desktop, Windows mobile, Mac, iOS, Android, and the Web. We have a great group of people from the Excel team with deep product experience ready to answer your questions. We did this a year ago and we are excited to be back. We'll focus on answering questions that pertain to Excel usage, its various platforms, and the Excel team. There may be questions that come up around bugs, feature requests, what’s new, training & resources. Here are some quick links that may be useful to you.

Home page for all Excel resources and feature requests: https://techcommunity.microsoft.com/t5/Excel/ct-p/Excel_Cat

Excel Support for Bugs: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel?auth=1Excel Support

We'll start answering questions at 9:00 AM PDT and continue until 11:00 AM PDT.

This post can be verified here: https://twitter.com/msexcel/status/797450130853134336

After this AMA, you may have other help related questions that come up. You can still ask these normal Excel questions in the https://www.reddit.com/r/excel subreddit.

10:45 AM - The questions so far have been great. Please keep them coming. Our session ends in an about 15 minutes.

11:00 AM - We are wrapping up the Excel AMA. We'll stick around for a few more minutes to finish some answers. Thanks for all of the great questions.

Comments: 712 • Responses: 90  • Date: 

DamerFlinn147 karma

Do you intend on changing graphs so they do not automatically truncate the y-axis?

If not, what is the reasoning behind this?

JonPeltier47 karma

PMFJI but this is related to the data being charted. If the minimum is more than 5/6 of the maximum, then the Y axis will not start at zero. For a line or scatter chart, this is fine. For a bar or area chart it can be very misleading. I agree that it would be nice if the bar chart behavior were different, but because of the data, it's pretty obvious when you're making a chart that you need to force the axis to start at zero.

MicrosoftExcelTeam44 karma

Thanks Jon! We are looking at updating this to have better defaults for future versions MSFT - Leif

DamerFlinn19 karma

As a follow-up to this: are there any plans on getting rid of ineffective visuals such as 3D pie charts, in line with Stephen Few's thoughts on the matter?

Is there a data visualization sub-team as part of the wider MS Excel development thing?

Thanks a bunch for answering, Excel has given me a lot in my professional life and I hope you never stop improving it!

MicrosoftExcelTeam24 karma

Hi Damer Yes, there is a Data Viz team in the greater Excel. We create the charts for all Office apps. In regards to 3D charts, we certainly support the views from Few and others on this topic. For instance in the Recommended Chart features you will never ever see a 3D chart. We are looking at ways in the future to minimize the chances for the end user to pick these charts. MSFT - Leif

MicrosoftExcelTeam4 karma

Could you give some more details information on what you are referring to? MSFT - Leif

setsomethingablaze91 karma

What is your favourite feature which the average Excel user wouldn't necessarily know about?

MicrosoftExcelTeam145 karma

Well, the average Excel users do not know about PivotTables. And that is by far my favorite feature in Excel. MSFT-Leif

Noytal16 karma

I work in data analysis and use pivot tables every day. They are great, but for ONE feature; "Summarize Values By" cannot be applied to all values. So you have to right click, find the menu, and select "SUM" or "GROUP" or whatever for each column in Values.

There should be an option to set everything in "Values" to the same summarization. If there is already an option to do this then please, I will give you my money for it.

MicrosoftExcelTeam26 karma

Hi Noytal,

Thanks for the feedback. You can do this with Power Pivot ("Summarize By" in Advanced). For non-data model based pivot tables, do add an item to https://excel.uservoice.com, we look forward to having the community vote on this!

thanks, ash [MSFT]

MicrosoftExcelTeam127 karma

Ctrl-` switches to formula view and back. Love it! :-)

-- Alex [MSFT]

MicrosoftExcelTeam44 karma

Actually use this Excel Top 10 tricks file and you will see some useful features which many users are not aware of. https://docs.com/ExcelDemos/6781/excel-10-tricks -Sangeeta [MSFT]

MicrosoftExcelTeam42 karma

Flash Fill for me - https://support.office.com/en-us/article/Turn-Flash-Fill-on-3f9bcf1e-db93-4890-94a0-1578341f73f7

My most favorite trick on it is that it can actually be activated by Ctrl+E, many people don't know that. ;)

-Jeff Zhang (MSFT)

MicrosoftExcelTeam16 karma

I love the Table feature. Most of the time I work with simple tables of information, and Tables makes it easy to manage (format, manipulate, write formulas against, etc.) compared to a normal cell range. Try it out! Just select your tabular data and click Insert->Table (or press CTRL+T). -Joe [MSFT]

MicrosoftExcelTeam16 karma

For Excel on Android, my favorite feature would be flick gesture. select a cell, touch the right handle and flick! It does the touch equivalent of Ctrl+Down! - Sanjay [MSFT]

heliotrope3n79 karma

How often does the team use or hear the "I excel at this" joke?

MicrosoftExcelTeam220 karma

For us it's not a joke, it's a way of life! :-D

http://i.imgur.com/G3a68BG.jpg

-- Alex [MSFT]

epicmindwarp25 karma

Er, where can I buy that?

turbodb58 karma

If you ever visit Redmond, you can pick one up in our company store - building 92 :-)

Cheers, Dan [MS XL]

ViperSRT3g6 karma

No online ordering methods? :P

baseCase00762 karma

Power User / VBA Developer here. I'm going to be a little direct, because while I love Excel, I've definitely seen it's warts.

Four questions:

  1. Can you/someone fix the Ctrl-Break bug in Excel? I don't run into it often, but when I do, I am surprised that this isn't a priority. It's the second most WTF thing in Excel. http://stackoverflow.com/a/5823507/2886869

  2. The most WTF thing in Excel is the Data Model. I've had Pivot Tables quit on me, saying that the Data Model is corrupt, with no additional tooling, or help, and I've had to rebuild entire spreadsheets. I really like the idea of ListObjects and Tables, but if they randomly break, I can't really put them in my spreadsheets until the tooling around fixing them is better.

  3. Can we update the VB Editor to, like, 2005? It hasn't changed since Office 97 and there are things that I really would like IE code folding, regex search inside the VBE, Eclipse's automatic reformatting of code, VIM bindings, etc, etc. Can we take SOME of the Visual Studio Code stuff and port it?

  4. If we are getting Javascript in Office, that's great, then can you pretty please heavily promote D3 as the visualization library of choice, complete with some training to integrate both?

MicrosoftExcelTeam40 karma

Great post and series of questions. We want to take the time to answer it well and will do so over the next couple of days (running out of time in the room and we have to go back to our days jobs now). Thanks for the terrific questions!

-Charlie [MSFT Excel Team]

phd_dude57 karma

Can you add a way to copy and past formulas without it updating the referencing? Also, a way to "transpose" formulas without updating referencing. Currently, I have to "Find-Replace" all the "=" signs to ..., then paste it somewhere else (or transpose) and then "Find-Replace" all of the "..." with "=" again. This would have saved me days of time in my graduate work.

MicrosoftExcelTeam41 karma

That's a great suggestion! You should post it as a feature suggestion to our user voice site: http://excel.uservoice.com

For pasting fomulas without adjusting, one little workaround that might help you is to do this for small-ish ranges is: 1. Press Ctrl+` (control back-tick) - this changes the sheet to display formulas instead of values 2. Copy, paste into notepad 3. Copy from notepad and paste back into Excel 4. Ctrl+` to switch back to standard view

Jim [MSFT]

psymonprime40 karma

Will there ever be a VLOOKUPS?

MicrosoftExcelTeam25 karma

Cool suggestion!

Sounds a lot like this: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10552227-vlookup-function-for-multiple-criteria

Is that what you're thinking of? If so, go vote for it!

-Charlie [MSFT Excel Team]

btbrian40 karma

I think I speak on behalf of all accountants everywhere when I say, thank you for Excel.

Where do you see Excel going from here? Are there any "game-changing" new features on the horizon, or is the focus going to instead be to continue to polish the existing product?

MicrosoftExcelTeam7 karma

What "game changing" new feature would you like to see?

diegojones438 karma

I'm late, but is there any plan to make it so a highlighted range would stay highlighted when switching between screen? For example, I highlight a column and then go to a screen where I need to enter data. My highlight no longer shows. It would be pretty nice for data entry and auditing.

MicrosoftExcelTeam13 karma

Interesting idea. By highlight, do you mean the cells that you selected with your keyboard/mouse? Feel free to add your request to our uservoice site: https://excel.uservoice.com/ -Joe [MSFT]

MicrosoftExcelTeam3 karma

Can you explain your scenario a bit more? Are you selecting a range or highlighting through a specific operation - Sanjay [MSFT]

ViperSRT3g29 karma

I've got two questions from the /r/excel sub:

  • Will VBA ever be replaced with VB.NET? (Or the .NET Framework?)

  • Will other scripting languages be incorporated into the Office Suite?

MicrosoftExcelTeam7 karma

You should explore the JavaScript and other samples on http://dev.office.com !

yawningcat25 karma

Having to make the decision between 32bit vs 64bit is a HUGE roadblock to convincing people that you can use Excel for medium sized data and that using the DataModel/DAX is a big productivity gain. Using 32bit excel, you run into memory errors that to the uninformed user make like the product is shit. In (some/many/most?) corporate environments only 32bit is provided because it removes the complexity of people having to deal with the decision of picking 32bit/64bit add-ins.

So the question is, can you/will you/when will you get rid of 32bit Office and have only 64bit office which plays well with 32bit add-ins?

turbodb13 karma

We totally understand the pain here, and we're constantly evaluating how to best serve our user base. We've recently enabled "Large Address Awareness" for 32-bit Excel 2016, so that it can work with additional memory, even on 32-bit installations.

We also work with lots of customers on moving to 64-bit (because of large data scenarios, etc.) as well as with add-in developers to move critical add-ins to 64-bit (to reduce the comapt issues for users moving to 64-bit Office).

For the DataModel specifically, we're also looking at creative ways to improve the memory usage and stability of the integration with XL.

Cheers, Dan [MS XL]

yawningcat2 karma

The "Large Address Awareness" is great..... ...but what's the holdup on just getting rid of 32bit?

MicrosoftExcelTeam13 karma

On the technical side, marshalling between 32 and 64 bits to support the existing add-ins would introduce a significant performance cost. Which means we'll have to support 32 bits for the customers who need their add-ins to be as fast as in older versions. So, back to square one.

-- Alex [MSFT]

OpenCap24 karma

Let's be real here:

Does the Microsoft Excel team prefer waffles or pancakes? [serious]

MicrosoftExcelTeam114 karma

Waffles are superior in every way: crisper, lots of wells to hold the syrup, and they look like a spreadsheet. -Stacy [MSFT]

MicrosoftExcelTeam28 karma

Well, which one more closely resembles a spreadsheet? ;) -Joe [MSFT]

MicrosoftExcelTeam12 karma

Why choose? -Jen [MSFT]

MicrosoftExcelTeam11 karma

Personally, I like pancakes, but what do you like?!

Do Excel users prefer pancakes or waffles?

Jim [MSFT]

MicrosoftExcelTeam8 karma

We are a diverse bunch so I don't think there's one answer, but for me, it's pancakes!
--Yigal

Coffeh21 karma

Is there a keyboard shurtcut to past without text style? Thus inhereting the one in the cell?

Apolgize if the nomenclature is improper due to havign never used excel in english.

MicrosoftExcelTeam33 karma

You can also do CTRL V and then CTRL V again. (CTRL V twice) Cheers, -Dave L [MSFT]

ProfessorExcel15 karma

If I switch my computers region from "English (US)" to "German (Germany)" (where I'm located), it takes 5,3x the time for calculating. Even worse when choosing Hindi: It needs app. 33x the time for calculating (here is the full report: http://professor-excel.com/performance-excel-study/) What is the reason? Are you going to solve this?

MicrosoftExcelTeam28 karma

A better way of looking at this performance difference is that lookups in English are faster :-) The reason is that we can short-circuit English to byte comparison. More complex scripts require involving more heavy Unicode machinery.

-- Alex [MSFT]

mattreyu12 karma

How does Excel decide to change things based on the cell values? For example, I regularly handle surveys and one field uses text ranges (i.e. 0-2, 3-4) but it automatically converts it to a date, then when I change it to a text field, makes it a 5 digit number instead of the original data?

MicrosoftExcelTeam7 karma

When a user first types a formula or value into a cell, Excel attempts to parse the string using a large set of number formats including dates, scientific notation, currency and more. Because a string like 3-4 matches a date format (such as March 4th or 3rd of April) we commit the value as a date. When you convert the formatting back into text, we show the date value as a string. (Times and Dates are stored in Excel as a floating point number of days since an epoch)

-Nathan [MSFT]

oldschoolcool9 karma

"1-2" should be prioritized to a string of "1-2" and not "JAN-02"

MicrosoftExcelTeam11 karma

Not a solution, but a workaround: prepend ' to force Excel treat "1-2" as text.

-- Alex [MSFT]

mattreyu1 karma

As I'm importing a large dataset from a CSV, would it be in my best interest to have the data append an apostrophe to the beginning of all the field values to avoid that?

MicrosoftExcelTeam3 karma

That's probably the easiest solution if your dataset doesn't export directly to a richer format or support data connections from Excel.

-Nathan [MSFT]

Shaner42111 karma

Any plans to extend Microsoft excel or other office products to Linux operating systems? I know it is a longshot but having access to these would be nice.

MicrosoftExcelTeam7 karma

Hey there,

No plans currently in the works. We never say never, but for the foreseeable future, if you want to use Excel on Linux operating systems, it will have to be through a browser.

-Charlie [MSFT Excel Team]

sbach898 karma

Why does CTRL+A work in the search box? Every time I'm searching for something, and I need to do another search, I click in the box, press CTRL+A and start typing a new query, and gets added onto the end of my previous query...

MicrosoftExcelTeam3 karma

That'd be a good suggestion to add to User Voice: http://excel.uservoice.com/

-- Alex [MSFT]

phd_dude8 karma

What is the coolest spreadsheet you have ever seen designed?

MicrosoftExcelTeam20 karma

A workbook that made creative use of circular references to solve the shortest route out of an arbitrary maze. Not particular useful but fun to build :)

-- JoeMcD [MSFT]

MicrosoftExcelTeam10 karma

I'm a big fan of this artist: http://www.psfk.com/2013/05/japanese-prints-excel.html -Andrew [msft]

psymonprime7 karma

Will you be designing an add-in to build Excel Dashboards? I want to be able to use animation (JS) and hover text. I'm currently building my own interface and wanted to know if you were ever going to make it easier for dashboard designers to make their own.

MicrosoftExcelTeam11 karma

If you're looking into a custom dashboard interface you should look into Power BI and Power BI embedded. Power BI has a framework for creating D3/JS based custom visuals and supports a variety of embedding scenarios:

https://powerbi.microsoft.com/en-us/developers/ https://app.powerbi.com/visuals/ https://azure.microsoft.com/en-us/services/power-bi-embedded/

Thanks, Patrick [Power BI]

MicrosoftExcelTeam6 karma

We are currently working with the PowerBI team to facilitate easier migration of Excel workbooks to PowerBI dashboards and vice versa. For example, you can currently publish a data model from Excel to PowerBI. Our longer term plans are still taking shape.

Thanks, Scott [MSFT]

314159265358979323_7 karma

Hi Team,

I spend the vast majority of my waking life working in Excel and I love it. I’ve always had one lingering question though. Working in finance, I have to geometrically link numbers (returns) quite often. The formula is relatively straight forward, something like: =PRODUCT(1+(A2:A4))-1. I understand that the product function is not expecting an array to be passed, so using this formula, #VALUE! Is returned. However, stepping into the cell and executing with ctrl+shift+enter (rather than just enter) ‘forces’ the formula to execute (becoming: {=PRODUCT(1+(A2:A4))-1}) and returns the expected result. My question: why make this a necessary extra step? What is the danger of just having this formula execute with a simple ‘enter’ command? You have no idea how many people I’ve had to explain this to over the years…

Thanks so much!

MicrosoftExcelTeam8 karma

The reason =PRODUCT(1+(A2:A4))-1 behaves like this is because Excel performs "implicit intersection" on the range A2:A4. It does this because PRODUCT expects a single value as its first argument - so Excel attempts to narrow the range you provided to a single cell.

How does this work? Excel chooses the cell in the same row as your formula. So if you input your formula in Row 2 through 4 the formula returns a result, while in all other rows it will error with #VALUE. CTRL-SHIFT-ENTER suppresses this narrowing behavior and you get your desired result.

MrExcel has a video on the implicit intersection: https://learnmrexcel.wordpress.com/2012/08/06/learn-excel-2010-named-range-and-implicit-intersection-podcast-1579/

  • Joe McD [MSFT]

MicrosoftExcelTeam7 karma

Formulas that are entered with Ctrl-Shift-Enter are called "array formulas". Here is a handy primer explaining what they are for and how to use them: https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7D94A64E-3FF3-4686-9372-ECFD5CAA57C7

-- Alex [MSFT]

jsach36 karma

Why did office become a subscription based program?

Did any of you meet Bill Gates?

Did Bill give any orders on what to add, make, etc?

MicrosoftExcelTeam9 karma

Subscription: we wanted to offer a new model of buying Office that wasn't about buying new software when you bought a new computer so that we could deliver functionality outside of the old "box software" model, get new features out to folks quicker than traditional/slow upgrade cycles, and help users with service-based offerings such as Office365 and even things like the new Designer feature in PowerPoint (that helps you make beautiful looking slides using cloud services).

Meeting Bill Gates: Yup! I've met Bill a couple of times. The first, and most memorable, was at his house when I was an intern. They stopped doing that years ago, but he was a very gracious and patient guy with all the ridiculous questions that us interns were throwing at him.

Bill's "orders": Oh yeah, over the years Bill has weighed in on what we should and shouldn't do for every single version. I still remember contributing to my first BillG review. Me and Dan (one of the other folks commenting here) built this crazy prototype and Bill (rightly) ripped it apart. Good times.

-Charlie [MSFT Excel Team]

MicrosoftExcelTeam6 karma

We'll continue to have more of the value we provide to people delivered via intelligent services (like Map Charts, PPT designer, file storage, collaboration, etc.). We also update the product itself monthly, and it's excited getting to push those new features out to people on a regular basis.

Yes, a number of us have had the chance to meet Bill and present our plans to him. It's always exciting. He's definitely a big fan of Excel, and has some great opinions on the investments we're making.

-Brian [MSFT]

SuperFreakonomics6 karma

Why can't we still not open two workbooks with the same name at the same time?

MicrosoftExcelTeam17 karma

Allowing two workbooks with the same name would make entering formulas that reference them quite confusing:

=[Book]Sheet!$A$1

Which one did the user mean?

-- Alex [MSFT]

ShoutOutTo_Caboose5 karma

Why did you name it Excel? Or did you guys name it at all? I mean, it is a spreadsheet program...

MicrosoftExcelTeam12 karma

Here we use "XL", which is handy, since it's easier to fit on a license plate than "WORD" or "PPT" :-D

-- Alex [MSFT]

P.S.: Mine is "XLRULEZ", naturally.

MicrosoftExcelTeam12 karma

Obviously for the endless supply of "I excel at this" jokes. :) -Joe [MSFT]

MicrosoftExcelTeam19 karma

WORD! -Sanjay[MSFT]

Professor_Fool3 karma

Is there anything that you thought would be a cool feature to add then it turns out to be the most used and important one?

MicrosoftExcelTeam6 karma

Not sure if you used the iPhone version of Excel at all, we added a full screen view feature there, which takes you to full screen on the phone to view only the current sheet content. It's very helpful for users to navigate the content on a small screen. I'm not sure if the most used, but it's absolutely a popular one on the phone version.

  • Jeff Zhang (MSFT)

Mehnard3 karma

Who did the flight simulator?

MicrosoftExcelTeam4 karma

Me

MicrosoftExcelTeam4 karma

No Me

MicrosoftExcelTeam6 karma

Nobody here did - there was a source repository mix-up and Flight Sim folks checked it in by accident

rfelds3 karma

When I am using Excel formulas such as vlookup or index match (often combined with offset and/or Indirect), there will commonly be more than one instance of a result that I am looking for. Sometimes, I want to pick up the first instance, but other times I would like to pick up the 2nd, 3rd, 4th, nth instance. For example, let's say I am using a look-up function on a table with cities down the left, and sports team from those cities on the right. In this example, I want to know all sports teams associated with Chicago, not just the first or last one. I know there is a way to do this using an Array formula, which I have used. However, Array formulas are brutal to work with, especially when applying them to a big data set. Is there another workaround that allows you to specify the instance (2nd, 3rd, nth) it occurs without using an array?

MicrosoftExcelTeam5 karma

Since you're already making it work with array formulas, I assume you are already aware of SMALL and LARGE to get the Nth smallest or largest item from an array. When I'm filtering a range using INDEX, I often find an extra disambiguation column (ROW()/2000000) to help for sorting and extracting multiple entries.

I don't know of any easier way to do this right now, but feel free to vote at uservoice. https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10633782-vlookup-for-2nd-3rd-etc-occurrance

-Nathan [MSFT]

iphoneluver3 karma

Hey Excel team!! As a Business major and stats minor, I use Excel daily and have a few questions.

1) What would you say is the most underutilized tool/data pack? (specifically that could be useful in business or stats)

2) How many functions can you "string" together in a single cell before excel crashes, I've written some pretty long strings but excel has spit out the answer every time

3) How come when Im working with the same data pack and functions/spreadsheets as my professors I can never get my sheets to work nearly as well as them?

MicrosoftExcelTeam3 karma

  1. Some of my favorite tools within Excel are Tables, PivotTables, and Named Ranges. If you are familiar with these, you can do some pretty incredible things. Check out the Excel Sudoku Solver blog post To see some pretty great uses of named ranges.

  2. There's no direct limit on the number of functions, but the maximum number of characters in a cell is 32k and the maximum nesting level of functions is 64. Check out Excel limits and specifications for more information.

  3. I'm curious what kinds of differences you're seeing here. If you're seeing calculation or performance issues, definitely post them to https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel?auth=1Excel.

Jim [MSFT]

johhnytexas3 karma

Do you know of any free Excel training programs? I use it everyday at work - pivot tables/vlookup are my favorite features but I still feel like a total noob using it.

MicrosoftExcelTeam5 karma

Great question! Please go to the Excel Tech Community and in the top pinned post or in the resources section, you will see link to all Free training programs for Basic, Intermediate & Advanced Users. https://techcommunity.microsoft.com/t5/Excel/ct-p/Excel_Cat -Sangeeta [MSFT]

ripoffcandydate2 karma

Any plans to build some of the features that ThinkCell offers?

BTW, I love O365 and Office 2016.

MicrosoftExcelTeam2 karma

ThinkCell is a great add-on for Office. There is a Uservoice for additions to the Waterfall chart here: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/11120742-include-thinkcell-add-on-waterfall-chart-capabilit If there are other items you would like to see, please add them to user voice. MSFT-Leif

Excel10x2 karma

Could you suggest the best programming that helps to automate tasks/ report for the online version of Excel ?

MicrosoftExcelTeam3 karma

Check out the REST APIs that we expose as part of the Microsoft Graph: http://dev.office.com/excel/rest

-Brian [MSFT]

mdoor112342 karma

What language is MS Excel written in?

MicrosoftExcelTeam6 karma

Desktop client is mostly C++ (with some older parts that look suspiciously like vanilla C), plus a few bits of Assembly for critical parts related to the grid operation.

-- Alex [MSFT]

JanetYellensFuckboy2 karma

First of all: as a finance major, I love you guys and your product. You're wizards.

As far as I can tell, Excel has seemingly endless depth despite being easy to use. Where do you go from here? How are you working to improve it?

MicrosoftExcelTeam4 karma

Thank you! We do have a lot of wizards around here (I'm level 34, personally). ;-)

As far as where we go next, we're always working on adding new functionality to Excel and always thinking about new directions for the product. We're listening a lot to the user feedback we get from the Excel Community and always trying to make the producteasier to use.

For specifics, we're planning to ██████████████ and then we're going to make a huge splash with ███████████████████ and ████████████████. I think you'll really like ██████████████████ as well. Hope that helps! ;-P

Jim [MSFT]

MicrosoftExcelTeam3 karma

Great to hear that you love the product. We continue to improve the product in terms of reliability and performance. We have millions and millions users worldwide and it is important to keep Excel as a reliable experience for all of them. See this blog post on some of the themes around Intelligent Cloud Services and Collaboration which we will working towards.. https://blogs.office.com/2016/09/26/office-365-news-in-september-at-ignite-intelligence-security-collaboration-and-more/ -Sangeeta [MSFT}

MicrosoftExcelTeam2 karma

Check out our What's new page that lets you know the how we are improving Excel : https://support.office.com/en-us/article/What-s-new-and-improved-in-Office-2016-for-Office-365-95c8d81d-08ba-42c1-914f-bca4603e1426 -Sanjay [MSFT]

bigbigtea2 karma

Hi guys! Can I ask a tech question here please?

Why is so hard to count a set of different things, then take an average of those things from associated cells? For example an average of this years monthly sales.

I'll explain: Say I have a list of this years months as Jan-Nov. Each month appears numerous times. For example January appears 11 times, February appears 18 times and so on. Each of those month entries has an associated dollar value. These are actually recorded from my sales work. What I'd like is to figure the average dollar amount across those moths.

Far as I can tell, the way to do that is like this:

=SUM(H3:H129)/SUM(IF(FREQUENCY(MATCH(E3:E35,E3:E35,0),MATCH(E3:E35,E3:E35,0))>0,1))

Column E is the Months, Column H is the dollar values. One thing I notice is that it when I change say, "H3:H149" to H3:H1000" across all of the H's, it returns an error. This is combated by only putting in for the cells that contai data.

Is there really nothing simpler to do this? Thanks in advance!

MicrosoftExcelTeam3 karma

If you create a PivotTable, one of the possible aggregations (summarize values as) is average. You can put the dollars column on the values axis and the months on the rows axis, then change the dollars summarization to average.

  • Carl [MSFT]

BlueD2O1 karma

Convert text to a value formula creation help request. We have a sports pool that I get submissions from 3 dozen people weekly. He with the most points a weekend close wins. Right now I need to go through and manually check each person’s submission after the weekend is over, and tally them up. I make mistakes sometimes, and they get pointed out (think pitchforks coming).
Is there a formula method that allows, me to do the following? If I create a column of controls, the correct picks, to compare text in the submissions to set correct answers to fill green, and incorrect to fill red? Also is there a way in a hidden column, to generate a 1 or 0 value, so that the correct cells can be summed for a tally under each submission for each individual?

MicrosoftExcelTeam3 karma

For formatting the answers as green or red, check out Conditional Formatting. I'd also take a look at the COUNTIF and SUMIF formulas. Those should help you tally up the results!

One other feature that may help you is Data Validation. You can use that to enforce that the people filling out submissions have results that all look the same.

-Scott [MSFT]

MicrosoftExcelTeam3 karma

If you still need to get 0 and 1 values in cells, you can use something like =($A1=E1)+0 to return 1 or 0 depending on if $A1 matches E1.

-Nathan [MSFT]

teddylumpkinz1 karma

What updates and features do you plan on implementing in excel?

MicrosoftExcelTeam2 karma

You can also check our What's new page: https://support.office.com/en-us/article/What-s-new-in-Excel-2016-for-Windows-5fdb9208-ff33-45b6-9e08-1f5cdb3a6c73

We update that whenever something new is released.

Smitty [MSFT]

MicrosoftExcelTeam2 karma

Also we share many of our recent updates, what's new and discussions around in the Excel TechCommunity at https://techcommunity.microsoft.com/t5/Excel/ct-p/Excel_Cat -Sangeeta [MSFT]

how2excel1 karma

FTLOE! Why is it not possible to create a histogram straight out of a pivottable?

jfjeschke1 karma

1. Does the Excel team have any plans for integrating Cortana with Excel?
2. If so, what kind of ideas are you looking at?

Thanks for doing the AMA!

MicrosoftExcelTeam2 karma

Cortana in Excel - that sounds pretty cool :)

I can tell you that we're generally exploring this area and we partner with folks in Microsoft research on a range of new technologies, including new ideas in ML, AI, and natural language.

There's a ton of scenarios that we could look at, so I'd love to get your suggestions on specific stuff you'd like to see us make Cortana do in Excel by using user voice: https://excel.uservoice.com/.

  • Charlie [MSFT Excel Team]

inowhazareddit1 karma

Nearly 95% of the time I am in excel my hands are on the keyboard, but I am curious how the Surface Dial interacts with the application and couldn't find this information online. Could you or someone familiar with the Dial speak to this?

MicrosoftExcelTeam2 karma

The Surface Dial in Excel uses the common Windows Dial Features of Scroll and Zoom-in Excel. With 2016 we also added Ink features in Excel ( as well as rest of Office), with the Surface Dial we support the Ink Replay feature as well.

https://blogs.office.com/2016/08/30/new-to-office-365-in-august-the-continued-evolution-of-inking-and-more/ MSFT - Leif

LoyalServantOfBRD1 karma

Any plans to add a shortcut to force selected cells to calculate as arrays?

It's a pain in the ass to go and ctrl+shift+enter 70 different cells.

MicrosoftExcelTeam2 karma

Here are a few things to try, all of which will persist array formula property:

  • Drag-fill the formula (use $ to lock row and column references, if needed)
  • Use fill-down (Ctrl-D) or fill-right (Ctrl-R)
  • Copy formula, select destination range, paste

-- Alex [MSFT]

NullableThought1 karma

Oh man, I've been looking forward to this all week! Unfortunately I don't need to use Excel at work that often, so most of my experience has been for personal use.

Q1 What's the most memorable/off-the-wall use of Excel that you've run into?

Q2 Did most of you have a great desire to work specifically on the Excel team beforehand? Or did most of you more or less kinda fall into the team?

Q3 Do you find yourself trying to find excuses to make spreadsheets for personal use more often now that you're on the team?

MicrosoftExcelTeam2 karma

Q1 - I wrote a tower defense game in Excel a couple years ago. I've also found that customers always come up with the most creative uses of Excel.

Q2 - I didn't really have a preference when I went into the interview for my internship, but I did choose Excel over the SmartArt team and this team has been awesome.

Q3 - Are they really excuses? I use it for everything from mini-golf scorecards to white-elephant party gift tracking.

-Nathan [MSFT]

MicrosoftExcelTeam1 karma

For Q1, there are a number of very creative uses of Excel. Here is one of them https://www.youtube.com/watch?v=Vs2XUffK-1w.

Thanks, Scott [MSFT]

MicrosoftExcelTeam1 karma

Q1 - The ASCII AC/DC video in Excel (https://www.youtube.com/watch?v=Vs2XUffK-1w) Q2 - I worked with Excel for years, so it's a fantastic bonus to work with the Excel team (note that I'm a writer for Excel topics on support.office.com, which is a different group). Q3 - Nope, I still use Excel for everything. Smitty [MSFT]

MicrosoftExcelTeam1 karma

Q1 - First thing that comes to mind is artwork done in Excel. Search for it online - lots of examples! Pretty amazing what some people have been able to achieve.

Q2 - Microsoft was my first job out of college, and I landed on the Excel team. In my college life I had not really used Excel, but knew of it. Now I can't imagine life without it!

Q3 - I don't make excuses - it just flows naturally for me. I love data. My friends joke that my whole life is planned out in some secret spreadsheet (which is not too far from the truth...) -Joe [MSFT]

how2excel1 karma

Please add comments possibility to formulas, like in PowerPivot?

//would be great :)

MicrosoftExcelTeam3 karma

You can use the N function to add comments in a formula like this: =SUM(A2:A31)+N("This is the sum of current orders") Smitty [MSFT]

yawningcat1 karma

Why don't the new Chart Types (Box and Waterfall for example) work with the DataModel? Will you fix that? Will the d3.js visualizations from PowerBI make it to Excel?

MicrosoftExcelTeam2 karma

Hi The support for datamodel and PivotTables is something that is in our future plans. Here is the user voice entry for this as well, if you want to add more votes to this . https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9306765-allow-new-chart-types-treemap-sunburst-etc-to In regards to d3.js visualization in Excel, that is something that we are evaluating, but not plans for now. MSFT - Leif

yawningcat1 karma

When will DAX(PowerPivot+DataModel) and M(PowerQuery/Get&Transform) make their way to macOS Excel?

MicrosoftExcelTeam2 karma

Getting the PowerPivot and PowerQuery functionality is something that we would like to add in the future. It also is one of the most asked request on User Voice. https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/8995483-add-support-for-power-components-power-pivot-que MSFT - Leif

tjen1 karma

5 years from now, what will the main differences be in excel compared to today?

MicrosoftExcelTeam2 karma

There will be many differences in the Excel in 5 years from now. See this blog post on some of the themes around Intelligent Cloud Services and Collaboration which we will working towards.. https://blogs.office.com/2016/09/26/office-365-news-in-september-at-ignite-intelligence-security-collaboration-and-more/ -Sangeeta [MSFT]

Only4DNDandCigars1 karma

How do you feel on v-lookup vs match-index? I am huge proponent of the latter, but I dont know how to argue why.

MicrosoftExcelTeam3 karma

VLOOKUP has been around since the dawn of spreadsheeting, is easier to author, and is widely used/understood. INDEX MATCH is more flexible, less prone to error, and can be far faster if you break out the MATCH into its own cell and use its result in multiple INDEX functions.

As a previous financial modeler, I almost exclusively use INDEX MATCH.

--JoeMcD [MSFT]

TheApelsin1 karma

Could you tell us about a feature that has been worked on in the past that was abandoned or didn't make it into Excel even though it was a good idea? What made it good? What fell through?

MicrosoftExcelTeam2 karma

One feature that we shipped in Excel 97, but turned off by default shortly thereafter, and eventually removed in Excel 2007 was Natural Language Formulas. This feature enabled users to write formulas using nearby cell contents as implicit names. This was great in that it made formulas easier, but it had a big downside that it meant re-entering formulas could give different results if the nearby contents had changed.

-Nathan [MSFT]

illfishhead1 karma

what is the Excel team's dream sandwich?

MicrosoftExcelTeam2 karma

I enjoy a Big Macro from McDonalds. (Thanks to Jordan Goldmeier for that whopper of a joke). -Joe [MSFT]

AmansLivearth1 karma

Why did you change the separator from ; to , ?

MicrosoftExcelTeam2 karma

We use the list separator as defined for your locale. Please check what locale are you using. The definition is available here: http://demo.icu-project.org/icu-bin/locexp -Sanjay[MSFT]