Modern Scalable Data Collection Strategy for Skills Analysis

Maria, a passionate Learning and Development (L&D) manager, was gearing up for her biggest challenge yet: conducting a comprehensive needs analysis for her 200 employees. Her department was growing rapidly, and leadership wanted clear data to guide upcoming training initiatives. Eager to keep everything simple and organized, Maria decided to use a single Excel table to capture all relevant information—employee names, departments, roles, existing skills, required competencies, and even performance metrics. Initially, this single-table strategy seemed easy enough to maintain. She set up column after column, ensuring each row had the necessary data points.

For the first few weeks, Maria felt she’d made the right decision. She could quickly scroll through her table, see which employees lacked certain skills, and note any performance gaps. It was straightforward for her to explain the data to her boss, who appreciated the simplicity of searching a single spreadsheet. Yet, as more employees were onboarded and her company expanded its skill requirements, her once-pristine spreadsheet began to balloon with extra columns. Soon, she was adding columns for new training needs, separate fields for self-assessments vs. manager assessments, and updated performance metrics each quarter.

The turning point

The turning point came during a department-wide meeting. The company had launched multiple training initiatives within a few months, and different managers needed to capture new feedback from employees. Suddenly, Maria was receiving daily emails: “Please add these new mandatory skills to the table,” “Could you include a column for each skill’s proficiency level?” or “We’d like to record multiple assessments per quarter—can you fit them all in?” With every request, her once-clean table became wider and more unwieldy.

Before long, Maria began noticing several problems:

1. Data Overlap: The same employee details (e.g., name, department, job title) were repeated across multiple rows, increasing the chance of typos and inconsistencies.
2. Complex Filters: Whenever she tried to filter or pivot her data (e.g., by department, skill gap, or training priority), she had to navigate an ever-growing list of columns.
3. Difficult Maintenance: Entering new data became cumbersome, and any small mistake—like a row shift or incorrect formula—could wreak havoc on the entire sheet.
4. Limited Scalability: As the workforce expanded and more skill data needed tracking, the single-table approach seemed to buckle under the weight of new information.
5. Redundant Updates: Each new assessment period required Maria to add more columns, which quickly turned the spreadsheet into a patchwork of repeated fields.

Scalable Data Collection Strategy

Overwhelmed by these challenges, Maria decided to consult her colleague Raj, who specialized in data analytics. Raj’s first recommendation was to rethink the spreadsheet structure from a data analytics perspective. Instead of stuffing everything into a single wide table, Raj suggested splitting the data into multiple, related tables:

• Employees Table: Containing basic employee information (employee ID, name, department, role).
• Skills/Competencies Table: Listing all possible competencies the organization tracks.
• Assessments Table: Linking each employee to each skill, along with assessment results (self-ratings, manager ratings, required levels, and dates).

By normalizing the data in this way—using unique IDs and clearly defined relationships—Maria could drastically reduce redundancy and simplify data updates. If an employee was promoted or changed departments, she only needed to update one table (the Employees table). Adding new skills became as easy as creating a row in the Skills table, without worrying about columns clogging up her main sheet. Plus, with the Assessments table, it was simpler to track multiple ratings or changes over time. Instead of adding multiple columns for new assessments, Maria would just add new rows with different assessment dates. This structure also made it easier to pivot her data in Excel or feed it into a business intelligence tool if the organization wanted more advanced reports down the line.

Energized by this new approach, Maria also explored Microsoft Forms (MS Forms) to gather employee information. Raj explained that creating a short, well-designed form would drastically reduce the manual data entry burden. Rather than manually updating a spreadsheet each time an employee completed or needed training, employees and managers could fill out an online form capturing their skill ratings, training requests, or feedback on completed courses. The data would populate directly into a dedicated Excel workbook, or Maria could link it to SharePoint or Power Automate for even smoother processing. This method saved her hours of copying and pasting, reduced errors from manual entry, and allowed real-time feedback from her workforce.

Significant reduction in the time and effort

Before long, Maria noticed a significant reduction in the time and effort required to maintain her data. She had gone from being buried in one monstrous spreadsheet to overseeing a neatly managed system of related tables, each serving a clear purpose. Her new approach allowed her to analyze skill gaps, identify priority training needs, and track performance improvements far more efficiently. Moreover, the MS Forms integration freed her from endless back-and-forth emails, letting managers and employees input their data seamlessly.

In the end, Maria didn’t just fix her immediate spreadsheet woes—she built a modern, scalable data collection strategy that supported her organization’s continuous growth. Through her journey, she learned that sometimes, it’s better to split data into multiple tables to maintain integrity and clarity, and that digital tools like MS Forms can dramatically simplify and automate the needs analysis process.