Select Page

Income Tax  Department  has provided User Friendly utilities in Excel format, called the Return Preparation Software, to make it easier for Income tax payers to fill his returns. These may be used by the individuals or organizations to file their returns electronically.In this article we shall explain the excel file used for income tax return, answering questions like why excel, what are macros, how excel file for Income tax return form is , how to fill the data, validate it and Generate XML file.

Overview of Process of E-filing

Process to efile your returns using this is given below. Our article E-Filing of Income Tax Return explains e-filing of Income Tax Return in detail.

To e-file your returns you may download Excel based utility from the official E-Filing website,incometaxefiling.gov.in and start the E-Filing process.

  1. Download the applicable ITR form which is zip file which has Microsoft excel file also called as Return Preparation Software
  2. Fill it offline
  3. Generate a XML file and save it to your computer.
  4. Logon to incometaxindiaefiling.gov.in (If you don’t have the Login you need to register. Our article Registering on Income Tax efiling Website explains it )
  5. Go to e-File->Income Tax Return and Upload your XML file (saved in step 3)
  6. Choose whether you want to use Digital Signature or not
  7. On successful upload acknowledgement details would be displayed.
  8. If you did not use digital signature the Income Tax receipt, ITR-V, needs to be printed out and sent to Central Processing Unit (CPC) Banaglore within 120 days of filing return.
Excel based utility of Income Tax Department

Excel based utilities are available for all the Income Tax Return forms (ITR). Excel format was chosen because it is a very powerful tool and easily available. Excel is everywhere you look, especially in areas where people are adding up numbers a lot, like marketing, business development, sales and finance. Excel file is organized as Workbook which contains one or more Worksheets. An Excel worksheet is a single spreadsheet that contains cells organized by rows and columns. Each worksheet can contain a different set of data. For example, one worksheet can contain your business, sales data, a second can contain your inventory and a third can contain your expenses.

With intuitive interface, outstanding computation ability and excellent chart tools MicroSoft Excel is the most popular spreadsheet software in the world.  Free Personal Finance Calculators has excel based calculators for Mutual Fund Portfolio Tracker, FD vs Debt Fund Returns Comparator etc. Features of excel that are used in income tax return utility are:

  • Income Tax Return Excel Utility has data organzied in various sheets like Income Details, TDS etc.
  • Complex computing tasks can be done using the powerful Visual Basic (VBA) Macros . For Income Tax Forms Functions like validating the fields, information in sheets, generating the XML file are possible because of Excel.

Any taxpayer who is familiar with Microsoft Excel, and has a copy installed on his computer, can download the excel form from the incometaxefiling.gov.in website and use the excel file to then fill in the details and upload back the generated XML. It’s advantages are as follows :

  • Downloadable by any taxpayer from the website. Easy to maintain.
  • No additional software required other than Microsoft Excel. Convenient to use even for a beginner with Microsoft Excel.
  • The screens of the utility look very similar to the ITR Forms.
  • Customizable for a taxpayer  ex: so as to reduce the sheets which are not . Has features which allow the taxpayer to create as many rows as required for most of the schedules
  • Extensive inbuilt validation before generation of XML

It’s disadvantages are :

If you are not comfortable with Excel then it might seem daunting. It also expects you to have basic knowledge of Income tax.  But we at bemoneyaware  are trying to make it simpler.

Excel based utility differs from tax filing sites like Tax-Shax, TaxSmile which have to some extent simplified the task of e-filing (at a price!). By asking unambiguous, clear-cut questions about the nature and amount of your annual income the what and the how much, it systematically draws relevant income tax filing information from you, without requiring you to get into the nitty-gritty areas of income tax law interpretation and computations. Please note we are not recommending the tax filing sites  but just highlighting the difference. In any case you should be aware of what information needs to be filled in and why ?

Installation of the Excel utility of Income Tax Return

Excel utility that can be downloaded is a zip file i.e for example Excel file for ITR1 for 2012 is named ITR1_2013.zip. One needs to extract the excel file , with extension .xls, from the zip file. For example Extracted file from  ITR1_2013.zip is 2013_ITR1_PR3.

Zip file is a computer file carrying the extension .ZIP in which contents of one or more files are compressed for storage or transmission. Size of

 Size of  ITR1_2013.zip is 427 KB while size of excel file 2013_ITR1_PR3 is 1512 KB.

Extract the xls file from the downloaded zip file using WinZip or other extracting software. This can be done by using Right Click on the downloaded zip file, and selecting the option to Extract to here under WinZip menu. This step will create an .xls file in the selected folder.

Tip: Create a folder in your computer for tax purposes so that the files remain in an organized manner in the folder. Copy the downloaded zip file into this folder. This will also help in searching for files and maintaining files in the future.

What are Macros : How To check and enable macros

Open the excel file downloaded, in the above example  is 2013_ITR1_PR3. This file is the Return Preparation Utility for ITR1 for AY 2013-14. The exact file id in your case may vary depending on the ITR Form and the Version or Revision or File ID of the particular ITR Form. You may be asked to Enable Macros as shown in picture below,depending on your security settings (and Excel Version)

