Excel for Data Analysis: Ultimate Guide

Navigate the Excel menus from home to view to customize formatting, insert records and charts, and protect workbooks, while using the formula bar to see cell contents.

Navigate around a worksheet with the arrow keys, select data across a row or a column using ctrl+shift+right or ctrl+shift+down, and jump with page down/up and ctrl+n or ctrl+home.

Learn to jump to a specific cell with go to (Ctrl+G) and use find and replace in Excel, including sheet versus workbook scope for precise replacements.

Master tab management in Excel by renaming, adding, deleting, duplicating, and copying tabs, and organize workbooks with hide/unhide and color-coding.

Master essential Excel shortcut keys for data entry, including F2 to activate a cell, Esc to exit, and Ctrl Z, Ctrl D, Ctrl R for undo and fill.

Explore essential Excel shortcuts by category to navigate the worksheet, select cells and rows, and copy, paste, and paste special options for formats, values, or formulas.

Excel for data analysis: ultimate guide walks through an exercise on formatting cells, paste special options, range multiplication, and filling down or right using keyboard shortcuts.

Enter and view data in a hands-on lab for Excel data analysis. Complete the manual data entry exercise using copy-paste to fill months and values.

Learn a quick Excel trick to copy and fill data by dragging the fill handle, auto populating months, groceries, and repeated values across a table.

Learn to apply and remove freeze pane in Excel to keep headers visible while scrolling, including freezing the top row for easier data navigation.

Explore quick text formatting shortcuts to italicize, bold, and underline text using ctrl i, ctrl b, and ctrl u in Excel.

Master formatting with Excel's TEXT function to convert cells to text using various formats, including leading zeros for eight-digit numbers, currency, dates, and phone numbers.

Master custom formatting in Excel by concatenating two or more columns with the ampersand operator, inserting spaces via double quotes, and preparing SQL-ready lists with quoted values.

Learn data cleaning in Excel by checking spelling with the review tool, then identify duplicates using conditional formatting and remove duplicates to clean the data.

Learn to remove empty rows in a data table by selecting the range, using go to special to find blanks, and deleting entire rows to keep records with complete values.

Apply Excel's lower, upper, and proper functions to clean and format text, converting strings to lowercase, uppercase, or properly capitalized forms.

Learn how to clean text in Excel using the trim function to remove irregular whitespace, standardize records, and apply the formula across cells for data analysis.

learn how to use the find and search functions to locate the position of a pattern within text in Excel, with find being case sensitive and search not case sensitive.

Learn to apply left, right, and mid functions in Excel to extract characters from text strings using start positions and lengths, with practical exercises featuring Amazon and Ikea.

Explore the substitute function to replace old text with new text in a cell, choosing where to reference or hard-code values, with an example replacing pie with cheesecake.

Learn how the value function converts text representations of numbers into numbers in Excel. Apply =value with a cell reference to convert text to numbers and observe leading zeros behavior.

Master concatenate in Excel by building a two-column join with the colon separator, composing the formula, and applying it across cells to produce a single concatenated column.

Learn data cleaning in excel with chip sales dataset: format dates, fill missing values, convert to proper nouns, split product names into description and weight, concatenate custom id.

Learn to craft complete SQL statements using advanced text formatting, operators, and concatenation to count records across tables and produce a two-column result with table name and count.

Learn how to perform addition, subtraction, division, and multiplication in Excel using cell references, and explore hard coded, relative, and absolute data references.

Identify hard coded values—text, numbers, and dates—and format them. Relative references change when copied; absolute references stay fixed for sales and tax calculations.

Master the basic functions in the excel for data analysis lab, including sum, count, min, max, avg, and median, and practice input of cell ranges.

Explore date-related functions in Excel, including yearfrac, networkdays, and weekday, to compute durations, count working days with holidays, and apply absolute references.

Explore how to use Excel logical functions—IF, nested IF, AND, OR, NOT, and IFERROR—to classify scores, evaluate multiple conditions, handle errors, and create pass/fail indicators.

Learn to perform advanced conditional calculations with single criteria using sumif, averageif, and countif, from defining ranges and criteria to fixing absolute references for reliable copying.

Learn to use sumifs, countifs, and averageifs to compute total, count, and average electricity usage by condo unit and month.

Explore practical Excel techniques for data analysis, solving six problems from age and working days calculations to product demand, expenditure buckets, drug cost markup, savings, and multi-criteria averages.

Learn to use vlookup by mastering its four inputs: lookup value, table array, column index, and range lookup; identify exact or approximate matches and the importance of the first column.

Master horizontal lookups with hlookup, using an exact match (false) and a row index, comparing with vlookup, and applying it to region sales data.

Master index and match for advanced lookups in Excel, including nested index and match, exact-match lookups, and retrieving rows, columns, and data like sales revenue.

