Excel (PowerPivot) vs MS Access vs SQL Server

krazykrivda

New Member
Joined
May 19, 2015
Messages
2
I've been searching the web high and low to help me explain, compare, and differentiate between data manipulation using various Microsoft Office tools. I've found many links and articles pre-PowerPivot and PowerView days, but cannot find anything more current.

Please help me understand the MAJOR differences between Excel, Access and SQL Server.

The basics that are known is that Excel is king for analysis of data, and quick-adhoc calculations and visual manipulations.
Access can store and link more data more effectively than Excel (PowerPivot), but has now lost pivot tables etc, but can feed INTO excel.
SQL Server (at least of these 3) is KING when it come to LARGE data sets, tables, connections, relationships, etc.
 

Smitty

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

Excel is awesome for data analysis, and as a lot of people find out it isn't a data warehouse.
Access is a great small size data warehouse (<2GB) & query tool, but if all you're doing is Pivot Table type analysis off of large data sets then PowerPivot makes it pretty much obsolete. And don't forget that PowerPivot is built on SQL Server anyway, and you do have relational abilities, which is one of the coolest things about it. I've got views that can update in PowerPivot 100x faster than queries in Access, so there's a lot to be said for it in the right role.
SQL Server is the ultimate as far as databases go, but overkill if you can accomplish the same thing in Access. And SQL Server is much harder to learn than Access.

It essentially comes down to what you need to do. I have a large Access database that we migrated to SQL Server this year, but we still use Access for the front-end and Excel/PowerPivot for data analysis, so they do all play well with each other.
 

krazykrivda

New Member
Joined
May 19, 2015
Messages
2
Welcome to the Board!
...
It essentially comes down to what you need to do. I have a large Access database that we migrated to SQL Server this year, but we still use Access for the front-end and Excel/PowerPivot for data analysis, so they do all play well with each other.
Thanks for your input. I've become pretty well versed in excel, and have use Access as a stepping stone when data became to big for excel. I've progressed to SQL Server in the last year or so maintaining 30-40 tables and building various scripts, queries, and the like to keep the data as accurate and updated as possible. SQL Server is maintaining month over month and year over year data in many different aspect for me at the moment.

I loosely am existing in all 3 platforms, depending on the task. Leveraging SQL Server heavily for large datasets, data pulls, and general data manipulation; wall this meanwhile still utilizing Excel to do the heavy lifting in terms of analysis.

Not being as familiar with PowerPivot, and the capabilities, especially in contrast with Access now, I'm unsure which medium to best use.

Any general rules of thumb or use cases to differentiate is helpful, but thank you very much for your input :)
 

Forum statistics

Threads
1,082,612
Messages
5,366,606
Members
400,906
Latest member
incanus

Some videos you may like

This Week's Hot Topics

Top