Ask HN: What can't you do in Excel?
One of the startups in the current YC cycle is making a new, more powerful spreadsheet. If there are any Excel power users here, could you please describe anything you'd like to be able to do that you can't currently? Your reward could be to have some very smart programmers working to solve your problem.
200 comments
(1) Optional Fortress like "typing". It's almost always a mistake to add a number representing an amount of dollars to a number referring to feet. And feet can't be added to meters. Heck, it's typically wrong to add gigabytes to megabytes without a conversion (which depends on whether you're talking about disk space or ram).
(2) Many of the complaints mentioned come from the way that a given entity, "the sheet" (or page) is used to both computation and to present multiple computations. Rethinking that is likely to yield huge benefits.
(3) It should be possible to tag computations so they can be more easily used to build other computations. For example, I was recently doing some cost analysis and realized part way through that I'd like to break down the numbers in other ways. Since I was using a sequence of equations in an ordinary programming language, it was easy enough to define appropriate accumulators and pick up the values from the equations, but it would have been a pain with a spreadsheet. And, my solution was too granular.
I'm not expressing (3) very well, but I think that it's a big deal, so feel free to contact me.
(2) Many of the complaints mentioned come from the way that a given entity, "the sheet" (or page) is used to both computation and to present multiple computations. Rethinking that is likely to yield huge benefits.
(3) It should be possible to tag computations so they can be more easily used to build other computations. For example, I was recently doing some cost analysis and realized part way through that I'd like to break down the numbers in other ways. Since I was using a sequence of equations in an ordinary programming language, it was easy enough to define appropriate accumulators and pick up the values from the equations, but it would have been a pain with a spreadsheet. And, my solution was too granular.
I'm not expressing (3) very well, but I think that it's a big deal, so feel free to contact me.
I have to use old software databases at my engineering job. The only way to get the data out of the system is to show it on the screen 1 at a time or to print it out. I would love for this new spreadsheet to pretend to be a printer and have the data piped directly to my spreadsheet instead of my pdf printer.
Upvoted for the craziest bad-ass suggestion in the whole thread.
Thanks.
Every engineering company I have worked at had these legacy databases. They all allow you to enter information (not very well), see the information on your screen, or send a report to the printer.
Piping data to a spreadsheet is something almost everyone in the companies I work with (I am a consultant) could do. These aren't sophisticated users. Even the ones with recent EE degrees aren't programming in their spreadsheets. The Mechanical Engineers don't even know that you can.
What they do need is tools to analyze the data existing in these large databases. Switching to another database/interface is not an option.
To extend my original idea: Excel has a way to import data from a text file. It works ok. Select your field delimiter, etc. What would be awesome is if I could draw my own macro on the screen. Make it more powerful. Excel shows me how my input data will work in the spreadsheet. Do that, but give me more editing tools.
Here is why: the data coming out of these databases to the printer is structured. Every record looks the same. If I can draw what one case looks like all of my data will be entered to the spreadsheet correctly.
I know I am not explaining this well, but if anyone (including the YC group) wants me to elaborate further I will.
I would pay for a spreadsheet that has these 2 features. A large part of my job is analyzing data in my customer's database. I can provide my customer with more value if the data input to spreadsheet is trivial as opposed to me billing for several hours performing the task by hand.
Every engineering company I have worked at had these legacy databases. They all allow you to enter information (not very well), see the information on your screen, or send a report to the printer.
Piping data to a spreadsheet is something almost everyone in the companies I work with (I am a consultant) could do. These aren't sophisticated users. Even the ones with recent EE degrees aren't programming in their spreadsheets. The Mechanical Engineers don't even know that you can.
What they do need is tools to analyze the data existing in these large databases. Switching to another database/interface is not an option.
To extend my original idea: Excel has a way to import data from a text file. It works ok. Select your field delimiter, etc. What would be awesome is if I could draw my own macro on the screen. Make it more powerful. Excel shows me how my input data will work in the spreadsheet. Do that, but give me more editing tools.
Here is why: the data coming out of these databases to the printer is structured. Every record looks the same. If I can draw what one case looks like all of my data will be entered to the spreadsheet correctly.
I know I am not explaining this well, but if anyone (including the YC group) wants me to elaborate further I will.
I would pay for a spreadsheet that has these 2 features. A large part of my job is analyzing data in my customer's database. I can provide my customer with more value if the data input to spreadsheet is trivial as opposed to me billing for several hours performing the task by hand.
Queries.
Excel isn't a database but a lot of people use it like one anyways.
Excel isn't a database but a lot of people use it like one anyways.
You can do a lot of stuff that overlaps with queries with just array formulas, and a lot of the rest with pivot tables.
For example, getting the sum bill of every person who lives in England looks like this in one workbook I have:
{SUM(IF($C1:$C$10000 = "England",$E$1:$E10000,0))}
(If I want to see, say, the top 10 outstanding bills in England I have to use a pivot table).
Excels biggest problem is that the workbooks produced with it are really hard to maintain. Looking at the code above, for example... going back to that (which is only a trivial example) in a month is going to be pure pain. Updating data requires cutting and pasting, which can be error prone. Unit testing is only possible with sample known-good data sets, and copying in new data tends to make it less than certain that the version you are using is the same as the one you tested.
Oh, and sharing workbooks between users is really tough. I can usually figure out other people's Java - but I have yet to be able to reverse engineer a non-trivial Excel work flow.
For example, getting the sum bill of every person who lives in England looks like this in one workbook I have:
{SUM(IF($C1:$C$10000 = "England",$E$1:$E10000,0))}
(If I want to see, say, the top 10 outstanding bills in England I have to use a pivot table).
Excels biggest problem is that the workbooks produced with it are really hard to maintain. Looking at the code above, for example... going back to that (which is only a trivial example) in a month is going to be pure pain. Updating data requires cutting and pasting, which can be error prone. Unit testing is only possible with sample known-good data sets, and copying in new data tends to make it less than certain that the version you are using is the same as the one you tested.
Oh, and sharing workbooks between users is really tough. I can usually figure out other people's Java - but I have yet to be able to reverse engineer a non-trivial Excel work flow.
You can do a lot of stuff that overlaps with queries with just array formulas, and a lot of the rest with pivot tables.
This is true, you can do a lot with this. And if you're prepared to make a lot of columns with the various flags you need, you can build up quite complex queries - having an easy way to do this would be nice though.
I've spent a lot of time in Financial Services and it's surprising how much of the industry is run on spreadsheets, especially investment banking - and I mean online - they'll have Excel running all day, receiving real time price feeds, running a calculation and republishing. Excel is pretty much the glue that holds the whole industry together.
So I also agree on the maintenance issue - These sheets can get quite complex and it's almost impossible for someone to understand coming in cold. It would be very useful to have a workflow model on top - which I guess is really adding the algorithm aspect - but I've never seen a speadsheet metaphor that does this well.
This is true, you can do a lot with this. And if you're prepared to make a lot of columns with the various flags you need, you can build up quite complex queries - having an easy way to do this would be nice though.
I've spent a lot of time in Financial Services and it's surprising how much of the industry is run on spreadsheets, especially investment banking - and I mean online - they'll have Excel running all day, receiving real time price feeds, running a calculation and republishing. Excel is pretty much the glue that holds the whole industry together.
So I also agree on the maintenance issue - These sheets can get quite complex and it's almost impossible for someone to understand coming in cold. It would be very useful to have a workflow model on top - which I guess is really adding the algorithm aspect - but I've never seen a speadsheet metaphor that does this well.
You might find dynamic named ranges to help keep things orderly. That way you can refer to the ranges by name, and also not have to worry about your $E$1:$E10000 not being up to date when there's 15000 rows of data instead of 10000.
You could do something like this:
{SUM(IF(country_name = "England",bill_amt,0))}
http://www.ozgrid.com/Excel/DynamicRanges.htm
You could do something like this:
{SUM(IF(country_name = "England",bill_amt,0))}
http://www.ozgrid.com/Excel/DynamicRanges.htm
> SUM(IF($C1:$C$10000 = "England",$E$1:$E10000,0))
Slightly offtopic, but I just wanted to point out that excel syntax looks a lot like lisp, with biggest different being the first item is outside the parens.
Slightly offtopic, but I just wanted to point out that excel syntax looks a lot like lisp, with biggest different being the first item is outside the parens.
BTW, you might be able to throw =large() around that formula to get your top 10 directly
Just the ability to represent one-to-many relationships in a native way (without resorting to hiding rows or the like) would be a huge step in that direction. The goal could be: make Excel handle more of the borderline cases where you really should be using a simple database, but all you know is Excel.
> Excel isn't a database but a lot of people use it like one anyways.
Then why not build database-like functionality into the system?
Then why not build database-like functionality into the system?
I'd like to be able to open a database as as spreadsheet, with fields turned into column names, and tables on worksheets.
I'd like v and hlookups to be turned into SQL (and vice versa) with animations explaining what's happening in each.
I'd like v and hlookups to be turned into SQL (and vice versa) with animations explaining what's happening in each.
did you mean SQL? You can connect to a .xls via ODBC
Excel is actually a reasonably complete program. That said, I would like to be able to:
1. Build predictive models from data I've entered in Excel. I find myself exporting from Excel into R a lot to satisfy this need. Microsoft partially addresses this need with the Data Mining Add-ons for Excel.
2. Have more than ~1 million rows (which is Excel 2007's limit).
3. More easily clean up data in a large spreadsheet.
4. Reversibly anonymize data -- if I download some logs with usernames or IPs, I don't want those in my analysis (I just hide those columns), but I do need to have a unique identifier for each row. And later get the names back if I want.
And a couple things I think Excel is great at already:
1. Making data look pretty. The charts are great, and there's (http://www.officelabs.com/projects/chartadvisor/Pages/defaul...) for non-power users.
2. Making data easily portable.
3. Filtering/sorting data -- VLOOKUP has saved the day so many times.
1. Build predictive models from data I've entered in Excel. I find myself exporting from Excel into R a lot to satisfy this need. Microsoft partially addresses this need with the Data Mining Add-ons for Excel.
2. Have more than ~1 million rows (which is Excel 2007's limit).
3. More easily clean up data in a large spreadsheet.
4. Reversibly anonymize data -- if I download some logs with usernames or IPs, I don't want those in my analysis (I just hide those columns), but I do need to have a unique identifier for each row. And later get the names back if I want.
And a couple things I think Excel is great at already:
1. Making data look pretty. The charts are great, and there's (http://www.officelabs.com/projects/chartadvisor/Pages/defaul...) for non-power users.
2. Making data easily portable.
3. Filtering/sorting data -- VLOOKUP has saved the day so many times.
1 million rows??? Who wants to scroll through that mess?
No one. But people often have that much data - e.g. from mechanical sources - that they need to analyze or report on.
I have a lot of tools put out CSV for me. It'd be nice if Excel could handle a few thousand lines when I have a few gigs of RAM.
[deleted]
Is it aimed at any particular domain? Finance folks pretty much live in excel, they might make a good use-case on what can't be done or what they don't like in excel.
Slides 4 & 5 give some insight: http://www.scribd.com/doc/2191289/yaron-minskycufp-2006
While I am not a big user of excel, the google docs version of excel is very similar to the desktop version + collaboration (think Oddpost). The other approach taken by some people I have spoken with is viewing it more as data and tying to do more (think gmail to some extent).
Slides 4 & 5 give some insight: http://www.scribd.com/doc/2191289/yaron-minskycufp-2006
While I am not a big user of excel, the google docs version of excel is very similar to the desktop version + collaboration (think Oddpost). The other approach taken by some people I have spoken with is viewing it more as data and tying to do more (think gmail to some extent).
[deleted]
65,000.
(that's the maximum number of rows in Excel before 2007). I'd like something similar to SPSS, which is more convenient for tables with lots of rows and separates the data from the formulae.
A tool for working with streaming data.
Also, charting that works for large amounts data. Try having Excel chart 65,000 rows and you'll have time to make coffee while you wait. There are no ways to zoom or analyze Excel charts, either.
In fact, charting for data analysis alone is a big enough problem that needs solving (and you won't have to do all that catching up with Excel). Short of using HTML/Flash or .NET/Java components, there is nothing an enterprise worker can use. Get access to a Bloomberg terminal from somewhere and play with its charts to see what I mean.
(that's the maximum number of rows in Excel before 2007). I'd like something similar to SPSS, which is more convenient for tables with lots of rows and separates the data from the formulae.
A tool for working with streaming data.
Also, charting that works for large amounts data. Try having Excel chart 65,000 rows and you'll have time to make coffee while you wait. There are no ways to zoom or analyze Excel charts, either.
In fact, charting for data analysis alone is a big enough problem that needs solving (and you won't have to do all that catching up with Excel). Short of using HTML/Flash or .NET/Java components, there is nothing an enterprise worker can use. Get access to a Bloomberg terminal from somewhere and play with its charts to see what I mean.
Take a look at SQL Server Reporting Services.
It's a charting and reporting tool over SQL Server (and I thinkg any other ODBC/OLEDB source), it works over large datasets, has both Web UI and desktop UI, and comes in the box with SQL Server itself.
FD: I work in SQL Server, athough not on Reporting Services. FWIW, Reporting is a huge success with our customers.
It's a charting and reporting tool over SQL Server (and I thinkg any other ODBC/OLEDB source), it works over large datasets, has both Web UI and desktop UI, and comes in the box with SQL Server itself.
FD: I work in SQL Server, athough not on Reporting Services. FWIW, Reporting is a huge success with our customers.
It is also available as a free download for developers to try it out and learn / test.
I have used SQL Reporting Services as well as Business Objects tools and I am pretty impressed with the latest releases from MS.
I have used SQL Reporting Services as well as Business Objects tools and I am pretty impressed with the latest releases from MS.
This minor, but:
A round() function that isn't stupid, i.e. it rounds to a certain number of significant figures, rather than relative to the decimal point. Workarounds exist, but I want a no-work-around.
I suppose it's not a problem worthy of very smart programmers, but it still makes me wish for bullets-over-SMTP.
A round() function that isn't stupid, i.e. it rounds to a certain number of significant figures, rather than relative to the decimal point. Workarounds exist, but I want a no-work-around.
I suppose it's not a problem worthy of very smart programmers, but it still makes me wish for bullets-over-SMTP.
Export to quote-qualified CSV.
Automatic versioning and version control.
Did you try Sharepoint? It does versioning, version control and a bit of collaboration for Excel.
Sharepoint is fine if you are only tacking things at the file level. You can't answer stuff like "who changed the formula in E5?" Or even "how long has the formula in E5 been wrong?"
Some kind of auditability of Excel spreadsheets would save enormous amouns of money and time.
Some kind of auditability of Excel spreadsheets would save enormous amouns of money and time.
No, Sharepoint is not fine. At its core, Sharepoint is one enormous ugly kludge.
You can diff two versions against each other.
Of course it's not as nice as "blame", which I think is what you want here?
Of course it's not as nice as "blame", which I think is what you want here?
That's correct, textually `diff'ing two versions of a file in a bisecting fashion is quite disparate from viewing a document's change history. The idea of auditing and version control ought to be more intrinsic to a spreadsheet or database system than an after-thought supported (poorly) by external tools.
No. I'm not really looking for blame-generation, although others would. I don't do auditing, but some larger financial institutions do.
When investigating bugs (and more than a few times, we're turning into real code something cobbled out of an Excel spreadsheet, so the reference standard is the old spreadsheet), it helps to know why something is different from another, and why the formula in E5 is different than the formula in E4 or E6. It is incredibly easy to screw up formulas with inserting/removing rows with cut & paste.
Two sample foul ups: http://thedailywtf.com/Articles/The-Great-Excel-Spreadsheet.... http://thedailywtf.com/Articles/The-Revealing-Spreadsheet.as...
I've encountered worse situations in the past than those 2 dailyWTF episodes (as well as seen one old employer's code on that site).
Word has a feature where you can see the changes, what was previously present, and who changed it when. Something like.
When investigating bugs (and more than a few times, we're turning into real code something cobbled out of an Excel spreadsheet, so the reference standard is the old spreadsheet), it helps to know why something is different from another, and why the formula in E5 is different than the formula in E4 or E6. It is incredibly easy to screw up formulas with inserting/removing rows with cut & paste.
Two sample foul ups: http://thedailywtf.com/Articles/The-Great-Excel-Spreadsheet.... http://thedailywtf.com/Articles/The-Revealing-Spreadsheet.as...
I've encountered worse situations in the past than those 2 dailyWTF episodes (as well as seen one old employer's code on that site).
Word has a feature where you can see the changes, what was previously present, and who changed it when. Something like.
In revision control systems, the "Who changed this line last and when?" is called "blame", so in fact, you are looking for "blame". :)
Sharepoint is only really an option if all your users are on Windows.
Not really. You sacrifice some functionality, but you can still use Sharepoint from a Mac.
http://www.microsoft.com/mac/itpros/default.mspx?MODE=ct&...
No Linux support of course.
http://www.microsoft.com/mac/itpros/default.mspx?MODE=ct&...
No Linux support of course.
Or else the ability to save it as text (XML) in a way that will work with SVN so that we can have multiple people working on an Excel sheet saved that way and SVN will be able to merge it. When you try that with Excel, you get all kinds of conflicts in extra data that it's saving in the headers.
Microsoft Word has a versioning feature, under the File menu, not automatic though.
Dropbox?
Excel is Turing-complete :P
Turing-Machines are Turing-complete, too.
O RLY?
To make my point explicit: Excel being Turing-complete does not imply it being feature-complete.
I know. It was a joke, thus the ":P", and so was the "O RLY?". But maybe the emoticon wasn't clear enough and I should've added an interrobang there? Anyway, thanks for the -10 karma points.
BTW, I do my spreadsheets with a Von Neumann derivate of a finite state automaton.
BTW, I do my spreadsheets with a Von Neumann derivate of a finite state automaton.
Can you prove it? (Not using scripting, of course.)
I do not want to prove it, because I'm lazy, but I know it's trivially provable that it's possible to create a Turing machine in Excel in many ways. You could take any row or column to be the tape and store state and table in other cells. Then add a bunch of conditionals and LOOKUPs you get your Turing machine.
It would probably be easier using VBA, but it's an interesting idea. (Not that it would get you anywhere, though.)
I'm not so sure. Googling around on this brings up a lot of statements to the effect that Excel is not Turing-complete. (I mean, of course, excluding VBA.) I haven't found a proof either way, though surely one is out there.
This paper claims to prove that the authors' spreadsheet is Turing-complete in contrast to Excel:
http://web.engr.oregonstate.edu/~burnett/Turing/TuringMachin...
Actually, it would be more interesting if spreadsheets were not Turing-complete, given how much people are able to do with them.
This paper claims to prove that the authors' spreadsheet is Turing-complete in contrast to Excel:
http://web.engr.oregonstate.edu/~burnett/Turing/TuringMachin...
Actually, it would be more interesting if spreadsheets were not Turing-complete, given how much people are able to do with them.
Really? I'd find it hard to believe that it isn't Turing complete. The only reason I can think of would be limits on nested functions or otherwise, but I dunno, I've been googling around myself and some say it isn't.
Well, next thing to do: implement a Turing machine in Excel. Or Google Docs. I think I'm going for the latter.
Edit: Spreadsheets have no state. This is hard. If only I could delay evaluation :P
Edit: Spreadsheets have no state. This is hard. If only I could delay evaluation :P
Can you email me about this? Address in profile.
Oh, sure, I'll probably even start a blog out of this :P
Watch out, you're blowing your cover as lazy :)
Well this is just too interesting, and I should stop putting off this blog thing.
So? What have you learned?
It is possible, not specially hard, I implemented a Turing machine and a one-dimensional cellular automaton. You can program just about anything in a spreadsheet. I will later study the inner properties of the spreadsheet as a data structure and a programming paradigm.
Are they aware of Resolver One? It's spreadsheet-Python mashup.
http://www.resolversystems.com
Sometimes I miss similar type of scripting. With Excel, I often finish copy-and-pasting raw data into Python string, upon which I then do something procedural.
http://www.resolversystems.com
Sometimes I miss similar type of scripting. With Excel, I often finish copy-and-pasting raw data into Python string, upon which I then do something procedural.
Here are some features that would be nice in Excel:
1. Programmability in something other than VBA (Python?).
2. Online spreadsheets like Google.
3. Better search and replace.
4. Ability to reference tables through URLs so they could show up in blogs and in HTML.
Something like this: http://ycspreadsheets.com/joe/doc1.ss?s=1&block=a1:c10. This
should produce HTML that some javascript can replace in my blog with the table pulled
out of the spreadsheet.
5. Ability to pull and reference data dynamically from online sources. For example,
imagine a spreadsheet cell that pulled the current stock price of GOOG every time it
was viewed. And the rest of the spreadsheet would naturally update automatically.1. Already doable to a significant extent http://www.google.com/search?q=excel+python
2. Well, Google does this :)
3. Follows from 1.
4. Good idea, but why not just use this: http://www.google.com/search?q=spreadsheet+widget
Or http://docs.google.com/support/bin/answer.py?answer=55244...
5. Already easily doable in Excel http://articles.techrepublic.com.com/5100-10878_11-6115870.h...
In Google Spreadsheet, just use: =GoogleFinance("GOOG"; "price")
------
And I hope Google is working on a REPL for Google Docs where you can interactively run any language on your spreadsheet. :-)
2. Well, Google does this :)
3. Follows from 1.
4. Good idea, but why not just use this: http://www.google.com/search?q=spreadsheet+widget
Or http://docs.google.com/support/bin/answer.py?answer=55244...
5. Already easily doable in Excel http://articles.techrepublic.com.com/5100-10878_11-6115870.h...
In Google Spreadsheet, just use: =GoogleFinance("GOOG"; "price")
------
And I hope Google is working on a REPL for Google Docs where you can interactively run any language on your spreadsheet. :-)
Actually a repl might be just the thing in a spreadsheet.
I just learned that this sort of exists: http://projects.gnome.org/gnumeric/doc/sect-extending-python...
[ credit : http://news.ycombinator.com/item?id=430129 ]
[ credit : http://news.ycombinator.com/item?id=430129 ]
Can't you accomplish #2 via Sharepoint or Office Online? I know there are some options if you click Windows Logo > Publish in Excel 2007.
I think so. But I don't think the published spreadsheet can be edited online.
2. The next version of Excel will be online.
(http://en.wikipedia.org/wiki/Microsoft_Office_14)
I thought number 5 was already possible in excel :/
It is already possible in Excel, but it is very poorly documented.
I have a few sheets that I use which pull from a non-ODBC URL using http. It populates a range of cells with new data automatically each time the spreadsheet is opened. (on a Mac too).
I have a few sheets that I use which pull from a non-ODBC URL using http. It populates a range of cells with new data automatically each time the spreadsheet is opened. (on a Mac too).
"Programmability in something other than VBA (Python?)."
Resolver One is a spreadsheet that has that: http://www.resolversystems.com/products/programmability.php
OpenOffice Calc has some kind of Python programmability too, though I don't know how good it is.
Resolver One is a spreadsheet that has that: http://www.resolversystems.com/products/programmability.php
OpenOffice Calc has some kind of Python programmability too, though I don't know how good it is.
"Ability to pull and reference data dynamically from online sources. For example, imagine a spreadsheet cell that pulled the current stock price of GOOG every time it was viewed. And the rest of the spreadsheet would naturally update automatically."
Yes. This feature implemented well would create a really cool app. What would be even more interesting would a situation where several different sheets could pull data from each using a clever protocol to avoid the churning of values. Imagine different enterprises which each had online sheets describing their current production abilities and current supply needs. With a clever protocol, their production processes be semi-automatically coordinated (and remember, semi-automatic, some throttling is necessary to prevent self-referential cells from feeding back in an unhelpful way).
Yes. This feature implemented well would create a really cool app. What would be even more interesting would a situation where several different sheets could pull data from each using a clever protocol to avoid the churning of values. Imagine different enterprises which each had online sheets describing their current production abilities and current supply needs. With a clever protocol, their production processes be semi-automatically coordinated (and remember, semi-automatic, some throttling is necessary to prevent self-referential cells from feeding back in an unhelpful way).
Could the "clever protocol" simply be: refresh the numbers when the user refreshes a page?
The issue with this is that Excel refreshes the active workbook by default after every change. Any quant or someone dealing with huge sheets of formulas learn that you can change this behavior to default to refresh via shift+F9 (I think)
That's for local changes. I think what we're talking about here is a sheet (at one URL) referencing a completely different sheet (at some other URL). The equivalent in Excel, I suppose, would be one .xls file referencing another.
#1 is possible using COM automation. You can write an app that interfaces with Excel via COM, or integrate your own functionality into Excel by developing a COM add-in.
#5 can be done in Excel by implementing an RTD server (http://office.microsoft.com/en-us/excel/HP030662371033.aspx). An interesting example is here: http://fransking.blogspot.com/2007/03/yfquotertd-real-time-d...
#5 can be done in Excel by implementing an RTD server (http://office.microsoft.com/en-us/excel/HP030662371033.aspx). An interesting example is here: http://fransking.blogspot.com/2007/03/yfquotertd-real-time-d...
#1 is really important - VBA is an awful language.
My two cents: add support for arbitrary precision math. Sometimes you need it, and when you do, you really need it.
My two cents: add support for arbitrary precision math. Sometimes you need it, and when you do, you really need it.
(1) can be done with Visual Studio Tools for Office: http://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office
what about being able to specify linear algebraic manipulations of part of the spread sheets, that would go a long way to make many things easier (sort of a mini matlab, but done right)
There is an equation tool in Excel for this purpose.
Gnumeric is programmable in Python
ability to recurse.
Recursion is fun, but how would you use it in a spreadsheet?
Changing values in place?
A1 = 0.1*A1
Can be useful if you need to convert values, etc., but don't want to create an entire new row/column
A1 = 0.1*A1
Can be useful if you need to convert values, etc., but don't want to create an entire new row/column
Can't think of a concrete example at the moment, but there have been cases when I had to make a financial model with a lot of columns (and rows) where a recursive algorithm would have presented the result I was looking for using a lot less cells.
3d plotting.
I think something that google has begun to do, and that is incredibly valuable, is data "sources" that aren't hard coded into any particular spreadsheet, but that come from a URL or API of some kind.
I should be able to make a row of "oil prices by month since 2005" that updates on its own. Or a cell with "current value of the DOW".
I should be able to make a row of "oil prices by month since 2005" that updates on its own. Or a cell with "current value of the DOW".
There are plenty of third-party plugins that do this -- Bloomberg and Reuters have them, for example. For example, the formula for the current level of the Dow Jones Industrial average is =blp("INDU Index","LAST_PX"). The equivalent Reuters formula is something like =RtGet(".INDU","PXLAST"). At work I have a sheet which pulls in real-time prices on Treasury bonds, interest rate swaps, options, stocks, futures, mortgage-backed securities, etc. and organizes them all into nice tables and charts. It's built on the Bloomberg and Reuters APIs.
Decent looking plots.
If I'm going to make a scatter plot style plot for anything besides an internal memo (even an internal presentation) I always plot with something else.
If I'm going to make a scatter plot style plot for anything besides an internal memo (even an internal presentation) I always plot with something else.
Look at ASAP Utilities.
I'm exactly the opposite of your target audience with this question, but hopefully I can give a useful response anyway :)
I know almost nothing about Excel and it's a deficiency I feel quite keenly. The program is almost completely undiscoverable to me and I don't even know where to start with it. I know that there are powerful uses and features of Excel but the model (or at least the bits of it I've been exposed to) hasn't clicked for me yet.
I suspect I'm not alone in my bafflement; maybe there is the possibility this new spreadsheet could be useful to us Excel-ignorant customers who nonetheless want access to the benefits of this class of software.
As a hacker and programmer who is comfortable with all sorts of paradigms I feel really strange admitting this weakness but I suspect that I'm not the only one who is in this boat.
I know almost nothing about Excel and it's a deficiency I feel quite keenly. The program is almost completely undiscoverable to me and I don't even know where to start with it. I know that there are powerful uses and features of Excel but the model (or at least the bits of it I've been exposed to) hasn't clicked for me yet.
I suspect I'm not alone in my bafflement; maybe there is the possibility this new spreadsheet could be useful to us Excel-ignorant customers who nonetheless want access to the benefits of this class of software.
As a hacker and programmer who is comfortable with all sorts of paradigms I feel really strange admitting this weakness but I suspect that I'm not the only one who is in this boat.
Did you try Excel 2007 yet? They dedicated entire release trying to make it easier for people to get started with Excel (all of Office, really) and then discover more features as they go along.
Support videos/images. Edit: as well as audio/video comments.
I would like to have mind mapping (like http://www.mindjet.com/) right inside excel (as well as for other office applications) but it should be tightly integrated. It seems to me such type functionality might be useful for data crunching and modeling with full brain thinking. Spreadsheets enable only logical half of brain so there is room for improvement.
Oh my goodness, you're going to get my spreadsheet rant. This is what frustrates me every single time I use Excel (or the Google Docs spreadsheet, for that matter).
I may not be answering your question since I'm talking about usability instead of more powerful features, but I can't but imagine that there'd be a market for simple and easy to use, even if it turns out it's not going to be addressed by your particular startup...
I have a table, some data that I've laid out in rows and columns. Something simple. How much money I've been paid on my invoices to clients, for example, one invoice per row.
Then I want to sum the column, to get how much I've been paid in total. (Yes, I'm talking about a very simple spreadsheet. But that's my point, that something so simple is still messed up!) So I type in a formula: =sum(C2:C10)
Now I add a row, to put in another entry. Does my sum change, to include the new row? (C2:C11) No, it does not.
So I do not want to be saying sum(C2:C10). I want to say, here is my simple table, and give me the sum of this column. Which, I don't know what the language would look like, but if I named my table "invoices" maybe it would be sum(invoices.C) or sum(invoices.amount) or something.
Every time someone comes out with a new spreadsheet (Excel, OpenOffice, Google Docs...) I look to see if it is easier to use. Nope! Everyone is too busy being compatible with the last guy.
I may not be answering your question since I'm talking about usability instead of more powerful features, but I can't but imagine that there'd be a market for simple and easy to use, even if it turns out it's not going to be addressed by your particular startup...
I have a table, some data that I've laid out in rows and columns. Something simple. How much money I've been paid on my invoices to clients, for example, one invoice per row.
Then I want to sum the column, to get how much I've been paid in total. (Yes, I'm talking about a very simple spreadsheet. But that's my point, that something so simple is still messed up!) So I type in a formula: =sum(C2:C10)
Now I add a row, to put in another entry. Does my sum change, to include the new row? (C2:C11) No, it does not.
So I do not want to be saying sum(C2:C10). I want to say, here is my simple table, and give me the sum of this column. Which, I don't know what the language would look like, but if I named my table "invoices" maybe it would be sum(invoices.C) or sum(invoices.amount) or something.
Every time someone comes out with a new spreadsheet (Excel, OpenOffice, Google Docs...) I look to see if it is easier to use. Nope! Everyone is too busy being compatible with the last guy.
CatDancer, the problem is that the system can't always know what you intended: should the range be "greedy" and jump to incorporate the new numbers as you append them, or should it be "strict" and keep to the boundaries you originally gave it? Sometimes the latter behavior is what is desired, and expanding to C2:C11 would be wrong in that case. So what's really needed here is a lightweight way to communicate your intention to the system. (Actually, you can do this in Excel - http://tinyurl.com/26b78 - but it's far from lightweight.)
I'm assuming that (in terms of your example) the invoice amounts that you're adding up would form a contiguous range of numbers, and that this range would be bounded by whitespace. That is, you might have some other range that used column C -- say "expenses" -- but it would be lower down, say starting at C15, and there would be at least one blank cell between the two. Is that correct? If it weren't for that lower range, you could just take the sum of the whole column and you'd be good. But it's too inconvenient (and not the "spreadsheet way") to force everything into separate columns.
If the above is correct, how would you feel about being able to define a range with a notation like this: "C2:C✱", meaning "the range of cells that starts at C2 and goes down until it hits whitespace"? Then as you add numbers to C11, C12, etc., the range would automatically expand to include them. But you'd still have to be careful to ensure there was a "moat" of whitespace around your invoice range. If you filled in the last non-whitespace cell before your other table, you'd now have connected the two tables in such a way that "C2:C✱" would leap down to the end of the second table. In other words you'd be lumping "invoices" together with "expenses" which is probably incorrect.
I'm assuming that (in terms of your example) the invoice amounts that you're adding up would form a contiguous range of numbers, and that this range would be bounded by whitespace. That is, you might have some other range that used column C -- say "expenses" -- but it would be lower down, say starting at C15, and there would be at least one blank cell between the two. Is that correct? If it weren't for that lower range, you could just take the sum of the whole column and you'd be good. But it's too inconvenient (and not the "spreadsheet way") to force everything into separate columns.
If the above is correct, how would you feel about being able to define a range with a notation like this: "C2:C✱", meaning "the range of cells that starts at C2 and goes down until it hits whitespace"? Then as you add numbers to C11, C12, etc., the range would automatically expand to include them. But you'd still have to be careful to ensure there was a "moat" of whitespace around your invoice range. If you filled in the last non-whitespace cell before your other table, you'd now have connected the two tables in such a way that "C2:C✱" would leap down to the end of the second table. In other words you'd be lumping "invoices" together with "expenses" which is probably incorrect.
You can do most of that (pretty simply) in excel.
For your data C2:C10 - Do a field: sum(C2:C11) Then when you want to add more data - right-click on the row (11) and "insert" That will update your sum calculation.
Also - you can do named fields - so that if you select the fields C2:C11 - then you can name them as "invoices" (in excel 2003 it's in the top left corner - there's a selection box you can type in. Just select and type a name in there). The lets you do the command sum(invoices)
Also - don't forget you can do something like sum(C:C) which will just give you everything in C column..
For your data C2:C10 - Do a field: sum(C2:C11) Then when you want to add more data - right-click on the row (11) and "insert" That will update your sum calculation.
Also - you can do named fields - so that if you select the fields C2:C11 - then you can name them as "invoices" (in excel 2003 it's in the top left corner - there's a selection box you can type in. Just select and type a name in there). The lets you do the command sum(invoices)
Also - don't forget you can do something like sum(C:C) which will just give you everything in C column..
Do a field: sum(C2:C11)
I.e. leave a blank row at the bottom of my table, and have the sum include that blank row? I actually know about that trick (thanks :)... what I want is a spreadsheet that does what I want without my tricking it.
I.e. leave a blank row at the bottom of my table, and have the sum include that blank row? I actually know about that trick (thanks :)... what I want is a spreadsheet that does what I want without my tricking it.
You don't have to trick it in Excel 2003. If you have 10 rows (i.e. c1 thru c10) and in c11 you have the sum if you right click on row 11 and insert row it will insert a row above 11 and update your formula for you in c11 to include the new row.
When Excel asks if you want to use the list builder, you do. That is exactly what it does.
With the list builder you are always given an extra row at the bottom to continue adding to the list. Any formulas below the list builder will be pushed down and expanded.
The list builder also turns on Auto Filters for the list as well.
With the list builder you are always given an extra row at the bottom to continue adding to the list. Any formulas below the list builder will be pushed down and expanded.
The list builder also turns on Auto Filters for the list as well.
You can retain formulas when you add a row but it only works if you add a row before the last row where your formula applies.
You can do what you're wanting with dynamic named ranges.
From reading some of your other responses it seems like you don't want to sum the entire row, maybe because you have the sum listed at the bottom of the dataset or something. With a dynamic named range you can add rows to the bottom of the range, and you also get a nice name to reference it by. It works by using offset and count/counta to deliver a range based on how many occupied cells there are (depending on if you use count or counta).
There are a few ways of doing it listed here: http://www.ozgrid.com/Excel/DynamicRanges.htm
In my experience you can do an incredible amount of things in Excel before you even break into doing stuff in VBA. You just have to look at any of the numerous resources out there that have tricky formulas available.
You can do what you're wanting with dynamic named ranges.
From reading some of your other responses it seems like you don't want to sum the entire row, maybe because you have the sum listed at the bottom of the dataset or something. With a dynamic named range you can add rows to the bottom of the range, and you also get a nice name to reference it by. It works by using offset and count/counta to deliver a range based on how many occupied cells there are (depending on if you use count or counta).
There are a few ways of doing it listed here: http://www.ozgrid.com/Excel/DynamicRanges.htm
In my experience you can do an incredible amount of things in Excel before you even break into doing stuff in VBA. You just have to look at any of the numerous resources out there that have tricky formulas available.
I see I wasn't very clear about the point of my rant... I apologize to everyone who has taken the time to thoughtfully offer me solutions of how to get Excel to do this, but I know about that. I should have explained that I know about getting Excel to extend a range when I insert a row using techniques such as having the range include a blank row at the bottom, and I'm not surprised to hear that Excel has a feature like "list builder" bolted on.
When I said, "I'm frustrated every single time I use a spreadsheet", it's not that I can't do whatever it is that I need to get done, I just get annoyed when products are made hard to use when they don't have to be. It's not so much a personal frustration as that I've spent a lot of time at non-profits helping non-computer people use computers, and it's a huge waste of their time and of my time to have to train them how to manipulate the software to get what they want instead of the software just doing it.
C2:C11 was a tremendous advance in 1979 when personal computers had 48K of memory and 40x25 character screens, but goodness gracious, it's thirty years later!
Making something easier to use is a tremendous amount of hard work, but it isn't conceptually all that hard to understand: you look at what people are doing, and you write software to implement that, instead of making them manipulate the software to do the implementation themselves.
I haven't looked at it myself so I don't know if Apple got it right or not, but from Timothee's comment that "Numbers actually manages tables as independent objects of a page", it sounds like they're at least trying.
When I said, "I'm frustrated every single time I use a spreadsheet", it's not that I can't do whatever it is that I need to get done, I just get annoyed when products are made hard to use when they don't have to be. It's not so much a personal frustration as that I've spent a lot of time at non-profits helping non-computer people use computers, and it's a huge waste of their time and of my time to have to train them how to manipulate the software to get what they want instead of the software just doing it.
C2:C11 was a tremendous advance in 1979 when personal computers had 48K of memory and 40x25 character screens, but goodness gracious, it's thirty years later!
Making something easier to use is a tremendous amount of hard work, but it isn't conceptually all that hard to understand: you look at what people are doing, and you write software to implement that, instead of making them manipulate the software to do the implementation themselves.
I haven't looked at it myself so I don't know if Apple got it right or not, but from Timothee's comment that "Numbers actually manages tables as independent objects of a page", it sounds like they're at least trying.
I think your rant is somewhat misplaced as inferring the user desire in this case is not always possible. In general, I find it to be dangerous behavior when merely adding data changes formulas. I think the proper action is to insert a row.
On the other hand, having defined tables with in a workbook is great idea, but it makes the whole application a wee bit more complicated. I'd like to see it in something of a hybrid between Access and Excel, where you can mix structured and tabular data.
On the other hand, having defined tables with in a workbook is great idea, but it makes the whole application a wee bit more complicated. I'd like to see it in something of a hybrid between Access and Excel, where you can mix structured and tabular data.
No, I don't want the spreadsheet to "infer" my desire or for it to change my formulas when I enter data.
The formula should describe the calculation I want performed and it should continue to work even when I enter new data. For example, if I have a "defined table" as you say, I should be able to ask it for a sum of a column in the table, and have it continue to work even if I add new data to the table, with hacks or trickery or invoking obscure commands.
The formula should describe the calculation I want performed and it should continue to work even when I enter new data. For example, if I have a "defined table" as you say, I should be able to ask it for a sum of a column in the table, and have it continue to work even if I add new data to the table, with hacks or trickery or invoking obscure commands.
Numbers, from the iWork suite, does this. You can name your rows and columns and just them in your formulas:
http://www.apple.com/iwork/numbers/
Of course, it's not perfect if you look at the proprietary format, at the smaller number of formulas than Excel, and so on. But it's a nice piece of software as far as I'm concerned.
Of course, it's not perfect if you look at the proprietary format, at the smaller number of formulas than Excel, and so on. But it's a nice piece of software as far as I'm concerned.
I don't have a Mac so I don't have a way to tell if iWork does what I want or not, but note that simply being able to name a range doesn't do it.
It's actually simpler than naming a range because you can use the names of the rows and columns that you have in the header of the table.
It's a moot point since you don't have a Mac but from what you describe, Numbers does what you're missing.
I saw in one of your later comments that you were also talking about multiple tables on the same page. Numbers actually manages tables as independent objects of a page. So, in a table you can ask for the sum of a whole column without getting the numbers from another unrelated table on the same page. That's something that always bothered me in Excel.
It's a moot point since you don't have a Mac but from what you describe, Numbers does what you're missing.
I saw in one of your later comments that you were also talking about multiple tables on the same page. Numbers actually manages tables as independent objects of a page. So, in a table you can ask for the sum of a whole column without getting the numbers from another unrelated table on the same page. That's something that always bothered me in Excel.
Tables as independent objects on the page does sound like what I'm getting at. Of course I'd need to see it to see if they're doing it the way I want ^__^
[deleted]
Apple Numbers allows you to simple say =Sum(C) to sum up an entire row.
Excel allows you to do named ranges. Select a range, then type its name in the address box. Then, in another cell, you can type =sum(myrange).
Doesn't help. The point is Excel doesn't know what range I want when I extend my table, not whether I can give the range a name or not.
Am I missing something, or wouldn't =sum(C:C) work?
Would that sum the entire column in the spreadsheet? But what if I wanted to have a couple tables on a page (which I often do), or my sum below the numbers?
see my post above.
if you use insert row - then formulas respond and will go from C2:C10 to C2:C11 (tested in excel2003 at least)
if you use insert row - then formulas respond and will go from C2:C10 to C2:C11 (tested in excel2003 at least)
This is probably useful in many cases, but one could imagine where the inserted row bisects other ranges in other columns unintentionally.
An 'inner join' would be nice.
A few years ago I worked at a large financial consulting firm, and I was amazed at how often accountants would implement what basically amounted to an 'inner join' using nested iteration over columns in vba.
This was a few versions of Excel ago, so I don't know if this feature is available in recent versions or not. I imagine not, since then Excel would really start to encroach on Access's domain.
A few years ago I worked at a large financial consulting firm, and I was amazed at how often accountants would implement what basically amounted to an 'inner join' using nested iteration over columns in vba.
This was a few versions of Excel ago, so I don't know if this feature is available in recent versions or not. I imagine not, since then Excel would really start to encroach on Access's domain.
One thing nice about Numbers is that you don't worry about cells as an absolute coordinate system. Each table's unique, and you can add/remove rows without hitting everything else.
So, in a word, encapsulation.
Also, the line between databases & spreadsheets is fairly thin, how about some relational calculus? Some import/export with SQL? Or a query language?
So, in a word, encapsulation.
Also, the line between databases & spreadsheets is fairly thin, how about some relational calculus? Some import/export with SQL? Or a query language?
Instead of "new, more powerful spreadsheet", how about providing spreadsheet interface for a relational database?
Of course, if you want it to be successful, you need to be able to import Excel spreadsheets as is, including macros and formula, and you also may need to maintain the linking to/from other microsoft artifacts.
Of course, if you want it to be successful, you need to be able to import Excel spreadsheets as is, including macros and formula, and you also may need to maintain the linking to/from other microsoft artifacts.
Set the Excel Spreadsheet to pull from a remote data source using ODBC.
That gives the added security of allowing people to play with the data without changing the data in the database.
That gives the added security of allowing people to play with the data without changing the data in the database.
But you want them to be able to manipulate the data from Excel.
There is a spreadsheet interface metaphor. Parts of it are quite useful. Other parts are less convenient given the ways that people program/use spreadsheets. Some of them are better addressed with parts of the database metaphor.
There is a spreadsheet interface metaphor. Parts of it are quite useful. Other parts are less convenient given the ways that people program/use spreadsheets. Some of them are better addressed with parts of the database metaphor.
Worth a quick look at ThinkDigits for iPhone.
http://ignitedsoftware.com/products/thinkdigits/
http://ignitedsoftware.com/products/thinkdigits/
I'm not a big spreadsheet person, but I'd throw in aesthetics to the mix. I love Numbers when I have to use it, much more than I like Excel. Even though it's weaker, the looks more than make up for it.
Perhaps, one could gather up some good ideas from Ken Tiltons 'cells' system for Common Lisp?
My understanding of Cells what that it is a dataflow system that handles calculation graphs for some kind of formula. I doubt that it has any insight into user workflows in an actual spreadsheet application.
SQL queries would be really good. I have been in many situations where I would like to do a simple query on a large spreadsheet and can't so I have to resort to programming. Most of the time it is not worth the effort, so I don't bother. This feature would be a big help.
Excel doesn't work well on very large spreadsheets (I use it on some sheets with over 150,000 rows.) Things are slow to load, and commands like lookups are extremely slow, enough that I think they're doing O(n) lookups for things like the VLOOKUP command, even when looking up data on tables or sorted columns.
What makes the above even worse is that Excel has a pretty poor understanding of when a change necessitates a re-calculation of all values in the workbook, so I grind to a halt when making random unrelated changes (and even if I switch it to manual calculations, it re-calculates upon saving, meaning saving my work can become a 30-40 minute endeavor).
Pivot tables are pretty clunky an unintuitive for most users, even though I think lots of people would use them if they understood what they were.
VBA is a very verbose and inelegant language, and there are lots of operations which are called in totally different ways than the analagous forumulas in the spreadsheet. There are even some things you can do in spreadsheets which don't have an analagous VBA command, which leads to the fantastic work-around of using cells on your worksheet instead of variables and changing their text values to the command you really want to just run in VBA.
The standard fill down operation sometimes doesn't Just Work(TM). Example: say you want to make a cell "=C2E5". You try filling down and you get "=C3E6", when you wanted "=C3E5", because E5 is a constant. OK, fair enough, you say, you can't reasonably expect the machine to infer what you meant. But now you adjust the cell below to what you want, and now you select two cells, one that's "=C2E5", one right below it that is now "=C3E5", and now with both selected, you fill down again. Presto, the next cells are "=C4E7","=C5E7","=C6E9","=C7E9","=C8E11".... etc. That's pretty bad.
Some of these are pretty mundane, but they would all be big deals for me.
What makes the above even worse is that Excel has a pretty poor understanding of when a change necessitates a re-calculation of all values in the workbook, so I grind to a halt when making random unrelated changes (and even if I switch it to manual calculations, it re-calculates upon saving, meaning saving my work can become a 30-40 minute endeavor).
Pivot tables are pretty clunky an unintuitive for most users, even though I think lots of people would use them if they understood what they were.
VBA is a very verbose and inelegant language, and there are lots of operations which are called in totally different ways than the analagous forumulas in the spreadsheet. There are even some things you can do in spreadsheets which don't have an analagous VBA command, which leads to the fantastic work-around of using cells on your worksheet instead of variables and changing their text values to the command you really want to just run in VBA.
The standard fill down operation sometimes doesn't Just Work(TM). Example: say you want to make a cell "=C2E5". You try filling down and you get "=C3E6", when you wanted "=C3E5", because E5 is a constant. OK, fair enough, you say, you can't reasonably expect the machine to infer what you meant. But now you adjust the cell below to what you want, and now you select two cells, one that's "=C2E5", one right below it that is now "=C3E5", and now with both selected, you fill down again. Presto, the next cells are "=C4E7","=C5E7","=C6E9","=C7E9","=C8E11".... etc. That's pretty bad.
Some of these are pretty mundane, but they would all be big deals for me.
Use $ to keep copy/fill from autoincrementing. $E$5
"... In the long term, it is unlikely that any particular ASP will survive without you needing to migrate your data to something else. ... The issue you bring up, of how do you deal with the fact that it is unlikely most ASPs will be around to maintain your data, is a good one. It should be discussed and careful attention should be paid to it. Your suggestion to go with ones that have the most partners and customers (VisiCalc's situation in its day) doesn't fit with your comparison to staying away from VisiCalc. ..."
From the man (Dan Bricklin) himself ~ http://www.bricklin.com/nextvisicalc.htm
From the man (Dan Bricklin) himself ~ http://www.bricklin.com/nextvisicalc.htm
Some thoughts:
-With an online spreadsheet program, it would be nice if it could understand existing VBA code/Excel Macros. There is a lot of this out there.
-Better access control. AFAIK, Currently with Excel you can only password the document with one password. It would be nice to have an access control list, and maybe even restricting access within worksheets within the document.
-With an online spreadsheet program, it would be nice if it could understand existing VBA code/Excel Macros. There is a lot of this out there.
-Better access control. AFAIK, Currently with Excel you can only password the document with one password. It would be nice to have an access control list, and maybe even restricting access within worksheets within the document.
Hmm, I've seen some pretty hair-raising stuff done with Excel already... I don't think we need to increase its capabilities any further. It's an extremely powerful piece of software already.
The fundamental problem is that Excel forces a novice (and actually everyone) to work at a level of detail such that key elements of the plan or analysis are lost in a forest of low level code. I would focus on the novice because it will be hard to get power users to switch. Letting a user pick a range or distribution for an input instead of a specific value will help to avoid anchoring around the "one right answer."
Compare two spreadsheet highlighting changes more intelligently than Excel's. For example, allow for a more intentional representation of
Allow for a set of input variables (and optionally some rules) to be defined as a scenario to compare differences between scenarios. Also, let an input variable be a distribution or an interval and the scenario specify any covariances.
Scenario planning and decision trees: a graphical view that can switch to a traditional spreadsheet view and vice versa.
EDIT http://www.google.com/search?q="next+generation+spreadsheet" turns up some interesting ideas as well.
Compare two spreadsheet highlighting changes more intelligently than Excel's. For example, allow for a more intentional representation of
decisions/numbers/inputs: control variable
relationships/rules: system representation
outputs/results: state variable
Allow for a set of input variables (and optionally some rules) to be defined as a scenario to compare differences between scenarios. Also, let an input variable be a distribution or an interval and the scenario specify any covariances.
Scenario planning and decision trees: a graphical view that can switch to a traditional spreadsheet view and vice versa.
EDIT http://www.google.com/search?q="next+generation+spreadsheet" turns up some interesting ideas as well.
In no particular order, here are some desirable features:
1) Version control. 2) Access restrictions and permissions. BONUS: Responsibilities, a la Siebel and other CRMs. 3) Simple drag and drop manipulation of data, such as concatenation. 4) Wufoo-like ease with building data entry forms. 5) Dummy data mode, so you can get help with customizing a complicated spreadsheet without revealing sensitive data. 6) Example use cases for sophisticated features, with well-written instructions, INOW a good, non-linear tutorial.
1) Version control. 2) Access restrictions and permissions. BONUS: Responsibilities, a la Siebel and other CRMs. 3) Simple drag and drop manipulation of data, such as concatenation. 4) Wufoo-like ease with building data entry forms. 5) Dummy data mode, so you can get help with customizing a complicated spreadsheet without revealing sensitive data. 6) Example use cases for sophisticated features, with well-written instructions, INOW a good, non-linear tutorial.
Quantrix is already a better excel and look how well they're doing for themselves (or not).
But seriously: take a gander at quantrix, rip off its features, slap yourself on the back for innovating, and go home.
Edit: the above is perhaps a bit too snarky to be helpful.
As a "power spreadsheet user" I moved on from excel to quantrix; this is something I can get away with b/c I'm not stifled with a closet full of legacy, mission-critical excel spreadsheets.
Quantrix is pretty much at the sweet spot of spreadsheet functionality: it's possible to imagine a more-powerful and more-general-purpose tool, but taking it even a little further would turn it into something not really a spreadsheet any more...you'd wind up back at R.
You'll find in Quantrix a mature, well-thought, and all-around "better excel".
But seriously: take a gander at quantrix, rip off its features, slap yourself on the back for innovating, and go home.
Edit: the above is perhaps a bit too snarky to be helpful.
As a "power spreadsheet user" I moved on from excel to quantrix; this is something I can get away with b/c I'm not stifled with a closet full of legacy, mission-critical excel spreadsheets.
Quantrix is pretty much at the sweet spot of spreadsheet functionality: it's possible to imagine a more-powerful and more-general-purpose tool, but taking it even a little further would turn it into something not really a spreadsheet any more...you'd wind up back at R.
You'll find in Quantrix a mature, well-thought, and all-around "better excel".
I'd like a better HTML export. One that spits out all the worksheets in an XL file as one simple HTML document with a bunch of tables. No javascript or multiple files, just a simple file that can be parsed easily. Also, the ability to import that same format.
Easily customizable keystrokes for absolutely everything.
I have a field that's a dictionary. Excel doesn't have a way to store it as a dictionary in a cell.
A really simple way to do VLOOKUPS, for people who never have before.
findincolumn('john',a) scrollaccross(3) retrievedata
findincolumn('john',a) scrollaccross(3) retrievedata
Best ideas I have here:
* Support Seadragon-like zooming into cells which expand into full spreadsheets of their own. You can go to infinite distance deep into the spreadsheets and each spreadsheet chooses a "cell" value to represent it to above container sheets.
* Ideally the above spreadsheet allows you to pull in other people's spreadsheets across the world to use as one of your cells (somewhat like Yahoo Pipes, I suppose)
* Alternatively, I'd like to see Excel go three dimensional for a single sheet. At a minimum perhaps use "layers" like Photoshop would to apply transformations and adaptations that collapse into the final view.
* I'd prefer this imaginary Excel also use Python or JavaScript for cell programming/calculations.
* I second the versioning information idea, too-- keep a history of every manual change to every cell and allow them to be reverted. In a git-esque way, support branching of cells, etc.
* Support Seadragon-like zooming into cells which expand into full spreadsheets of their own. You can go to infinite distance deep into the spreadsheets and each spreadsheet chooses a "cell" value to represent it to above container sheets.
* Ideally the above spreadsheet allows you to pull in other people's spreadsheets across the world to use as one of your cells (somewhat like Yahoo Pipes, I suppose)
* Alternatively, I'd like to see Excel go three dimensional for a single sheet. At a minimum perhaps use "layers" like Photoshop would to apply transformations and adaptations that collapse into the final view.
* I'd prefer this imaginary Excel also use Python or JavaScript for cell programming/calculations.
* I second the versioning information idea, too-- keep a history of every manual change to every cell and allow them to be reverted. In a git-esque way, support branching of cells, etc.
I used to work for a decent-sized international bank that used Excel for pretty much everything (any development projects were doomed if users couldn't pull the data they produced/exposed into Excel). So I can tell you some of the things I saw people need when working with Excel in a large organization:
1. Ability to push/pull a range from a company-wide database, based on a (name,date) key. When you pull based on a (name,date) key, you get the first range with that name that was published on or before the date you specified. A dev team at the bank implemented this and people really loved it.
2. Versioning, but more for reasons of space than for having a "blame" feature. People use the same spreadsheet daily/weekly to create a report, so they have to save copies of the reports daily/weekly in case they need to reproduce the calculations from a particular report even though the differences from report to report were just minor tweaks. It wasn't uncommon for me to see spreadsheets that were > 100 MB, copied and saved daily.
3. Better explaining of formulas - you can ask Excel what cells reference another cell and it'll draw a bunch of arrows for you, but it still takes a lot of concentration to figure out why you're getting the number 4 in a cell when its references are many cells deep, spread across several worksheets. It would be nice if there was a clear way of explaining a cell's formula without having to navigate from worksheet to worksheet and actually hand-trace the references. Even collecting all of the references in one place and drawing out a tree of formulas would be an improvement.
1. Ability to push/pull a range from a company-wide database, based on a (name,date) key. When you pull based on a (name,date) key, you get the first range with that name that was published on or before the date you specified. A dev team at the bank implemented this and people really loved it.
2. Versioning, but more for reasons of space than for having a "blame" feature. People use the same spreadsheet daily/weekly to create a report, so they have to save copies of the reports daily/weekly in case they need to reproduce the calculations from a particular report even though the differences from report to report were just minor tweaks. It wasn't uncommon for me to see spreadsheets that were > 100 MB, copied and saved daily.
3. Better explaining of formulas - you can ask Excel what cells reference another cell and it'll draw a bunch of arrows for you, but it still takes a lot of concentration to figure out why you're getting the number 4 in a cell when its references are many cells deep, spread across several worksheets. It would be nice if there was a clear way of explaining a cell's formula without having to navigate from worksheet to worksheet and actually hand-trace the references. Even collecting all of the references in one place and drawing out a tree of formulas would be an improvement.
Build something that helps bridge the gap between 'wet-lab' biologists and 'computational' biologists. There is an absolutely humongous amount of biological/statistical/genomics data stored in Excel files. Build something that has dead-easy data entry, looks like Excel, but can still easily be accessed by us computational types.
diff in Excel. Against different components, whether cell contents, formulae, cell-associated comments, etc. If you want a headache, try supporting recognition of intracell text formatting.
Rereading, I see this is a "new" spreadsheet application. Well, hopefully then the data format and API's will be less... obtuse.
Interactive, visual interface to same diff functionality.
When I last looked, for Excel there was a product or two going partway in this direction; however, most seemed rather limited, e.g. export the cell contents to text and diff that.
Useful for number crunchers. Also useful for all the people who end up using something like Excel as a glorified table. In the business world, there are endless use cases of people managing documents, requirements, results, etc. in Excel. Providing such a "BeyondCompare" fucntionality for this content would be very useful to a lot of them, (Caution: You might also have to teach them how to use it, including the diff concept. And that could be a very significant bump to try to get over.)
Since Excel is so dominant, that class of people might not be your target market. Nonetheless, I see a good diff type utility as being a real plus.
I also can agree with Zain's comment regarding versioning support.
And, integrated regexp support. I wedged same in to Excel/VBA by defining a reference to Windows Scripting Host (back in 1999 or 2000). Very useful. A lot of problems people deal with in spreadsheets can be greatly aided by decent pattern matching and substitution.
Rereading, I see this is a "new" spreadsheet application. Well, hopefully then the data format and API's will be less... obtuse.
Interactive, visual interface to same diff functionality.
When I last looked, for Excel there was a product or two going partway in this direction; however, most seemed rather limited, e.g. export the cell contents to text and diff that.
Useful for number crunchers. Also useful for all the people who end up using something like Excel as a glorified table. In the business world, there are endless use cases of people managing documents, requirements, results, etc. in Excel. Providing such a "BeyondCompare" fucntionality for this content would be very useful to a lot of them, (Caution: You might also have to teach them how to use it, including the diff concept. And that could be a very significant bump to try to get over.)
Since Excel is so dominant, that class of people might not be your target market. Nonetheless, I see a good diff type utility as being a real plus.
I also can agree with Zain's comment regarding versioning support.
And, integrated regexp support. I wedged same in to Excel/VBA by defining a reference to Windows Scripting Host (back in 1999 or 2000). Very useful. A lot of problems people deal with in spreadsheets can be greatly aided by decent pattern matching and substitution.
As someone who works in an investment bank, it would be great if this new, more powerful spreadsheet played nicely with Bloomberg, Reuters, and Factset.
Smart conversions. If I have a graph of something in Celsius, I have to copy the data, edit it, and create a new graph just to convert it to Fahrenheit. Same with any other unit of measurement. When I was an engineer my biggest gripe with Excel was that it didn't know anything about my data.
An interactive shell (Python, with Excel objects and helper methods ready provided).
Have it interface with Fluidinfo.com. [which should be going alpha sometime soon]
I am a huge fan of Excel. It's the program that I use most for doing work.
At the entry user end, there are so many features that are simply unusable or technically way to difficult. For the power user of Excel, there is very little that can't be accomplished. It's power is basically limitless with VBA (or choose your favorite scripting language). Apart from the comment suggesting larger sizes to the sheets (larger than 1 million rows) and being online (something that would really not fly in most corporations) I can't find a problem in here that can't be accomplished with Excel and a firm knowledge of scripting for it. This may be a cop-out though as you must actually script the stuff yourself. This, however; is why I love this program so much.
So as for building a more powerful spreadsheet program for the power user market, it's going to be very hard for anyone to produce something that does more because it's already basically limitless.
For the novice user though, the program is convoluted, confusing and extremely limited. The novice user also represents a much bigger market. I have had several jobs simply because people couldn't do things in Excel that I assumed a monkey could do. Companies love Excel even though 99% of employees at them have no idea how to do basic things with it (summing columns for example). Giving some of the 99% of employees a program that they can do basic to intermediate things without having the limitless back end scripting power would put me out of many of my jobs.
Excel at the start is like a country kid visiting the big city for the first time. There is just way to much power in it and the map for getting around is far too confusing, but once you've been living there for a while everything about it becomes a breeze. Making that adjustment easier would be a huge benefit.
On a side note I enjoy using Google Spreadsheets but one of my top wishes is that Google, or someone, would implement either a Google scripting language or a allow for other scripting languages (Ruby, Python, VBA) to be used.
At the entry user end, there are so many features that are simply unusable or technically way to difficult. For the power user of Excel, there is very little that can't be accomplished. It's power is basically limitless with VBA (or choose your favorite scripting language). Apart from the comment suggesting larger sizes to the sheets (larger than 1 million rows) and being online (something that would really not fly in most corporations) I can't find a problem in here that can't be accomplished with Excel and a firm knowledge of scripting for it. This may be a cop-out though as you must actually script the stuff yourself. This, however; is why I love this program so much.
So as for building a more powerful spreadsheet program for the power user market, it's going to be very hard for anyone to produce something that does more because it's already basically limitless.
For the novice user though, the program is convoluted, confusing and extremely limited. The novice user also represents a much bigger market. I have had several jobs simply because people couldn't do things in Excel that I assumed a monkey could do. Companies love Excel even though 99% of employees at them have no idea how to do basic things with it (summing columns for example). Giving some of the 99% of employees a program that they can do basic to intermediate things without having the limitless back end scripting power would put me out of many of my jobs.
Excel at the start is like a country kid visiting the big city for the first time. There is just way to much power in it and the map for getting around is far too confusing, but once you've been living there for a while everything about it becomes a breeze. Making that adjustment easier would be a huge benefit.
On a side note I enjoy using Google Spreadsheets but one of my top wishes is that Google, or someone, would implement either a Google scripting language or a allow for other scripting languages (Ruby, Python, VBA) to be used.
Think SAS. Their customers have been answering this exact question for a very long time. Give me R with a real GUI and a text editor so I can inspect the CRAN package that tells me 2 + 2 = 3.9999995559
Does R Commander fit your needs?
http://socserv.mcmaster.ca/jfox/Misc/Rcmdr/
http://socserv.mcmaster.ca/jfox/Misc/Rcmdr/
not even close. That was a quick slap-together for an undergrad class. And the look and feel is just painful. SPSS would be a better model.
[deleted]
I do not use Excel regularly and I thus am certainly not a power user. However, I would think scripting for plots would be useful (like I do in matlab or matplotlib) for repetitive, highly configured, high quality plots with the ability to easily export them as ps or pdf. This may very well be possible now (and I'm just ignorant), but that would be one thing that would be handy that I have not seen.
1.) automatic smart chart creation - the spreadsheet guesses the kind of data I'm inputting and builds a chart on the fly.
2.) UI that values datasets over data points, or some sort of functionality that defines a dataset. Since most tasks deal with sets as a whole (and not individual points) this would result in a much cleaner, quicker interface. Also, you could start treating a dataset like a black box instead of a TON of cells with meaningless value, and thereby gain access to a lot of shortcuts not possible currently. This should allow easy data entry, dataset searching, and will keep all your scripting in your view. Best of all, there's no need to manipulate cells at all with a dataset approach.
2.) UI that values datasets over data points, or some sort of functionality that defines a dataset. Since most tasks deal with sets as a whole (and not individual points) this would result in a much cleaner, quicker interface. Also, you could start treating a dataset like a black box instead of a TON of cells with meaningless value, and thereby gain access to a lot of shortcuts not possible currently. This should allow easy data entry, dataset searching, and will keep all your scripting in your view. Best of all, there's no need to manipulate cells at all with a dataset approach.
Freeze multiple, unconnected columns and rows.
That way I could view the descriptions at the top and the sums at the bottom while I'm scrolling through any point in a spreadsheet.
That way I could view the descriptions at the top and the sums at the bottom while I'm scrolling through any point in a spreadsheet.
You can't tell whether you got the right answer.
My rule of thumb is that any interesting spreadsheet has a mistake. Anyone who uses a spreadsheet and doesn't independently know the answer "close enough" is living in a fool's paradise.
My rule of thumb is that any interesting spreadsheet has a mistake. Anyone who uses a spreadsheet and doesn't independently know the answer "close enough" is living in a fool's paradise.
1. Really, really great looking graphs. Excel fails so hard at this it's unbelievable.
2. Easy navigation. The giant spreadsheet model is a very simple metaphor but sometimes I'd like a way to jump to different parts of it more easily.
3. Not be a spreadsheet. The one-big-sheet model might be better represented as a bunch of smaller tables floating in space with formulas interconnecting them. The letter/number convention has been around since the very first spreadsheet, surely we can do better.
4. Understand the internet. RSS, live stock quotes, etc. The address of a cell ought to be internet-compatible somehow, e.g. http://myspreadsheets.com/daily_report/C2 or http://myspreadsheet.com/daily_report/mynamedtable?x=Jan2007.... Google Docs does something like this, I think.
5. Allow scripting in a language that doesn't completely suck. Javascript or python would be good choices.
2. Easy navigation. The giant spreadsheet model is a very simple metaphor but sometimes I'd like a way to jump to different parts of it more easily.
3. Not be a spreadsheet. The one-big-sheet model might be better represented as a bunch of smaller tables floating in space with formulas interconnecting them. The letter/number convention has been around since the very first spreadsheet, surely we can do better.
4. Understand the internet. RSS, live stock quotes, etc. The address of a cell ought to be internet-compatible somehow, e.g. http://myspreadsheets.com/daily_report/C2 or http://myspreadsheet.com/daily_report/mynamedtable?x=Jan2007.... Google Docs does something like this, I think.
5. Allow scripting in a language that doesn't completely suck. Javascript or python would be good choices.
Amen to the end of one-big-sheet. Apple seems to have addressed that: http://www.apple.com/iwork/numbers/
(I don't know if their version is any good)
(I don't know if their version is any good)
I'm still waiting for an Excel-like program that can make graphs as pretty as R.
One interesting model of where spreadseets may go is Resolver, at http://code.google.com/p/resolver/ or http://www.resolversystems.com/. Most of the issues complained about here can be solved through the Python back end.
It seems to be an elegant tool. But as I see it, it deviates from the core value of spreadsheets, which is putting computing power in the hands of non-programmers. Most spreadsheet users will balk at learning Python.
They must be very good, if they are expected to come up with something within a few months that will be able to compete with Google Docs, Microsoft Office and Open Office.
I can't wait to see it.
I can't wait to see it.
Separation of data, formulas, and graphs (a la MVC). It shouldn't be a mystery to me whether a number is data I've entered, or a result of a computation.
It should also have a real solver, so I don't have to work out formulas on paper first. If I can type in some equations (legibly) and it's possible to derive the data I want from the data and formulas I've given, then just do it.
(I understand Lotus Improv got this right. In fact, from what I've read about it, Lotus Improv got many things right. A modern clone of Improv would be cool.)
Also, it should know units. If my search engine can do unit conversions on the fly, my spreadsheet ought to be able to.
Better graphing. I'm not sure exactly what I want, but every time I try to make a graph in a spreadsheet, I end up with something that doesn't look at all like I wanted.
N.B., I've basically given up on spreadsheets. For lists, I use an outliner. For simple math, I use a HLL like Ruby or Lisp or Octave. I suspect if there was an OmniOutliner for Windows, Excel would die overnight.
It should also have a real solver, so I don't have to work out formulas on paper first. If I can type in some equations (legibly) and it's possible to derive the data I want from the data and formulas I've given, then just do it.
(I understand Lotus Improv got this right. In fact, from what I've read about it, Lotus Improv got many things right. A modern clone of Improv would be cool.)
Also, it should know units. If my search engine can do unit conversions on the fly, my spreadsheet ought to be able to.
Better graphing. I'm not sure exactly what I want, but every time I try to make a graph in a spreadsheet, I end up with something that doesn't look at all like I wanted.
N.B., I've basically given up on spreadsheets. For lists, I use an outliner. For simple math, I use a HLL like Ruby or Lisp or Octave. I suspect if there was an OmniOutliner for Windows, Excel would die overnight.
Emacs key bindings please.
Maybe a better contribution would have been to say, "highly customizable keyboard navigation, to make using it mouseless possible"
I agree.
As others have written, some of the things I've longed for in Excel are found in Numbers -- they're also available in Ragtime: the ability to place multiple spreadsheets independently in a single document and work on full ranges within them. Ragtime adds rotation of spreadsheet objects.
I prefer lightweight databases to spreadsheets, so my suggestions would be similar to what FileMaker says: http://www.filemaker.com/articles/database/new_database.html
In short: make it easy to present a set of information through different views, make it easy to share between users, make it easy to create and maintain complex data structures and validation. Presenting information from the spreadsheet through the web, with or without edit capability, would be good.
I prefer lightweight databases to spreadsheets, so my suggestions would be similar to what FileMaker says: http://www.filemaker.com/articles/database/new_database.html
In short: make it easy to present a set of information through different views, make it easy to share between users, make it easy to create and maintain complex data structures and validation. Presenting information from the spreadsheet through the web, with or without edit capability, would be good.
[deleted]
Disclaimer: not particularly a power user. Problem also probably more of a UI/UX issue than a technical issue.
From time to time when I'm working in excel I'll find I've derived an answer from a couple adjacent input cells and several intermediate calculation step cells below. As a contrived example, lets say I have pennies per number of hours and I want to convert to dollars per year. I'll start with hours per year (365.25*24) and divide that by the input "number of hours". Then I'll divide the input "number of pennies" by 100 to get dollars. Then I'll multiply the two together to get my output.
Without fail after working through a problem like this I will want to make a 2-D grid with an input variable on each axis and have the results filled into the grid. Unfortunately because I just did it using a bunch of intermediate cells I can't copy and paste, drag and fill, or any of the other intuitive mechanisms. To date the only solution I've found to this problem is to re-do all of my work in VBA as a single function and use =myFn(colval,rowval) in each cell.
There are a couple ways this could play out. One of which would be to call subordinate sheets (or chunks of sheets) as functions. This is how I've envisioned it in my head. It would be a terrible pain to make that work, and I'd be afraid it would confuse people who wouldn't understand which cells worked normally and which cells were function components.
Another solution would be to select an output cell and have it refactor it to a function that could be called. This is probably a lot easier to do, but might not be as maintainable by the kinds of people that don't understand VBA -- they could keep the "source code" cells around and recompile when changes are made, but all of the standard code generator / manual edit problems apply.
From time to time when I'm working in excel I'll find I've derived an answer from a couple adjacent input cells and several intermediate calculation step cells below. As a contrived example, lets say I have pennies per number of hours and I want to convert to dollars per year. I'll start with hours per year (365.25*24) and divide that by the input "number of hours". Then I'll divide the input "number of pennies" by 100 to get dollars. Then I'll multiply the two together to get my output.
Without fail after working through a problem like this I will want to make a 2-D grid with an input variable on each axis and have the results filled into the grid. Unfortunately because I just did it using a bunch of intermediate cells I can't copy and paste, drag and fill, or any of the other intuitive mechanisms. To date the only solution I've found to this problem is to re-do all of my work in VBA as a single function and use =myFn(colval,rowval) in each cell.
There are a couple ways this could play out. One of which would be to call subordinate sheets (or chunks of sheets) as functions. This is how I've envisioned it in my head. It would be a terrible pain to make that work, and I'd be afraid it would confuse people who wouldn't understand which cells worked normally and which cells were function components.
Another solution would be to select an output cell and have it refactor it to a function that could be called. This is probably a lot easier to do, but might not be as maintainable by the kinds of people that don't understand VBA -- they could keep the "source code" cells around and recompile when changes are made, but all of the standard code generator / manual edit problems apply.
I have not read all the comments since there are too many of them. Here is my wish list(I am an ex- Mgmt consultant) without knowing if its already covered in the comments:
1) Creating a new tab/sheet and choosing if the tab should be a document or spreadsheet type so that I can run my numbers once and reference my table in the document and not worry about cutting and pasting. Really helps reduce word document and report writing. This is a fundamential mind set shift as an excel spreadsheet hardly goes on its own. Its always accompanied by a document and syncing the numbers between the report/tables and the excel is a massive PITA.
2) Importing some functionality of Access (running unique/group by queries, inner join/outer join etc...) in Excel. Helps me run complex data queries while maintaining data integrity.
3) A wiki like online spreadsheet. (dont think Google Docs does this. For e.g. if we are running a valuation for a company and each team mate has different assumptions for each variables, this can help team mates input thier assumptions and track it and see the overall impact on the numbers. Instead of the current model of emailing spreadsheets back and forth or erasing new data over old ones (e.g. like Google Spreadsheets). Btw- I strongly believe just implementing this last point alone and narrowly defining yourself as a model builder (with wiki like enterprise capabilites) would generate strong traction in I-Banks, Buy side firms, sell side research firms, market research companies, mgmt consulting firms, insurance companies etc...
Would love to try out a beta version if available. my email is raonikhilesh "@" gmail
Would love to try out a beta version if available. my email is raonikhilesh "@" gmail
OLE is a joke, if this is a client-side application add embeddable spreadsheet widgets.
Pivot-tables would be nice as a core function to model data (see quantrix.com)
Make 'light databases' part of the architecture and not counter-design. something like, spreadsheets meets csv with a cli query component or similar.
Pivot-tables would be nice as a core function to model data (see quantrix.com)
Make 'light databases' part of the architecture and not counter-design. something like, spreadsheets meets csv with a cli query component or similar.
1. It should be possible to turn a sheet into a function. So I can then define my own 'inputs' and 'outputs' for that sheet, and 'call' it with whatever parameters I like from another sheet. Sure it can be done in code, but for building large, maintainable models that's not practical.
2. 'Hard' and 'Soft' numbers (i.e., numbers I manually enter vs. numbers that are the result of a formulae) should be automatically coloured accordingly if I want them to be. Manually plugging in a number on top of a formula should not necessarily over-write the formula. It's quite common I want to preserve the logic but 'hack in' a variable. Instead, it's either or (and I have to copy/paste the formula into a comment for the cell, which is a pain and clumsy as hell).
3. Track changes.
4. Non-euclidean topology ... by which I mean, making it possible to vary the number of colums / rows and column / row sizes within a single sheet.
2. 'Hard' and 'Soft' numbers (i.e., numbers I manually enter vs. numbers that are the result of a formulae) should be automatically coloured accordingly if I want them to be. Manually plugging in a number on top of a formula should not necessarily over-write the formula. It's quite common I want to preserve the logic but 'hack in' a variable. Instead, it's either or (and I have to copy/paste the formula into a comment for the cell, which is a pain and clumsy as hell).
3. Track changes.
4. Non-euclidean topology ... by which I mean, making it possible to vary the number of colums / rows and column / row sizes within a single sheet.
Instead of the programming angle, I would like a user-interface that works for right-brained people and doesn't drive you bonkers after spending more than a few hours on it. That would be a blue ocean in the world of spreadsheets.
For conventional programming I've been working on laying the logic out as a graph automatically. Instead of cryptic formulas in the cells, you would have an easy to understand graph that shows how information is related and computed. In addition the intermediate results for selected input values could be shown next to the nodes and links, to allow inspection of the computation.
This could be adapted for the spreadsheet concept. Actually, it might be more appropriate there.
This could be adapted for the spreadsheet concept. Actually, it might be more appropriate there.
FWIW, I would often query a database and make completely de-normalized datasets that I'd paste into Excel. Then I would pivot the crap out of them and figure out how I wanted to slice the data...
The ability to do regex matches to cells, not just full exact text with a case sensitive/insensitive option.
Please do pivot tables
I'm surprised that no one else has mentioned EditGrid. For me, the user experience with EditGrid has always been better than the user experience with Google, and EditGrid has always had more and better features as well. See:
http://www.editgrid.com/tnc/pkchan/EditGrid_v._Google
The thing Excel doesn't do as well as I'd like is Text to Columns, specifically for 13F filings. Many people like to track what major investors are buying and selling and would like to do this directly, from SEC filings, instead of through websites like Gurufocus.
For example, here's the Gurufocus page for Seth Klarman:
http://www.gurufocus.com/ListGuru.php?GuruName=Seth+Klarman
You can also find the data for his investment firm, Baupost Group, at SEC Info:
http://www.secinfo.com/$/SEC/Filing.asp?T=1ZCS7.t61_1wt
Clearly, it's possible to automatically get the data from Baupost's freely available SEC filings (http://www.sec.gov/Archives/edgar/data/1061768/0001061768080...) into a spreadsheet. It's just that Excel seems to make it a lot harder than I'd like.
http://www.editgrid.com/tnc/pkchan/EditGrid_v._Google
The thing Excel doesn't do as well as I'd like is Text to Columns, specifically for 13F filings. Many people like to track what major investors are buying and selling and would like to do this directly, from SEC filings, instead of through websites like Gurufocus.
For example, here's the Gurufocus page for Seth Klarman:
http://www.gurufocus.com/ListGuru.php?GuruName=Seth+Klarman
You can also find the data for his investment firm, Baupost Group, at SEC Info:
http://www.secinfo.com/$/SEC/Filing.asp?T=1ZCS7.t61_1wt
Clearly, it's possible to automatically get the data from Baupost's freely available SEC filings (http://www.sec.gov/Archives/edgar/data/1061768/0001061768080...) into a spreadsheet. It's just that Excel seems to make it a lot harder than I'd like.
I'd very much like if I could right click on a chart in excel and select "get GoogleChartsAPI url".
That'd be sweet.
That'd be sweet.
Excel's core functionality is great, but many of its extended functions are hard to use and/or limited. In addition to adding functionality, you could revamp some of the functions that excel currently doesn't do well. My list would be:
1. Multi-user experience - Better workbook sharing and editing.
2. Better scripting (already mentioned above)
3. More than 65536 rows!
4. Queries/Lookups - It may be that I'm not proficient enough, but it seems like VLOOKUP and pivot tables can be done A LOT better.
FYI our old company was considering buying a product to extend excel functionality. Probably a possible competitor. Check it out: http://www.businessobjects.com/product/catalog/xcelsius/
1. Multi-user experience - Better workbook sharing and editing.
2. Better scripting (already mentioned above)
3. More than 65536 rows!
4. Queries/Lookups - It may be that I'm not proficient enough, but it seems like VLOOKUP and pivot tables can be done A LOT better.
FYI our old company was considering buying a product to extend excel functionality. Probably a possible competitor. Check it out: http://www.businessobjects.com/product/catalog/xcelsius/
I think something that mixes structured and tabular data together would be great. If you had a worksheet style
"dynamically typed" structure that was supplemented by some structured tables of data that could be easily referenced and updated by Excel style formulas, it would be very useful. In this way the spreadsheet sits as a layer on top of several database tables that can be shared between users. I sort of imagine the ultra-flexible spreadsheet on one monitor and this floating collection of data tables on the other one. It's a hybrid.
1. create a Table and then have an option to create the corresponding mySQL. (Relation DB Design)
2. create a mySQL Dump from the Table Data.
3. Other cool MySQL Optimizations.
2. create a mySQL Dump from the Table Data.
3. Other cool MySQL Optimizations.
I want access to whatever data is inside the spreadsheet. Currently Excel locks away your data unless you use a library of some sort that may not necessarily be 100% accurate, or capable of a specific feature.
Likewise, generating spreadsheets should be easy for 3rd parties.
Likewise, generating spreadsheets should be easy for 3rd parties.
Excel on the Mac really sucks and I don't really want to install Windows just for that.
I worked in a consulting firm that had built its entire business on Excel "apps", heavy, clever VBA-driven rich GUI interface spreadsheets applications that were replicated time the number of people in the client organization. While a bit wonky, and web-naive in way, these app's proved to be surprisingly resilient and flexible for "lowly" Excel (lowly only in the hierarchy of "legit" programming environments). From this experience, I would say that what would have been a profoundly useful tool, would be a software conversion tool, that could capture some of the logic, or UI of such "apps" and webify them, or at least convert some of the syntax into better object oriented code. This is of course a tall order, if not impossible. The dream of automating quality code may be nigh impossible, but it would be a huge business value even if executed only in part.
An alternative question might be: what are you doing in Excel today for which Excel was not intended?
I remember Wil Shipley (of Omni, Delicious Monster) talking about how they originally developed OmniOutliner after noticing that most people were using Excel to make simple lists rather than complex spreadsheets, so there was a market for something less expensive than Excel and more tailored to the things people were actually doing with it. With a product as broadly installed as Excel I'm sure there's still under-served niches.
I remember Wil Shipley (of Omni, Delicious Monster) talking about how they originally developed OmniOutliner after noticing that most people were using Excel to make simple lists rather than complex spreadsheets, so there was a market for something less expensive than Excel and more tailored to the things people were actually doing with it. With a product as broadly installed as Excel I'm sure there's still under-served niches.