Enable Macros warning

Enable Macros warning

What are Macros?

If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a series of commands and functions that are stored in a Microsoft Visual Basic module and can be run whenever you need to perform the task. For example, if you often enter long text strings in cells, you can create a macro to format those cells so that the text wraps. In Income Tax return form validation of whether all mandatory fields are filled, whether information filled in a field is in correct format example : PAN or TAN  number has 10 digits, generation of XML file is possible because of macros. Microsoft’s Macros demystified: What they are and why to use them explains it in detail.

Macros and Security : Macros can be harmless or  harmful (containing destructive code that causes harm to your documents or your system), based on the intention of the person who has written the code. Hence macros are an important security issue and it is suggested that  to protect your system and your files, do not enable macros from unknown sources. One has the option to enable or disable macros,by setting macro security in your Office program.  Ex: Macro Security of Medium provides you with the option to enable or disable macros anytime you open a file that contains a macro, but will allow you to run any macros you choose. So depending on the security of Macros in your excel file you might be asked to enable the macros.

Check macro security level in your excel file. The screen menu for selecting Macro Security option may change across Excel Versions.

  • Excel 2003 : Select Tools-> Macro-> Security. Set the Security level to Medium and Click on OK.
  • Excel 2007 : Click
    • Excel Options –> Trust Centre –> Trust Centre Settings –> Macro Settings –> Enable all macros
    • AND
    • Excel Options –> Trust Centre –> Trust Centre Settings –> ActiveX Settings –> Enable all controls without restriction and without prompting
  • Excel 2010 :When you open the excel file, the yellow Message Bar appears with a shield icon and the Enable Content button. Click on the Enable Content to enable the macros. (as shown in picture above)

Ensure that Macros are enabled in your utility. To confirm that your macros are enabled, type first name in lower case and press Tab to go to next field. Automatically, the entered First name value will get capitalized.

Understanding the excel sheet

When you open this excel file, you may be presented with some messages in a dialog boxes For Assessment Year, Green Cells are for data entry etc,  as shown below. These are for information purposes, read them and click OK. Note every year Income Tax Return forms are different due to changes in income tax laws. Hence be careful on choosing the appropriate form for the year. For income earned in Financial Year Apr 2012 – Mar 2013 , Assessment Year is 2013-14.

Assessment Year Info on opening excel file

Assessment Year Info on opening excel file

Info on opening excel file

Info on opening excel file

Finally, you will be presented with the ITR Excel Screen which will allow you to start entering the returns in the Excel based Utility.

Excel File is organized as follows :

  • It has buttons on the right hand side like Validate,Next, Help etc . Button may differ based on the sheet.
  • Area to fill information which is colorful with green cells , red marks in corner, white cells with blue labels etc as shown in picture below. You can use TAB key to move between cells
Excel ITR Form

Excel ITR Form

  • WorkSheets on the bottom as tabs such as  Income Details, TDS, Taxes paid and Verification, Instructions, 80G. You can navigate to any of the tabs by
    • clicking on the tab using mouse, or
    •  pressing Ctrl PgUp or Ctrl PgDn keys to navigate to the desired tabs.
    • Clicking the Next/Previous button on the sheet
  • Tabs in Income Tax Excel Form

    Tabs in Income Tax Excel Form

Colour codes used in the various cells

  • Green cells indicate data entry fields where one can enter information ex: First Name, PAN etc
  • Label of cells which is in red colour indicates mandatory fields for example : Last Name,PAN, Date of Birth
  • Label  of cell which is in black colour   indicate optional fields for example : First Name, Road,Phone
  • Down arrow in cells can be used to select from a list of options for that field  For example State, Employer Category. Selecting the State is shown below
  • Selecting from a given list of values

    Selecting from a given list of values

  • Red mark on the upper right hand corner of a cell provides help or more information about what needs to be filled in as shown in image below. Click on the arrow to get information about the cell.
  • Help for filling information in the field

    Help for filling information in the field

  • White cells with blue labels indicate auto calculating fields which should not be filled. These are calculated automatically based on information entered in other cells.
  • Excel blue cells - which will be filled automatically

    Excel blue cells – which will be filled automatically

Configuring the Excel Utility

The ITR2 to ITR6 utility has sheets with multiple schedules per sheet. Many of the schedules are optional and may not be required to be filled in by all taxpayers / tax payers. Before starting the usage of the utility, a taxpayer can decide which of these sheets are relevant for their eFiling and can choose to view only such sheets. For ex : If there are no House Property details to be submitted, the taxpayer can deactivate / hide this sheet . XML will only be generated hence for the active sheets.This is an optional feature just to help the taxpayer focus only on the relevant sheets and keep the non relevant sheets hidden.

Note: Do not hide or deactivate a sheet which has valid data already filled in. If you have data filled in a hidden sheet, XML tags will be generated for such a sheet.

