MBA in Excel- Master Business Analytics in Excel

Get a preview of the modules in the MBA in Excel course, outlining how business analytics in Excel will be covered to build mastery.

Apply filters in Excel to drill down data, then use the subtotal formula to sum visible data and compare each product’s contribution to the grand total.

Learn how to remove duplicates in Excel by using the remove duplicates tool and advanced filter to extract unique month names from a dataset.

Explore sumif to calculate monthly totals for quantity and value, then compute average price, locking references with F4 and dragging formulas, and visualize trends with a line chart.

Master using sumifs for multiple criteria, filter unique entries with the advanced filter, and transpose results into a horizontal layout for a clean, formatted summary.

Master advanced sumifs in excel by building multi-criteria array formulas with ctrl+shift+enter, summing sales by product, region, and date.

Master the old but versatile sumproduct formula in Excel by multiplying and summing across rows, handling text with double negatives, and applying multiple criteria for dynamic sales analyses across dates.

Explore advanced count and sum techniques in Excel, including countif, sumproduct, large, and max, to analyze transactions, unique sales reps, top three 2019 sales, and January Dubai maximums.

Master advanced count and sum techniques in Excel, using maxifs, countifs, sumifs, and sumproduct to analyze monthly sales, returns, and regional data for 2019.

Learn to define named ranges with the name box, verify in name manager, and apply them in sumifs to total sales by month and sales rep.

Explore daily Excel shortcuts for formatting dates, borders, merging and centering cells, paste as values, and using advanced filter, zoom, and quick alignment.

Analyze sales data with conditional formatting to compare quarter-to-quarter performance, highlighting higher quarters in green and lower in red, using data validation and copy formatting across quarters.

Learn to apply conditional formatting in Excel to color dates by day of week, highlight sales above a threshold with formulas and data bars, and format positive and negative changes.

Format your data as an Excel table to enable dynamic updates, then use slicers and table tools to filter and prepare a dataset named sales data for pivot tables.

Explore how to use slicers in pivot tables to filter data by salesman, city, and category, and switch the report layout to tableau form for a cleaner, slicer-driven view.

Create multiple sales reports in Excel by using a pivot table with a salesman filter and show report filter pages to produce separate sheets per sales person, including running totals.

Explore the get pivot data feature in excel, build dynamic references to sales values by salesperson and month with f4, handle errors with iferror, and chart monthly trends by category.

Learn to use slicers and a date timeline in Excel pivot tables to filter by category and period, build a dynamic chart, and identify key contributors with running totals.

Record macros to automate pivot table dashboards, enable the developer tab, and create shape buttons to update charts and show top five sales reps, SKUs, categories, and customers.

Record and store multiple macros to automate pivot table analyses. Assign macros to shapes for quick use, covering top five sales reps, SKUs, categories, and customers.

Master advanced Excel formulas and nested logic to analyze sales data, using text formatting, sumproduct, sumifs, countifs, array formulas, named ranges, and advanced filter.

Explore excel lookup techniques using VLOOKUP and INDEX-MATCH to retrieve SKU names, barcodes, and sales values, handle text versus number formats, and validate results across sheets.

Learn to build dynamic lookups with offset and indirect, using match and named ranges to retrieve year-to-date sales across multiple sheets, with multi-criteria sumifs and sheet-name handling.

Master margin and selling price calculations in Excel, including gross profit and margin on selling price; explore weighted average cost, dynamic formulas, and scenario analysis with what-if and solver.

Create a dynamic sales dashboard that analyzes current period sales versus previous month and year, with category and channel breakdowns, currency comparisons, and auto-updating chart titles.

Convert your data into a table for dynamic updates and easier formulas, then build an end-user dashboard where date-driven charts auto-update, showing category, sales by period, and customer type comparisons.

Record a macro to remove unique dates starting from June 1, 2020 using an advanced filter, and paste results for calculating the previous month and the previous year sales.

Learn to create a dynamic date range in Excel by defining a named range with the offset function, counting the range height, and excluding non-date cells.

Build category totals in Excel using a sales data table and sumifs for current period, previous month, and year-ago values, then compute percentage change and category weight.

Calculate category totals by channel in excel with an IFS formula on a table named data, filtering handheld appliances by modern trade dates and comparing to the year-ago period.

Learn to use shapes with named ranges in Excel to display up or down indicators. Build dynamic visuals with an if statement, indirect function, and Name Manager.

Create a dynamic sales analysis header from start to end date using the text function, and illustrate current vs. year-ago performance with conditional formatting and a donut chart.

Update a macro-enabled dashboard by appending July data to a table, expanding the dataset, and sorting dates for a seamless macro run via the form control.

Record macros in Excel and compare absolute versus relative reference recording. Enable the developer tab and save macros in a workbook or the personal macro workbook.

Record and compare macros in absolute and relative mode in Excel, using Visual Basic Editor to capture actions, understand hard coded references, and store macros in the personal macro workbook.

Explore the range object in VBA, learn how to select and resize cells, loop through ranges with for each, and sum totals with offset and message box feedback.

Discover how to dynamically sum quantity and value in Excel with a VBA macro that locates headers and updates totals in the next blank cell.

Lock formulas using VBA by prompting a password with an input box, locking cells with formulas on the active sheet, and protecting the sheet for reuse via a personal workbook.

Record and edit VBA macros to extract unique salespeople with an advanced filter, then generate dynamic totals per salesperson using sumif, building a reusable summary report in Excel.

Record macros and write VBA to generate a monthly sales report using sumifs by salesman and month. Prepare data with advanced filter and dynamic ranges.

Learn to format an Excel report with color formats and borders, remove formulas by pasting values, and automate with a macro named format sales to create the report.

Design a dashboard with pivot tables, macros, and formulas to compare market shares across 2018–2020. Build a calculation sheet and a pivot table showing total sales by brand.

Extract unique brand names with an advanced filter, then use the GetPivotData function in a pivot table to compute shares, balances, and dynamic totals.

Plan and create six charts for brand shares, transforming percentages into pie charts, color them, and align them on the grid while editing data series from the calculations sheet.

Format the charts by merging cells, calculating and centering percentages from the calculation sheet, and adjusting fonts and outlines for a clean, bold company dashboard.

Link eight category shapes to sales data and update charts automatically when chips are clicked, using application color code to recolor shapes and pass the shape name to a macro.

Assign eight category shapes to a macro, implement a select case using color to turn the selected shape green and others black, updating formulas and charts.

Learn to apply and format slicers to compare category shares over time in a pivot table, including year and month filters and consistent styling.

Build a dynamic dashboard title in Excel by merging a header and concatenating fixed text with a cell reference to update with slicer selections.