How to Open XML File in Excel for Income Tax (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Open XML File in Excel for Income Tax 1


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 DataFrom File → Select From XML.

How to Open XML File in Excel for Income Tax 2

  • This will bring up the Import Data dialog box.
  • Secondly, navigate to the directory and select the “source.xml” file.
  • Thirdly, press Import.

How to Open XML File in Excel for Income Tax 3

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

How to Open XML File in Excel for Income Tax 4

  • So, the Power Query Editor will pop up.

How to Open XML File in Excel for Income Tax 5

  • Then, select the icon on the right side of the “Formula” column and press OK.

How to Open XML File in Excel for Income Tax 6

  • 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

How to Open XML File in Excel for Income Tax 7

  • Therefore, another Import Data window will appear.
  • Then, select the Existing worksheet and set cell B4 as the output location.
  • Next, press OK.

How to Open XML File in Excel for Income Tax 8

  • After that, we will see the XML file for income tax is opened into Excel.

How to Open XML File in Excel for Income Tax 9

  • Lastly, we have modified the dataset a bit and this is what the final output looks like.

How to Open XML File in Excel for Income Tax 10

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.

How to Open XML File in Excel for Income Tax 11

  • After that, from the Developer tab → select Source under the XML group.

How to Open XML File in Excel for Income Tax 12

  • Then, select “XML Maps…”.

How to Open XML File in Excel for Income Tax 13

  • After that, click on “Add…”.

Add XML Map

  • Then, navigate to the folders and select the XML file named “Source.xml”.
  • After that, press Open.

Import XML File

  • Next, a warning message will pop up.
  • Press OK.

Warning Message

  • Then, it will populate the XML Source.

XML Map Elements

  • After that, drag the “record” element and release it on cell B4.

Dragging XML Elements

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

Developer Tab XML

  • Then, the output will look like this.

Developer Tab Output

  • Finally, after some formatting, our opened XML file for income tax will be similar to this.

Formatting Output

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.

Practice Dataset


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!


Related Articles

Rafiul Haq
Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo