Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Facebook Twitter Instagram
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Tech News Today
Home » Excel

Why is My Excel CONCATENATE Not Working? Here’s How to Fix It

Asmi DhakalBy Asmi DhakalAugust 6, 2022
excel-concatenate-not-working

Excel offers its users a range of functions to use in their workbooks. Among many functions with different features, the concatenate function in Excel combines data in multiple cells into a singular cell. Although the function is very simple to use, many new users may face issues in correctly using this function.

Unfortunately, if you face problems using the concatenate function, we’re here to help! In this article, we will be discussing why concatenate may not be working for you and how you can fix it! 

Table of Contents

  • Why is My Excel CONCATENATE Not Working?
  • How to Fix the CONCATENATE Function Not Working?
    • Use Correct Spelling
    • Use Correct Formatting
    • Disable Show Formula
    • Use Proper Syntax
  • Other Ways to Combine Data: Ampersand
  • More on CONCATENATE Function
  • Frequently Asked Questions (FAQ)
    • How Do You Activate the CONCATENATE Function?
    • Why Are the Ribbon Icons Greyed Out?

Why is My Excel CONCATENATE Not Working?

There are many rookie errors even the most advanced users of Excel may face when it comes to using the concatenate function. While entering a function, you must consider everything ranging from errors in typing to formatting your cell contents. Before sliding to the solutions, let’s examine why concatenate may not work.

  • Error in Typing: You may have accidentally misspelled the function. As the function’s name is comparatively longer, it can be tricky to spell it correctly.
  • Format: Many users may not consider the format of the cell contents while using Excel. However, this plays a vital role while using a function. Your function won’t work if you’ve set the cell format containing the formula as text. 
  • Show Formula is Enabled: Excel offers its users the feature to only show the formula. When this feature is active, Excel will display the formula used to calculate the content instead of displaying the calculated results.
  • Syntax Error: The concatenate function cannot pass arguments as a range. All arguments must be included with the comma symbol (,) separating each entity.

How to Fix the CONCATENATE Function Not Working?

It is quite simple to rectify the mistakes mentioned above. You can try solutions ranging from correcting spelling errors to correcting syntax. You can refer to the relevant solutions to the issue you suspect. Refer to the solutions mentioned below to fix the issue with the concatenate function in MS Excel.

Use Correct Spelling

Most users rush while entering functions they are familiar with. Especially in workbooks with large data, any user may have simply overlooked the spelling error. If you received the #NAME? error, you have mostly spelled the function wrong, or the cell you mentioned is empty.

Use Correct Formatting

Set the format of the cell with the concatenate function as General. When you correctly set the format, Excel can execute the command. Follow these steps to change the formatting of your cell to General:

  1. Launch MS Excel to open your workbook.
  2. On the Home ribbon, locate the Number section.
  3. Select the drop-down menu and choose General.ChangeFormatExcel

Disable Show Formula

You may have forgotten to disable the Show Formula tool on your workbook. You will only view the calculated result when you manually disable this tool on Excel. Here are the steps you can refer to disable Show Formula on MS Excel.

  1. Open your Excel Workbook.
  2. From the menu bar, select Formula.
  3. On the Formula ribbon, locate the Formula Auditing section. 
  4. If selected, deselect the Show Formula option to view the calculated results.
    Excel-Show-Formulas

Use Proper Syntax

You need to pass arguments individually while using the CONCATENATE function. Each argument must be separated using the comma (,) symbol. The function will be invalid if you’ve used any other symbol or passed the arguments as a range. Similarly, all functions begin with the equals sign (=). Be sure to have used all of these correctly.

Here is an example of how the CONCATENATE function is properly used:
=CONCATENATE(A2,B2,C2,“text”)

Excel-Concatinate-Syntax

Other Ways to Combine Data: Ampersand

You can also use Ampersand (&) to combine data from multiple cells into one. Unlike CONCATENATE, Ampersand is an operator. In comparison to other functions, users find the operator more convenient to use as it leaves less room for syntax error and saves more time. 

Here is how you can use the Ampersand to combine your data:
=A2 & B2

