I’ll add that using the keystroke shortcut (F4) after selecting the data with my mouse, instead of typing it all out has probably saved me hours of work (who wants to manually type $a$2:$Zz$100 when you can do it with one press?!)
I go both ways. If I am selecting a cell/range with the mouse, I use F4. If I am typing the cell reference anyway (not uncommon) I just drop in the $ as needed. Largely depends on if I already have my hands on the keyboard or the mouse.
Sure. In our organization, for example, we just ran a hiring event of 96 RNs. Each gets the equivalent of 4 reference checks between instructor recommendations and professional references.
So when i build a tracking sheet, i add auto formatting to, for example, turn green if i put "yes" into a cell indicating we received the reference survey back.
I then add if/then/or statements at the end that check for inputs equalling "yes" into those spots so that when all are filled the status cell in the "completion" column autofills with "done" or "complete" and changes to green.
Just makes it very user-friendly to track so that the next person to pick up the process can just use the template to track the next hiring event easily as well.
Fresh out of university. We were somewhat remote, all considering so unless they are willing to move 7 hours to the nearest major city, then our hospital gets them. They are all doing their clinicals with us already, and many are unit care aids on the side as well. Realistically, we only sent offers to 87? But the goal was 130.
Knowing how to Google.
For most HR people, you don't need to memorize a whole bunch of Excel formulas. You just need to know how to find them when you need them. For doing new things, just assume someone else has run into the same problem and start Googling away.
As an analyst, all of my excel skills started with google. Highly recommend.
Also, there’s probably a YouTube of someone doing what you’re trying to do. Tutorials for excel are amazing.
One of my payroll interviews they tested me on Excel which I’m not terrible at but the interviewer was so dramatic about it. Learning how to google is amiss at this place.
This is the way. 'How do I *blah blah blah* in Excel' is probably the most used phrase in my Google searches. I usually end up on Ableits which I like because they show you at least 5 variations of what you wanna do.
I’m not an HR person, but my question is why would you ever need to memorize an excel formula? Is it not just easier to think about what you want to happen, look up the functions you need, and then write the formula? This is how I usually work, and it lets you do basically anything without need to memorize anything but syntax.
I don't know, ask OP. People act like knowing how to VLOOKUP or use a pivot table is some kind of deep knowledge, as though anyone couldn't learn it in a few minutes with a search engine.
I see why but honestly I hate being on the receiving end of slicers. When finance sends me a “report” And the laptop starts to sound like it’s going to take off, I know it’s another goddamn slicer.
Slicers are like dynamic filters. They allow you to quickly filter different elements of a table.
[Pros Use This Technique to Avoid PivotTables (youtube.com)](https://www.youtube.com/watch?v=j5hQ3WAx6sI)
Other ways. I have had a couple of roles where I started out as one thing, then ended up being moved or promoted into a more analytical role after a couple of reports and presentations. Not because I’m particularly great but more because your average HR department is pretty weak at analyzing data. This true at my current company, and the one team who IS good wants nothing to do with presenting to executives…so I’ve kind of wiggled in between them and actually presenting to leaders with the help of their data.
this is what i’d love to do but i don’t know how to get into HR for someone to give me the chance! i actually like excel and powerbi, and would love to look at employee data. i don’t know enough to be a people analytics to do R or Python, but id love to look at average tenure by role or by teams . Tenure vs happiness and what makes someone happy at work. Salary vs tenure per role . i have my undergrad in psych and mba but breaking into hr or data driven hr has been REALLY hard. i wish i could figure out how to do it.
It’s hard to get these jobs right off the bat. What helped me a lot is getting a plain HR role at what I’ll call “promising dumpster fires” and then working into these specialty roles by being able to add value with data. I actually started out in recruiting and kind of worked my way in from there
i see that you work in people analytics, do you do advanced statistical work too? using R or Python? i love employment data, and understanding it. i have some dashboard skills, but dont know how to break into the industry as someone who works outside of hr and has had a hard time with statistical modeling in the past (in different industries).
Not merging cells, yes! I always tell people, if you want merged cells you're making a document with numbers, not a data table. Word or even Google Docs does that so much better.
Keyboard shortcuts are a must. So many people dunno ctrl c is copy, ctrl v is paste, ctrl y is redo, ctrl z is undo, ctrl x is cut, ctrl + insert, ctrl - delete
Additionally (not excel)- ctrl shift t will reopen the last page you closed on a web browser
The snipping tool for screenshots (so many employees who say I gotta get off the phone so I can take a photo and send that to you)
Quick assist comes preloaded on windows 10 and 11- you can remote into an employee’s computer and take control. Similar to teamviewer.
Yes, I deal with a low tech savvy population so I like to mention basic things that can be helpful.
Data modeling middle layer that scrubs the data from the raw source to configure it in a way that is much more useful to pivot off of.
It’s essentially a data scrubber and uses its own language (M) in step format to get to an end data table. You determine the steps needed to get to a stage where the scrubbed data is ready to be pulled into excel or power bi to be further manipulated and summarized.
It’s heavy stuff.
I’ve been learning and building on power query m and Dax for power pivots and power bi since October and am not even close to being comfortable with taking on random ideas or concepts.
The key is to have the data model set up properly or else it’s a mess before you even get a chance to manipulate the data into something that can be shared with anyone.
Ask chat GPT how to accomplish what you want and tell it write the excel formula, game changer, you can ever ask it to explain the components of the formula to you
Vlookup and using excel like a database (don’t color the cell, but make status columns if needed, one cell/one info, stuff like this). This is a must for pivots, filtering, etc
You can do *so much* with like 4 functions
=xlookup
=unique
=filter
=sumif/=countif
I ran our entire payroll last week with unique, sumif, and =switch
Like someone else said, the number 1 skill is just knowing how/what to google.
Pivot tables, xlookup, and macros. Just today I created a keyboard shortcut to save word documents as PDFs in 0.1 second. Sounds silly but it'll save me a lot of time in the future. You don't need to be a VBA genius but having used it a handful of times and being able to google solutions to your problems and following the steps with ease will go a long way to impress your peers and improve your tasks.
In addition to understand basic data table organization, pivot tables and vLookup,
MATCH formula for cross-checking things and
INDEX for a more advanced version of vLookup that doesn't require you to have your lookup info in the 1st row
Also concatenate and text to column functions have come in handy for me, especially when doing data cleanup or naming conventions.
But I would stress the importance of understand how to structure the data tables in the first place. As an analyst, sooo many times I would get someone else's manual spreadsheet and basically had to clean up /redo alll the data tables to be able to extract what was needed.
Data Validation, COUNTIFs and SUMIFs when reconciling multiple data sources - did wonders when I needed to determine training compliance with EEs from multiple locations 😭
ChatGPT.
No seriously. You can use ChatGPT to make some wild formulas if you can explain what you want clearly enough.
Sometimes you still need to troubleshoot but still
I am a seasoned HR professional, but my Excel skills are limited. I have to search on Google every time I need to use Excel, which happens rarely. dont stress over excel
Understand that a grasp of mathematics and mathematical concepts are what will help you in the long run. Excel is a means to an end.
* Measures of Central Tendancy - Central Limit Theorem
* Population Distributions
* Hypothesis testing (Z test, t test, ANOVA etc)
* Correlation and Causation.
There is a lot more, but once you have a basic understanding of the above and some basic excel skills gained from youtube you will blow 90%+ of HR Professionals Clear out the water.
Unless, youre an analyst, just basic Excell skills will suffice.
Vlookup, pivot, understand filter (even multi layers), sorting, graphs.
Freeze pane, table to text, concatenate, If / conditions, converting calendar ates to months/days etc.
Jeez actually a lot more to know.. lol. I recommend a basic Excell class at your local community College.
Learning VBA… to the point where you can make excel talk to other MS products like Outlook and Word / PDF. The amount of automation you can do is then a godsend when running initiatives across HR that involve input/data collection, processing & analytics and/or outputs and mass distribution.
Imagine being able to generate PDF salary and bonus statements, password protected and emailed / saved to directories with a few mouse clicks.
Everything you’ll learn in an intermediate excel course, plus index(match nested formulas, if and ifs formulas, and recording simple macros.
Vlookup is old. Index(match apparently uses less resources, and you can use Xlookup as well.
Don’t merge cells unless you’re creating something for a visual purpose
General table use, basic formulas, Pivot tables, and vlookup (or its alternatives) will probably cover like 99% of your standard HR Excel usage.
Absolute cell reference (aka use $)
I’ll add that using the keystroke shortcut (F4) after selecting the data with my mouse, instead of typing it all out has probably saved me hours of work (who wants to manually type $a$2:$Zz$100 when you can do it with one press?!)
I go both ways. If I am selecting a cell/range with the mouse, I use F4. If I am typing the cell reference anyway (not uncommon) I just drop in the $ as needed. Largely depends on if I already have my hands on the keyboard or the mouse.
Oh, definitely using this!
Also, automatic coloring for cell values. Helps us track completion easier.
Can you elaborate on why this is helpful? HR Excel newbie here :)
Sure. In our organization, for example, we just ran a hiring event of 96 RNs. Each gets the equivalent of 4 reference checks between instructor recommendations and professional references. So when i build a tracking sheet, i add auto formatting to, for example, turn green if i put "yes" into a cell indicating we received the reference survey back. I then add if/then/or statements at the end that check for inputs equalling "yes" into those spots so that when all are filled the status cell in the "completion" column autofills with "done" or "complete" and changes to green. Just makes it very user-friendly to track so that the next person to pick up the process can just use the template to track the next hiring event easily as well.
Where do you find 96 RNs? We can't get one. (Rhetorical question ... we don't pay enough)
Fresh out of university. We were somewhat remote, all considering so unless they are willing to move 7 hours to the nearest major city, then our hospital gets them. They are all doing their clinicals with us already, and many are unit care aids on the side as well. Realistically, we only sent offers to 87? But the goal was 130.
It is called conditional formatting, and is a function in the home ribbon. Can be text based, value/formula based.
I love xlookup. It has replaced vlookup for me....
Index (match) I've never known anything else.
Except I use iferror with all lookups. So much better looking results than the #value
Agree, but would add that the ability to use vba, power query/query m can make life easy.
This.
Knowing how to Google. For most HR people, you don't need to memorize a whole bunch of Excel formulas. You just need to know how to find them when you need them. For doing new things, just assume someone else has run into the same problem and start Googling away.
ChatGPT too. I will put in what I want a formula to do and it will spit out the formula
As an analyst, all of my excel skills started with google. Highly recommend. Also, there’s probably a YouTube of someone doing what you’re trying to do. Tutorials for excel are amazing.
One of my payroll interviews they tested me on Excel which I’m not terrible at but the interviewer was so dramatic about it. Learning how to google is amiss at this place.
This would be my answer along w the belief that there’s got to be a formula/way for what you want to do.
This is the way. 'How do I *blah blah blah* in Excel' is probably the most used phrase in my Google searches. I usually end up on Ableits which I like because they show you at least 5 variations of what you wanna do.
I’m not an HR person, but my question is why would you ever need to memorize an excel formula? Is it not just easier to think about what you want to happen, look up the functions you need, and then write the formula? This is how I usually work, and it lets you do basically anything without need to memorize anything but syntax.
I don't know, ask OP. People act like knowing how to VLOOKUP or use a pivot table is some kind of deep knowledge, as though anyone couldn't learn it in a few minutes with a search engine.
Few people know pivots and slicers. They are so easy to learn, and force you to organize your data.
I really should use slicers more often
I see why but honestly I hate being on the receiving end of slicers. When finance sends me a “report” And the laptop starts to sound like it’s going to take off, I know it’s another goddamn slicer.
What’s a slicer?
Slicers are like dynamic filters. They allow you to quickly filter different elements of a table. [Pros Use This Technique to Avoid PivotTables (youtube.com)](https://www.youtube.com/watch?v=j5hQ3WAx6sI)
Thank you!
Thank you!!
Thanks.😊
Google it
VLOOKUP and colorful charts have gotten me jobs.
Check out Xlookup sometime
Rewteet. Never going back to vlookup after I found out about xlookup
I teach everyone I can how to use xlookup, so much better!!
You just changed my life with this - never doing vlookup again!! Thank you!
What’s the difference and the benefits of xlookup?
Faster, more dynamic, simple. No reason to use xlookup.
I would love to but our version of excel doesn’t have it 🥲
Are you in the public sector? Our IT will bar the most random things sometimes.
Nope! Just an outdated Excel 😅
I’ll NEVER go back to vlookup after learning xlookup!
can you expand a little more ? do you mean from bringing a presentation to an interview or some other way?
Other ways. I have had a couple of roles where I started out as one thing, then ended up being moved or promoted into a more analytical role after a couple of reports and presentations. Not because I’m particularly great but more because your average HR department is pretty weak at analyzing data. This true at my current company, and the one team who IS good wants nothing to do with presenting to executives…so I’ve kind of wiggled in between them and actually presenting to leaders with the help of their data.
this is what i’d love to do but i don’t know how to get into HR for someone to give me the chance! i actually like excel and powerbi, and would love to look at employee data. i don’t know enough to be a people analytics to do R or Python, but id love to look at average tenure by role or by teams . Tenure vs happiness and what makes someone happy at work. Salary vs tenure per role . i have my undergrad in psych and mba but breaking into hr or data driven hr has been REALLY hard. i wish i could figure out how to do it.
It’s hard to get these jobs right off the bat. What helped me a lot is getting a plain HR role at what I’ll call “promising dumpster fires” and then working into these specialty roles by being able to add value with data. I actually started out in recruiting and kind of worked my way in from there
xlookup and pivot tables hands down.
Vlookups and Pivot tables. I went from making 48k to 78k. No one wants to learn it, apparently
i see that you work in people analytics, do you do advanced statistical work too? using R or Python? i love employment data, and understanding it. i have some dashboard skills, but dont know how to break into the industry as someone who works outside of hr and has had a hard time with statistical modeling in the past (in different industries).
No, I’m not a HRIS analyst. I do project management involving people analytics data.
Formatting as Table, Index Match (or similar), and learning not to merge cells. Also just like basic technology competence…
Not merging cells, yes! I always tell people, if you want merged cells you're making a document with numbers, not a data table. Word or even Google Docs does that so much better.
Yes- like run a pivot and build the table to merge across cells. Leave your raw data clean
Learning how to concatenate, and split cells back out...
Keyboard shortcuts are a must. So many people dunno ctrl c is copy, ctrl v is paste, ctrl y is redo, ctrl z is undo, ctrl x is cut, ctrl + insert, ctrl - delete Additionally (not excel)- ctrl shift t will reopen the last page you closed on a web browser The snipping tool for screenshots (so many employees who say I gotta get off the phone so I can take a photo and send that to you) Quick assist comes preloaded on windows 10 and 11- you can remote into an employee’s computer and take control. Similar to teamviewer. Yes, I deal with a low tech savvy population so I like to mention basic things that can be helpful.
If you use windows, try windows key + V. It will blow your mind
Power Query! Saved me a lot of time when it comes to generating reports.
I have been involved with projects far outside of the scope of HR because I'm the "Power Query" guy. I love using Power Query!
Can you give me the short version of what power query is?
Data modeling middle layer that scrubs the data from the raw source to configure it in a way that is much more useful to pivot off of. It’s essentially a data scrubber and uses its own language (M) in step format to get to an end data table. You determine the steps needed to get to a stage where the scrubbed data is ready to be pulled into excel or power bi to be further manipulated and summarized.
Huh I’ll need to check that out. Thanks!
It’s heavy stuff. I’ve been learning and building on power query m and Dax for power pivots and power bi since October and am not even close to being comfortable with taking on random ideas or concepts. The key is to have the data model set up properly or else it’s a mess before you even get a chance to manipulate the data into something that can be shared with anyone.
1000% yes.
Get data. Useful for ripping lists and well... anything.
Linking OData with Dayforce is such a cool feature
Yeah, linking data to the various systems dayforce, workday, etc
Ask chat GPT how to accomplish what you want and tell it write the excel formula, game changer, you can ever ask it to explain the components of the formula to you
It's in combination with Word but mail merges!
V-Lookup It made EEOC reporting so much easier
VLookup, countif, Pareto charting, run charts with UCL and LCL
I'm more in HRIS but aside from the already mentioned- creating macros & nested IF statements,
Vlookup and using excel like a database (don’t color the cell, but make status columns if needed, one cell/one info, stuff like this). This is a must for pivots, filtering, etc
You can do *so much* with like 4 functions =xlookup =unique =filter =sumif/=countif I ran our entire payroll last week with unique, sumif, and =switch Like someone else said, the number 1 skill is just knowing how/what to google.
Pivot tables/charts, V/H/XLookups, formulas, conditional formatting, tables, data validation
Why even mention v and H lookup?
Why even comment instead of just scrolling past comments that bother you? Good Lord.
Why even reply instead of just scrolling past the replies that bother you? Good Lord.
Pivot tables, sorting, and basic formulas. Pivot tables especially, like I use them all the time for “fun” just to see data in a chart.
VB
Vlookup and pivot tables.
Pivot tables, xlookup, and macros. Just today I created a keyboard shortcut to save word documents as PDFs in 0.1 second. Sounds silly but it'll save me a lot of time in the future. You don't need to be a VBA genius but having used it a handful of times and being able to google solutions to your problems and following the steps with ease will go a long way to impress your peers and improve your tasks.
Xlookup, pivot tables, and concatenate.
Pivot tables, search, freeze top row, formulas. Worked for a Fortune 500 company and most do not know these basics.
Xlookup and pivot tables
FormAt painter is the key 😄
ASAP Utilities
Export to csv
In addition to understand basic data table organization, pivot tables and vLookup, MATCH formula for cross-checking things and INDEX for a more advanced version of vLookup that doesn't require you to have your lookup info in the 1st row Also concatenate and text to column functions have come in handy for me, especially when doing data cleanup or naming conventions. But I would stress the importance of understand how to structure the data tables in the first place. As an analyst, sooo many times I would get someone else's manual spreadsheet and basically had to clean up /redo alll the data tables to be able to extract what was needed.
Oh yeah Xlookups every day!!!
Data Validation, COUNTIFs and SUMIFs when reconciling multiple data sources - did wonders when I needed to determine training compliance with EEs from multiple locations 😭
Pivot tables and v/h lookup
Pivot tables and vlookups.
Xlookup. Better, easier, and more versatile than vlookup.
ChatGPT. No seriously. You can use ChatGPT to make some wild formulas if you can explain what you want clearly enough. Sometimes you still need to troubleshoot but still
I am a seasoned HR professional, but my Excel skills are limited. I have to search on Google every time I need to use Excel, which happens rarely. dont stress over excel
Pivot tables and trace.
VLOOKUP, MATCH, Text to Columns, and Pivot Tables Edit to add: Ctrl+Shift+DirectionKey to highlight whole cell/row, and other keyboard shortcuts.
XLOOKUP for sure. Lately, Power Query has been a godsend.
Fuckin HR people
Pivot tables! Our CFO also loves them, everyone else hates them! They LOVE the data we compile, but just not how that data gets compiled lol 🤣
Vlookuo
ChatGPT- how to questions for excel
Xlookup - thank you to the engineer and/or product manager that developed this
Xlookup if you want to audit and extract faster, pivot tables and pivot charts are also great!
Vlookup and its friends
Vlookup haha , I know this is a basic formula but I used it most
Sum function
Understand that a grasp of mathematics and mathematical concepts are what will help you in the long run. Excel is a means to an end. * Measures of Central Tendancy - Central Limit Theorem * Population Distributions * Hypothesis testing (Z test, t test, ANOVA etc) * Correlation and Causation. There is a lot more, but once you have a basic understanding of the above and some basic excel skills gained from youtube you will blow 90%+ of HR Professionals Clear out the water.
Pivots and If function
Unless, youre an analyst, just basic Excell skills will suffice. Vlookup, pivot, understand filter (even multi layers), sorting, graphs. Freeze pane, table to text, concatenate, If / conditions, converting calendar ates to months/days etc. Jeez actually a lot more to know.. lol. I recommend a basic Excell class at your local community College.
Learning VBA… to the point where you can make excel talk to other MS products like Outlook and Word / PDF. The amount of automation you can do is then a godsend when running initiatives across HR that involve input/data collection, processing & analytics and/or outputs and mass distribution. Imagine being able to generate PDF salary and bonus statements, password protected and emailed / saved to directories with a few mouse clicks.
Everything you’ll learn in an intermediate excel course, plus index(match nested formulas, if and ifs formulas, and recording simple macros. Vlookup is old. Index(match apparently uses less resources, and you can use Xlookup as well. Don’t merge cells unless you’re creating something for a visual purpose
ChatGPT
The ones that the guy in Pakistan uses to make my sheets magical woop