Salary Calculation Formulas in Excel – Detailed and Complete Guide

Salary calculation formulas in Excel are essential tools to help businesses automate the salary calculation process, save time and minimize errors. With the ability to process large and flexible data, Excel has become the top choice for human resource managers in setting up an effective salary system. From basic to advanced functions, Excel provides a full range of tools to build automatic, accurate salary calculation tables that are suitable for the characteristics of each business. 1C Vietnam will guide you on how to apply salary calculation formulas in Excel most effectively for businesses.

1. Overview of salary calculation formulas in Excel

Excel is a popular and effective tool for payroll management. Before going into the details of the formulas, let's find out why Excel is popular and what factors need to be prepared.

1.1. Why should you use Excel to calculate salaries?

Using Excel for payroll calculation offers many advantages over traditional manual calculation methods. Here are the main reasons why Excel is the preferred tool for payroll management:

• Significant time savings: With pre-set Excel payroll formulas, calculations for hundreds of employees can be done in just minutes instead of hours manually.
• Automate processes: Excel allows you to automate repetitive calculation steps, from calculating wages and benefits to deducting taxes and insurance.
• Minimize errors: Properly set up formulas will ensure high accuracy, limiting human errors caused during manual calculations.
• Easy to customize: Excel allows flexible spreadsheet adjustments to suit the specifics of each business, from small to large scale.
• Data storage and retrieval capabilities: Easily store, search, and compare salary data across months, quarters, or years.

According to a Microsoft survey in 2023, more than 78% of small and medium-sized enterprises in Vietnam used Excel as the main tool for payroll management before switching to specialized software.

1.2. Factors to prepare before calculating salary with Excel

To build an effective payroll system in Excel, the following factors need to be carefully prepared:

Detailed timesheet:

• Information on actual working days of each employee
• Overtime and night work hours (if any)
• Vacation, sick leave, unpaid leave
• Late arrival and early departure times (if applicable)

Relevant legal regulations:

• Regional minimum wage (currently from 4,420,000 VND to 6,240,000 VND depending on region)
• Regulations on personal income tax (PIT)
• Social insurance (SI), health insurance (HI), unemployment insurance (UI) contribution rates
• Regulations on allowances and subsidies according to labor law

List of employees and basic information:

• Employee code, full name, job position
• Basic salary, salary coefficient
• Fixed allowance (if any)
• Dependent information (for family deduction purposes)
• Bank account information (if salary is paid by bank transfer)

Company salary regulations:

• How to calculate salary (by time, product, revenue...)
• Reward and punishment policy
• Regulations on overtime
• Other benefits

Fully preparing the above elements will help the process of building salary calculation formulas in Excel become more convenient and accurate.

2. Popular salary calculation formulas in Excel

Excel offers many powerful functions that can help automate your payroll process. Here are some common functions and how to use them in your payroll.

2.1. Logical functions (IF, AND, OR)

Logical functions are an important foundation in payroll calculation, allowing to set conditions and classify data according to various criteria.

IF function: This is the most basic function, allowing to perform conditional tests and return corresponding results.

Syntax: IF(logical_test, value_if_true, value_if_false)

Applications in payroll:

Tax Rate Classification Based on Income

Calculating allowances based on job position

Determine performance-based bonuses

Example: Calculating position allowance based on position

=IF(C2="Head of Department", 2000000, IF(C2="Deputy Head of Department", 1500000, 0))

AND and OR functions: Combine multiple conditions in one formula.

AND syntax: AND(logical1, logical2, ...)

OR syntax: OR(logical1, logical2, ...)

Applications in payroll:

Combine with IF to create complex conditions

Determine conditions for special allowances

Example: Calculate toxic allowance for employees working in a workshop with a toxic environment and with more than 2 years of seniority

=IF(AND(D2="Chemical workshop", E2>2), 1000000, 0)

Illustration table of IF formula combined with AND:

2.2. Search and query functions (VLOOKUP, INDEX, MATCH)

