Instructions for Creating a Salary Spreadsheet in Excel and How to Use Common Functions

Excel is a powerful and flexible tool, suitable for calculating and managing salaries. In this article, ADSMO will show you how to create a salary spreadsheet in Excel and use common functions to make calculations easy and accurate.

In the article “Excel Salary Payment Table Template According to Circular 200 and 133” ADSMO also mentioned that businesses are allowed to design their own salary payment tables that are suitable for their specific characteristics and convenience. However, regardless of how they are built and calculated, this salary table template also needs to be based on a number of factors:

• Timesheet
• Labor contract
• Regional minimum wage (updated according to the latest regulations)
• Taxable and non-taxable personal income
• Amounts included in premiums and not subject to social insurance
• Insurance deduction rate for business expenses and employee salaries

After ensuring the above factors, we will learn about:

Basic items when applying salary calculation on Excel

Usually the main components of a payroll will include:

1. Main salary
2. Allowances (including Allowances Subject to Social Insurance Contributions According to the 2023 Labor Law and allowances not subject to social insurance)
3. Total income
4. Working days
5. Actual salary
6. Social Insurance Contribution Salary
7. Insurance deduction rate

8. Personal Income Tax 

9. Advance

10. Real field

>>> Download the Salary Calculation Payment Table Template Excel according to Circulars 200 and 133 Here!

How to create a salary calculation table on Excel and some common functions

Step 1: Prepare data

First, you need to prepare data such as employee names, employee number, job title, hours worked, base salary, and any other allowances or benefits. Make sure the data is in the appropriate columns.

Step 2: Create a new spreadsheet in Excel

Open Microsoft Excel and create a new spreadsheet. Name the data columns for ease of use.

Step 3: Calculate salary for employees

Is the agreed salary in the Labor Contract and allowances, salary levels, and bonuses according to the regulations of the Enterprise.

Step 4: Calculate deductions

In the payroll, there may be deductions such as Social insurance, health insurance, personal income tax, etc. to calculate taxable income. To calculate the total taxable income for each employee, you can use the formula to subtract deductions from their total income.

Step 5: Calculate total salary

Step 6: Calculate net salary

To calculate net salary, you need to determine other deductions such as deductions and advances (if any).

Step 7: Customize the format

To make your payroll easier to read and more professional, you can customize the amount format, add your signature, company logo, and use colors to highlight important information.

Step 8: Save and use

Finally, save the payroll spreadsheet and use it in managing monthly salaries for your employees you.

Some common functions in salary calculation tables in Excel

Above are basic instructions on how to create a payroll spreadsheet in Excel and use some common functions. Depending on the specific needs and requirements of your company or organization, you can customize the spreadsheet and use other functions to suit your payroll process. Or more simply, use the software HRM Plus+ of ADSMO with modules such as human resource information management, timekeeping management, payroll calculation, recruitment management, approval of leave and business trips, diverse customization according to the requirements and characteristics of each business. Contact Hotline: 0356.105.388  to receive advice from our team of experts.

>>>See more: How to calculate salary for employees