What is spreadsheet useful for? [closed]

Question

I have been in computer business for 15 years in various roles (sysadmin, developer, researcher), and I have never encountered someone using excel for something more advanced than for formatting tables, or as an ad-hoc database that could have been maintained in a text-file.

I had to do heavy data-processing and plotting and for that I used some perl scripts + gnuplot, got tiredof it, and went over to R eventually. 2D spreadsheet just didn't seem well-suited for doing statistical analyses over 5-dimensional datasets (not to mention that it produces UGLY plots).

I attempted to use spreadsheet for time-tracking, and found out that I would have better been served by a relational database, so I gave up on using excel for that too. For example, it's important to consistently name tasks, and I needed to find out unique task names in a given column across several sheets (I had one timesheet for each month). How do you make such "query" in a program that essentially evaluates independent cells and has little notion of relations between them?

So, what are spreadsheets useful for? Why do they have a bunch of mathematical stuff built into them when, AFAICT, people use them mostly as table formatters or bad substitutes for databases?

Answer 1

In almost any industry, Excel is a fantastic tool for rapid prototyping and automation.

Even in organizations that have a proper research and development team, nothing beats the ability to work alongside a business user with a spreadsheet to capture and apply important business knowledge, work-flow, and algorithms in real time.

In spreadsheet software, especially one like Excel which has the back end programming support, developers have the opportunity to quickly produce tools with a familiar interface and easily identifiable logic to help users identify their needs and preferences.

Once a fully functional prototype is completed, you have one of the best possible requirements packages, which can either be handed to an IT group so that a permanent, standalone system can be developed to replace it, or run through QA and released as the final solution to the business user's problem.

So after years of providing useful prototypes and in-house solutions in next to no time, bare-minimum cost, and with no additional 3rd party involvement, perhaps a more interesting question would be:

What isn't a spreadsheet useful for?

Answer 2

Spreadsheets, or if you wish to name the best of all of them namely "Microsoft Excel" is without a doubt one of the best tools in the market today. If learn't how to use it properly one can use it from maintaining tables to forms to a simple database to more important stuff like complex calculators.

The most important thing about the spreadsheet is not the spread sheet itself. Of course the spreadsheet alone offers only a table. The most important about spread sheet are the other goodies that come with it out of the box. Things like quickly sorting a table/column on some criteria. Things like performing a multiplication or any other calculation on a row or a column. Performing calculations between two tables. The list goes endless.You can even draw graphs and give some sort of visual presentation to data .The very fact that you can all this without worrying much about the math and programming behind sorting, searching and computing itself makes this tool indispensable.

Another big advantage of spreadsheets or specifically Microsoft Excel are the automation features like Macros. These are amazing things, if you are doing some complex set of operations of large data sets frequently you can automate that.

It takes some time to learn these tools, but its worth learning them. I see my manager using them very efficiently. Actually spreadsheets are programming at an very abstract level. You can do a lot of stuff without writing a actual program which you would have otherwise done by writing one.

I would say that as programmers without showing aversion to any tool in specific we must learn and adapt to any thing that makes our job easier and makes us more productive.

Answer 3

How do you make such "query" in a program that essentially evaluates independent cells and has little notion of relations between them?

You don't. Your design was wrong for that tool. Or that tool was wrong for your design.

Every problem is not a nail. Every tool is not a hammer.

So, what are spreadsheets useful for?

From the tone of your question ("15 years ... never encountered someone using excel for something more advanced than for formatting tables, or as an ad-hoc database that could have been maintained in a text-file") the only answer you want to hear is "nothing".

Spreadsheets are useless. It can be done with a text file.

So, too, are word processors useless. It can be done with a text file.

For that matter, your insightful question has pointed something that everyone seems to be overlooking: All desktop software can be replaced with text files. Really.

Interestingly, however, people persist in avoiding the obviously superior text file and relational database.

I wonder why?

Why do they have a bunch of mathematical stuff built into them when, AFAICT, people use them mostly as table formatters or bad substitutes for databases?

There's a lot of argumentative and subjective built into that question. Which makes it very hard to answer.

Step 1. Google for "Excel Tutorial". Really. It's worth an hour of your time to actually do a tutorial and learn something rather than make a judgement call based on -- well -- working in places where spreadsheets weren't widely used.

Step 2. Google for "Excel Templates". Really. Find a template for a time-tracking system built in Excel. Don't roll your own until you know something about spreadsheets. Use someone else's time-tracking system for a few months. Don't like it? Download another. You should be able to spend several years trying different Excel-based time tracking systems until you (a) find one you like or (b) learn a little something about spreadsheets.

Step 3. Consider for a moment that "an ad-hoc database that could have been maintained in a text-file" may actually have value to people. "I would have better been served by a relational database" may be true for you, but not everyone is so skilled with a relational database. Not everyone likes text editors. Indeed, if you talk to non-professional computer users, you may find that many of them can barely make Excel work. Many of us are not very smart and can't actually edit text files without a lot of help from a tool like Excel.

Answer 4

It just works

Your vision of Excel may not be completely accurate. Have you seen how useful a simple Excel sheet can be in engineering, management, banking or even marketing?

Almost anyone can understand how Excel works by just playing with it few minutes, and immediately get value out of it.

That's why Excel is a powerful tool, ... so powerful it brings a big part of Microsoft's 50 billions yearly revenues.

Answer 5

I have often encountered more complex usage of spreadsheets.

I have worked at two large banks. If the proverb is true that "an army marches on its stomach," then there is a corollary that "a bank crunches with spreadsheets."

You and many readers of this forum have years of experience with both the concepts of RDBMS and the constructs of programming. You may have taken many programming and computer science courses, and have logged hundreds of hours with heads-down programming.

In contrast, most of the people who work on the corporate side of banks have a college degree (usually in liberal arts or business) and perhaps one introduction to programming course.

Spreadsheets provide them a way to program without programming. I have observed Excel as the poison of choice for:

• Pivot tables for summarizing of data
• Sorting and filtering data
• Lookup of information
• Statistical analysis
• Cost/benefit analysis (Pareto)
• Goal seeking
• Charting and graphing
• Financial functions, such as NPV and IRR
• Sharing data

Can you program these kinds of tasks? Certainly. This is what we do.

Can a Business Analyst program these kinds of tasks? Only through a spreadsheet, which the common man's IDE of choice.

Can the Business Analyst get you to program these kinds of tasks for her? I'm guessing you do not want this on your plate, or you have a shortage of people who can do this for you, or you have an onerous business process that would only obtain her result after someone high up commits a pile of money and a team of programmers.

Answer 6

The biggest advantage of a spreadsheet is to be able to change things to give you the What If? ability. I call it Playing with the data, but most corporate types don't like that terminology. Are there differences in sales in different areas? Not really, no sense in asking the IT Department to create a monthly report (You don't get to see the amount of ad hoc requests you've been spared because of the spreadsheet.). Will these data be better represented in bar or line graph?

If it is being used as a stagnant display of data, than it is not much more to you than a word processor in a grid format. It also makes a terrible database. Once someone starts doing the same thing repeatedly, but with a different result set of data, it is time to get a report writer.

Answer 7

Since this is programmers.stackexchange, I'll give you example how spreadsheets are useful to me as programmer in real life scenarios:

• analyze log data using pivot tables, giving me nice statistics that can be data mined;
• using latency data from the server to analyze the curve and figure out what effect improvement will have;
• given server failure probabilities (data mined using spreadsheet), figure out redundancy scenarios if these rates increase or decrease;
• general powerful calculator for complex formulas with possibility of tweaking any parameter at any time;

As for 5-dimensional data, it's just data with vector length of 5. It can be handled by 2-dimensional spreadsheet, as it can be handled by computer with 1-dimensional memory.

Also -- it's not substitute for database nor it claims to be. In fact Excel for example is very often used as front-end for RDBMS data.

Answer 8

I've occasionally used a spreadsheet as an ad-hoc code-generation tool. Say I had a bunch of lines of data, like this list of primes:

2
3
5
7
11

and needed to marry them up with some code, like this

