Top 10 list (XP+)

Topic

New Reply

Where is David Letterman when you need him?

(For those not knowing who he is, Dave is a late-night TV talk show host and comedian who has a feature where he provides a top-10 list for … you name it – usually done with a comedic twist.)

Anyway, I’ve been asked to put together a top 10 list for Excel. The request was not very clear, as you’ll see in a moment from looking at my distinctions in answering the question. Since my uses of Excel are somewhat limited (hardly ever do things that need Pivot tables and graphs are not much more frequent), I thought I’d ask one and all to contribute.

To limit this so it doesn’t go on forever, I’d like to see if we can close the voting booths in a day or 2 from this post (maybe the moderaors can help out here by locking the thread after some suitable time). I’ve searched both here and thru google and didn’t find anything useful.

I’m thinking the request can be either discuss:
– what are Excel’s most important features (charts, pivot tables, wide range of built-in functions, fill, flexible formatting (including conditional formatting), filtering, sorting, data validation, scenarios, goal seeking, lookup tables, etc.)
OR
– what kind of “applications” can Excel be used for (teacher grading sheets, business modeling, tax calculations, mortgage payoffs, etc.)

Obviously the lounge has had lots of people asking about how to use various features and many times it has been in the context of their own application which has also been stated.

So if you’d like to contribute, provide a few things (ranking them if possible) that
– you think are the most important features of Excel: you use them a lot in many of the spreadsheets you build, or it would be difficult to present whatever information you need to show without the feature, or the feature makes it very easy to present the info
– describe what applications you use Excel to build (and, perhaps, there’s no pgm other than a spreadsheet pgm that would do the job) – in this case, it might also be useful to mention the few key features that make this application really work well because of using Excel (or a spreadsheet) to do it.

Contributions to either or both lists are appreciated.

Other than saying that Excel has a rich object model to extend the user interface, I’d prefer to leave anything dealing with VBA out of this.

TIA to all

Fred

Reply | Quote

Replies

A hard question to answer Fred.

Probably due to the versatility of the program.

In essence it is number crunching program with which you can easily do calcs and inspect different scenarios/situations.
To ease that, Excel has a huge built-in functionality to get data into the model and lots of functions to do your computations.
To present the results, there is a lot of formatting you can do and the charting helps out greatly too.

That being said, I would not know where to begin to state what it is used for. Anything ranging from creating shopping lists to Banks analysing their business (or even running mission critical process handling!)

In chemical engineering it is used to analyse experiments, build and compute process models and who knows what else.

But I think the financial industry (banking, accounting, insurance) is the largest chunk where Excel is used intensively.

Reply | Quote

I use Excel most in money-related applications.
But because some of the sheets and output will be seen by real people, I think its ability to generate good-looking output is a big plus.
One contribution to this is to make the result of lots of formulas display as blank cells rahter than error messages if the formula can’t work because no or wrong data available.
Two ways of achieving this are by specifying an ‘If’ condition, or by conditional formatting.
Negative vibes: not wiysiwyg, and not enough columns per worksheet for one per year.
To add to the thread, here are my top x formulas…
countif, sumif, sumproduct, iserror, max, min, pmt, fv, I’d hate to put them in order.
Note not an array formula in sight, I suppose the danger of only ‘knowing what you know’ is that we’re forced into inefficient and clunky ways of achieving the desired result.
Or to come here and get not just wised up but also cheered up from knowing that the world does contain folk who go out of their way to help others, all for free.
End of positive rant.

Reply | Quote

>>top x formulas…
ignoring as read the usual arithmetic operators!
Plus the big big big omission of vlookup.

Reply | Quote

[indent]

---

…but also cheered up from knowing that the world does contain folk who go out of their way to help others, all for free.

---

[/indent]
Well – there’s the Glory, of course…

It is difficult to come up with the top ten strengths or features of Excel – it’s like asking for the top ten features of an incredibly well-equipped tool shop: it all depends on what you need to do. In general Excel is exceptionally strong wherever you need to work with numbers – storing, manipulating, or presenting numeric data or results: obviously that includes financial applications, but it extends to engineering, systems analysis, science labs, statistics, and on and on – one developer has used Excel to run engineering design and even produce drawings by running Visio through VBA. Excel is pretty strong whenever you have lists – it is a rough and ready database; not the best, but certainly the most widespread.

I think that the strongest “feature” of Excel is its sheer ubiquity, driven by its flexibility: while it is the best of breed as a spreadsheet, it is only a reasonable tool for many other purposes – but it is there, it will work “well enough,” and the user is reasonably familiar with it (and knows that his audience will have a copy) – so it becomes the tool of choice. Eventually a little ecosystem of specialized users and developers huddles around Excel, and they create the add-in and attachments and refine the approaches that make it a better tool for that particular operation than it was before. For an example, look at the number of Monte-Carlo or statistical add-ins for Excel, or genetic solver add-ins, engineering add-ins, and so on. All that said, MY top-ten list would look something like this:

