Excel for Data Analytics - Full Course for Beginners

This section introduces the Excel for data analytics course, designed for beginners with no prior experience. It outlines the curriculum, starting with basic functions, charts, and tables, then advancing to pivot tables, Power Query, and Power Pivot. The course emphasizes hands-on learning with exercises and practice problems, leading to two portfolio-ready projects. It also highlights Excel's popularity and importance as a data analysis tool, second only to SQL for data professionals.

This part details how to access course materials, including Excel workbooks, datasets, and project files, which are all freely available on GitHub. It explains the repository structure, how to download the files, and the use of blank templates for exercises. It also introduces optional paid resources like guided practice problems and course notes. The primary dataset for analysis will be a 30,000-job posting dataset from 2023, focusing on data roles and salaries, with examples tailored for data analysts in the United States.

This segment covers installing Excel and choosing the right version for the course. It breaks down different Microsoft products: Microsoft 365, Microsoft Office Home & Student, and Microsoft 365 Online. While Windows versions (Microsoft 365, Office) are fully compatible, Mac versions and the online-only version have limitations, particularly with advanced features like Power Query and Power Pivot. The instructor recommends Microsoft 365 Family for full functionality. A step-by-step guide for installing Microsoft 365 via a free trial is provided, including how to cancel the subscription.

This lesson introduces the basic concepts of worksheets, rows, columns, and cells in Excel. It demonstrates data entry, autofill capabilities for numbers, boolean values, and dates, and explains how Excel stores dates as numbers. The section also covers modifying cell formats, using simple formulas for calculations, and concatenating text from different cells. Emphasis is placed on using keyboard shortcuts (like Ctrl C for copy, Ctrl V for paste) to improve efficiency during data manipulation.

This part focuses on managing workbooks and transferring data between sheets and workbooks. It demonstrates creating new workbooks, opening existing ones, and understanding the file menu's various options. Three methods for copying data are shown: using formulas for dynamic ranges, traditional copy-paste, and the recommended method of right-clicking a sheet to 'Move or Copy' it to another workbook. The instructor explains the nomenclature of formulas when referencing data across different sheets and workbooks.

This lesson explores the Excel ribbon, detailing the functionality of various tabs. It starts with the Home tab, covering text formatting, cell alignment, number formatting, and basic data filtering. The Insert tab is highlighted for its importance in creating tables, pivot tables, and various charts, with a quick demonstration of inserting a histogram and a clustered bar chart. Other tabs like Draw, Page Layout, Formulas, Data, Review, and View are briefly discussed, emphasizing their less frequent use compared to Home, Insert, and Data tabs.

This section delves into Excel formulas, starting with basic math operators (addition, subtraction, multiplication, division) and their order of precedence. It then moves to comparison operators (equal to, greater than, less than) which return boolean values (true/false). A key concept introduced is cell referencing, explaining absolute and mixed references using dollar signs or the F4 shortcut, essential for dynamic formulas when autofilling across cells. The lesson concludes with a practical application of formulas to identify job postings meeting specific experience and salary conditions.

This lesson introduces Excel functions, emphasizing their efficiency over manual calculations for complex tasks like averaging or conditional evaluations. It compares manual summation to the AVERAGE function and highlights the AND function as a cleaner alternative to nested IF statements. The Formulas tab in the ribbon is explored, especially the 'Insert Function' feature for discovering and understanding various functions. The lesson also covers common errors encountered with formulas and suggests using AI chatbots for quick troubleshooting, showcasing COUNT and COUNTIF functions for basic data analysis.

This lesson focuses on logical functions, particularly IF, AND, OR, and IFS. It begins with a quick recap of the IF statement's utility in providing conditional responses. The core of the lesson demonstrates how to identify job roles (Data Analyst, Business Analyst) using nested IF statements, emphasizing its readability issues. Instead, it advocates for AND and OR functions to combine conditions more effectively. The IFS function is introduced for handling multiple conditions, useful for 'bucketing' data like salaries into different categories (e.g., 'no data', 'salary > 85k', 'salary low').

This lesson focuses on essential math and statistical functions for Exploratory Data Analysis (EDA). It covers COUNT, COUNTA, COUNTIF, and COUNTIFS for counting entries under various conditions, showing how these can be used to filter data for specific criteria (e.g., jobs in the United States, data analyst roles). SUM, SUMIF, and SUMIFS are demonstrated for aggregating numerical data like salaries. The lesson highlights the practical application of these functions to derive insights, such as job funnel visualization and initial salary comparisons, while also touching upon common errors like the #VALUE! error.

