Faysal Easy Excel

There are Many ways to remove extra spaces but in this tutorial I'll share 2 most common ways to remove spaces between words or numbers in Excel cells. These are: TRIM formula, Excel Find and Replace options. Why it happens: When you copy paste data from an external source to Excel then it can be happens. For example, if you copy a data table from Web Based Software or Web Page and then Paste them to an Excel Worksheet, then an extra space will add along with your each cell values. This space usually add at the end of the cell values, but you can also find them at the beginning or in the middle point of a cell value. For this additional spaces your data table looks disorderly and it becomes difficult to work with. For example if you use Find option to find a First Name "Bill Jillen" which has no extra spaces, then it will not found in your data table. Thus it is very important to remove spaces between words or numbers in Excel cells to do good data analysis. The Solutions...

    To make your favorite and most used commands accessible easily, you can add your own tab to the Excel ribbon. You will find it very convenient to have your own tab and most used commands at your fingertips. In this video you will learn how to create your own custom Tab in Excel ribbon 2016. Also you can learn how you can perform with your custom Tab in Excel Ribbon.

    ROUNDUP function in Excel helps you to round a specific number to nearest upper or next number. More specifically it converts a specific mentioned number to nearest upper number. It can convert both Integer numbers (Whole numbners) or Decimal numbers as user requires. For anyone who performs financial or data analysis in excel, it can frequently be important to round a number. In this video, I will show you how to round a number up in Excel using the ROUNDUP function. This formula will be a major help in saving your time when performing financial modeling and it will also improve your accuracy in Excel.

Have you ever tried to insert Emoji (Icons) in your Excel Formulas? Let's learn it. In Windows 8 or later version, you can insert Emoji. If you are still running Windows 7 or earlier version, you can insert Symbols by clicking "Insert > Symbols" instead of Emojis. Use Emojis or Symbols in your Excel Charts, Formulas to make your report attractive. Emoji picker shortcut key for Windows: Windows Key + "." or Windows Key + ";" Emoji picker shortcut key for Mac: Control + Command + Space Bar

Just now I've found an interesting formula to find the first no sales date by lookup with First Blank Cell. I was trying to create a Daily Sales Tracking Chart with a combination of Line Chart and Area Chart in Excel 2016. For that, I have designed my data table as below: Image 1: Sample data table to find the first no sale date Now, In F2 cell, I was trying to find the first no sales date by matching Sales is blank. I have used below Array Formula to get my result in F2 cell: =INDEX(B3:B32,MATCH(1,ISBLANK(C3:C32)+0,0)) And surprised to get the result. This tutorial is described in another way like "How to match blank cell in Index-Match formula". I know that you can easily find the 9-Jun-20 with below LOOKUP formula, like this: =LOOKUP(2,1/(C3:C32<>""),(B3:B32))+1 or Array Formula: =INDEX(B3:B32,MIN(IF(C3:C32="",ROW(C3:C32)))-2) Hope this tutorial will help you sometime in your excel world. Thanks.

Assume that you have a file that contains TEXT value in Column A2:A4 . In each cell a special delimiter (character) has used " ^ ". Now you wish to separate rest of the text after " ^ " character to another column. If I say more clearly and in short then: You have this: Image 1: Sample data And you want to convert it like this: Image 2: Converted data Seems it is easy. But the problem is, + (Plus) symbol is the starting character of next column. And if you start a cell value from + (Plus) then it will converted as a formula "=" (Equal) character in excel and returns an #NAME? error: Image 3: Converted data If you take a close look then you will see + symbol converted as Formula character and returns #NAME? error due to =ebook1.php is not a Microsoft Excel function. Solution: So, how you can convert it as same as in image 2 shows? Well, follow the below steps: Step 1: Select A2:A4 cell. Step 2: Click on Dat...