insert into thetable (primenumber) values (##)

except I needed to do it a few thousand times.

I copy the list of primes into the A column of the spreadsheet, and in cell B1 column I write the above sql like:

=concat('insert into the (primenumber) values (', A1, ')')

In B1 will now be

insert into thetable (primenumber) values (2)

I then copy B1 from B2 to B3000. Now I have 3000 lines of sql I can just copy/paste into Sql Mgmt Studio (or your favorite sql too) and run it. Assuming I have the initial data already in a column form (or can quickly make it that way), then I can generate masses of sql code for immediate execution very quickly.

Answer 9

I see nobody has yet mentioned Power Pivot, and it's ability for anyone savvy with Microsoft Excel to effectively produce their own "self-service" business intelligence solutions, slicing and dicing OLAP data to how they see fit without the need of Visual Studio BIDS.

Mixing this up with the already extremely powerful and easy to use Charting system, it's ability to synchronise data with external systems such as SharePoint... you have one of the most powerful data presentation platforms on the market today.

And yes, it's still a Spreadsheet.

Answer 10

Running most investment banks.

Answer 11

They're useful for the same reason people still own hammers even though pneumatic nail guns are available. It's a lightweight tool with a well known interface that's ideal for short jobs. If you've never had occasion to use one in 15 years, you're almost certainly overpowering some problems, like pulling out a nail gun to hang a picture.

The last example I can think of is when we had a few hundred functions that all needed a certain modification. It had to be done manually as it was short but not trivial, we needed to make sure it was done everywhere, and 4 or 5 developers needed to not step on each other's toes while making it. A spreadsheet was quickly made and shared, and people could filter or sort by columns as necessary and sign up for functions by adding their name.

When the work was completed in a couple weeks, the spreadsheet went away. If we did that exact sort of thing all the time, it would have been worth some time to make a more specialized tool, but for something that only lives a couple weeks, a spreadsheet is ideal because it takes almost zero effort to set up and understand.

Answer 12

This is not an answer so much as it is an example. Without giving away too much proprietary information about it, I once held a position with a meteorology department. I used excel in 2 fashions that had nothing to do with displaying tabular data in any fashion. The first was a daily forecast model showing 8 specific areas with accompanying satellite imagery and data analysis. I linked approximately 16 excel spreadsheets to a powerpoint slide presentation and through excel, when a button was clicked, it would update the satellite image as the background of each of the 8 slides, then compute from a downloaded data source the hi/low expected for specific cities corresponding to each of the slides and place this data in separate excel files in specific cells to correspond with the location overlay of those cities on the images. Then it cycled through that same downloaded data and analyzed particular variables to determine weather conditions for each of the areas based on general requirements. It would basically determine for a set of preassigned work types if it was good, cautionary or no good.

The second item I used it for was to analyze and distribute remote weather data downloaded from a satellite feed into an access database. At the time, there were no small implementations of SQL server and this software had to run on a laptop on a wireless cellular modem (which was brand spanking new). The data would be fed directly into the access database. The excel spreadsheet would then (on clicking the form button), go into the database and pull the latest data out and begin analyzing each chunk for each of the 28 remote stations it was responsible for. It would analyze the data for outages, specific outage or anomalous patterns, it would then generate charts/data sheets indicating the weather patterns marked by each of the 28 stations and then a summary report indicating outages and recommended action (leave, service, or replace the unit).

It was a complete pain in the butt to build, but it was very functional and any knuckledragger in the field could use it without screwing it up.

I've also seen massive forms in excel implemented by banks and insurance companies to parse out amortization of loans, depreciation of assets, etc.

Answer 13

I used for quickly capture "data dictionaries" for database apps. I use some formulas to generate the S.Q.L. script, much faster than a diagram tool.

Answer 14

For any given example you gave, and probably for most uses of spread sheets, there's either a better tool available, or you could develop a better one yourself.

However, spread sheets are a good solution for many tasks when you want something done quickly, without the need to search \ buy \ develop a specific tool (note that not everybody is a programmer, and even programmer usually have other tasks)

But with the power of spread sheets comes also a big risk. in many places such an 'ad-hoc' spread sheet which starts as a local initiative turns into a mission critical application for the company, although it's usually not suited for it

• There's no protection from data corruption
• It's isolated from corporate application, usually resulting in duplicate data (and quickly after data that is inconsistent with other applications)
• It's mostly suited for single use applications

Answer 15

I have used spreadsheets in the past to have a better overview of the development status of the project. On one sheet I would list all the components of the project, and if they are implemented/dummy/waiting/not_implemented/not_supported. On another sheet I would write some formulas to iterate the first sheet and report how many % of a certain component still need to be implemented, or how many are waiting for 3rd party support.

I found that high level managers love that kind of stuff on spreadsheets.

Answer 16

There is very broad usages of spreadsheet more than ever one could expect. It contains 8000 columns and 3600 row, with a ton of formulas you can perform on the spreadsheet and make them for presentation as well. The modern use of spreadsheet, you can say that fetching the data from the web query and anyone can easily export it to excel by using market data excel and option data excel.