What are some spreadsheet applications most people don't know?

[MacBH928]

I know spreadsheet software like Excel are extremely popular in use but I also know there is specialized software for accounting. So other than creating lists, how can spreadsheet software be used to help you in daily tasks for personal or business use?

What do you use spreadsheets for?

 

[triptolemus]

tl;dr: Spreadhseets are often used when a database would be superior.

Spreadsheets -- and specifically MS Excel -- have got to be the single most misused piece of technology in business today. Generally, they are often used in place of a database and reporting suite, like Crystal Reports. Mostly, I suspect this is due to people in those roles simply working with the tools they are given; a PC, Word, Excel, PowerPoint, Outlook, and a web browser. The learning curve is less -- someone can whip up a "solution" in a few minutes or hours which works for them. Scalability is where it all falls apart. When data needs to be reported out upon... when others need to enter data in. The problem starts at the beginning of this process when the users do not recognize that the data can/will/could/should be used and accessed in other ways.

The last few times I've opened up Excel were to: generate a time sheet, update a vehicle mileage log, create an expense report, and do some quick math (like a scratchpad). In the past, I have used Excel to pull data from a db and create "reports" where no other option was available - it works, but its a hack.

In your example though, "creating lists" is exactly the type of thing that a spreadsheet should not be used for. I am confident in saying that the majority of stuff that is done in a spreadsheet could be done better in a database. But people just don't have the resources (time/money/skill). My wife recently earned her PhD. During the dissertation, she began tracking her bibliographic information in Excel. She was disorganized and overwhelmed very quickly. A proper database application (EndNote) was the solution.

Too many times we hear "if you want to calculate anything, use a spreadsheet" -- that statement makes my skin crawl. It's more accurate to say something like "if you want to model or manipulate numbers and data sets, use a spreadsheet populated with data from a proper database".

/opinion

 

[Mousse]

Payroll, tax calculations, tracking the company's finances... general bean-counter stuff. I also use if for financial modeling. With VBA (Visual Basic for Applications), I create a simple interface for the spreadsheet so that the user (da Boss) can't enter improper data.
If you're serious about working with spreadsheet, learning macro programming is a must. Excel has VBA, dunno what scripting language the other guy uses.

 

[sracer]

tl;dr: Spreadhseets are often used when a database would be superior.

Spreadsheets -- and specifically MS Excel -- have got to be the single most misused piece of technology in business today. Generally, they are often used in place of a database and reporting suite, like Crystal Reports. Mostly, I suspect this is due to people in those roles simply working with the tools they are given; a PC, Word, Excel, PowerPoint, Outlook, and a web browser. The learning curve is less -- someone can whip up a "solution" in a few minutes or hours which works for them. Scalability is where it all falls apart. When data needs to be reported out upon... when others need to enter data in. The problem starts at the beginning of this process when the users do not recognize that the data can/will/could/should be used and accessed in other ways.

The last few times I've opened up Excel were to: generate a time sheet, update a vehicle mileage log, create an expense report, and do some quick math (like a scratchpad). In the past, I have used Excel to pull data from a db and create "reports" where no other option was available - it works, but its a hack.

In your example though, "creating lists" is exactly the type of thing that a spreadsheet should not be used for. I am confident in saying that the majority of stuff that is done in a spreadsheet could be done better in a database. But people just don't have the resources (time/money/skill). My wife recently earned her PhD. During the dissertation, she began tracking her bibliographic information in Excel. She was disorganized and overwhelmed very quickly. A proper database application (EndNote) was the solution.

Too many times we hear "if you want to calculate anything, use a spreadsheet" -- that statement makes my skin crawl. It's more accurate to say something like "if you want to model or manipulate numbers and data sets, use a spreadsheet populated with data from a proper database".

/opinion

Cause and effect. People often use spreadsheets instead of a relational database like Access because (A) for many basic database operations, a relational database is overkill, and (B) Access was only available on the higher-end configurations of MS Office, which most individuals and even many small companies didn't have.

Back-in-the-day there were "flat-file" database programs like dBase, FoxPro, Paradox, MS Works Database, Lotus Symphony, early versions of FileMaker, ClarisWorks Database, Bento, and so on. When those were available, there were plenty of people using these database programs instead of spreadsheet programs for those type of things.

I would agree with your statement that I bolded if such flat-file database apps still existed.

 

[campyguy]

