Microsoft Excel Crash Course for Business Analysts

Master basic data entry and formatting in Excel by creating cells, entering data with tab navigation, applying wrap text, formatting currency, and aligning text and numbers.

Explore setting date data types, managing locale-based date formats, applying formats with the format painter, and creating date-time stamps via keyboard shortcuts.

Learn how cell references create dynamic links in Excel by using equals, selecting cells, and using arrow keys. Explore cross-tab references and why keeping data in one workbook helps reliability.

Learn arithmetic operators in Excel—addition, subtraction, multiplication, division, and exponentiation—and build a total sales column with plus, equals sign, fill handle, and F2 to inspect references.

Use the fill handle to autofill dates, the first of each month, days of the week, and incrementing numbers, and to copy the same formula across a table.

Learn relative, absolute, and mixed cell references in Excel, lock rows or columns with $, use the F4 shortcut and fill handle, and calculate percentage of sales reliably.

Learn to name cells and ranges in Excel, like total sales and sales table, then reference them across sheets using absolute references and the name manager to control scope.

Learn to speed up Excel work by using Alt shortcuts to access the ribbon, format numbers, align cells, and navigate data with control shift arrows.

Master font formatting, text alignment, and merging cells in Excel by applying bold, italics, underline, using merge and center or center across selection, and auto fit for width and height.

Master inserting and deleting cells, rows, and columns in Excel using shortcuts like shift+space, ctrl+shift+plus, ctrl+space, and ctrl+minus, and learn to move cells without disrupting headers.

Learn how to hide and unhide rows and columns in Excel to simplify a table without breaking calculations, using shortcuts like Ctrl+Space, Ctrl+- (delete), Ctrl+0 (hide), and Ctrl+9 (hide row).

Learn to use borders to visually separate data in Excel—bottom and top borders, and double underline values—plus apply fill colors to distinguish data categories and toggle grid lines.

Learn to visualize state population data with Excel conditional formatting, applying highlight rules, data bars, and icon sets to reveal magnitude and percent change trends.

Learn to insert and manage hyperlinks in Excel, linking to files, web pages, or specific locations within a workbook, including navigating to the conditional formatting tab.

Learn to protect a whole Excel workbook by encrypting with a password to prevent opening, safeguard the workbook structure, and apply sheet protections with customizable permissions and selective cell locking.

Use data validation in Excel to restrict inputs, create a lunch order dropdown from a predefined list, and enforce a 10-digit phone number and a 10:45 a.m.–2 p.m. time window.

Explore how Excel functions act as preset formulas that perform mathematical, statistical, or logical operations, enable quick data analysis, and look up data across cells or sheets. They include an equal sign, a function name, and required or optional arguments, and next learn the order of operations.

Learn the order of precedence in Excel, from parentheses to logical operators, using the Prem Damsel mnemonic; apply left-to-right rules for multiplication/division and addition/subtraction.

Learn to sum numbers in Excel using the sum function and auto sum, including inserting functions with fx, selecting ranges, and using shortcuts like alt equals.

Master core count functions in Excel: use count, counta, and countblank to identify numeric cells, non-empty cells, and blanks, with practical examples and quick workflow tips.

Calculate the average monthly sales by summing two months and dividing by count, then use Excel's average function and the AutoSum feature to compute and apply across ranges.

Use min and max to find the smallest and largest values. Dates are stored as serial numbers; exclude them from calculations and use auto sum for faster results.

Explore Excel's isblank, isnumber, istext, and iserror functions to test cell conditions and return true or false, with examples using blank cells, numbers, text, dates, and division by zero errors.

Explore booleans and logical operators in Excel to evaluate data with true or false values, using equals, not equals, and greater than or equal to, less than or equal to.

Master the if function and nested if with is blank to categorize grades and status (11th/12th, pass/fail, and did not finish) in excel.

Learn VLOOKUP, a vertical lookup, to fetch values from the right table using the leftmost column. Lock the table array, use false for range lookup, and copy results as values.

