Faysal Easy Excel

Converting any TEXT (actually numerical value but in TEXT mode) value to NUMBER or from NUMBER to TEXT is a daily routine in Excel. Many array formulas while you are trying to Lookup a Numerical Value but actually that was in TEXT mode, you need to convert your Lookup value first and then start lookup. Sometimes reverse of it. So, if you are facing this problem, then learn this trick which you help you a lot in Excel Calculation. Convert TEXT to NUMBER: There are 2 main ways to convert a Text value to Number. These are: (a) Use "+0" after formula: After removing extra spaces or took the number with LEFT, RIGHT, MID etc command, use a "+0" (Plus Zero) at the end of the formula immediately. For Example: Image 1: Using "+0" formula (b) Use VALUE(): This is another way to converting a Text numerical value to Calculative numerical value. Use this VALUE() within the entire formula. For Example: Image 2: VALUE() If this VALUE() failed

To Reset the Quick Access Toolbar in Default mode, you need to follow the below process: Step 1: Click on File | Options | Quick Access Toolbar and Click on Reset button. Image 1: Reset QAT Step 2: This will show 2 new options. Click on what you wish. In this case I would like to Click on Reset only Quick Access Toolbar . Step 3: Click on Ok .

Many times it is seen that, MIS Executives need to Export or Import their customized QAT or Ribbon settings because of their Laptop or PC changing. In my case, I've got a new laptop :) with newly installed MS Excel 2013. I have few custom commands in my QAT and also in Ribbon. It is time costing issue to customize them again. So, I've to choose Import them all in my newly installed Excel 2013. But before I do it, I need to do Export QAT or Ribbons first from my old Laptop. So, let's see how in details for you. Export customized QAT or Ribbons: Step 1: Open a New MS Excel Workbook in old Laptop. Step 2: Click on File | Options to open the Excel Options dialog box. Step 3: Click on Quick Access Toolbar option and Select the Export all customization from Customizations option. Image 1: Export QAT Step 4: After selecting this option, you will see a File Save dialog box. Now locate a location (example: Desktop) and Click on Save button. Make sur

All we know that, Index-Match can returns Text or Numbers. But did you try to return any image with Index-Match formula? I guess No. Yes, Index-Match formula both can return image also. So, want to know how to do it? Ok, let's try. My Simple Project: To do this, I would like to design a project first. My project is, for analyzing purpose I need to see the Phone Specification of many Brands and their many models. Assume that, your Management asked for a report where, they will just select a Model Name, and the details will display including image. To make this example realistic, I have used Motorola Mobile Phone Brand and their few models and features, which I've collected from www.gsmarena.com. My raw data sheet format are given as below but it would be better if you gather more data of all Brands and their models with image and BDT Price. Please note that, RawData is the Sheet name where raw data gathered. Image 1: Raw Data format Report Format: After gather

In many case, in our professional life, we need to do Copy and Value Paste or Copy and Formula Paste in all most always. Those employees, who works under MIS Department, have to do it lots of time in a single day to prepare reports. It is very time costing task in Office. Many of us use Excel Menubar to do it, others are using Keyboard Shortcut by pressing ALT key (like for Value Paste Press ALT+H+V+V and for Formula Paste Press ALT+H+V+F ). But did you noticed that, these two ways strongly need your attention to do this task. More clearly, if you choose Value Paste from Menubar , then what you need to do? First you should take the mouse in Hand Wheel the mouse in Home menu Then Click on Paste Then you pressure your eyes to find the Value Paste icon And then Click on it to paste it These all steps can take more than 1 second. Am I right? Below is an image of this process: Image 1: Value Paste from Home Menu On the other hand, if you choose the shortcut f

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: 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.

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

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