How to Calculate Gratuity in Excel

This article explains how to calculate gratuity in excel using formulas. You will be able to use this excel formula to calculate gratuity easily. below we also provide an excel sheet so you can easily download that and can use it any time.

How to calculate gratuity in excel


How to use this excel

  1. Open Excel: Launch Microsoft Excel on your computer.
  2. Set Up Your Spreadsheet:
    • In cell A1, type “Basic Salary”.
    • In cell B1, type “Number of Years”.
    • In cell C1, type “Gratuity Amount”.
    • In cell A2, enter the employee’s basic salary.
    • In cell B2, enter the number of years of service.
  3. Calculate Gratuity:
    • In cell C2, enter the formula: =(A2 * B2 * 15) / 26. This formula calculates the gratuity amount based on the provided basic salary and years of service.
  4. Press Enter: After entering the formula, press the Enter key.
  5. View the Gratuity Amount: Cell C2 will now display the calculated gratuity amount based on the provided information.
  6. Copy the Formula: If you want to calculate gratuity for multiple employees, you can copy the formula in cell C2 and paste it down for other rows, adjusting the values in columns A and B accordingly.

Please note that the formula assumes a factor of 15/26, which is a common practice. However, gratuity laws and regulations may vary by location, so it’s important to ensure that you’re using the correct formula for your region’s rules.

Gratuity is an additional amount of money that employees receive for their work. It is typically calculated as a percentage of the employee’s gross earnings.

The purpose of gratuity is to compensate the employee for the value of the work he or she has done. In order to calculate the amount of gratuity that should be paid to your employees, you must first calculate the base pay for each employee.

This calculation is made by adding the base pay for each employee to his or her hourly rate. Then, you can add a certain amount of gratuity to each employee’s total.

The amount of gratuity that is added to each employee’s total will depend on the type of job the employee does and the company’s culture.

Gratuity is the amount of money that a person is paid as an incentive for doing a job or providing a service. It’s usually included as a line item in an employee’s paycheck, but it can also be given to a customer as a tip.

The amount of gratuity varies based on the type of service or job being done. In some cases, the amount of gratuity is calculated by multiplying the hourly rate of the employee times the number of hours worked.

In other cases, the gratuity is added as a separate line item on the bill. In both cases, the gratuity is added to the final bill after the regular service charge and the tax.

How to calculate gratuity formula in excel

The formula to calculate Gratuity is as follows:

Last Drawn Salary X Total No. of Years of Service X 15/26

UAE gratuity calculator in excel format

The UAE gratuity calculator is an essential tool for employers and employees alike. Gratuity is a mandatory end-of-service benefit paid to employees who have completed their service with the employer.

Step 1: Determine the Basic Salary To calculate the gratuity amount, you need to know the employee’s basic salary. The basic salary is the amount before any deductions, such as taxes or contributions to pension plans.

Step 2: Determine the Length of Service The length of service is the total number of years and months the employee has worked for the employer.

Step 3: Determine the Gratuity Calculation Formula The UAE labour law stipulates that the gratuity is based on the last basic salary received by the employee. The formula for calculating gratuity is:

Gratuity = (Basic Salary ÷ 30) × 30 days for each additional year of service

Step 4: Enter Data into Excel Spreadsheet Open an Excel Spreadsheet and create a table with the following columns: Employee Name, Basic Salary, Date of Joining, Date of Leaving, Total Service Period, and Gratuity Amount.

Enter the data for each employee into the corresponding cells. Make sure to enter the dates of joining and leaving in the correct format (dd/mm/yyyy).

Step 5: Use Formulas to Calculate Gratuity In the Gratuity Amount column, use the formula explained in step 3 to calculate the gratuity amount for each employee. You can copy and paste the formula to calculate the gratuity for all employees automatically.

Step 6: Save and Print Once you have calculated the gratuity amount for all employees, save the Excel spreadsheet and print it out for your records.


In this article we check about how to calculate gratuity in excel is an easy and efficient way to ensure accurate and timely payments to employees. By following the step-by-step guide outlined above, you can easily calculate the gratuity amount for each employee in your organization.