Data Analytics Bootcamp with Microsoft Excel and ChatGPT

Learn how to access Microsoft Excel or Google Sheets, evaluate the family 365 plan for six, and install on devices while noting duplicate removal features.

Learn how to create a blank Excel workbook, enable autosave to OneDrive, and understand cells, their locations (like b4, i12) and their values with simple examples.

Master Excel formatting by editing cells d3 and f5, applying bold, color, alignment, and wrap text. Use the format painter and undo with ctrl z, and explore online 365.

Explore data types in Excel, including integers, text (strings), decimals (floats), and booleans, and learn how to merge cells, align text, and format data for analysis.

Discover how to use conditional formatting in Excel to highlight scores above 30 or below 30, apply color scales and data bars, and identify top ten values.

Apply and customize Excel filters to sort data, filter by color, and set conditional criteria such as greater than 30.

Use Excel's remove duplicates feature to clean data by selecting the relevant columns, such as E and F, and removing found duplicates to keep unique values.

Learn how to use ChatGPT for Excel and data analytics, from signing up and prompting to solving problems with screenshots, conditional formatting, sorting, and filters.

Download the classic dataset, extract it, open it in Excel, save to cloud or desktop, and convert the data to a table with Ctrl+T or Cmd+T on Mac for filters.

Learn to sort data by value and color, apply conditional formatting, and use filters and tables in Excel to organize, highlight, and customize data views.

Discover how to split a single column into multiple columns in Excel using text to column, choosing delimited or fixed width methods with spaces, commas, or other delimiters.

Learn to use the flash fill feature in Excel, powered by AI, to automatically populate first and last names and to split data with text to column delimited by space.

Learn to distinguish null from blank values in Excel data, and apply strategies like deleting blanks or imputing with mean, mode, forward fill, or backward fill, with owner permission.

Learn to apply the Excel IF function for logical tests, determine pass/fail outcomes, and combine conditions with and/or not, illustrated by CS hackathon criteria.

Learn the basics of logical operators, including and, or, and end condition, with examples showing how all conditions must be true for and, and any single condition true for or.

Explore nested ifs and the ifs function to classify a credit limit into low, medium, and high using thresholds such as 50,000 and 100,000.

Use ChatGPT to write and debug Excel formulas, including not, and, or logic, with practical CSS branch and hackathon scenarios for data analytics.

Explore aggregate and conditional aggregate values in Excel, including sumif, sumifs, countif, countifs, averageif, averageifs, plus practical examples and ChatGPT guidance.

Use sumifs for multiple criteria in Excel, defining sum range, criteria ranges, and criteria; compare with sumif for single criteria in examples like Uber foods paid via UPI.

Master count functions in Excel, including count, countif, and countifs, to tally numbers, text, and blanks with single or multiple conditions, and use ChatGPT for help.

Explore basic text functions in Excel, including trim, length, upper, lower, proper, left, right, and concat, and learn to apply find, search, replace, and substitute for data cleaning and transformation.

Explore date and time functions in Excel, using today, now, year, month, day, and text to format names and extract values, including net working days and edate concepts.

Explore numeric functions in Excel, including round, ceiling, floor, power, square root, absolute, and mod, then use if to identify even and odd numbers.

Master the lookup function in Excel, which searches a value in one range and returns a matching result from another range, with cross-sheet use, grades and salaries, and absolute references.

Learn how to use the vlookup function to retrieve data from a vertical table by employee number, including table setup, column index, and exact match. Also discusses left-to-right limits.

Discover Xlookup, a simple and powerful Excel function that searches data in any direction, overcoming VLOOKUP's left-to-right limitation. Specify lookup value, lookup array, and return array to retrieve results.

Discover Power Query Editor, an ETL tool that extracts data from sources, transforms and loads it into Excel. Learn how to use applied steps, transform data, and load results efficiently.

Load data from the web into Excel using Get Data and load to a worksheet via Power Query editor, with multiple tables and refresh options.

Import data from a folder into Excel using get data from folder, transform in Power Query, create and rename queries for each table, and perform basic cleanup like header promotion.

Learn how to clean and transform data in Power Query Editor, handle nulls and inconsistencies, merge and enrich datasets, compute costs, sales, profits, and discounts for reliable loading into Excel.

Open the data in Excel’s Power Query editor and explore merge queries, learning inner, left, right, full outer, left anti, and right anti joins with examples.

Learn to use conditional column and custom column in Power Query Editor to categorize order quantities as low, medium, or high with if-then-else, and generate M language code using ChatGPT.

Learn to clean and load data by handling null ship dates, computing date differences (required date minus order date), and creating custom columns to derive days and rounded results.

Activate pivot tables in Excel by enabling developer, adding Power Pivot, and building a data model to visualize and analyze data through interactive fields.

Explore star and snowflake schemes for data modeling, using a single fact with multiple dimensions. Build relationships with keys like product code and customer number, and apply pivot table workflows.

Explore cardinality concepts in data relationships, including one-to-one, one-to-many, many-to-one, and many-to-many, with practical examples using product codes, offices, and sales data.

Learn to create and customize pivot tables in Excel using the data model, ranges, and fields; explore rows, columns, values, sorting, filtering, and top/bottom analyses.

Explore pivot tables in Excel to analyze sales with sum of sales and percentage of grand total, and use field list, filters, and year and month data.

Discover chart types in Excel and their use cases. Create and customize bar, line, pie, donut, treemap, and scatter charts from pivot tables with design options.

Explore how to use slicers and timeline in Excel to filter charts and pivot tables, connect slicers across multiple charts, and customize slicer formatting.

Connect shapes to pivot tables in Excel to create interactive, slicer-driven data visuals and dynamic charts, ensuring consistent formatting and enabling cross-filtered insights across sales data.

Compare direct formulas, M query editor, and Dex for calculations in Excel and Power BI, and master measures creation in PowerPivot with pivot tables and slicers.

Create interactive buttons in a spreadsheet by inserting shapes and icons, link them with right-click to other sheets like the customer dashboard, and style with effects for a clickable look.

Create an interactive Excel dashboard using pivot tables and charts to show sales by product lines, top five by sales and orders, and customer-driven insights with slicers and report connections.