Abstract: A large number of data of water production, water level drop and gas production in the process of coalbed methane （ＣＢＭ） production are usually stored in Excel form. Based on these data, in order to guide the later stage production of CBM, it is very important to predict gas production and water drawdown. In theory, gas and water drawdown in the Excel table can be calculated by Theis formula, but the calculation process is complicated, the production of CBM is a water production process with variable discharge. By the VBA in Excel, the secondary development for original test data processing table of Theis formula is carried out. In the calculation process, three Excel functions are added to calculate the water drawdown, the parameter m and the accumulated gas production, and the three functions are programmed by VBA in Excel. By clicking the test results button, we can calculate the production water drawdown and gas, it is found that the curves are basically consistent and meet the requirements.