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.

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 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.

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 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.

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 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.

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.

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 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.

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.

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.

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.

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.

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.

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.

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 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.

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.

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.

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.