The Complete Excel Guide with AI Integration

Back on the Dashboard of the Personal Budget Tracker, recap SUM and read every part of =SUM(Transaction!D3:D15), from the sheet name and exclamation mark to the range. You then calculate the average expense with the AVERAGE function, leaving the income entry out of the range so the mean reflects only your spending.

Make the biggest expenses in the Personal Budget Tracker stand out automatically with Conditional Formatting. You tour its menu of Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales and Icon Sets, then apply a Greater Than rule that marks every amount above 1200 with Light Red Fill so problem expenses show at a glance.

Build a Monthly Expense Summary on the Dashboard of the Personal Budget Tracker with a Category and Total layout. You copy the category names, link single entries with simple cell references, add up multiple entries with SUM and Ctrl-click selections, style the headers with Format Painter, and format the totals as Indian Rupee currency.

Turn the category totals of the Personal Budget Tracker into a pie chart. You explore Recommended Charts and the main chart types, insert a 2-D Pie, control Chart Elements such as Data Labels, the Legend and the Chart Title, show percentages instead of values, try Chart Design styles and the chart filter, and title it Total expense category.

Watch compound interest at work by building a Mutual Fund SIP Growth Calculator. You enter the monthly investment, rate of return and duration, fill a 20-year timeline, calculate total investment and year-end value with absolute references, compare the result with the FV function, and plot the growth as a line chart.

Convert the Dunder Michael Paper Company employee database into an Excel table with Insert > Table or Format as Table. You pick and customize Table Styles, set Table Design options such as Banded Rows and the Total Row, total the Salary column with AutoSum, and see new rows join the table and its totals automatically.

Sort the Dunder Michael Paper Company employee dataset from the filter drop-down: A to Z for Employee ID, Smallest to Largest for salary and Oldest to Newest for Date Hired. You set up a multi-level Custom Sort with Add Level, see why whole rows move together, check that dates are real dates, and return to the original order with Undo.

Filter the Dunder Michael Paper Company employee table so only the rows you need stay visible. You use AutoFilter checkboxes for branches, salaries and status, Text Filters such as Equals and Contains with an OR condition, and a Between Number Filter for a salary range, then clear each filter so no rows stay hidden by mistake.

Clean a messy Raw Data sheet in the Dunder Michael Employee Database. You remove extra spaces with TRIM, turn formulas into plain values with Paste Special (Ctrl + Alt + V), try UPPER and LOWER, and fix typos, double @@ symbols, stray dashes and inconsistent casing with Find and Replace (Ctrl + H), limiting it to one column when needed.

Duplicate records cost money, so here you find and delete them in the Dunder Michael Employee Database. You highlight repeated Employee IDs with Conditional Formatting and Duplicate Values, use Remove Duplicates with Expand the selection to delete whole rows instead of single cells, and remove chosen duplicates by hand through a table filter.

Split full names into First Name and Last Name columns in the Dunder Michael database with Flash Fill. You insert two new columns, type one example and let Ctrl + E fill in every first name, then run Flash Fill from the Fill menu on the Home tab for the last names, checking the pattern-based results as you go.

Split data by a strict rule with Text to Columns on the Dunder Michael employee list. You walk through the Convert Text to Columns Wizard, choose Delimited with Space to separate full names, use Treat consecutive delimiters as one and the Data Preview, then take on a challenge: split the Email column at the @ symbol to pull out the domain.

Keep your headers in sight while scrolling the Dunder Michael database with Freeze Panes on the View tab. You use Freeze Top Row so the column headers stay at the top, Freeze First Column so the left column stays put while you scroll sideways, and Unfreeze Panes to release each lock when you are done.

Prepare the Dunder Michael database for paper from the Print screen (Ctrl + P) and its Print Preview. You choose a printer, print the active sheet, a page range or the entire workbook, then set Narrow Margins, Portrait or Landscape orientation, Paper Size, copies and collation, and use Fit Sheet on One Page scaling.

