Basic Excel for Data Analytics

1. Introduction

Spreadsheets are a practical interface for small and medium-sized analysis tasks. They let a learner inspect records, calculate derived values, and share a result without setting up a database. A reliable workbook still needs a clear structure and reproducible formulas.

2. Core concepts

Keep one observation per row and one variable per column. Use a single header row, consistent types within a column, and a stable identifier where possible. Formula references can be relative, absolute, or mixed; understanding how they change when copied prevents subtle errors.

3. Topics covered in this module

• Workbook, worksheet, cell, range, and structured table
• Relative and absolute references such as A2 and $A$2
• SUM, AVERAGE, COUNTIF, IF, and lookup patterns
• Sort, filter, remove duplicates, data validation, and error checking

4. Syntax and how to read it

=IF(C2>=1000,"High","Standard")
=SUMIFS(Orders[Amount],Orders[Region],F2)
=XLOOKUP(A2,Products[SKU],Products[Name],"Not found")

In Excel, a formula starts with =. Structured references use table and column names, which are easier to review than long ranges. Function availability and argument separators can vary by spreadsheet application and locale.

5. Practical workflow

Convert a clean rectangular data range into a named table, add a formula column, then filter the result to check edge cases. Keep raw data on a separate sheet and make transformations visible.

6. Common mistakes

• Including a total row in a range that is later summed again.
• Sorting only one column and breaking the relationship between fields in each row.

7. Best practices

• Use descriptive headers and Excel Tables instead of unexplained cell ranges.
• Check formulas on a few records manually before filling them down.

8. Practice task

In a table with Region and Sales columns, write a formula to total Sales for the region stored in H2.

9. One possible solution

Use =SUMIFS(SalesTable[Sales],SalesTable[Region],H2). The criteria range and sum range should cover the same table rows.

10. Summary

Organize workbooks as tidy tables and use formulas, references, sorting, filtering, and validation safely. Use descriptive headers and Excel Tables instead of unexplained cell ranges. Check formulas on a few records manually before filling them down.

11. What comes next

The next module, Basic Python (AI-powered), builds on this foundation and applies it to a new part of the analytics workflow.