How to Convert Hectare Into Bigha in Excel Sheet
If you work with land records, agricultural data, or property listings, there is a good chance you have run into the same problem again and again. Government records store land area in hectares, while buyers, sellers, and farmers on the ground still talk in Bigha. Doing this conversion by hand for a handful of plots is manageable, but once you are working with a spreadsheet full of entries, typing the same multiplication over and over is a waste of time. Excel can do this instantly for an entire column, and once the formula is set up correctly, it never needs to be touched again.
This guide walks through the exact formula, the correct state wise conversion factor to use, and how to build a small reusable converter directly inside your spreadsheet.
Why Hectare to Bigha Conversion in Excel Is Different From a Simple Formula
A hectare is a fixed metric unit. It always equals 10,000 square meters, no matter where the land is located. Bigha, on the other hand, is a traditional unit with no single fixed size. Its value changes from state to state, and in some states even from district to district. This means the Excel formula itself stays the same everywhere, but the number you multiply by, called the conversion factor, changes depending on which state the land belongs to.
Before setting up any formula, it is important to know the correct conversion factor for the relevant state, since using the wrong one will silently give an incorrect result even though the formula looks perfectly correct.
State Wise Hectare to Bigha Conversion Factors
The table below lists the commonly used conversion factor for each major state. This is the number you will use in the Excel formula.
| State | 1 Hectare Equals (Bigha) |
|---|---|
| Uttar Pradesh, Rajasthan, Bihar (Pucca Bigha) | 3.95 Bigha |
| Punjab and Haryana | 4.94 Bigha |
| Gujarat (Vigha) | 6.18 Bigha |
| Assam | 7.47 Bigha |
| Madhya Pradesh | 8.97 Bigha |
| Himachal Pradesh and Uttarakhand | 12.35 Bigha |
The Basic Excel Formula for Hectare to Bigha
Once you know the correct conversion factor for your state, the Excel formula is a single line multiplication. Assume that your hectare values are entered in column A, starting from cell A2, with a header in A1.
Formula (for Uttar Pradesh, Rajasthan, or Bihar)
=A2*3.95Type this formula in cell B2, then press Enter. To apply it to the rest of the column, click on the small square at the bottom right corner of B2 and drag it down, or simply double click that square to auto fill the formula for every row that has data in column A.
For a different state, replace 3.95 with the correct factor from the table above. For example, for Madhya Pradesh the formula would be =A2*8.97, and for Gujarat it would be =A2*6.18.
Building a State Wise Auto Converter With a Lookup Formula
If your spreadsheet has land records from multiple states, typing a different multiplication for every row is inefficient and prone to mistakes. A better approach is to create a small reference table and let Excel pick the correct factor automatically using a lookup formula.
Step 1: Create a Reference Table
In an unused area of the sheet, for example columns D and E, list every state along with its conversion factor, exactly as shown in the table earlier in this article. Column D should contain the state name, and column E should contain the matching factor.
Step 2: Set Up Your Data Columns
In your main data area, column A can hold the state name for each row, column B can hold the hectare value, and column C will hold the converted Bigha value once the formula is applied.
Step 3: Use VLOOKUP or XLOOKUP
Using VLOOKUP (works in every Excel version)
=B2*VLOOKUP(A2,$D$2:$E$7,2,FALSE)Using XLOOKUP (Excel 365 and newer)
=B2*XLOOKUP(A2,$D$2:$D$7,$E$2:$E$7)Both formulas do the same job. They look at the state name in column A, find the matching row in the reference table, pull out the correct factor, and multiply it with the hectare value in column B. Once this formula is in row 2, you can drag it down for the entire dataset, and every row will automatically use the correct factor for its own state, without you having to remember or manually type any conversion number again.
Converting Bigha Back to Hectare in Excel
Sometimes the reverse calculation is needed, for example when a Bigha value from a local land deed needs to be entered into a hectare based government form. The formula simply divides instead of multiplying.
Formula (for Uttar Pradesh, Rajasthan, or Bihar)
=A2/3.95With the lookup based version, it would be =B2/VLOOKUP(A2,$D$2:$E$7,2,FALSE), which again pulls the correct state wise factor automatically.
Rounding the Result to a Fixed Number of Decimal Places
Raw multiplication in Excel can sometimes produce long decimal values, such as 15.803254. For most practical purposes, rounding to two decimal places is enough. This can be done by wrapping the formula inside Excel's ROUND function.
This formula multiplies the hectare value by the conversion factor and then rounds the result to two decimal places, giving a clean and readable number such as 15.80 instead of a long decimal.
Common Conversion Examples in Excel
The table below shows a few sample hectare values and their converted Bigha equivalent using the Uttar Pradesh and Rajasthan factor of 3.95, exactly as the formula =A2*3.95 would calculate them.
| Hectare Value (Column A) | Excel Formula | Result in Bigha |
|---|---|---|
| 0.5 | =A2*3.95 | 1.98 |
| 1 | =A2*3.95 | 3.95 |
| 2.5 | =A2*3.95 | 9.88 |
| 4 | =A2*3.95 | 15.80 |
| 10 | =A2*3.95 | 39.50 |
What Is the Formula for Hectares in Excel?
This question usually comes up in two different contexts, and both are worth covering here. If the question is about converting hectares into another unit such as Bigha, the formula is a simple multiplication using the correct state wise factor, for example =A2*3.95. If the question is instead about calculating a hectare value from the length and width of a plot, since 1 hectare equals 10,000 square meters, the formula uses the plot's dimensions in meters.
Calculating Hectares From Length and Width
=(Length_in_meters*Width_in_meters)/10000For example, if a plot is 200 meters long and 150 meters wide, entering =(200*150)/10000 in a cell returns 3, meaning the plot covers 3 hectares.
Common Mistakes to Avoid
- Mixing up state factors: Applying the Madhya Pradesh factor of 8.97 to Uttar Pradesh land, or vice versa, will produce a completely wrong result even though the formula syntax is correct.
- Forgetting the dollar signs in lookup ranges: When dragging a VLOOKUP or XLOOKUP formula down a column, the reference table range must be locked using dollar signs, such as $D$2:$E$7, otherwise the range shifts incorrectly for each row.
- Entering text instead of numbers: If hectare values are typed with extra spaces or as text rather than numbers, Excel will return an error instead of a calculated result. Formatting the input column as Number usually fixes this.
- Ignoring Pucca and Kaccha Bigha differences: In states like Rajasthan, both Pucca and Kaccha Bigha exist. Always confirm which type the source record is using before finalizing the factor in your spreadsheet.
A Few Things to Keep in Mind
The conversion factors used in this article are based on the standard and most commonly accepted Bigha definitions for each state. Since Bigha is a non standardized traditional unit, some districts may follow a slightly different local value. For any legal, registry, or loan related paperwork, it is best to confirm the exact figure with your local tehsil, patwari, or the relevant state Bhulekh portal before relying on the spreadsheet output for official purposes.
Frequently Asked Questions
What is the formula for hectares in Excel?
If you are converting hectares into Bigha, the formula is a multiplication using the correct state factor, such as =A2*3.95 for Uttar Pradesh and Rajasthan. If you are calculating hectares from a plot's length and width in meters, the formula is =(Length*Width)/10000, since 1 hectare equals 10,000 square meters.
Can Excel automatically convert Hectare to Bigha for different states in the same sheet?
Yes. By creating a small reference table with each state and its conversion factor, and using VLOOKUP or XLOOKUP alongside a multiplication, Excel can automatically apply the correct factor for every row based on the state listed in that row.
Why does the same Hectare value give a different Bigha result in different rows?
This happens when different rows belong to different states, since each state has its own Bigha size and therefore its own conversion factor. The same hectare value will always produce a different Bigha result depending on which state's factor is used.
How do I round the converted Bigha value in Excel?
Wrap the conversion formula inside the ROUND function, for example =ROUND(A2*3.95,2), which rounds the final result to two decimal places.
Conclusion
Converting Hectare into Bigha inside Excel comes down to two things, using the correct state wise conversion factor and setting up a formula that can be dragged down or automated with a lookup function. A simple multiplication works well for a single state, while a VLOOKUP or XLOOKUP based setup is far more efficient for a sheet containing land records from multiple states. Once this is set up correctly, converting hundreds of entries takes only a few seconds instead of manual calculation for every single row.
.webp)
