Microsoft Excel Skill for Data Analyst

Master data entry in Excel by creating headers, entering products, quantities, and prices, applying currency formats, using format painter, and calculating total revenue with basic formulas.

Enter and manage data efficiently in Excel using the built-in form, convert ranges to a table, and edit, search, and add records without VBA.

Demonstrates how to enforce accurate data entry in Excel using data validation, including date ranges, duplicate prevention with countif, gender dropdowns, and numeric constraints with messages.

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.

Discover how to add comments in Excel to explain formulas and cell context, edit or hide comments, and remove them, using hover and right-click actions to improve template clarity.

Customize the ribbon and quick access toolbar in Excel to add frequently used commands, create new tabs, and use the tell me search to streamline formatting, data validation, and charting.

Select multiple worksheets, hold control to add them, then right-click and hide. Learn how to unhide and explore an advanced method to hide without the active indicator.

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.

Navigate large Excel files by using freeze panes to lock the header row and the first column, keeping top row context as you scroll.

Learn how to work with a Microsoft Excel workbook, insert and move worksheets, understand workbooks, sheets, ranges, gridlines, and basic navigation and formula referencing.

Insert rows and columns in Excel by right-clicking, placing new columns between year and month and adding multiple columns, then locate blanks with find and select to delete carefully.

Master a keyboard shortcut to insert rows and columns in Excel with Ctrl+Shift+Plus. Select the number of rows or columns you want, then press the shortcut to insert them.

Learn to use split window in Excel to divide the screen into panes that scroll independently, enabling side-by-side comparison and quick hide and unhide of data.

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.

Learn to hide and unhide rows and columns in Excel using go to special to select visible cells, the ctrl+g shortcut, and right-click commands to reveal or hide data.

Learn to quickly adjust column and row widths in Excel by selecting all data, auto-fitting with a double-click, and applying a fixed or equal width across columns.

Master grouping in Excel by learning to hide and collapse rows and columns, create nested groups, and clear outlines to quickly reveal or conceal 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.

contrast Excel formulas and functions by computing the total products sold from 2022 to 2025, first with a formula, then with the sum function, using Ctrl+Enter to copy.

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.

Master relative cell referencing in Excel by building and copying a subtraction formula to track income minus expenses across months, as part of templates and dashboards.

Explore absolute cell references in excel using an if condition to identify sales reps meeting 50,000 benchmark. Learn to lock references with dollar signs to keep formulas correct when copying.

Learn mixed cell referencing in Excel, lock columns or rows with dollar signs, and use copy-down to build a multiplication table and compare with relative and absolute references.

Learn to create and use name ranges in Excel to simplify formulas, from selection or top row, and update or delete, applying countA, sum, and average.

Explore how to use the sum function in Excel and its alternatives to aggregate values, including average, count, subtotal, and the aggregate function, across a selected range.

Master sumif and sumifs in excel by using name ranges to create dynamic criteria, then calculate yearly, regional, and weekday quantities with copy-down efficiency.

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.

Explore how the average function computes the mean, how summing a column and dividing by the count yields the same result, and differentiate between the standard average and average a.

Compare average and averagea in Excel, showing how non-numeric values are handled: averagea treats text as zero, while average ignores non-numeric entries.

Learn how to use the max function to find the largest value and the large function to pull the top three values, with dynamic referencing and absolute cell locking.

Learn how the min function in Excel finds the smallest value in a data set, with examples like 28 and 12, and apply it to dashboards and reports.

Explore the difference between count and counta in excel, showing that count tallies numbers while counta includes text and dates; use minus one to ignore the header.

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 the countifs function in Excel to count transactions by multiple criteria, such as category, city, weekday, product, and quantity thresholds using named ranges.

Discover three methods to join text in Excel using formulas, the ampersand operator, and the concatenate function, with dynamic cell references and spacing tips.

Explore how the Excel index function retrieves data from a chosen row and column within an array, and combine it with match for dynamic, complex lookups across multiple columns.

Understand how the match function searches a lookup value, returns its relative position in a range, supports exact match (0), and enables dynamic lookups with index and data validation.

Compare index match with vlookup for complex lookups, showing left and right lookups and how column changes can break vlookup.

Explore how the vlookup and match functions search across worksheets and workbooks to join data and retrieve call type and employee details for efficient analysis.

Explore how to use vlookup with the match function to pull data from a table, applying mixed cell references and absolute vs relative locking for dynamic lookups.

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 advanced techniques to remove all spaces in text using substitute and trim, including instance-based removal, length checks, and handling large datasets.

