Basics of Data Analytics using Spreadsheet Laboratory Notes
Subject Overview
The practical companion to Basics of Data Analytics using Spreadsheet, building Excel skills from basic functions and data import/
Subject at a glance
2
Units
18
Topics
1
Credits
2
Infographics
Unit-wise Syllabus
2 units — open any unit for detailed topic-wise notes, diagrams and important exam questions
- Excel basics — workbook/worksheet/cells/ranges
- data entry and formatting
- SUM/AVERAGE/MIN/MAX/ROUND
- cell referencing
- importing data from CSV/text/web
- data cleaning and transformation
- Find & Replace and text functions
- descriptive statistics — mean/median/mode
- range/variance/standard deviation
- frequency distributions and the Data Analysis Toolpak
Average
=AVERAGE(B2:B50)
Rounding
=ROUND(B2, 2)
Standard deviation
=STDEV.S(B2:B50)
Frequency count
=COUNTIF(C2:C50, "Pass")
Absolute reference
=$B$2 * C2
$ fixes the cell when copying
Study guide
The first half teaches you to understand and describe data in Excel: workbooks, worksheets and ranges, data entry and formatting, SUM, AVERAGE, MIN, MAX and ROUND, cell references, importing from CSV, text and the web, cleaning and transformation with Find and Replace and text functions, and descriptive statistics with the Data Analysis Toolpak. Make sure you understand relative versus absolute references; they cause most formula errors.
- Logical functions (IF, AND, OR, IFERROR)
- lookup/reference functions (VLOOKUP, HLOOKUP, INDEX, MATCH)
- aggregation functions (SUMIFS, COUNTIFS, AVERAGEIFS)
- text functions
- chart types and advanced charting
- PivotTables and Pivot Charts
- dashboard concepts
- conditional formatting and interactive dashboards with slicers and timelines
IF
=IF(B2>=40, "Pass", "Fail")
VLOOKUP
=VLOOKUP(A2, Table, 3, FALSE)
INDEX-MATCH
=INDEX(C:C, MATCH(A2, A:A, 0))
SUMIFS
=SUMIFS(D:D, B:B, "North", C:C, "Q1")
IFERROR
=IFERROR(formula, "Not found")
Study guide
The second half turns data into insight: logical functions (IF, AND, OR, IFERROR), lookup functions (VLOOKUP, HLOOKUP, INDEX, MATCH), conditional aggregation (SUMIFS, COUNTIFS, AVERAGEIFS), charts, PivotTables and Pivot Charts, conditional formatting and interactive dashboards with slicers and timelines. Build one complete dashboard from a data set; it is also a strong portfolio item.
Why Basics of Data Analytics using Spreadsheet Laboratory Matters
Excel remains the most widely used analysis tool in business. PivotTables, lookup functions and dashboards are standard skills listed in analyst and operations job descriptions.
How to Prepare for Basics of Data Analytics using Spreadsheet Laboratory
- Practise VLOOKUP and INDEX-MATCH on two linked tables.
- Build a PivotTable dashboard from a sales data set.
- Always add titles, labels and units to charts.
Basics of Data Analytics using Spreadsheet Laboratory: Frequently Asked Questions
What Excel skills are taught in this lab?
Formulas, text and lookup functions, conditional aggregation, charts, PivotTables, conditional formatting and dashboards with slicers.