Ask HN: What can't you do in Excel?

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.

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.

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

How about this: http://www.bygsoftware.com/Excel/SQL/UsingSql.html

Or: http://www.exceluser.com/explore/msquery1_1.htm

did you mean SQL? You can connect to a .xls via ODBC

1 million rows??? Who wants to scroll through that mess?

"more than ~1 million rows."

Grow up and use an actual database.

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.

Did you try Sharepoint? It does versioning, version control and a bit of collaboration for Excel.

Dropbox?

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.

Turing-Machines are Turing-complete, too.

  HAI
  CAN HAS STDIO?
  I HAS A WITTEH
  I HAS A KARMA
  
  IZ WITTEH BIGGER THAN "O RLY"?
    YARLY
      UP KARMA!!1
    NOWAI
      DOWN KARMA!!1
    KTHX
  KTHXBYE

  BTW "On-Topic: Anything that good hackers would find interesting."
  BTW http://lolcode.com/

Can you prove it? (Not using scripting, of course.)

I thought number 5 was already possible in excel :/

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

2. The next version of Excel will be online. (http://en.wikipedia.org/wiki/Microsoft_Office_14)

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

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

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)

(1) can be done with Visual Studio Tools for Office: http://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office

Gnumeric is programmable in Python

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

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

Recursion is fun, but how would you use it in a spreadsheet?

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.

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.

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

Apple Numbers allows you to simple say =Sum(C) to sum up an entire row.

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.

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

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.

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.

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.

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.

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.