Build a small School_DB dataset from a blank workbook, styling the header row and using the Fill Handle to fill the Student ID and Pass Mark columns. Then write your first logical test, comparing Marks against Pass Mark with the > symbol, and watch Excel return TRUE or FALSE for every student.

Turn TRUE and FALSE tests into readable results with the IF function on the School_DB dataset. You will add a Result column, fill in logical_test, value_if_true and value_if_false to return pass or fail, wrap text in double quotes, and copy the formula down the whole class with the Fill Handle.

Step into the role of a sales analyst on the Sales_DB sheet and use IF formulas to label every record. You will build a Target Status column that returns Achieved or Missed against the Quota and a Deal Value column that tags deals as High Value or Standard, and see how the @ structured reference keeps Table formulas consistent.

Handle more than two outcomes by placing one IF inside another. Working on the sales dataset, you will build a Customer Value Tier column that turns ratings into Premium, Regular or Low Value Customer, then a Target Achievement Level column that flags Achieved, Near Target at 90% of the Quota, or Underperformed.

Replace long chains of nested IFs with the IFS function, which tests conditions in order and stops at the first TRUE. You will write a Sales Performance (IFS) column from Top Performance down to Low with TRUE as the catch-all, solve a Deal Priority Level challenge, and freeze the header row with Freeze Panes.

Combine IF with AND and OR to apply business rules that check several conditions at once. On the sales dataset you will flag Bonus Eligibility (AND), build a High Performance Flag that excludes the West region with the <> operator, raise a Risk Alert (OR) for Poor ratings or small deals, and mark Follow-Up Required.

Summarize the Sales_DB data with SUM, first over a range like F2:F36 and then over a structured Table reference that keeps up as rows are added. You will total the first 10 rows, combine SUM with FILTER to total the Quota for the East region only, and start counting numeric entries with COUNT.

Finish the COUNT challenge for the Quota and Date entries, then move on to COUNTA to count non-blank cells, including text. You will count the salesperson entries, combine COUNTA with UNIQUE to find how many distinct products the dataset holds, and run a quick completeness check on the Target Status column.

Answer targeted questions with SUMIF and COUNTIF instead of filtering by hand. You will total sales for SmartOffice Printer Pro, for Excellent-rated customers and for the East region, fix a misaligned range that quietly returns the wrong total, and count Excellent ratings, deals above 500 and East region deals.

Stack multiple conditions with SUMIFS and COUNTIFS. You will total Ecolite LED Bulb Pack sales in the East region, North region sales over 1,000 and Excellent-rated deals above 1,500, then count East region sales over 500, Excellent ratings in the West, and February sales using a date range as two criteria.

Find the right function even when you cannot remember its name, beyond what Formula AutoComplete suggests. You will open Insert Function on the Formulas tab, search for average and count to compare related functions such as AVERAGEIF and COUNTIFS, and build an UPPER formula in the Function Arguments window with its live preview.

Work with dates as real data using TODAY, MONTH and NOW. You will calculate the days since a sale, extract the month and display it as January with a custom mmmm number format, add 10 days to create a follow-up date, insert a live timestamp, and combine IF with MONTH to flag January sales.

Put the whole section to the test in a fifteen-question challenge on the sales dataset. Try the questions yourself first, then follow the solutions with COUNTIF, SUMIF, IF, nested IF, COUNTIFS, SUMIFS, IF with OR and IFS, finishing by adding two SUMIF results together to total the Excellent and Good customers.

Join fragmented employee data from the Dunder Michael Paper Company starter file. You will combine first and last names with CONCAT and the & operator, layer an Employee ID, a dash and the full name together, and use TEXTJOIN with a delimiter and ignore_empty_text to merge names, department, manager and location into one string.

Clean messy text in the Dunder Michael Paper Company employee data. You will switch casing with UPPER, LOWER and PROPER, strip leading and trailing spaces from Job Title and Email with TRIM, nest TRIM and PROPER to fix the Location column in one step, and lock in the results with Paste Special Values using Ctrl+Alt+V.

