tech2touch

Author: Aniruddh

  • Format Cell using EXCEL function

    In EXCEL, we can format the numbers into different formats using the Format option. In some instances, we need to format the cell value which is a result of another formula. In other output from EXCEL format should be in the desired format. There is a EXCEL function called TEXT. This is useful for this. In this post we will learn the way to format cell using a EXCEL function TEXT.

    Format Cell Values using EXCEL Function

    We can use TEXT excel function to Format Cell in Excel. This method is useful when we need to implement formatting inside a EXCEl formula.

    Syntax of Excel TEXT Function

    =TEXT(value, format_text)

    Where
    Value is the number which we want to change the format.
    format_text is the required format.

    Examples of converting the format of values in Excel using formula

    We have a value 41479 in cell A1.
    =TEXT(A1,”dd-mmm-yyyy”)
    The above formula changes the format of the value in cell A1 to dd-mmm-yyyy format(24-Jul-2013).

    Similarly you can use any format in which you want to display the number. Below are some more example for the same.

    • =TEXT(A1,”#,##0″) this will give output as 41,479.
    • =TEXT(A1,”£#,##0;-£#,##0″) which gives output in the format £41,479.

    Like this you can use your own formatting using this excel formula. The biggest advantage of this excel function is that you can use this formula inside another formula or function to format the output.

    For example you want to concatenate date and a text . In Cell A1 we have “Today Date is” and in cell B1 we have 24-Jul-2013. Now if we use normal concatenation.
    =A1&B1 the output will look like this. Today Date is41479. So we need to use the text formula to convert the number in cell B1 to Date and then we can concatenate. The modified formula look like this.
    =A8&TEXT(B8,”dd:mmm:yyyy”) which shows the output as Today Date is24:Jul:2013.

    You can even use this formula with other excel formulas such as vlookup, hlookup. The formula to change the format of vlookup formula out is as mentioned below.
    =TEXT(VLOOKUP(B15,A13:B13,2,FALSE),”DD:MMM:YYYY”)

    Hope this helps to create your excel templates by using the Formula to Format Cell.

  • Excel If Function – Syntax And Examples

    Excel IF function is one of my favorite function in Excel. It is very useful and very simple to use. It does wonders when you want to create some complicated excel Formulas. You can extend the functionalities of IF function by nesting it or using along with other Excel Functions such as VLOOKUP, AND, OR etc.
    How Excel IF Function Works?
    Excel IF Function  checks for a condition(Criteria) and returns a value if the condition is met or returns another value if the criteria is not met. The criteria can be anything such as comparison, text check or error check etc. You can use nested IF functions upto 98 times in a excel worksheet.

    Syntax of Excel IF Function

    =if(condition, value if the condition is met, value if condition is not met)

    Examples:
    =if(A1>10, “Greater Than 10”, “Less than 10”)

    The Above IF formula returns Greater than 10 if the cell A1 has the value greater than 10 else returns Less than 10.

    Using IF function with AND

    As said earlier, you can use AND function inside IF  while you want a value to be written provides multi conditions were met. AND function returns TRUE if and only if  all the conditions are TRUE.For example, you have a value in cell A1. You want to check whether the number is in between 10 to 15. You can use the IF function as mentioned below.
    IF(AND(A1>10,A1

    Using IF function with OR

    IF and OR is used when you want to check any of the conditions is met. The OR Function returns true if any of the condition is met. For example you have want to check out of range if the number in cell A1 is less than  0 or greater than 100. You can use IF function with OR to calculate this as mentioned below.
    IF(OR(A1>100,A1

    Using Nested IF Function

    Using IF functions inside another IF function is called as nested IF functions. It is useful when you want to dig into the more detailed checks. For example you want to check if the number you have entered is a negative or positive in the cell A1.  First we need to check whether the entered value is number. If it is a number, then we need to check whether it 0. Then we need to check whether it is greater than 0 or less than 0 to find out the value entered is positive or negative. The nested IF function in Excel looks like as mentioned below.
    =IF(ISNUMBER(A1), IF(A1=0,”NIL”,IF(A1>0,”Positive Number”,”Negative Number”)),”It is not a Number”)
    The above uses ISNUMBER function to check for the whether the entered value is number or not.
    To learn how to use IF function with vlookup read my post on how to do vlookup on different ranges based on criteria.

  • Hiding or Unhiding Rows and Columns in Excel using Buttons

    Have you seen a excel spreadsheets with + or – symbols which on clicking unhides or hides rows and columns?  You might think that, it is done using Excel VBA. But this is not done by any VBA code. It is a simple grouping features of Excel. It is very useful when you want to display only the summary and show details with single button click. Lets start learning how to group and ungroup in Excel.
    For example you might have data which contains datewise data along with Weekly summary. You can show only the summary and provide the option to user to view the details using the + or – buttons

    Steps to Group Rows or Columns in Excel

    Step 1: Select the cells you want to group it. You can choose rows or columns according to your requirement. In this example i am selecting the rows from A2 to A5.
    Step 2: Go to Data and click on Group and select Group once again.  Your selected rows or columns are grouped.
    Step 3: There will be  + sign when your details are grouped and showing only summary. Where clicking on + sign will expand the group

    Steps to Ungroup rows or Columns

    Step1: Select the rows or columns you have Group enabled.
    Step2: Go to Data and select Ungroup and choose Ungroup.
    Step3: The Group is removed.

    As mentioned earlier, grouping in excel is very useful for presenting the data in good manner. Advantages of grouping is it shows the summary and it required, the all the details are also there. Unlike simple row/column hiding, in grouping you will will have symbol which indicates that, there is a grouping.

    Hope this Excel tips is useful. If you find this guide useful, then please share it with your friends. Any queries and support needed, then leave a comment below.

  • How to Run PHP Scripts Using XAMPP

    We all know that, XAMPP is a free open source cross platform web server solution stack package, which consist mainly Apache HTTP server, MySQL server database and interpreters for script written in the PHP and Perl Programming languages. It is a good tool for learners of PHP, Perl or mySQL. Here is the simple steps to installing the XAMP and start using this for running your PHP scripts.

    Steps to run PHP Scripts Using XAMPP

    1. As said above, you can download XAMPP for free. You can get your free copy from here.
    2. Open the downloaded file and install the applications.
    3. Once installed, run the XAMPP control panel and start Apache and MySQL servers. Enable MySQL if your program needs database.

    running PHP scripts in XAMPP

    4. Click on the ‘Explore’ button on the XAMPP control panel. The new window opens with file explorer.  Search for the ‘htdocs’ folder.
    4. htdocs folder is the folder Apache server points to. In this folder you need to place the  PHP scripts your want to run.
    5. Open the browser and enter the address http:// localhost/yourphpscript.php, where yourphpscript is the script name of your php code.
    6. You can even create sub folders inside htdocs to place and run php scripts. If you are using folders insider htdocs, then you need to enter the following path in the browser address bar.
    http://localhost/foldername/yourphpscript.php where ‘foldername’ is the name of the folder which contains php scripts  created inside your htdoc folder.
    7. Your browser will execute the script and show the output.

  • Type Rupee Symbol in Excel 2010

    Ever since the introduction of Rupee symbol, usage is also increased. When we type, we would like to use Rupee symbol rather than Rs. I have already made a tutorial on how to type rupee symbol in keyboard windows. That post is useful if you want to write rupee symbol in MS word or blogging platforms such as blogger, WordPress etc.

    Recommended Reading:

    Type INR Symbol in Word

    Conditional VLOOKUP in EXCEL

    However, if you want to use Rupee symbol in  Excel 2010, there is a simple way than that. You can easily insert rupee symbol in excel spreadsheet by choosing the currency symbol rupee in the format tab. There is no need to install any update or any change of keyboard layout if you want to use Rupee symbol in excel 2010. Just follow the steps given below.

    Steps to Insert Rupee Symbol in Excel Worksheets

    Step1: Enter the values as desired.
    Step2: Select the cells which you want to insert rupee cell.
    Step 3: Right click and go to format cells.
    Step 4: Select Currency from the category.  In the symbol drop down, scroll down till you see ₹ English (India).

    Step 5: Click Ok. Your cells are converted into currency and it shows along with the rupee symbol.
    Hope this help on inserting rupee symbol in excel. If you have any difficulties, please share in the comment section.

    Note:
    • If you are not able to find English (India) under the symbol, then you need to install the keyboard layout and make it as default.
    • If you want to enter Rupee symbol while entering in the cell, you can use shortcut key Ctrl+Alt+$(4) combination.
    • If you want to update many rows, then you need to use the method which I have mentioned above.
    • This method of typing Rupee symbol works with Excel 2016 also.

    Video guide on how to use Rupee Symbol in EXCEL