Microsoft Excel Skill for Data Analyst

Master Excel autofill to quickly populate dates, weekdays, and years using drag and double-click, and copy down with or without formatting to work fast.

Explore how to use flash fill in Excel to automatically split data, detect patterns, and fill down with proper case or initials, noting it is not automated and requires reapplication.

Use the developer tab and visual basic to hide and unhide worksheets, including very hidden sheets, by customizing the ribbon and setting the visible property.

Explore advanced copy and paste techniques in Excel, including paste special options, formulas vs values, transpose with array formulas, and linked pictures that update with source data.

Master moving rows, columns, and cells with values or data in Excel using the move handle and shift-drag technique, while avoiding accidental replacements.

Identify the four Excel constructs—formulas, numbers, true/false, and cell references—and learn how to craft formulas and functions using sum and if with ranges.

Explore the sumifs function with multiple criteria ranges to sum revenue by city, weekday, year, and product, with Boston on Wednesday 2020 and January in the West region.

Master Excel's round, round up, and round down functions by learning how to set decimal places, reference cells, and understand how each function handles rounding and approximation in practice.

Understand how the Excel count if function counts text and numbers, compare it with count, and apply a "greater than 200" criterion and filter to verify results.

Master pro-level sumproduct techniques in excel to calculate revenue from quantity and price, perform multi-criteria analysis, and extend beyond sumifs and countifs.

Learn to use the ifs function in Excel to evaluate multiple logical tests, replacing nested ifs with a streamlined approach using score thresholds and grade outcomes.

Learn how to convert text to numbers in Excel using smart tag, isnumber and text functions, enabling charts and data cleaning across large datasets.

Learn to convert text to numbers in Excel using the value function, then paste values only with paste special to avoid formulas and errors.

Learn to apply conditional formatting in Excel to highlight top or bottom items, use color scales and data bars, and manage or clear rules across a worksheet.

Learn to select a data range quickly using shortcuts, avoiding slow scrolling; use ctrl+shift+down arrow, then ctrl+home, and press enter to finalize the calculation.

Explore how pivot tables in Microsoft Excel analyze data, summarize thousands of rows, and create interactive views by region and brand without writing formulas.

Master pivot table error handling by pulling values into a card with get pivot data and fix missing range references by enabling generate get pivot data.

Create a pivot table in Excel to count transactions over time, including sales reps and products. Set fields to count, arrange in rows and columns, and format grand totals.

Create a pivot table to display monthly revenue by product and region in Excel. Define the date as months, group by quarter, and format currency.

Learn to create a cross tabulated pivot table in Excel that shows revenue by month and quarter for each sales representative, using tabular layout, repeat labels, and currency formatting.

Learn to sort data in a pivot table by revenue across products and colors, from largest to smallest, format currency, and customize totals to reveal top sellers.

Use pivot table value filters to refine data by revenue ranges, applying greater than, less than, equals, less than or equal to, and include or exclude criteria with currency formatting.