Excel – analytic platform

Hello,

My dear readers, for my third post I will write my Blog about Excel. Why I think it is one of the best and most versatile analytic tools available. I know what some of you will be thinking Excel, it is just good for spreadsheets and quick calculations – but it is so much more.

I am Excel Power user for number of years, in my professional life I do use it everyday and I still learn something new. When I was given these two CA1 and CA2 assignments most of the work I did do with Excel, data extraction, validation. Just the end result was produced with different software package. For the CA1 it was Google Fusion tables and for CA2 it was R-Studio.

For the CA1 similar result could be achieved in Excel, in excel used can load additional add-ins, they are also refereed as Apps for Office. For the data presentation in Maps I did use the app “Bing Maps”, it is straight forward to use, just load the map reference data to the app and it will produce the report.

Excel map report

The result is not exactly the same, but it the aim of the assignment is to represent the population in each Town in Ireland, Excel is more than capable for the job.

CA2

When it comes into data visualization, summaries Excel has excellent features for the task. The same chart that I did create in R-Studio can be done in Excel Pivot Chart. There is many types to choose from

2000 - 2010 movies2000 - 2010 movies chart2

These features are standard, they are not new, but what is relatively new is Power Pivot. The concept is similar to ordinary pivot, but the the main difference is that the user can create data models from multiple data sources. Available connections are 1-to-1 or 1-to-many.

In movies scenario we can easily link the movies data tale to another table holding decades information. These two data sources can be liked via Year data column with one to many relationship.

One to Many connection

This database relationship model enables the users to get and transform the data from multiple sources with ease. The data can be presented in more meaningful ways, more insight can be gained from the data. In CA2 scenario we can easily represent movie releases by decades.

PowerPivot

With Excel 2016 we can get data from multiple sources, not just conventional CSV or spreadsheets. We can use Excel to query databases such a, MySQL,, Oracle, SQL server, other data sources, as SharePoint lists, Facebook, SalesForce and many more.

For me Excel is Lightweight DBMS, it can retrieve the data, transform the data and load it. To sum it up Excel is great for rapid report prototyping data validation, extraction, it can combine multiple data steams into one report. The only limitation is the local computer it is running on.

 

Thanks for reading my blog, I hope you did learn something new:)

 

Evaldas

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *