Hacker News Reader

Comment by crazygringo | Hacker News Reader

Obviously, Excel isn't exactly at fault -- it's the people using it, as echoed by many comments here.

HOWEVER, spreadsheets are rather unique in the fact that they hide the very formulas used to generate everything, unless you have a specific cell selected.

So, opposite of computer programs, they're almost impossible to "read through" and verify that everything makes sense. If 99 cells have identical formulas, and 1 in the middle has a slight typo, how on earth are you ever going to see that?

I wonder if there are other similar spreadsheet-like tools that one might use, that make a point of showing everything going on in the spreadsheet, rather than trying to hide it?

View on HN

> If 99 cells have identical formulas

If we want to enter 99 identical (or relatively identical) formulas into 99 cells, then we should be able to select all 99 cells, and enter the formula once without any dragging. If we want to change that formula, we should be able to change it once and not need to remember to redrag. We should be able to enter a formula into the column heading and know it'll apply whenever relevant to every cell in a column. That we can't means Excel (and Lotus and OpenOffice) is defective.

What QA is to blame for isn't letting unfinished spreadsheets out the door, but rather for letting users in a company use defective tools like Excel and Lotus and OpenOffice in the first place.

Many of us developers learnt the hard way that visual programming tools from the 1990's don't work, which is why scripting languages became popular. Users in corporations (and the IT staff who authorize what tools that can use) need learn the same lesson.

> we should be able to select all 99 cells, and enter the formula once without any dragging.

Agreed. You can do this by creating your own macros, but it's a pain. Considering how often I do this for personal spreadsheets, you'd think that this would have been simplified long ago.

And, this deficiency has been ported to both Numbers and Google spreadsheets.

then we should be able to select all 99 cells, and enter the formula once without any dragging.

Double-click the fill-handle (thing you click on to drag) and Excel will fill-down as far as the next empty row.

If we want to change that formula, we should be able to change it once and not need to remember to redrag.

How would it know whether you want to change it in one place for a special case, or update many places?

"That we can't means Excel (and Lotus and OpenOffice) is defective"

You can complain about the UI (I certainly do. For example, try inserting a row in an array formula), but the feature is there: http://office.microsoft.com/en-us/excel-help/introducing-arr...

After reading your comment, I did some googling to find something that could convert an existing spreadsheet into code. I found a few commercial solutions that turn Excel into C++ or Java. I think the idea is that Excel becomes the prototyping tool for non-coders. That seems like a better idea than treating Excel as the final product. I feel like there's a new paradigm for both spreadsheets and programming in there somewhere but I need a bigger brain to see it.

Some sort of http://en.wikipedia.org/wiki/Reactive_programming to the masses would be nice.

I suspect the reason those commercial solutions aren't very prominent is that they don't work very well. Spreadsheet compilation is a harder problem than it appears to be.

Related to this is that there are no descriptive variable names either. Just a massive three dimensional grid of, non typesafe, memory indexed by letter and number: sheet/row/column. Imagine programming like this:

    A5 = (C6*C6 * Sheet5:D1 + Sheet6:D1 / B3)

I'm not saying you have to use excel like this, there are options to do for example Sum(myTable[myRow]) and pivottable reduces alot of manual equations also. However both of these alternatives require that you format everything nicely into one table per sheet like a proper database and use the "Format as table" option, you almost need a DBA to use excel properly! The problem is that normal people don't use excel like this, they paste random data around everywhere, they want to have the sum, the average and lots of other calculations both below and on the right side of the data so you can see both simultaneously but once you do this you've screwed up your table and we're back on square one.

Related to this is that there are no descriptive variable names either.

Sure there are; You can add a name for a cell or range of cells (and have been able to since at least Excel 97):

http://excelsemipro.com/wp-content/uploads/2010/09/Named-Con...

The software should have pattern hints.

If you've got 99 cells with identical formulas, and one without, it's not far fetched for Excel to speculate that there may be a mistake (particularly if they're within X% of being identical). At which point it can provide a simple, non-intrusive hint.

And going further up the wishful thinking tree, into more difficult territory, Excel should be able to grasp what you're attempting to do by understanding, say, the top 10,000 (arbitrary number) tasks commonly performed by people using spreadsheets. That 'understanding' could then tip Excel toward deciding whether there's a problem, and whether it should leave you a nice little illuminated icon at the top right of the program with an exclamation mark.

Microsoft should have insane amounts of data on everything people do with spreadsheets, and they should be able to make it radically more intelligent (without being obnoxious or intrusive - it should be optional and highly passive).

Excel does give hints when formulas are out of pattern. It's called "Inconsistent Formula" and it's displayed with a little triangle on the box.

It already does this, actually. Enter some data in two columns, like:

Then enter a sum formula in the next column like =SUM(A1:B1) and drag it all the way down. Change one in the middle to something else, like =SUM(B3:B3) instead of =SUM(A3:B3) and a little green arrow appears in the corner. Hover above it an it explains there is an "inconsistent formula".

Spot that triangle among the million cells in a 1000 by 1000 table.

Click the "take me to possible formula errors" button.

http://www.c-sharpcorner.com/UploadFile/7e39ca/error-check-f...

Excel does have that. Exactly that.

You can actual show formulas in excel, instead of the values.

But that's not to say that it'd be easy to spot the one that is different. This is why excel has a little tooltip that pops up and says 'inconsistent formula' when it finds a cell that doesn't seem to belong.

> The software should have pattern hints.

I think a better idea would be to scrap the datasheet concept completely. Make people use a dataflow programming language, it will give you:

a) versioning of source code with standard tools

b) separation of inputs and process

c) program source that doesn't look like a game of battleship unless who wrote it was disciplined

d) strong typing

But it won't happen, god forbid we are faced with an initially steep learning curve.

HOWEVER, spreadsheets are rather unique in the fact that they hide the very formulas used to generate everything, unless you have a specific cell selected.

Unless you click the button to "show formulas" in Excel, then you can view them all at once.

http://i237.photobucket.com/albums/ff263/dharma-putra/Excel2...

http://www.lytebyte.com/wp-content/uploads/2008/04/view-form...

No use if the worksheet has thousands of rows.

Reply on news.ycombinator.com