Simplifying Excel Data Analysis with AI Tools Part 2

In our previous post, we introduced how AI tools like ChatGPT and Copilot can enhance Excel data analysis. Now, let's apply this to a real-world example in the Learning & Development sector. 

Step 1: Organizing Your Data 

Start by ensuring that your Excel data is well-organized and relevant to L&D needs. Your dataset might include several sheets such as: 

• Training Sessions Sheet: This includes records of all training formats, such as ILT, vILT, WBTs, and video sessions. Each record should have fields like 'Session ID', 'Course Title', 'Instructor', 'Date', and 'Type of Training' to differentiate between the various formats. 
• Employee Participation Sheet: This contains detailed attendance and engagement records for each type of training session. These records are linked by 'Employee ID' and 'Session ID’, and include specifics such as participation duration and completion status. 
• Feedback Scores Sheet: This sheet lists the feedback scores for each training session, linked by 'Session ID'. It also includes qualitative data like comments and ratings, providing a deeper insight into the effectiveness of each session across different training modalities. 

Remember PII (Personal Identifiable Information) 

In the context of linking data, the term "linked by" refers to the process of aligning similar columns across multiple sheets to consolidate related information. The key to this alignment is identifying a column that serves as a unique identifier or 'primary key' for each row of data. In the specific example we discussed earlier, the 'Session ID' acts as the primary key. This is because, in this scenario, 'Session ID' remains consistent across different sheets and uniquely identifies each session. 

It's important to note that the choice of a primary key can vary depending on the dataset and the specific needs of the analysis. For instance, in marketing, an email address often serves as the unique identifier. The appropriateness of a primary key, such as 'Session ID' or 'Employee ID', depends on its ability to provide unique and unchanging identifiers within the given context. In our case, 'Session ID' is ideal because it remains unchanged and accurately links related entries across the dataset. 

In Database Management and Statistics, you’ll need a good primary key that connects data across your organization. Let’s look at some examples of bad primary keys: 

Good primary keys are ID numbers of the employees. But it depends what information you’re looking at. Identify what data you want to join and see what’s available on the sheets you have. 

Primary Key Recap 

A primary key is a field or set of fields with values that are unique throughout a table. Values of the key can be used to refer to entire records, because each record has a different value for the key. Each table can only have one primary key. 

Step 2: Uploading and Preparing Data for AI 

Once your data is organized, upload it into an AI tool capable of processing Excel data. If you're using ChatGPT with specific integrations, ensure that these can handle Excel formats. 

Example Upload Prompt: 

• "I have uploaded the Excel workbook containing multiple sheets. Sheet 1 is the training sessions, sheet 2 is employee participation, and sheet 3 is feedback scores. Please prepare the data for comprehensive analysis.” 
• It helps to guide the AI into what you're looking for. You could say: 

• “I would like to look for any score increases in feedback sheets based on the amount of employee participation and attendance.” 

Step 3: Mapping Columns for Data Linking 

Instruct the AI on how to intelligently link data across the sheets and don’t be afraid to ask the AI prompt to do its own work. If the mappings are correct, then you can simply say “look at the sheets and tell me what you think is interesting about the data.” 

Example Mapping Instruction: 

• "Link 'Session ID' from the Training Sessions sheet with 'Session ID' in the Employee Participation and Feedback Scores sheets. Think about my original request and provide your own analysis based on the data." It’s that simple. 

Step 4: Analyzing Data 

Request the AI to analyze the data based on specific L&D objectives. 

Example Analysis Prompt: 

• "Analyze the correlation between employee attendance rates and feedback scores for each course. Identify which courses have high attendance but low feedback scores, and vice versa.

Step 5: Visualizing Data 
Ask the AI to create visual representations of the analysis. This helps in making the data easier to interpret and present to stakeholders. 

Example Visualization Request: 

• "Generate a bar chart showing average feedback scores and attendance rates for each course. Highlight courses where there is a significant discrepancy between attendance and satisfaction." 
• It might take a couple tries to get the chart/graph you need. 

Step 6: Reviewing AI Insights 

Evaluate the insights provided by the AI to determine if adjustments in the L&D strategy are necessary. 

Additional Tips 

While AI provides quantitative insights, qualitative interpretation is crucial for everything to come together. Regularly update your data and rerun the analysis to ensure that your L&D strategies remain aligned with employee needs and organizational goals. If your analysis finds any interesting stories, you should document your workflow and steps to recreate what you need later. 

Conclusion 

We encourage you to start integrating AI into your L&D data analysis processes. Stay tuned for more advanced tips and techniques in upcoming installments! 

Cover image generated by A.I. with guidance by an Ardent human.