Search and query functions help to look up information from data tables, which is useful when needing to get information from many different sources.

VLOOKUP function: Searches for a value in the first column of a table and returns the value in another column on the same row.

Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Applications in payroll:

Look up basic salary from salary scale table

Search employee information from list

Determine tax rate from personal income tax table

Example: Look up basic salary based on employee code

=VLOOKUP(A2, BangLuong, 3, FALSE)

INDEX and MATCH functions: Combining these two functions allows for more flexible searching than VLOOKUP.

INDEX syntax: INDEX(array, row_num, [column_num])

MATCH syntax: MATCH(lookup_value, lookup_array, [match_type])

Applications in payroll:

Search data by multiple criteria

Look up information from a table with multiple columns and rows

Example: Find salary based on position and seniority

=INDEX(BangLuong, MATCH(C2&D2, ViTri&ThamNien, 0), 5)

VLOOKUP formula illustration table:

2.3. Sum functions (SUM, SUMIF, SUMIFS)

Aggregate functions help summarize data based on various conditions, which is useful for calculating total income or deductions.

SUM function: Calculates the sum of values.

Syntax: SUM(number1, [number2], ...)

Applications in payroll:

Calculate total income from multiple sources

Calculate total deductions

Example: Calculate total income

=SUM(D2:G2)

SUMIF and SUMIFS functions: Sum values that satisfy one or more conditions.

SUMIF syntax: SUMIF(range, criteria, [sum_range])

SUMIFS syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Applications in payroll:

Calculate total salary by department

Calculate total overtime hours according to conditions

Calculate total personnel costs by position

Example: Calculate total overtime hours on weekdays

=SUMIF(C2:C32, "Weekday", D2:D32)

Example: Calculate the total salary of employees with more than 3 years of seniority and in the sales department.

=SUMIFS(F2:F100, D2:D100, ">3", B2:B100, "Business")

SUMIF formula illustration table:

2.4. Date data processing functions (DATE, NETWORKDAYS)

Date data processing functions help accurately calculate working days, holidays, and other time-related information.

DATE function: Creates a date value from year, month, day.

Syntax: DATE(year, month, day)

Applications in payroll:

Determine the start and end dates of the pay period

Calculate employee age or seniority

Example: Create first and last day of month

First day of the month: =DATE(2025, 3, 1)

Last day of the month: =DATE(2025, 3, 31)

NETWORKDAYS function: Calculates the number of working days between two dates, excluding weekends and holidays.

Syntax: NETWORKDAYS(start_date, end_date, [holidays])

Applications in payroll:

Calculate the actual number of working days in a month

Calculate valid leave days

Example: Calculate the number of working days in March 2025

=NETWORKDAYS(DATE(2025,3,1), DATE(2025,3,31), DateLe)

Where NgayLe is a range of cells containing a list of holidays during the year.

NETWORKDAYS formula illustration table:

List of holidays (cells H2:H10):

