K2E Canada Inc
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • TECHNOLOGY CONFERENCE
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • TECHNOLOGY CONFERENCE
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG

Solve Formatting Issues in Excel with Center Across Selection

4/1/2020

 
Many Excel users struggle with formatting their spreadsheets to give them a polished and professional appearance. Among the challenges these users face is how to center text over multiple columns.  Learn how you can use Excel’s Center Across Selection feature to solve this problem quickly and easily in this tip. Also, learn how Center Across Selection differs from Excel’s Merge & Center feature, which often results in “downstream” formatting issues.

Understanding the Issue

Suppose you have created a report like the one presented in Figure 1. In this report, you would like to center the “header” information in cells A1 through A3 so that it “stretches” to column F. You would also like to center each of the column headings in cells B5 through F5 within their respective columns.
Picture
Figure 1 - Sample Report for Centering Text

Regarding centering the column headings on row 5, that is an easy task. Select those cells and click the Center icon in the Alignment group on the Home tab of the Ribbon. It’s centering the heading that causes problems for many Excel users.

Why Merge & Center Can Be Problematic

To center text across multiple columns, it is tempting to use Excel’s Merge & Center feature. If you choose to work with this tool, select the text you want to be centered and the range over which you want to center it. Then from the Alignment group in the Home tab of the Ribbon, click Merge & Center. Upon doing so, the text you selected centers over the range you selected, as shown in Figure 2.
Picture
Figure 2 - Using Merge & Center to Center Text Over Multiple Columns

At first glance, this method appears to be the perfect solution. However, there are some downsides associated with Merge & Center. Among the more significant of these are the following.
  1. Once you use Merge & Center, you cannot insert columns into the merged range.
  2. If you use Merge & Center, you cannot sort data within the merged range.
  3. Merge & Center limits your ability to paste data into the merged range.

For the reasons outlined above, and perhaps others, in most cases you should avoid using Merge & Center when formatting your spreadsheets. Instead, use Center Across Selection.

Center Across Selection Is the Better Solution

Fortunately, Excel offers an alternative to merging cells for formatting effect, and that solution is Center Across Selection. When you use Center Across Selection, your formatting results appear the same as if you had used Merge & Center. However, you do not experience any of the adverse side effects of Merge & Center.
To format your data using Center Across Selection, begin by selecting the text and the range over which you want it centered, as shown in Figure 3. Importantly, note that you can select text from multiple rows when formatting with Center Across Selection. That is a significant difference between this feature and Merge & Center.
Picture
Figure 3 - Selecting Text for Center Across Selection

After you select your text and the range over which you want it centered, click the dialog launcher in the Alignment group on the Home tab of the Ribbon. Then choose Center Across Selection in the Horizontal list and click OK to complete the process. Figure 4 illustrates the steps for applying the Center Across Selection format.
Picture
Figure 4 - Applying Center Across Selection

Once you click OK to complete the process shown in Figure 4, you see that the data you selected centers over the range that you also selected. Further, you can sort the data, insert columns into the range if necessary, and paste data into the range without restrictions. In short, when you use Center Across Selection, you do not experience any of the adverse side effects associated with Merge & Center.

Summary

Merge & Center remains a popular feature in Excel, probably because of its’ easy access on the Home tab of the Ribbon. However, when you use Merge & Center, you open the door to other formatting and data manipulation issues, such as inserting columns into the range. Therefore, as an alternative, consider using Center Across Selection instead. Although Center Across Selection is a little more challenging to find in Excel’s user interface, it provides superior results compared to Merge & Center, particularly considering there are no negative consequences associated with Merge & Center. So, the next time you need to center text over multiple columns, give Center Across Selection a try. I’m confident in your response to the results!

Tommy Stephens


Comments are closed.

    Categories

    All
    2022
    Accountant
    Accounting Software
    Accounting Solutions
    Adobe
    Advisory
    AI
    Artificial Intelligence
    Automation
    Backup
    Bitcoin
    Blockchain
    Business Automation
    Business Continuity
    Business Intelligence
    Business Management
    Cloud Computing
    Collaborate
    Collaboration
    Colonial
    Computer
    COVID 19
    COVID-19
    CPA
    CRM
    Cryptocurrency
    Customer Relationship Management
    Cybersecurity
    Cyptocurrencies
    Dext
    Doc.It
    Ecommerce
    Emerging Technologies
    Entrepreneurs
    Excel
    Forecasting
    Forecast Sheet
    Google
    Grammarly
    HR
    Internal Controls
    Internal Priorities
    KPIs
    LET
    Links
    Management Reports
    Microsoft 365
    Microsoft Office 2021
    Microsoft Teams
    Office
    Office 365
    Online Shop
    Outsourcing
    Pandemic
    Paperless
    Personal Computer
    PivotTables
    Power Automate
    Power BI
    PowerPoint
    Productivity
    QuickBooks
    Quickbooks Online
    Ransomware
    Receipt Bank
    Remote Work
    RPA
    Sage 50
    Security
    Small Business
    Small Business Accounting
    Small Business Accounting Software
    Stockhistory
    Technologies
    Technology
    Tech Update
    Windows
    Windows 10
    Windows 11
    Word
    Work At Home
    Workflow
    Work From Home
    Xcm
    Zoho
    Zoho CRM

    Authors


    Ward Blatch
    Ward provides consulting and training services as the Managing Director of K2E Canada Inc. He joined K2E Canada in 2005 and is responsible for the Canadian operations of this international consulting group, which provides professional development technology education for accountants across Canada and the US. Ward lives in rural Nova Scotia and can be reached at ward@k2e.ca.

    Tommy Stephens
    Tommy is one of the shareholders in K2 Enterprises, affiliating with the Firm in 2003 and joining as a shareholder in 2017. At K2, Tommy focuses on creating and delivering content and is responsible for many of the Firm's management and marketing functions. Tommy resides in the metro Atlanta area. You may reach him at tommy@k2e.com.

    Randy Johnson
    Randy is a nationally recognized educator, consultant, and writer with over 40 years experience in Strategic Technology Planning, Accounting Software Selection, Paperless, Systems and Network Integration, Business Continuity and Disaster Recovery Planning, Business Development and Management, Process Engineering and outsourced managed services. Randy can be reached at randy@k2e.com


    Bernie Smith
    Bernie coaches businesses to develop meaningful KPIs and present their management information in the clearest possible way to support good decision making. As the owner of Made to Measure KPIs, he has worked with major organisations including HSBC, Airbus, UBS, Barclays, Credit Suisse, Lloyds and many more.

    RSS Feed

Training & Education

Webinars
Road to Excellence Online Seminar
Seminars
Conferences
Instructors

Tech News

K2E Canada's Tech Update Newsletter

More

Privacy Policy
About
Contact

K2E Canada Inc.  |  484 Scarlett Crescent  |  Burlington, ON L7L 5M2  |  (902) 200-9207
© 2022 K2E Canada Inc. ALL RIGHTS RESERVED.