Why is excel not letting me share?

Paul has a worksheet in which he wants to apply Merge and Center to a selection. However, the tool is continually grayed out on his worksheet and Paul can't figure out why. He wonders if there is some combination of formats or settings that disables the tool.

Actually, there are at least three conditions that can cause the Merge and Center tool to be unavailable. You should check, first, to see if your worksheet is protected. Second, you should check to see if the workbook is shared. Both protection and sharing have been discussed in other issues of ExcelTips, and both of them happen to disable Merge and Center. (Actually, they disable a lot of other commands in addition to Merge and Center.)

If you turn off sharing (if it is on) and disable protection (if the worksheet is protected), then the tool should once again be available.

The third potential cause is if the cells you've selected are in a table, instead of a range of cells. If the cells are part of a formatted table (the Format as Table tool, on the Home tab of the ribbon was used), then you cannot merge cells within the table. The only solution in this case is to convert the table to a range, and then you can once again access the tool.

In many instances, your best bet is to simply not use the Merge and Center tool. Instead, check to see if Center Across Selection will do the trick for you. Many Excel users (and most longtime Excel users) consider Center Across Selection to be far superior to Merge and Center. You can use this setting by selecting the cells you want to center across and then using the Horizontal drop-down list on the Alignment tab of the Format Cells dialog box.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11845) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Merge and Center Not Available.

Author Bio

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen...

MORE FROM ALLEN

Throwing Out the Lowest Score

Want to add up a bunch of scores, without including the lowest one in the bunch? You can make a small change to your ...

Discover More

Conditional Calculations in Word

Word allows you to insert simple formulas, using fields, in table cells. You can also create simple conditional ...

Discover More

Drawing Lines

Excel doesn't limit you to only numbers and text in your worksheets. You can also add different types of shapes. Here's ...

Discover More

Many times you may wonder why your Excel file cannot be saved and what is the cause for it. In this article, we will be discussing some common causes that conflict with your Excel file and the measures to resolve it. So, let’s get started.

Sharing Violation – Introduction

Generally, a sharing violation occurs when a particular file or document is being used by another program. In other words, a file being read by another user causes a sharing violation. This problem is not something new, as it dates back to earlier versions of Excel as well. The error statement clearly reads- Your changes could not be saved to ‘ FileName’ because of a sharing violation. In many cases due to such issues, saving Excel spreadsheet files can not be successful.

The below image shows the error statement that appears when there is a sharing violation.

The primary cause for this error is when files are not saved with proper extensions that are specified by the file extension itself. This causes an error at the time of saving so when a file is saved as .xlsm instead of .xml a violation box pops up indicating an error.

There are various causes that would stall the enabling the option to save an Excel file. Below we discuss how to resolve these.

• Authorize Indexing for file folders
• Deactivate third party Antivirus program
• Permitting Sharing Wizard
• Save file to a different location
• Rename file

Let us discuss in detail the ways to resolve various causes of Sharing Violation:

1. Authorize Indexing for file folders:

Indexing is one of the common causes of sharing violations. This problem can be managed by modifying the Advanced Attributes of that particular folder so that the files are permitted to get indexed. The following are the steps to activate this option

Step 1: Click the folder containing error >> select Properties from the context menu.

(Note: Close all the Excel files and Microsoft Office running in the background).
Step 2: From Properties box >> select General tab >>go to Attributes section >> click on Advanced button

Step 3: Click OK, the Advanced Attributes box appears.

Step 4: In Advanced Attributes box >> go to the Archive and Index attributes >> check the box with Allow files in this folder to have contents indexed in addition to file Properties. Click Ok to save the changes.

2. Deactivating third-party antivirus program:

If the third-party antivirus is on the run it is important to deactivate them as it takes over the Excel file when trying to save it. It can also be caused by an overprotective antivirus suit. This issue is generally associated with Comodo AVG, Avast, McAfee, and also the Premium version of Malwarebytes. Deactivating the real-time protection might increase the chance of saving a file without error.

If the process becomes unsuccessful and the error is still displayed, deactivate the antivirus when working in Excel or uninstall the AV completely and you can enable Windows Defender.
The below image shows the Windows Defender’s home page after being installed.

Note: The following steps will guide you to uninstall any antivirus software.
Step 1: Press Windows key + R to open the Run box.
Step 2: Type ‘appwiz.cpl’ in the Open box, and open the Windows uninstaller.

3. Permit share wizard:

There are high chances of getting an error statement when share wizard option is not permitted. It is important to permit this feature in order to process efficiently without error.
The following steps will show how to enable the Sharing Wizard feature via the Folder Options menu.

Step 1:Press Windows +R key and type “control folders” in the Run open box >> Click Enter to open the Folder Options menu.

Step 2: Folder Option appears >>select View tab >> Advanced Settings >> scroll till you find  Use Sharing Wizard (Recommended). Check Use Sharing Wizard (Recommended) >> click Apply to save the order.

Step 3: Reboot your systen and check if the issue is solved.

4. Save file to a different location:

You can also move the Excel file to a different folder or a new folder to avoid errors. Delete the original file after moving it to another location. Later, transfer the altered file in the original folder. To save a file in a different folder the following steps will guide you,

• Select File tab >> select Save As option >> select a different folder/a new folder >> click Save button.
  In the below image you can see how the original file can be saved to a different location or folder.

5. Rename file:

This is a simple way to fix a sharing violation problem. Rename the error occurring file to something else and save it. For Example, if the legitimate file is named as ScannedDocument150 it can be renamed as ScannedDocument350. After some time, delete the legitimate file and rename the original to copy back. This process should resolve your issue.

We have discussed thus some of the reasons that cause sharing violations and how to resolve them. To know more on topics related to Excel

Subscribe to our YouTube channel for regular updates.
To learn more Excel Formulas Click Here.

How do I enable sharing on an Excel spreadsheet?

Why is Share workbook not working?

Why can't others edit my Excel spreadsheet?