Microsoft Excel Data Analytics & AI Masterclass: GPT Copilot

Sort data in Excel by last name with A-Z or Z-A, ensure headers, and use custom sort to order by last name, first name, and customer ID.

Learn to filter data in Excel by activating the filter tool, using dropdown arrows to select from unique values, and applying text and numeric filters to build compound criteria.

Create quick charts in Excel using the F11 shortcut. Switch chart types from column to line to pie, and compare sales by country, year, and month.

Learn to cleanse data in Excel by backing up, performing spell check, removing duplicates, adjusting case, trimming spaces, and merging or splitting columns for clean data.

Learn to cleanse data with Excel's remove duplicates tool, back up data, choose fields that define duplicates, and use COUNTIF to audit and decide which records to keep.

Change the case of names in Excel using upper, lower, and proper functions. Back up the file, then apply the formulas in a new column and paste values back.

Master data cleansing in Excel by replacing text using find and replace commands and replacement formulas. Save a backup, apply column-specific changes with case options.

Learn to remove non printing characters and leading or trailing spaces in excel data using clean, trim, and substitute, guided by ascii and char/code tools for accurate cleansing.

Identify numbers stored as text in Excel, convert them to numbers via multiply by one or the value function, then use paste special and format with dollar, fixed, or text.

Explore how excel handles dates as serial numbers, 1900 vs 1904 date systems, the two-digit year assumption, and converting between time values, decimals, and data using text functions and text-to-columns.

Master data cleansing in Excel by moving columns and rows, and transposing data with the transpose function or paste special transpose.

Explore how to create and use Excel tables to organize data, with features like header dropdowns, table tools, and named tables for referencing columns in formulas.

Manage rows and columns in a purple banding Excel table, add or insert rows at the end, delete rows and columns, and rely on auto expansion and calculated age.

Sort and filter table data with dropdown arrows, performing single or multi-level sorts by last name, city, and state, and applying text, number, and date filters to refine results.

Add a total row to an Excel table, choose per-column functions (sum, average, count), and use subtotal to exclude hidden rows when filtering.

Add a calculated column in an Excel table to derive initials from first and last names, using the left function and ampersand with the at-front column reference.

Learn to filter efficiently using data slicers in Excel tables, creating city and gender slices, working with floating slices, adjusting colors, stacking, and preserving filters across sessions.

Explore advanced filtering in Excel to perform or logic with a criteria sheet and the advanced filter dialog, noting that data slicer and filter dropdowns do not support or logic.

Link external data in Excel to tables not stored in the workbook, enabling filtering and sorting while protecting the raw data. Refresh from Access or SQL Server to pull updates.

Master value field settings in pivot tables to switch functions like sum, count, average, max, min, and product, and format numbers and headers accordingly.

Learn how to move a pivot table between worksheets using pivot table tools, selecting a new or existing sheet and the starting cell while preserving data linkage.

Use the pivot table report filter to constrain data by fields like sales rep and country. Drop fields into the filter, select single or multiple values, and observe the results.

Explore pivot table sorting and filtering using 2018 sales data; sort by country or by sales, reorder manually, and apply filters to highlight top or outlier countries.

Learn how pivot tables refresh manually or automatically, including refreshing on open, and how to create a macro with a keyboard shortcut (Ctrl+Shift+Q) to refresh all pivot tables quickly.

Explore pivot table styles in Excel by selecting from light, medium, and dark categories, and customize row headers, column headers, and banded rows or columns to format pivot tables.

Learn to filter pivot tables by adding a top-level filter, apply label or value filters, and use date filters to analyze sales data.

Explore additional pivot table options in Excel using sales data, including zeros for empty cells, error values, display options, classic layout, and accessibility via alt text.

Insert a data slicer into a pivot table to create floating filters, then connect it to multiple pivot tables and adjust colors for clear, interactive data filtering.

Connect Excel pivot tables to an external SQL server using a connection file with server name, user, and password to access a database view or table and refresh.

Create pivot tables from external data sources via ODC connection files, pulling data from databases like SQL Server or Oracle, with passwords stored in the ODC and refresh options available.

Learn to create charts from country and year sales data in Excel, adjust the axis scale or hide large values, and convert year numbers to labels for accurate time-based charts.

Explore how to create charts that auto update with new data in Excel, using dynamic ranges via tables or dynamic named ranges with the offset function.

Create forecast sheets in Excel 2016 to predict future sales, choosing line or column visuals and adjusting options like end date, confidence, seasonality, and missing data handling.

Plot page views and shares on a chart with a secondary axis to compare different scales. Learn to use column and line charts, adjust axis limits, and explore combo charts.

Create a pivot chart from your data to visually summarize sales by month, leveraging the pivot table and chart linked updates for interactive analysis.

Master pivot charts in Excel by selecting chart types, styles, and layouts, customizing colors and legends, and performing a manual refresh of the underlying pivot table data to reflect changes.

Explore get and transform, Excel's built-in power query, to pull, transform, and merge data from external sources. Connect to files, databases, and web data, then load in the query editor.

Connect to a Microsoft Access database and load the employees table in Power Query. Edit, rename, split, and remove columns, track steps, and load the final data into Excel.

Learn to group by data and create calculated columns in Excel using get and transform, including concatenating title and surname into a salutation, then load grouped results into sheets.

Explore Get and Transform to pull data from internet sources, connect to data feeds, and merge orders with order details to create a single, analysis-ready dataset for Excel.

Connect Excel to Google Sheets via get and transform, load the data with first-row headers, select needed columns, and refresh to update charts and pivot tables.

Learn to pull data from a web page into Excel with Power Query, select and load tables, and merge or refresh data to compare population, area, density, and median age.

Connect to a SQL Server database with Power Query, select views and tables, write SQL, add a line total, load to Excel, and refresh for updated pivot charts.

Use a 2D geographic heat map add-in for Excel from the Office store to visualize country or US state data in your sheet with gray scale or red-green color schemes.

Explore Excel's 3D map tools to create tours with scenes and layers, using color swatches and bar charts to display population and median age.

Learn to build a 3D map in Excel using population data, customize layers and colors, configure data cards, and manage scenes to present geographic insights.

Create and compare multiple data layers on a 3D map by adding scenes for population density and median age, then animate transitions between them.

Filter data in a 3d map scene using rank or median age with slider controls, create top 30 views, and duplicate scenes to demonstrate different filters.

Explore Power Pivot, an Excel add-in that connects to external data sources like Access and SQL Server, enabling large data sets and relationships beyond normal pivot tables.

Connect and shape data in Excel using Power Pivot by creating external connections, importing tables from Access or other sources, and applying previews and filters to build the data model.

Use Power Pivot to build PivotTables and PivotCharts from a data model, selecting tables and creating counts, averages, and gender breakdowns.

Explore creating measures and KPIs in Power Pivot to analyze the average age against a target of 50, using a data model and pivot tables with dynamic status indicators.