Step-By-Step Build-Up And Visualisation Of A Payroll Using Excel

In this project, I will demonstrate how to use Excel to prepare a payroll for a company. A payroll is a list of employees and their salaries, deductions, and benefits. It is an important document for accounting and tax purposes.

To create the payroll, I will use some common Excel functions such as VLOOKUP, VALUE, and TRIM. These functions help me to look up data from other sheets, convert text to numbers, and remove extra spaces. I will also use some formatting and validation tools to make the payroll more readable and accurate.

After preparing the payroll, I will use the pivot table feature to visualize the data and analyze the salary distribution, average pay, and other statistics. A pivot table is a powerful tool that allows me to summarize and manipulate large amounts of data in a dynamic way.

By completing this project, I will learn how to use Excel to perform various tasks related to payroll management and data analysis. I will also improve my skills in using functions, formatting, validation, and pivot tables.

Cleaning the data and preparing it for analyses

Here is the link to the data Payroll (kaggle.com)

The data is in XLSX format, so double-clicking it will open it in the Excel app. Here is a snapshot of what the data looks like:

The first sheet contains details of workers and hours worked, and the second sheet is details of the pay rates.

Lets get stated with checking and cleaning the data. Looking closely there is an extra space in sheet 1 between the names in cell B2. So I use the TRIM function to remove these spaces. In cell B11 input =TRIM(B2) then click enter. drag down to automatically fill down the cells.

Next step is to populate the newly formed cells to analyse the data by using the Value function to make sure the values are in number strings. Then add extra columns for total hours and pay rates. The result:

The total hours can be calculated by adding the hours worked per day using the SUM function =SUM(C11:H11).

The hourly rates are located in sheet2, here the VLOOKUP function will come in very handy to bring it into Sheet1 and fill up cell I11:I15. Since sheet2 doesn’t have a list of the names of the employees, we will use the ID column(A2:A6) to lookup the values. here is the syntax =VLOOKUP(A2,Sheet2!A2:D6,4,FALSE). Then drag and fill. Then use the SUMPRODUCT function =SUMPRODUCT(I11,J11). drag and fill.

Analysis of the Payroll

Now that the data is clean and includes pay rate and total pay information, we can create a pivot table.

Highlight the data in the new cells, click on insert, and click on pivot table and Click on New Sheet, then click the Create button.

On the side of the new sheet, the Pivot table editor will display. The pivot table you are creating will contain each employee’s name, pay rate, and total pay. Follow these steps to create the pivot table:

1. Click the Add button for Rows. Select Names.

2. Click the Add button for Values. Select Pay Rate.

3. Click the Add button for Values again. Select Total Pay.

Next, convert the cells in the Sum of Total Pay column to currency.

4. Select the cells in the column Sum of Total Pay.

5. Click on the $ symbol on the toolbar.

The result should display like this:

To visualise the data, remove the Pay Rate and highlight the pivot table, click on insert, click on recommended charts and choose the clustered column chart.

Here we can see that Patel Anika earned the highest and Fisher Wolfgang earned the least. We can further explore who worked the longest hours by selecting names into the rows and total hours into values. Highlight data and create a clustered column chart. The result should display like this:

There are many other visuals and analysis that can be carried on with this data set. feel free to explore and have fun using using to answer questions.

Conclusion

In this project, I have demonstrated how to use Excel to prepare a payroll for a company. I have used some common Excel functions such as VLOOKUP, VALUE, and TRIM to look up data from other sheets, convert text to numbers, and remove extra spaces. I have also used some formatting and validation tools to make the payroll more readable and accurate. After preparing the payroll, I used the pivot table feature to visualize the data and analyze the salary distribution, average pay, and other statistics. I have learned how to use Excel to perform various tasks related to payroll management and data analysis. I have also improved my skills in using functions, formatting, validation, and pivot tables.

This project has shown the power and versatility of Excel as a tool for working with data. Excel can help to organize, manipulate, and present data clearly and effectively. Excel can also help to discover new insights and patterns from the data that can inform my decision-making and problem-solving. Excel is a valuable skill that can help in academic and professional endeavours. I hope you have enjoyed this project and learned something new from it. Thank you for your attention and feedback.