Excel has many limitations for data analysis. It's more of a holdout for the user, the non-technical guy that needs all his analysis and feeds in excel because that what they know, and the firm doesn't have front-end developers for little day-to-day stuff. Excel does not allow you to nest nearly as many functions as you would like, and sometimes can't recognize certain ones when combined. It's a great tool, until you start to get frustrated that it can't do what you want it to do without freezing your computer, lol.
Putting datasets into SQL Server/Sybase databases is a quick and simple way to do more meaningful data analysis. The datatypes are defined, you can obtain data integrity, set keys and indexes, and normalize your data. How many times have you had to create a concatenated field containing 4-5 fields so you have a 'key' to do vlookups on? Doing SMF recon with excel is a huge pain. I suppose you could try to use excel's 'Query' add-in, but it's just another limited wrapper on what you can do with a SQL relational database.
Obviously the aggregation functions are very useful, but by far my favourite set of functions are the case/when/then/else/end set. It can be used everywhere, inside aggregates, sorting, grouping, even on joins. If gives you the flexibility to allow you to define how you want your data to come together. You are very limited in excel, you can't write complex conditional statements within the out of the box functions. So you end up heading to VBA to write macros and functions, but to me that's just complicating things, when you can use a straight data store and query tool. I feel the same way about MS Access, it's better in terms of data management, but in the end you have to write 8 queries in the SQL view for something that I can do in one statement in true SQL. I like the exists/not exists functions as well, key for doing reconciliation.
Then once in a table, you can write stored procedures to perform your analysis, such as recursive functions to optimize or solve, or perform a regression analysis on historical data.
It's kind of the halfway point between business-level data apps and more sopisticated programming languages. You can do many things faster with a
C++ for instance than a stored proc, but for someone with less IT knowledge, the code is more readable and simple to program, and space and memory gets cheaper by the day.
When performance matters though, you will find it's limitations, but it's great to browse and understand data.