Create a Billing Statement in Excel for Human Resources Effortlessly

Billing statement in excel for Human Resources

hi there thank you for joining me today i am skills from skillsondemand.online in this tutorial i will be taking you through how to build a dynamic and interactive excel dashboard that you're seeing now in this example we will learn how to create a dynamic excel hr dashboard using the details from a demo it company we will be analyzing details such as employees by department department expense designation employee type employee status total expense number of males and females in the company average sick leave and average employee rating all data connected with each other to give you an impressive and dynamic excel dashboard in few simple steps before we start let's download the workbook file attached under this section this is the data we will be using for this dashboard there are over 400 rows of employee data our data is in tabular format with date range from december 2017 to january 2014. the data is classified by full name gender higher date status higher type department category qualification salary bonus overtime sickly rating metrics like performance behavior innovation colleague feedback average rating and promotion eligible employee details by department all data is formatted in an excel table a structured data is the first step in building a dashboard when the table data is refreshed all the changes that affect the formulas pivot tables pivot charts are updated automatically to build an efficient and dynamic dashboard we will use excel functions such as formulas pivot tables spark lines slices and pivot charts we have created this course using excel 2016 however you can use any excel version such as excel 2010 2013 or 2016. so what are you waiting for let's get started i believe you have the example workbook open and ready our workbook has sheets such as readme data pivot 10 charts and dashboard let's format the data to an excel table use the keyboard shortcut key control plus a which select all the data then ctrl d which will create the table excel will give you a problem with details of the data range if your data has header click on my table as a header and press ok now the table is ready let's give our table a name as data now let's create our first pivot table that shows number of employees by gender or number of mailbase number of females in a company select the data click on insert tab click on pivot table select the range where you want your pivot table to appear press ok our pivot field list is activated let's drag and drop gender to row field again for the values drag and drop gender to value field our pivot table is ready now let's copy paste the pivot table as values format the table we will use this data for our dynamic labels now replicate the value using formula equal to cell value next to avoid errors let's add the formula if error and a value to display in case of an error let's add one more pivot table to show the number of employees each year so copy paste the previous pivot table clear the existing selection next drag and drop ears to row field higher date to value field we will use this pivot table for our slicers later on now let's create our pivot chart for this pivot table we will be using donut chart to display the data select the data click on insert tab click on pivot chart under all chart select donut chart remove the fill buttons legends and titles let's add colors to the section for male let's select green and for female let's select blue next let's add data labels and add shapes to the data labels make the text as bold and format the data labels to change the values to percentage now let's add two icons to our charts click on insert tab click on icons select the icon male and female and press okay change the color of the icon to match the chart now let's move this chart to the dashboard sheet resize and adjust the chart place the icons inside the donut chart next click on the chart and select no fill option to remove background color now let's add dynamic shape labels so click on the insert tab click on shapes select a rectangle replicate the rectangle three times for the first rectangle let's add the text as female for the second rectangle let's add the text as male for the third rectangle let's add the text as total employees for all three select the shape color as no fill remove shape outline change text color as black adjust the font size and make it bold let's add three more shapes click on shapes select a rectangle replicate the rectangle three times for the first shape insert the formulas equal to cell value that has the female pivot table count for the second shape insert the formula as equal to cell value that has the male pivot table count for the third shape insert the formulas equal to cell value that has total employees pivot table count for all three select the shape color as no fill remove shape outline change text color as black adjust the font size and make it bold now to make the chart stand out let's add one more shape click on insert tab click on shapes select a rectangle shape adjust the size to match our chart area add an effect to the shape now right click and send the shape backwards change the background color of the chart to no fill simple as that now let's create our second pivot table that shows employees by department let's copy paste the previous pivot table clear the existing selection drag and drop department to row field gender to value field change the pivot table design to outline our pivot table is ready we will use a tree map chart to replicate these values cannot directly use a tree map chart with a pivot table as we get an error message like this we need to replicate the pivot table data into a table create a traymap chart and then map the chart with the pivot table values simple as that so let's copy paste the pivot table as values format the table with the same design next for the department add the formulas equal to department cell value replicate to the below rows for count of gender let's do a vlookup with the department name and pivot table replicate to the below rows now let's create our chart select the data click on insert tab under all chart select remap chart press ok remove the legend add title as employees by department next let's format data label click on category name and values now let's move the chart to the dashboard sheet to make the chart stand out let's add one more shape click on insert tab click on shape select a rectangle adjust the size to match the chart add an effect to the shape now right click and send the shape backwards change the background color of the chart to no fill simple isn't it now let's create our third pivot table that shows higher types is the employees hired as an ftc or fde which means are the employees hired on a contract basis or a permanent basis so let's copy paste the previous pivot table clear the existing selection drag and drop higher type to row field and higher type to value field change the report layout to outline form next let's create our pivot chart for this pivot table let's use a bar chart select the data click on insert tab from all chart select bar chart remove plot area legends axis values and field buttons let's add a title as employed time add a data label and add shapes to the data label customize the bar color based on your preference oh now let's move the chart to the dashboard sheet to make the chart stand out let's add one more shape click on insert tab select rectangle shape adjust the size to match our chart area add an effect to the shape change the background color now right click and send the shape backwards change the background color of the chart to no fill it's so simple do now let's create our fourth pivot table that shows employee status this pivot table will show details of active employees employees on medical leave and employees on notice period let's copy paste the previous pivot table clear the existing selection drag and drop status to row field status to value field next let's create our pivot chart for this keyboard table let's use a bar chart select the data click on insert tab from all chart select bar chart remove plot area legends access value and field buttons let's add a title as employee status add a data label and add shapes to the data label customize the bar color based on your preference now let's move the chart to the dashboard sheet to make the chart stand out let's add one more shape click on insert tab under shapes select rectangle shape adjust the size to match our chart area add an effect to the shape change the background color now right click and send the shape backwards change the background color of the chart to no fill simple isn't it now let's create our fifth pivot table that shows employee designation this pivot table will show different designation in the company let's say team member lower management middle management top management so let's copy paste the previous pivot table clear the existing selection drag and drop category to row field category again to value field in the value field the value should reflect as count now our pivot table is ready let's create a pivot chart for this pivot table let's use a bar chart select the data click on insert tab from all charts select bar chart remove plot area legends axis value and field buttons let's add a title as employee designation customize the barcode based on your preference add a data label and add shapes to the data label now let's move the chart to the dashboard sheet next to make the chart stand out let's add one more shape click on insert tab click on shape select a rectangle adjust the size to match our chart area add an effect to the shape change the shape color now send the shape backwards change the background color of the chart to no fill now our chart is ready simple isn't it now let's create our sixth pivot table that shows employee expense this pivot table will show what is the amount of monitor employee salary employee bonus and employee overtime let's copy paste the previous pivot table clear the existing selection drag and drop salary bonus and overtime to value field we are not using other pivot fields this pivot table will give you an overall picture of total expense for workforce change the report layout to outline form next let's create a pure chart for this pivot table let's use a bar chart select the data click on insert tab from all chat select bar chart remove plot area legends access value and fill buttons let's add title as employee expense add a data label and add shapes to the data label customize the bar color based on your preference now let's move the chart to dashboard shade to make the chart stand out let's add one more shape click on insert tab select the rectangle shape adjust the size to match our chart area add an effect to the shape now right click and send the shape backwards change the background color of the chart to no fill simple isn't it next let's create our 7th pivot table that shows expense by departments like previous this pivot table will show what is the amount of money spent on employee salary employee bonus and employee overtime by departments let's copy paste the previous pivot table drag and drop department to row field next we want only the total value for each department so copy paste the department name as values in the next column add a header as total expense now insert a formula as equal to sum of salary bonus and overtime now we have the total expense drag and drop the formula to replicate to other rows next let's create a pivot chart for this pivot table let's use a bar chart select the data click on insert tab under all chart select bar chart remove plot area legends axis value and field buttons let's add a title as expense by departments add a data label and add shapes to the data label customize the bar color based on your preference now let's move the chart to the dashboard sheet next to make the chart stand out let's add one more shape click on insert tab click on shape select rectangle adjust the size to match our chart area add an effect to the shape now right click and send the shape backwards change the background color of the chart to no fill our pivot chart is ready simple isn't it now let's create our eighth pivot table that shows average sick leave let's copy paste the previous pivot table our pivot field list is activated clear the existing selection drag and drop department row field seek lift value field ensure value in the value field is reflecting a sum let's format our pivot table we will be using a speedometer chart or a modified combo chart for this pivot table let's add a couple of fields below total names average sick leaves maximum sick leaves prorated rating multiplied by hundred now for total names let's add a formula as equal to value of total names which we will get from our first pivot table average sickly value is a sum of all sick leaves divided by total names maximum sick leave is let's say your company allows a maximum sick leave of 10. prorated rating value is average sick leave divided by maximum sick leave last column is for multiplying the value with 100 to normalize it now let's format the table now for speedometer let's make a table and add headers like speedometer average sick leaves start initial middle end max total for average sick leave let's add a formula to multiply prorated rating into hundred in this chart we are going to subdivide the values into four buckets total value of the chart will be 200 let's keep the start value as zero initial as 15 middle as 35 and as 50 and max value is 100 so that total becomes 200. now for the pointer let's add a header like point to value sickly let's add headers like value pointer size end for value let's add a formula as equal to average sick leave for pointer size let's add the value as 2 for end let's add a formula as equal to total minus sum of value and pointer size now let's format the table now let's create our chart we'll be using two charts here one is a pie chart and second is a donut chart for the donut chart select the value from start to max click on the insert tab under all chart select donut chart delete the chart title and legends change the shape outline to no outline now that our donut chart is ready let's format it click inside the chart right click format data series under series option select the angle of the first slice as 270 degrees let's change the chart bar color as green blue and red for the below part let's make the color as no fill now let's make the chart background color as no fill chart outline as no outline now for the pointer click on the chart right click select the data add a series name series value which is our pointer value press ok now we have our second chart ready now let's change the second chart type to pie chart so right click change chart type under all chart select pie chart now let's change the chart angle to 270 degrees as before change the shape outline to no outline now let's make the section background color as no fill except for the pointer let's added data labels to your pointer click only on the pointer right click and add data labels change the font size to 12. let's add custom values to our data label click only on the data label in the formula bar reference the data label value with the values our chart is ready let's move the chart to the dashboard sheet let's add a title to your chart click on the chart now design tab gets activated under design tab click on add chart element add chart title select above and title as average sick leaves let's add a shape to show dynamic labels so click on insert tab under shape select a rectangle adjust the rectangle to match the chart area now click on the shape in the formula bar add a formula as equal to average sick leave select the shape outline as no fill and shape background as no fill now let's add three more shapes to reference the short color for the first shape let's add the color as green for the second shape let's add the color as blue for third shape let's add the color as red let's add three more shapes so click on insert tab click on shape add a rectangle adjust the rectangle to match the chart area for the first shape let's add the text as good for the second let's add the text as ok for third let's insert the text as bad select the shape outline as no outline select the shape background as no fill change the text color to black and make it bold now to make the chart stand out let's add one more shape click on insert tab from shapes select a rectangle resize and adjust the chart to match the short area add an effect to the shape change the shape background color right click and send the shape backwards now our speedometer chart that shows average sickly value is ready simple isn't it now let's create our ninth pivot table that shows average employee rating let's copy paste the previous pivot table our pivot field list is activated clear the existing selection next drag and drop department row field average rating to value field ensure value in the value field is reflecting as sum like before we'll be using a speedometer chart or a modified combo chart for this pivot table let's copy paste the fields that we used earlier let's change couple of fields change average sick leave to average employee rating max eclipse to max employer rating average employee rating value is a sum of all employee rating divided by total names max employee rating value is 5 star prorated rating value is average rating divided by maximum rating last column is for multiplying the value with 100 to normalize it now for speedometer for average rating let's add a formula to multiply prorated value into hundred since we copy pasted the previous table all the formulas are reflecting as it is now let's create our chart we will be using two charts here one is a pie chart and second is a donut chart for the donut chart select the value from start to max click on insert tab under all chart select donut chart delete the chart title and legends change the chart outline to no outline now that our donut chart is ready let's format it click inside the chart right click format data series under series option select the angle of the first slice to 270 degrees let's change the chart bar color as red blue and green for the below part let's make the color as no fill let's make the chart outline as no fill chat background as no fill now for the pointer right click select the data add a series name add a series value now our combo chart is ready now let's change the chart type to pie chart so right click change the chart type to pie chart like before now let's change the chart angle to 270 degrees now let's make the section background color as no fill except for the pointer let's add data label to our pointer so click only on the pointer right click add data labels let's add custom data label to a pointer so click only on the data label in the formula bar insert the formulas equal to average employee rating change the font size to 12 and font color to black increase the font size let's add a title as average employee rating so click on the chart click on design tab click on add chart element add chart title and select about chart insert the title as average employee rating simple isn't it now let's move the chart to the dashboard sheet now let's add a shape to show dynamic labels copy paste the previous shape in the formula bar add a formula as equal to average employee rating our speedometer chart that shows average employee rating is ready it's so simple now let's learn how to make our dashboard interactive and dynamic in nature oh let's learn how to make the charts come alive using slicers slicers are customized visual filters that help you filter tables pivot tables pivot charts and cube functions in a click slicers are available in excel 2013 and above to add a slicer click on any pivot table or chart click on insert tab click on slicers here you will see all your headers listed with a checkbox select the header you prefer to select as a base item to filter your data in this example let's select status gender promotion eligible qualification years higher type press okay now we have 6 slicers ready you can add design and style elements like change of color to your slicer okay in some cases we see items with no data in such cases to remove it click on the slicer click on the options select slicer settings here click on the check box hide items with no data and press ok now it's time to connect the slicers with the charts click on the slicer click on options click on report connections here we see all our pivot tables and pivot chart names select all and press ok let's repeat the steps for all the slices huh next let's lock all the slicers in place to avoid unnecessary resize and movements now let's check if our dashboard is working click on the slicers let's select ers 2016. now our dashboard display data pertaining to 2016. if we select ers 2017 data pertaining to 2017 will reflect in our dashboard based on our selection the entire dashboard gets updated it's that powerful to unfilter the selection click on clear filter option on top of your dashboard wow our dashboard is complete and it's alive simple isn't it hey congrats you have mastered a new skill today i believe i was able to help you learn an essential skill in a simple way now you need to think how you can utilize what you have learnt and apply the idea in your workplace any doubts any queries feel free to ask we are here to help you hope you enjoyed this class see you again in the next tutorial