• 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
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Cell Formatting Tips & Tricks

You are here: Home / Excel / Excel Cell Formatting Tips & Tricks
Excel Cell Formatting Tips & Tricks
January 7, 2011 by Mynda Treacy

Here are some easy solutions to Excel cell formatting frustrations that I get asked about all the time.

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the workbook used in this tutorial here. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

1. Force text to wrap to the next line

How often do you add extra spaces between your text to force it to wrap just the way you want, only to change the column width and to have to do it all over again?  It’s frustrating and a waste of time.

Thankfully there’s a better way to get your text to wrap to the next line in Excel.  Simply press ALT+Enter after each word you want to wrap.  This forces the text onto the next line.

Tip: don’t add a space after the last word on each line otherwise it won’t be centered properly.

excel force text to wrap onto next line

2. To Merge & Center or To Center Across Selection

How often do you get that error message ‘Cannot change part of a merged cell’?

Or have you ever been in the process of highlighting a series of cells and all of a sudden your mouse scrolls over some merged cells and BAM, the area you’re highlighting is three times the size you want?

I’m sure you’ve had moments where you’re cursing the merged cells for one reason or another.  Well there’s a better way to merge cells too.

And it’s called Center Across Selection.  To use Center Across Selection:

a. Enter your text in the left most cell that you want your heading in

b. Highlight the cells you want your heading centered across

c. CTRL+1 to open the Format Cells dialog box

d. On the Alignment Tab select Center Across Selection from the Horizontal drop down list

excel cell formatting center across selection

The differences between Merged Cells and Center Across Selection is:

  • Merged Cells converts multiple cells into one big cell.
  • Center Across Selection keeps the underlying cells as individual cells and simply centers the text for you.

The limitation of Center Across Selection is that in can only center across one row because it’s simply horizontal text alignment.  Ah, but there’s a solution to this if you really want your headings to straddle more than one row.

3. Excel Shapes As An Alternative to Merge & Center

Excel shapes an alternative to merge & center

Shapes can be found on the Insert tab under Illustrations.

a. Choose the shape you want

b. Drag the mouse pointer to draw the shape the size you want

c. Format it using the new ‘Format’ tab that appears when the shape is selected

d. Write your text in the shape and format your text as you normally would

You can see in the example above that the shape appears to be floating above the cells.  This is partly because it has a shadow effect, but also because it actually is floating.

You can still type text underneath the shape (a handy place to put information required for the worksheet, but not required for the reader of the report).

I’m going to do a tutorial dedicated to Shapes, so I’m not going to say much more here. But needless to say, shapes are fast becoming one of my favourite features in Excel for making my reports look professional, while at the same time eliminating the restrictions of Merge & Center.

4. Bonus Excel Cell Formatting Tip – Fill

While fiddling with the cell formatting options I stumbled upon ‘Fill’ in the Alignment Tab.

excel fill cell formatting

You can see in the formula bar that I’ve only typed in ‘Fill’ once and Excel has repeated it across the selection.  Note: I actually typed in ‘Fill ’ with a space after it so you could read it without going cross-eyed! Mind you, I’m not sure it helped all that much 🙂

excel cell format Fill

At first I couldn’t think of why I’d use the Fill formatting style, but then I thought it would be handy to use it as a dot leader.................................................like this.

Or a star leader ********************************like this, and so on.

Of course this would require you to have at least three columns. The first one with your text in it, the next one with your dots or stars, or whatever you choose, and the third one with your text in.

Alternatively you could use one of the custom cell formats in one of my previous tutorials.

I must say it has been quite a challenge to spell 'center' with an 'er' rather than an 're' since I speak English English, not American English. So in case you're wondering, I did this simply to be consistent with the spelling in Excel 🙂

Got any formatting tips you'd like to share? Please tell us in the comments below.

Excel Cell Formatting Tips & Tricks

More Excel Posts

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.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.


Category: Excel
Previous Post:Short Survey, Win a PrizeCalculating Time in Excel
Next Post:How to Use Excel Conditional FormattingHow to Use Excel Conditional Formatting

Reader Interactions

