I can't imagine life without Excel. Almost all of my non-web time on my computer is spent on Excel. Without Excel, my computer would just be a toy. My field is chemical engineering and I often use Excel to solve a problem from start to finish because I can set up a problem and solve it in less time than it takes me to look up the relevant function(s), syntax, and write and debug a MatLab or Mathematica script.
Until my junior year of college, I didn't even know that you could add two cells together in Excel. I use it for almost every calculation now - even for stuff that I could do by hand, on a calculator or in a more complex math program like Octave, MatLab or Mathematica. I'm often amazed by how limited people think Excel is.
Some things I frequently do in Excel (all of these have very similar setup, fwiw):
- solve systems of differential equations describing reaction kinetics, heat/mass transfer, etc. I prefer using Excel to solve diffeqs with no analytical solution (rather than MatLab, Mathematica, etc.) because I can play around with the solver method, step size, etc. to see how the solution behaves. If the problem is implicit, I can set up a macro to iterate the solution to convergence.
- manually integrate graph data or discrete data points (usually for software whose freeware implementation only spits out a table of data with no integration work done on it), and manually differentiated curves.
- use Excel's random number generator to do searches for solutions to non-linear regression problems and generate joint confidence intervals
- use Excel's non-linear solvers to do robust linear regressions
- use Excel to explore how an algorithm behaves
I do 99% of my plotting in Excel and I've gotten really good at making graphs look professional and getting rid of that stock "Microsoft Office 2010 red blue yellow green" theme. Often, my diffeq solutions look 100% better than Mathematica or MatLab plots, so nobody ever complains about where it comes from.
I only really use Excel for math and I feel like I barely scratch the surface. With VB, Excel is essentially Turing-complete. I've never dabbled in forms (except on GDocs) and I rarely write my own formulas. I've never made a pivot table and the most complex "business-esque" data analysis thing I've done is dabble in GQL on Google Docs for my part-time boss's disgustingly disorganized spreadsheet-based job tracking system. I also rarely use built-in formulas, other than SUM, AVERAGE, STDEV and the linear regression functions, since I just plug in the formula for most of the math I'm doing.