Discover the Bilty Format in Excel for Human Resources

Bilty format in excel for Human Resources

welcome to learn it training the exercise files for today's course are located in the video description below don't forget to like And subscribe so for module 2 we'll take a look at table tools we'll take a look at conditionally formatting our data we'll take a look at the subtotal option we'll Implement data validation and we'll spend some time taking a look at pivot table tools so please go ahead and open up the module 2 exercise file and come right back looking to support our Channel and get a great deal become a member today to unlock ad free videos that's right your favorite courses without a single ad interested in a specific video purchase one of our adree courses individually looking for even more gain access to exams certificates and exclusive content at learnit anytime.com more information can be found in the video description below [Music] when you're working in Excel you're more likely working from a list or a table here is a basic list and what I want to do is convert this list into a table once I convert this list into a table I'll have some more capabilities to sort and filter efficiently I can utilize slicers and we'll also take a look at the total row now I'll go ahead and click on the Home tab and over in the Home tab in the Styles command group going to go ahead and click on format as a table once we format this as a table we'll utilize some of these tools I'll go ahead and choose a table style I'll go for this light style here a little dialogue box appears asking me just to confirm the range for my data here I'll go ahead and press okay here is my table I can see that I have headers here for oh dropdowns rather or filters for all of my headers this allows me to do things like I can sort that column A to Z or Z to A I can even sort by color if I want to once I start using conditional formatting I can search for a specific value or part of a value I can select a distinct value down here as well and I even have some other logical filters that I can utilize Well for now I just want want to go ahead and make sure that my table here is sorted by the employeed number column so I'll go ahead and click on this drop down and I can either sort from smallest to largest or largest to smallest and there you go so now we're sorted by employee ID if I wanted to I can go ahead and sort the name as well so I can go ahead and click on sort A to Z now I'm sorted by first name in this case because we only have the first name in here so that is sorting but let's take a look at some filters maybe I want to go ahead and use filters sorting is a great way to kind of just organize your data but filters builds upon the Sorting capability to give us more opportunities to focus on the data set that we want to so maybe I want to just focus on a particular State I'll click on the drop down here let's say I just want to focus on California I can either type CA in the search bar and we can see it it's only going to return all those records where the state is California or I can just go ahead and choose California from here either method works well I'll go ahead and press okay and we can see it's filtered our data when we run our filter it tells us right away how many records were found so right now it's telling us that 10 of 51 records were found where the state is California so that's a pretty nice filter I'll go ahead and clear that filter so I'll click on the filter icon here and I want to clear the filter from the state this will put my table back to normal here and let's take a look at another type of filter what about the filter for the higher date so I'll go ahead and click on higher date here and maybe I want to learn how many hires did we have let's say in 2023 I could just select 2023 here but I'll use a date filter and I want to search for a value between two dates so I'll say between January 1st 2023 and December 31st 2023 I could also click on the date picker here if I wanted to I'll go ahead and press okay and here are my four highers from that particular year so pretty good we can see how the filters just moves all of the other data out of the way for us temporarily so that we can focus on the data that we are looking for I'll go ahead and clear the filter here from the higher date but in fact I can go ahead and run multiple filters if I want to so based on the higher date has to be 2023 and the state is going to be Louisiana and there we go so we can run multiple filters on our data set right now by higher date and by state as well I'll go ahead and clear these two filters put my data back to normal and there we go so practice filtering the different columns on your data set and come right back another nice way to filter our data is by using slicers now I'm going to go ahead and click on my table here you'll notice when I click on my table I have what's called a table design Tab and this table design tab is where we can find some tools to analyze our data like slicers and the total row so we'll take a look at the slicer tool here I'll go a and click on my table design and over on the far left here is my insert slicer command I'll go ahead and insert click on that button here and here are the different slicers that I have so I have one slicer for each of my headers and so I can create some buttons that will allow me to filter by these different headers I'll go ahead and insert a slicer for the state this is a good option because we only have a few Selections in here if I were to go ahead and insert a slice let's say for the name I'll go ahead and press okay and we can see that we have our slicer for our state states are in here and even though it's quite a bit I can still kind of see them on one screen here and I can go ahead and just click on a button now if I click on the Arizona button on my state slicer here notice how it filters my table for me so it's only showing the State of Arizona if I click on California here are my 10 records from the state of California so it's a pretty pretty nice tool it offers a oneclick solution instead of clicking several places to filter what you're looking for now I can combine slicers with other slicers if I want to now you can see the name slicer is not really a good candidate because there's so many individual or unique or distinct values but let's see if I can make this work for me here I'll go ahead and clear my filter from the state slicer and let's see if I can maybe have three columns for the names here let's see if I can get this to fit on one screen once I click on my slicer I'll head on up to my slicer tab here and I'll go ahead and change the columns from one to two and let's see what that looks like so it's a little better still have to kind of scroll down here a little bit maybe I'll make it three columns so it's still a little bit to handle and I could make this as big as I needed to be but if I add another column in here it's going to become very very difficult but this works if I just want to go ahead and click on a particular employee there you go it just shows the actual record here's Hazel rich here's Janice Thorton there's Edwin Johnson notice how the other slicer automatically updates because Edwin Johnson is from New Mexico I'll go ahead and clear the filter here and I'll get rid of this fil this slicer just press the delete key and now this slicer works a lot better I'll go ahead and make this one two columns now I can make it a little smaller I can put it right here I'll change the color I'm good to go if I click on New Mexico we have one Texas there are six records so those are the slicers we'll visit these more um a little when we are constructing our pivot tables one of my favorite table tools is the table row now the table row can be found on the design Tab and right in the table style options we'll see the table the total row we can see it's not turned on by default I want to go ahead and turn that on because the next logical thing for us to do with our data set is to start summarizing our columns we have a couple of numerical columns in here that we can perform some math on whether it's summarizing grabbing the average so forth and so on so I'll go ahead and turn on the total row and it adds a row to the end of my data set here and we can see it's already summarizing the time off balance for us we we have 535 if I click on the drop down here so each cell on the total row has a drop- down where we can insert the sum function average function the count the max the Min and if necessary more functions as well we'll focus on the basic functions here the max the Min the average and the sum so if I wanted to calculate the entire gross pay I can click here and I'll click on the sum function here and it summarizes that column for me the total gross pay $467 44.50 what were the average hours worked 30 and what is the average pay rate $305 cents so we're getting some really really nice data in here so that's how the total row works now if I filter the data these numbers will reflect what we are filtering for so if I click on my let's see if I still have my slicer here I do I'll go ahead and click on Oregon so here are my five employees for from the state of Oregon and we can see these numbers have been updated time off balance here is 82 here's our gross pay average hours worked and the average pay rate so pretty pretty cool so that is the total row we can use them to summarize the entire table or based on what we are filtering by I'll go ahead and put that back and we can see our numbers are displaying here as well well what is the max hours worked so the highest amount of hours worked by any employee is 40 let's change that to the men and we have 20 most likely several several examples of 20 here so that is the total row no need to write formulas we have the formulas built into that last row of our table for us let's apply some conditional formatting to this table now I do have some colors in here already conditional formatting is all about color coding different cells to give us a visual so for example if I highlight my entire time off balance column and I want to see some some data bars here let me turn off my total Row first and so if I click on conditional formatting I can have some data bars and this will kind of give me a quick visual to show me which values are standing out from the others I can clearly see on row 20 that ethal Nash has a high time off balance of 18 whereas on row 19 Ernie Chandler has six so those are the data bars that we can use so these are gradient fills on the bottom I have more solid colors but it's the same concept here so conditional formatting is another way to filter your data without moving the data or hiding the data we also have what's called color scales so there are different types of color scales that I can use here's the green yellow red color scale so the green represents the higher values whereas the red represents the lower values so I'll go ahead and apply the green yellow red color scale so there we go so now we can quickly scroll through and we can quickly highlight those green values which represent the higher values and also those red values which represents the lower values let's see if I can filter by color I'll go ah aad and click on the filter icon here and I want to sort by color well I can sort by color let's see if I can sort by that color here not really not really doing a good job let's see if I can filter by color I'll filter for all of the green and there you go so now we can focus on all of our higher time off balances I'll go ahe and use another filter here what about the I'll go for more of this color here so we can filter by color after applying conditional formatting to our data set I'll go ahead and clear that so I'll click on the filter here I'll clear the filter I'll leave that conditional formatting turned on another way that I can conditionally format I'll go ahead and highlight all of my gross pays now this time I want a condition so I'll click on conditional formatting and I have highlight cell rules so I can only I I can highlight all the gross pays that are greater than a certain value less than or between or a specific value I can take a look at the top 10% or the top 10 items I'll go ahead and look I'll click on top 10 items let's see what happens here actually I'll go for top 10% and so conditional formatting is you select the the range you apply your your built-in conditional format in and then you just choose your cell formatting for this one I want this to be a green fill with dark text I'll go ahead and press okay and there you go so it pretty much highlights those top 10% of the gross pace so Brandy Turner Cecilia Carr jacn Cordova and so forth and so on so that is pretty cool well let's try another one here what about the hours we go ahead and highlight all the hours let's take a look at another conditional formatting this time I'll take a look at a highlight cell I'll look for let's say less than so less than 30 so if it's less than 30 give me a green fill with dark text and we can see right here in real time it's highlighting all of those hours where it's less than 30 so I can change this number if I want to maybe 25 five and I'll go ahead and press okay so the conditional formatting is a really nice way to basically filter your data in the form of a visual let's take a look at the subtotal tool now with this particular report I want to generate a report that's based on the length of service The Hours by state So based on the state I want a summary by state we're going to go ahead and add up the length of service and the hours as well so I can do that manually but there is a tool that will pretty much provide this report for me so the first thing I need to do is I need to go ahead and make sure that I sort the column of Interest so my column of Interest here is the state so I'll go ahead and make sure that I sort the state column so that's the first thing that I need to do so I'll click on the state column here and I'll click on the data tab I'll go ahead and sort A to Z so we can see it's now sorted A to Z this goes down to row 52 so now what I can do now that that's sorted I'm still on the data tab here I want to use this subtotal command in the outline command group let's hover over it and see what it says it says quickly calculate rows of related data by inserting subtotals and totals well that's exactly what I want so at the end of California for example it's going to insert a row that's summarizing the data for the length of service and the hours for me once it gets to the end of Iowa here it's going to do the same thing so you can see that's quite a bit of a task I'm going to go ahead and run the subtotal here now the subtotal only works with lists so it does not work with the with the table so I have not converted this list into a table I'll go ahead and click on the sub sub total we can see it automatically selects all the data here so now I just need to go ahead and add the information I'll actually do this backwards so I want length of service and hours by state so I'll make sure to check off length of service and hours and for the state it's that first box it says at each change in this is another way for just saying our column of Interest so at each change in the state column go ahead and give me subtotals for the length of service and the hours as well I'll go ahead and press okay and let's see what happens so we can see that it's giving us a lot of detail here we can see here's a subtotal line for Arizona we only had one in Arizona so here's the total length of service and the hours as well for California we had several so the total length of service is 52 and we have 309 hours in here as well now if you take a look at the top left right underneath the name box here we have three buttons to click on I want to click on number one and number one just gives us a page one of the report the grand total for the length of service and the hours work if I click on page two gives me a another condensed breakdown for each state length of service and the hours as well and then if I still want to look at the overall data I can click on button number three and at each change in the state it's inserting a subtotal Row for us so that is the subtotal command data validation is a really efficient way to limit the type of data that can be placed in cells so we have our employee turnover metrics data sheet here and we're recording the top reasons for turnover but we want to limit the list to better opportunities family relocation career change or personal growth so as you can see we have some other options in here that kind of overlap each other so do job offers is kind of similar to better opportunities or career growth so I'm going to go ahead and delete these values in here and what I'll make sure is that you can only select from one of these options in here so you can only select one of these four options at a high level so to do that I'm going to highlight all of those cells and what I would like to do is have some in cell dropdowns here you can click on the dropdown and you'll be prompted to select one of these four options so and then if you try to enter any other value in there that will not work so step one I'll go ahead and highlight those cells I can always expand this later if I want to but for now just for demonstration purposes I'll use these I'll click on the data Tab and in the data tab we have the data validation has a little check Mark little checklist here if I hover over it tells me that I can pick from a list of rules to limit the type of data that can be entered into a cell I'll click on it here and right now it's allowing any value well I want to allow from a list so I will click on list here notice you can also prevent what can be entered if this is going to be a date you can limit the start date and the end date so only dates between two dates or different operators in here we also have time so start between a start time and an end time here for this one I just want to pick from a list now the source I can either type the the list in here manually or I can use this opportunity to refer to this range of cells right here so I'll just click here and I'll highlight those cells and I'm good to go I'll go ahead and press okay if I wanted to I can change the input message change the error alert if I want to but I'll leave everything as default I'll press okay and there we go now if I come here I can see I have a small drop down here and it forces me to choose from one of these four options so maybe we have this we don't have the all the information in here but we'll just go ahead and populate this let's see if I try to add something in here maybe they're upset tells me you cannot do that you have to enter one of the items from the list here so I'll click on retry and I can type it as long as I type it correctly as long as it matches my list and then I'm good to go maybe the rest of these are personal growth go and do that so there we go so that is data validation just different ways that we can restrict the type of data that can be entered into a cell let's start talking about pivot tables now as you can see I don't have any pivot data in here what we're going to do is import data from another another source so if I click on the data tab over in the left I have the get and transform command group here now what I can do is I can grab data from many different sources from a picture I can grab data from a text file or a CSV file CSV files are very common in the data World typically when data is being exported from one system into the next I can even grab a data set from the web as long as I have a valid URL I can also go ahead and grab data from a table or range in this workbook and if I click on get data here's a combination so I can grab from a PDF file from a folder from a SharePoint folder so there's several different ways for me to be able to add data from here in this example we're going to be go grabbing data from a CSV file so I'll click on from textcsv and here's the CSV file that I want to use looks like Excel but that's a CSV file I'll go ahead and click on import here and because there's a CSV file I prompted with this information here I can kind of take a look at my data I can see all of my headers are being displayed properly so this was actually exported pretty well so I'm good to go the I'll leave everything here as is if things did not look the way they were supposed to I can change the delimiter and here until I get the look and feel that I want I'll go ahead and load the data I could transform the data if I wanted to for example if I wanted to remove some of these columns maybe I don't want to include some of these this is power query so these are kind of Beyond this course here I'll just go ahead and load the data and let's see what this file looks like here and there we go so we can see that the data has been loaded in here now 100 rows of data were loaded and if I hover over this I can just kind of see little brief overview of the data set that I'm working with and the columns as well right I'll go ahead and close this side pane so that's how we can import data from another either data set or another data source now that we've imported data from a CSV file we can see that it automatically converts this into a table for us I'll go a and click on the table design here and one of the tools that's available is to summarize our table with a pivot table so by clicking on the table design tab all the way to the left I have summarized with a pivot table I can also click on the insert tab from here I can go ahead and insert a pivot table I also have what's called recommended pivot tables let's go ahead and choose a recommended pivot table this way we can see the pivot table and we can Dive Right In I'll go ahead and click here and this little dialogue box pops up here and we can see this is giving us the sum of age by the department that may not be useful here's the sum of experience by department or by position rather so we have a lot of experience for the sales representative roll and performance score base B on the academic credentials so we can kind of see if there's a correlation between the score and the academic credentials as well I'll scroll down so quite a few in here I'll go ahead and just choose one to find the data that I'm looking for I'll keep it simple I'll go ahead and select the first one here sum of age by apartment and I'll go ahead and press okay and so that inserts the pivot table for me now the first thing that we can take a look at this does not really make too much sense we have the sum of age and we have our different departments in here but what we can do instead of summarizing the age let's see if we can get the average in here so what I'm going to do right here in the values area box I'll click on the drop down here and I will click on value field settings now notice it's summarizing the data but what if I want to maybe grab the average or the count see what else I can do in here right so maybe I just want the average so I'll click on average here and then I'll go ahead and press okay and let's see what I get so I'll go ahead and press okay and that makes more sense so the average age for our employees in the finance department is 38 where sales is a little older so the average age is around 46 now I have a lot of decimals in here so I'll go ahead and fix those as well I can do that by going back to the value field settings I can click here I can also get to the value field settings just by wri clicking on a number and here is the value field settings here as well so I'll go ahead and right click I'll click on value field settings we were here before well I want to change the number format so I'll go ahead and click on number format here this is a number I just want maybe one decimal space I'll go ahead and press okay press okay again and there we go so now we have created made a small report made a minor change and now we can go ahead and get some details in terms of which department tends to be younger and which one has more experience as well for this example I'll go ahead and insert a pivot table from scratch here so I'll click on my table I'll click on the insert Tab and I'll click on pivot table now I do want to place the pivot table on a new worksheet so I'll just go ahead and press okay it's already selected the data in my HR data table so I'll go ahead and press okay and so we have a blank canvas to work with and over here on the right we have our pivot table Fields so basically I can just go ahead and have Excel build a pivot table for me so for example if I wanted some some reports on the salary I can click on salary notice how it places the salary in this in the values box and it's summarizing the salary for me well I do want to change this to the accounting format so I'll right click here go back to Value field settings again and down here for the number format I'll click here and I'll change that to the accounting format I'll get I'll keep the decimals places there I'll go ahead and press okay also go ahead and press okay again so we can see that some of the salary is 8, 39,6 74 so the pivot table is just making the calculations for us so I have a sum of salary but uh what else do I want to add to this report well I can get these the salary by position or by Department I want the salary by department so I'll click on Department here and notice how it's just providing all the calculations for us so it's a really nice way to just analyze your data well we can see um from this data set here we can actually sort the data like this so I'll click on the data Tab and I'll sort C to a and I can see that the IT department has slightly higher salaries than the sales department as well now another thing that we can do we can also so instead of displaying the sum of the salary we can also display the percentage of the running total so we need to go back to the value field settings so I'll right click here click on value field settings we already saw how we can change the sum to the average I'll go ahead and click on show value as and right here we don't have a current calculation in here if I click on this drop down maybe I want to show the percentage of the grand total so I'll go ahead and click here and I'll go ahead and press okay and there we go so now this is showing us the percentage so the bigger you know piece of the pi here so it is taking up 23.2 n% of the pi whereas HR is 14.52% so that's how we can change the value to show as a percentage of the grand total I'm going to reset my table here so I'll just go ahead and uncheck the department and the salary I'll go ahead and build out a new table I want to focus on the performance score so we can see it's summarizing the performance score for us also want to go ahead and analyze the score based on the department in here as well and I may also go for let's say bu education level so there we go so we're summarizing the Performance Based on departments and education level as well I could add the department into the columns making the table a little easier to read here just like we did before I'll go ahead and change this to the average and and got a lot of decimals in here so I'll go ahead and fix that as well this is a number with just one decimal space and there we go so if we were to take a look at this data now we can kind of see that the overall performance score the average is 78.6 and we can see for our marketing team had the highest score at 81.7 now what I can do is I can make use of what's called a drill down report and this is a pretty nice tool so if I wanted to click on this 81.7 here if I double click on this this is going to create a new table that I can use for analysis so I'll go ahead and double click here and as you can see it inserts a new worksheet for me and here's my table and this table is all about marketing so that that is a drill down report here are all of the employees from the marketing department and we can see their individual scores in here so with the pivot table we can double click on any one of those and we'll get some really really nice reports that we can work with so feel free to go ahead and double click on any number on your pivot table and go ahead and explore the drill down report and come right back another useful tool is a report filter page now in this case I'm showing the average performance by department and by education level but what about position as well what if I wanted to create several different pivot tables based on the actual position and this way I can get the same detail but based on the actual position if I were to add the position drag that to the filters box here I can now filter by position so if I click on the drop down I just want to see all this data based on our sales representatives so I'll click okay and notice how the numbers have been updated to just focus on that particular position now if I want to take a look at another position I can simply come in here and change it to the developer I'll go ahead and press okay and here we go but what if I wanted to create several pivot tables for all five of our different positions here so I'll have one pivot table on one worksheet and that way I can manage these pivot tables independently well that is called a report filter page if I click here on my pivot table I'll click on pivot table analyze and right over here to the far left we have to click on the options and here is the show report filter pages so first we have to select a place on our pivot table click on pivot table analyze and then make our way over to the options drop down and here's the show report filter Pages let's see let's see what it says doesn't show us any detail right here that's okay well because I have the position in the filters this will allow me to generate several different pivot tables each pivot table will be based on a specific position now if I did not have this in here goe and clear this filter first put everything back to normal now if I remove the position here notice that it's grayed out so the first step in creating those report filter Pages you must have a field in the filters box let's create some separate pivot tables based on position I'll go ahead and click on the drop down here I'll click on show report filter Pages we can see because we have position in here we can create the the report Pages I'll press okay here is our first pivot table accountants only here's the next one developers only HR managers marketing lead and sales so these pivot tables will behave independently of the others so it's a nice way instead of copying your pivot table and trying to paste it we can create those report filter Pages now I can go ahead and build out the if I want to I could even go ahead and just have the department and the education level all in one column now that we've analyzed our data taking a look at different tools of the pivot table let's go ahead and create a visual so if I click on my pivot table I'll go back to pivot table analyze here is our pivot chart I'll go ahead and insert a pivot chart and let's see what it looks like so I'll click on pivot chart here and here is a clustered column chart lots of data in there but pretty nice to read here's a line chart here's a pie chart well I'll go for that very first one which is the column chart here and I'll go ahead and press okay so here is my chart and this is interactive just like the data charts from module one if I just highlight or just move my mouse over the different data bars here it's giving me the actual values so this is giving me a nice visual in terms of the average scores based on in this case based on the education level so for our masters we can see we have higher performance overall and for HR and marketing which is pretty cool now my pivot table is to the data that's on my Pivot chart so if I make any changes here to my pivot table my Pivot chart should update automatically for example I'll click on my pivot table maybe I don't want to show the department maybe just by education level go ahead and remove the department here and there we go we can see the pivot table is showing the average performance score based on degree level and here we can see our our pivot chart has been updated as well I do have filters here for our pivot chart as well we can filter by position and we can also filter by education level so if I just go for Masters and PhD we can see my Pivot chart is updated and my pivot table is updated as well to reflect those filters so we can filter either from the pivot table or from the pivot chart I'll go ahead and reset that filter so I'll clear the filters from my Pivot chart and there we go feel free to click on the little chart Styles here and choose a different style and there you go so that wraps up our module 2 just as a recap we're able to take a look at some table tools we're able to utilize conditional formatting for some color visuals we're able to take a look at the subtotals tool and we're able to take a look at data validation as well and finally we spend some time taking a look at various pivot table tools well congrats and completing the course and I look forward to seeing all of you in a future learned course thanks for watching to earn certificates and watch our courses without ads check out learnit anytime.com [Music] audio jle [Music]