Assume that, you have a Table1 with "First Name" "Last Name" "Sale" column and another Table2 with "Name" ("First Name" and Last Name"), "Sale" column. Now you wish to return the "Sale" quantity from Table1 to Table2 by matching "Name" (from Table2 field) with "First Name" and "Last Name" (from Table1). Image 1: Table1 (A1:C5) and Table2 (D8:E12) Solution: To do this below formula can help to return the "Sale" quantity from Table1 to Table2: =INDEX($C$2:$C$5,MATCH($D9,CONCATENATE($A$2:$A$5," ",$B$2:$B$5),0)) You will see the below result: Image 2: Result  You can download this file from below link: Lookup a single cell but match with 2 cells

Getting tired by using Text to Column option to convert a Text into Column after a specific character each time? If so, then you can use a formula to split a Text into columns after each time a specific character appeared in the Text in Excel. Want to know how? Check the below example. Sample Text: Before starting our study today, I'm going to show a sample data to make clear that what exactly the data is and what we need: Image 1: A2 cell contains the Sample Data And we are going to do: Image 2: Convert the Text (A2) to Column (B2:F2) Solution: In cell B2 write the below formula and copy right to the F2 cell: = SUBSTITUTE( MID( SUBSTITUTE($A2," # ",REPT("-",LEN($A2))) , (COLUMN(A2)-1)*LEN($A2)+1,LEN($A2)) ,"-","") So, how this formula works? Well, we need to took a starting point after a certain number of characters and then add a sample character ("-") specific time LEN($A2). And finally delete the a...

In real life data, sometimes you need to get a result by matching 2 or more criteria. In most cases we use Data Filter, to count or get the result manually. But when your time is too short to do this, then you need to depend on Advance Formula. Advance formulas are those where 2 or more Excel Functions included. Sample Data: Assume that, we have a data table like below image: Image 1: Source Data Table We have to get the Working Days in below Data Table: Image 2: Target Data Table (1) SUMIFS: In I3 cell enter the below SUMIFS formula to get the result: =SUMIFS($D$2:$D$12,$A$2:$A$12,$F2,$B$2:$B$12,$G2,$C$2:$C$12,$H2) Image 3: Result through SUMIFS formula In SUMIFS formula first I have selected the "Working Days" column range $D$2:$D$12 from source data table, which I need to get. Then I have selected a range for First Criteria "Name" from the source data table as $A$2:$A$12 and mentioned the criteria as F2 ("Deb") fr...

In many cases, you need to calculate the last date of a month based on a given date. If I give you an example then it would be very clear. Assume that, you got a joining date and question is how many days you have worked on that joining month? To find the last date you can take help from EOMONTH() function. But in this article I will show you how you can do that in 3 ways. Before going to start design your worksheet like below image: Image 1: Before calculate the last day of the month 1. EOMONTH(): In EOMONTH() function "EO" means "End of". This function needs to supporting values to calculate the last date of the month. First is the "start date", means a date based on you need to calculate the last date of that month. And second is, which month? Here you should remember: 0 = Current month based on given date 1 = Next Month -1 = Previous Month. You can use Next 11 months and previous months by using 11 or -11. Ok, enough describing. Now ...

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

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

We use VLookup() in most of the case due to it is very easy to compare or populate your data table from a reference data table. But each time we apply a Vlookup() for generating next column data, then we have to change manually column number 2 to 3 or 4 or 5 etc. It is not time consuming. As a professional user you need to consume your time. So you need to apply it fast and submit the report. But the question is, what is the way to get automatically column number which will increase automatically and return data from reference source data table? It's nothing but a COLUMN(). Column(): This function generally return the Column() where your Column() is present. For example If you type in D115 cell below function and press enter: =COLUMN() Then it will return, D = 4 and nothing else. You can also use this function in the same cell D115 by this way: =COLUMN($G205) This function collected data from G205 and as G = 7 (A = 1, B = 2, C = 3, D = 4, E = 5, F = 6, G = 7) it retur...

Text to Column converting is an important thing to in Excel due to almost all types of raw data need to collect from others software or Web Applications which was mostly unstructured to make a report on it. In this article you will learn how can you convert a cell into Columns that contains Text with "Enter" and ";" character. Step by Step: Assume that you have below data: Image 1: Sample Data Now you want to convert the data like below: Image 2: Converted data (1) To do this, first of all make sure that, your B1 and C1 cell has set to Wrap Text. If not then Click on Home | Wrap Text to activate it. (2) In B1 cell use the below formula to collect data before ";" character: =LEFT(A1,FIND(";",A1)-1) This formula, took the LEFT mentioned numbers of characters found with FIND and matched character. (3) Now to collect the value after ";" use the below formula in C1 cell: =RIGHT(A1,LEN(A1)-FIND(";",A1...

In many thesis or research you need to calculate data in Excel worksheet and present the calculated data with reference in MS Word. But many of us use the Table in MS Word for calculation like MS Excel does. Most of the time we failed because a Table can't do all the things like Excel fast. So, the question is "Is there a way to use the MS Excel spreadsheet in MS Word properly"? The answer is Yes. You can use your Excel in Word. The process is very simple, which described as below: Sample Excel Worksheet: Assume that, you have calculated a "HR Salary Sheet" in Excel like below: Image 1: HR Salary Sheet Way 1: Copy and Paste Special: (a) To present it in the MS Word page, just select the data table ( B3:I14 ) cell and press Ctrl+C to copy them. (b) Now open your MS Word and Click the Paste Special option. Image 2: MS Word Paste Special option (c) While Paste Special dialog box appears, Select Paste radio button and then Microsoft Ex...