Define quantitative and qualitative variables with examples like temperature and height versus gender and eye colors, to ground basic statistics for data analysis in Excel.

Identify the difference between population and sample. Learn how a random, representative subset of the population supports valid inferences in statistical studies.

Explore sampling strategies such as simple random, stratified random, and systematic sampling with proportional selection, and learn why sampling saves time while providing estimates of population mean and standard deviation.

Explore central tendency by computing mean, median, and mode from lemonade sales data with Excel formulas, illustrated by a histogram and discussion of ties.

Explore measure of variability by defining range, standard deviation, and variance, and compute them with built-in formulas to compare two ROI portfolios and identify the safer, higher-return option.

Explore hypothesis testing with null and alternative hypotheses. Learn to apply two-tailed and one-tailed t-tests, anova, and experiment design; set significance levels and interpret type one and type two errors.

Compare means across two or more groups with t-tests and anova, and learn the null and alternative hypotheses. See examples of one-sample, two-sample, paired, and two-way anova.

Master t tests with up to two groups, covering unpaired and paired designs, and one- versus two-tailed hypotheses, through practical calorie, height, and training examples.

Compare whiteness scores across toothpaste brands using one-way ANOVA for more-than-two groups, and learn data formats for Analysis Toolpak and two-way ANOVA with replication for detergent types and temperatures.

Explore linear relationships via correlation and regression, interpret the Pearson coefficient (-1 to 1) and its magnitude and sign, and test null hypotheses for r and beta1.

Compute the Pearson correlation coefficient and R-squared value from a data set of x (years of experience) and y (salary), then derive the linear regression slope and intercept.

Learn to activate the Excel analysis Toolpak add-in and access it from the Data tab’s Data Analysis menu, using File options, add-ins, and manage steps.

Perform an unpaired t-test using Excel's Analysis Toolpak, selecting two-sample unequal variances, defining variable ranges with headers, and a 0.05 significance level; interpret the p-value to conclude mean differences.

Perform a paired t-test in Excel with Analysis Toolpak, compare score before and after, assume zero mean difference, and reject the null with a significant p-value; then learn one-way ANOVA.

Learn to perform a one-way ANOVA in Excel with Analysis ToolPak, including input and output ranges. Interpret the p-value against 0.05 to determine if brand differences are significant.

Learn to run linear regression using analysis toolpak to examine the relationship between years of experience and salary, interpreting intercept, coefficients, p-values, and adjusted r-squared.

Learn to run a one-sample t-test in Excel using a dummy variable and the Analysis Toolpak. Interpret p-values to assess whether mean energy bar calories differ from 200.

Run two-factor ANOVA with replication using the Analysis Toolpak to test detergent brand and water temperature effects on dirt removal, interpret p-values, and compare replication scenarios.

Select the D19 to g29 range, convert it to a table with a header, then name the table (for example tbl_1) in table design for easy access.

Learn to filter and sort a table using the column header and the sort button to order by customer ID (A–Z) and furniture category (Z–A), then apply or clear filters.

Create a pivot table from a sample device payment dataset in Excel, place it on an existing or new worksheet, and decide whether to use a data model.

Create and manipulate pivot tables in Excel to summarize data by region and age, apply sorting, switch sums to counts, and format currency and accounting values.

Learn to use a slicer and a timeline feature to enhance a pivot table, add slicers for severity and brand name, and filter data to a focused final view.

Learn to build a pivot table with a claim ID count, insert a timeline for service date, filter to 2019, and control slicer and timeline connections for a single table.

Create a pivot table from sample data, then insert a pivot chart with a column layout, using worker region as rows and total paid as currency-formatted values with data labels.

In this lab, create a cluster bar chart from a pivot table of worker age groups and pay, adjust axes and labels, and note when bars suit many categories.

Build a line chart from a pivot table to show service date versus service units, then customize the chart with markers, axis titles, and data labels.

Create an Excel pie chart from a pivot table, customize data labels as percentages, adjust legend placement and font size, and title it hearing aid claim count by brand name.

Learn data visualization basics and 2d exploratory data analysis, applying the exploratory and explanatory goals framework from asking interesting questions to communicating results with graphical integrity and telling a story.

Maintain graphical integrity with zero-based y-axes and proper scales, minimize clutter via data-ink ratio, avoid 3d visuals and unnecessary decoration to tell the data clearly.

Master best practices for effective visuals in tables and charts, including muted backgrounds, proper font size, whitespace, borders, and chart selection from column, line, area, to pie charts.

Learn a quick framework for building an effective dashboard by clarifying purpose, goals, audience, and key business questions and KPIs through structured wireframing.

Design an executive dashboard for restaurant sales analysis by exploring dashboard wireframing, a requirement canvas, KPIs like GMV and CSat customer satisfaction score, data sources, and guiding visualizations.

