Skip to main content

Posts

Showing posts from July, 2017

New Article

How to SUM by matching partial text in Excel

How to create and publish your first Report in Power BI Desktop to Power BI Service?

This is the first report I'm going to prepare through Power BI Desktop. The first thing is adding raw data. To do this, you need to Click the Get Data option in Startup Screen .  Image 1: Startup Screen of Power BI Desktop If you are advanced user, then you don't need to open this startup window all the time while you need to open data sources. You can get data by Clicking on Home | Get Data | More . While the Get Data dialog box appeared, Select the Excel program and Click on Connect button: Image 2: Get Data dialog box After appearing Open dialog box, Select the Excel file which you want to open and Click on Open button. Now this will connect to your Excel file . When it is done, it will show you a Navigator dialog box listed with all data tables present in that Excel file. Select the Table or Tables which you want to open (i.e. Invoice ) and Click on Load . After Loading, it will show you Sheet Names . If you have 2 different tables in ...

Download Install and Run the Power BI Desktop

Download: Power BI Desktop is a desktop application includes with Microsoft Power BI software and services, that allows you to connect your raw data and then designing require reports. You can download the Power BI Desktop application from this link . Install: After Downloading the application file from the above link, double Click on it to install it. You can install it either 32bit or 64bit whichever your system supports. Run Power BI Desktop: So once you download it, you'll install Power BI Desktop and run it like other applications on Windows  Image 1: Power BI Desktop startup screen Power BI Desktop connects to different types of data sources, from your pc to cloud. And then changing data types, working with dates. You can work with tables and build relations in between tables to get your report fast.

Analyze data by using Two Variable Data Table

The Two Variable Data Table is another important data analysis tool which helps you to calculate different situations based while you change 2 different variables to affect the calculation. It is not widely used. But it can helpful to calculate for 10 to 20+ variables at once. Here is an example that helps you to understand this type of Data Table. Problem: Assume that, you want to take a loan from Bank about BDT 1000000 . The Bank Interest Rate is 8.50% . Maximum Years to settle the loan is 6 (6 * 12 = 72 Months). Now you need to calculate Monthly Installment vs Terms amount. If terms increases, then what will be the installment. Solution: Plot the above problem in your Excel sheet like below image: Image 1: Problem Plotting Calculate B5 cell by using =B4/12 and B7 cell =B6*12 . Finally in B9 cell =PMT(B5,B7,B3) . After calculation it will looks like this: Image 2: Monthly Installment Calculated Now to analyze your Monthly Installments vs Inte...

Analyze data by using One Variable Data Table?

While you need to check every final result for changing a cell or two cells values, you can use Data Table option to analyze and take your decision. Assume that, you will take a loan from a Bank. Now Bank will inform you the maximum term of loan payment. If the term is long then interest amount is much more than short term. So, you can calculate through Data Table how much you can pay per month to the bank maximum to reduce the loan payment term. This calculation you can do it by using Formulas , Scenario or Goal Seek . But if you want to do fast calculation, then you can take help from this Data Table option. What is Data Table? Data Table is a feature located in What-If Analysis under Data menu in Excel . In my opinion, it is nothing but a faster way than Excel formula to analyze every single situation while changing one or two cell's values that are depends the situation. For this reason, this analysis is also known as Sensitive Analysis . Because it shows you ev...

How to use the powerful Scenario feature in Excel?

Scenario is very helpful tool in Excel for Data Analysis. Not only in our professional life but also it can be used in our personal life. It helps to get the perfect decision for your problems. This is the secret of the Scenario tool. Now let's start working and destroy your problems.   The Problem:   Assume that, you want to purchase 30 units of Smart Phones for your own business. The unit price is 11000 BDT. The Carrying Cost, VAT and others cost in total 2500 BDT. So, you have invested for your business is total 332500 BDT. (30 * 11000 = 330000 BDT + 2500 BDT). Now you need to take a decision about " what will be the Sell-Out price per unit of your product to earn a suitable profit? ". In Excel sheet plot this problem like below:   Image 1: Problem plotting In E3 cell the formula is =D3*C3 . F3 cell contains an amount fixed 2500 . Total Invested Amount in G3 cell =E3+F3 . In I3 cell the formula is =H3*C3 and in J3 cell the formula is =I3-G3 .   ...

How to analyze data through Goal Seek in Excel?

Goal Seek is a feature that included in Excel. Do you know what is the excellence of Excel? It is the formula, with reference cell. If you change any reference cell value, then it will automatically changed the result where formula entered. Goal Seek works similar like this. The users who don't know about the use of a Goal Seek, are no need to afraid. Just remember my below thing: Assume that, you have a value 3 in A2 cell and 5 in B2 cell. If you enter =A2+B2 in D2 cell, then it will return 8 in D2 cell. Now if you change the value of A2 cell to 4, then in D2 cell it will automatically return 9 (i.e. 4+5). This is the point I'm trying to explain. While you change a cell value, your result in D2 changed automatically just because of formula entered in D2 cell.     Bullet Point of Goal Seek: Goal Seek is a similar process to find the result like a formula explained above. But the fact is, in above example you have changed the value in A2 from 3 to 4 to ge...