Excel for Accounting & Finance: Fundamentals Masterclass

Learn to remove duplicates in Excel, even with large datasets, by selecting data, using remove duplicates, choosing which columns to check, and handling unique and non-unique cases.

Learn to clean data in Excel by removing extra spaces with trim, apply the formula across thousands of rows with Ctrl+Enter, and paste values to finalize.

Master spell check in Excel to clean data, using the spell check dialog, change all or ignore, and selectively fix column spellings while preserving brand names.

Learn to find data in Excel with ctrl+f, search across the sheet or a column, and replace values using replace or replace all to clean data.

Replace blank cells in large Excel datasets by using go to special, select blanks, and apply zero with Ctrl+Enter to fill missing values, streamlining data cleaning for millions of rows.

Learn to clean and format data in Excel by converting text to upper, lower, and proper case using UPPER, LOWER, and PROPER formulas, then efficiently fill across rows.

Learn how to extract substrings from data strings in Excel using left, right, and mid functions, flash fill, and text to columns, including dealing with emails and delimited data.

Merge data from multiple columns into one cell in Excel using concatenation with ampersand and spaces. Follow practical keyboard and mouse steps to join values.

Split a mashed column into multiple columns using Excel's text to column tool, choosing delimited or fixed width, and using spaces or other delimiters to align data.

Learn how to sort data in Excel, apply ascending or descending order, sort by multiple columns, and refine results within brands and products for organized datasets.

Master the transpose tool in Excel to switch data orientation from horizontal to vertical (and vice versa) by copying data, using paste special with values, and verifying the results.

Learn how to use Excel's flash fill to automatically extract first names, last names, and domains from email addresses by identifying data patterns, even across thousands of records.

Master vertical lookups with vlookup in Excel by pulling product names and prices from a separate sheet, locking ranges, and ensuring exact match for bulk data.

Master horizontal and vertical lookups in Excel using HLOOKUP and VLOOKUP to fetch price and cost data across sheets with exact-match formulas, locking, and replication.

Master index and match in Excel to retrieve names and prices from other sheets by an id, overcoming VLOOKUP limits and enabling exact lookups.

Master the consolidate function to merge data from multiple sheets into a single summary sheet, using labels, left column, and linked source data. See totals and breakdowns across subjects.

Append data from 12 monthly Excel files into one workbook using Power Query, load the combined data, and verify records and totals in Excel.

Separate date components—day, month, and year—from the date column in Excel, then extract quarter and day name to enable apples-to-apples data analysis.

Learn to extract the name of the day and the name of the month from dates in Excel using the text function with day and month formats.

Compute the day of the week with weekday(date, 2) to start on monday, and find the week of the year with weeknum(date, 2) for cross-year comparisons.

Calculate the day of the year in Excel with dynamic date formulas, avoid hard coding, and compute the day number from year, month, and date.

Learn to calculate the quarter of the year in Excel to compare apples with apples across years, accounting for seasonal variation by using month divided by three and rounding up.

Learn to create dynamic dates in Excel with the TODAY function, automatically updating today, tomorrow, and yesterday without hardcoding.

Calculate age from date of birth in Excel using the yearfrac function with actual days basis to handle leap years, then apply integer rounding for clean year values.

Learn to use Excel's if function to create a conditional column that classifies customers by age into young (≤40) or senior (>40) for streamlined data analysis.

Learn how to use nested if statements in Excel to classify ages into three categories—young, middle age, and old—by layering multiple logical tests.

Use the if or logic in Excel to classify card types into normal or priority, marking platinum and gold as priority and all other cards as normal.

Learn to build a multi column if statement using or to prioritize customers with gold or platinum cards, or income above seventy thousand dollars, and classify others as normal.

Explore advanced conditional logic in Excel by practicing a multi-condition if, or, and, not with customer data to determine free upgrades based on age, income, gender, children, and membership level.

Use the Excel IF with AND to classify customers as high or low potential when age is under 30 and income exceeds $60,000, applying multiple-condition logic across data.

Apply an if statement with multiple and conditions to determine a discount, based on age over 50, income under fifty thousand dollars, female, zero children, and bronze membership.

Discover AI-driven formula writing in Excel, including year, month, day, quarter, and day name extraction. Build calendar schedules with tables and conditional columns for weekend, half-month, and demand type.

Learn how AI enables Excel to write complex lookup formulas in plain English, replacing vlookup, xlookup, and index matches with an AI-aided editor and seamless blank handling.

Learn how to freeze panes in Excel to keep headers visible while scrolling, including freezing top rows, first columns, or multiple rows and columns for clearer data analysis.

Master filters and advanced filters in Excel to analyze data by brand, price, and text criteria, including multi-parameter filtering and date and year to date options.