This lesson delves into advanced statistical functions, including MEDIAN, STANDARD DEVIATION, QUARTILES, and MODE, to analyze salary distributions. It demonstrates how to calculate these values and explains their meaning, particularly contrasting Median with Average to illustrate skewness caused by outliers (high salaries). Histograms are used to visualize salary distribution. The lesson further introduces quartiles (first and third, inclusive and exclusive) to understand data spread and median. Finally, it applies these functions to rank job titles by average salary, showcasing their utility in data analysis and visualization.

This lesson focuses on collaboration features, particularly data validation and sheet protection, to create robust and user-friendly Excel dashboards. It uses a sneak peek of the project dashboard to illustrate how data validation creates dropdown menus for standardized input, preventing errors. Sheet protection is demonstrated to lock cells and prevent accidental modifications by other users. The lesson also covers hiding sheets to simplify the user interface. A detailed walkthrough shows how to set up a data validation list from a dynamic sorted range, create a named range for easier formula referencing, and then protect a sheet to allow interaction only with specific unlocked cells, while also protecting the workbook structure.

This lesson guides participants through building their first Excel project: a Data Science Salary Dashboard. It combines all previously learned skills, from data validation to formula usage and charts. The dashboard is structured with dropdown menus for job title, country, and job type, dynamically updating associated graphs and KPI cards for median salary, top job platform, and job count. The lesson provides a step-by-step build, starting with setting up data validation for country and job type, then developing new queries with median salary calculations that integrate job title, country, and type filters. It concludes with an emphasis on applying dynamic formatting and basic cleanup of the created dashboard for better visual appeal.

This lesson explains various methods for sharing Excel projects, particularly the Data Science Salary Dashboard. It briefly touches on portfolio websites and video demonstrations. The main focus is on hosting the Excel file online using OneDrive for sharing. It demonstrates uploading the workbook to OneDrive and creating a shareable link that allows others to interact with the dashboard, even without a Microsoft account, thanks to Excel Online's capabilities. A workaround for the map chart not functioning in Excel Online is also provided.

This lesson introduces pivot tables, highlighting their power in automating repetitive data analysis compared to manual formulas. It demonstrates creating a pivot table from a raw dataset, explaining the fields pane (filters, rows, columns, values) and how to drag fields to aggregate data (e.g., counting job titles). The lesson covers customizing pivot table calculations (e.g., changing from sum to count in value field settings) and basic formatting. It also provides a quick overview of the 'PivotTable Analyze' tab, discussing options like refreshing data, changing data sources, and managing field lists.

This lesson delves into advanced pivot table features, using a large salary dataset. It explains hierarchy by dragging multiple fields into rows (e.g., job country and job title short), allowing for multi-level data expansion. Data sorting within hierarchies is also demonstrated. The lesson then covers automatic grouping for dates (e.g., grouping job posted dates by year, quarter, month) and manual grouping for categorical data (e.g., grouping job titles into 'Data Nerds', 'Senior Data Nerds', 'Other Data Nerds'). Finally, it explores showing values as percentages of grand total or parent total, offering deeper analytical insights into data distribution within groups.

This lesson focuses on creating interactive pivot charts and integrating slicers and timelines for dynamic data filtering. It demonstrates building a column chart to visualize average salary by job title, including sorting data. The power of slicers is introduced, allowing users to filter data based on categories like job group (e.g., 'Data Nerds') or country, making charts more interactive. Timelines are then shown for date-based filtering (e.g., filtering job trends by quarter or month). The lesson emphasizes connecting these interactive elements to multiple pivot tables/charts using 'Report Connections' to create comprehensive, linked dashboards.

This lesson introduces advanced data analysis using Excel add-ins, focusing on individual, non-sequential tasks. It starts by enabling essential Excel add-ins like Analysis ToolPak and Solver. The first practical application is forecasting, demonstrating how to use the built-in 'Forecast Sheet' feature to predict future job postings based on historical data, including visualizing trends and confidence intervals. The lesson then introduces 'What-If Analysis' tools: Scenario Manager for comparing multiple job offers under different input conditions, and Goal Seek for finding a specific input value to achieve a desired outcome.

