How to Apply General Formatting in Excel

General formatting in Excel lets you control how numbers, text, and dates appear so your spreadsheet is clear and professional. This guide covers the essential formatting options—number formats, alignment, fonts, borders, fills, and conditional formatting—and shows you exactly where to click in the Ribbon. You’ll need Excel 2016 or later (Windows or Mac), though the steps are nearly identical in older versions.

What You’ll Need

• Microsoft Excel installed (any recent version)
• A spreadsheet with data you want to format
• Basic familiarity with selecting cells (click, drag, Ctrl+Click)

Step-by-Step

1. Select the cells you want to format. Click and drag to select a range, or click a single cell. To select non-adjacent cells, hold Ctrl (Windows) or Cmd (Mac) while clicking. To select an entire row or column, click its header letter or number.
2. Open the Format Cells dialog (optional but powerful). Right-click the selected cells and choose Format Cells, or press Ctrl+1 (Windows) / Cmd+1 (Mac). This dialog gives you access to all formatting options in one place. You can also use the Ribbon for quick formatting, but the dialog is more thorough.
3. Apply number formats. In the Number tab of the Format Cells dialog, choose a category like Number (for decimals), Currency, Date, or Percentage. Adjust decimal places and use the checkbox for thousands separators. For quick formatting, use the Number Format dropdown in the Home tab (it shows “General” by default).
4. Align text and numbers. In the Alignment tab, set horizontal and vertical alignment (e.g., center, left, top). Use Wrap Text to show long text on multiple lines, and Merge Cells to combine selected cells into one. You can also rotate text by setting a degree in the Orientation box.
5. Change fonts and styles. In the Font tab, pick a font, size, and style (bold, italic, underline). Use the Effects for strikethrough or superscript. For quick access, use the Font group in the Home tab—the B, I, U buttons and the font size dropdown.
6. Add borders. In the Border tab, choose a line style and color, then click the border buttons (top, bottom, left, right, inside, outside) to apply. You can also use the Borders button in the Home tab for preset options like “All Borders” or “Outside Borders.”
7. Apply fill colors. In the Fill tab, choose a background color or a pattern. For a quick fill, click the paint bucket icon in the Home tab and pick a color. Use light fills for headers and subtle highlighting.
8. Use conditional formatting for automatic rules. Select your data range, go to the Home tab, and click Conditional Formatting. Choose a rule like Highlight Cell Rules > Greater Than to highlight values above a threshold. You can also use Data Bars or Color Scales for visual representation. Conditional formatting updates automatically as data changes.
9. Copy formatting with Format Painter. Select a cell that has the formatting you like, click the Format Painter icon (a paintbrush) in the Home tab, then click or drag over the cells you want to apply that formatting to. Double-click the Format Painter to apply it to multiple non-adjacent ranges.
10. Clear formatting when needed. To remove all formatting from cells, select them, go to the Home tab, click Clear (the eraser icon) and choose Clear Formats. This removes fonts, colors, borders, and number formats but keeps the values.

Common Mistakes

• Mixing up number formats with values. Formatting only changes how a number looks, not the underlying value. For example, formatting 0.5 as a percentage shows 50%, but the cell still contains 0.5. Be careful when using that cell in calculations.
• Using too many fonts or colors. A rainbow spreadsheet is hard to read. Stick to one or two fonts and use colors sparingly for emphasis.
• Merging cells excessively. Merged cells can cause issues with sorting, filtering, and copying. Use “Center Across Selection” (in Alignment tab > Horizontal dropdown) as an alternative that doesn’t merge.
• Applying borders to the wrong range. If you select a range and apply “Outside Borders,” you only get a border around the whole selection, not each cell. Use “All Borders” for individual cells.
• Forgetting to clear old formatting. When you copy data, formatting comes along. Use Paste Special > Values if you only want the numbers.

Tips

• Use Excel Table styles. Convert your data range to a table (Ctrl+T) to get automatic banded rows, filter buttons, and a consistent look. You can change the style from the Table Design tab.
• Create custom number formats. In the Format Cells dialog, go to Custom and enter codes like #,##0.00 for thousands separators and two decimals, or 0.0% for percentages. This gives you more control than presets.
• Use keyboard shortcuts. Ctrl+B (bold), Ctrl+I (italic), Ctrl+U (underline), Ctrl+Shift+$ (currency), Ctrl+Shift+% (percentage), Ctrl+Shift+# (date). These save time.
• Apply formatting to entire rows/columns. Select the row or column header before formatting so that new data added later automatically gets the same format.
• Use themes for consistency. Go to Page Layout > Themes to change the default fonts and colors across the whole workbook.

Troubleshooting

• Formatting doesn’t apply to new data. If you add rows below a formatted range, they may not inherit the formatting. Select the entire column or row before formatting, or use a Table (Ctrl+T) to auto-extend formatting.
• Number shows as #####. This means the column is too narrow. Double-click the column header’s right edge to auto-fit, or drag it wider.
• Conditional formatting rules overlap. If you have multiple rules, they may conflict. Go to Conditional Formatting > Manage Rules to see the order and priority. Use “Stop If True” to prevent lower rules from applying.
• Format Painter doesn’t work on merged cells. Merged cells can be tricky. Unmerge them first, apply formatting, then re-merge if necessary.
• Custom number format shows wrong result. Double-check your format code. For example, 0.00% multiplies the value by 100, so 0.05 displays as 5.00%. If you want to show the raw value with a percent sign, use 0.00"%".

Frequently Asked Questions

1. What is the difference between formatting and changing the value?
Formatting changes only how the cell displays. The underlying value stays the same. For example, formatting 0.25 as a percentage shows 25%, but the cell still contains 0.25. If you need the actual value to be 25, you must multiply by 100.

2. How do I apply the same formatting to multiple sheets?
Select all the sheets you want to format by holding Ctrl and clicking their tabs, then apply formatting to one sheet. The formatting will be applied to all selected sheets simultaneously. Remember to deselect the group when done.

3. Can I save my custom number format for future use?
Custom formats are saved with the workbook. To reuse them, you can copy the cell and paste only the formatting to another workbook, or save the workbook as a template (.xltx) and use it as a starting point.

4. Why does my conditional formatting not work when I copy the cells?
Conditional formatting rules are relative by default. When you copy cells, the rule’s references may shift. Use absolute references (e.g., $A$1) in the rule formula if you want it to stay fixed. Also, check the “Applies to” range in Manage Rules.

5. How do I remove borders but keep other formatting?
Select the cells, go to Format Cells > Border tab, and click “None” to remove all borders. This won’t affect fonts, fills, or number formats.

6. What is the quickest way to format a table nicely?
Select your data and press Ctrl+T to create a table, then choose a style from the Table Design tab. This applies alternating row colors, borders, and filter buttons automatically.

Related

• Block Pop-Ups in Windows Browsers: Safe Fixes
• Fix AMD Catalyst Control Center Missing on Windows
• Fix Chinese & Japanese Typing in Android SSH Terminals