Learn dashboard layout principles: place key visuals in the top-left sweet spot, using columnar, vertical matrix, and horizontal matrix designs for KPI and story-driven executive dashboards.

Design a wireframe for an Uber Eats order analytics dashboard using the provided data. Analyze monthly spend, regional orders, and popular items to inform marketing and service improvements.

Learn to build a dashboard by formatting Uber Eats data as a named table, adding it to the data model, and creating a pivot table on a working sheet.

Create visuals from the dashboard wireframe by adding measures with DAX expressions in pivot tables, and build KPI charts to analyze spend, orders, items, and restaurants.

This lab guides finalizing a data dashboard by arranging KPIs, charts, and tables into an interactive, user-friendly interface with slicers for year, quarter, restaurant, and item.

Learn Power Query, a data transformation engine in Excel, to connect sources, clean, transform, and automate refreshing tasks, plus filtering, sorting, and grouping.

Learn to load data from Excel tables—transactions, products, states, and categories—into Power Query, rename tables, and create connections to the data model for efficient loading.

Merge multiple tables in Power Query using primary key-foreign key relationships, perform inner joins and expand columns, and assemble a final dataset for loading into the data model.

Master data cleaning in Power Query by correcting data types for the transaction ID and date columns, applying trimming and deduplication, and rearranging columns for readability.

Create calculated columns using Power Query M code, including custom columns for year, quarter, and revenue, applying if-else logic and text formatting for year-month.

Learn to merge two tables in Power Query using primary and foreign keys, and explore left outer, inner, and right anti joins on employee and store data loaded as connections.

Apply Power Query to concatenate a unique list of products purchased per customer ID, using text combine and group by to produce a per customer product list.

Learn basic KPI aggregation in Power Query to compute total quantity and total revenue by customer, and total data set metrics, with a distinct customer count using List.Distinct.

Explore PowerPivot as an Excel data modeling and analysis tool. Create data models, define relationships across tables, and perform advanced calculations with DAX on large data sets.

Build a Power Pivot data model from four tables and establish relationships: product to transaction on product code; category to product on category code; state to transaction on state code.

Learn to create calculated columns in Power Pivot with DAX, pulling product, category, state, and unit price into the transaction table via established relationships.

Learn to build a Power Pivot data model and create calculated measures with DAX, using two methods: direct DAX in a table and the measure window for efficient management.

Explore power pivot and DAX to build pivot tables from a data model, analyzing sales by channel, category, and state, and create calculated measures.

Build a capstone project 1 personal finance budget dashboard in Excel, covering Power Query ETL, transaction data appends, spend categorization by description, DNA model, and DAX expressions.

Build an Excel finance budgeting dashboard that tracks plan versus actual incomes and expenditures, analyzes month-over-month spend by category, and highlights savings using Power Query, data model, and DAX.

Learn to load data with Power Query from a folder of csv files, split into checking account and credit card queries, and build a dropdown year-month interface for the capstone.

Use Power Query to append two data sources, create year, month, and quarter columns, and load to the data model, then apply Excel formulas for spend category.

Learn to classify spend from transaction descriptions using an Excel if formula with sumproduct, search, and double negation, transitioning from Power Query to a calculated column; preparing for PowerPivot model.

Build a data model from the data tab, create DAX measures with PowerPivot, and design a pivot table dashboard to analyze transactions, income, expenses, and savings.

Explore capstone dashboard wireframes in Excel for data analysis, featuring automated and manual entry views, yearly and monthly perspectives, pivot tables, power query, and cash payment tracking.

Explore building and refining pivot tables for a monthly dashboard in Excel, including measures like total income and expenditure, percentage calculations, top-five filtering, and slicer-driven month views.

Set up a pivot table and chart for spend category view, configuring filters, top ten by total expenditure, and slicer connections across dashboard views.

Create a manual budget tracker in Excel using data validation for year and month, enter cash transactions, and visualize plan vs actual with charts and a final dashboard.

Learn to build a yearly Excel dashboard by moving slicers and tables, creating pivot charts, kpi cards, and sparklines to visualize income, saving, and expenditure.

Create a monthly view dashboard by configuring slicers, building income, expense, and KPI panels, implementing IF and VLOOKUP formulas, and visualizing trends with a donut chart and conditional formatting.

Create an excel spend category dashboard that shows the top ten transactions by category, with year and month slicers, using conditional formulas and pivot table sorting to safeguard data quality.

Refine the capstone dashboard by cleaning up hidden sheets and wireframes, review the yearly and monthly financial visuals, and fix a pivot table error by using income.

This capstone project two guide builds a work performance scorecard in Excel, using a solution file, a template, DAX expressions, a data model, and Power Pivot to measure SLA.

Celebrate completing modules one through seven in Excel for data analysis and chart your next steps. Practice with real datasets; expand SQL, Power BI, and Python or R for analysis.