Comments

  1. Watson

    April 20, 2013 at 12:00 am

    Mynda l am getting a lot of help in excel from your on line training hub.

    Many thanks always

    Reply
    • Mynda Treacy

      April 21, 2013 at 7:18 pm

      You’re welcome, Watson 🙂

      Reply
  2. Mario T

    October 3, 2012 at 11:45 pm

    Can anyone assist with UNDOING or REMOVING a forced wrap command (ALT+ENTER) from a string of text? We have a data base that outputs the entire customer address into one cell (up to 4 lines) and I’m trying to break out zip code and state. I think the first step is unwrapping the data string.

    Thanks for any ideas!

    Reply
    • Mynda Treacy

      October 4, 2012 at 2:41 pm

      Hi Mario,

      There are two ways:

      1. Do a Find & Replace of the character. To do this press CTRL+H to open the Find & Replace dialog box > In the Find field hold down the ALT key while you enter 010 > in the Replace field enter a space or other character as you wish.

      2. Use the SUBSTITUTE function. =SUBSTITUTE(A1,CHAR(10),” “) where A1 contains the text you want replace, CHAR(10) is the carriage return you want removed and ” ” is replacing it with a space.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  3. Frank

    August 22, 2012 at 12:46 pm

    Came across this blog after unsuccessful search for an answer to the following. I see that the last post was 2011, but thought my question might be relevant.
    I use merge cells and word wrap to format text in a large, multi-sheet workbook. I often have to type a series of words, like the type of material, separated by “/”, e.g. metal/glass/composite/…. . Word wrap puts the whole sequence on a new line if it does not fit on the current line. How can I get Excel to break the line at the “/”? My client does not want “metal / glass / composite / ” and entering a space (or Alt+Enter) to create a break at the right spot would be tedious even if I could do this, but I can’t because Excel does not print the same as it formats on screen so I don’t know where to put the space to get the correct wrapping (unless I flip back and forth at each occurrence to see it in print preview – even more tedious).
    Thanks for your help.
    Frank

    Reply
    • Mynda Treacy

      August 22, 2012 at 1:09 pm

      Hi Frank,

      Thanks for your question. If you force a break using ALT+ENTER it will print the same as it looks on the screen. I’m not aware of any custom cell format that will wrap text at the “/” characters, sorry.

      If you force a carriage return using spaces that’s when you get inconsistent views between printing and screen. ALT+ENTER is your solution, albeit tedious.

      BTW, the last post was last week. You can see the latest posts on the blog.

      Kind regards,

      Mynda.

      Reply
  4. Nancy

    April 12, 2012 at 10:40 pm

    thx

    Reply
  5. Mynda

    May 9, 2011 at 9:30 pm

    This tip is courtesy of Margaret Burt.

    For those of you who use Excel on a Mac you can force a carriage return with the Command + Control + Enter keys.

    Thanks for your input Margaret.

    Reply
  6. Jim Morley

    April 15, 2011 at 1:12 pm

    Thanks Mynda, Looks great I will give it a try and let you know how I go with this. Thanks for the prompt reply.

    Jim

    Reply
  7. Jim Morley

    April 14, 2011 at 6:00 pm

    CAN ANYONE TELL A NEWBY HOW TO CREATE A CELL FORMULA THAT KEEP THAT CELL BLANK UNLESS DATA IS ENTERED INTO ANY CELL OF A RANGE OF OTHER CELLS IN THE SAME SPREADSHEET? FOR EXAMPLE I WANT TO HAVE CELL A15 STAY BLANK AND ONLY DISPLAY CERTAIN (PRE DETERMINED) TEXT IF DATA IS ENTERED INTO CELLS A1 TO A10 OF THAT SPREADSHEET. I CAN DO IT USING THE IF FUNCTION BUT ONLY TO A SINGLE CELL NOT TO A RANGE OF CELLS.

    Reply
    • Mynda

      April 14, 2011 at 8:46 pm

      Hi Jim,

      Yes you can, and yes you use an IF statement.

      Let’s say your cells (A1:A10) contain text, as opposed to numbers, you could use the COUNTA function to count if any cells contain text. And if they don’t then leave the cell blank, but if they do then enter “your text”. Like this:

      =IF(COUNTA(A1:A10)=0,””,”enter this text”)

      Translated the formula above reads:

      =IF(the number of cells in the range A1:A10 that contain text is = 0, then put nothing in the cell, otherwise put the words “enter this text”)

      Note: if cells A1:A10 contain numbers then replace COUNTA with COUNT.

      Let me know if that helps.

      Regards,

      Mynda.

      Reply
  8. Adrian Young

    March 31, 2011 at 8:38 pm

    Hi. Re: Force Wrap Text, I must be doing something wrong, because when i press CTRL+ENTER it doesn’t work???

    Reply
    • Adrian Young

      March 31, 2011 at 8:43 pm

      I just googled it, and it’s actuallt ALT+ENTER :o)

      Reply
      • Mynda

        March 31, 2011 at 9:07 pm

        🙂 Oops, you’re right. It’s one of those things I do on auto pilot but when I have to think about it get’s all mixed up. I’ve corrected the post. Thanks for letting me know.

        Reply
  9. john quin

    January 9, 2011 at 5:26 am

    thanks, very informative..

    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...

Featured 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

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

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 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
 

Company

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

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.