Tech2 Touch

Tag: Excel Formula

  • Creating Templates in Excel

    Excel templates are very useful whenever we need to do repeated tasks. Excel VBA does it. But Excel VBA is a little hard to learn and implement.

    Excel templates is nothing but pre defined format. For example if you want to get Agent productivity report in Excel. You have raw reports which has productive hours, non productive hours and other details. Instead of doing every day look up and sum function, create template. That create a new sheet.  In that put all the fiormula or excel functions and required data. So now onwards whenever you need to do the report, just put the raw reports you will get output immediately.

    Advantages of Excel Templates:

    • Saves lot of time
    • Accuracy of the reports increases as manual work is reduced
    • No need to know complicated VB codes.
    • Get time explore new things
    • Do more analysis on data.
    Useful functions and tools to create Excel Templates: Below are minimum Excel formula you should know to create a simple and effective excel templates.
    • Pivot tables: This is one of the tool very useful. You can customize the data the way you want to. Create calculated fields in pivot table to enhance the look and get more data.
    • Vlookup and Hlookup functions: These lookup functions will be very handy while you need to map some fields based on some data criteria. 
    • Conditional sum and count:  Conditional sum function sumif and conditional count function countif are useful when you need to count or sum of the data for a particular data.
    • Multi conditional sum and count: There are many occasions we need to find sum or count based on multiple criteria. SUMIFS, COUNTIFS and sumproduct are the useful functions
    Tips on Creating Excel Templates:
    • Always create a template such a way that, the output of the template matches with desired report format. Do not give a scope for manual work on template on regular basis. This will save time.
    • Use Conditional functions to get ride of error codes such as #n/a.  For this IF function with iserror function will be very useful.
    • While selecting the range, select only the range you have data. Do not select complete sheet. Selecting whole sheet will slow down the template. Using Dynamic ranges will also helpful.

  • Removing Duplicates in Excel

    We sometime look for easy way to remove duplicate  entries from a large table without any filters or complicated formulas. Starting Office 2007, Microsoft added a direct method to remove duplicates from a range.  There is one shortfall in this method which I will describe later.

    • Use mouse or keyboard arrow keys with “Shift” button pressed to select the area which contains duplicate content.
    • Click on “Data” tab of excel and click “Remove duplicate” button in the multi function tool bar.
    • A dialog box pops up. In the dialog box define the columns of the area to be included in the comparison of individual rows. All cells of the two rows should thus not display the same content due to which the rows become duplicates of each other.
    • In the column field of dialog box, remove the check marks in front of the columns which needs to be ignored during the comparison and click ‘OK’.
    • The duplicates in the columns of excel has been removed.

    Note: If all the columns are not included in the comparison(when there are obvious differences between individual row),  Excel always first top most row out of the rows that have been identified as duplicate. This is needs attention when the previously excluded cells are required later.

    This method short fall is that, you will not be able to see which cells has duplicates. If you want to know which cells having the duplicate values, go to “Conditional formating” and select “Highlight Cell Rules”. In that, choose “Duplicate Values”.  In the pop up select color which you want to see for the duplicate values. Now, use a filter by color and select the color you have opted for duplicats. It will show all the cells with duplicate values. Once found, you can decide on which data need to be removed and which needs to be retained.

  • Rotating Tables in Excel

    After we create a  tables in Excel, we will realize that the columns could be better as rows and vice versa. So in this post we will have a look into excel tip which describes how to rotate tables or swapping columns and rows in Excel. This tip will work on Excel XP, Excel 2003, Excel 2007, Excel 2010. Swapping of columns and rows in excel using this method is very simple and it does not changes the data.

    How to Swap Row and Column in Excel Sheet

    Follow the steps given below, to perform swapping of rows and columns in a range.

    Swapping of rows and Columns in Excel

    • Select the area whose column and rows needs to be swapped.
    • Right click the selected area and select “Copy” if you want to create a new table keeping the original or Select “Cut” if you want to entirely replace the Table.
    • Select the cell where the table should be placed.Ensure that area where the we want to insert the Table should be the same as original.
    • Right click on your mouse or keyboard shortcut, select “Paste Special”.
    • A pop up dialogue box appears. Select the option “Transpose” which is places bottom right corner.
    • Select “OK”. Done. Your Table rows and columns swapped.

    If you are facing any issue for transposing the rows and columns then, please leave your issue in the comment section below.

  • Finding the Last Row in Excel using Excel VBA

    In excel there are situations where we need to find last row of an sheet. This can achieved by defining a variable and assigning the last row value to the variable and using offset property to find the last row.

    Method 1: Declaring the variable and assigning the last row to the variable.
    Eg:
    Dim FinRow   ‘Finrow is variable which hold the value of last row.
    Finrow=Range(“A65536”).End(x1Up).Row
    Now Finrow variable contains the last row number in Column “A”

    Now if you want to put total in the last row of Column “A” then use
    Range(“A”&Finrow+1).Value=”Total”

  • Conditional sum in MS excel using SUMIF

    In excel adding a range with one condition can be done with conditional formula called sumif. The conditional sum worksheet function  sumif adds the specified range based on the criteria. The sumif conditional sum accepts only one criteria. If you want to perform sum based on multiple criteria, then you need to use multi conditional sum formula sumifs.

    Syntax of conditional sum sumif Worksheet Function

    Syntax: =SUMIF(range, criteria, [sum_range]).

    Range: The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
    Criteria: The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. For example, criteria can be expressed as 32, “>32”, B5, 32, “32”, “apples”, or TODAY().
    Important Note:  Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks (“). If the criteria is numeric, double quotation marks are not required.
    sum_range:The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).
    Note: If you dont define sumrange then excel adds the range based on criteria. sumrange is needed if you want add other than criteria range.

    Examples for sumif worksheet function

    Below are the example for conditional sum in excel.

    Conditional SUM

    So sumif excel formula to add only Apple is
    =SUMIF(A2:A6,”Apple”,B2:B6)
    If you are having multiple criteria for sum then you should use  multi conditional sum refer to my previous post on multiconditional sum.

    You can also use Wild card charectors with SUMIF function. In that way you can even extend the usage of SUMIF functions.