Locate and swap text with formulas instead of manual edits. You will use FIND to return the position of a letter in a job title, wrap it in IFERROR to show NA instead of errors, compare it with the case-insensitive SEARCH, and use SUBSTITUTE to turn every Accountant into Accountant Pro in the Dunder Michael Paper Company data.

Split combined city and state data into separate columns with the Text to Columns wizard. You will choose Delimited, set the comma as the separator, see what Treat consecutive delimiters as one does and pick a destination, then rename the new City and State columns and fix their header formatting so the Dunder Michael table stays intact.

Prevent messy entries before they happen with Data Validation. You will restrict the Department column to an approved list from the Validation Lists sheet, turn on the in-cell dropdown, watch Excel block an entry that is not on the list, and remove the validation from extra cells you selected by mistake.

This Ninja Challenge hands you a messy employee dataset and seven tasks to solve on your own. Standardize the name casing, split Full Name into first and last names, clean the department text, rebuild the emails with CONCAT, unify the location names, add a location drop-down from the validation list sheet, and fix inconsistent employee IDs.

Walk through the answers to the Ninja Challenge step by step. You will nest TRIM and PROPER to clean names and locations, split Full Name with Text to Columns, rebuild emails with CONCAT wrapped in LOWER, paste results back with Paste Special Values, add Data Validation drop-down lists to four columns, and standardize the employee IDs.

Meet the Marketing Engagement Dashboard you will build step by step from a full year of campaign data across eight channels. Learn what data visualization is and how to choose the right chart: column charts to compare categories, line charts for trends, pie charts for parts of a whole, plus combo charts, sparklines and modern chart types.

Build your first column chart to compare Sessions across marketing channels. You will select only the Channel and Sessions columns, insert a 2D Column chart from the Insert tab, change colors and styles with the paintbrush, filter data with the funnel icon, and explore Chart Elements such as Axis Titles, Data Labels and the Legend.

Track signups month by month with a line chart. Using the Marketing Engagement Dataset, you will select just the Month and Signups columns with Ctrl, insert a 2-D Line chart from the Insert tab, try preset chart styles with the paintbrush, and toggle Chart Elements such as Axis Titles to keep the trend easy to read.

Show how total signups are divided across marketing channels with a pie chart. You will select the Channel and Signups columns, insert a 2-D Pie, try chart styles and color palettes, drag a slice outward to create an exploded pie chart that highlights one channel, and use Chart Filters to show or hide channels.

Plot Revenue and Signups together in one visual with a combo chart. On the Combo_Chart sheet you will select Month, Revenue and Signups, insert a preset that shows Revenue as columns and Signups as a line on a secondary axis, and learn when a secondary axis helps your audience and when it confuses them.

Turn a default chart into a polished, readable one. Starting from a Clustered Bar Chart picked in Recommended Charts, you will adjust Gap Width and Series Overlap in Format Data Series, try shadow and 3-D bevel effects, toggle Chart Elements, rename the chart title, and change the fill, pattern and border options.

Add tiny charts inside cells with sparklines. Using quarterly email click data on the Sparklines worksheet, you will create Line, Column and Win/Loss sparklines from the Insert tab, set the Data Range and Location Range, copy them down with the fill handle, and learn where sparklines fall short of a full chart.

Build three specialized charts that show exactly where a problem sits. You will create a Funnel chart to reveal stage-by-stage drop-off, a Pareto chart that pairs bars with a cumulative line to apply the 80/20 rule to support incidents, and a Waterfall chart that explains the changes in Monthly Recurring Revenue.

Explore statistical and hierarchical charts for the Marketing Engagement Dashboard. You will build a Histogram and a Box and Whisker chart to compare session lengths across the Prospect, Lead, Customer and Loyal segments, then a Sunburst and a Treemap to show revenue by region, country and channel.

Get to know the Sales Plus Performance dataset, a sales diary where every row is one order. Walk through its columns, from Order ID, Region, State and City to Sales Channel, Customer Segment, Product Category, Net Sales, Gross Profit, Order Month and Order Quarter, and see what a PivotTable does with data like this.

