38 votesAccepting Votes · 7 comments · Excel for Windows (Desktop Application) » Charting, Mapping and Visualizations · Flag idea as inappropriate… · Admin →
Two years later and Excel scatter plots are still absolutely hopeless. I foolishly thought just now I could do some analysis using PowerQuery, create a scatter plot and some Slicers and have the scatter plot update as the data updated. But of course, I forgot, in Excel you still have to manually sort the data and then manually select the ranges you want for each category (series) in order for the scatter plot to make any sense!!
And you still can't draw a scatter plot from a pivot table either. Unbelievable.
What's the next thing you want to do after plotting some data on a scatter chart? Very often the answer is 'draw some lines with certain equations over it'.
Great to see this is under review :)
My opinion only, but I think MS should aim to make the PowerX tools suitable for 'Data Science Lite' - it wouldn't require a great many additions to what currently exists. This one is a start.
Btw Scott that SuperUser workaround doesn't work if you don't know how many unique categories your data set has.
35 votesinfo needed · 28 comments · Excel for Windows (Desktop Application) » PivotTables and Power Pivot · Flag idea as inappropriate… · Admin →
Why is this still tagged as 'Info Needed'?
Good example Dave.
Here's an example from Tableau: https://onlinehelp.tableau.com/current/pro/online/mac/en-us/calculations_aggregation_ex1aggregate.html
Yes this is for a chart but with the way pivot charts and tables work, getting pivot tables to not aggregate data seems like a necessary condition for getting pivot charts to not aggregate data, which is my main interest.
Go down to the disaggregate section and see how easily the data is plotted. If you then add a category measure to it you'll automatically get different series. Though unlike Tableau, I think scatter charts should be disaggregated by default.
My primary interest in this is to then be able to make non-aggregate pivot charts from the pivot table.
Clarke, I just looked at that and it looks like it might help to make a table that meets the OP's criteria (though I can't test as I don't have the right Excel version), however it doesn't help with the related question of making non-aggregate pivotcharts from the table.
I have also made a related request here:
Abolutely agree. It seems Microsoft think the PivotTables/Charts are ONLY for aggregate data, however that really limits the usefulness of their BI solutions. In many use cases (especially science/engineering) it is simply wrong to go looking at aggregates first - you need raw (unaggregated) data usually with scatter plots.
A regular scatter chart is not the answer. Slow to set up. Breaks when the data changes. You can't even get it to group categories by a third column without performing other gymnastics. It's unworkable. This feature needs to be in PivotTables/Charts (and also in PowerBI, for that matter).
Yes the above relies on there only being ONE value for each row by column i.e. a datacube. That's fine - it's a similar approach to the .NET frameworks enumerable Single method: https://msdn.microsoft.com/en-us/library/vstudio/bb155325(v=vs.100).aspx
33 votes10 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
A comment added to my previous comment: You can actually achieve something like what I wrote using the FORMULATEXT formula with the hidden EVALUATE formula that is only available through the Name Manager.
It could still be a lot easier if all of these could be UDFs though.
To clarify my comment 'make functions first class objects' you could have a cell function as follows:
= FormulaAt(reference, [arg1]. [arg2], [arg3]...)
Cell A2: = A3 + A4
Cell B2: = 3 * FormulaAt($A$2, B3, B4)
What made me think of this is that one time I wanted the spreadsheet user to enter a formula into a cell, and I then wanted that formula to be applied to a collection of other data in a protected sheet (without using VBA).
That's fairly niche though, so the suggestion of doing this in the Name Manager or a Function Manager is probably cleaner. Documentation could also be specified.
I agree that it would be great to be able to have simple UDFs without going to xlsm format (which I never want to send to clients if I can avoid it).
Generally speaking could describe this request as 'make functions first class objects' right? Whether a good place to define these is a new function manager, or in the name manager, or even in spreadsheet cells which other cells can point to, I'm not sure.
This would also clear up quite a few of the requests for new formulae on this uservoice site e.g. "Two new IFs", because people could just define their own.
Posted 15 months ago and only 3 votes for something that I wish for literally every day that I use Excel with a large workbook :/
13 votes7 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
A generalisation of the Aggregating (aka. Reducing or Folding) functions would indeed be helpful. We have SumProduct, SumIfs, the aforementioned lack of other aggregating functions, and we have array formula for more general usage though they have their own serious usability limitations.
Ryan's idea of having an enumerated value of Sum/Average/StdDev to supply to formulae is a good one.
While I'm at it, the problem with IF statements in SumProduct or array formulae is that you have to return some kind of null value (e.g. zero or empty string, and sometimes there isn't an appropriate one) when you apply a function, whereas you really want to apply an actual Filter (Where clause) to the array that omits these elements entirely.
8 votes0 comments · Excel for Windows (Desktop Application) » Charting, Mapping and Visualizations · Flag idea as inappropriate… · Admin →
Good feedback. We’ll roll this into our plans for new features that we are considering for a future release. Please continue to vote on this feature idea.
1 vote3 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
Thanks Sarvesh, I had no idea that existed!
3 votes2 comments · Excel for Windows (Desktop Application) » Viewing / Navigating Workbooks · Flag idea as inappropriate… · Admin →
A Back button as found in web browsers would be a more general solution:
123 votes15 comments · Excel for Windows (Desktop Application) » Charting, Mapping and Visualizations · Flag idea as inappropriate… · Admin →
I hope the planned charts cover both tree diagrams (single parent per node) which have subtypes such as decision trees and organisation charts, and network diagrams (n edges per node) which have subtypes including Sankey diagrams and Gantt charts.
I'd love to be able to generate these from data, especially if I can add drill-down functionality to them as per the OP too.
The ability to generate node or tree diagrams based on data (typically some self-joined tables) would be exceptional. If you look around the PowerPivot and Tableau forums there are dozens of attempts to handle and diagram this data, but it's not native and it's not easy.
136 votesAccepting Votes · 15 comments · Excel for Windows (Desktop Application) » Charting, Mapping and Visualizations · Flag idea as inappropriate… · Admin →
This is related to what I posted here - I suggested a different solution though anything to address this type of problem would help.
140 votes15 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
Good suggestion Frederic – thanks for taking the time to post it. We’ll take a look at the F4 loop and including other functionality like this as soon as we can. We’ll be spending more time on the things with more votes – so folks that really want to see this should keep voting it up.
John [MS XL]
If you're making a reference to a named column in a table then surely 90+% of the time you want that to be an absolute reference. I can't see why the default is a relative reference in the first place. It seems inconsistent with the concept of named tables with named columns.
14 votes12 comments · Excel for Windows (Desktop Application) » PivotTables and Power Pivot · Flag idea as inappropriate… · Admin →
This feature may seem kind of arbitrary at first but in thinking about it, I think it's the next logical step for data entry to tables/PowerPivot.
I had just encountered this problem and came here to post the idea, but Kenneth Barber beat me to it :)
For me, I want it through the PowerPivot Linked Table function ('Add to Data Model'). I think these tables are a great idea where the user inputting data is actually adding that into a data model behind the scenes. However the user (especially the less tech savvy) is really going to need the cues provided by a crosstab 'form' to do this right.
The functionality also allows the user to declare pseudo-classes or tuples and then populate a value for each property. E.g.
MaterialClass: ID, Name
Material: ID, Name, MaterialClassID
QuantityType: ID, Name, Units, MaterialClassID
Data Entry begins with user adding records to the MaterialClass and QuantityType tables. Then in the crosstab form they can add Material records where QuantityType is a column label and Material.ID is a row label.
775 votes213 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
Thanks to Graham for starting this conversation. If you would also like Excel to maintain named range references and structured table references in the “applies to” field for Conditional Formatting rules, please add your comments and vote this one up. We will prioritize accordingly.
Steve (MS Excel)
This is the same behaviour as when you try to make a chart series refer to a named range - again it defaults to absolute cells. This is fairly annoying if you expect your named range might change in size.
3 votes1 comment · Excel for Windows (Desktop Application) » Charting, Mapping and Visualizations · Flag idea as inappropriate… · Admin →
Interesting idea. We’ll consider this in the planning phase for the next release. Please continue to vote on this feature idea.
My Excel sheet is a report with lots of charts on it. The charts represent different views of a data set. Say it's a Mine that produces Gold, Copper and Silver. There are different types of Costs and Revenues. There are Constraints, there are material Deliveries, there are material grades and recovery rates. The important point is there are lots of classes of things.
My report has 50 charts on it. I would like to keep the styling consistent between all of these so that Gold is always charted in yellowy orange, Copper in pink, Costs are shades of red etc. I don't think there's any good way to do this in Excel (especially when I can't rely on series being in the same order on every chart).
So I suggest the CSS approach where chart elements can be tagged with IDs or Classes and then those IDs or classes are given style values.
It doesn't have to be the CSS way but something to help alleviate the error-prone tedium of manually dealing with the problem would be nice.
I played with PowerBI a little while ago and it looked like some of this was possible (or maybe I'm thinking of bind colour intensities to data values, which is also a good idea).
16 votesAccepting Votes · 4 comments · Excel for Windows (Desktop Application) » Charting, Mapping and Visualizations · Flag idea as inappropriate… · Admin →
Quote Scott - "If you had the ability to graph and equation; e.g. x^2 + y^2 = 1 would that address your needs?"
I was thinking of posting something along those lines on this site so now I have:
13 votes1 comment · Excel for Windows (Desktop Application) » Charting, Mapping and Visualizations · Flag idea as inappropriate… · Admin →
Thanks for posting this. We’ll definitely consider it in the planning for our next round of features. Please continue voting on this feature request which will increase its likelihood of getting in the product.
I like this idea - it would be extremely powerful.