Excel, is not only a spreadsheet tool, it also allows you to analyze data. In this article, I would like to review some business intelligence tools that are available in Excel.
Interactive dashboard in Excel
Check out the video below. I created a simple dashboard in Excel that I would like to share with you. You’ll see that you can use Excel to create more than just spreadsheets and simple reports.
I really enjoyed doing this example. What I like about Excel reporting is that you can combine Business Intelligence tools as Power Query, Power Pivot, and DAX to created and automate your reports. Moreover, you can customize the look of your reports adding multiple bottoms, charts, icons, slicers, filters and more.
Power Query for data preprocessing
You can use Power Query to transform raw data into useful tables for analysis with Excel, Power Pivot, or Power BI. It is an Excel tool used to search, reshape and combine data from different sources.
Thanks to the query editor, we can navigate, define and perform data transformation operations on a data set. This BI tool provides efficient data query management, and a key Power Query feature is to filter and reshape data from a wide variety of data sources.
Power Pivot in business intelligence
It allows you to work with large data sets and create connections between different tables. It is the engine that makes it possible for calculations to be visually displayed interactively in our reports. You can use it to directly access all types of data and integrate data from a multitude of sources, such as: corporate databases, spreadsheets, reports, text files and Internet data sources.
It is capable of creating its own data model from different data sources, modeled and structured to suit the needs. This data can be updated from its original sources whenever you require it.
It is a technology that allows you to easily create reports, interactive charts, which help inspect and visualize data in different ways.
This language allows you to create your own measures to perform operations and calculations that work very fast. They are much more effective than Excel formulas for creating new calculated fields.
When you start using DAX, you might think that it looks strangely much like Excel formulas – but while they do look similar, they work in completely different ways.
If you combine these DAX formulas with a slicer to select e.g. the year, the source (being the table) will be filtered, and the formula will be executed on this filtered dataset. This is a filter context and one of the reasons why DAX is great for data analysis.
Pivot tables are one of the most powerful features in Excel. A pivot table enables you to draw conclusions from a large and detailed data set.
If you have interest in data analysis, EDA and clustering algorithms, check out this project: E-commerce sales EDA and Clustering for customer segmentation.
Are you looking for an analyst? Contact me
Whatsapp: +593 97 907 2682