AmpersandExcel

There are other combining techniques in MS Excel besides the CONCATENATE function and Ampersand. Learn more about how you can combine cells in Excel without losing data.

More on CONCATENATE Function

Like every function, the CONCATENATE function has its own set of protocols you must remember when in use. When using this function, you will leave less room for error when you keep these rules in mind. 

Below, we’ve listed all the things you need to keep in mind while using the CONCATENATE function:

  • You cannot pass more than 255 strings using a single function.
  • You cannot pass more than 30 arguments in a single function.
  • Excel will display the #VALUE error if any argument is wrong.
  • You must pass at least one argument in text format to return a value.
  • The result passed by the function is always a text string.
  • You cannot pass arguments as a range. 
  • Texts need to be kept inside quotation makes in the CONCATENATE formula. However, this does not apply to numbers.

Frequently Asked Questions (FAQ)

How Do You Activate the CONCATENATE Function?

If you want Excel to register your data as a function, you must use the equals sign (=) in the beginning. Similarly, the CONCATENATE function only works if you add an equal (=) sign before it; =CONCATENATE(

Why Are the Ribbon Icons Greyed Out?

Most ribbon icons get greyed out when you enter the ‘Editing Mode’ in Excel. You may have accidentally hit the F2 button on your keyboard that triggered this change. To revert this, you can simply select a different cell in Excel. 

fix
Asmi Dhakal
  • Twitter
  • LinkedIn

Asmi Dhakal is a Tech Writer specializing in Microsoft Office-based programs at TechNewsToday. She aims to use her experience to help others learn their way around its programs. Asmi’s interest in Office products grew when she first entered the marketing industry. While she initially struggled to understand their application, she eventually understood her way around them. In her articles, she exhibits a strong understanding of related issues and attempts to push only relevant fixes. Over time, she has grown a particular fondness for MS Excel and spends her free time learning Visual Basic Analysis (VBA). She’s always on the lookout for productivity tools that can get the job done quicker in Excel. Her approach to related problems is very humane, helping new users not only fix the issue but learn more about its nature. Asmi also heavily indulges in personal writing. She is a disciplined journal writer and has maintained a regular journal for 8 years! When her schedule allows, she works to rehabilitate street animals. You can contact her at asmi@technewstoday.com

Related Posts

check mark in excel

How to Put Check Mark in Excel

January 20, 2023
best excel shortcuts

Best 52 Excel Shortcuts Only Experts Know

January 19, 2023
How to Find and edit Hyperlinks on Excel

How to Find and Edit Hyperlinks on Excel

January 23, 2023
how to find unique values in excel

How to Find Unique Values in Excel

January 15, 2023
how to find links in excel

How to Find and Edit External Links in Excel

January 13, 2023
reference isn't valid excel

Reference Isn’t Valid Excel Error – 6 Ways to Fix it

January 6, 2023
Add A Comment

Leave A Reply Cancel Reply

Latest Posts
how to clean RAM

How to Clean RAM? Step-By-Step Guide

October 2, 2023
motherboard form factors

Motherboard Types: ATX, Micro ATX, and Mini ITX Explained

September 28, 2023
how to download and use rufus

How to Download and Use Rufus

September 28, 2023
You may also like
printer-not-printing

How to Fix a Printer That’s Not Printing

September 26, 2023
Share Location With Friends and Family

Share Location With Friends and Family (iPhone and Android)

September 18, 2023
how-to-print-without-a-printer

How to Print Without a Printer

September 18, 2023
Recommended
Cookie Clicker Garden Guide

Cookie Clicker Garden Guide to Unlocking Every Seed

September 26, 2021
monitor no signal

Computer Turns On But Monitor Says No Signal (9 Ways To Fix)

November 10, 2022
Facebook Twitter Pinterest
  • Home
  • About Us
  • Our Team
  • Editorial Guidelines
  • Privacy Policy
  • Affiliate Disclosure
© 2023 TechNewsToday, editor@technewstoday.com | Tech Central Pvt. Ltd.

Type above and press Enter to search. Press Esc to cancel.