How-to guides

The answer is in the first paragraph; the steps, formulas, and a free template follow. Written for current Microsoft 365 Excel, with notes for older versions and Google Sheets.

• How to Make a Drop-Down List in Excel

  To make a drop-down list in Excel, select the cells, go to the Data tab, click Data Validation, and set Allow to List. In the Source box, either type the choices separated by commas or point at a range of cells, then click OK. Every selected cell now shows an arrow with your options.

• VLOOKUP vs XLOOKUP: Which to Use

  Between VLOOKUP and XLOOKUP, use XLOOKUP whenever your Excel has it — Microsoft 365 or Excel 2021 and later. It looks left or right, defaults to an exact match, survives inserted columns, and handles missing values with a built-in fallback. Reach for VLOOKUP only when a file must open in Excel 2019 or earlier, where XLOOKUP simply does not exist.

• How to Remove Duplicates in Excel

  To remove duplicate rows in Excel, select any cell in your data, go to the Data tab, and click Remove Duplicates. In the dialog, tick the columns Excel should compare — rows that match on every ticked column are treated as duplicates and deleted in place, leaving the first occurrence. To review before deleting, highlight duplicates with conditional formatting first, or extract a clean list non-destructively with =UNIQUE.

• How to Merge Cells Without Losing Data

  Merge & Center keeps only the top-left value and discards the rest, so it always loses data. To keep everything, first combine the values into one cell with & or TEXTJOIN, paste the result back as values, then merge. Or skip merging entirely with Center Across Selection, which spans the look without touching the underlying cells.

• How to Make a Gantt Chart in Excel

  To make a Gantt chart in Excel, list each task with its Start date and Duration, then calculate End with =B4+C4-1. Lay a row of dates across the top, and use a conditional-formatting rule, =AND($B4<>"",F$2>=$B4,F$2<=$D4), to fill a colored bar in every cell whose date falls inside a task's run. The steps below build it cell by cell.

• How to Freeze Rows and Columns in Excel

  To freeze rows and columns in Excel, go to the View tab and click Freeze Panes. Pick Freeze Top Row to lock row 1, Freeze First Column to lock column A, or select the cell below-and-right of everything you want pinned and choose Freeze Panes — selecting B2 freezes both row 1 and column A so your headers stay visible while you scroll.

• How to Calculate Hours Worked in Excel

  To calculate hours worked in Excel, enter start and end times as real times, then subtract: =(E2-B2)-(D2-C2) takes end minus start and removes the lunch break. Format that cell as [h]:mm so totals over 24 hours still show. For decimal hours for payroll, multiply the result by 24 with =F2*24 and format it as a plain number.

• How to Make a Budget in Excel (Step by Step)

  To make a budget in Excel, list monthly income in one section and expenses in another, each with Planned and Actual columns. Total each section with =SUM, then subtract total expenses from total income to see what's left. The structure takes about ten minutes to build — the steps below walk through every cell, or download our free template with the formulas already in place.

• Conditional Formatting in Excel: The 5 Rules You'll Actually Use

  Conditional formatting lives under Home → Conditional Formatting. Five rules cover almost everything: Highlight Cells Rules → Duplicate Values to catch repeats, Less Than 0 in red for over-budget figures, Data Bars for instant magnitude, Top/Bottom Rules → Top 10 Items, and a New Rule formula like =$D2="Done" to color an entire row. The steps below apply one end to end.

• How to Print an Excel Sheet on One Page (and Save as PDF)

  To print an Excel sheet on one page, open the Page Layout tab, find the Scale to Fit group, and set both Width and Height to 1 page — Excel shrinks everything to fit a single sheet. The fast version is File → Print, then Fit Sheet on One Page from the scaling dropdown. To save a PDF, use File → Export → Create PDF/XPS.

• Pivot Tables in Excel: A Beginner's Guide

  To create a pivot table in Excel, click any cell inside your data, go to the Insert tab, click PivotTable, and click OK. A blank pivot appears on a new sheet with a field list on the right. Drag a text field into Rows and a number field into Values — Excel groups and totals automatically.

• SUMIF vs SUMIFS: Which to Use

  Use SUMIFS for everything. It sums values on one condition or many, while SUMIF is limited to a single condition. The one trap is argument order: SUMIF(range, criteria, [sum_range]) puts the numbers you add last, but SUMIFS(sum_range, criteria_range1, criteria1, …) puts them first. The only reason SUMIF survives is legacy workbooks.

• Excel Keyboard Shortcuts: The 50 Worth Learning (Free PDF)

  The Excel keyboard shortcuts that pay off fastest are Ctrl+Arrow to jump to the edge of your data, Ctrl+Shift+Arrow to select to that edge, Ctrl+1 to open Format Cells, F4 to lock a reference or repeat your last action, and F2 to edit a cell in place. The full set of 50, with Mac keys, is in the tables below and the printable PDF.

• COUNTIF in Excel: 7 Examples That Cover Most Jobs

  COUNTIF counts the cells in a range that meet one condition: =COUNTIF(B2:B51,"High") returns how many cells in B2:B51 equal High. The criteria can be text, a number, a comparison like ">100", a date, or a wildcard pattern. For two or more conditions at once, use COUNTIFS. The seven examples below cover almost every counting job you'll hit.

• How to Use IFERROR in Excel

  IFERROR wraps a formula and returns a fallback you choose whenever that formula errors: =IFERROR(VLOOKUP(A2,D:F,3,0),"Not found") shows Not found instead of #N/A. The syntax is =IFERROR(value, value_if_error). It catches every error type — #N/A, #DIV/0!, #REF!, #VALUE!, #NAME?, and #SPILL! — so reach for IFNA when you only want to hide a failed lookup.

• How to Split Text into Columns in Excel

  To split text into columns in Excel, select the column, open the Data tab, and click Text to Columns. Choose Delimited, click Next, tick the delimiter that separates your values — comma, semicolon, space, or tab — set each column's data format, then click Finish. Excel splits the cell into separate columns at every delimiter it finds.

• CONCATENATE vs TEXTJOIN: Combining Text in Excel

  To combine text in Excel, use TEXTJOIN when you need a delimiter or are joining three or more cells — =TEXTJOIN(", ",TRUE,A2:C2) takes one separator, ignores blanks, and accepts whole ranges. Use the & operator for quick two-cell joins like =A2&" "&B2. CONCATENATE still works but is legacy; you never need it in Microsoft 365.