Faysal Easy Excel

Many of Excel Users have requested in many times this question. Users usually think that, they are using the updated MS Office version 2016, So why PowerPivot is not supporting? Well, I would like to show a list of MS Office 2016 that supports PowerPivot below: Image 1: PowerPivot - Microsoft Office 365 ProPlus - Office Professional Plus - Excel 2016 Standalone - Office 2016 Professional You can find more information from below blog: https://blogs.office.com

What is Hierarchy? Hierarchy is one kind of Custom New Column where few columns are involved. You can also say that it is a Calculated Field. But the difference is, in Calculated Field, you have to calculate among fields and, in Hierarchy Column you have to joint few columns by their reporting priority. Whenever you need to create more than 2 matrix, then you should take help from Hierarchy Column. Image 1: Report from a table "tblSalesData" and shown the Hierarchy In the Image 1, I have tried to clearing the concept of Hierarchy with a real sales report. We all MIS Executives do know that, we can generate reports based on 2 matrix (Row vs Column). Sometimes we need to show more that 2 criteria within 2 matrix such as, "Date wise", "Month wise", "Year wise" sales quantity against "Region wise", "Area wise" and "Territory wise". If we generate the above report in PivotTable we first generally drag...

What is a Calculated Field? Calculated field is a custom field that calculates each cell value in PowerPivot window. To create a report sometimes you may need to add a custom column for a table for getting a result. For example, in my previous article, I used a tblOrdersData table where there is no column for Amount. But in a Sales Report it is essential to know the amount against an invoice. You might think, then tblOrdersData table needs to modify and add a column named Amount. But the answer is No. As we had previously created Relationship among tables. We can easily create this calculated column in tblOrdersData table through Relationship. So question is how to do it? And the solution is here. How to create a Calculated Field? Step 1: We have seen that in tblOrdersData table has a column Quantity but there is no column named Amount. For Sales Report you need to add it. To do this first of all you need to create a relationship in between "tblProducts" an...

In real life, some times you need to work with 2 or more tables to make a report. Assume that, you have 3 tables tblCustomers, tblProducts and tblOrdersData. From these 3 tables you need to create a report Customer vs Product - Sales Quantity. Image 1: tblCustomers Table Image 2: tblProducts Table Image 3: tblOrdersData Table Cust_ID is the primary key of tblCustomers table, Product_ID is the primary key of tblProducts and Record_Num is the primary key of tblOrdersData. Why you need to create a relationship between tables? If you do not create relations between tables, then you have to work some extra time for make extra helping reports and then finally create the target report which you want to create. But if you create a relation between 2 tables then it would be very easier to get the target data within just few seconds. And you can create your desire reports very easily. In others database program like, SQL, Access, Oracle yo...

How to Save a file as PowerPivot supported file format? PowerPivot developed from Microsoft Office 2013. It will support only .xlsx format. To make .xlsx file, you need to click on Save menu from Excel Window and select save as file type Excel Workbook (it means in your Excel 2013 is ".xlsx") in the Save As dialog box. Image 1: Saving the PowerPivot file

What is Linked table? A table which stayed in a Excel WorkSheet and connected to PowerPivot window is a Linked table. If you change any cell value in WorkSheet then it will automatically changed on PowerPivot window. What will you need to do it? Before importing any data through PowerPivot, you need to activate the PowerPivot window by simply clicking on PowerPivot menu and then click on Manage button under Data Model group. After clicking on Manage button Excel will load the PowerPivot Window and it is completely separate from main Excel Window like below: How to import data by using a linked table? Assume that, you have the below sample data table in Sheet1 of your Excel Window: Image 1: Sample data Step 1: Select anyone cell inside the sample data. I have selected B2. Now press Ctrl+T and press Enter. This will convert the table range into a real Table Format like below: Image 2: Sample data converted to Table Format Step 2: In Excel you may need to use more than 1 tabl...

What will you need to do it? Before importing any data through PowerPivot, you need to activate the PowerPivot window by simply Clicking on PowerPivot menu and then Click on Manage button under Data Model group. Image 1: Activating the PowerPivot window After clicking on Manage button Excel will load the PowerPivot Window and it is completely separate from main Excel Window like below: Image 2: PowerPivot for Excel window How to import data by using basic Copy-Paste? Step 1: Select the data table which you want to import and press Ctrl+C in you Excel sheet like below image: Image 3: Copying the data table from another Excel workbook Step 2: Now you need to paste these data table into your PowerPivot window. To do this first you need to activate the PowerPivot window and then, click on Home menu and click again on Paste button. Step 3: After clicking on Paste button, a dialog box will appear named Paste Preview. Image 4: Paste Preview window Step 4: From this dia...

What will you need to do it? Before importing any data through PowerPivot, you need to activate the PowerPivot window by simply clicking on PowerPivot menu and then click on Manage button under Data Model group. Image 1: Activating the PowerPivot window After clicking on Manage button Excel will load the PowerPivot Window and it is completely separate from main Excel Window like below: Image 2: PowerPivot for Excel window How to import data by using import wizard? First of all you need to decide from which source you will import. It can be others database files like MS Access, SQL etc. Assume that, We are going to import a database from MS Access which is already I have. Now to import this database just follow the below steps: Click on Home menu and click on From Access under Get External Data. Image 3: Select From Access After clicking on From Access menu, a dialog box will appear named Table Import Wizard. Table Import Wizard: Step 1: Connect to a Microsoft Access Datab...

Downloading: (1) Before starting downloading make sure what is your system type (32bit or 64bit). (2) Then just search in microsoft.com with: PowerPivot and download the add-in for your system type. Installing: (1) Unzip the downloaded zip file. (2) Run the .msi file and click on Run and follow the wizard to install it. Running: (1) In Microsoft Excel 2013 - Open an Excel file. (2) Then go to File | Options (3) Select Add-Ins from navigation and click the drop down list under Manage and select COM Add-Ins and click on Go button. Then COM Add-Ins dialog box will appear. Image 1: Excel Options (4) Mark Microsoft Office PowerPivot for Excel 2013 and click on "Ok". Then you will see the POWERPIVOT menu in Menubar like below: Image 2: COM Add-Ins Image 3: PowerPivot menu

The Beginning: Before saying anything about PowerPivot, I would like to ask a question to you, "did you use Pivot Table" in MS Excel? If the answer is "Yes" then it could be easier to you to understand about the data analysis engine. Yes! it works like thunderstorm. If the answer is "No", then you must try Pivot Table before using it. Without any doubt you can call it "The most powerful data analysis add-ins tool". Image 1: Microsoft Excel 2013 - PowerPivot To improve the power of Microsoft Excel from 2010 or higher version,  Microsoft has developed a Business Analysis tool named "Power Business Intelligence" for self service reporting and analysis the business. PowerPivot is an analytical add-ins of Microsoft Power BI package tool. It is basically the main engine which can easily generate your desired reports in seconds. PowerPivot mainly the updated version of Pivot Table. It is more easier to understand if I say ...