Clean a messy sales sheet before it reaches a PivotTable. Use Find and Replace to standardize the Region column, TRIM to strip trailing spaces from State and PROPER to fix City capitalization, paste the helper columns back as values with Paste Special, then remove empty rows and convert the range into an Excel Table.

Build your first PivotTable from the cleaned sales table. Select the data, go to Insert, choose PivotTable From Table/Range, keep New Worksheet with Table1 as the range, and get to know the empty PivotTable layout and the PivotTable Fields pane you will work with from here on.

Answer a first business question: what are Net Sales by Region? Drag fields into the Rows and Values areas, change the calculation in Value Field Settings, add State under Region to drill down, and place Sales Channel in Columns to split each region's total by channel.

Build two more PivotTables on a Dashboard_Data sheet with Existing Worksheet: one comparing the Sum of Net Sales and Total Cost for each Product Category, one summarizing Net Sales by Order Date. Expand the automatic years, quarters and months, remove levels to change the view, and add City to drill into a region.

Add clickable filters to a PivotTable so it shows only the slice you need. Open the field list, drag State, City and Region into the Filters area, then filter the Product Category summary to Arizona and to the West region and switch back to All, without touching the source data.

Turn PivotTables into PivotCharts for the Sales Plus Performance Intelligence Dashboard. Create a Clustered Column Chart of Net Sales by Product Category, move it to a Dashboard Charts sheet and filter it by city, then build a Line Chart of monthly Net Sales with a chart title and axis titles.

Give a PivotTable of Net Sales by region and product category a finished look with the Design tab. Set Subtotals and Grand Totals, switch between Compact and Outline Form layouts, add Banded Rows and Columns, apply a PivotTable Style, and use Value Field Settings to show values as percentages and in millions with a custom number format.

Learn why PivotTables do not update on their own when the source data changes. Add a new row to the sales table, see the dashboard stay unchanged, then use Refresh on a single PivotTable and Refresh All on the PivotTable Analyze tab to bring every PivotTable in the workbook up to date.

Put your PivotTable skills to the test in a Ninja Challenge built on the Sales Data sheet. Download the problem statement and solve three tasks on your own: total sales by region, sales by region and product category, and a monthly trend analysis.

Meet the PeopleInsight HR Analytics workbook, where employee information, engagement survey data, compensation data and lookup sheets for departments and locations live on separate tabs. See why related data spread across sheets calls for lookup functions instead of copying and pasting.

Use VLOOKUP to pull Department Names and HRBP Names from the Lookup_Department sheet into Employees_Raw, with DepartmentID as the common reference. Set the column index number and FALSE for an exact match, fix errors with absolute references using F4, and see why inserting a column can break the formula.

Bring City, State, Region and WorkArrangement from the Lookup_Location sheet into Employees_Raw with XLOOKUP, using LocationCode as the lookup value. Lock the lookup and return ranges with absolute references, return N/A when a value is not found, use 0 for an exact match, and see why XLOOKUP keeps working when columns are inserted.

See what happens when a DepartmentID no longer matches the Lookup_Department table and VLOOKUP starts returning errors. Wrap the existing formulas in IFERROR to show a readable message like Not Matched instead of #N/A, and learn why XLOOKUP does not need IFERROR thanks to its built-in error display.

Learn how INDEX returns a value from a row and column position and how MATCH returns the position of a value in a list, using the Lookup_Location sheet. Then rename the tables in Table Design and combine INDEX and MATCH with structured references to pull HRBP Name from Lookup_Department into Employees_Raw.

Combine first and last names into a Full Name column in the PeopleInsight employee data. Build the same result three ways, with CONCAT, the ampersand operator and TEXTJOIN with a space delimiter, then copy the formula down the whole column with the fill handle.

Break a position code like DAL-SAL-0001 into its parts. Use LEFT to extract the location, RIGHT to pull the four-digit ID and MID to take the department code from the middle, fill the formulas down the Employees_Raw sheet, then turn the data into a structured Excel table with Format as Table.

Build a small task sheet and work with dates. Apply a custom d-m-yyyy date format, count the days until a deadline with TODAY, return the end of the deadline month with EOMONTH, and calculate working days with NETWORKDAYS, fixing the errors that appear when Excel reads a date as text.