Learn to use conditional formatting in Excel to highlight top and bottom values with color coding, data bars, and icons, and combine with filters for quick analysis.

Learn to apply Excel subtotals to summarize data by category, choosing sum, count, average, max, or min for columns like sales revenue, cost, and profit, with grand and category totals.

Master the Excel countif function to count records by brand or price criteria, using ranges, criteria, and cell references to reveal counts and data distribution.

Explore countifs to apply multiple conditions in Excel for data analysis, using a criteria range and a second criterion greater than 2 to filter brands by retail price.

Learn to use sumif to calculate revenue by brand, selecting criteria range and sum range in Excel. Apply hardcoded values and cell references to verify results.

Master multiple-condition summation with sumifs to total sales revenue when brand equals a value and profit margin is greater than 60 percent, including range locking and result verification.

Learn to analyze data in Excel by examining dates, brands, products, and customer demographics to identify best markets and to evaluate sales, cost, and profit margins.

Learn to analyze sales by country with a pivot table in Excel, placing country in rows and sales revenue in values, then sort, format, and expand to state and city.

Learn to add multiple parameters and drill down and up to analyze country, state, and city data with sorting for sales performance.

Break down total sales by year, quarter, and month using column fields. Drag the transaction date to columns to break data down and hide months or years as needed.

Filter pivot table data by gender to compare sales for female and male customers. Learn to filter by country or brand and discover why slicers are preferred over filters.

Explore value field settings in Excel to summarize data with sum, count, average, max, min, and standard deviation, and use show values as percentages of grand total or column total.

Master date handling in Excel by bringing in the transaction date, then group data by years, quarters, and months and drill down to see monthly details.

Demonstrate grouping by profit margin with five-percent bands (49-54, 54-59, 59-64, 64-69, 69-74) to summarize revenue without extra columns. See which margin bands drive the most revenue.

Compile and analyze data on one sheet with pivot tables, exploring country, state, brand, and customer type, then copy the pivot to a summary sheet for cross-sheet analysis.

Learn to create and configure slicers in Excel to filter pivot tables by country and brand, apply slicers across multiple tables via report connections, and analyze data interactively.

Use the timeline feature in Excel to filter data by time, choosing months, quarters, or years, and apply the filter across all related tables.

Learn to create dynamic calculated fields in pivot tables, compute profit as revenue minus cost, format results, and analyze by country, state, city, and brand.

Demonstrates why pivot table calculated fields outperform data-level calculations for profit margin and shows creating an average profit margin with a calculated field and why weighted averages matter.

Visualize data by creating charts from a pivot table and assemble a dashboard that summarizes information on a single screen for business managers to analyze and make useful decisions.

Explore advanced chart options by adding a profit and loss line, adjusting number format and decimals, and using state, country, and city breakdowns with component bar charts to enhance dashboards.

Compile country, state, and city data on a single dashboard sheet, then align charts and use slicers to filter all charts by country, state, and city.

Add a timeline slicer to a dashboard to filter sales data by time, enabling year, quarter, month, and day selections and updating all charts via report connections.

Add and adjust slicers to filter dashboard data by brand and product in Excel, customize slicer layout and columns, and connect slicers to multiple charts for synchronized updates.

Add trendline charts in Excel to compare sales by country, state, and city on a dashboard, using slicers to filter and reveal rising or falling trends.

Go beyond pivot tables with Power Pivot in Excel to analyze data across multiple tables and unlock advanced calculations, approaching Power BI.

Explore Power Pivot to analyze data from multiple tables via a data model. Use distinct count, a calculation pane, and a powerful formula language, and activate Power Pivot.

Activate Power Pivot in Excel via file options, add-ins, and COM add-ins by selecting Microsoft Power Pivot for Excel. Verify Excel version and note that Microsoft 365 supports Power Pivot.

Explore how relational databases store structured data across five tables—fact and dimensions—using primary and foreign keys to enable efficient, time-based analytics with Power Pivot in Excel.

Upload Excel data to the data model using get data to load tables from the workbook, then clean dates and sales values and begin data modeling to define table relationships.

Learn to build a data model by connecting tables through key relationships, using territory key, product key, channel key, and date, to enable pivot analysis across territories without lookups.

Deploy a Power Pivot table from the data model to analyze sales across five tables, using fields, filters, and slicers to compare by country, product, and sales channel.

Explore distinct count in Power Pivot to analyze product sales across regions, channels, and time, using pivot tables and the data model.

Master pivot tables in Excel to summarize data with sum, average, distinct count, running totals, and rankings, plus row and column percentage options. Learn DAX for Power Pivot calculations.