Microsoft Excel DATA ANALYSIS Using my Proven 4-Step System

Apply the text to columns tool to split delimited data by commas, turning a messy list into individual cells for clean data and accurate calculations in Excel.

Add headers, adjust columns, and apply basic number formatting in Excel to convert dates, currency, and numbers into readable, analysis-ready data.

Remove duplicates in Excel to create a unique list of salespeople from employee IDs, paste as values, and ensure headers for building a side reference table.

Sort and clean your table to prepare for analysis, using headers, the data tab, and multi level sorting by written date, store number, year, and price.

Enhance data in Excel by using text formulas to combine make and model into a single column, introducing formulas and functions and preparing for data analysis.

Calculate sales commissions in Excel by multiplying price by commission rate, adjust for percent using divide by 100, and organize with notes for complex formulas.

Master absolute cell references in Excel by locking rows and columns with the dollar sign and F4, then copy formulas down to keep a fixed value like a commission rate.

Learn how to round numbers in Excel using the round function, apply two decimal places, remove formatting, and combine formulas for accurate commission calculations.

Explore how to build a sales commissions report in Excel using sum, count, average, and max functions, and name a range to simplify formulas.

Learn how to use the if function in Excel to determine warranty eligibility based on mileage, creating a yes or no column by testing whether miles are under 100000.

Learn to use the if error function in Excel to replace calculation errors with a clear message like 'missing price,' ensuring totals still sum correctly.

Use vlookup to add salespeople names by linking ID numbers from the salesperson table to the big table, performing an exact-match lookup with cell locking.

Use vlookup part 2 to add employee names to main table by naming the range salespeople and matching on the employee ID to pull back second column with exact match.

Learn to use index and match together for robust Excel lookups, overcome leftmost-value limitations, and pull names from an old table using a common employee ID.

Learn to use index match to bring salesperson names from an old table into a main table, naming ranges like old numbers and old names for easy references between sheets.

Create a dynamic pivot table from car sales data by dragging fields into rows, columns, values, and filters, generating a fast, formula-free, interactive report.

Switch value field settings from count to sum or average, rename to total sales dollars, and use slicers and a timeline for quick, presentable pivot table reports.

Combine all course lessons to import data, clean and format columns, enhance with formulas, and build a pivot table with slicers to analyze sales by car type and salesperson.