• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

10 Mistakes to Avoid With Excel Formatting

You are here: Home / Excel / 10 Mistakes to Avoid With Excel Formatting
10 mistakes to avoid when using excel formatting
June 6, 2023 by Mynda Treacy

Excel Formatting Best Practices

Proper use of Excel formatting can significantly impact the speed and clarity in which users can work with and interpret your spreadsheets.

It enables you to highlight essential information, direct users, and encode data. Effective formatting subtly enhances usability, while poor formatting can be a distraction.

These are my top 10 GRIPES I see time and again.


Table of Contents

  • Number alignment
  • Comma separators
  • Rounding
  • Currency Symbols
  • Merged Cells
  • Angled Text
  • Zoomed Out with Massive Font
  • Too Many Fonts
  • Too Much Colour
  • Cell borders EVERYWHERE


Watch the Video : 10 Mistakes to Avoid With Excel Formatting

Subscribe YouTube

Number alignment

By default, in Excel numbers are aligned right and text aligned left. Don’t mess with numbers by centring them. Keep the ones, tens, hundreds etc. lined up so they’re easy to read:

number alignment

Comma separators

If you have numbers in the thousands or higher, put a comma separator in them. It makes them instantly easier to read:

comma separator

Rounding

If the numbers are greater than 1000, the decimals are immaterial. Round them so the important information is easy to see:

rounding

Currency Symbols

Repetition of currency labels just adds unnecessary noise. Instead, label the header of the column. And if there’s a chance it could be confused with other currencies that share the same symbol, add the currency name:

currency symbols

Merged Cells

Instead of merging cells, use Centre Across Selection. This way you still have the aesthetic of a centred label, without the inconvenience merged cells causes when selecting ranges interspersed with merged headers.

In the image below the tables in columns B:D and F:H look the same, but the tables in B:D have merged headers, whereas the tables in F:H use ‘Centre Across Selection’ to align them:

merged cells

You’ll find the ‘Centre Across Selection’ option in the Format Cells dialog box on the Alignment tab:

alignment tab

In the animation below you can see the hassle Merged Cells causes when trying to sum the column of data compared to the easy when using Centre Across Selection:

merged cells

Angled Text

Avoid making people turn their head to read labels where possible. The tables in columns B:D are more time consuming to interpret because the region labels are off to one side and require more effort to read than those in columns F:H, which allow the reader to read in a logical, top to bottom order:

angled text

Zoomed Out with Massive Font

I’ve never worked out why people zoom way out on a worksheet, only to then increase the font size so they can read it, but I see it a lot.

The only thing it achieves is smaller font in the column and row labels, but they hardly take up that much space anyway.

zoomed out with large font

If you must zoom out, simply stop when you can still read the font.

Too Many Fonts

Too many fonts, serif fonts, comic sans and the like are all inappropriate for most business scenarios.

Keep it simple and stick to no more than three different fonts in a spreadsheet.

Choose fonts that are easy to read and complement one another. Or instead of different fonts, use different font sizes or bold to differentiate headings etc.

too many fonts

Too Much Colour

The image below is an anonymised example of a real workbook I was sent by someone asking for Excel help. The coloured headings are way too much. Plus, the pink and red fill colours make the font very difficult to read. It makes me want to wear sunglasses to view it:

too much color

Colour is a great tool to categorize data and communicate information, but you can do it with subtle colours too:

subtle color choice

Or consider whether you need every column coloured because when everything is coloured, nothing stands out:

minimize color use

Cell borders EVERYWHERE

By default, Excel has gridlines turned on, so there’s no need to add cell borders to a table. These extra borders unnecessarily bloat your file and create a busy workspace.

too many cell borders

If you want to hide the gridlines for the header area, apply white cell fill, and leave the gridlines in place for the table area:

minmize borders on cells

This will result in a much smaller file and you won’t have the hassle of gridlines disappearing if you cut and paste a cell.

And if you want to see gridlines when you print, you can turn them on in the page layout settings:

page layout settings

That wraps up my pet peeves. Did I miss any formatting habits you find annoying? Please share them in the comments.

10 mistakes to avoid when using excel formatting
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Posts

8 Excel in-built AI Tools

Built-in Excel AI Tools

Clean, analyze and visualize data with these Official Excel AI tools by Microsoft. Including formula writing, PivotTables, Charts and more.
excel date and time

Excel Date and Time

Everything you need to know about Excel date and time. Includes comprehensive workbook with every date and time function, plus PDF version.
linear regression

Excel Linear Regression

Excel linear regression is easy with the built-in tools. Use charts to plot linear regression or use the Data Analysis Toolpak.
speed up slow excel files

How to Improve Excel Performance

How to improve Excel performance and the various causes of slow Excel files so you can speed up Excel and avoid problems in future.
Securely Share Excel Files

Securely Share Excel Files

Securely share Excel files stored locally, on OneDrive or SharePoint. Prevent editing or downloading, specify who can open and edit the file.
excel check boxes

Interactive Excel Check Boxes

Excel check boxes are interactive elements you can link to formulas, charts, conditional formatting and more.
tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.


Category: Excel
Previous Post:excel formula by exampleExcel Formula by Example
Next Post:Excel Scroll and Sort Table

Reader Interactions

Comments

  1. Tom W Van Dam

    June 7, 2023 at 12:08 am

    I agree with the gridline comment. A lot of the users at my work (especially the boss) use the gridlines everywhere which makes it very difficult to view. My take is to put a border on the right of each column that I am using and then put a dotted line border (the one just below the none option). I then put solid border on the bottom of the row where my headings are. When printing, this doesn’t overpower the user and helps to differentiate the columns and cells. Yes, I do have to fix some things when copying or pasting but it doesn’t usually happen very often.

    Reply
    • Mynda Treacy

      June 7, 2023 at 11:05 am

      Sounds like you could create a custom table style and use that to speed up your formatting, Tom.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x