This lesson delves into 'What-If Analysis' using data tables, focusing on one-input and two-input scenarios. For a one-input data table, it demonstrates how to analyze the impact of a single variable (e.g., annual raise percentage) on multiple outputs (e.g., yearly salaries and total salary over four years). The process involves setting up a table with input values and output formulas, then using the 'Data Table' tool. For a two-input data table, the lesson expands this to analyze the interaction of two variables (e.g., annual raise and bonus rate) on a single output (e.g., total four-year salary), illustrating how to set up the input cells and interpret the results. Conditional formatting is applied to enhance table readability.

This lesson explores the Analysis ToolPak add-in for advanced statistical analysis. It covers: **Descriptive Statistics:** Quickly generating key statistics (mean, median, mode, standard deviation, min, max) for a data column. **Histograms:** Creating more customizable histograms by defining specific bin ranges to control the X-axis and prevent visual clutter from outliers. **Rank and Percentile:** Calculating the rank and percentile for each value in a dataset, useful for identifying data points relative to others. **Moving Average:** Smoothing out time-series data (like job postings with weekly fluctuations) by applying a moving average to highlight underlying trends, demonstrating how to set the interval for the average and visualize it on a chart.

This lesson introduces Power Query, an incredibly powerful ETL (Extract, Transform, Load) tool within Excel. It explains Power Query's function: connecting to various data sources (databases, text files, web pages), transforming the data, and loading it into Excel. It highlights key benefits like automating repetitive tasks, ensuring reproducibility, and overcoming Excel's 1 million-row limit. A quick demonstration shows importing data from a Wikipedia page. The lesson then walks through importing a simple Excel file, creating a Power Query connection, loading data to an Excel table, and linking it to a pivot table. Finally, it demonstrates importing and combining data from multiple Excel files within a folder, emphasizing the efficiency of this process.

This lesson provides a comprehensive introduction to the Power Query Editor, the interface for transforming data. It covers the layout of the editor (ribbon tabs: Home, Transform, Add Column, View), the queries pane, the data preview area, the formula bar (showing M language code), and the 'Applied Steps' pane (for tracking transformations). The 'View' tab is explored for its EDA capabilities, including 'Column Quality' (errors, empty, valid data), 'Column Distribution' (distinct, unique values), and 'Column Profile' (statistics, value distribution). Basic Home tab transformations are demonstrated, such as changing data types (e.g., number to date/time), replacing values (e.g., 'Via' from job platforms), and renaming steps for clarity, emphasizing the non-destructive nature of Power Query transformations.

This lesson dives into advanced Power Query transformations, focusing on cleaning and analyzing skills data. It starts by adding an 'index column' to the main dataset, a best practice for fact tables. The concept of duplicating and referencing queries is explained, with referencing being preferred for cascading changes from the source query. The primary goal is to transform a messy 'job skills' column into a usable format: first, removing unwanted characters (brackets, quotes), then splitting the column by a delimiter (comma). The crucial 'Unpivot Columns' transformation is demonstrated to convert multiple skill columns into a single 'Job Skills' column, making it suitable for aggregation. The lesson addresses troubleshooting errors when renaming columns mid-query and finally demonstrates how to load the transformed skills data to create a pivot table/chart for visualizing top skills.

This lesson covers two critical Power Query operations: 'Append' and 'Merge'. It starts with 'Append', demonstrating how to combine multiple Excel sheets (representing monthly job data) into a single, comprehensive query. This highlights the efficiency of Power Query over manual copy-pasting. The lesson then shifts to 'Merge', the main focus, explaining how to combine two related queries ('data jobs salary' and 'data job skills') based on a common key (Job ID). All six types of merge operations (Left Outer, Right Outer, Inner, Left Anti, Right Anti, Full Outer) are explained with visual aids, emphasizing their impact on the resulting dataset size and content. The lesson concludes by using a 'Full Outer' merge to create a combined dataset for analyzing skill salaries, leading to a combo chart visualization comparing average salary and skill count for top skills.

This lesson introduces the M language, the scripting language behind Power Query. It explains that all transformations performed in the Power Query Editor are translated into M code, accessible via the 'Advanced Editor'. The structure of M code (let and in keywords, variables, comments) is broken down, emphasizing how each step references the previous one. While not aiming for M language expertise, the lesson aims to provide familiarity for minor edits and troubleshooting. It demonstrates creating custom columns using the 'Column from Example' feature, which automatically generates M code based on user input, showcasing its power in extracting specific text patterns (e.g., job schedule type, year, hour, cleaned job titles). It also shows how to copy and paste M code between queries for reusability.

