Rafiul Haq
XML or “Extensible Markup Language” is a file format, which is mainly used for storing and transferring data. Microsoft Excel supports this file type. If you are looking for ways to open an XML file in Excel for income tax, then you are in the right place. We will show you how to do that in 2 easy ways.
Download Practice Workbook
2 Handy Approaches to Open XML File in Excel for Income Tax
We will open an XML file that contains tax-related data in Excel. If we open that file in Notepad, then the file will look like this. There are three columns: “Particulars“, “Formula“, and “Amount“. The rows are denoted by the “<record>” tag and we have 5 rows in this XML file.
- The dataset shows the following information:
- Gross Income → $14,500.
- Total Deductions → $2,500.
- Total Exemptions → $1,800.
- Taxable Income → $10,200. If we subtract the total discounts (total deductions and total exemptions) from the gross income, then we will get the value and the formula is shown in the adjacent cell.
- Tax → $1,020. It is 10% of the taxable income in our case, so we get the value. Moreover, we have shown this formula in the previous cell.
1. Using Power Query to Open XML File in Excel for Income Tax
We will use the Power Query feature to open the XML file in Excel for income tax.
Steps:
- Firstly, from the Data tab → Get Data → From File → Select From XML.
- This will bring up the Import Data dialog box.
- Secondly, navigate to the directory and select the “source.xml” file.
- Thirdly, press Import.
- After that, the Navigator window will pop up.
- Then, select “record” from the Display Options and press Transform Data. We can see under the “Formula” column, that the data are shown as “Table”. So, using the previously mentioned feature, we will show the actual data.
- So, the Power Query Editor will pop up.
- Then, select the icon on the right side of the “Formula” column and press OK.
- By doing so, it will expand the data to show the actual values.
- Afterward, from Close & Load → select “Close & Load To…”.
- Now, here we have two formulas to calculate income tax for an individual.
- The first formula calculates the amount of taxable income by subtracting all the exemptions for the gross income.
=14500-2500-1800
- The second formula finds the amount of tax, which is 10% of the taxable income.
=10200*0.1
- Therefore, another Import Data window will appear.
- Then, select the Existing worksheet and set cell B4 as the output location.
- Next, press OK.
- After that, we will see the XML file for income tax is opened into Excel.
- Lastly, we have modified the dataset a bit and this is what the final output looks like.
Read More: [Solved]: XML File Not Opening in Excel (3 Suitable Solutions)
2. Utilizing Developer Tab to Open XML File in Excel for Income Tax
In this last method, we will utilize the Developer tab to open an XML file for income tax.
Steps:
- To begin with, we need to enable the Developer tab in Excel.
- So, press Alt, F, then T to bring up the Excel Options window.
- Then, from the Customize Ribbon option → select Developer.
- Afterward, press OK.
- After that, from the Developer tab → select Source under the XML group.
- Then, select “XML Maps…”.
- After that, click on “Add…”.
- Then, navigate to the folders and select the XML file named “Source.xml”.
- After that, press Open.
- Next, a warning message will pop up.
- Press OK.
- Then, it will populate the XML Source.
- After that, drag the “record” element and release it on cell B4.
- Thus, it will define an XML map on the Excel file.
- Then, select cell B4.
- After that, from the Developer tab → select Import under the XML group. This will again bring up the Import window and we will point to the file location one more time.
- Then, the output will look like this.
- Finally, after some formatting, our opened XML file for income tax will be similar to this.
Read More: VBA Code to Convert XML to Excel (Apply with Quick Steps)
Practice Section
We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.
Conclusion
We have shown you 2 quick ways to open an XML file in Excel for income tax. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!