• 08/03/2025 (International Women's Day)
• April 30, 2025 (Southern Liberation Day)
• 01/05/2025 (International Labor Day)

3. Instructions for creating automatic payroll in Excel

Creating an automated payroll in Excel requires proper structure design and application of appropriate payroll formulas. Here is a detailed guide on how to design and apply formulas to a payroll.

3.1. Design payroll structure

An effective payroll needs to be clearly structured, easy to follow, and easy to update. Here are some guidelines for designing a standard payroll structure:

Step 1: Create a basic information sheet

Create a separate sheet containing basic employee information

Required columns: Employee code, Full name, Date of birth, Department, Position, Date of employment, Basic salary, Account number, Number of dependents

Step 2: Create timesheet sheet

Design a table to track each employee's working days

Required columns: Employee code, Full name, days of the month (1-31), Total working days, Overtime hours (weekdays, weekends, holidays)

Step 3: Create main payroll sheet

Design a payroll spreadsheet with complete information

Required columns:

Basic information: Employee code, Full name, Department, Position

Working day information: Standard working day, Actual working day, Vacation day, Unpaid leave day

Income information: Basic salary, Daily salary, Allowance, Overtime pay, Bonus, Total income

Deduction information: Social Insurance (8%), Health Insurance (1.5%), Unemployment Insurance (1%), Personal Income Tax, Advance, Total deduction

Actual Information: Actual, Notes

Step 4: Create auxiliary sheets

Parameter sheet: Contains fixed parameters such as regional minimum wage, social insurance rate, personal income tax table

Report Sheet: Summary and analysis of salary data

Suggestions for arranging scientific data:

Use employee code as primary key to link between sheets

Name data ranges for easy reference in formulas

Use conditional formatting to highlight important information

Lock cells containing formulas to prevent accidental modifications

3.2. Applying the formula to the payroll

Once you have designed your payroll structure, the next step is to apply payroll formulas in Excel to automate the calculation process.

Step 1: Calculate actual working days using the COUNTIF function

To calculate actual working days based on timesheet:

=COUNTIF(BangChamCong!C2:AG2, "X")

In there:

BangChamCong!C2:AG2 is a range of cells containing employee timekeeping information.

"X" is the symbol that marks the working day.

Or use NETWORKDAYS to calculate the number of working days in a period:

=NETWORKDAYS(DayBatDau, DayKetThuc, DanhSachDayLe) - SongayNghiKhongLuong

Step 2: Calculate salary by working day

Salary calculation formula based on actual working days:

=IF(TodayCongChuan>=TodayCongChuan, Salary, Salary/TodayCongChuan*TodayCongThucTe)

Step 3: Calculate overtime pay

Formula for calculating overtime pay on weekdays:

=ROUND((LuongCoBan/DayCongChuan/8)*1.5*SoGioLamThemDayThuong, 0)

Formula for calculating overtime pay on holidays:

=ROUND((LuongCoBan/DayCongChuan/8)*2*SoGioLamThemDayNghi, 0)

Formula for calculating overtime pay on holidays:

=ROUND((LuongCoBan/DayCongChuan/8)*3*SoGioLamThemngayLe, 0)

Step 4: Calculate insurance premiums

Formula for calculating social insurance (8%):

=MIN(Salary*8%, 20*Salary*8%)

Formula for calculating health insurance (1.5%):

=MIN(Salary*1.5%, 20*Salary*1.5%)

Formula for calculating BHTN (1%):

=MIN(Salary of the Company*1%, 20*Salary of the Company*1%)

Step 5: Calculate personal income tax

Formula for calculating taxable income:

=TotalImport - TotalInsurance - 11000000 - Number ofPatients*4400000

Formula for calculating personal income tax using VLOOKUP function and tax table:

=IF(ThuNhapChiuThue<=0, 0, VLOOKUP(ThuNhapChiuThue, BangThue, 2, 1) + (ThuNhapChiuThue - VLOOKUP(ThuNhapChiuThue, BangThue, 3, 1))*VLOOKUP(ThuNhapChiuThue, BangThue, 4, 1))

Step 6: Calculate the net salary

Formula for calculating net salary:

=TotalImports - TotalExports

4. Tips to optimize payroll process using Excel

To improve efficiency and accuracy when using payroll formulas in Excel, here are some useful tips to optimize your workflow.

4.1. Use Conditional Formatting to quickly check for errors

Conditional Formatting is a powerful tool for quickly detecting unusual values or errors in payroll spreadsheets.

Tip 1: Highlight outliers

• Color cells with negative or excessively high values
• Mark employees whose actual workdays are less than 50% of the standard workdays
• Highlight cases with unusually high personal income tax

How to do:

• Select the data area to apply
• Go to Home > Conditional Formatting > New Rule
• Select the rule type (for example, "Format only cells that contain")
• Set the condition (eg: Cell Value < 0)
• Select format (background color, text color)
• Click OK to apply

Tip 2: Create a visual progress bar

• Shows the percentage of completed workdays compared to standard workdays
• Create progress bars for employee KPI achievement levels
• Visualize the ratio of deductions to total income

How to do:

• Select the data area to apply
• Go to Home > Conditional Formatting > Data Bars
• Choose the right progress bar color and style
• Customize parameters like min, max values if needed

Tip 3: Use Icon Sets to Categorize Performance

• Classify employees by level of contribution
• Overtime Rate Review
• Warning about excessive absences

How to do:

• Select the data range to apply
• Go to Home > Conditional Formatting > Icon Sets
• Choose the appropriate icon set (traffic lights, arrows, ...)
• Customize the value threshold for each symbol

4.2. Create visual reports with PivotTable

PivotTable is a powerful tool that helps analyze and summarize salary data in a flexible and intuitive way.

Tip 1: Create a summary report of salary expenses by department

• Select payroll data
• Go to Insert > PivotTable
• Drag the "Department" field to the Rows area
• Drag the fields that need to be summed (Basic Salary, Allowance, Total Income) into the Values area
• Customize number formats and report layout

Tip 2: Analyze salary trends over time

• Create PivotTable from Multi-Month Salary Data
• Drag the "Month" field to the Columns area
• Drag the "Department" or "Location" field to the Rows area
• Drag the "Total Income" field to the Values area
• Add PivotChart to visualize trends

Tip 3: Compare income structure between employee groups

• Create PivotTable from salary data
• Drag the classification field (Position, Rank) to the Rows area
• Drag the income components (Basic Salary, Allowances, Bonuses) into the Values area
• Select to display data as % of total
• Add a stacked column or pie chart PivotChart for visualization

4.3. Optimize formulas with advanced functions

In addition to basic Excel salary calculation formulas, using advanced functions will help optimize the salary calculation process and increase the flexibility of the spreadsheet.

Tip 1: Use the SUMPRODUCT function instead of multiple SUMIF functions

The SUMPRODUCT function allows you to calculate the sum of products of ranges of numbers, which is very useful when you need to calculate sums with complex conditions.

Example: Calculate total overtime pay based on day type and number of hours

=SUMPRODUCT((TypeDay="Weekday")*(Number of Hours)*(DonPrice))

Tip 2: Use the IFERROR function to handle errors

The IFERROR function helps handle error cases in formulas, avoiding displaying unwanted error messages.

Example: Calculate salary increase rate compared to last month

=IFERROR((This Month's Salary-Previous Month's Salary)/Previous Month's Salary, "N/A")

Tip 3: Use the INDIRECT function for dynamic references

The INDIRECT function allows you to create dynamic references to cells or ranges of cells, which is useful when you need to get data from multiple sheets.

Example: Get data from sheet corresponding to current month

=INDIRECT("'"&B1&"'!C5")

Where B1 contains the sheet name (for example: "March Salary")

Tip 4: Use Data Validation to control input data

Data Validation helps control input data, avoiding errors due to incorrect data entry.

How to do:

Select the data area to control

Go to Data > Data Validation

Set up validation conditions (e.g. limit number of working days from 0-31)

Add error message when entering wrong information

Tip 5: Use Name Manager to manage data areas

Name Manager helps you name data ranges, making formulas easier to read and maintain.

How to do:

Go to Formulas > Name Manager

Click New to create a new name

Enter a name (for example, Salary) and reference a cell or range of data.

Use this name in formulas instead of cell references

For example: Instead of =A1*8%, you can write =Basic Salary*Social Insurance Benefits

Salary calculation formulas in Excel are powerful tools that help businesses automate and optimize the salary management process. From basic functions such as IF, SUM to advanced functions such as VLOOKUP, INDEX-MATCH, Excel provides a full range of tools to build an accurate, flexible and effective salary calculation system. Applying the right formulas combined with a reasonable spreadsheet design not only saves time but also minimizes errors, ensuring transparency in human resource management. 1C Vietnam always accompanies businesses in optimizing the salary management process through the application of modern technology.