For a fast Excel cheat sheet, turn a data range into a table with Ctrl+T, use Ctrl+Shift+L to add filters, and start calculations with functions such as SUM and IF. In Microsoft 365 for Windows, these basics—combined with a few navigation shortcuts, careful formula references, and sensible formatting—make everyday spreadsheets quicker to build and easier to check.
Set up a workbook that is easy to use
A reliable worksheet starts with a clear layout. Put one kind of information in each column, use a single header row, and keep each record on its own row. For example, a sales list might have columns for Date, Region, Product, Quantity, and Amount. Avoid blank rows inside the data: they can interrupt sorting, filtering, and automatic range detection.
Convert a clean data range into an Excel table by selecting a cell in it and pressing Ctrl+T, checking that “My table has headers” is selected when appropriate, and choosing OK. On Mac, Command+T opens the Create Table dialog. Tables add filter buttons, apply consistent formatting, and usually expand when you type directly below them. They also make formulas easier to read: a table formula can refer to a column by its header instead of relying only on a cell address.
- Use one clear, unique header for every column; avoid merged header cells.
- Keep dates as dates and amounts as numbers rather than storing them as text.
- Give a workbook a descriptive file name and save it in a known location.
Formatting a value does not necessarily change the value itself. A date displayed as “Nov 2” can still contain a year and time, and a percentage displayed with no decimal places can still have a more precise underlying value. This distinction matters when checking calculations and comparing results.
Navigate and edit faster with shortcuts
Keyboard shortcuts reduce repeated trips to the ribbon. These Windows shortcuts are useful in most worksheets; on Mac, the Command key commonly replaces Ctrl for standard editing shortcuts, though some Excel-specific shortcuts differ.
- Ctrl+C, Ctrl+X, and Ctrl+V copy, cut, and paste; use Command+C, Command+X, and Command+V on Mac.
- Ctrl+Z undoes the last action; on Mac, use Command+Z.
- Ctrl+F opens Find, and Ctrl+H opens Replace; use Command+F and Command+Shift+H on Mac in current Excel versions.
- Ctrl+1 opens Format Cells; on Mac, use Command+1.
- Ctrl+T creates a table on Windows; Command+T does so on Mac.
- Ctrl+Arrow moves to the edge of a continuous data region. Ctrl+Home moves to the beginning of the worksheet; the Mac equivalents depend on the keyboard and may require Fn with an arrow key.
To select a whole column or row, click its letter or number heading. Ctrl+Space selects the current column and Shift+Space selects the current row on Windows. If a laptop keyboard has function keys assigned to system controls, you may need to press Fn as well. Shortcuts can vary with keyboard layout, Excel settings, and platform, so use the ribbon command when a key combination behaves differently.
Use formulas for common calculations
Excel formulas begin with an equals sign and use cell references so results update when the input values change. For a total, with amounts in B2:B6 containing 12, 18, 9, 21, and 10, enter =SUM(B2:B6) in another cell. The result is 70. The colon means “through,” so B2:B6 includes every cell from B2 to B6.
To return one result when a condition is true and another when it is false, use IF. With a score in C2, where C2 contains 62, enter =IF(C2>=50,"Target met","Below target"). The result is Target met. In the formula, the comparison is tested first; the second and third arguments are the outputs for true and false.
SUMIF totals values that match a condition. With region names in A2:A6 reading East, West, East, South, and West, and their corresponding amounts in B2:B6 reading 120, 80, 95, 110, and 70, enter =SUMIF(A2:A6,"East",B2:B6). The result is 215, because 120 + 95 = 215. The first range contains the values Excel checks, the quoted text is the criterion, and the final range contains the numbers to add.
For a percentage change, with the old value in B2 as 80 and the new value in C2 as 100, enter =(C2-B2)/B2 in D2. The result is 0.25, which displays as 25% if D2 is formatted as Percentage. This calculation describes the change relative to the old value; if the old value is zero, the division is undefined and Excel returns a division error.
Understand references and copy formulas safely
When you copy a formula, Excel adjusts ordinary cell references relative to the new location. If D2 contains =B2*C2 and you fill it down to D3, the copied formula becomes =B3*C3. That is useful when each row has its own inputs.
Sometimes a reference must remain fixed. Dollar signs make a reference absolute: with a tax rate in F1 and a pre-tax amount in B2, enter =B2*$F$1 in C2, then fill down. The B2 reference moves to B3, B4, and so on, while $F$1 stays fixed. A mixed reference such as $A2 locks the column but allows the row to change; A$2 locks the row but allows the column to change.
On Windows, while editing a formula, select a reference and press F4 to cycle through reference styles. On Mac, Command+T cycles reference styles in formula editing. If your keyboard uses function keys for brightness or volume, Fn+F4 may be needed on Windows. Check that the dollar signs appear where intended before copying a formula across many rows.
Find, sort, and filter information
Ctrl+F is useful for finding a value or text in a worksheet; Ctrl+H can replace matching content. Check the search options if Excel does not find something you can see: the search may be limited to the current sheet or workbook, or set to look in formulas rather than displayed values. Replace carefully, especially in large workbooks, because replacing a short string can change many cells at once.
Tables provide filter arrows in their header row. Select a column’s arrow to show only chosen values, search within the list, or apply a condition such as “greater than.” To sort, use the same menu or select a cell in the data and choose a sort command. Sort the entire table or range, not just one column, so each record stays together. Sorting a single column independently can mismatch names, dates, and amounts.
For a quick filter toggle on Windows, select a cell in the range and press Ctrl+Shift+L. The filter arrows help narrow what is visible; they do not delete excluded rows. Clear a filter from its header menu to show all records again. Before sending a workbook, check whether filters or hidden rows might make the displayed data look incomplete to someone else.
Make important values easier to spot
Conditional formatting changes how cells appear when they meet a rule. Select the relevant cells, then use Home > Conditional Formatting. For a built-in rule, choose a category such as Highlight Cells Rules or Top/Bottom Rules and set the condition. For example, highlighting amounts below a target can make exceptions easier to scan, but color should support—not replace—clear labels and numeric values.
Use number formats to communicate what a value represents. Select cells and open Home > Number to choose formats such as Currency, Percentage, or Date. A value of 0.25 formatted as Percentage displays as 25%; applying percentage formatting does not multiply the stored value by 100. Similarly, adding a currency symbol through formatting changes the display, not the underlying number.
Keep visual emphasis restrained. If every cell is brightly colored, exceptions stop standing out. Use consistent colors, readable contrast, and descriptive chart titles. A reader should be able to understand the data without relying on color alone, especially when a workbook may be printed or viewed with color-vision differences.
Clean data and prevent avoidable errors
Many formula problems begin with inconsistent input. Extra spaces, misspelled categories, numbers stored as text, and inconsistent date formats can make matching or sorting unreliable. Use Data > Data Validation to restrict entries when a column should contain a limited set of choices, such as approved department names. A drop-down list can reduce spelling variations, although it does not automatically repair older entries already in the sheet.
When a formula displays an error, inspect its inputs and references before hiding the message. #DIV/0! commonly means a formula divides by zero or a blank cell. #N/A often means a lookup did not find a match. #VALUE! can indicate an unexpected data type. A cell showing ###### often needs a wider column or a different number format; it does not usually mean the formula is broken.
Also check whether a result is stale or appears not to update. Excel’s calculation setting is normally Automatic, but a workbook can be set to Manual. In that case, formulas may wait for recalculation; check Formulas > Calculation Options. If a formula appears as text, confirm that the cell is not formatted as Text and that the entry starts with = rather than an apostrophe. Avoid wrapping every formula in an error-hiding function: a hidden error can make a workbook look complete while concealing a missing input or incorrect reference.
Summarize larger lists and look up values
A PivotTable is often a better choice than a long set of hand-built subtotals when you need to summarize a list by categories. Select a cell in a clean table, then choose Insert > PivotTable and follow the dialog to place it in a new or existing worksheet. Drag fields into Rows, Columns, Values, or Filters to explore totals by region, product, or date. Confirm that the Values area is summarizing the field as intended—for example, Sum rather than Count—and refresh the PivotTable after its source data changes.
For a single matching result, XLOOKUP can return a value from a related column. It is available in Microsoft 365 and Excel 2021 or later. With product IDs in A2:A4 (P101, P102, P103), product names in B2:B4 (Pen, Notebook, Folder), and the ID P102 in E2, enter =XLOOKUP(E2,A2:A4,B2:B4,"Not found") in F2. The result is Notebook. XLOOKUP searches the first range for E2 and returns the corresponding position from the second range; the final argument supplies a readable result when there is no match. Make sure IDs are consistent—an extra space or a number stored as text can prevent an expected match.
Frequently Asked Questions
What are the most useful Excel shortcuts for beginners?
Start with Ctrl+C and Ctrl+V for copying and pasting, Ctrl+Z for undo, Ctrl+F for finding, Ctrl+1 for formatting, and Ctrl+T for creating a table on Windows. On Mac, the common editing equivalents use Command; Ctrl+1 and Ctrl+T correspond to Command+1 and Command+T.
How do I make a formula apply to the whole column?
Enter the formula in the first data row of a table column and press Enter; Excel tables commonly extend a calculated-column formula to other rows. In an ordinary range, use the fill handle at the lower-right corner of the selected formula cell to copy it down, then check that relative and absolute references behave as expected.
Why does Excel show the formula instead of its answer?
The cell may be formatted as Text, the formula may begin with an apostrophe, or Show Formulas may be enabled. Change the cell to General, re-enter the formula, and check Formulas > Show Formulas. Widening the column will not fix a formula stored as text.
How can I remove duplicate rows?
Make a copy of the data first, select the range or table, and choose Data > Remove Duplicates. Select the columns Excel should use to identify a duplicate. The command removes rows, so review the selection carefully and keep a backup when the original records matter.
Does filtering delete data from a worksheet?
No. A filter hides rows that do not meet the selected condition; clearing the filter displays them again. Deleting visible rows is a separate action, so verify whether you intend to hide records or remove them before editing a filtered list.
Related
- Remove Duplicates in Power Query: A Practical Guide
- Power Query Basics: Import, Clean, and Transform Data
- Append Queries in Power Query: How to Combine Tables
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Dropdown List From a Table Column That UpdatesYes. In Excel for Microsoft 365 on Windows, turn the source data into a table, define a workbook…
- How to Create a Background Template in WordTo create a reusable background template in Microsoft Word, place a picture or colored shape in the document…
- COUNTIF TRUE in Google Sheets: Count Boolean ValuesTo count cells whose value is the Boolean TRUE in Google Sheets, use =COUNTIF(A2:A20,TRUE), replacing A2:A20 with your…
- Excel Data Validation Based on Another Cell: How It WorksYes. In Excel for Microsoft 365, you can make data validation depend on another cell by using a…
- How to Mass Archive Emails in OutlookTo mass archive emails in Outlook, open the folder containing the messages, select the messages you want, and…
- Excel Row and Column AutoFit: Steps, Limits, and FixesTo auto-adjust Excel row or column size, select the relevant rows or columns, then choose Home > Cells…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.