There is so much to learn when using Microsoft Excel. From shortcuts, to formatting tricks, and even all the formulas that are available for synthesizing or analyzing information - there is no shortage of new ideas to better use the software and become a superuser. Using Excel in your personal life is one thing, no one is paying attention to formatting, or permissions, or even double checking to ensure that the information displayed in the spreadsheet is correct - but when it comes to work: that’s another story entirely. Whether you’re new to excel, or you can type IF functions from memory, we have gathered 5 tips to put in your arsenal to take your certification tracking to the next level.
Tip #1: Charting
If your data is going to be presented to your bosses, or if you’re looking for a clear way to visually represent a trend that you’re seeing across the employees while they are training - taking some time to create charts to display information is the way to go.
Whether you’re looking to show trends in scoring across certain tests that you are administering to employees to show levels of on-the-job competence or success, or you are working to show the number of employees who hold a coveted position within training. There are plenty of ways to use charting to your advantage when showcasing information that is important to stakeholders. Furthermore, visually appealing data display can increase buy-in, so if you’re looking for a way to convince your bosses that more money, time or other resources are needed to increase the level or value that the training team is able to provide employees, charts and graphs may be your new best friend.
To use: enter data you wish to display in a chart within the columns and rows in Excel, with headers, then select “Insert >, Chart >, Chart Type.” From there, you can manipulate which layout, colors, figures, etc. to ensure that everything looks visually appealing and represents that data as best as possible.
Tip #2: Conditional Formatting
After years (yes, you read that right… years) of using Microsoft Excel, I was introduced to the power of conditional formatting and it changed the way that I view data, use data, and use equations in my spreadsheets. Conditional formatting applies heavily to using Excel for certifications, especially those that expire. Imagine seeing in “red” the certifications that your employees are missing, have expired, or are at the very least close to expiring. It will make searching for those certifications much easier, and will help to avoid any costly penalties that could ensue if your company just so happens to have an audit. There are ways to use conditional formatting to move from green, to yellow, to red to show the movement of time before the window of opportunity closes, and this is just one use case!
Ultimately, you can customize any of the conditional formatting tools to change based on the rules that you set forth. Conditional formatting is an extremely flexible and handy tool to play around with in excel and can help speed up any processes you have in place currently to ensure that people are up-to-date in their learning tracks, have the proper certifications, and more.
To use: Select the cell and click “Home > Conditional Formatting > Add” to set up your rules and configurations.
Tip #3: Autofill
This tip is mainly just a time-saver and works well in certain situations. Be mindful of when and where you are using the autofill function to ensure that you are always reflecting the most accurate data within your spreadsheets. A use case where it can be especially helpful: in onboarding new employees, if there are multiple that start on the same day and need the same information in which to keep tabs. Another time when it could be especially helpful is if there is training that happens offline, say a company wide training and a date needs to be recorded for each employee: autofill will save you lots of time inputting that information.
A few important things to note: when using numbers within the cell, sometimes autofill can think the purpose is to count up from the last entered number. In this case, dates can start counting forward by the year, by the day, by the month - be on the lookout for patterns like this. Additionally, autofill is not the best to use when everyone’s data looks different - yes, this seems really obvious, but it’s worth saying. Do not use autofill if there are variances in data that are unpredictable.
To use: Click and hold the right corner of the selected cells (there will be a tiny square), and drag. Super simple! Very effective.
Tip #4: Pivot Tables
Pivot Tables are powerful. Very powerful. Pivot Tables allow you to summarize, manipulate, and visualize large amounts of data in lists and tables. Even better, none of this requires the use of formulas. When using pivot tables for certification tracking, it can be helpful to summarize a long list of trainings that employees need to go through, completion rates, scores, department averages and so much more. This tool is quick at synthesizing data and can help you spot trends in your training and areas that may need a little more focus.
Don’t let the excess amount of information scare you, spend some time digging into the data of your training program, your onboarding program or other continuing education that is happening at your company to ensure that you are taking advantage, tweaking, and changing the necessary components to make it the best it can be. There is a lot to learn from data, after all, if you can’t measure it, you can’t change it. Additionally, there are some awesome resources online to get you started with pivot tables - just check them out by searching on Google.
To use: Go to “Insert > Pivot Table” and select your data range. Select your fields in the top half of the right-hand-side bar, and the bottom half is where you generate what and how data appears in your table.
Tip #5: Vlookup
This tool is especially helpful if you’re working across multiple workbooks and need to collate data. If there is data in two separate spreadsheets that needs to be brought together or matched up with other important data based on a series of shared attributes - vlookups make this task a snap.
Vlookups would be helpful to use if you are working to see different certifications or trainings that someone has undergone and apply a rule that if they finish X, Y, and Z - they will then get a promotion. Joining the data that is spread across sheets will help to find the employees that fit your criteria.
To use: Find the VLOOKUP formula in the formula menu and enter the cell that contains your reference number. Then enter the range of cells to pull data from, the column number for the data point you’re looking for, and either “True” or “False” depending on the conditions.
Excel is one of the most-used business software programs on the market, but there are many of its features that are underutilized, or unused completely. Regardless of where tracking education, onboarding, and learning are now - there are benefits to be gained from using these five tips and tricks. Whether you are planning on using excel for the foreseeable future or eventually upgrading to some sort of certification tracking software, it is safe to say that these tools will help get your data in tip-top shape to be used as effectively as possible.
What tools do you use most frequently when tracking certifications, training, onboarding and more in Excel?