Making a Skills Matrix for your Team with Excel

Подписаться 14 тыс.

Просмотров 55 тыс.

50% 1

Curious about taking your project management career to the next level? Dive into 1-to-1 coaching and mentorship with me. Discover more here: / influentialpmo
#projectmanagement
This tutorial shows how to create a basic skills matrix for capturing and presenting your teams skills in Excel, using vlookup function and charts. This will be useful for anyone managing a team and interested in developing the skills of that team.
Timestamps:
0:00 - Intro
1:20 - Create the Data worksheet in Excel
5:00 - Create the Skill worksheet in Excel
5:56 - Make a dropdown list in Excel using Data Validation
7:28 - Transpose paste in Excel to turn horizontal data into a vertical list
8:00 - Vlookup function in Excel to find data in a table
12:25 - Creating charts in Excel that are updated by our Vlookup data - creating radar chart and column chart in Excel
15:15 - Setting Print Area in Excel to limit the outputs
The sequel to this video which shows how to create team skill views and charts can be found at: • Excel Charts for Begin...
To download my copy of this template, sign up for the Influential PMO newsletter at: geni.us/skillsmatrix
#exceltutorial #excel

Опубликовано:

29 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 57

@InfluentialPMO 18 дней назад

Looking to grow in your project management career? Get simple, effective coaching and mentorship. Learn more here: www.patreon.com/influentialpmo

@Vin-og2dj 9 дней назад

OMG!!! Thank you so much for this!!! haha!! I had an assignment due and this was SO helpful!! Thank you again!

@InfluentialPMO 9 дней назад

Glad it helped! 😀

@InfluentialPMO 3 года назад

Hey, I hope you find this useful. I’ll be making a follow up to this video about adding teams and looking at team data in the near future. Keep an eye on the channel so you don’t miss it.

@JamesPeluso 2 года назад

Hi! This video was amazing! did you ever got around to making a follow up?

@JamesPeluso 2 года назад

Found it incase anyone wants to watch it! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-m5_FSx2LQB8.html

@InfluentialPMO 2 года назад

@@JamesPeluso You beat me to it. I'm glad you enjoyed the first video enough to find to the second!

@allegrazz Год назад

I fiddled with this a few months ago to get familiar, and now I've finally completed the survey I needed. I'd made a template following your steps and was able to fit in my new data with only modest adjustments. I still needed to replay you a few times to make sure I was doing it right, but it seems to work! Thank you so much for your clarity!

@krisschnee3151 Год назад

Thank you! I was shown a different video about this topic that managed to boast about how many features the maker's project had, yet did a poor job of explaining how to actually build it. I learned a few things I can use in the future besides solving my immediate problem.

@philizzo4764 11 месяцев назад

you saved my life and showed me, that excel isn't that bad :) thanks for that!

@siphesihlenicholsonkhoza9876 3 года назад

I cannot thank you enough for this tutorial. My life has changed!

@InfluentialPMO 3 года назад

I may retitle this video “Create a Life Changing Skills Matrix” 😁 Thank you for the feedback - if there’s anything else you need the channel to cover - just let me know.

@amandamiles5885 3 года назад

So useful! Thank you very much, I followed step by step and amazingly (with my track record) this worked perfectly! Very keen to understand how to use this for full team skills matrices, will keep a look out for that tutorial 👍

@InfluentialPMO 3 года назад

Thank you Amanda! I’m glad it worked for you. I was going to record the next part in a few weeks, but I won’t keep you waiting. Look for it next week 👍

@markaustin3724 6 месяцев назад

This was an informative video! I like what was taught. The instructions were very clear and the video has a great pace.

@oscarpereira3730 3 года назад

Thanks a lot!! Well explained and very helpfull. Gracias totales!!

@InfluentialPMO 3 года назад

Thank you - I’m glad the video was helpful to you. And thank you for taking the time to comment, it is appreciated.

@IronPump09 2 года назад

Perfect - Thanks a lot!

@InfluentialPMO 2 года назад

😃 you’re welcome

@inesmariacoba3701 3 года назад

Thanks for the video, really useful : )

@InfluentialPMO 3 года назад

Thank you for taking the time to feedback, I really appreciate it.

@InfluentialPMO 2 года назад

If this is useful to you, take a look at the sequel where I created charts to illustrate the data more easily: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-m5_FSx2LQB8.html

@rajanikumari6252 Год назад

This video is really helpful i could build from scratch

@InfluentialPMO Год назад

That’s great to hear. Thank you 😊

@luckyandreyanshiplayhouse1714 2 месяца назад

Great one.

@InfluentialPMO 2 месяца назад

Thank you 🙏

@Akash_Junavane 3 года назад

Very helpful

@InfluentialPMO 3 года назад

Thank you for the kind feedback.

@edwardrobson 2 года назад

I'm only part way through so forgive me if this is addressed later - when setting up you grid of info on the Data worksheet turn it into a table (Ctrl+t). In that way, when you add new names the validation dropdown in the presentation page will automatically take in the new entries. Just a suggestion :-)

@InfluentialPMO 2 года назад

Good shout! Thanks for the feedback 👍

@joliemi6954 Месяц назад

I tried but it's not working 😢 does it also work when the figures are with percentage?

@addisonwong3032 29 дней назад

is it possible to do the opposite? I'm currently using this guide to track courses achieved by my colleagues. By searching their name, I can have an overview of all the courses they have completed. How do I make it such that by searching a specific course, I can have an overview of all the colleagues that have completed the course?

@nikkistanbridge4462 3 года назад

Very helpful thank you. I do have a question though what if the formula works for the first few pieces of data but then you get #N/A?

@InfluentialPMO 3 года назад

Hi, I'm really sorry - I've only just seen this comment - for some reason the RU-vid filter moved it to a review area that I've never seen before! :-O It sounds a little like the vlookup is looking at the wrong cell. I suggest putting sequential numbers - 0 to 9, over and over in all of the cells being looked up and verifying the Vlookup is pointing where you expect it to. It's remarkably easy, even when experienced, for this the vlookup to be pointing at the wrong column. Give it a try, and let me know if you still have any problems. And again, I am very sorry for the delay in getting back to you. Stuart.

@waynereeves3121 2 года назад

Hi Stuart, I love your tutorials they are so helpful, so I have been following this video for my skills matrix but instead of numbers in the skills I have conditional formatting with dates and colours, so when I try and follow your video its not working, also my names are along the columns and tasks down the rows. I would like the individual skills list to bring up the dates in the correct places, if this makes sense, can you advise

@InfluentialPMO 2 года назад

Hey Wayne, thanks for the feedback. I’m glad the videos have helped you. I don’t think the lookups can see the conditional formatting, only the values - so, I’d suggest applying the formatting rules to the page that’s showing the output rather than the data page. I suspect as you’ve got the axis’ set up differently, you’ll need to use a different lookup. Connect with me on LinkedIn if you can and I’ll try to arrange a meet up to take a look at the document with you. Can promise a solution, but happy to try 👍

@kirkwilson5117 2 года назад

I was looking for a qualification matrix... say something similar to this that captures employees skills from their resume's upon entry to a company so that I can use it to build internal continuous improvement teams. I have not seen any cases where this qualification matrix is used. It starts with what training the people have been trained to after onboarding with the company which is also good. Question?? Is anyone using a Skills / Qualification matrix generated from Resume submissions?

@InfluentialPMO 2 года назад

I'm sure this would have value, but only if policies and procedures were in place that made use of the information. Just storing it would not result in any benefits at all - which from my own experience appears to be the norm.

@kirkwilson5117 2 года назад

@@InfluentialPMO I agree, my thought process is if the employer has a high volume of projects, or a challenge of stalled projects and growth, or a need for projects but no project activity this information can be mined for making effective project teams quickly and internal training. I envision that the information would be available to select staff such as management, HR, employee development staff.

@rulewithglen 2 года назад

Not sure why, but I get a #spill! error on O365 when trying the vlookup at video 10:40. Need to see how I can fix this and will proceed after that. Thanks so far!

@InfluentialPMO 2 года назад

I’ve not seen that before - would you like to connect via LinkedIn and send me some screenshots?

@johannaboman1634 2 года назад

I tried following this with an error at the very first attempt of vlookup, as it did not allow a comma. It worked when applying a semicolon instead.

@InfluentialPMO 2 года назад

I’m glad that it worked (eventually), and well done for improvising to make it work 👍

@johannaboman1634 2 года назад

@@InfluentialPMO According to my colleague - a semicolon is the standard in Sweden. Perhaps it is the same in other countries :)

@InfluentialPMO 2 года назад

That’s interesting, I had no idea about that. Thank you for sharing though - it could really help others watching the video 👍

@anthonyelg9058 2 года назад

@@InfluentialPMO Yes even here in France, we use semicolons instead of commas

@ruthwangui-thedynamichr7566 Год назад

Hello Sir, thank you for this information..however you lost me while making the sequence for the demo results of the skills for each person..anyone kindly help

@InfluentialPMO Год назад

Oh no, I'm sorry about that - do you have the timestamp for when you got lost?

@ruthwangui-thedynamichr7566 Год назад

Yes, at 4:25 on putting sequential numbers..what's the formula for making the demo results sequentially

@stephaniejones6796 Год назад

Help!! I have fallen at the first hurdle, VLOOKUP is coming up N/A but I followed your steps exactly, I can't see what the issue could be. I tried the semicolon suggestion a previous comment suggested but it would not accept that.

@InfluentialPMO Год назад

Oh no, are you still stuck?