Filter Not Working Error in Excel [Fix 2024]

Summary: The filter is not working issue in Excel can occur due to several reasons, like blank rows, hidden rows, merged cells, corrupted data, etc. In this post, we will mention the reasons why the filter is not working correctly in Excel and several fixes to resolve the issue. We will also mention an advanced Excel repair tool to repair the Excel file if corruption in file is the cause of the issue.

You can use the Filter function in Excel to filter data in large-sized Excel files quickly. While using Excel filters, sometimes, you face a situation where the filter is disabled or may fail to function properly.

The Excel filter usually fails to work if you have not selected the complete and correct range of data. Let’s learn more about the “Sort and Filter not working in Excel” issue and look at the possible methods to fix it.

Why the Filter is not Working in Excel?

You can face the “filter is not working” issue if you are applying the filter on a protected worksheet or trying to find the data from a hidden row. Besides this, there could be many other reasons contributing to this issue, such as:

• The data you are trying to filter is in merged cells.
• The Excel file automatically selected the data up to the first empty cell, excluding the remaining rows.
• Grouped sheets in Excel file.
• Blank row in the Excel sheet.
• You are trying to apply a filter on an invalid data range.
• The workbooks in which you’re facing the filter issues are corrupted.
• You are specifying incorrect criteria in the filter columns.

Solutions to Resolve the Filter is not Working Issue in Excel

There might be two scenarios: the Excel filter option is disabled/grayed out or the filters fail to function properly. You can follow the given troubleshooting solutions to resolve the issue based on the scenario you’re facing.

Scenario 1 – Filter Option is Disabled or Grayed Out

Method 1: Check and Un-group the Worksheet

When you apply filters to a single sheet in a grouped set, Excel disables the filter option in other sheets within the group. You can check the grouped sheets and try ungrouping them to enable the filter option. Here’s how to do so:

• In the Excel file, go to the Group section.

• Right-click on the Ungroup Sheets.

Alternatively, you can press the Shift + Alt + Left keys to ungroup the sheets.

Method 2: Unprotect Worksheet

The “disabled Excel filter” issue can also occur if your worksheet is protected. You can unprotect the worksheet to enable the filter option. To do so, go to the Review tab and then select Unprotect Sheet.

Method 3: Check and Uninstall Excel Add-ins

Sometimes, the Excel filter gets disabled due to faulty or corrupted Excel add-ins. You can run the Excel in Safe mode to check whether the issue has occurred due to add-ins. To do this, type excel /safe in the Run window and click OK.

In safe mode, if you see the filter option, it indicates some problematic Excel add-ins were causing the issue. In such a case, you can check and uninstall the faulty Excel add-ins to fix the issue.

Scenario 2 – Filter is not Working

Method 1: Try Clearing Filters

Sometimes, the Excel filter fails to work correctly if some filters from the previous sessions are still active. In such a case, you can clear the applied filters. Follow the below steps:

• In Excel file, click Sort & Filter option.
• Select clear.

Method 2: Select Entire Data

The filter not working issue in Excel can occur when the range selected for filtering is incomplete or incorrect. You need to make sure that you’ve selected the entire data range in Excel. You can use the Ctrl+A keys to select the entire content in the worksheet.

Method 3: Check and Delete Blank Cells from the Table’s Columns

When you apply a filter to the data, Excel expects data to be in a continuous range. Excel filters do not consider the blank cells, thereby resulting in incorrect functioning of the filter. To resolve this issue, check and delete all blank cells. In case your Excel file is too large to delete the blank cells, then you can add a “Serial number” row as an alternative. Adding serial number row creates a data continuity, thus helping in fixing the filter-related issue.

Method 4: Unhide Hidden Rows and Columns

Hidden rows or columns in worksheets can also affect the filter functionality. You can check and unhide rows/columns to troubleshoot the issue. Here is how to do so:

• In the affected Excel file, go to Home.
• Click on Format > Hide & Unhide.

• Click Unhide Rows or Unhide Columns (as required).

Method 5: Unmerge Cells

You can experience the filter in Excel is not working issue if you are using the filter to extract data from merged cells. Ensure to unmerge the “merged cells” before applying a filter in Excel. Follow the below steps to unmerge the merged cells in Excel:

• Navigate to the Home option.
• In the toolbar, select the Merge & Center option.
• Click Unmerge Cells.

