Master Excel analysis with BCG consultant secrets

Learn to insert and delete rows, columns, and sheets in Excel using ctrl and minus, ctrl shift plus, and alt shortcuts, with tips on undo limitations.

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.

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.

Learn how to apply filters and multiple levels of sorting in Excel to clean data, including shortcuts to toggle filters, and manage dates, categories, and quantities.

Use find and replace with control h to change 'carrot' to 'carrot two' across workbook or a sheet, choosing to match entire cell contents or any cell and update formulas.

Learn how to use the iferror function to handle calculations that fail, show an alternative value or blank, and convert euro prices to dollars by multiplying by 1.1.

Master Excel text manipulation using left, right, length, and search to extract the first two letters of a region, measure character counts, and locate spaces for data cleaning.

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.

Identify the year, month, and day from a single date column and reconstruct complete dates using the date function in Excel, enabling precise data analysis.

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.

Learn to compute average, max, min, and median in Excel by entering formulas, selecting the data array, and dragging formulas to analyze datasets.

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.

Learn basic Excel data crunching with count, counta, and countifs to drive insights, using absolute and relative references, locking formulas with F4, and dynamic drag-down for regional criteria like East.

Learn how to use sumifs and averageifs to analyze regional sales in excel, summing quantities sold in the east and west regions and calculating average prices by region.

Use sumproduct in excel to multiply arrays of quantity and price, row by row, and sum the results to calculate total sales across regions.

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.

Compare pivot tables with advanced countifs and sumifs to analyze data, weighing quick insights against formatting flexibility.

Pull specific rows and columns from a large data set using index match, focusing on the quantity and unit price while applying exact matches and dynamic lookups.

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.

Open the format cells dialog with ctrl+1 to access detailed formatting options, including numbers, alignment, font color and size, borders, fill color, and cell protection.

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.

Master Excel analysis teaches quick alignment of order dates and center unit prices, and applying blue font color to totals using alt h shortcuts.

Add clear borders in Excel with the alt h b shortcut, choosing all borders or outside borders to improve header readability.

Highlight the header row by coloring it light gray using alt h h h from home.

Master paste special to copy formats from another cell, saving time by applying the original cell's formats to others using control alt shortcuts and the formats option.

Master excel analysis by using the alt h e shortcut to clear formatting from cells, choosing between clear formats and clear contents, and resetting to Arial.

Adjust column width and row height in Excel using shortcuts like alt h o w and alt h o h, plus auto fit to optimize sizing.

Learn to hide gridlines in Excel to present cleaner analysis outputs, a common consulting practice. Use the shortcut Alt W, then VG, and press B then G to remove gridlines.

Learn how to color worksheet tabs using the alt h l t shortcut to access the colors menu, helping you visually distinguish sheet types in your workbook.

Structure your Excel workbook with separate raw data, clean data, and analysis sheets to keep edits isolated and track your workflow. Use color coding to distinguish sections and improve navigation.

Learn to structure Excel analysis sheets with separate tables for data cuts, such as region metrics and employee type, while including data sources, methodology, and key takeaways.

Use dynamic formulas to create flexible data analyses that adapt to changing inputs and header definitions, updating results automatically when you reorder columns or sort business types.

Color code your analysis output numbers by value type to reveal data origins and enable auditing, using blue for hard-coded data, black for formulas, and green for values from sheets.