Extract distinct lists from the Employees_Raw sheet with the UNIQUE dynamic array function. See why the formula fails inside a table, where it has no room to spill, then build spilled lists of unique employment statuses, location codes and work arrangements outside the table.

Filter the Employees_Raw table with the FILTER function instead of the filter button. Return employees by location code and by job level, then combine conditions with the asterisk for AND and the plus sign for OR to list Active employees at job level 5 and employees in NYC or SFO.

Organize employee data with formulas that update as the data changes. Use SORT to order the Employees_Raw table by First Name, nest UNIQUE inside SORT for a clean alphabetical list of job titles, and use SORTBY to sort employee columns by Hire Date without displaying that column.

Take on a Ninja Challenge with four tasks on an orders sheet. Use XLOOKUP to bring in product names and prices from the products sheet, calculate totals by multiplying price by quantity, and build formatted order IDs with text concatenation, then compare your work with the solution walkthrough.

Get to know the TaskFlow project tracker you will build step by step. Explore the starter workbook's raw task data, with Task ID, Project, Assigned To, Start and Due Dates, Status, Priority and Progress, the Dropdown List sheet, and a preview of the finished interactive task tracker dashboard.

Design the TaskFlow layout before adding any automation. Highlight the header row with a fill color, use Freeze Panes to keep the top row and first column in view, add a new task to test the structure, format Progress as a percentage, and see why this tracker stays a normal range instead of a table.

Stop messy entries like HIGH or urgent pls with Data Validation. Create a Priority drop-down list by typing High, Medium, Low as the source, then apply list validation to whole columns linked to the Dropdown List sheet for Priority and Status, so every update in the TaskFlow tracker stays consistent.

Make the TaskFlow tracker flag what needs attention with Conditional Formatting. Write formula rules with AND and locked column references to highlight High priority tasks that are not Completed and High priority tasks under 50% progress, refine them in Manage Rules, and add a rule for Blocked tasks.

Turn task dates into a Gantt-style timeline. Build a Stacked Bar chart from Task Name, Start Date and the planned work days, hide the start-date bars with No Fill, show the tasks in reverse order, and set the date axis Minimum bound and Major Unit so the project timeline reads clearly.

Make progress and priority readable at a glance. Add solid green Data Bars to the Progress column, build a Priority Score helper column with a nested IF that turns High, Medium and Low into 3, 2 and 1, then apply an indicator Icon Set so urgent tasks stand out in the TaskFlow tracker.

Add time tracking to the TaskFlow tracker. Calculate Work Days Planned with NETWORKDAYS, combine IF, TODAY and NETWORKDAYS to show Work Days Left, and compute Days Past Due with an IF formula that leaves on-time tasks blank, so the sheet shows which deadlines have already slipped.

Protect the TaskFlow tracker from accidental edits. Learn what the Locked and Hidden options in Format Cells do, use Protect Sheet with an optional password to block changes, unprotect it again, and use Protect Workbook to lock the structure so sheets cannot be added, renamed or moved.

Stress-test the finished TaskFlow tracker with fresh data. Add task T-258 by copying an existing row, change its Start and Due Dates, and check that the drop-down lists, conditional formatting, work-day formulas and Days Past Due all recalculate correctly for the new task.

Get a clear picture of what Copilot in Excel, ChatGPT and Claude each do well, what they cost and where they fall short. You will try Analyze Data on the Data tab, open Copilot from the Home tab with App Skills, and prompt a five-bullet summary of revenue, profit, top region, top product and anomalies, then review it before trusting it.

Copilot gives better answers when the data is clean, so you first check the Sales_Clean table: clear headers, no merged cells, Currency and Percentage formats. Then you test Copilot with Chat and App Skills prompts that summarize the columns, sort by date, filter blank values and insert quick totals on a new sheet, undoing any change you do not want.