Method 6: Repair the Workbook

Sometimes, the Filter Not Working in Excel issue can occur due to inconsistencies in file structure. If these issues occurred due to corruption in the worksheet, you can repair it using the Open and Repair tool. It is an in-built tool in Excel that is used to repair corrupted Excel files. Here are the steps to use this tool:

• In the Excel application, navigate to the File option.
• Click Open and then click Browse to choose the Excel file.
• In the Open dialog box, click the problematic Excel file.
• Click the arrow next to the Open option and select Open and Repair.
• Click Repair to recover as much data as possible.
• The application prompts a message after the repair process is complete. Click Close.

In most cases, the Open and Repair tool can easily fix corruption issues in the Excel file. However, for any reason, if the open and repair tool doesn’t work you can consider repairing the file using a professional Excel Repair tool. Stellar Repair for Excel is one such advanced and secure tool to repair Excel files. With this tool’s powerful scanning capabilities, you can repair highly corrupted Excel files and recover all their objects with complete integrity. The tool is compatible with all Windows editions, including the latest Windows 11.

Closure

Several reasons are associated with the filter not working issue in Excel. The filter option may not work as expected if you have not selected the complete and correct range of data or for many other reasons. You can follow the troubleshooting methods discussed above to fix the issue. If the filter fails to work due to corruption in the workbook, then try Stellar Repair for Excel . It is an advanced tool that can even repair severely damaged files. It also helps to recover all the data from corrupted files without changing the original formatting. You can check the tool’s functionality by downloading its demo version. It allows you to preview all the repairable objects in the corrupted Excel file.

How to repair corrupt Excel file

Stellar Repair for Excel is an excellent tool to repair corrupt or damaged MS Excel files. Mentioned below are the steps to perform Excel repair with this tool:

• Download & Run the Stellar Repair for Excel.

• A dialog box appears on your screen, click ‘OK’ to proceed.

• To select your corrupt .XLS or .XLSX file, click ‘Browse’ button. However, if you do not know the location of your .XLS or .XLSX file, the software provides you the option ‘Search’ to search for your corrupt Excel files.

• Select the checkboxes against the files that you want to repair and click ‘Repair’. This starts the scanning process.

• The list of all the files that the software has scanned is displayed in the tree-view in the left pane. Click on a file from this tree-view to see its preview in the middle pane. From this list, you can select the file that you want to recover.

• You can either select the ‘Default location of file’ or ‘Select New Folder’ in the ‘Save Document’ dialog box to save the repaired files.

Stellar Repair for Excel Stellar Repair for Excel is the best choice for repairing corrupt or damaged Excel (.XLS/.XLSX) files. This Excel recovery software restores everything from corrupt file to a new blank Excel file.

Learn More

How to Fix Excel Formulas Not Working Properly | Step-by-Step Guide

Summary: Excel formulas sometimes fail to function correctly and even return an error. This article explains what you might be doing wrong that prevents Excel formulas from working properly and solutions to resolve the issue. If your formulas have disappeared from the Excel spreadsheet and you are having trouble recovering them, you can use an Excel repair tool to recover the formulas.

When working with Excel formulas, situations may arise when the formula doesn’t calculate or update automatically. Or, you may receive errors by clicking on a formula.

Problems Causing the ‘Excel Formulas not Working Properly’ Issue and Solutions

Let’s check out the possible reasons that cause Excel formulas to work properly and solutions to resolve the issue.

Problem 1 – Switching Automatic to Manual Calculation Mode

Automatic and manual are the two modes of calculation in Microsoft Excel.

By default, Excel is set to automatic calculation mode. Everything is recalculated automatically when any changes are made in a worksheet in this mode. You may switch from automatic to manual mode to disable the recalculation of formulas, particularly when working with a large Excel file with too many formulas.

Excel will not calculate automatically when set to manual calculation mode. And this may make you think that the Excel formula is not working properly.

Solution – Change Calculation Mode from Manual to Automatic

To do so, perform these steps:

• Click on the column with problematic formulas.
• Go to the Formulas tab, click the Calculation Options drop-down, and select Automatic.

Problem 2 – Missing or Mismatched Parentheses

It’s easy to miss or incorrectly place parentheses or include extra parentheses in a complex formula. If a parenthesis is missing or mismatched and you click Enter after entering a formula, Excel displays a message window suggesting to fix the issue (refer to the screenshot below).

