iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Excel’s built-in features can help you reshape text, retrieve values, manage lists, keep headings in view, spot exceptions, and summarize data. These six tips are practical ways to work with a spreadsheet; the exact interface and availability can vary by Excel version and platform.
1. Clean up text with Flash Fill
Flash Fill recognizes a pattern from examples you type, then fills the rest of a column. It can split or combine text—for example, turning a full name into separate first- and last-name columns, or combining a code and a label.
- In the first output cell, type the result you want for the first row.
- Start typing the result for the next row. If Excel displays a preview of the remaining entries, review it and accept the suggestion.
- If no preview appears, select the output range and choose Data > Flash Fill, or press Ctrl+E in a supported Windows version.
Flash Fill infers values from examples; it does not create a formula that updates when the source text changes. If you need outputs to recalculate as source data changes, use a formula instead. Microsoft’s enablement guidance covers Microsoft 365, Excel 2024, and Excel 2021; on Windows, Flash Fill may need to be enabled in Excel settings. Microsoft’s Flash Fill instructions provide the applicable steps.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →2. Retrieve values with XLOOKUP
XLOOKUP finds a value in one range and returns the corresponding value from another. It can look in either direction, and Microsoft says it uses exact matches by default—useful when, for example, you want to find a product’s price from its ID.
#1 Best Overall
Illustrative syntax: =XLOOKUP(E2,A2:A100,C2:C100). Here, the formula looks for the value in E2 in A2:A100 and returns the corresponding value from C2:C100. Adjust the ranges to match your sheet, and verify the syntax and function support in your Excel version.
Unlike VLOOKUP, XLOOKUP does not require the lookup column to be the leftmost column in the return range. Microsoft lists XLOOKUP in its overview of Excel formulas, alongside compatibility information. If you use an older Excel release, check whether it supports the function before relying on it.
Rank #2
- Over 215 Microsoft Windows Excel Shortcuts
- Two-Sided Durable Laminiated Sheet
- Designed for Excel on a Windows Computer
3. Make a data range an Excel Table
An Excel Table adds sorting and filtering controls to the column headers and makes a list easier to work with. Start with a clean, rectangular range: use one clear header row, keep each column’s entries consistent, and avoid blank rows or columns inside the data.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →- Select a cell in the range, or select the full range.
- Use Excel’s command to format the range as a table, then confirm whether the first row contains headers.
- Use a header’s arrow to sort or filter the list.
For example, filter an order list to one customer, or sort a date column to bring the newest entries to the top. Microsoft’s basic Excel tasks guide covers tables and related operations. Menu labels can differ by platform and version.
4. Keep headings visible with Freeze Panes
Freeze Panes keeps rows above and columns to the left of the selected cell visible while you scroll. The selection determines what stays on screen.
- Keep one header row visible: select the first cell below that row, then choose View > Freeze Panes.
- Keep headings and identifying columns visible: select the cell below and to the right of the rows and columns you want to retain, then choose View > Freeze Panes.
To undo the setting, choose View > Freeze Panes > Unfreeze Panes. Microsoft documents this feature for Excel versions from 2016 through Microsoft 365; consult its Freeze Panes instructions for the interface applicable to your version.
5. Make exceptions stand out with conditional formatting
Conditional formatting applies a visual style when data meets a rule. Use it to flag values above or below a threshold, specific text or dates, duplicates, or top- and bottom-ranked values. You can also create a formula-based rule; its formula must return TRUE or FALSE for Excel to apply the formatting.
If a rule highlights unexpected cells, check these settings:
Best Value
- 💻 ✔️ EVERY ESSENTIAL SHORTCUT - With the SYNERLOGIC Reference Keyboard Shortcut Sticker, you have the most important shortcuts conveniently placed right in front of you. Easily learn new shortcuts and always be able to quickly lookup commands without the need to “Google” it.
- 💻✔️ Work FASTER and SMARTER - Quick tips at your fingertips! This tool makes it easy to learn how to use your computer much faster and makes your workflow increase exponentially. It’s perfect for any age or skill level, students or seniors, at home, or in the office.
- 💻 ✔️ New adhesive – stronger hold. It may leave a light residue when removed, but this wipes off easily with a soft cloth and warm, soapy water. Fewer air bubbles – for the smoothest finish, don’t peel off the entire backing at once. Instead, fold back a small section, line it up, and press gradually as you peel more. The “peel-and-stick-all-at-once” method only works for thin decals, not for stickers like ours.
- 💻 ✔️ Compatible and fits any brand laptop or desktop running Windows 10 or 11 Operating System.
- 💻 ✔️ Original Design and Production by Synerlogic Electronics, San Diego, CA, Boca Raton, FL and Bay City, MI, United States 2020. All rights reserved, any commercial reproduction without permission is punishable by all applicable laws.
- Applies to: confirm that the rule covers the intended cells.
- Formula references: check relative and absolute references, which can change how the rule evaluates across a range.
- Rule order: review the order or precedence of overlapping rules.
Use color as a visual aid, not the only way to convey meaning; retain clear labels so the sheet remains understandable without relying on color. See Microsoft’s conditional-formatting guide for its instructions.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.6. Get a quick summary with Quick Analysis
Quick Analysis brings common summary and visual options close to a selected range. Select numeric cells and inspect the Quick Analysis options for totals such as sums, averages, or counts, as well as formatting, sparklines, or charts. Choose an option that fits the question you are asking—for example, a total to summarize a column or a chart to visualize a series.
Microsoft’s basic-task guide describes these options for Excel 2016. Availability and the interface may differ in current versions and across platforms, so check your Excel copy if the Quick Analysis tool does not appear. The Microsoft guide to basic Excel tasks explains the feature.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