This lesson introduces Power Pivot, focusing on its role in data modeling and its distinction from Power Query. It explains that Power Pivot is an add-in for data modeling, enabling the creation of relationships between tables and the use of Data Analysis Expressions (DAX) for advanced calculations (measures). The lesson demonstrates how to enable the Power Pivot add-in and briefly overviews its interface. It then covers importing data into the Power Pivot data model, emphasizing the importance of using Power Query for ETL and Power Pivot for modeling. The core concept of data modeling—defining relationships between tables (e.g., linking job ID in salary data to job ID in skills data)—is explained, and its impact on cross-table querying is illustrated with a pivot table showing skill counts per job title.

This lesson delves into the Power Pivot window, exploring measures, calculated columns, and date tables. It demonstrates creating an explicit measure (e.g., job count) using the AutoSum feature and defining a custom DAX measure (e.g., median salary) manually. The distinction between implicit (automatically generated) and explicit (user-defined) measures is highlighted, with explicit measures being favored for their reusability in complex calculations. The lesson then covers creating a calculated column within the data model (e.g., converting yearly salary to Euro values) and creating a date table with a single click, which automatically generates various date-related columns. Finally, it demonstrates linking the date table to the main data using relationships and analyzing job postings by day of the week using a pivot table and chart.

This lesson provides a deeper dive into DAX (Data Analysis Expressions), focusing on aggregation, statistical, and filter functions. It explains how DAX functions are similar to Excel formulas but offer more power for data modeling. The first application involves calculating 'skills per job' by creating explicit measures for 'skill count' (using DISTINCTCOUNT) and 'job count' (using DISTINCTCOUNT of job IDs), then dividing them using the DIVIDE function for robustness. The lesson then focuses on visualizing the correlation between median salary and skills per job using a scatter plot. The second major application involves using DAX filter functions, particularly CALCULATE, to create measures that enable comparing median salaries across different geographical contexts (e.g., US vs. non-US countries) within a pivot table while respecting or overriding slicer filters.

This lesson delves into advanced DAX, specifically focusing on filter and relationship functions to address data model limitations. It revisits the challenge of filtering across tables in Power Pivot due to one-way filtering relationships. The solution involves using the `CROSSFILTER` DAX function within a `CALCULATE` measure to enable bidirectional filtering between the 'data jobs salary' and 'data job skills' tables. This allows for accurate calculation of median salary per skill. The lesson demonstrates creating a new measure, `Median Salary Skills`, that correctly filters the median salary based on selected skills. It emphasizes the importance of understanding DAX relationships for effective cross-table analysis. Finally, the lesson updates the dashboard visualizations to reflect the improved salary calculations for skills and introduces the concept of 'skill likelihood' (percentage of jobs requiring a skill) as an alternative to raw skill count.

This lesson introduces arrays, a powerful Excel feature. It differentiates between modern dynamic arrays (which automatically spill results across cells) and classical arrays (requiring Ctrl+Shift+Enter for execution and manual range selection). The lesson then applies dynamic arrays to practical data analysis: first, identifying unique and sorted job titles from a dataset. Next, it tackles a more complex problem: calculating the median salary for each job title, demonstrating a step-by-step approach using IF, array multiplication, and MEDIAN functions to filter and process data with multiple conditions.

This lesson focuses on text functions, crucial for cleaning and manipulating text data, particularly in smaller datasets. It covers three main areas: text combination (using TEXTJOIN to merge multiple columns into a single address field), text extraction (using TEXTSPLIT to separate full names into first and last, and RIGHT to extract the last three digits of an ID), and text search (using FIND and MID functions together to extract state abbreviations from addresses). The lesson culminates in a data analysis to identify the most common skills among job applicants by combining these text functions.

This lesson covers essential date and time functions in Excel. It demonstrates extracting components of a date (MONTH, DAY, YEAR) and combining them using the DATE function. The TODAY function is introduced for getting the current date, and DATEIF for calculating the difference between two dates. For time, functions like HOUR, MINUTE, and SECOND are shown, highlighting the use of military time. The TEXT function is revisited to format dates and times (e.g., AM/PM format). The lesson concludes with an analysis of job posting frequency by hour of the day, using SEQUENCE and COUNTIF functions to visualize peak application times.