I'm an engineer with a background in cost engineering and forensic analysis, with degrees and experience in civil and industrial engineering and a focus on transportation and structural analysis. And, I have a life...

Excel, to me, is not a "spreadsheet application". Excel, like Photoshop, is an extensible platform that provides a basis of analyzing data with one or multiple toolsets and displaying those analyses in graphical format(s). Excel is a tool, a very powerful tool for analyzing and interpreting data sets - and a tool to be used to display the results in a visual format that others can understand/comprehend. MS's VBA shell is IMO one of Excel's most powerful supplementary tools. Honestly, the macOS version of Excel is a gimped shadow of Excel - most of my productive work is on the WinOS platform version of Excel although that may change in the near future (I won't hold my breath...).

Database apps like Access are data containers, too many users that I've encountered use Excel for a placeholder or data container. I'm not judging, but I see this as a waste of Excel's power.

Examples of what I've used Excel for include mapping transportation/run times of light/heavy rail lines, 5-dimensional flow representations (X/Y/Z +time +location) of fluids or soil, and using Excel's reporting features to show budget status - all of this on the Windows platform since the early 90s but still not practical on the Mac platform today. <rant>Until MS opens up Excel on the Mac to PowerBI or makes their VBA platform as accessible as the Win OS Excel on macOS will be a great tool for "spreadsheets" and recording "CD collections"...</rant> The Office Store is a nice, but pretty toothless addition to Office - give me power tools on the macOS or I'm sticking with Excel on Windows.

 

[rhett7660]

I'm an engineer with a background in cost engineering and forensic analysis, with degrees and experience in civil and industrial engineering and a focus on transportation and structural analysis. And, I have a life...

Excel, to me, is not a "spreadsheet application". Excel, like Photoshop, is an extensible platform that provides a basis of analyzing data with one or multiple toolsets and displaying those analyses in graphical format(s). Excel is a tool, a very powerful tool for analyzing and interpreting data sets - and a tool to be used to display the results in a visual format that others can understand/comprehend. MS's VBA shell is IMO one of Excel's most powerful supplementary tools. Honestly, the macOS version of Excel is a gimped shadow of Excel - most of my productive work is on the WinOS platform version of Excel although that may change in the near future (I won't hold my breath...).

Database apps like Access are data containers, too many users that I've encountered use Excel for a placeholder or data container. I'm not judging, but I see this as a waste of Excel's power.

Examples of what I've used Excel for include mapping transportation/run times of light/heavy rail lines, 5-dimensional flow representations (X/Y/Z +time +location) of fluids or soil, and using Excel's reporting features to show budget status - all of this on the Windows platform since the early 90s but still not practical on the Mac platform today. <rant>Until MS opens up Excel on the Mac to PowerBI or makes their VBA platform as accessible as the Win OS Excel on macOS will be a great tool for "spreadsheets" and recording "CD collections"...</rant> The Office Store is a nice, but pretty toothless addition to Office - give me power tools on the macOS or I'm sticking with Excel on Windows.

Excel 2016 for Mac does have VBA now. I haven't used it, since I still have an older version, but is now available.

 

[MacBH928]

tl;dr: Spreadhseets are often used when a database would be superior.

Spreadsheets -- and specifically MS Excel -- have got to be the single most misused piece of technology in business today. Generally, they are often used in place of a database and reporting suite, like Crystal Reports. Mostly, I suspect this is due to people in those roles simply working with the tools they are given; a PC, Word, Excel, PowerPoint, Outlook, and a web browser. The learning curve is less -- someone can whip up a "solution" in a few minutes or hours which works for them. Scalability is where it all falls apart. When data needs to be reported out upon... when others need to enter data in. The problem starts at the beginning of this process when the users do not recognize that the data can/will/could/should be used and accessed in other ways.

The last few times I've opened up Excel were to: generate a time sheet, update a vehicle mileage log, create an expense report, and do some quick math (like a scratchpad). In the past, I have used Excel to pull data from a db and create "reports" where no other option was available - it works, but its a hack.

In your example though, "creating lists" is exactly the type of thing that a spreadsheet should not be used for. I am confident in saying that the majority of stuff that is done in a spreadsheet could be done better in a database. But people just don't have the resources (time/money/skill). My wife recently earned her PhD. During the dissertation, she began tracking her bibliographic information in Excel. She was disorganized and overwhelmed very quickly. A proper database application (EndNote) was the solution.

Too many times we hear "if you want to calculate anything, use a spreadsheet" -- that statement makes my skin crawl. It's more accurate to say something like "if you want to model or manipulate numbers and data sets, use a spreadsheet populated with data from a proper database".

/opinion

Interesting,
So you are saying spreadsheets are being miss used. Which type of DB software do you recommand. I hardly know any except the enterprise stuff like Oracle, and on consumer level like Microsoft Access are not popular at all (If not shut down by now).

I know you mentioned that spreadsheets are best to model and manipulate numbers, but what does that exactly mean. I heard people use it for everything from creating just simple plain lists of names to using it for invoicing in a business. I also heard accountants use it for sales and costs entries recording and using that to do the balance sheet.

 

[NoBoMac]

The problem starts at the beginning of this process when the users do not recognize that the data can/will/could/should be used and accessed in other ways.

This is the big thing. Lots of stories out there where someone picked up a spreadsheet that they did not create, make changes, enter different data, and incorrect calculations occur. Heck, people that supposedly know their own spreadsheet makes these mistakes. Incorrect formulas (eg. summing incorrect cell range). And then people taking this "bad" data and treating it as gospel. I've done this with my own spreadsheets, but have the good sense to tell that some number(s) is off.

That said, I use spreadsheets to do my tax estimates. My taxes are not particularly tricky, been using this spreadsheet for years, and has always tracked real close to final numbers. I use spreadsheets to create indexes for recipes I collect (recipes are PDFs from websites, combined together, and then index is printed as PDF and added to the recipe file, all through Preview [yes, I know word processors can generate TOCs, but, this is an easy hack to deal with files being PDFs]).

Simple things like tracking meals, exercise, weight. In the case of exercise and weight, simple formulas for average, max/min, summation, so not tricky to keep up. Not making major changes to them. And not treating as gospel.

ADD: a couple of Excel gotchas:

http://catless.ncl.ac.uk/Risks/25/39#subj2.1
http://catless.ncl.ac.uk/Risks/27/25#subj9.1
http://catless.ncl.ac.uk/Risks/29/73#subj15.1
http://catless.ncl.ac.uk/Risks/29/73#subj29.1

 

[campyguy]

Excel 2016 for Mac does have VBA now. I haven't used it, since I still have an older version, but is now available.

Thanks, I am aware of that re-addition of VBA - it wasn't that great on the Mac platform before it was chopped out. I have the fast-ring version of Office 2016 installed, and do check MS's progress on Word/Excel. While MS has made some progress with the Mac suite and it's "version" of VBA, my contention is the suite is gimped when compared to the Windows suite, including the VBA shell. I've been programming in VBA to work with Excel for around 25 years and IMO my thought for MS re VBA for their Mac suite is "Why bother?" Office 2019 should be widely available in beta to the general public on both platforms, and it's available to some business customers now - if MS can't unify the VBA environment by the end of the year I'd offer advice to stick with the Windows suite for serious work, just like the past several years. MS stated in a blog entry a few months ago that they've achieved a shared Office codebase on both OSes and iOS/Android, but I'm not seeing parity in their VBA environment yet. Cheers.

 

[rhett7660]

Thanks, I am aware of that re-addition of VBA - it wasn't that great on the Mac platform before it was chopped out. I have the fast-ring version of Office 2016 installed, and do check MS's progress on Word/Excel. While MS has made some progress with the Mac suite and it's "version" of VBA, my contention is the suite is gimped when compared to the Windows suite, including the VBA shell. I've been programming in VBA to work with Excel for around 25 years and IMO my thought for MS re VBA for their Mac suite is "Why bother?" Office 2019 should be widely available in beta to the general public on both platforms, and it's available to some business customers now - if MS can't unify the VBA environment by the end of the year I'd offer advice to stick with the Windows suite for serious work, just like the past several years. MS stated in a blog entry a few months ago that they've achieved a shared Office codebase on both OSes and iOS/Android, but I'm not seeing parity in their VBA environment yet. Cheers.

Ah, so from the sound of this, they gimped the VBA on the Mac version. Am I reading correctly, see bolded section, if you open up a VBA based spreadsheet from the Mac version it doesn't play well with the Win version?

 

[campyguy]

Ah, so from the sound of this, they gimped the VBA on the Mac version. Am I reading correctly, see bolded section, if you open up a VBA based spreadsheet from the Mac version it doesn't play well with the Win version?

Yes, you are understanding what I wrote. MS has stated that Office apps share a unified codebase, and on the fast ring I get 2-3 updates per week for most or all of the 5 apps (although they've recently stated that OneNote's metaphor will be different with the upcoming suite). I'm seeing parity in the VBA environment. More relevant to me and everyone I interact with professionally relative to VBA, there are zero Mac Office suite users and I don't want to bother with the OS-specific calls/variables. I do know some architects who use Mac Office, but none of them use VBA.

I soured on VBA in the Mac Office suite when the environment in Office 2004/2008 was pretty much useless. I was coding in the Win suite at that time anyway. A unified VBA environment would be nice, but I'm not betting on it. I'm not going to advise anyone to avoid the environment, however, when I hire my sub-consultants and contractors I specify the Win suite and provide templates - no headaches or wasted time reformatting and/or chasing down code errors in two OS platforms.

 

[MacBH928]

I soured on VBA in the Mac Office suite when the environment in Office 2004/2008 was pretty much useless. I was coding in the Win suite at that time anyway. A unified VBA environment would be nice, but I'm not betting on it. I'm not going to advise anyone to avoid the environment, however, when I hire my sub-consultants and contractors I specify the Win suite and provide templates - no headaches or wasted time reformatting and/or chasing down code errors in two OS platforms.

What kind of things you can do with VBA on Excel thats already not built in as a function of the software?
[doublepost=1526191163][/doublepost]

Cause and effect. People often use spreadsheets instead of a relational database like Access because (A) for many basic database operations, a relational database is overkill, and (B) Access was only available on the higher-end configurations of MS Office, which most individuals and even many small companies didn't have.

Back-in-the-day there were "flat-file" database programs like dBase, FoxPro, Paradox, MS Works Database, Lotus Symphony, early versions of FileMaker, ClarisWorks Database, Bento, and so on. When those were available, there were plenty of people using these database programs instead of spreadsheet programs for those type of things.

I would agree with your statement that I bolded if such flat-file database apps still existed.

aren't flat-file database is just an XML file?

 

[sracer]

What kind of things you can do with VBA on Excel thats already not built in as a function of the software?
[doublepost=1526191163][/doublepost]

aren't flat-file database is just an XML file?

That is one implementation of one, and not a very efficient one at that. How the database is stored and structured is only a small piece of what those flat-file database programs did. They built features and functions around that simple structure to give it a usefulness that went beyond what is easily possible with spreadsheets.

I suspect many of the members on this forum weren't even born when these applications were in use... dang I'm old.

 

[campyguy]

What kind of things you can do with VBA on Excel thats already not built in as a function of the software?

Several examples: automating the Goal Seek and Solver Tools, Circular Calculations, use the Golden Section search technique for one or more optimization problems, create/implement targeting and optimization algorithms, and - this is a big one for my needs - create stand-alone Add-ins and input interfaces.

My first "project" in Excel with VBA was borne out of frustration with a DOS-based computational tool that was used by transit agencies and consultants to calculate the run times of two then-proposed light rail lines branching out of Portland. The LRT lines had several proposed routes, and each proposed route took up to a couple of hours to calc out. That tool had been used for years by others, and I was new to the agency - therefore the "grunt work" designee. I was already proficient in the Basic programming language. So I spent about two hours writing a VBA routine to emulate what the DOS application performed; the DOS app was a paid app but I already knew all of the relevant formulaic metaphor (I'm a civil engineer with a focus on transportation, transit, and environmental analysis - so, pretty much QED stuff for me). My new workbook (with multiple worksheets and the VBA module) simply prompted for specific data from our surveyor's measurements (lengths, curve radii, inclination/declination, etc.), and the output was a full runtime of the proposed LRT line, station times, and a graphical representation of the line to be used in presentations to the interested parties. I cut the amount of time down to about 7-10 minutes per route. A second VBA module was written that resulted in an animated Excel worksheet that could be displayed in a live presentation (at hearings and open houses) and to secure funding. Both LRT lines were built and are running today, and my tool was distributed among other transit agencies gratis.

Excel, to me, isn't a container or database - it's an analysis tool. I connect Excel to Access or to an Oracle database, and when compared to those apps I find Excel to not be a "database app". VBA is an automation tool as well as an interface builder. I generally set up my employees with some time (paid) to take pro-level coursework - I learned OTJ, but I mastered several programming languages in college (probably work taught in HS now...).

 

[TiggrToo]

What kind of things you can do with VBA on Excel thats already not built in as a function of the software?

I for one created a Point-of-Sale system complete with Hand Scanners, full ability to add\delete line items in mid-stream, handling multi-pack purchases (Buy % get 1 Free, Buy 6 for 10% etc.), full tax calculation and credit card payments.

It was a bad week when I first created it after we discovered our accounting system that would normally do all that wasn't going to work across the WAN at a convention center during one of our World Conferences and, well, what started out as a simple list of items purchased per customer turned into a full blown App used for two years in a row.

 

[MacBH928]

I for one created a Point-of-Sale system complete with Hand Scanners, full ability to add\delete line items in mid-stream, handling multi-pack purchases (Buy % get 1 Free, Buy 6 for 10% etc.), full tax calculation and credit card payments.

It was a bad week when I first created it after we discovered our accounting system that would normally do all that wasn't going to work across the WAN at a convention center during one of our World Conferences and, well, what started out as a simple list of items purchased per customer turned into a full blown App used for two years in a row.

You made an accounting/POS out of Excel? This is the kind of thing I was trying to find about Excel. I want to know what it can do more than turn numbers into tables and graphs.

Several examples: automating the Goal Seek and Solver Tools, Circular Calculations, use the Golden Section search technique for one or more optimization problems, create/implement targeting and optimization algorithms, and - this is a big one for my needs - create stand-alone Add-ins and input interfaces.

My first "project" in Excel with VBA was borne out of frustration with a DOS-based computational tool that was used by transit agencies and consultants to calculate the run times of two then-proposed light rail lines branching out of Portland. The LRT lines had several proposed routes, and each proposed route took up to a couple of hours to calc out. That tool had been used for years by others, and I was new to the agency - therefore the "grunt work" designee. I was already proficient in the Basic programming language. So I spent about two hours writing a VBA routine to emulate what the DOS application performed; the DOS app was a paid app but I already knew all of the relevant formulaic metaphor (I'm a civil engineer with a focus on transportation, transit, and environmental analysis - so, pretty much QED stuff for me). My new workbook (with multiple worksheets and the VBA module) simply prompted for specific data from our surveyor's measurements (lengths, curve radii, inclination/declination, etc.), and the output was a full runtime of the proposed LRT line, station times, and a graphical representation of the line to be used in presentations to the interested parties. I cut the amount of time down to about 7-10 minutes per route. A second VBA module was written that resulted in an animated Excel worksheet that could be displayed in a live presentation (at hearings and open houses) and to secure funding. Both LRT lines were built and are running today, and my tool was distributed among other transit agencies gratis.

Excel, to me, isn't a container or database - it's an analysis tool. I connect Excel to Access or to an Oracle database, and when compared to those apps I find Excel to not be a "database app". VBA is an automation tool as well as an interface builder. I generally set up my employees with some time (paid) to take pro-level coursework - I learned OTJ, but I mastered several programming languages in college (probably work taught in HS now...).

Thanks for sharing. That was insightful.I am trying to know what can Excel be used for more than store and arrange data.

 

[rhett7660]

What kind of things you can do with VBA on Excel thats already not built in as a function of the software?
[doublepost=1526191163][/doublepost]

aren't flat-file database is just an XML file?

What I usually use it for is the following:

• Coping whole sections over to another sheet/tab with the click of a button.
• Inserting rows or columns throughout the sheet new entry points.
• Creating input boxes for the end user.
• Highlight values (think top ten with a certain color)
• Create mail handlers with a click of a button. This allows me to use cells within the sheet to auto-populate an email with the correct and current information based off what is in a cell. First name, last name, dates, etc.'

That is just the tip of the iceberg on what you can do with VBA. Super powerful.

 

[MacBH928]

• Highlight values (think top ten with a certain color)

So you can do stuff like : If "This" Do "That" .
Thank you. I am not into programming so this stuff is foreign to me. The auto-populating emails is interesting.

 

[rhett7660]

So you can do stuff like : If "This" Do "That" .
Thank you. I am not into programming so this stuff is foreign to me. The auto-populating emails is interesting.

That is correct.

 

[poorcody]

I suspect many of the members on this forum weren't even born when these applications were in use... dang I'm old.

Not the only one! I've often wondered what has replaced those applications... maybe it is Excel? I speculate they sort of died off because they weren't really designed optimally for multi-users, but it seems strange to me that (apparently) nothing has come along that allows a typical office worker to create a shared database. Creating a web-based database seems to be more complicated and require more skills than creating say an Access one.