10 Excel formatting tips and tricks

Have you ever opened a spreadsheet and thought, “Whoa, what happened here?” Columns running wild, numbers hiding in plain sight, and that one row you just can’t find? Good news: formatting in Excel doesn’t have to be a headache. With these Excel tips and tricks, and a little help from Microsoft 365 Copilot¹^,² you can make your data look sharp, stay organized, and enjoy working in your spreadsheets.

Ready to work smarter, not harder? Let’s dive in.

Copilot in Excel helps a woman format info in a spreadsheet.

1. How to find duplicates (and keep your data clean)

Duplicate entries can throw off your calculations and make your lists unreliable. Use Excel’s built-in tools to find duplicates:

Go to the Home tab
Click Conditional Formatting > Highlight Cells Rules > Duplicate Values

Why it matters: Spotting duplicates helps you clean up lists, avoid errors, and keep your data trustworthy.

2. How to remove duplicates in a flash

Cleaning up client lists or inventory logs? Removing duplicates ensures you’re not working with the same info twice.

Head to the Data tab
Click Remove Duplicates

3. How to merge cells for standout headers

Merged cells are perfect for section titles or dashboard headers that need to span multiple columns, making your sheets look polished and organized.

Select your cells
Go to the Home tab > Merge & Center

Turn data into insights with Excel

Make better decisions backed by data and insights

Learn more

4. How to freeze a row or column for easy navigation

When you’re scrolling through a giant spreadsheet, freezing headers keeps important info visible.

Go to the View tab
Click Freeze Panes
Select Freeze Panes to freeze your current selection
Select Freeze Top Row to keep the top row visible while scrolling through the rest of the sheet
Select Freeze First Column to keep the first column visible while scrolling through the rest of the sheet

Perfect for financial sheets or project trackers with lots of columns.

5. How to unhide all rows to reveal hidden info

Sometimes spreadsheets from coworkers or clients arrive with missing information. Unhiding rows ensures nothing is overlooked.

Select all cells
Right-click and choose Unhide

Copilot can even help you spot hidden rows. No more missing data surprises.

6. How to wrap text to keep notes tidy

Long notes can stretch columns across your screen. Wrapping text keeps everything neat and readable.

Select your cells
Go to Home > Wrap Text

Now your notes stay visible without endless scrolling.

7. How to lock cells to protect formulas

Protect important formulas from accidental edits, especially before sharing your sheet.

Unlock all cells first
Lock only the ones you want protected
Go to Review > Protect Sheet

8. How to password protect an Excel file for security

Sharing invoices or financial plans? Add a layer of security so only the right people can access your data.

Go to File > Info > Protect Workbook > Encrypt with Password

9. How to add a drop-down list for easy data entry

Drop-down lists make data entry fast and reduce errors (perfect for content calendars, status trackers, or CRM-style sheets).

Select your cell(s)
Go to Data > Data Validation > List

10. How to use Copilot in Excel to format smarter

Sometimes, formatting a big spreadsheet feels overwhelming. That’s where Copilot in Excel ¹^,² can help. Think of it as your AI-powered assistant for fast, clean results.

Try these prompts to tidy up your data:

“Format this range as a table with headers.”
“Highlight any duplicate values or outliers.”
“Suggest the best chart for this data.”
“Find and fix inconsistent number formats.”

As more people bring AI tools into the workplace, Copilot gives you a secure “bring-your-own-AI” (BYOAI) option that fits your organization’s policies. This lets employees use AI to work efficiently without putting data at risk from unmanaged or unapproved tools. Want to give it a go? Get Copilot in Excel with a Microsoft 365 plan.

Try Microsoft 365 and work smarter today

Ready to transform your spreadsheets and your workflow? Try Microsoft 365 and see how Excel and Copilot¹^,² can help you format efficiently and get more done.

DISCLAIMER: Features and functionality subject to change. Articles are written specifically for the United States market; features, functionality, and availability may vary by region.

Copilot in Excel requires AutoSave to be enabled, meaning the file must be saved to OneDrive; it doesn’t function with unsaved files.

Microsoft 365 Personal, Family, or Premium subscription required; AI features only available to subscription owner and cannot be shared; usage limits apply. Learn more. Minimum age limits may apply to use of AI features. Details.

Achieve the extraordinary with Microsoft 365

The powerful productivity apps and creativity tools in Microsoft 365 just got better. Work, play, and create better than ever before with the apps you love and Microsoft Copilot by your side.

Try for free