Familiar Tools Become New Again.
Most professionals have, in one way or another, interacted with Excel at some point in their career, but often at the surface level, and honestly i was no exception.
Having worked in the field of research,Drug discovery from natural products, Excel was always present in my workflow, primarily for collecting experimental data, storing results, sorting observations, and performing simple calculations. Then, this felt sufficient; the tool was actually doing what I needed! Or so I thought.
However, my interaction with Excel at times reached a dead end. I often found myself wondering what some of these functions actually did, and I would resort to repeated referencing and online searches. Simple operations or tasks that should have been straightforward become tedious, time-consuming, and sometimes frustrating. My one week of learning Excel at LuxDev Academy has changed my perspective entirely. I've realized it is not just a spreadsheet tool; it's also a Data analysis tool.
What is Excel?
Microsoft Excel is a software tool designed for organizing,analyzing, and visualizing data using structured rows and columns.
After the data entry process, Excel enables users in:
- Managing large datasets efficiently
- Cleaning and transforming datasets.
- Performing mathematical and statistical calculations.
- Creating charts, dashboards, and automating workflows.
Excel in Real-World Scenarios.
Almost every data-driven field relies on Excel, but I will be focusing on Excel in Drug Discovery Research. In drug discovery, researchers often begin their work by referencing already published datasets either to identify research gaps, avoid duplication of previous work, or compare experimental outcomes.
Let's explore one modern approach,Network Pharmacology. Researchers gather chemical and biological data from multiple databases, such as compound libraries, protein interaction databases, and pathway repositories. These datasets originate from different sources, and they have different formats. Excel, therefore, becomes the integrating tool where researchers:
- Removes duplicates
- Merges datasets
- Standardises variables
- Filter relevant targets
- Prepares data for downstream analysis
Different Formulas in Excel and how they apply in Data Work.
During my one week of learning Excel, I began by learning Excel formulas not just as abstract functions but as a solving tool, and here are some of the few examples and how they apply in a real analytics workflow, in our case, drug discovery research.
Logical Functions-IF()
This function would apply in categorizing experimental outcomes. A good example application is classifying compounds as Active or Inactive based on inhibition values. This would allow automatic classification instead of manual labelling.
=IF(B2>50,"Active","Inactive")
Text Cleaning Fuctions-LEFT(),RIGHT(),SUBSTITUTE()
These functions are useful when dealing with datasets with inconsistent formatting. The best example is extracting numerical values from ranges or percentage strings during data cleaning. These functions will transform messy data into analysis-ready datasets.
Lookup Functions-XLOOKUP()/VLOOKUP()
How do you apply this function? A good example is when matching compound names with biological targets imported from separate databases. This is a very useful function because rarely do datasets originate from the same source.
>XLOOKUP(A2,Sheet2!A::Sheet2!B:B)
Aggregate Functions-AVERAGE(),SUM(),COUNT()
Most of the experiments are done in triplicate, so these aggregate functions are applied when summarising experiment replicates.
Pivot Tables.
Making a pivot table is one of the most transformative skill i learnt. Now, instead of manually summarising results, the pivot table allows me to compare activities across compounds, summarise experimental batches, and analyse trends instantly, interactively, and all in one Excel workbook.
Example of an Excel workflow
Interactive Dashboards
Drug research does not end with lab work and analysis of the data; researchers need to disseminate their months/year of research work to the rest of the world. This session is always allocated very limited time,15 to 20 mins max, and presenting raw spreadsheets is idealistic. The interactive dashboards skill becomes invaluable in this section. With this skill as a researcher, I can :
- Summarise my findings visually
- Use slicers to explore my datasets interactively
- Use interactive charts to explain trends
- Highlight key findings instantly (KPI section)
Example of an Excel Dashboard
Rediscovering Excel
Earlier, Excel was simply a storage tool or a digital notebook for recording values. The view has changed to a system for thinking analytically. Data collection alone does not translate to insights. You may gather very important data, but without the ability to clean, analyse, and visualize it effectively, its true value remains hidden. Looking back, I only wish I had learnt this skill earlier. Nevertheless, beginning this learning path has changed how I approach data, which is not just a value to be recorded, but a coded message waiting to be decoded!
Top comments (0)