Comprehensive Data Fundamentals, Excel Analytics, and Data Science Study Guide

Introduction to Data Fundamentals

  • Data vs. Information

    • Data: Raw facts and figure inputs that have not yet been processed or contextualized (e.g., individual customer names, product codes).
    • Information: Data that has been organized, structured, or processed to render it meaningful and contextually useful (e.g., identifying sales increases from 100 to 200 units, determining which product sold the most).

  • Structured vs. Unstructured Data

    • Structured Data: Data organized in a standardized, fixed format—typically formatted into rows and columns—making it highly accessible to store, search, analyze, and process (e.g., a relational customer database containing fields for names, ages, phone numbers, and addresses).
    • Unstructured Data: Data that lacks a predefined or fixed structural framework, making it significantly more difficult to store, search, and analyze using standard tools (e.g., digital images, videos, emails, and audio files).

  • Quantitative vs. Qualitative Data

    • Quantitative Data: Numerical measurements and values that can be counted, measured, and analyzed statistically (e.g., age, total income, sales revenue, or total number of customers).
    • Qualitative Data: Non-numerical descriptive information that characterizes attributes, qualities, subjective experiences, or operational properties (e.g., customer feedback comments, open opinions, or product preferences).

  • Data in Business

    • Purpose of Data: To provide key organizational information that enables businesses to analyze complex situations, make evidence-based decisions, identify emerging market trends, and systematically solve problems.

  • Sources of Data

    • Internal Data: Data generated and collected directly from within an organization's internal operations (e.g., internal sales records, employee HR records, and customer records).
    • External Data: Data collected from sources outside the boundary of the organization (e.g., market research reports, competitor tracking data, external website metrics, and social media platforms).

  • Reporting Systems

    • Definition: Integrated operational systems designed to collect, process, and present organizational data in structured reports, enabling leadership to monitor business performance and make informed tactical decisions (e.g., a monthly sales report detailing total revenue and net profits).

Big Data and the Internet of Things (IoT)

  • Big Data Fundamentals

    • Definition: Extremely large, dynamic, and complex datasets that exceed the storage, management, and processing capabilities of traditional database management tools (e.g., high-volume data streams generated by social media networks, online transaction systems, or global GPS tracking devices).
    • The 5 V's of Big Data:
    • Volume: The massive quantity and scale of data generated from countless disparate sources.
    • Variety: The diverse structural formats in which data arrives, including structured, semi-structured, and unstructured data.
    • Velocity: The rapid continuous speed at which new data is generated, ingested, and processed.
    • Veracity: The degree of uncertainty, inconsistency, quality variation, and reliability associated with incoming data sources.
    • Value: The ultimate business utility, economic impact, and actionable insight extracted from the collected data.

  • Internet of Things (IoT)

    • Definition: An interconnected global network of physical computing devices embedded with sensors and software that communicate via the internet to collect, transmit, and exchange data autonomously.
    • Examples: Smartwatches, smart televisions, and automated smart home security systems.