Clicking ‘Yes’ might help fix the issue. But Excel might not fix the parentheses properly, as it tends to add the missing parentheses at the end of a formula which won’t always be the case.

Solution – Check for Visual Cues When Typing or Editing a Formula with Parentheses

When typing a formula or editing one, Excel provides visual cues to determine if there’s an issue with the parentheses inserted in a formula. Checking for these visual cues can help you fix missing/mismatched parentheses.

• Excel helps identify parenthesis pairs by highlighting them in different colors. For instance, the pair of parenthesis outside is black.
• Excel does not make the opening parentheses bold. So, if you’ve inserted the last closing parentheses in a formula, you can determine if your parentheses are mismatched.
• Excel helps identify parentheses pairs by highlighting and formatting them with the same color once you cross over them.

Problem 3 – Formatting Cells in an Excel Formula

When adding a number in an Excel formula, don’t add any decimal separator or special characters like $ or €. You may use a comma to separate a function’s argument in an Excel formula or use a currency sign like $ or € as part of cell references. Formatting the numbers may prevent the formula from functioning correctly.

Solution – Use Format Cells Option for Formatting

Use Format Cells instead of using a comma or currency signs for formatting a number in the formula. For instance, rather than entering a value of $10,000 in your formula, insert 10000, and click the ‘Ctrl+1’ keys together to open the Format Cells dialog box.

Problem 4 – Formatting Numbers as Text

Numbers are displayed as left-aligned in a sheet in a worksheet, and text formatted numbers are right-aligned in cells. Excel considers numbers formatted as text to be text strings. Thus, it leaves those numbers out of calculations. As a result, a formula won’t work as intended. For example, in the following screenshot, you can see that the SUM formula works correctly for normal numbers. But, when the SUM formula is applied to numbers formatted as text, the formula doesn’t return the correct value.

Sometimes, you may also see an apostrophe in the cells or green triangles in the top-left corner of all the cells when numbers in those cells are formatted as Text.

Solution – Do Not Format Numbers as Text

To fix the issue, do the following:

• Select the cells with numbers stored as text, right-click on them, and click Format Cells.
• From the Format Cells window, click on Number and then press OK.

Problem 5 – Double Quotes to Enclose Numbers

Avoid enclosing numbers in a formula in double-quotes, as the numbers are interpreted as a string value.

Meaning if you enter a formula like =IF(A1>B1, “1”), Excel will consider the output one as a string and not a number. So, you won’t be able to use 1’s in calculations.

Solution – Don’t Enclose Numbers in Double Quotes

Remove any double quotes around a number in your formula unless you want that number to be treated as text. For example, you can write the formula mentioned above as “1” =IF(A1>B1, 1).

Problem 6 – Extra Space at Beginning of the Formula

When entering a formula, you may end up adding an extra space before the equal (=) sign. You may also add an apostrophe (‘) in the formula at times. As a result, the calculation won’t be performed and may return an error. This usually happens when you use a formula copied from the web.

Solution – Remove Extra Space from the Formula

The fix to this issue is pretty simple. You need to look for extra space before the equal sign and remove it. Also, ensure there is an additional apostrophe added in the formula.

Other Things to Consider to Fix the ‘Excel Formulas not Working Properly’ Issue

• If your Excel formula is not showing the result as intended, see this blog .
• When you refer to other worksheets with spaces or any non-alphabetical character in their names, enclose the names in ‘single quotation marks’. For example, an external 5reference to cell A2 in a sheet named Data enclose the name in single quotes: ‘Data’!A1.
• You may see the formula instead of the result if you have accidentally clicked the ‘Show Formulas’ option. So, click on the problematic cell, click on the Formula tab, and then click Show Formulas.
• If you’re getting an error “Excel found a problem with one or more formula references in this worksheet”, find solutions to fix the error here .

Conclusion

This blog discussed some problems you might make causing an Excel formula to stop working properly. Read about these common problems and solutions to fix them. If a problem doesn’t apply in your case, move to the next one. If you cannot retrieve formulas in your Excel sheet, using an Excel file repair tool like Stellar Repair for Excel can help you restore all the formulas. It does so by repairing the Excel file (XLS/XLSX) and recovering all the components, including formulas.

Solutions to Repair Corrupt Excel File

