I would say that 90% of the time (if I am not in meetings), I would be staring at an Excel spreadsheet and tinkering away with numbers and formulae. Along the way, I have discovered and learned new things.
1. Use SOLVER
I learned for example that the inbuilt SOLVER in Excel is a good tool - someone interested in optimization (minimization and maximization) should consider it as a first-stop. I have used it to fit theoretical models on data - specifically in fitting curves (S-curves, Sigmoid/Gompertz Curves are my favorite these days...) against a data-set.
Well. It's not the perfect solution - I am sure that there are a few more in the market. But for someone cash-conscious as me, it works. It sometimes doesn't - but hey, it comes with the bundle so I am happy with it.
One of the things that I have tried doing is embedding SOLVER in optimization and Monte Carlo routines in VBA. It works on my PC in the office (using Office 2007) - but it sometimes conks in my Mac Office 2004. I have used it to 'simulate' a very basic genetic algorithm in optimization. (Note the words *very basic*.)
SOLVER in Excel however is very good for a basic stuff: Say, you have 1'000USD and you'd want to spend it across different campaigns (yeah, I know it's unrealistic) given certain preconditions (at least 30% ought to be on the internet, minimum of 40% ought to be on press... etc) and given certain goals (e.g., reach 40% of the targets at least three times).
SOLVER can easily do that.
If you've taken up Linear Programming or Calculus's "First-order Derivatives and Optimization" in college - and are finding that you're doing more and more of similar stuff in your work, consider SOLVER. It's not the best tool - and certainly not a panacea. But again, it's the first step.
There are a few alternatives - power-solvers - in the market. I have come across SOLVER.Com. But I have yet to try their stuff. (The last time I tried, my 512KB machine couldn't handle it... so I guess, you have to have at least 1GB RAM.)
2. Visualize in Excel
Sometimes, when I deal with masses of numbers (a survey for example of 400 respondents with more than 10'000 columns representing their responses), I get overwhelmed too. (I guess one never gets immuned from "mathphobia and numerophobia".)
Some people I know would dive right in and do descriptive statistics on the data, calculate means and standard deviations, kurtosis, and skewness, after having checked the data's integrity. It creates more numbers - essentially - and adds to the perceived enormity of the task at hand.
(Actually, I am one of those who'd dive right in and do descriptives...)
Others would visualize them first - chart the data, look for aberrations and outliers, and then dive into a deeper analysis.
One of the beautiful things that OFFICE2007 has though is the in-built visualization techniques, under "Conditional Formatting". They have these icons that get embedded based on the value of the numbers - they are preset to assign icons (e.g., red-yellow-green) based on percentiles, but you can change that.
That's one way of visualizing in Excel.
3. Use the Data Analysis Toolpak
The Data Analysis Toolpak in Excel is quite basic - but it does make life a little easier. It has single/multiple-IV linear regression, histogram building, correlation and covariance extraction, and a few more routines available.
But if you really need high-powered stuff for analytics - and you don't want to spend that much money on SPSS, SAS, and other high-end softare (or just simply, you are familiar with Excel and don't want to get away from that environment) - then XLSTAT might be a good bet.
There are a few more stuff that you can do on Excel. I was told - but have not tried - that Excel can handle massive databases and relate it with MS Access or a SQL database. I have tried getting live stock price data using Excel2007 - but it keeps on hanging.
I think Excel is a great tool - and while Google's spreadsheets would probably be a challenger soon with its 'cloud computing' offering, right now, Excel is still my choice.
The Comprehensive R Archive Network I am a believer in making data analytics more accessible to the masses. R is a significant alternative to SPSS, SAS, and other stat software that cost an arm and a leg.