Spreadsheet Basics and Excel Functionality

  • Spreadsheet Overview

    • Microsoft Excel: A powerful spreadsheet application utilized to store, organize, compute, analyze, and present structured quantitative data using grid-based systems consisting of rows, columns, mathematical formulas, data charts, and dynamic tables.

  • Core Spreadsheet Formulas and Functions

    • Addition: =SUM(B2:B6)=\text{SUM}(B2:B6) — Computes the arithmetic total of all numerical values within the specified cell range.
    • Mean Calculation: =AVERAGE(B2:B6)=\text{AVERAGE}(B2:B6) — Computes the statistical average of the dataset in the designated cell range.
    • Numerical Count: =COUNT(B2:B6)=\text{COUNT}(B2:B6) — Calculates the total number of cells within a range that contain numeric values.
    • Non-Empty Count: =COUNTA(B2:B6)=\text{COUNTA}(B2:B6) — Calculates the total number of non-empty cells within a range regardless of data type.
    • Maximum Value: =MAX(B2:B6)=\text{MAX}(B2:B6) — Extracts and returns the highest numeric value from the dataset.
    • Minimum Value: =MIN(B2:B6)=\text{MIN}(B2:B6) — Extracts and returns the lowest numeric value from the dataset.
    • Logical Evaluation: =IF(B2≥50,"Pass","Fail")=\text{IF}(B2 \ge 50, \text{"Pass"}, \text{"Fail"}) — Evaluates a single logical expression, returning a specified value if TRUE and an alternate value if FALSE.
    • Multi-Condition Logical Evaluation: =IF(AND(B2=$B$8,C2="In Stock"),"YES","NO")=\text{IF}(\text{AND}(B2 = \$B\$8, C2 = \text{"In Stock"}), \text{"YES"}, \text{"NO"}) — Combines multiple logical operations to test concurrent parameters.
    • Vertical Lookup: =VLOOKUP(E2,A2:C6,3,FALSE)=\text{VLOOKUP}(E2, A2:C6, 3, \text{FALSE}) — Searches vertically down the first column of a table for a key value and returns data from a specified column in the matching row.
    • Horizontal Lookup: =HLOOKUP(B1,B1:F3,3,FALSE)=\text{HLOOKUP}(B1, B1:F3, 3, \text{FALSE}) — Searches horizontally across the top row of a table for a key value and returns data from a specified row in the matching column.
    • Sample Standard Deviation: =STDEV.S(B2:B6)=\text{STDEV.S}(B2:B6) — Computes the standard deviation based on a sample of a population to quantify variance.

Advanced Data Manipulation and Visualization

  • Data Manipulation

    • Definition: The systematic process of modifying, restructuring, cleaning, sorting, and transforming raw datasets into organized formats to enhance readability, utility, and analytical efficiency (e.g., sorting numeric entries from highest value to lowest value).

  • Pivot Tables

    • Definition: An interactive analytical tool within spreadsheet applications used to rapidly summarize, re-group, analyze, and compare extensive quantitative datasets.

  • Automation via Macros

    • Definition: Built-in stored sequences of commands, keystrokes, or automated scripts designed to execute repetitive data-entry and formatting tasks automatically.

  • Data Visualization Types

    • Pie Chart: A proportional circular graph partitioned into slices to represent data as percentages or fractional components contributing to a whole.
    • Line Chart: A continuous visual graph that connects distinct data points via straight line segments to show trends and continuous changes over time.
    • Column Chart: A visual display utilizing vertical or horizontal rectangular bars to compare discrete numeric values across distinct categories.

Introduction to Data Science

  • Definition of Data Science

    • An interdisciplinary field combining art and science focused on acquiring deep domain knowledge through data extraction, systematic collection, quantitative analysis, and context interpretation to guide strategic decision-making.

  • The 5-Step Data Science Process

    1. Formulate a Question: Define a clear, actionable problem statement or analytical question that requires empirical resolution.
    2. Collect Data: Gather relevant raw data streams from targeted internal and external data sources.
    3. Explore Data: Perform exploratory data analysis to detect patterns, anomalies, distributions, and structural relationships.
    4. Create a Model: Construct mathematical, statistical, or machine learning algorithms based on collected parameters.
    5. Visualize and Present Results: Build graphical visualizations to present findings to stakeholders clearly.

  • Core Skill Domains of a Data Scientist

    • Primary Pillars:
    • Math and Statistics
    • Computer Science / IT
    • Domain / Business Knowledge
    • Interdisciplinary Skill Overlaps:
    • Machine Learning: Intersection of Math & Statistics and Computer Science / IT.
    • Software Development: Intersection of Computer Science / IT and Domain / Business Knowledge.
    • Traditional Research: Intersection of Math & Statistics and Domain / Business Knowledge.
    • Data Science Core: The full synthesis where all three foundational pillars—Math & Statistics, Computer Science / IT, and Domain / Business Knowledge—overlap.

Venn diagram illustrating the core skills and domain intersections of Data Science

  • Data Science Tools and Technologies
    • Programming Languages:
    • Python
    • SQL
    • Java / JavaScript / C++
    • Platforms and Software Tools:
    • Microsoft Excel
    • Apache Spark
    • Apache Hadoop
    • Power BI
    • Tableau