How-To Guides
Step-by-step guides to the Excel formulas and features people use most, with pictures, worked examples and real-world scenarios in every guide.
Excel formulas
Add, average and count
Totals, averages, counts, highest and lowest values, and rounding.
SUMHow to Use the SUM Function in ExcelAdd up a column, a row or scattered cells with SUM, and get a total in one keystroke with AutoSum.Beginner11 min readAVERAGEHow to Use the AVERAGE Function in ExcelFind the average of a column, a row or several ranges with AVERAGE, and know how it treats blank cells, zeros and text.Beginner12 min readCOUNTCOUNTACOUNT and COUNTA in Excel: Count Numbers and Non-Blank CellsCount the cells that hold numbers with COUNT, count every filled cell with COUNTA, and know which one to use.Beginner12 min readMINMAXHow to Use MIN and MAX in ExcelFind the smallest and largest numbers in a range with MIN and MAX, within a group with MINIFS and MAXIFS, and use them to cap a value.Beginner13 min readROUNDROUNDUPROUNDDOWNHow to Round Numbers in Excel: ROUND, ROUNDUP and ROUNDDOWNRound prices to the cent, sales to the nearest thousand and box counts up to whole numbers with ROUND, ROUNDUP and ROUNDDOWN.Beginner11 min read
Totals with conditions
Add, count or average only the rows that match: one region, one month, amounts over a limit.
SUMIFSUMIFSHow to Use SUMIF and SUMIFS in ExcelAdd up only the numbers that meet a condition with SUMIF, or several conditions at once with SUMIFS.Intermediate14 min readCOUNTIFCOUNTIFSHow to Use COUNTIF and COUNTIFS in ExcelCount the cells that meet a condition with COUNTIF, or several conditions at once with COUNTIFS, using operators, cell references and wildcards.Intermediate14 min readAVERAGEIFAVERAGEIFSHow to Use AVERAGEIF and AVERAGEIFS in ExcelAverage only the numbers that meet a condition with AVERAGEIF, or several conditions at once with AVERAGEIFS, including date ranges and wildcards.Intermediate12 min read
Make decisions
Show different results depending on a value, combine conditions and handle errors.
IFHow to Use the IF Function in ExcelMake a formula return one result when a condition is true and another when it's false, with text, numbers or calculations.Beginner13 min readANDORHow to Use AND and OR in ExcelTest several conditions at once with AND and OR, reverse a test with NOT, and use them inside IF to label, flag and calculate rows.Beginner12 min readIFSHow to Use the IFS Function in ExcelSort values into grades, tiers and price bands with IFS instead of nested IFs, and know when SWITCH or a lookup table is the better choice.Intermediate13 min readIFERRORHow to Use IFERROR in ExcelReplace error values such as #DIV/0! and #N/A with a value or message of your own using IFERROR and IFNA, without hiding real mistakes.Intermediate14 min read
Look up values
Find a value in one table and bring back matching information: prices, names, rates.
VLOOKUPHow to Use VLOOKUP in ExcelLook up a value in a table and return a matching value from the same row with VLOOKUP, and fix the #N/A and #REF! errors it can return.Intermediate15 min readXLOOKUPHow to Use XLOOKUP in ExcelFind a value in one column and return the matching value from another with XLOOKUP, including a fallback for missing values, nearest matches and wildcards.Intermediate15 min readINDEXMATCHHow to Use INDEX and MATCH in ExcelCombine INDEX and MATCH to look up a value in any column, including one to the left, and to find the value where a row and a column meet.Intermediate13 min read
Work with text
Join, split, clean and format text: names, codes, IDs and labels.
CONCATTEXTJOINHow to Join Text in Excel: CONCAT, TEXTJOIN and &Combine names, codes and lists into one cell with the & operator, CONCAT and TEXTJOIN, with spaces, separators, line breaks and number formats where you want them.Beginner11 min readLEFTRIGHTMIDHow to Use LEFT, RIGHT and MID in ExcelPull characters from the start, end or middle of a code, ID or email address with LEFT, RIGHT and MID, and handle parts of varying length with FIND and LEN.Beginner11 min readTRIMHow to Use the TRIM Function in ExcelRemove the extra spaces that break lookups and counts with TRIM, and clean up non-breaking spaces and line breaks from web and exported data.Beginner10 min readTEXTHow to Use the TEXT Function in ExcelUse TEXT to format numbers, percentages and dates inside a formula, so they read properly when you join them into labels and sentences.Intermediate11 min read
Dates and times
Today's date, deadlines, ages and days remaining.
Which formula do I need?
Find what you want to do in the left column, then open the guide for the formula next to it.
| I want to… | Use | Example |
|---|---|---|
| Add up a column of numbers | SUM | =SUM(B2:B13) |
| Add up only some rows, like one region | SUMIF / SUMIFS | =SUMIFS(D2:D50,B2:B50,"East") |
| Find the average | AVERAGE | =AVERAGE(C2:C31) |
| Average only some rows | AVERAGEIF / AVERAGEIFS | =AVERAGEIF(B2:B50,"Online",D2:D50) |
| Count how many entries there are | COUNT / COUNTA | =COUNTA(A2:A100) |
| Count entries that meet a condition | COUNTIF / COUNTIFS | =COUNTIF(E2:E100,"Late") |
| Find the highest or lowest value | MIN / MAX | =MAX(D2:D50) |
| Round a price or a percentage | ROUND / ROUNDUP / ROUNDDOWN | =ROUND(B2*1.08,2) |
| Show one result or another depending on a value | IF | =IF(C2>=60,"Pass","Fail") |
| Check two or more conditions at once | AND / OR | =AND(C2>=60,D2="Yes") |
| Choose between several results, like grade bands | IFS | =IFS(C2>=90,"A",C2>=80,"B",TRUE,"C") |
| Show a friendly message instead of an error | IFERROR | =IFERROR(B2/C2,"No data") |
| Look up a price or a name from another table | XLOOKUP | =XLOOKUP(F2,A2:A50,C2:C50) |
| Look up in an older version of Excel | VLOOKUP | =VLOOKUP(F2,A2:C50,3,FALSE) |
| Look up to the left, or by row and column | INDEX and MATCH | =INDEX(A2:A50,MATCH(F2,C2:C50,0)) |
| Combine first and last names | CONCAT / TEXTJOIN / & | =A2&" "&B2 |
| Take part of a code or ID | LEFT / RIGHT / MID | =LEFT(A2,3) |
| Remove extra spaces from imported text | TRIM | =TRIM(A2) |
| Show a number or date in a set format inside text | TEXT | =TEXT(B2,"mmm d, yyyy") |
| Put today's date in a cell that updates itself | TODAY / NOW | =TODAY() |
Excel features
Basics and shortcuts
Keyboard shortcuts, saving and undo, instant totals on the status bar, a ribbon set up your way and ready-made templates.
- Keyboard shortcutsExcel Navigation and Keyboard ShortcutsJump to the edges of your data, select whole columns of numbers, insert totals and run everyday commands from the keyboard.Beginner10 min read
- SaveAutoSaveUndoHow to Save, AutoSave and Undo in ExcelSave a workbook, let AutoSave keep cloud files up to date, undo and redo mistakes, and get back work after Excel closes unexpectedly.Beginner9 min read
- Status barQuick Totals from the Excel Status BarGet the sum, average, count, minimum and maximum of any selection from the status bar, without writing a formula, and copy the result in one click.Beginner7 min read
- Quick Access ToolbarRibbonCustomize the Ribbon and Quick Access Toolbar in ExcelPut the commands you use most one click away: add them to the Quick Access Toolbar, build your own ribbon tab, show the Developer tab and reset everything.Beginner8 min read
- TemplatesHow to Use and Create Excel TemplatesStart a workbook from a ready-made Excel template, save your own workbook as an .xltx template, and reuse it every month without overwriting the original.Beginner9 min read
Enter and edit data
Fill series and patterns, paste only what you need, and find, replace or select cells in bulk.
- AutoFillFill handleHow to Use AutoFill and the Fill Handle in ExcelFill days, months, numbers, dates and numbered labels by dragging the fill handle, and control the result with Auto Fill Options and the Series dialog.Beginner9 min read
- Flash FillHow to Use Flash Fill in ExcelSplit, combine and reformat text by typing one example and letting Flash Fill complete the column, and know when a formula is the better choice.Beginner9 min read
- Paste SpecialPaste Special in Excel: Paste Values, Transpose and MorePaste only the part of a copy you need: values instead of formulas, formats, a transposed copy, or a calculation applied to a whole range.Beginner10 min read
- Find & ReplaceHow to Use Find and Replace in ExcelFind any value on a sheet or in a whole workbook, change every copy of it at once, and use options and wildcards so you replace only what you mean to.Beginner9 min read
- Go To SpecialHow to Use Go To Special in ExcelSelect every blank, formula, constant, error or visible cell in a range in one step, then fill, check, fix or copy them all at once.Intermediate10 min read
View large sheets
Keep headings on screen, zoom and split the window, and hide rows, columns and sheets you don't need to see.
- Freeze PanesHow to Freeze Panes in ExcelKeep headings and labels on screen while you scroll: freeze the top row, the first column, or any rows and columns, and unfreeze them again.Beginner8 min read
- ZoomSplitNew WindowZoom, Split and New Window: Viewing Large Sheets in ExcelSee more of a big sheet, or two parts of it at once: zoom in and out, zoom to a selection, split the window, and open a second window side by side.Beginner9 min read
- HideUnhideHow to Hide and Unhide Rows, Columns and Sheets in ExcelHide rows, columns and sheets you don't need to see, bring them back, and know how hidden cells affect formulas, copying and charts.Beginner8 min read
Formatting
Make sheets readable: cell and number formats, Format Painter, styles and themes, conditional formatting, and checkboxes and pictures in cells.
- Cell formattingHow to Format Cells in ExcelMake a plain table easy to read with bold headings, fill colors, borders, alignment, wrapped text and the right column widths.Beginner10 min read
- Number formatsNumber and Date Formats in ExcelShow prices as currency, discounts as percentages and dates as real dates, and control decimal places without changing the stored values.Beginner9 min read
- Format PainterHow to Use Format Painter in ExcelCopy the look of one cell to other cells with Format Painter: number formats, fonts, fills, borders and highlight rules, once or many times.Beginner8 min read
- Merge & CenterWrap TextMerge & Center and Wrap Text in ExcelCenter a title over a table, fit long text inside a cell with Wrap Text and line breaks, and avoid the problems merged cells cause.Beginner9 min read
- Cell StylesThemesCell Styles and Themes in ExcelFormat cells with named cell styles you can change in one place, create your own styles, and switch a workbook's colors and fonts with a theme.Intermediate10 min read
- Conditional FormattingHow to Use Conditional Formatting in ExcelColor cells automatically by rule: flag values over a limit, catch duplicates, highlight top values, add data bars and write your own formula rules.Intermediate13 min read
- CheckboxesPicture in CellCheckboxes and Pictures in Cells in ExcelAdd tick boxes that count themselves, strike through finished tasks, and place pictures inside cells so they sort and filter with their rows.Beginner10 min read
Organize lists
Turn a list into a table, sort and filter it, split columns, remove duplicates, control what can be typed, name ranges and add subtotals.
- Excel TablesHow to Use Excel TablesTurn a plain range into an Excel table with Ctrl+T, then let it filter, band, total and extend formulas to new rows for you.Beginner9 min read
- SortFilterHow to Sort and Filter Data in ExcelPut a list in order by one or more columns, and show only the rows you need with text and number filters.Beginner9 min read
- Advanced FilterHow to Use Advanced Filter in ExcelFilter a list with conditions typed in cells, combine AND and OR across columns, copy the matching rows elsewhere and pull out a unique list.Intermediate10 min read
- Remove DuplicatesHow to Remove Duplicates in ExcelDelete repeated rows from a list with Remove Duplicates, choose which columns decide what counts as a duplicate, and check for duplicates before you delete.Beginner8 min read
- Text to ColumnsHow to Split Data with Text to Columns in ExcelSplit one column into several at a comma, a dash or a fixed position, keep leading zeros, and turn text dates into real dates with Text to Columns.Beginner9 min read
- Data ValidationHow to Use Data Validation in ExcelAdd drop-down lists, number and date rules, input messages and error alerts, then protect formula cells so only the inputs can change.Intermediate13 min read
- Named rangesName ManagerHow to Use Named Ranges in ExcelGive cells and ranges names like Sales or BonusRate, use them in formulas, and edit, fix or delete them in Name Manager.Intermediate11 min read
- GroupOutlineSubtotalGroup, Outline and Subtotal in ExcelAdd a total under each group of a sorted list with Subtotal, collapse rows and columns with an outline, and group detail rows or columns yourself.Intermediate10 min read
Import and combine data
Bring in CSV files and other sources, clean them up, and combine data from several sheets or files.
- Get DataCSV importText to ColumnsFlash FillRemove DuplicatesImporting and Cleaning Data in ExcelImport a CSV file without losing data, check that numbers are really numbers, then split, fill, standardize and de-duplicate an export.Intermediate13 min read
- Power QueryPower Query Basics: Combine and Clean Data in ExcelImport a folder of monthly files with Power Query, clean them with applied steps, and refresh the result whenever a new file arrives.Advanced15 min read
- ConsolidateHow to Consolidate Data from Several Sheets in ExcelCombine lists from several sheets or workbooks into one summary with the Consolidate command, matching rows by their labels or by position.Intermediate10 min read
Charts and PivotTables
Chart your numbers, use Quick Analysis and sparklines, and summarize data with PivotTables, slicers, timelines and the Data Model.
- ChartsHow to Create a Chart in ExcelTurn a column of numbers into a column, line or pie chart, add a clear title and axis titles, and pick the right chart for your question.Beginner9 min read
- Quick AnalysisHow to Use the Quick Analysis Tool in ExcelSelect some data and press Ctrl+Q to preview and add conditional formatting, a chart, totals, a table or sparklines in one or two clicks.Beginner8 min read
- SparklinesHow to Add Sparklines in ExcelDraw a tiny line, column or win/loss chart inside a cell next to each row of numbers, mark the high and low points, and compare rows on the same scale.Beginner8 min read
- PivotTablesHow to Create a PivotTable in ExcelSummarize a list by category, month or region in a few clicks with a PivotTable, then change the calculation, format the numbers and refresh it.Intermediate13 min read
- SlicersTimelinesPivotChartsPivotCharts, Slicers and Simple Dashboards in ExcelTurn a PivotTable into a PivotChart, add slicers and a timeline anyone can click to filter, and arrange them into a clean one-page dashboard.Intermediate13 min read
- Data ModelPower PivotExcel Data Model and Power Pivot BasicsRelate a sales table to a product table in the Data Model, build one PivotTable from both, and write your first DAX measures.Advanced14 min read
What-if analysis and forecasting
Find the input that hits a target, compare scenarios, optimize with Solver, forecast trends and ask questions about your data.
- Goal SeekScenario ManagerData TablesWhat-If Analysis in Excel: Goal Seek, Scenarios and Data TablesWork backward from a target with Goal Seek, save best and worst cases with Scenario Manager, and compare many inputs at once with data tables.Advanced15 min read
- SolverForecast SheetTrendlineSolver, Forecasting and Statistics Tools in ExcelFind the best product mix with Solver, test how sensitive it is, project a time series with Forecast Sheet and handle outliers in a trend honestly.Expert18 min read
- Analyze DataCopilotAsk Questions About Your Data: Analyze Data and Copilot in ExcelGet your data ready, ask Analyze Data or Copilot questions in plain language, and check every answer with a formula before you rely on it.Beginner11 min read
Reviewing, sharing and output
Comment, share, protect, check, print and export your work.
- CommentsNotesComments and Notes in ExcelAsk questions with threaded comments, mention and reply to colleagues, resolve finished threads, and explain cells with notes.Beginner9 min read
- ShareCo-authoringVersion HistoryShare, Co-author and Restore Versions in ExcelShare a workbook from OneDrive or SharePoint with the right permissions, edit it with others at the same time, and bring back an earlier version.Beginner10 min read
- Freeze PanesNamed rangesProtect SheetProtect WorkbookOrganize and Protect an Excel WorkbookGive each sheet a clear job, freeze headers, name ranges, add notes and comments, and protect formulas while inputs stay editable.Intermediate12 min read
- Go To SpecialPaste SpecialFormula auditingProductivity and Formula Auditing in ExcelSelect blanks and formulas in one step, paste values and formats, copy styles, and trace and evaluate formulas to find out why a number is wrong.Intermediate13 min read
- PrintPage SetupHow to Print in Excel: Print Area, Titles and Fit to PagePrint exactly the cells you want, repeat the heading row on every page, fit a sheet to one page wide, and add page numbers.Beginner11 min read
- PDFCSVHow to Save Excel as PDF or CSVTurn a sheet into a PDF that looks right for anyone, or a CSV file that other apps can import, and know what each format keeps and loses.Beginner10 min read
Automation
Let Excel repeat the steps for you: record macros, write VBA, and automate Excel for the web with Office Scripts.
- MacrosHow to Record a Macro in ExcelRecord a repeated formatting job as a macro, read the VBA Excel writes, choose relative or absolute references and save and open macro files safely.Advanced13 min read
- VBAAutomating Excel with VBAWrite VBA macros by hand: loop over ranges without Select, handle errors, respond to events, and log every run.Expert20 min read
- Office ScriptsPower AutomateOffice Scripts and Power Automate for ExcelRecord and write Office Scripts, run them on a schedule with Power Automate, and choose the simplest automation that solves the job.Expert16 min read
Prefer to go in order? Follow the learning path: The 20 Most-Used Excel Functions → · Everyday Excel Skills →