Summary: MS Excel can throw various errors due to corrupted Excel files. This blog discusses the error messages that indicate Excel file corruption and the methods to prevent data loss due to a corrupt file. It also discusses the reasons behind the corruption in Excel file and their solutions. It also mentions a “Stellar repair for Excel” tool that can help to repair the corrupt or damaged Excel file.

Is your Excel file corrupted? And you don’t have backup of your data? There is no need to worry. There are some simple solutions to repair Excel file 2019. But before heading towards the solutions, let’s discuss the possible reasons for Excel file corruption and how you can prevent losing your data.

Error Messages that Indicate Excel File Corruption

When an Excel file gets corrupted, different error messages appear. For example:

• “Excel found unreadable content in . Do you want to recover the content of this workbook, click Yes.”
• “Can’t find project and library.”
• “The workbook cannot be opened or repaired by Microsoft Excel because it is corrupted.”
• “Microsoft Excel has stopped working.”

Reasons Behind Excel File Corruption

The reasons for corruption in Excel file could be any of the following:

• Improper system shutdown
• Computer virus/malware attack/Hacker attack
• Outdated anti-virus definition
• Hardware failure
• Unintentional deletion of files
• Large Excel files
• Bad sectors on storage media

How to Avoid Data Loss Due to Excel File Corruption?

Excel users should follow the below precautionary measures to prevent data loss due to Excel file corruption:

1. Create an Automatic Backup Copy

When you create an Excel spreadsheet, it is advised to Save As your document, as follows:

1. In Save As window, click Tools next to Save option.
2. Select General Options from the drop-down menu.
3. Then check the dialogue box Always create back up and click OK.

This will always create a backup of your Excel. If it’s deleted or corrupted at any time, it can be recovered.

2. Create Recovery File at Different Time Periods

Steps are as follows:

1. Go to File and then click Excel Options.
2. Click Save and then select the Save Auto Recover information every checkbox
3. Add the required minutes and location. Ensure that Disable AutoRecover for this workbook only box is unchecked.

Methods to Repair Corrupted Excel 2019 File

Try using these 5 methods to restore your Excel file and recover data:

Method 1: ‘Open and Repair’ Excel Files

Excel automatically opens the corrupted file in Recovery Mode. If not, you can repair Excel file manually through the following steps:

• Click on the File and select Open.

• Go to the location where the corrupt workbook is stored. In the Open window, select the corrupt file.
• Click Open and then select Open and Repair.
• In the window that opens, click Repair.

If the Repair option doesn’t work, you can select Extract Data and try to extract the values and formulae safely from the corrupt file.

Method 2: Recover Data from Open Workbook

If you face issues while working in an Excel file, you can choose to return to the last saved version of the Excel file. For this:

• Click File. Then select Open.
• Double click on the name of the workbook (the one that is open in your Excel).
• Click Yes to reopen it.

• The workbook will now appear.

Please note that it will show the last saved version and changes made after that won’t be recovered.

Method 3: Set Calculation Option as Manual

You can also recover data from Excel workbooks that you’re unable to open. For this, you need to configure the calculation option as manual in Excel. You can do this through the following steps:

• Click on File. Select New and open a Blank workbook.
• From File, select Excel Options.

• From the Formulas category, under the section Calculation options, select Manual. Now click OK.

• Then click File, and select Open to open the corrupted or damaged Excel file.

Method 4: Recover Content by Using External Links

You can also recover specifically the content (leaving formulas/calculated values) from the workbook by using external references (to link Excel workbook). For this:

• Click on File, Select Open.
• Navigate to the folder that contains the corrupted workbook.
• Now, right-click on the file name of the corrupted workbook and click Copy.
• Click File button. Then, select New and create another blank workbook.
• In the first cell (A1), type =!A1 and press Enter.
  • Select the corrupted workbook in the Update Values dialogue (if it appears). Then click OK.
  • Select the relevant sheet in the Select Sheet dialogue (if it appears). Then click OK.

• Again, select the cell A1, go to Home and select Copy.
• Now select (start from the cell A1) an area equal to that of the data in the original workbook.
• Go to Home now and select Paste.
• Again, go to Home, and Copy the data (the same selection of cells).
• Go to Home, and then click on the arrow below Paste. Then click on Values.

By pasting values, you removed the links to the corrupted workbook and only the data is left behind.

Method 5: Excel Repair Software

If the above-mentioned methods do not help in repairing the corrupt Excel file, try an Excel repair software.

One of the most commonly used Excel repair tools is Stellar Repair for Excel. Its trial version is available for free download, which lets you scan and preview the repaired Excel files. Once you’ve ascertained the effectiveness of the software, you can save the file after activating the software.

Here’s the complete repairing process of the corrupt Excel file

Conclusion

This post shared the reasons behind Excel file corruption and precautionary measures to prevent data loss. It also outlined different methods to repair corrupt Excel file 2019. There are several in-built utilities in Microsoft Excel to repair corrupt workbooks and recover data from it. In case these methods didn’t work, you can use Stellar Repair for Excel – an easy-to-use DIY tool that can fix all Excel corruption errors and restore data with all original properties.

Fix Cannot Paste the Data Error in Microsoft Excel

When copying and pasting the cell contents or attributes from one workbook to another, you can encounter the “Cannot paste the data” error. The error indicates that the values you are trying to paste do not match the cell format (Currency, Date, Text, etc.) used in the column. The error can occur if you are attempting to paste information into a merged cell. You can also get this error if the data you are trying to paste contains more columns than the destination worksheet can accept or if the copy area and paste area aren’t the same size.

Why you cannot Paste the Data in Microsoft Excel?

You can experience the “Excel cannot paste the data” error due to one of the following reasons:

• Locked cells
• Data size limitation
• Formatting issues
• The file size is too large
• Overloaded clipboard
• Data type mismatch
• Invalid range
• Lack of permissions
• Excel file is corrupted
• Compatibility issues
• While pasting the merged cells

Methods to Fix MS Excel Cannot Paste the Data Error

The “Microsoft Excel cannot paste the data” error usually appears when copying and pasting data within the spreadsheet. It primarily affects the copy-and-paste feature within the Excel file. However, the impact of this error may vary with the data you are working with. When this error occurs, first try restarting the Excel application. Ensure that you’ve saved all your important data before restarting the application to prevent data loss. If, after performing this basic step, you still face the issue, then try the following troubleshooting methods.

Method 1: Check and Unlock Cells in Excel

You can get the Excel cannot paste the data error if the cells you are trying to paste in the Excel file are locked. You can check and unlock the specific cells in the Excel file using the below steps:

• Go to the Home tab in your Excel file and click Format Cell Font popup launcher.

• In the Format Cells dialog box, click Protection.

• Under the Protection option, unselect the locked field and then click OK.

Method 2: Check and Fix the Cell Format

Sometimes, you can get the “Cannot paste the data” error in Excel if there is an issue with the formatting of the cells in the column (in which you are trying to paste the data). You can check whether the cell formats of the columns of the source file (from where you are copying data) match the destination file (in which you are pasting the cell data). If not, then change the cell format. Here’s how to do so:

• In the Excel file, click on the affected column heading (whose cells you need to modify).
• Navigate to the Home tab and click the General dropdown menu.

• Click on the desired cell format that matches the values you are trying to paste into the column.

Method 3: Check Copy and Paste Area Size

The “data cannot be pasted” issue can also occur if Excel detects the copy area of the range of cells is not of the same size and shape as that of the paste area. So, make sure the size of the range of cells for the paste area is same as the copied area. To ensure this, you can try the following:

• Use Upper-left Cell

 You can select the upper-left cell instead of the complete range to paste.

• Use Paste Special Option

• You can use the Paste Special option to paste the data. To use this option, go to Home > Paste Special.

• You can choose from the different options, such as paste all, formulas, operations, Transpose, skip blanks, etc. to take control over how the data is pasted.

Method 4: Check and Unmerge Cells

The merged cells in the workbook can create discrepancies when copying and pasting data. These can create mismatch between the source and destination cell areas, resulting in errors. Make sure you are not pasting the data copied from merged cells. You can check and unmerge the cells using the below steps:

• Open the Excel file (in which you are copying data) and go to the Home tab.
• Click Merge & Center > Unmerge Cells.

Method 5: Unselect the Excel DDE (Dynamic Data Exchange) Option

Sometimes, you can get the Excel cannot paste the data error while copying and pasting data from Excel file to external data sources or applications. It usually appears if you have established DDE links between them. To resolve this, you can uncheck the “Ignore other applications that use Dynamic Data Exchange (DDE)” option. Follow the below steps to do so:

• Open your Excel file.
• Go to File > Options.

In Excel Options, click Advanced and unselect “Ignore other applications that use Dynamic Data Exchange (DDE). Click OK.

Here are some additional solutions you can try to fix the “Cannot paste the data” error in Excel

Check the Excel File Format

Sometimes, the copy and paste error can occur if you try to move data from an older version of Excel file (XLS) into a newer version file (XLSX) or vice versa. You can try to change or convert the file format to resolve the issue.

Clear Clipboard

Too many items on the clipboard can prevent you from copying and pasting the data. You can empty your clipboard  by deleting all clips. To do this, go to Home and click the Clipboard option.

Change the Column’s Cell Format

You can get the “Cannot paste the data” error when the information you need to paste does not match the cell format in the column. In such a case, you can change the column’s cell format. To do this, navigate to the Home option and then click on the General menu arrow. Then, change the cell format suitable to the type of information you are trying to paste in that column.

Add Columns

The copy-and-paste error in Excel can also occur if there are not enough columns to accept the data that you are trying to paste. You can insert more columns and then try copying and pasting data. To insert additional columns, click on the column heading, click Home, and then select Insert.

Remove Incompatible Add-ins

Sometimes, incompatible or faulty add-ins can also create conflicts with Excel’s clipboard operations, leading to the error. To check if add-ins are causing the issue, open the Excel file in safe mode. In safe mode, if you are able to paste the data, then check and uninstall the incompatible Excel Add-ins.

What to do if Nothing Works?

If the above methods do not work, then corruption in the Excel file could be the cause of the “Cannot paste the data” error. In such a case, you can use Excel’s built-in Open and Repair utility to repair the corrupted file. Here’s how to use this utility to repair the Excel file:

• Open your Excel application.
• Navigate to File and then click Open.
• Click the Browse option to select the affected Excel file.
• The Open dialog box opens up. Click on the corrupted file.
• From the Open dropdown, select the Open and Repair option.
• The Excel will prompt you to choose one of the below options:
• Repair
• Extract
• Cancel
• Select the Repair option to extract maximum data from the file. If the Repair option fails, choose the Extract option to recover the data, excluding formulas and values.

 The Open and Repair utility may not be able to repair your Excel file if it is severely corrupted. In such a situation, the best possible way out is to use a professional Excel repair software to repair the file. You can try Stellar Repair for Excel. It can repair highly damaged or corrupted Excel (.xls, .xlsx, .xltm, .xltx, or .xlsm) file and recover all the objects from the file by maintaining the original formatting. The tool supports 2007 and higher editions of MS Excel. You can download the demo version of the software to preview the repaired objects and verify its functionality.

Conclusion

The “Excel cannot paste the data” error in Excel can occur due to different factors. You can check and clear the clipboard, unmerge the cells, unlock the cells, and use various other methods mentioned above to troubleshoot the error. If you fail to copy and paste the data due to file corruption, then opt for an advanced Excel repair software, like Stellar Repair for Excel. It is a reliable tool that can help you repair highly damaged/corrupted Excel files. It enables you to recover all the objects from the corrupted Excel file, with complete integrity.

How to Fix “Errors were detected while saving Excel” Error?

When trying to save the Excel file, you might face unexpected errors. The “Errors were detected while saving Excel” is one such error. It can also occur when using VBA in Excel. The complete error message appears as:

**“Errors were detected while saving [file name]. Microsoft Excel may be able to save the file by removing or repairing some features. To make the repairs in a new file, click Continue. To cancel saving the file, click Cancel.”
**

The error can occur if the features (Pivot tables, charts, macros) used in the Excel file get corrupted . However, there could be several other reasons behind the occurrence of the error. Let’s discuss them.

What Causes the “Errors were detected while saving Excel” Error?

There are various reasons why you encounter this error. Here are some of them:

• Incompatible pivot table in the Excel file
• Large or uncompressed images in the Excel file
• File-sharing properties are not allowing file saving
• Excel file is corrupted
• Large-sized Excel file
• File version incompatibility
• VBA code is corrupted

Ways to Fix the “Errors were detected while saving Excel” Error

You’re not able to save the Excel file if there is no storage space on your hard drive. So, first check if your hard drive has sufficient storage space to save the file. If this is not the case, then it might happen that your antivirus program is interrupting the saving process. To check this, temporarily disable your antivirus program and then try to save the file. If still your Excel is throwing the “Errors were detected while saving Excel” error, then follow the below given methods to fix the error:

Click here

###  **Method 1: Open the Excel in Safe Mode and Disable the Add-ins**

When you open Excel in safe mode, it opens without the third-party add-ins. This helps in finding out if any add-ins are causing the error.

 Here’s how to open the Excel in safe mode:

• Open the Run window by pressing Windows key + R.

• Type excel /safe in the Run window.

  ?

• Next, click on OK.

• It will open Excel in safe mode.

• Now, try to open and save the affected file.

If you are able to save the file without any issue, then this means that the error has occurred due to third-party add-ins or settings. You can try disabling the add-ins to fix the issue. To do this, follow these steps:

• First, open Excel.
• Then, go to the File tab and click Options.

• In Excel Options, click on the Add-ins

• Under the Manage section, select Excel Add-ins and then click on the Go

• In the Add-ins dialog box, unselect the add-ins under the Add-ins available option and click OK.

  

Note: Disabling add-ins does not remove them from the system. To remove them permanently, you need to uninstall them.

Method 2: Check the Excel File Name

Some users have observed this error when saving the Excel file with an invalid name. You can check the file name and ensure that it should not contain more than 218 characters. If the name exceeds the required limit, then try shortening the file name or move the file to a folder with a short path name.

Method 3: Copy the Data from the Affected File to a New File

If you are not able to save the Excel document, then try copying the data from the affected file to a new Excel file. Then, save the new file with a different name. This helps in resolving the issue.

Method 4: Check and Provide File Permissions

You may experience the “Errors were detected while saving Excel” issue when you do not have desired permissions to modify the folder in which your Excel file is located. To modify the folder, you should have read, write, and create permissions. You can check and provide the desired permissions using the below steps:

• Navigate to the Windows Program Files and then find the desired folder (where the Excel file is saved).
• Right-click on the folder and then choose Properties.
• Select the Security tab and then click
• Click on Change Permissions in the Advanced Settings
• Click Administrators and then click Edit.
• Now set the Apply to drop-down button to This Folder, Subfolder, and Files.
• Click on the Full Control field and then click Apply > OK.

Method 5: Check Pivot Tables in Excel Sheet

You can review Pivot tables to see if they are causing the “Errors were detected while saving Excel” error. To do so, follow the below steps:

• Click Power Pivot > Manage.

  

• Check the tabs in the Power Pivot

• Check if all the formulas used in the table are correct. Sometimes, even a small typo can create an issue in Excel.

Method 6: Repair Your Excel File

The “Errors were detected while saving Excel” issue can also occur if the Excel file is corrupted. In such a case, you can take the help of the built-in utility in Excel – Open and Repair to repair your Excel file. Here’s how to use the tool:

• In Excel, click the File tab and then click Open.
• Click Browse to select the desired file.
• The Open dialog box is displayed. Click on the corrupted file.
• Click on the arrow next to the Open button and then click Open and Repair.
• Click on the Repair

• After repair, a message will appear (as shown in the below figure).

![Message Appear After Repair](https://www.stellarinfo.com/public/image/catalog//article/Repair-Office-Documents/errorsweredetected/message-appear-after-repair.jpg)

• Click Close.

 However, sometimes, the Open and Repair utility fails to fix the file if it is severely corrupted or large-sized. In such a case, you can take the help of a third-party Excel repair software, such as Stellar Repair for Excel. The tool performs a comprehensive scan of the corrupted Excel file to fix the issues and recover all the items from the file without changing the original formatting. It can recover pivot tables, charts, images, engineering formulas, etc. The tool is compatible with Windows 11/10/8.1/8/7. You can download the free trial version of the tool to evaluate its functionality.

Closure

Many Excel users reported facing the situation when they are saving the Excel file. You can check the file’s compatibility to fix the “Errors were detected while saving Excel” issue. If you are getting this error in a Macro-enabled file then you can try deleting the VBA project from a document to resolve the issue. However, deleting the entire VBA code cannot be a better solution as it can lead a data loss in the Project you are working on. In the above article, you have learned the reasons behind the issue and discovered how to fix the error. Follow the methods and if none of them works then try using Stellar Repair for Excel. It is an advanced tool that can quickly repair corruption in Excel worksheets at any level. It lets you restore the corrupted components from the corrupted file without removing the existing data.

Ways to Fix Personal Macro Workbook not Opening Issue

Many users have reported encountering issues while accessing personal macro workbook, such as personal macro workbook not opening, personal macro workbook not loading automatically, Excel personal macro workbook keeps getting disabled, etc.

Such issues may arise due to a problem with the directory where the personal workbook is stored. However, there are various other reasons that may lead to such issues. Below, we’ll discuss the reasons behind the personal macro workbook not opening issue and the solutions to troubleshoot and fix the issue. But before proceeding, let’s understand why personal macro workbook is used.

Why Personal Macro Workbook is used?

You can access macros in a specific Excel workbook. However, when you need to use the same macro in other Excel worksheets, then you can create a personal macro workbook. A personal macro workbook (Personal.xlsb) is a hidden workbook that is used to store all macros. It makes your macros available every time you open Excel.

Causes of Personal Macro Workbook not Opening Issue

You may encounter personal macro workbook is not opening issue when attempting to record macros. Some possible causes behind such an issue are:

• Personal macro workbook is stored at an untrusted location
• Location of xlsb is changed
• Personal macro workbook is hidden
• Personal macro workbook becomes corrupted
• Disabled items in add-ins
• Workbook is Read-only

Methods to Fix the “Personal Macro Workbook not Opening” Issue

 Follow the given methods to fix the personal macro workbook is not opening issue:

Method 1: Check the Path of Personal.xlsb

The personal macro workbook (Personal.xlsb) file is stored in XLStart folder. It opens automatically when you open your Excel application. However, sometimes it fails to load automatically. It usually occurs when you try to open the file from an incorrect path. You can check the path of Personal.xlsb by following these steps:

• Open the workbook.
• Click on the Developer tab.

• Press Alt + F11 to open Visual Basic Editor.
• Go to View > Immediate Window.

• In Immediate Window, type the following code to know the location of the workbook:

?thisworkbook.path.

• Then, hit Enter.

• You will see the path of the personal macro workbook.
• Copy the path and paste it into Quick Access field in File Explorer.

Method 2: Unhide Personal Macro Workbook

If personal macro workbook is hidden, you may unable to see and open the Personal.xlsb file. To unhide the personal Macro workbook, follow the below steps:

• In Microsoft Excel, go to View and then click Unhide

• The Unhide dialog box is displayed. Click PERSONAL and then OK.

Method 3: Enable the Macro Add-ins

You may unable to open the previously recorded macros in your personal macro workbook if the macros are disabled. To check and enable the items, follow these steps:

• Go to File > Options.
• In Excel Options, click on the Add-ins
• Select Disabled Items from the Manage section and click on Go.

• The Disabled Items dialog box appears. Click on the disabled item and then click Enable.

Method 4: Change the Trusted Location

You may encounter the “personal macro workbook not opening” issue if the Personal.xlsb file is stored at an untrusted location. You can check and modify the path of XLSTART folder using the Trust Center window. Here are the steps:

• Open MS Excel. Go to File > Options.
• Click Trust Center > Trust Center Settings.
• In the Trust Center Settings dialog box, click on Trusted Locations.

• Verify the path of the XLSTART If it is untrusted or there is any issue, then click Modify and then click OK.

Method 5: Repair your Excel File

You may fail to open personal macro workbook if it is corrupted. To repair the corrupt workbook, you can use the built-in Open and Repair utility in MS Excel. To use this tool, follow these steps:

• Open your Excel application.
• Click File > Open.

• Browse to the location where the corrupted file is stored.
• In the Open dialog box, select the corrupted workbook.
• From the Open dropdown list, click Open and Repair.

The dialog box appears with the Repair and Extract buttons. Click Repair to retrieve all possible data or the Extract option to recover the data without formulas and values.

If the Open and Repair utility fails to repair the corrupted Excel workbook, then you can use a professional Excel repair tool, such as Stellar Repair for Excel. It can easily repair severely corrupted Excel (XLSX and XLS) files and recover all the components. You can download the free trial version of the tool to preview the recoverable data.

Closure

This article discussed the ways to fix the personal macro workbook not opening issue. In case you are unable to open the personal macro workbook because of corruption in the workbook, you can use the Open and Repair utility in MS Excel. If it fails, then you can use Stellar Repair for Excel to fix corruption in the Excel file and recover all its data with complete integrity.