Turn a sales table into a five-bullet executive summary with Copilot Chat, using a precise prompt that asks for key metrics, the top region and products, the sales trend and data quality concerns. You then ask Copilot to insert formulas and suggest formula columns, and learn to treat every AI answer as a first draft you verify.

Compare a vague prompt like “Show sales performance” with strong ones and see how much the output changes. You learn the PRO prompt pattern of metric, dimension and timeframe, use it for total revenue by region and the top region, then combine monthly revenue, growth and trend questions into one prompt and refine the answer with follow-ups.

Let Copilot App Skills build a PivotTable of total revenue by region and month from the sales_clean table on a new worksheet, without dragging a single field. When a broad request to format it for readability falls short, you switch to a precise prompt that sorts regions by revenue, apply it, and learn to undo or restart a chat when Copilot stalls.

Prompt Copilot for a line chart of monthly revenue trends and a bar chart of revenue by region, then ask for two chart options and the story each one tells. You repeat the task in ChatGPT with an uploaded workbook, handle a corrupted download and a missing PivotTable, and see which tool fits which job.

Use Copilot App Skills to apply conditional formatting that marks the lowest profit value in red and highlights returned orders with a yellow fill and bold text, then tidy the sheet with AutoFit Column Width. You then ask ChatGPT for the same rules, catch a step-by-step answer instead of an edited file, and ask again for a downloadable workbook.

Have Copilot add formula columns to the Sales_Clean table: a Profit Margin column with structured references and IFERROR, a Revenue Flag that marks unusually high revenue for review, and a Low Margin Flag for margins under 10%. You sanity-check each column before trusting it, then run the same prompts in ChatGPT and compare its downloaded copy.

Go into detective mode with Copilot: list rows with discounts above 10%, look for unusually low profit, and test quantity values for outliers, where Copilot falls back on the IQR method. Along the way you see Copilot's cleanup suggestions on a messy sheet and learn why words like “unusually” matter and why high and low need a clear business definition.

Let Copilot clean the Sales messy sheet from its own suggestions: remove hidden extra spaces and standardize inconsistent entries such as East, EAST and east, or product names like Laptop and Monitor. You then write your own data-cleaning prompt for text fields, spaces, casing and missing values, and compare the plan from Chat with the output from App Skills.

Build a mini dashboard with Copilot: populate Total Revenue, Total Profit and Return Rate KPI values from the sales_Clean table, ask for the best two or three business charts and place them, then add slicers for region and channel. You also see how a fresh chat fixes a reply about missing values, and why the finished dashboard still needs checking.

Run a short QA checklist on anything Copilot builds: confirm the exact data and source were selected, check that dates and time frames line up, make sure every chart points at the right data, and confirm each number is correct, not just plausible. You leave with the habit of treating AI output as a first draft that you sign off.

Use ChatGPT as an analysis planner before opening Excel: upload the file and ask for eight business questions, five KPIs with Excel-style formulas and three recommended charts as a checklist. You then paste one of its prompts into Copilot for results, and see how the fix suggestion on an Excel formula error opens Copilot with a ready-made prompt.

Use the COPILOT function as a formula inside your cells: join the Microsoft 365 Insider beta channel if needed, write =COPILOT with a prompt and a cell reference to return state acronyms, and build a column that raises unit cost by 10% and 20%. You also see the busy state, the usage-limit pop-up and Copilot generating a regular formula for you.

Open Get guided help with your apps in Copilot, choose Excel and start the guided voice experience, which can see your screen and talk you through what to do next. You ask it how to change the design of a table and learn to use it whenever a ribbon tab such as Table Design leaves you stuck.

Meet Microsoft Stride, an Office Agent that opens your Excel file and runs tasks on it, up to 25 tasks a month. You upload the project, prompt it to improve aesthetics and clean the data, download the finished workbook, review the cleaner, color-coded result, and keep checking the data and formulas yourself.

Install Claude as an Excel add-in from the Add-ins icon, sign in, authorize the connection and get past onboarding and the beta notice. You then select a cell, ask Claude to calculate profit percentage, approve each change with Allow once, and ask Claude to fix an error that appears, ending with the rule that AI suggests and you verify.