5 Tips for Analyzing Your Studies Using Spreadsheets

During my time as a user researcher at Key Lime Interactive, I’ve had the opportunity to conduct several studies involving a large number of participants. Depending on the complexity of the study, that can yield a lot of data to tackle and distill down into impactful and actionable findings. I value process and efficiency in my life, so I started investigating ways to organize and analyze my data more effectively. As Thomas Edison once said, “There's a way to do it better – find it.”

Over time, I’ve found a few techniques to use spreadsheet functions to streamline my analysis without sacrificing the quality of my findings. In fact, these techniques have helped me to quickly synthesize high volumes of data to reach actionable insights more quickly. Here are the 5 most powerful ways I use spreadsheets during my research:

One of the initial challenges in data analysis is handling unstructured data. Dropdown lists come to the rescue here, allowing the creation of a standardized list of options for specific variables. By using dropdown value lists, I ensure consistency and easily identify patterns or themes in my data.

For instance, when categorizing user feedback, using dropdowns can help researchers associate common themes which can later be used to group and compare findings. Formulas and pivot tables require exact values to calculate metrics on non-numeric data.  Dropdowns are an easy way to prevent misspellings when entering values across a large dataset. Furthermore, when using spreadsheets as part of the note-taking process, dropdowns are an efficient way to record observations such as completion status, single-choice questions, and summarizing where a participant goes during a task. 

In both Excel and Google Sheets, creating dropdowns is straightforward. With Excel, Data Validation allows the user to define the list of options, while Google Sheets provides Data Validation under the Data tab. Utilizing this feature will lead to a more organized dataset and accelerate analysis.

Analyzing data can be overwhelming, especially when dealing with large datasets. Conditional formatting is a game-changer for visually highlighting and categorizing important information and making it easier to spot trends or outliers. By setting up rules, cells that meet certain criteria will be automatically formatted with colors or styles, allowing the researcher to focus on critical aspects.

Conditional formatting allows for changes to both numeric and non-numeric values in cells. For example, I might highlight user satisfaction ratings below a specific threshold or emphasize the most frequently mentioned pain points in user feedback. When taking notes, I can create unique keywords, such as “#quote”, and use conditional formatting to help locate those specific pieces of information more quickly throughout the data. 

In Excel and Google Sheets, conditional formatting options are available under the Home tab, making it simple to apply and customize according to research needs.

Pivot tables are a researcher's best friend when it comes to summarizing and analyzing large datasets swiftly. These dynamic tables allow the aggregation, reorganization, and visualization of data in various ways without altering the original dataset. With just a few clicks, the researcher can perform calculations, aggregate data, and uncover valuable insights.

With the power of pivot tables, task success can be compared across a subset of users. Dropdowns identifying themes across user feedback can be used along with pivot tables to calculate how often a topic is mentioned and further identify common pain points across the data. 

In Excel, create a pivot table by selecting the data and clicking on the PivotTable button under the Insert tab. In Google Sheets, go to the Data tab and choose Pivot Table. From there, arrange and customize the pivot table to extract meaningful conclusions from the research data.

Formulas are the powerhouse of spreadsheet analysis, offering endless possibilities to manipulate and derive insights from data. As a user researcher, some of the most valuable formulas I've used include COUNTIF, SUMIF, and AVERAGE, which help summarize quantitative data effectively. Adding “S” to the end of any of these formulas increases their effectiveness by allowing analysis across multiple subsets of data. For example, COUNTIF alone could be used to identify how many participants gave a particular answer to a single choice question. COUNTIFS helps identify how many participants who succeeded at a particular task gave a specific answer. 

For tracking trends and patterns, sparklines are a great addition to a spreadsheet. Sparklines are small, simple charts that can be placed right next to data to visualize trends, such as user engagement or satisfaction levels over time. The ease with which sparklines are created can also help streamline the creation of more complex charts by helping researchers hone in on exactly what data to visually represent. 

Moreover, formulas can be used in combination with conditional formatting to target even more specific subsets of data than the default formatting options might allow. Rather than color coding data based on a single word or phrase, I can color code using “if” statements to highlight only the data the I want to focus on. 

Repetitive tasks are a natural part of data analysis, but they can be time-consuming and prone to human errors. This is where macros come into play. Macros are automated scripts that record a series of actions and allow replay with a single click. They are invaluable for reducing repetitive data cleaning, formatting, or charting tasks. 

For example, if I find yourself frequently highlighting cells with a specific color and bolding the text within them as a method of categorizing my data, the creation of a macro gives me a single keyboard shortcut to complete the same multiclick process, saving me time to focus on my data.  

Excel and Google Sheets both support macros, but with slight differences in implementation. In Excel, users can record and run macros using the Developer tab. In Google Sheets, users can use Google Apps Script, a powerful scripting language, to automate actions. Mastering macros can save hours of manual work and make research analysis more efficient.

These techniques have transformed the way I approach data analysis, saving me more time to focus on the insights that matter. By employing these 5 powerful tips for analyzing your studies using spreadsheets, you can enhance your efficiency and extract deeper insights from your data, too. 

At Key Lime Interactive, we thrive on the diverse backgrounds and experiences of our researchers. Our mission is to collaborate with organizations like yours to conduct impactful studies and uncover valuable insights that drive your business forward. Let's work together to unlock the full potential of your research projects and gain a deep understanding of your users, enabling you to make informed decisions that lead to exceptional user experiences.

Contact us to learn how we can add a unique perspective to your next research project and partner with you in achieving your goals through user-centric research.