Learn to use textjoin in Excel to replace concatenation, joining city, category, product, and quantity with custom delimiters while ignoring empty cells.

Learn how to use the offset function to build a dynamic, switchable revenue dashboard in Excel, creating a chart that updates by month with a combo box and absolute references.

Explore how the if function conducts a logical test to output results based on conditions, with examples of bonuses and absolute versus relative references.

Learn the nested if function in Excel to categorize scores into first class, credit, pass, and fail, including zero and not participated cases, with practical cell referencing.

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 to apply the if and and functions in Excel to assess vaccination status (first dose, second dose) and categorize employees as suspended, needing two days off, or cleared.

Explore how to use logical functions in Excel to evaluate multiple conditions with or and and operators, compose them via if statements, and apply conditional formatting to highlight true results.

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

Convert text to numbers in Excel by using the text to columns tool, selecting the data range, choosing the limited option, and finishing to convert text values into numbers.

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

Convert text to numbers in Excel by using paste special with add, selecting the data area, and applying the operation to convert, then create charts.

Master Excel alignment basics, from indent adjustments to centering, and distinguish numbers from text. Use shrink to fit, fit to screen, rotation for text orientation, and clear formatting.

Master center across selection and merge across cells in Excel, then set alignment to center text without affecting formulas.

Master how to justify long text in Excel by wrapping text, making the content fit with a double-click, and aligning cells for a clean look.

Format huge numbers in Excel using custom formats to display values as millions, billions, or K, with currency or accounting styles and options for negatives.

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 apply icon conditional formatting, from basic to advanced math, to highlight product sales differences between 2022 and 2023, manage rows, and create data bars.

Learn to transform data in Excel efficiently with Power Query, splitting columns by delimiter and loading the cleaned results. Split an address into city, state, and zip code.

Explore Excel ranges versus Excel tables for calculations and see how an official table updates totals automatically when new rows are added, and how to convert ranges with Ctrl+T.

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.

Compare manual calculations with built-in Excel functions to show efficiency and dynamic updating. See how using functions like sum avoids static results and keeps totals current as data changes.

Discover efficient calculation in Excel by avoiding hardcoded criteria, preventing self-reference issues, and using proper cell references so formulas copy correctly across columns and rows.

Create a dynamic Excel report by combining functions such as offset, index and match, large and mean to show highest and mean sales, with interactive region selection and dynamic charts.

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.

Prepare your data for pivot table creation by converting dates, turning data into a proper Excel table with headers, and using table styles and filters to ensure automatic updates.

Master pivot tables in Excel using the recommended pivot table to summarize revenue by region, sales reps, and product, and learn to open new worksheets for analysis.

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.

Master pivot tables to summarize quarterly revenue and units sold for cars, group data by month, quarter, and year, and analyze by weekdays with currency formatting.

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.

Create and analyze a pivot table to compare weekday unit sales across products and regions, using filters and slicers for months, quarters, and years to refine data.

Learn to build a monthly pivot table showing running totals by region using date fields and revenue, then format for currency and adjust subtotals and layout.

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.

Explore how to apply label filters in pivot tables, including contains, begins with, and date filtering, to refine movie dataset insights.

Learn how to hide selected items to filter a pivot table by right-clicking and using the filter option, keeping other items visible.

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.

Analyze top and bottom performing movies using pivot tables and filters to reveal gross revenue insights in Microsoft Excel.

Customize the pivot table filter list by moving and resizing the filter area, and switch between A to Z and data source order sorting for large datasets.

Learn to generate multiple region reports simultaneously in Excel by using a pivot table with region filters and per-region sheets, and format revenue as currency.

Double-click the red color in a pivot table to drill down and view transaction details, including revenue and units sold by brand, regions, and sales reps.

Apply advanced conditional formatting to pivot tables, adjust color scales, display units, and add slicers to make grand total data interactive and readable.

Master using the calculated field in a pivot table to perform advanced analysis, such as a 10% revenue increase and profit calculations.

learn to compute percentage differences in a pivot table by product across years, comparing 2016 to 2015, and using manual formulas versus pivot table options.

Learn to create custom date groupings in a pivot table by right-clicking a date, selecting group, and setting start and end dates to analyze revenue by five- and ten-day ranges.

Add a timeline to your pivot table to filter data by date ranges, switch between months and quarters, and use slicers to refine filters in your own project.

Add slicers to multiple pivot tables and control their connections across the worksheet. Learn to use report connections and filter connections to synchronize and disconnect as needed.

learn how to create multiple pivot tables in one worksheet without overlap by moving a pivot table to another sheet and planning layout for growth.