This lesson provides an introduction to various chart types in Excel and their applications in data visualization. It emphasizes that charts are powerful tools for identifying trends and patterns that might be missed in raw data. The lesson covers: **Line Charts:** Ideal for time-series data to show trends over time, with customization options for titles, axis labels, and trend lines. **Pie Charts:** Best for visualizing proportions of two categories, with tips on effective layout and color choices. **Column and Bar Charts:** Used for comparing multiple categories, explaining the preference for bar charts when category labels are long and demonstrating how to sort data for better readability and focus on top performers.

This lesson delves into advanced chart types and customization. It starts with scatter plots, demonstrating how to compare two numerical values (yearly vs. hourly median salaries for job titles). Customization includes adjusting axis bounds, number formatting, adding dynamic axis titles using cell references, and incorporating data labels with job titles. The lesson then moves to map charts, visualizing job counts and median salaries by country, highlighting geographical distribution. Advanced chart formatting options, like preventing charts from resizing with cells and using custom number formats for labels (e.g., 'K' for thousands), are also covered.

This lesson focuses on visualizing statistical distributions using histograms and box & whisker plots. It re-explores histograms to show salary distribution, emphasizing how to refine them for specific data subsets (e.g., data analyst jobs in the US) and customize bin sizes for better readability. The lesson then introduces box & whisker plots, explaining their components (median, quartiles, interquartile range, whiskers, outliers) and how they represent data distribution. It demonstrates building a box & whisker plot to compare salaries across different job titles, highlighting insights into salary ranges and outliers for various roles.

This lesson introduces sparklines, which are miniature charts embedded within single cells, providing quick visual summaries of data trends. It demonstrates creating column, line, and win/loss sparklines, with a focus on line charts for continuous data. Customization options for sparklines are explored, including changing colors and highlighting high/low points. The lesson illustrates how sparklines can offer immediate insights into monthly job counts for various titles, revealing trends like budget constraints towards the end of the year.

This lesson explores advanced spreadsheet features, starting with Excel tables. It demonstrates creating a table from a data range and highlights the benefits: automatic autofill for new columns, unique table-specific formulas for referencing data, and automatic filtering. Advanced formula usage within tables is shown (e.g., using SEARCH and ISNUMBER to identify skills in job postings). The lesson then covers adding a 'Totals Row' to tables for quick aggregations like averages, and introduces AGGREGATE function as a more flexible alternative to SUBTOTAL. Finally, it demonstrates using tables with slicers to create interactive dashboards for filtering large datasets.

This lesson covers various formatting techniques, with a strong focus on conditional formatting to dynamically highlight cells based on their values. It starts with basic cell formatting to enhance table readability (bolding, background colors, borders). The main part of the lesson delves into conditional formatting using data bars, color scales, and icon sets. It demonstrates how to apply these rules to highlight highs and lows within data, emphasizing best practices to avoid visual clutter. Detailed examples show how to create custom conditional formatting rules (e.g., highlighting salaries above a certain threshold) and manage multiple rules effectively.

This lesson initiates the process of sharing Project 2, the Data Science Salary Analysis, on GitHub. It emphasizes GitHub's utility beyond Excel, showcasing its use for SQL and Python projects. The instructor explains why GitHub is preferred over OneDrive for this project, as OneDrive (Excel Online) cannot support Power Query or Power Pivot features. The lesson outlines five steps: installing Git, setting up a GitHub account, installing GitHub Desktop, initializing a repository, and pushing the repository to GitHub. It clarifies that Git is a version control system and GitHub is a cloud-based hosting service for Git repositories. A step-by-step guide is provided for installing Git and setting up a basic GitHub profile.

This lesson concludes the process of sharing projects on GitHub and then on LinkedIn. It begins by explaining how to document project work using Markdown in a `README.md` file, which is crucial for detailing skills and insights. Markdown syntax for headings, bold/italic text, lists, code blocks, links, and images is demonstrated. The lesson then guides the creation of a comprehensive `README.md` for the main repository, linking to individual project `README.md` files (Projectile 1 and 2). It also covers capturing screenshots of dashboards and embedding them in Markdown. Finally, it provides instructions for sharing the completed GitHub repository on LinkedIn, emphasizing adding projects to the profile section and making engaging posts with images, links, and relevant skill tags.