Validation

In cell validations ensure that invalid data is not entered. For example Entering more than the allowed no of characters, entering negative values where not allowed, entering invalid PIN, TAN, PAN  etc.

Once you have filled up required data per sheet, you can validate that data filled in is correct by clicking on the Validate button provided on that sheet on upper right hand corner. On clicking this button:

  • If no issues exist, a pop message will tell you that the sheet is ok. You can then proceed to the next sheet to be filled up.
  • In case of any errors, the pop up message will indicate the error and the sheet on which the error occurs as shown in picture below. If the error is in a table, a number next to the field name will help you identify the row number where the error exists and the message details the error found. Once all errors are removed, clicking on Validate button will give a message that the sheet is ok.
  • Validating the data in excel

    Validating the data in excel

Issues that are more common while filling up the sheets, which will throw a validation error are

  • Mandatory fields not filled in. ex: PAN number in General Sheet.
  • Special characters filled up. Note: XML does not accept certain special characters such as <, >, &, ‘, “viz. Less than, Greater than, Ampersand, Single Quote and Double Quote characters. (These being reserved characters in XML)
  • Character length allowed is exceeded Ex : More than 75 characters in Town field
  • Negative value in fields that do not permit negative values
  • Greater than allowed numeric size Ex : Most numeric fields do not accept greater than 14 digits)
  •  Dates must be filled using dd/mm/yyyy format.A date such as 3rd June 1994 should be filled in as 03/06/1994. Invalid date field gives an error
  • Depending on the date field, the date is between a valid date ranges. Any error in filling appropriate date will of course be prompted by the utility.
  • Invalid PAN and TAN codes They must be a proper 10 digit code as required with no spaces
  • Invalid PIN Codes : They must be a 6 digit code with no spaces
  • Invalid BSR Codes They must be a 7 digit code with no spaces
  • Invalid MICR Codes They must be a 9 digit code with no spaces
  • Invalid Bank Account number They must not contain any character except “0” to “9” and must not exceed 15 digits.
  • Invalid Srl no of Challan They must be a number between 0 to 99999

Filling in the Excel sheet and Validating

Fill in the mandatory fields in the sheet and Validate each sheet, fix errors reported, before moving the next sheet. For ITR1 the process is outlined below and would be explained in detail in later articles.

  • Fill Personal Information, Filing Status, Income & Deduction in Income Details sheet and click on Validate button.
  • Fill Tax Deducted at Source (TDS) details from FORM – 16 ,in case of Salary and income other than Salary, in TDS sheet. Also fill the details of Advance Tax or Self Assessment Tax (if any) and click on Validate option.
  • Fill third, Tax Paid and Verification, sheet and click on Validate option.
  • The 80G sheet is used to provide information about any donations you might have made under the 80(G) section eligible for tax deductions. Of course, if you haven’t made any donations, leave the fields empty.

Computing taxes

  • Select the Income Details sheet(at the bottom of file) and click on Calculate Tax option.
  • Check Tax paid & Verification sheet , Tax Payable (row 17), if it contains non zero value that means individual needs to pay that much tax to Income Tax Department. After paying this amount, mention details of tax payment information in TDS sheet under Advance Tax & Self Assessment Tax Payments section.
  • If Tax Payable (row 17) in Tax paid & Verification sheet is zero then click on xml Generate option in Income Details sheet. It will create one sheet Pre-XML Check with Save XML button on it.

Generate XML, Pre-XML Check and Save XML

Clicking on Generate XML, will recheck all sheets to ensure that no sheet is invalid, and thereafter, will show a Pre XML sheet to reconfirm the count of the rows being generated in the various schedules as shown in image below. This count should be carefully crosschecked to see that the number of rows that are to be generated, are matching the filled in rows in each sheet.

Pre XML, Save XML

Pre XML, Save XML

Save XML button can be clicked to create the XML which needs to be uploaded to the incometaxindiaefiling website to complete the final phase of efiling the returns. When you click on this button, the utility will prompt with a message indicating the exact location and filename of the generated XML file. Go to this folder, and locate the file specified as per the message, and upload this file to the efiling website of Department of IncomeTax, India.

On successful upload you will be asked to download a ZIP file containing Form ITR-V. You will also receive this file in your e-mail inbox.Extract this PDF file and open it using the password, which is your PAN number followed by your date of birth, all in lowercase and with no spaces or special characters. As an example, assume that your PAN is AGHWR5148J and your birth date is 29-Jul-1979. In that case, the password will be: afhwr5148j29071979. Take a printout of this file, sign it, and mail it to the following address:

Income Tax Department – CPC, Post Bag #1, Electronic City Post Office, Bangalore – 560 100

Our articles can help you filling the Form ITR1 For FY 2012-13, AY 2013-14:

Related Articles:

e-Filing of Income Tax return using Excel based utility is not easy but not difficult also. It takes a little bit of patience and discipline like anything that you do new in life.

Share