Essential Techniques for Data Analysis
So, you’ve made it through year-end, and you finally have a moment to breathe and gather your thoughts. Your first thought may be, what should I do next?! This is the perfect opportunity to sharpen your excel skills. You needn’t rely on IT to analyze your data for you.
With these simple techniques, you can analyze your own data! Becoming the go-to person in your department for excel can help boost your employability and income as well. Below are some formulas and tips you can use to become that go-to person.
Know what data to include: When running a report out of Workday, it’s best to include more data than you think you need.
Weed out the data you don’t want than to run multiple reports to put together.
Always keep a copy of your “raw data” in case you weed out too much and need it back.
It’s easier to bring information back in
Using filters can seem daunting now, I know they were when I started using them! The steps below are designed to help make the use of filters easy and accessible to everyone. They are a great tool to use in everyday tasks and will help with efficiency!
Using Filters: Once you have your data:
One of the first things that you can do is filter your columns
Most reports out of Workday will have header columns. You can highlight this entire column and click on the word “data” in your toolbar.
This will insert arrows on the right of each column in your header row. The arrows are a drop-down menu in which you can use different techniques to filter your data, such as sorting from A to Z, filtering anything above or below a certain number, or choosing certain items you wish to see, like everyone from a certain department.
You can also filter more than just one column.
For example, you may wish to see everyone in the IT department that makes over $100K. You would first filter to everyone in the IT department and from there, you can filter to everyone over $100K.
Pivot Tables: Most people are afraid of the concept of pivot tables. I know I was when I first used one! But, in all actuality, they are quite simple to use once you get the hang of them, and trust me, you want to learn how to use them because they are quite handy!
You need to have a header row
You will want to highlight all the data you want included in your pivot table
Choose “insert” on your tool bar and click on “PivotTable”
A box will pop up, which already populates your range of data. You may want this in a new tab or at the bottom of the page on which you are working
Click “ok”
The new tab will populate with a blank pivot table on the left and a chart of pivot table fields on the right.
You will see all your header row items as selections on the field chart. You can then choose what fields you wish to see as a collection.
There are four places you can put your information in a pivot table: filters, columns, rows, and values. The two most common fields are rows and values.
You can drag any of your column headers to the fields in the field chart.
For example, let’s say you want to know how many employees there are in each department. You would want to move “department” to the row section and then employee name to the values section. This will give you the count of employees in each department. If you want to know the total hours worked on each team, you can move the team name to the row section and hours to the values section. You will want to make sure that your field setting is at values instead of count.
VLOOKUP: Sometimes Workday doesn’t have everything you want to look at in one report. You may have to run two separate reports and pull the information together to get everything you want. In this case, the vlookup formula is your best friend!
Make sure that the information you are using to do your lookup is unique. The most common item to use here would be an employee number. Some employees could have the same name, but each employee would have their own unique employee number
The first field that it looks for is the unique identifier you want to use to look up your information. Here is where you want to use the field that has the employee number
Once you click on the cell having the employee number, you will see it populate in the field box
Tell excel where to look to find the data to pull. You will want to go to that source and highlight all the columns needed.
The first column needs to be the column that has the employee number.
highlight from this column over to where the information you want to pull is located.
You will want to count from the first column where the employee number is over to the column where the data you want to pull is and type in that number.
The last item you will want to put in the box is the word false. This tells excel that you want to find an exact match.
If it is unable to find an exact match it will return #N/A as it’s result. If you get this in all your cells that you are looking up, there is something wrong with the formula you used. Usually, it is that you did not start with the correct column (the employee number), or you miscounted the number of columns to give you your return value.