Learn to join cells with con cat, the newer concatenate, and split text with text split into columns or rows, then trim spaces and paste values to clean your data.

Learn to identify cells containing specific text with search, turn results into booleans using isnumber, and label electric or gas cars with if in Excel.

Explore how index and match work together to perform flexible lookups in Excel, including two-way lookups, multiple criteria, and keyword searches with exact match.

Learn how to use the freeze panes feature in Excel to keep headers visible while scrolling, freezing the top row, specific rows, or columns, and how to unfreeze when needed.

Master sorting in Excel to reveal insights by grouping items, dates, and amounts with A to Z, oldest to newest, and multilevel or custom sorts, plus filters and color sort.

Learn to apply filters in Excel, using the home tab, sort and filter, and keyboard shortcut Ctrl+Shift+L to show only matching rows, including text, date, number, and color filters.

Master essential Excel shortcuts for filtering and sorting large datasets: toggle filters with Ctrl+Shift+L, open with Alt Down, and use space, Enter, C, and E.

Create a helper column with an ID to capture the original row order, then sort by ID to revert to the original data; extend filters to new columns before sorting.

Define your analysis objective to guide data cleaning in Excel, then remove irrelevant data, fill or drop missing values, delete duplicates, remove outliers, and segment and tag data for analysis.

Remove blank rows in Excel by creating a helper column to count blanks, apply filters with control shift l, and delete rows with control minus for a clean data set.

Identify and delete rows with blank values in item or category by selecting the relevant columns, using find and select, then blanks, and finally deleting the entire rows.

Learn to fill blank cells in Excel by using go to special blanks, reference above with Ctrl+Enter, and multi-level sort by price and item to populate prices and sales amounts.

Highlight the data range, open the data tab, and remove duplicates to see how choosing item, item and date, or all columns changes which rows are removed, with undo using control z.

Use Excel's find feature in the home tab (ctrl f) to locate data with find next or find all, then replace (ctrl h) across the sheet, including replace all.

Learn how the substitute function cleans data by replacing text within a string, as demonstrated by shortening 'Mercedes-Benz' to 'Mercedes' and applying the fill handle for batch edits.

Label data by creating a key or dictionary that maps terms to labels, enabling accurate, consistent tagging for easy aggregation and actionable insights in Excel.

Learn to import CSV, TSV, and TXT files into Excel, set Excel as the default app, convert CSV to an Excel workbook, and use text to columns for delimiter-based splitting.

Discover how Excel pivot tables rearrange and aggregate data to create concise summaries, enabling quick insights by slicing and dicing across countries and years.

Create and customize pivot tables using the field list, with rows, columns, filters, and values. Explore aggregators such as sum and average and view subtotals and grand totals.

Insert a pivot table, add data to the data model, and set the value field to distinct count to reveal the unique country total.

Learn three methods to add data to a pivot table source in Excel, change the source range, insert within the data, or convert to an Excel table, then refresh.

Maintain clean source data for pivot tables. Use column headers, remove empty rows and columns, keep formatting simple, and place data on a raw tab in a rectangular layout.

Format dates and numbers in pivot tables by grouping and ungrouping date values, apply number formats, and use conditional formatting and color scales to clearly display production trends.

Sort pivot tables by value, country, and date, using largest to smallest or a to z, and filter rows, columns, and non-displayed data to focus on selected years and countries.

Learn to use calculated values in pivot tables to show percent of grand total, rank, and year-over-year changes, plus percent of parent row totals.

Create a calculated field in a pivot table to convert kilograms to pounds by multiplying production millions of kilograms by 2.2 and naming the result millions of pounds.

Insert slicers to visually filter pivot tables or regular tables by country, and use timelines to filter date values and adjust year ranges.

Convert the pivot table to tabular form for export. Turn off grand totals and subtotals, and repeat all item labels to create clean source data ready to paste.

Learn to communicate data clearly by keeping visualizations simple, telling a narrative, and choosing the right chart for your data type while tailoring visuals to your audience.

