Advanced Data Analysis - Overview

Quiz

Excel provides several commands, functions and tools that make your complex data analysis tasks easy. Excel lets you perform various complex calculations with ease. In this tutorial, you will understand the versatile data analysis tools of Excel. You will understand data analysis with relevant examples, step by step instructions and screen shots at every step.

Data Consolidation

You might have to consolidate the data from various sources and present a report. The data could be in the worksheets of the same workbook or in different workbooks. With Excel data tool Consolidate, you can perform this in a few easy steps.

What-If Analysis

What-If Analysis provides you tools to handle the following data analysis situations −

Find the input values that result in a specified value. The result could be set up as a formula with the input values as variables. By varying the values of the input variables, Excel provides the solution with the Goal Seek Tool.
Find the possible output values by varying the values of one or two variables. The result could be set up as a formula with one or two input values as variables. By varying the values for the input variables, Excel provides the solution with the Data Table Tool.
Find the possible output values that are a result of varying the values of more than two variables. The result could be set up as a formula with the input values as variables. By varying the values for the input variables, Excel provides the solution with the Scenario Manager Tool.

Optimizing with Excel Solver Add-in

Solver is used to handle complex goal seek situations. In such cases, in addition to the inputs and outputs, there will be defined constraints or limits imposed on the possible input values. Further, Solver is used to result in an optimal solution.

Excel has a Solver Add-in that helps you solve such complex problems.

Importing Data into Excel

Your data analysis might depend on various external data sources. In Excel, you can import data from different data sources, such as Microsoft Access Database, Web Pages, Text Files, SQL Server Table, SQL Server Analysis Cube, XML File, etc.

You can import any number of data tables simultaneously from a database. When you are importing multiple tables from a relational database such as Access, the existing relationships among the tables will be retained in Excel also. While importing the data, you can also optionally create a PivotTable or PivotChart or Power View report based on that data.

You can just create a data connection with a data source, or import the data into Excel. If you import the data into Excel, the data tables are added to the Data Model in Excel.

Data Model

Data Model in Excel is used to integrate data from multiple tables in the current workbook and / or from the imported data and / or from the data sources connected to the workbook through data connections. Data model is used transparently in PivotTable, PivotChart, PowerPivot and Power View reports.

You can create a Data Model while importing data, or from the Excel tables in the workbook.
The data tables in the Data Model can be viewed either in Data View or Diagram View.
With a Data Model, you can create relationships among the data tables.
You can either use the Create Relationship command or just click and drag and connect the fields in the two tables that define the relationship in the diagram view of the Data Model.

Exploring Data with PivotTable

As you can integrate the Data Model with a PivotTable, you can do extensive data analysis by collating, connecting, summarizing and reporting data from several different sources. As you can import tables from external data sources and create a PivotTable, it is possible to have automatic updates of the values in the PivotTable whenever the data in the connected data sources is updated.

You can create a PivotTable with the fields from multiple tables, provided the tables have relationships defined. If a relationship does not exist, Excel prompts you to create one and you can do so from the PivotTable itself. The relationship that you so define is reflected in the Data Model.

Exploring Data with PowerPivot

You can use PowerPivot to access, analyze and report data from various data sources. PowerPivot can help you handle large data with ease and produce fascinating analysis reports.

PowerPivot provides you commands to manage the Data Model, add Excel tables to Data Model, to add calculated fields in the Data Tables, to define KPIs, etc.

Exploring Data with Power View

Power View provides interactive exploration, visualization and analysis of large data. Owing to its versatile visualization options, you can definitely find the one that gives your data the perfect platform wherein you can explore the data, summarize and report.

Ranging from Tables to Maps, it is just a play for you to visualize your data, filter it, analyze it, and report it interactively. Moreover, you can have multiple visualizations on the same Power View sheet that reflect and highlight values, when you click on a data point in any one of them.

You can explore data in Power View with a table, a matrix, a card, different chart types, multiples, maps and tiles. You will get fascinated with the versatility of these different views once you get hands-on experience. This is because it is easy to produce interactive reports highlighting significant values and dynamically switching across the views.

Exploring Data with Hierarchies

If your data has hierarchies, they can be either defined in the Data Model that is reflected in the Power View or build the hierarchies in Power View itself.

Once a hierarchy is defined, you can drill-up and drill-down the hierarchy, displaying the required data.

Aesthetic Power View Reports

You can arrive at a report layout based on what you want to present in Power View. You can add a background image that reflects your company logo or your corporate view. Optionally, you can format the background of the report to give it an elegant look.

You can select a theme for your report that best portrays your data. You can change the font and text size so that your report becomes easily readable.

Key Performance Indicators (KPIs)

Key Performance Indicators are commonly used to gauge the performance. In Excel, you define and portray KPIs in PowerPivot or Power View. The graphical presentation of KPIs will uplift your reports.

Print Page

Save or share this article