Data Analysis And Business Intelligence With Microsoft Excel

Explore power query as excel's etl tool to extract from csv, text files, databases, and other spreadsheets, transform data, and load into excel tables, with sql support and refreshable connections.

Transform external data in Excel with Power Query, adjust headers and data types to currency, remove unused columns, filter sales greater than 500000, and load to a worksheet.

Explore how Power Query applies steps to transform and refresh data in Excel, reuse and edit transformations, rename steps, and manage changes for reliable, repeatable data processing.

Learn to apply numerical transformations in Power Query, creating derived columns like commission from sale price, rounding values, and adding an index column to organize data for analysis.

Transform text data with Power Query by correcting misspellings, merging first and last names, splitting city and state, and formatting names to proper capitalization.

Explore date transformations in Power Query and build a calendar table in Excel with date components like day of week, week of year, month, year, and start of month.

Apply conditional logic in Power Query to create a derived one-zero flag for high value sales and categorize properties into large, medium, or small.

Merge datasets in power query by linking on a common id, pulling in additional fields, and choosing left outer or inner joins instead of VLOOKUP.

Discover how to append datasets in power query by stacking rows, ensuring identical columns and data types, and combining quarterly data for unified analysis in Excel.

Master power query in Excel by appending three call center CSV datasets, merging employee and call type lookups, filtering by date, and creating duration and wait time minutes.

Perform power query to split the site column into city and state using a comma plus space, derive month and year from timestamps, create a year-month, and load into Excel.

Learn to use pivot tables in Excel to quickly summarize large data sets, organize data in tabular form with headers, and analyze metrics like count, sum, and average across categories.

Create multilayered pivot tables from an Excel table to analyze calls by site, manager, and employee, and add layers with fields in rows and columns, including date-grouping options.

Learn to format and layout pivot tables in Excel, rename metrics, apply thousand separators, choose compact or outline layouts, and manage subtitles and grand totals for clear data storytelling.

Apply filters to pivot tables to narrow data by site, managers, or call type description, using report level and field filters, with outline and compact layouts.

Learn to transform pivot tables into a dynamic reporting system using slicers, enabling visual, multi-select filtering and single-filter control across multiple pivot tables for synchronized dashboards.

Add a helper field 'service level met' to mark wait times under 30 seconds. Summarize it as a percentage in the pivot table and refresh the pivot cache.

Load data in Excel via Power Query, choose load to table, create connection, or add to the data model, and use the data model's diagram view to link tables.

Discover how Excel's data model enables cross-table pivot analyses, storing large datasets and creating relationships to build pivot tables from multiple sources.

Learn how breaking data into separate tables reduces redundancy, using normalization, primary keys, and foreign keys to build one-to-many and one-to-one relationships in Excel data models.

Explore how the Excel data model implements one-to-many relationships, distinguishes lookup and data tables, and wires orders, order details, customers, states, regions, and products for Power Pivot analysis.

Connect multiple tables with one-to-many relationships in the excel data model to create power pivot tables, enabling multi-table summaries, filter context, and drill-down analysis.

Create and use a calendar table to enrich your data model with date attributes, establish a one-to-many link to orders, and build pivot analyses by day of week.

Explore power pivot pitfalls in the excel data model, focusing on how table relationships and filters flow downhill, and why many-to-many links break pivot calculations.

Learn to combine multiple data tables in Excel's data model using one-to-many relationships to create a single pivot table with cross-table filters.

Convert a date column into a calendar table with Power Query, then load as a data model connection to build a one-to-many model across customers, calls, orders, dates, and regions.

Build power pivot tables from the data model to count orders and calls by region and customer, applying primo customer as a filter.

Discover how DAX enhances the Excel data model by creating calculated columns for pivot tables, including concatenating names and converting durations to minutes for clearer insights.

Explore how the if function in DAX enables conditional logic to create calculated columns, using SLA and service level examples to demonstrate and or logic with multiple tests.

Learn the switch function as a readable alternative to nested ifs, building derived columns like quarter name and product category, using true function and inn operator for tests in Excel.

Use the related function to fetch values from the one side of a one-to-many relationship into the many side, enabling calculated columns in the orders table from the customers table.

Learn how measures in Excel pivot tables are defined in the data model, portable and reusable, and how explicit measures leverage DAX to deliver consistent, formatted results.

Explore DAX measures in a data model by counting rows, counting distinct values, and mastering the calculate function to build dependent measures like orders per day using divide and blank.

Discover how the calculate function enables conditional measures and cross-table analysis in power pivot, overrides pivot filters, and reveals loyalty club member revenue by region.

Explore the all function in DAX to remove filters and make measures immune to pivot table filters, enabling order count and percent of all regions calculations.

Learn to use DAX time intelligence with a date table to compute year-to-date revenue. Set up the calendar table and relationships, then build a year-to-date measure with calculate.

Use the isblank function in DAX to make the year-to-date revenue measure return blanks for months with no data in the pivot table.

Learn how the date add function shifts the filter context to compare measures across time frames, such as month over month and year over year, in a pivot table.

Explore the dates in period function to build rolling totals and three month moving averages in pivots, using max date, date add, and DAX measures for time intelligence.

Refactor DAX formulas with variables to replace intermediate measures, using VAR and return to simplify revenue two months prior and the three month moving average.

Refactor revenue in Excel by using sumx to compute unit price times order quantity row by row in a pivot, removing the line total column and highlighting iterator functions.

Learn to rank values in Power Pivot with the RankX function and the all function to override filter context, producing product profit rankings alongside a pivot table.

Master the top end function to filter the top five products by profit in Power Pivot measures, compare regional profits, and show the top five contribution as a percentage.

Customize pivot tables with techniques for handling empty cells, improving readability, and controlling layout, width, and captions, including switching to tabular layout and using conditional formatting.

Apply conditional formatting to pivot tables with data bars, color scales, and icon sets to visualize performance and thresholds across regions and states in Excel.

Master custom conditional formats in Excel by using number rules and formulas to highlight SLA under 75 percent in pivot tables, with multiple layered rules and stop-if-true controls.

Learn to create pivot charts in Excel to visualize regional SLA performance with bar charts, connect charts to a data model, and use slicers for dynamic, interactive insights.

Explore column charts to visualize time trends and category breakdowns, using pivot charts, year and quarter, legends, region slicers, and stacked column charts to show percent contributions.

Explore line charts to visualize measures over time and compare with stacked area charts, then use timelines as a date slicer to create dynamic dashboards.

Learn to plot multiple measures in Excel with a combo chart and a secondary axis to compare profit and items sold over time, using pivot charts and dynamic dashboards.

Learn to use a slicer to switch between measures in a pivot chart with a disconnected table, such as line items sold, profit, and revenue, while keeping the chart readable.

Convert pivot tables to formulas to create a tree map that shows product category to product hierarchy, controlled by a slicer and data model.

Walk through solution walkthroughs for data analysis exercises in excel, applying data bars, color scales, icon sets, and formula driven formatting for SLA and duration.

Create pivot charts from the data model, build a stacked area chart with a timeline to filter by year and quarter, and connect a second chart to share that timeline.

Create a combo chart that plots two metrics using two slicers, built with Power Query tables and DAX measures, enabling dynamic, dual-axis visualization.