Excel How-To Guides

Excel guides covering formulas, pivot tables, drop-down lists, charts and the layout jobs that come up most, with fixes for common errors.

Formulas and functions

How to Calculate Percentage in Excel With Formulas
Divide the part by the total, as in =B2/C2, then select Percent Style. Plus formulas for tax, discounts, percentage change and fixes for wrong results.
How to Calculate Standard Deviation in Excel
Calculate standard deviation in Excel with =STDEV.S for a sample or =STDEV.P for a whole population, plus PivotTable groups and fixes for common errors.
How to Enable Macros in Excel for Trusted Files
Select Enable Content on the yellow bar only if you trust the source and know what the macros do. A red Security Risk bar needs Unblock in Properties.
How to Multiply in Excel With Formulas and Cells
Multiply numbers, cells, and columns in Excel with the asterisk, PRODUCT, or Paste Special. See which method updates and how to fix wrong results.
How to Round in Excel With Formulas or Formatting
Click Decrease Decimal to make a number look rounded, or enter =ROUND(A2,2) in another cell for a rounded value. Plus round up, round down and multiples.
How to Subtract in Excel Across Rows and Columns
To subtract in Excel, type =A2-B2 or =10-5 and press Enter. Fill it down a column, take one number off every row, or use Paste Special.
How to Sum a Column in Excel With AutoSum and SUM
To sum a column in Excel, click the empty cell below the numbers, select AutoSum, check the range and press Enter. Plus SUM, filters and wrong totals.
How to Use Copilot in Excel for Formulas and Charts
Select the Copilot button at the lower right of the sheet, pick Chat only, Plan or Allow editing, then ask for formulas, charts or PivotTables.
How to Use the IF Function in Excel With Examples
Type =IF(test, result if true, result if false) and press Enter. Worked examples for AND, OR, nested IF, Excel for the web and the common formula errors.
How to Use VLOOKUP in Excel Across Sheets and Workbooks
Type =VLOOKUP(what to find, where to look, column number, FALSE) and press Enter. Worked examples for other sheets, other workbooks and Excel for the web.
How to Use XLOOKUP in Excel and Fix Errors
Type =XLOOKUP(F2,A2:A6,B2:B6) with your own cells and press Enter. Examples for left lookups, two columns, wildcards and #N/A or #SPILL! fixes.

Rows, columns and cells

How to Add Bullet Points in Excel Cells and Lists
Add a bullet with Insert > Symbol and code 2022 or Alt and keypad 7, stack bullets in one cell with Alt + Enter, or bullet a whole column with a format.
How to Alphabetize in Excel Without Breaking Rows
Click one cell in the column, then Data > Sort A to Z, and Excel keeps each row together. Plus two-column sorts, tables, SORT and Excel for the web.
How to Combine Two Columns in Excel With a Space
Combine two Excel columns with a space using &, CONCAT or TEXTJOIN, then fill down. See Flash Fill, Power Query, and fixes for blanks and lost formatting.
How to Hide Columns in Excel Without Losing Data
Right-click a column letter and choose Hide, and the data stays put. We also cover Ctrl+0, the Home tab, zero width, groups and Excel for the web.
How to Insert a Row in Excel and Add Multiple Rows
Right-click the row number below the spot and choose Insert. Add multiple rows, use the shortcut, work in tables and fix a greyed out Insert command.
How to Lock Cells in Excel (Windows, Mac, Web)
Clear Locked on the cells people may edit, then turn on Review > Protect Sheet. Steps for Excel on Windows, Mac and the web, plus passwords.
How to Merge Cells in Excel Without Losing Data
Select the cells, then Home > Merge & Center. Excel keeps only the upper-left value, so here is how to keep the rest or center a title without merging.
How to Pin a Row in Excel and Freeze Rows or Columns
To pin a row in Excel, go to View > Freeze Panes > Freeze Top Row. For more rows or columns, select the cell below and right of them, then Freeze Panes.
How to Remove Duplicates in Excel and Find Them First
Choose Data > Remove Duplicates, tick the columns that define a repeat and select OK. Excel keeps the first copy. To find duplicates first, highlight them.
How to Split Cells in Excel and Separate Names
Split cells in Excel with Text to Columns, Flash Fill or TEXTSPLIT, separate first and last names, and keep the original data safe while you do it.
How to Strikethrough in Excel on Desktop and Web
On Windows, press Ctrl+5 to cross out text in Excel, or use Format Cells. Covers one word in a cell, a Done rule, Excel for the web and stale formulas.
How to Unhide Columns in Excel Including Column A
Select the column letters on both sides of the gap, right-click and choose Unhide. Tested fixes for all columns, column A, greyed out commands and the web.
How to Wrap Text in Excel and Show Every Line
To wrap text in Excel, select the cells and choose Home > Wrap Text. Use Alt + Enter for your own break, and AutoFit or drag the row if lines are hidden.

Tables, charts and lists

How to Add a Checkbox in Excel and Use It in a Checklist
Add a checkbox in Excel with Insert > Checkbox, tick it with a click or Spacebar, and use its TRUE or FALSE value. Form Controls and the web too.
How to Create a Drop Down List in Excel (Windows, Mac, Web)
Select the cells, open Data > Data Validation, set Allow to List and fill in Source. Steps for Excel on Windows, Mac and the web, and how to edit the list.
How to Create a Pivot Table in Excel Step by Step
Select your list, choose Insert > PivotTable > From Table/Range and click OK, then tick a category for Rows and a number column for Values.
How to Make a Calendar in Excel That Updates
Make a monthly calendar in Excel from a Microsoft template or a blank sheet. Change the month with one date cell, then preview and print it on one page.
How to Make a Graph in Excel and Fix Its Labels
Select your data, choose Insert > Recommended Charts and OK. Then pick the right chart type, add labels and fix a missing series or gaps, desktop or web.

Files and printing

How to Convert PDF to Excel Without a Converter
In Excel for Microsoft 365 on Windows, select Data > Get Data > From File > From PDF, pick the table in the Navigator and select Load. No upload needed.
How to Password Protect an Excel File in Microsoft 365
Make an Excel file require a password before it opens. In Excel on Windows, use File > Info > Protect Workbook > Encrypt with Password, then save.
How to Set Print Area in Excel and Print Only What You Need
Select the cells you want, then choose Page Layout, Print Area, Set Print Area. Preview the result, add ranges, or clear the saved area.

All how-to guides →