1. user friendliness – Excel is approachable – anyone can start it up, and have at it. Now, that leads to some really stupid uses: I once had a client who used Excel as an expensive, specialized word-processor, since it did numeric alignment so nicely. This client entered invoice line items into an Excel spreadsheet, put the item cost in the next column, then calculated the total and entered it at the bottom of the invoice… she was quite surprised to learn that the computer could do that for her – without mistakes!
2. reasonable WISIWYG – it might be nice if Excel was exactly WYSIWYG – but I am not sure if the extra processing cycles would be worth it. As it is, I have a pretty good idea what my output will look like as I work, and a very good idea if I use print preview – it is pretty rare that the output is so different than what I see on screen that it requires more than some minor tweeks to get it right, and the output is pretty good quality.
3. financial functions – I do economic evaluation of large infrastructure projects, typically (although not exclusively) in the energy sector (pipelines, powerplants, gas processing plants and the like). All of these evaluations rely on the time-value-of-money calculations in Excel. Because my background is in finance, virtually every spreadsheet I have ever dealt with for any length of time has used these functions – and while Excel is used for many other things, I suspect financial applications are still the largest singe user segment
4. pivot tables – these allow users to extract data from relatively large data sets easily, intuitively, and quickly. They are extraordinarily powerful, but dramatically under-used. I have one file that lists hourly transaction prices for spot energy sales for a four year period (roughly 44,000 data points, with three elements per point). By being careful with the data format (which requires some massaging from the format the ISO provides it in) I am able to use the data as input to a pivot table, and I can then summarize and extract data – typically daily pricing profiles by hour – fairly quickly (it takes some re-calc time on my slow old computer), but with very little effort; I just click the buttons and wait a couple of seconds!
5. array formulas – like pivot tables, array formulas are incredibly powerful – but they seem more difficult to understand, or at least are less intuitive. Once a user begins to use them, though, they can provide a compact way of extracting information that either just isn’t available, or would take many steps to obtain
6. extensibility – it’s nice to be able to write a UDF when required, or to automate some tasks, but I am thinking more of the value of third-party developers having access to Excel’s object model. There is no way that I could write a VBA routine do do the sort of things that Palisade does with @Risk – not only is it beyond my abilities, I could never justify the time and expense for my use of the functionality even if I could do it for myself. Since Excel allows such easy access to the object model and can be automated with VBA (and calls to dlls written in VB or C, etc) there are any number of add-in and add-ons that can make Excel do things well beyond its “out of the box” capabilities.
7. pretty good display – here I am thinking of the charting abilities – sure, it is possible to use dedicated graphics packages that will produce truly stunning output – and if I was publishing the annual report for IBM or DuPont, or an illustrator for Time Magazine, that’s what I would use. But this sort of output quality comes with additional cost and complexity; as it stands, Excel produces pretty good output, pretty flexibly, in a wide variety of chart styles and formats. With a little care, the output is perfectly suitable for inclusion in documents (or the dread powerpoint presentation) or to be handed out “as is” for seminars, workshops, or presentations, even at pretty high levels
8. auto filter / advanced filter – I noted that Excel is a “rough-and-ready” database, and it is features like “auto filter” and “advanced filter” that make it so. It takes a huge leap of faith to make Excel act like a relational database, and a significant investment of time and effort – so significant that it is easier, faster, and more secure to go out and buy a copy of Access (and even cheaper to use the Open Office equivalent). But for managing flat files in a known environment with a familiar interface, Excel is hard to beat. I wouldn’t want to bet my life on it, or even significant amounts of money – but I am perfectly happy to bet the re-certification dates of minor hockey officials.
9. breadth of product offering – several times I have noted that Excel is “pretty good” at a particular task: the variety of things that can be done and the flexibility of the format are just astounding. Excel is like a Swiss Army knife – it may not be the best tool in the entire toolbox for a particular task, but it is the best tool to replace the entire toolbox for an unknown task. Okay – so I only got to nine; so sue me!

Reply | Quote

Dean and others,

Thanks to all for the thoughts that went into this. I’m makinging my list (and checking it twice).

>Okay – so I only got to nine; so sue me!

#10: analyze potential gain from law suit for short changing us 1 item versus lawyer cost.

Actually we don’t need Excel for that. Everyone knows the lawyer fees will out weigh any gain from any law suit.

Fred

Reply | Quote