161–170 of 275 posts
I recently sat down and built something more complicated than simple accounting in a spreadsheet. It's what I considered to be a pretty typical usecase for a non-math related sheet; taking in several tables of data and selectively joining them. You enter an ID, press a button, and it finds all of the data related to that ID and presents it to you. I was horrified to find that even with the supporting scripting capabi…
Earlier quoted context omitted.
> it is very non intuitive to write a formula that retrieves all the rows in another sheet that match this rule You can retrieve an entire range of data with a single formula in either excel or Google sheets. The formula is caller FILTER https://support.microsoft.com/en-us/office/filter-function-f...
That's exactly what I did. Now separate out some columns and perform some additional transformation on that FILTERed data. Can you do it without repeating yourself (duplicating the FILTER statement, or any of the other transformations you need to do, besides just filling down a column). Can you perform these transformations only on the row height of the data, and not have extra rows with broken formulas? I honestly w…
Hey, I work on Excel at Microsoft and wanted to say: if anyone here has any feature requests they want escalated - write them here and I will bring them up. P.S we actually do read all the feedback people leave in the feedback box - it goes mostly straight to the devs.
There's something horribly un-optimized going on when I click and drag some values to a new location. Even when there's no overlap, dragging a tiny number of values, like say 3, ends up hanging for several seconds on my very fast computer.
I remember this also didn't used to happen back ~2011-2013 ish, and then I remember it started happening at some point and hasn't been fixed since.
Hey, I work on Excel at Microsoft and wanted to say: if anyone here has any feature requests they want escalated - write them here and I will bring them up. P.S we actually do read all the feedback people leave in the feedback box - it goes mostly straight to the devs.
Hey, I work on Excel at Microsoft and wanted to say: if anyone here has any feature requests they want escalated - write them here and I will bring them up. P.S we actually do read all the feedback people leave in the feedback box - it goes mostly straight to the devs.
A way to truely, honestly, for the love of god, please, I beg you for mercy, force all pivot tables to fully refresh everything about themselves — their data, caches, retained items, etc.
Better pivot table value formatting: use the formatting from the source data set, let me format multiple value columns at once, or apply formatting from value cells to the value columns themselves.
Please let me hide everything from a pivot table except for value columns. There are many scenarios where I would like to insert two pivot tables right next to each other, then have a third columns that refers to their cells for a calculation. I don’t need any of the other pivot table options to be available to the user. Dynamic array and lambda functions are not a substitute, because they do not cache results, which causes significant performance problems.
A workbook level option to open the workbook in a new process that doesn’t allow interaction with other workbooks. Sometimes, I build computationally intensive standalone workbooks that my users hate to have open, because they degrade performance for all of their other open workbooks. They have to resort to using excel online (or the outlook web preview) to be able to have my workbook open for reference while working on something else.
Freeze(x) or Staticize(x): a function that evaluates once and retains its value. I know a similar effect is possible by enabling iterative calculation, but that feels hacky and I don’t know what else is affected by turning iterative calculation on (the fact that it is disabled by default implies significant consequences).
In Power Query, a way to append the content of a table to another table, on every Refresh All. This would make it much easier to create snapshots and temporal reports. E.g. I want to know what the value of all sales orders as they were reported each day, verses what I can infer from the database today.
I love all the investment in Excel! Are you hiring?
Hey, I work on Excel at Microsoft and wanted to say: if anyone here has any feature requests they want escalated - write them here and I will bring them up. P.S we actually do read all the feedback people leave in the feedback box - it goes mostly straight to the devs.
Ok, besides python support... The thing I'd like more than anything: A better way to edit cell with really long function calls. If nothing else, add color coded parenthesis to the bar at the top and not just in the cell. Like, sometimes you just need some if/else statements... but try to parse and edit even something fairly simple like: =IF(AND(LongExpression > 3, Other_longexpression 5,Other_longexpression Even with…
Expand the bar (or drag the vertical resize handle at its bottom edge), and then you can use alt+enter to insert a newline.
I recently sat down and built something more complicated than simple accounting in a spreadsheet. It's what I considered to be a pretty typical usecase for a non-math related sheet; taking in several tables of data and selectively joining them. You enter an ID, press a button, and it finds all of the data related to that ID and presents it to you. I was horrified to find that even with the supporting scripting capabi…
Hey, I work on Excel at Microsoft and wanted to say: if anyone here has any feature requests they want escalated - write them here and I will bring them up. P.S we actually do read all the feedback people leave in the feedback box - it goes mostly straight to the devs.
Wait really? Well if thats the case, making VLookups be able to use any column as an input or output, rather than being limited to having the input on the left and the output on the right. Ideally I'd be able to have 2 additional parameters, one that would indicate the column where the input value is to be found, and another that would indicated the column from which the output value would come from. I know there are…
Basically
- Put the data to be looked up in a named Excel Table with a short, meaningful name. `data` is a good default name, but something with more meaning is better.
- Put the lookup 'results' in an Excel Table (naming optional but recommended). The output will be one column of the table, with one of the other columns used as input.
- Construct the output formula like `=INDEX(data[[value_column]], MATCH([@[input_column]], data[[lookup_column]], 0))`.
- (Optional) Put all formulas at the far right of the results table, so that you can copy new data into the left side easily without overwriting the formulas.
The MATCH finds the first row in `data` that has the lookup value from `input_column` (in the current table) in the `lookup_column` (in the data table). The INDEX grabs the value from `value_column` of the `data` table in that row.
Using Excel Tables helps by making the formulas more readable, and resistant to change. If new columns are added or removed the formulas continue to work (not true for how most _LOOKUP formulas are written), and the formula gets copied down to new rows as you add them.
You can switch to row lookups if needed (though you can't really use Excel Tables anymore)
if you need dynamic lookups you can specify both row and column as MATCHes in the INDEX formula (and INDEX against the whole `data` table instead of just one column). Something like `=INDEX(data, MATCH([@[input_column]], data[[lookup_column]], 0), MATCH([@[column_name]], data[#Headers], 0))`.
Earlier quoted context omitted.
Not sure what you mean by "power queries", but Google Sheets support SQL queries. Would be easier to see on an example.
PowerQuery. It's a tool built into Excel. It's a GUI that wraps an almost purely-functional DSL designed for ETL and data munging, called the M language. You can either use the GUI or write the code directly. It has first class functions and closures and normies are programming in it. It's great. More people should know about it. Btw it's kind of funny seeing so many HN users, many of whom must be working on software…
Earlier quoted context omitted.
Does this criticism extend to graphing calculators?
No. Spreadsheets allow for easy manipulation of large tables of numbers and immediately see the results. It’s this ability to ‘tweak’ the numbers quickly that makes it so dangerous.
I hate to break it to you, but that applies to everything from bricks to aircraft carriers.