Create a time series chart in Excel from 2022 monthly search data for the keyword Christmas tree, using a line chart and noting seasonality in the data.

learn to create bar and column charts in excel to compare numerical data across categories and visualize time series with 2D column charts, stacked column charts, and 100% stacked options.

Learn to format charts with the format chart pane, using the element selector to adjust chart area, axes, legend, and series, including fill, line, tick marks, and axis bounds.

Use sparklines to embed small inline charts in a single cell, visually showing data trends within a range, by inserting sparklines and selecting a line option.

Learn how to visualize data with two-axis combo charts in Excel, combining clicks and CPC on different scales, and how to adjust axis type and add axis titles for clarity.

Create a line chart to visualize a time series, assign years to the horizontal axis, and add a linear trend line showing the Gini coefficient rising from 1990 to 2021.

Learn to create and customize 2D pie charts in Excel, display category names and percentages, and tailor chart titles and labels for carbon emissions by country in 2020.

Explore relationships using scatter plots in Excel by plotting MSRP versus acceleration for hybrid cars, label axes, adjust axis ranges, and add a least squares trend line for predictive modeling.

Learn to create an Excel bubble chart, a scatter plot with a third dimension from bubble size, and customize axes, legend, and data labels for clear comparisons.

Explore histograms to visualize the frequency distribution of data and compare them with Pareto charts, then learn to insert and customize both in Excel, including bin width.

Learn to create and interpret box and whisker plots in Excel, showing min, max, quartiles, median, and outliers for Las Vegas temperatures, with formatting tips for clarity.

Create heatmaps in Excel using conditional formatting to visualize data magnitude with color scales. See that September, July, and August have higher births per day while December shows the lowest.

Explore how to use Copilot inside Microsoft Excel to conduct data analysis with LLMs, boosting productivity amid the AI wave.

Download the start file called Start Excel Copilot, enable autosave, and format the data as a table starting at row 2 to enable Copilot for Excel.

Use Copilot in Excel to add a profit column from revenue minus costs, then build pivot tables to identify top stores by profit and compute profit per guest.

Explore a coffee production dataset with Copilot to create pivot tables and charts, showing total and average production by country and a line chart by year, with axis formatting.

Analyze digital marketing data in Excel with Copilot to compute max and min CPC; explore clicks trends and visualize with scatter plots and regression lines showing negative CPC–clicks correlation.

An introduction to analyzing the IMDb dataset in Excel with Copilot, using pivot tables and charts to identify top revenue directors, genre insights, and IMDb ratings.

Demonstrates solving an Excel assignment by turning data into a table, enabling autosave, and using Copilot to build pivot tables and charts that reveal top director revenue and genre performance.

Explore data analysis with generative AI and LLMs in this Excel crash course for business analysts, highlighting early-stage approaches and the growth potential of Copilot updates.

Explore the browser-based ChatGPT interface, including the chat bar and attachments. Learn about model options like GPT-4, Foro mini, and GPT-4 legacy, plus memory, custom instructions, and privacy controls.

Explore how to enable and use the chatgpt code interpreter to run python code for data tasks, view the analysis, and export results as csv.

Learn to clean a grocery sales ledger in Excel with the code interpreter, removing blanks, filling dates, deleting missing items, and calculating sales by multiplying quantity by price.

Explore data visualization with the code interpreter by plotting US housing price trends as a line chart, then apply moving averages, residual analysis, and interactive charts for forecasting insights.

Explore ecommerce data with a code interpreter to perform exploratory data analysis on climbing product keywords, brands, and monthly search volume, revealing top products and brands.

Learn how linear programming maximizes total revenue under constraints using decision variables for two products, t shirts and tank tops, with resources as limits on labor, material, and logos.

Turn a panoramic photo into a 9 to 16 mp4 video with code interpreter, panning from the right to the left using Image IO and frame step of eight pixels.

Perform regression analysis with the code interpreter to compute the correlation coefficient and regression line, plot a scatter with the line of best fit, and merge results with data.