Core Concepts in Excel

Demo

download the demo xlsx here -> demo.xlsx

source

“Data is the new oil. It’s valuable, but if unrefined it cannot really be used.” - Clive Humby

Excel in Research

in research we work with various types of data:

  • Species counts
  • Environmental measurements (temperature, salinity, pH)
  • Geospatial data
  • Temporal data (time series)
  • Many more…

Excel provides a user-friendly platform to:

  • Input and organize raw data
  • Clean and preprocess datasets
  • Perform basic statistical analyses
  • Create visualizations (charts and graphs)
  • Collaborate with colleagues

Anatomy of a Spreadsheet

  1. Workbook: An Excel file (with a .xlsx extension) that contains your datasets. Think of it as a book that holds all your research data.
  2. Worksheet: Individual ‘tabs’ within a workbook. Dedicate each worksheet to specific parts of your research or different datasets.
  3. Cells, Rows, and Columns: The building blocks of Excel’s grid structure.
    • Cells: Individual data points where rows and columns intersect.
    • Rows: Horizontal arrangements of cells (numbered).
    • Columns: Vertical arrangements of cells (lettered).
  4. Functions and Formulae:
    • Functions: Pre-built operations in Excel (e.g., SUM, AVERAGE, COUNT).
    • Formulae: Custom expressions you create using functions, cell references, and operators.
Figure 1: Rows and columns in excel

Important Considerations

  1. File Formats:

    • .xlsx is Excel’s native format but is not open-source.
    • Comma-Separated Values (CSV) files are plain text, making them more accessible across different platforms and software. However, they lack formatting and multiple-sheet capabilities of .xlsx files.
    • Consider saving final datasets as .csv for improved transparency and compatibility

  2. Excel vs Google sheets:

    • While Google Sheets offers collaborative features, Excel is preferred for research due to superior handling of large datasets - More robust statistical and analytical functions, Better integration with other scientific software, Enhanced data protection and privacy controls

  3. Advanced Tools for Data Analysis:

    • While Excel is valuable for initial data management, limitations emerge with complex analyses or large datasets. These are better handled by specialized software like R, Python, or MATLAB.

Commonly Used Excel Functions

  1. Basic Arithmetic Operations

    • Addition:
    = A1 + B1
    • Subtraction:
    = A1 - B1
    • Multiplication:
    = A1 * B1
    • Division:
    = A1 / B1

  2. SUM Adds a range of cells.

    = SUM(A1:A10)

  3. AVERAGE Calculates the average of a range of cells.

    =AVERAGE(A1:A10)

  4. COUNT Counts the number of cells that contain numbers.

    =COUNT(A1:A10)

  5. COUNTACounts the number of cells that are not empty.

    =COUNTA(A1:A10)

  6. IFReturns one value if a condition is true and another if it is false.

    =IF(A1 > 10, "Yes", "No")

  7. VLOOKUP Searches for a value in the first column of a range and returns a value in the same row from another column.

    =VLOOKUP(A1, B1:D10, 3, FALSE)

  8. HLOOKUP Searches for a value in the first row of a range and returns a value in the same column from another row.

    =HLOOKUP(A1, B1:F3, 2, FALSE)

  9. CONCATENATE (or CONCAT) Joins two or more text strings into one.

    =CONCATENATE(A1, " ", B1)

  10. TODAY Returns the current date.

    =TODAY()

  11. SUMIF Adds the cells specified by a given condition or criteria.

    =SUMIF(A1:A10, ">10")

  12. COUNTIF Counts the number of cells that meet a criterion.

    =COUNTIF(A1:A10, ">=10")

  13. TRIM Removes extra spaces from text.

    =TRIM(A1)

  14. ROUND Rounds a number to a specified number of digits.

    =ROUND(A1, 2)