Master Excel analysis with BCG consultant secrets

Name: Master Excel analysis with BCG consultant secrets
Rating: 4.6 (61 reviews)

A no-nonsense crash course on Excel for consulting analysis, taught by an ex-BCG consultant and ex-Amazon PM

Created byLucas Lin

Last updated 6/2024

English

What you'll learn

Navigate quickly around Excel (without using a mouse)
Clean data in preparation for data analysis
Use dynamic formulas to conduct detailed data analysis
Use beautiful and best-in-class formatting
Structure your workbook to be clear and auditable

Course content

7 sections • 40 lectures • 1h 55m total length

How to use this course2:59
This gives an overview of the course structure. Download the accompanying workbook in the lecture resource folder and use it to follow along as we step through the course.

Shortcuts to quickly navigate a workbook2:51
Insert or delete rows, columns, and sheets6:03
Move and copy sheets2:30
Learn to move and copy sheets in Excel, using alt h m and keyboard shortcuts to insert a copy before sheet three, or move sheets for placement, then delete duplicates.
Split screen and freeze pane4:26
Open new window of the same Excel file2:18
Open a new window to view data side by side, creating a second instance of the same Excel file. Navigate between sheets in each window to compare data.
Enter and exit cell using F20:49

Insert filters, multiple sort6:11
Create tables2:57
Find and replace data1:54
IFERROR for calculations2:13
IFS for creating buckets in a new column4:04
LEFT RIGHT LEN SEARCH for manipulating text3:21
LEFT RIGHT LEN SEARCH for manipulating text (advanced)1:57
Combine left, right, length, and search formulas to clean data in Excel and extract the order number from a cell, illustrating how to skip steps by composing functions.
Cleaning dates1:09
Using Excel's Smart Autofill feature2:14
Discover how Excel's smart autofill can automate pattern-based data manipulation to save time. Use it for simple, predictable tasks, but always verify results for accuracy.

AVERAGE, MAX, MIN, MEDIAN1:07
Learn to compute average, max, min, and median in Excel by entering formulas, selecting the data array, and dragging formulas to analyze datasets.
PERCENTILES1:41
Explore percentiles in Excel analysis, comparing include and exclude options for the percentile formula, and see how 50th, 100th, and 0th percentiles relate to median, max, and mean.
COUNT, COUNTA, COUNTIFS5:25
SUMIFS, AVERAGEIFS1:48
SUMPRODUCT1:21
Use sumproduct in excel to multiply arrays of quantity and price, row by row, and sum the results to calculate total sales across regions.
Pivot tables6:27
Master pivot tables in Excel by creating a pivot table on a new worksheet, dragging region and category into rows and filters, and summarizing values by count, sum, or average.
Pivot tables vs. advanced COUNTIFS5:12
Compare pivot tables with advanced countifs and sumifs to analyze data, weighing quick insights against formatting flexibility.
Index match8:10

Format numbers quickly, set decimal points2:48
Master quick number formatting in Excel using control shift shortcuts 1, 4, 5 to display two decimals with thousand separators, dollars, or percentages, and alt h shortcuts to adjust decimals.
Using detailed formatting options in Ctr + 11:22
Conditional formatting2:28
Apply conditional formatting to highlight values with color scales, mapping high values to green and low values to red, and learn how to clear rules using keyboard shortcuts.
Align text, font color1:34
Master Excel analysis teaches quick alignment of order dates and center unit prices, and applying blue font color to totals using alt h shortcuts.
Adding cell borders1:55
Add clear borders in Excel with the alt h b shortcut, choosing all borders or outside borders to improve header readability.
Cell highlights0:37
Highlight the header row by coloring it light gray using alt h h h from home.
Use Paste Special to copy formats from another cell2:46
Clear formatting from cells1:50
Insert and delete comments from cells2:10
Set row width and column height, Use autofit3:37
Show and hide gridlines0:59
Color the tabs in your workbook0:47

Requirements

You should have a basic understanding of Excel or more to fully benefit from this course

Description

Do you want to become known as the 'Superstar Analyst' on your team? Want to deliver rapid analysis and insights, but unsure where to start with the various formulas? Still slowly clicking around in messy Excel workbooks?

Excel mastery is an upfront investment that will save you thousands of hours throughout your career. This was one of the best personal investments I ever made which was further honed as a BCG consultant and Amazon PM. I would like to pass on my key learnings.

Consultants, Business Analysts, Data Analysts, and Business Intelligence Engineers pride themselves on rapidly delivering beautiful, dynamic, and structured analysis. If you work in this field, mastering Excel is essential.

After this crash course, you will know exactly how to do that. We will walk through examples of all the important formulas a superstar analyst needs to know, and the little-known shortcuts to do it faster than anyone else.

By the end of this course, you will:

Master all the important formulas in Excel that matter, without getting overwhelmed by less important details
Know the best-practices used in consulting and tech business analysis
Understand when and how to use each formula - you will shine in delivering workbooks that are beautiful and insightful, not just correct
Note: This course does not include VBA, Statistical Analysis, or data links to other programs (e.g., Python, SQL)

Who this course is for:

This is a fast-paced, intermediate to advanced course. It is not recommended for those completely new to Excel

Master Excel analysis with BCG consultant secrets

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 3min

How to navigate quickly in Excel6 lectures • 19min

How to clean data in preparation for analysis9 lectures • 26min

How to analyze data8 lectures • 31min

Practice Assignment0

How to format your output12 lectures • 23min

Best practices for structuring your workbook4 lectures • 13min

Requirements

Description

Who this course is for: