In Excel, a Data Table is a way to see different results by altering an input cell in your formula. Data tables are available in Data Tab » What-If analysis dropdown » Data table in MS Excel.

Data Table with Example

Now, let us see data table concept with an example. Suppose you have the Price and quantity of many values. Also, you have the discount for that as third variable for calculating the Net Price. You can keep the Net Price value in the organized table format with the help of the data table. Your Price runs horizontally to the right while quantity runs vertically down. We are using a formula to calculate the Net Price as Price multiplied by Quantity minus total discount (Quantity * Discount for each quantity).

Pivot Tables in Excel

Pivot Tables

A pivot table is essentially a dynamic summary report generated from a database. The database can reside in a worksheet (in the form of a table) or in an external data file. A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data. Pivot tables are very powerful tool for summarized analysis of the data.
Pivot tables are available under Insert tab » PivotTable dropdown » PivotTable.

Pivot Table Example

Now, let us see Pivot table with the help of example. Suppose you have huge data of voters and you want to see the summarized data of voter Information per party, then you can use the Pivot table for it. Choose Insert tab » Pivot Table to insert pivot table. MS Excel selects the data of the table. You can select the pivot table location as existing sheet or new sheet.

This will generate the Pivot table pane as shown below. You have various options available in the Pivot table pane. You can select fields for the generated pivot table.

• Column labels − A field that has a column orientation in the pivot table. Each item in the field occupies a column.
• Report Filter − You can set the filter for the report as year, then data gets filtered as per the year.
• Row labels − A field that has a row orientation in the pivot table. Each item in the field occupies a row.
• Values area − The cells in a pivot table that contain the summary data. Excel offers several ways to summarize the data (sum, average, count, and so on).
After giving input fields to the pivot table, it generates the pivot table with the data as shown below.

So, this brings us to the end of blog. This Tecklearn ‘Data Tables and Pivot Tables in Excel’ blog helps you with commonly asked questions if you are looking out for a job in Microsoft Excel. If you wish to learn Excel and build a career in Business Intelligence domain, then check out our interactive, Microsoft Power BI Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Microsoft Power BI Training

Microsoft Power BI Training

About the Course

Microsoft Power BI Training at Tecklearn will help you achieve expertise in business analytics Our best online training course teaches you all important concepts like Power BI Desktop, Mobile, Power Query & Power Pivot, Data modelling, visualization, creating dashboards and reports, DAX, etc. As part of this program, you will work on real-world projects. Also, our Power BI course curriculum will equip you with all the key skills that are required to clear the Microsoft Power BI Certification exam (70-778).

Why Should you take Microsoft Power BI Training?

• The average annual pay for a Power BI Professional is $114,000 . -PayScale.com.
• Cognizant, Dell, KPMG, Hitachi, Wipro, Avanade, Annik Inc, Brillio and 45,000 MNCs across 185 countries use Power BI and it has a market share of around 7% globally.
• Microsoft Power BI has been ranked at No.1 position in the Gartner Magic Quadrant for Analytics and Business Intelligence Platforms for 13 consecutive years.

What you will Learn in this Course?

Introduction to Microsoft Power BI

• Microsoft Power BI Introduction
• Power BI Products
• Power BI Architecture
• Installing Power BI
• Connecting to Data Sources

Power BI Workflow

• Key features of Power BI workflow
• Power BI Vs MSBI
• History of Power BI
• Power BI Products
• Data modelling and relationships

Power Query for Data Transformation

• Power Query for Data Transformation
• Learning about Power Query for self-service ETL functionalities
• Working with Excel data
• Introduction to Query Editor
• Data transformation
• Pivot and UnPivot
• Merge Join, relational operators, date, time calculations, working with M functions
• Summary Tables
• Writing custom functions and error handling
• M advanced data transformations

Filters and Drill Down Report

• Visualization Filter
• Page Level Filter
• Report LEVEL Filter
• DRILL Through Filter or Report
• BookMark Report

Power Pivot for Data Modelling

• What is SSAS
• Data Modelling
• Star Schema
• Snowflake Schema
• Introduction to MDDB and Tabular
• Data Access Modes
• Tabular with DAX
• Power BI with DAX
• DAX Functions

Data Analysis Expression – DAX Queries

• TABULAR with DAX
• Power BI with DAX
• Calculated Members, Row, Filter & Evaluation Context
• Cumulative Charts
• Calculated Tables, ranking and rank over groups
• DAX advanced features

Power BI Desktop & Administration

• Reports and Visualization
• Learning about data modelling and data relationships
• Deploying data gateways
• Reports and Dashboards
• On Premises Data Gateway
• Gateway Clusters

Introduction to Power Q & A

• Power Service
• Power Q & A best practices
• Integrating with SaaS applications
• Gateway

620