How to Fix the Unable to Record Macro Error in Excel?

Summary: You may encounter the “Unable to record macro” error in MS Excel when using Personal Macro Workbooks. In this post, we’ll discuss the possible causes behind this error and the ways to fix it. We’ll also mention a professional Excel repair tool that can help fix the error if it occurs due to corrupted workbook.

A personal macro workbook (Personal.xlsb file) is a hidden workbook in Excel that stores all macros in a single workbook. This allows you to automate processes while working in Excel. Sometimes, when recording macro codes in the personal macro workbook, you may get the message “Personal Macro Workbook in a startup folder must stay open for recording”. When you click on the OK button, it will show the “unable to record” error. This prevents you from recording the macros. Below, we’ll see the causes behind this error and discuss how to resolve this error.

Causes of Unable to Record Macro Error

You may be unable to record macros in Excel due to several reasons. Let’s take a look at the possible causes that can lead to this issue.

• The location of personal.xlsb file is changed.
• Personal.xlsb file is corrupted.
• Macros are disabled.

Methods to Fix the “Unable to Record Macro” Error in Excel

Here are some possible solutions that can help you resolve the unable to record macro error in Excel.

Method 1: Check the Path of XLStart Folder

You may be unable to record macros if the path of XLStart folder is incorrect. It is a folder where the Personal.xlsb file is stored by default. Follow these steps to find out the path of this folder:

• Open MS Excel. Go to File > Options.
• Click Trust Center > Trust Center Settings.

• In the Trust Center Settings window, click on Trusted Locations.

• Verify the path of the XLSTART folder and modify it if there is an issue.
• Once you are done, click on OK.

Method 2: Change Macro Security

The “Unable to record macro” error can occur if macros are disabled in the Macro Security settings. You can try changing the macro settings using the below steps:

• In MS Excel, go to File > Options > Trust Center.

• Under Trust Center, click on Trust Center Settings.

• Select “Enable all macros” and then click OK.

Method 3: Check Add-ins for Disabled Items

If there are any items in add-ins that are disabled, they may prevent Excel from functioning properly. You can check and enable the items in MS Excel using the below steps:

• Click File > Options.

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

• The Disabled Items window is displayed.
• Click on the disabled item and then click Enable.
• Restart Excel for the changes to take place.

Method 4: Repair your Excel File

You may fail to record macros if there is corruption in the workbook. In such a case, you can use the “Open and Repair” utility in MS Excel to repair the corrupt workbook. 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, choose the corrupted workbook.

• From the Open dropdown list, click Open and Repair.

Excel will prompt you to repair the file or extract data from it. Click Repair to retrieve maximum data. If the Repair option fails, then click on the Extract Data option to recover the data without formulas and values.

If the Microsoft utility “Open and Repair” fails to repair the corrupted Excel workbook, then try a professional Excel repair tool such as Stellar Repair for Excel. It is an advanced tool that can easily repair severely corrupted Excel (XLSX and XLS) files. It can recover all the file items, including chart sheets, cell comments, tables, macros, formulas, etc. without impacting the properties and cell format of the Excel file.

Closure

You may receive the “unable to record” error in Excel while creating or storing macros in Personal Macro Workbooks. There are several reasons that can lead to this error. You can try the methods covered in this post to resolve the error. If the error appears due to corruption in workbook, then try to repair it using the Open and Repair utility. Alternatively, you can use Stellar Repair for Excel – a professional tool that can help you repair an Excel file with problematic macros. Also, it allows recovery of all the file components with complete integrity. The tool is compatible with Excel 2021, 2019, 2016, and older versions.

File Format and Extension of [filename] don’t Match in Excel File

Summary: The “File format and extension of [filename] don’t match. The file could be corrupted or unsafe” error message indicates that the Excel file you’re trying to open is unsupported, unsafe, or corrupted. Read this article to learn more about this error and how to fix this error. It also mentions an advanced Excel recovery tool to repair the corrupted Excel file and retrieve all its data in a few clicks.

You can encounter the “File format and extension of [filename] don’t match. The file could be corrupted or unsafe” error when the Excel application detects any issue with the file. This happens when you try to open an old version file format in a newer version or if the file is received from an unsafe destination. This can prevent you from opening the Excel file.

As indicated from the error message, this error occurs due to the following reasons:

• The file has incorrect file extension.
• The file is corrupted.
• The file you are trying to open is protected.

Now, let’s see how to resolve this Excel error.

Methods to Fix the “File format and extension of [filename] don’t match” Error

Try the following methods to troubleshoot the “File format and extension don’t match” error in Excel.

Method 1: Rename the Excel File

You can face the “File format and extension don’t match” issue if the file has incorrect extension. It can occur if the file extension has been altered or you’ve mistakenly saved the file with incorrect extension. To fix this, you can try renaming the Excel file with the correct file extension.  

Method 2: Check the Default Excel File Format

Different versions of Microsoft Excel use different default file formats. For example, .xls is the default file format of older versions (2003 and lower) of Excel, whereas .xlsx format is used by the newer versions (2007 and later). Opening the Excel file with an incompatible extension can cause the “File format and extension don’t match” issue. You can check the Excel version you are using and ensure it’s compatible with the Excel file you are trying to open.

Method 3: Change the Protected View Settings

You may receive the “File format and extension of excel don’t match” error if the Excel file is protected. You can check and try disabling the Protected View settings .  

Caution: Changing the Protected View settings can put your system at risk. If the Excel file is being downloaded from the internet, it may contain viruses that can infect your system. So be careful before disabling the Protected View settings.

Steps to Change Protected View Settings in Excel:

• In the Excel’s File menu, click on Options.
• Select Trust Center > Trust Center Settings.

• Under Trust Center, select Protected View and disable the below three options:
• Enable Protected View for files originating from the internet.
• Enable Protected View for files located in potentially unsafe locations.
• Enable Protected View for Outlook attachments.

• Click OK. Then, try to open the Excel file.

Method 4: Check and Provide the Excel File Permissions

Sometimes, you can get the error if you don’t have sufficient permissions to open the Excel file. This usually happens when you try to open the Excel file received from other sources. You can check and provide the desired permissions to fix the error. Here are the steps:

• Locate the affected Excel file, right-click on it, and select Properties.

• In the Properties window, click the Securities option and select Edit.

• In the Security window, under ‘Group or users name’, select the user names. Check the file permissions and make sure Full Control is enabled. If not, then click on the Add option.

• Click on the Advanced option in the Users, Computers, Service Accounts, or Groups window**.**

• Click the Find Now option. A list of all users and groups appears in the search field.

• Select “Everyone” from the list and then click OK.

• In the object names field, you will see ‘Everyone’. Click on OK.

• In the Permissions window, select “Everyone” and enable all options (Full Control, Modify, Read & Execute, Read, and Write) under Permissions for Everyone.

• Click Apply and then OK.

Method 4: Repair your Excel File

As the error message indicates, corruption is one of the causes of the “File format and extension of [filename] don’t match” error. If your file is corrupted, you can repair it using Microsoft’s built-in Open and Repair tool. Here are the steps to run the Open and Repair tool to repair corrupted Excel file:

• In Excel, click on File.
• Click Open and then click on Browse to select the corrupted Excel file.
• In the Open dialog box, click the Excel workbook (in which you are facing the error).
• Click the arrow next to the Open button and select Open and Repair.
• Then, click Repair to recover as much data as possible.
• The Excel prompts a message after the repair process is complete. Click Close.

The Open and Repair utility may fail to give the intended results. In such a case, you can repair the corrupted/damaged Excel file using a specialized Excel repair tool . Stellar Repair for Excel is one such tool that can repair severely corrupted Excel files. With the help of this tool, you can quickly recover all the objects from the Excel file. The tool has a simple user interface that even a non-technical can use to repair the Excel files. The tool can also repair multiple Excel files at once. You can check the tool’s functionality by downloading its demo version.

Closure

You can encounter the “File format and extension of [filename] don’t match” error due to different reasons. To resolve the issue, you can check the file extension, permissions, protected settings, etc. If you suspect the error has occurred due to corruption in the Excel file, you can try repairing the Excel file using the Open and Repair tool. If nothing works for you, then try Stellar Repair for Excel . It can repair highly damaged Excel files and recover all the data while preserving the file properties and cell formatting. The tool can help you fix all the common corruption-related errors quickly.

How to Fix Microsoft Excel Error Code 0x800A03EC?

Summary: You can encounter the error code 0x800A03EC in Excel due to different reasons. This post discusses the causes of the error and the workarounds and methods to fix it. If the “Microsoft Excel error 0x800A03EC” prevents you from accessing the Excel file data, use the Excel repair file tool mentioned in this post to restore the data with complete integrity.

The error code 0x800A03EC in MS Excel can occur while exporting or importing Excel sheets to or from another application. It can occur when trying to execute a macro. It usually appears when the Excel application fails to read the queries in the VBA code, especially when using the PasteSpecial method (Range.PasteSpecial method) to paste cell data from the clipboard into an Excel sheet. It can occur if the cell data format is not compatible with the target data.

Causes of MS Excel Error Code 0x800A03EC

The error code 0x800A03EC in Excel can occur in different scenarios. There could be several reasons associated with this error. Some of them are:

• Incompatible data formats.
• Trying to paste an extensive range of cells into a smaller range of cells.
• Add-ins are interrupting the code operation.
• Issue with the VBA code (incorrect or incomplete queries).
• Excel file is corrupted/damaged.
• Corrupted macros.
• Trying to export large-sized Excel file which is more than the Excel’s prescribed limit.
• Outdated Excel version.
• Incompatible cell formats on source and destination (when copying/pasting cell data).
• Missing cell range (forget to specify the range while using PasteSpecial method in the VBA code).
• Trying to save Excel file to incorrect directory.
• File path contains invalid characters.

Solutions to Fix MS Excel Error Code 0x800A03EC

The error 0x800A03EC can appear if your Excel file is incompatible with your Excel application version. You can run the compatibility checker  to review the compatibility issues in your Excel file. If this is not the issue, then follow the below methods.

Method 1: Review VBA Code

The exception from hresult 0x800a03ec excel can occur if you are trying to execute incorrect or incomplete queries in the Excel VBA code. It can also appear if the formulas do not contain the equal (=) symbol at the beginning. Verify the VBA code for any logical flaws, typo errors, syntax errors, or missing references.

Method 2: Check the Field Size

Excel has certain limits and specifications . If the Excel file’s data exceeds these designated size limits, you can get the MS Excel error code 0x800A03EC error. For example, this error occurs if you try to export a file with more rows than the limit of 65536 and columns than the limit of 256. Check and optimize the file size by minimizing complex formulas and other objects.

Method 3: Check Add-ins for Disabled Items

Sometimes, disabled items in Add-ins settings can prevent macros from functioning correctly. You can check and enable the disabled items in Add-ins using these steps:

• Navigate to File > Options.

• In Excel Options, click on the Add-ins option.
• Click the arrow corresponding to the Manage section.
• Select Disabled Items and click on the Go option.

• You will see a list of disabled items.
• Click on the disabled items and then click Enable.
• Restart Excel for the applied changes to take effect.

Method 4: Change Macro Settings

The error code 0x800A03EC can also occur if macros are disabled in the Macro Security settings. Follow these steps to change the macro settings in Excel:

• In MS Excel, go to File > Options > Trust Center.

• Under Trust Center, click on Macro Settings.

• Select Enable all macros and click OK.

Method 5: Check the OLE Objects

The Microsoft Excel error code 0x800A03EC can also appear if there is a connection disruption in the VBA. Such an issue can occur if there is an issue with Object Linking and Embedding (OLE) in an Excel workbook. The OLE objects are linked to external files. You can check and remove the unnecessary OLE objects from your Excel file to fix the issue.

Method 6: Check Methods in VBA

Excel can throw the “Exception from HRESULT: 0x800A03EC” error if you are trying to call an invalid method in a VBA code. Many users have reported this issue when trying to use ‘copy and paste’ feature using copy paste special method  (range.pastespecial) in Excel. Paste special is an advanced option in Excel to smooth the copy-and-paste task. While using this method, the exception can usually occur when Excel application detects an invalid or misaligned range or mismatch data type in the syntax. To fix this, check the syntax of the paste special method.

Method 7: Repair your Excel File

Corruption in Excel file can create inconsistencies in the macro and lead to the “Exception from HRESULT 0x800a03ec Excel” error. In such a case, you can try repairing the Excel file using Microsoft’s inbuilt utility – Open and Repair. To use this utility, follow these steps:

• In the Excel application, go to the File tab and then click Open.
• Click Browse to select the Excel file in which you are getting this exception error.
• The Open dialog box will appear. Click on the corrupted file.
• Click the arrow next to the Open button and then select Open and Repair.
• You will see a dialog box with three buttons – Repair, Extract Data, and Cancel.

• Click on the Repair button to recover as much of the data as possible.
• After repair, a message is displayed. Click Close.

The Open and Repair tool works in significant scenarios. If Open and Repair tool fails to resolve the issue, try an Excel repair tool recommended by experts and MVPs to repair the corrupted Excel file. Stellar Repair for Excel is one of the recommended Excel repair tools for fixing issues caused by corrupt Excel files. It provides more features than the Open and Repair utility. It can even repair severely corrupted Excel files. The tool supports all the versions of Microsoft Excel, including 2019. Download the software’s demo version to scan the corrupted file and see the preview of all the recoverable components of the file.

Conclusion

There are numerous reasons, like invalid method, incorrect range, data type mismatch, etc., that could lead to the common exception error 0X800A03EC in Excel. Try the troubleshooting methods mentioned above to fix the issue. You can also encounter the error due to corruption in the Excel file. In such a case, you can try the professional software – Stellar Repair for Excel – to repair the severely corrupted Excel file. The Excel repair software can fix all the corruption-related issues in Excel files (XLS/XLSX).

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 Fix a Corrupted .xls File? The Everything Guide

Undoubtedly, Excel is so powerful that it can help you to process, analysis, and store data, in masses.

That’s the reason it has been there for years and helping this world in data.

But…

With all those powers comes some nasty problems which no Excel users like to face. Can you guess what I’m talking about?

Think about a Corrupted Excel File. Nightmare? Isn’t it?

And do you remember that last time when you have opened a workbook and you got a message that this workbook is might corrupt?

The TRUTH is, this is something which you cannot avoid, but, you can prepare yourself in the best way and deal with it like a PRO.

So today, in this post, I’d like to share with you to everything you need to know about a corrupt Excel file (.xls), why it happens, how to fix it like a PRO, and much more.

…let’s get started.

Note: In this post, we’ll be covering the .xls version (which is the extension for the file which is created in Excel 2007 or the earlier versions) and if you want to know about the new version, here’s the quick fix for that.

Why My Excel File Got Corrupted?

There can be one or multiple reasons for an Excel file to get corrupted. Below I have detailed about some of the major of them.

1. Large Excel File

You can store data in a workbook the way you want but sometimes using excessive thing can make an Excel file bigger in size.

And that kind of data files can crash at any point in time. Here are a few things which make the Excel files heavy, like

• Conditional Formatting.
• Colors formatting.
• Using merged cells in place of text alignment.
• Volatile functions: Formulae that iterate every time you open or change a cell value; OFFSET, NOW.
• Using a complete column or row as a reference than the data set range.
• Using complex formulas; VLOOKUP in place of Index/Match, Nested If in place of MAXIFS, MINIFS.
• Calculations or reference across workbooks.

Related: How to Fix Formatting Issues in Excel

2. Abrupt System Shutdown

Shutting down the system without following the procedure can corrupt your data file.

This shut down can be due to a power failure or any other unexpected technical challenges.

So it is always important to follow the procedures and shut down your system properly to avoid data losses.

3. Infected Excel File (Virus Attack)

This is the most common and obvious reason for Excel file corruption.

Although we always keep our system safe using various Antiviruses, still there is always a probability of virus attacks and loss of important files.

It is always advised to use a safe and strong antivirus compatible with your system requirements.

What are the Signs to Know When an Excel File is Corrupted?

In this section, we will discuss what are the signs which you can get when an Excel file is corrupted, let’s dig into it.

1. The File is Corrupt and Cannot Be Opened

This is one of the most common messages you can see when your workbook is corrupted.

But there is also a chance that it is just because of the version compatibility where you have a .xls file but you are using the latest version of Excel check out this detailed post by Priyanka

2. We Found a Problem with some Content in this File…

There’s another error message which you can get while opening a file:

We Found a Problem with some content in Do you want us to recover as much as we can? If you trust the source of this workbook, click yes.

There are a lot of applications out there (I think almost every) which exports the data as a .xls format. Those files have a greater chance of having this kind of error.

3. “Filename.xls” cannot be accessed

There can also be a situation where you get the error:

“Filename.xls” cannot be accessed. The file may be corrupted, located on a server that is not responding.

Well, this message is a bit misleading.

You won’t be able to decide that your file is actually corrupted or just not on the location.

My Excel File Got Corrupted, now What Should I Do?

There are many ways to recover the data from the corrupt excel files. But before you start, it is always advised to create a copy of the corrupted file.

You can save a lot of time with Stellar Repair for Excel, which make data recovery just with few clicks.

But before you go for a data recovery software, let’s try out some manual steps which can help.

When a workbook get corrupted the first thing comes to the mind is to recover data from it…

…and you what there’s a simple option there in the Excel which you can use to do this. Below are the steps you need to follow:

• First of all, open the Excel and click on the office icon.

• After that, go to the “Open” and select the file which is corrupted.

• Now, click on the open drop-down and select “Open and Repair”.

• At this point, you have two options:

1. Repair File
2. Extract Data

Let’s get into both of these options one by one…

1. Repair File

This option helps you to repair the file and the moment you click on it it takes a few seconds afterward and shows you the result with a message box and also provide you a log file.

And once it is done with repairing, you’ll get your file opened and you can save that file as a new copy.

Yes, that’s it.

2. Extract Data

If somehow you aren’t able to get your file repaired, you can also extract data from that file using “Extract Data” option.

Even in this option, you can get data in two ways.

1. As Values
2. With Formulas

In the first option, Excel simply extracts data as value ignoring all the formulas driving those value (which is the best way if you just need to have that data back ).

But in the second option, Excel tries to recover the formulas as much as possible.

Check out this smart technique by Jyoti which you can use it you aren’t able to recover data from the file.

Preventions to Not to have any Excel File Go Corrupt in Future

Future is fragile, what I’m trying to say is the more you work in Excel and process data there could be a chance that your workbook goes corrupt.

If there’s no security then what an EXCEL POWER user should do?

Well, there are few things which you can do or take care of while working with Excel so that you won’t have to worry about corruption of Excel workbooks.

Let’s see what you can do…

1. Change Recalculation Option

Now here’s the thing when you work with a hell lot of data, there a common thing that you gotta using formulas. Right?

But, the thing these formulas are something which makes your Excel file slows down sometimes make them go corrupt.

There’s one small tweak you can do in your workbook is change the calculation method.

Now with the manual calculation, you just need to whenever you open your file it won’t recalculate all the formulas.

And when you update your data you can simply click on the “Calculate Now” and it will calculate all the formulas again.

Quick Tip: Beware of Volatile Functions and use them with caution as recalculates them every time you change something in the worksheet.

2. Use VBA Codes Instead of Formulas

Now, this is what I do when I need to use complex formulas in a workbook.

Here’s how you can do this: Let’s say you have a formula in the cell A1, like below, which calculates the age.

=“You age is “& DATEDIF(Date-of-Birth,TODAY(),”y”) &” Year(s), “& DATEDIF(Date-of-Birth,TODAY(),”ym”)& “ Month(s) & “& DATEDIF(Date-of-Birth,TODAY(),”md”)& “ Day(s).”

Now, instead of simply entering it into the cell A1 which I would write a macro code which inserts this formula into the cell A1 and then convert it into the a value.

Here’s the code:

Sub CalculateAge()
Range(“B1”).Value = _
“=””Your age is “”” & _
“&DATEDIF(A1,TODAY(),””y””)” & _
“&”” Year(s), “”” & _
“&DATEDIF(A1,TODAY(),””ym””)” & _
“&”” Month(s), and “”” & _
“&DATEDIF(A1,TODAY(),””md””)” & _
“&”” Days(s).”””
Range(“B1”) = Range(“B1”).Value
End Sub

Note: To write these code you need to have basic understading of VBA (make sure check out this guide for this).

3. Use a File Recovery Application

Recently we asked a quick question to our readers on ExcelChamps that if they have ever faced a situation where they got a corruption message in Excel.

You’ll be astonied to hear that 50% percent of the people said “YES” they faced this thing in the past.

Now, this is alarming, if you are heading a team or you have a bunch of people in your company who use Excel…

…there’s a high probability that half of them gonna face this issue. So the best way to deal with this to have an App FIX your Excel file for you.

With STELLAR REPAIR FOR EXCEL, you just need a few clicks, yes that’s right. Let me show you with the below steps:

• First of all, download the app and install it (it’s simple).

• After that, open the app and click on the “Browse” and simply select the file which is corrupted.

• In the end, click on the REPAIR to let the Excel repair software fix your file (it takes a few seconds).

Once you complete repairing your file, you’ll get a message in your on the status bar and after that, you can open your file.

Final Thoughts

If you are a POWER Excel user then there’s a must for you to have known how to deal with a situation where you got a corrupt Excel file.

But I must recommend you to TRY OUT Stellar Repair for Excel so that’s you don’t have to worry about your Excel files anymore.

I’m sure you found this post helpful, and please don’t forget to share this tip with your colleagues, I’m sure they’ll appreciate it.

[Fix] Excel formula not showing result

Summary: Is your Excel spreadsheet showing text of a formula you’ve entered and not its result? This blog explains the possible reasons behind such an issue. Also, it describes solutions to fix the ‘Excel formula not showing result’ error. You can try Stellar Repair for Excel software to recover engineering and shared formulas.

Sometimes, when you type a formula in a cell of worksheet and press Enter, instead of showing the calculated result, it returns the formula as text. For instance, Excel cell shows:

But you should get the result as:

Why Does Excel Show or Display the Formula Not the Result?

Following are the possible reasons that may lead to the ‘Excel showing formula not result’ issue:

1. You accidentally enabled “Show Formulas” in Excel.
2. The cell format in a spreadsheet is set to text.
3. ‘Automatic calculation’ feature in Excel is set to manual.
4. Excel thinks your formula is text (Syntax are not followed).
5. You type numbers in a cell with unnecessary formatting.

How to Fix ‘Excel Showing Formula Not Result’ Issue?

Solution 1 – Disable Show Formulas

If only the formula shows in Excel not result, check if you have accidentally or intentionally enabled ‘show formula’ feature of Excel. Instead of applying calculations and then showing results, this feature displays the actual text written by you.

You can use the ‘Show Formulas’ feature to quickly view all formulas, but if you are not aware of this feature, and enabled it accidentally, it can be a headache. To disable this mode, go to ‘Formulas’ and click on ‘Show formula enabled.’ If it’s previously enabled, it will be disabled by just clicking on it.

Solution 2 – Cell Format Set to Text

Another possible reason that only formula shows in Excel not result could be that the cell format is set to text. This means that anything written in any format in that cell will be treated as regular text. If so, change the format to General or any other. To get Excel to recognize the change in the format, you may need to enter cell edit mode by clicking into the formula bar or just press F2.

Solution 3 – Change Calculation Options from ‘Manual’ to ‘Automatic’

There is an “automatic calculation” feature in Excel, which tells Excel to do calculations automatically or manually. If ‘Excel formula is not showing results’, it may be because the automatic calculations feature is set to manual. This issue is not easily detected because it results in calculating formula in one cell but if you copy it to some other cell, it will retain the first calculation and will not recalculate on the base of the new location. To fix this, follow these steps:

• In Excel, click on the ‘File’ tab on the top left corner of the screen.
• In the window that opens, click on ‘Options’ from the left menu bar.
• From ‘Excel Options’ dialog box, select ‘Formulas’ from the left side menu and then change the ‘Calculation options’ to ‘Automatic’ if it’s currently set as ‘Manual’.

• Click on ‘OK’. This will redirect you to your sheet.

Solution 4 – Type Formula in the Right Format

There is a proper way to tell Excel that your text is a formula. If you don’t write the formula in a particular format, Excel considers it as simple text and hence no calculations are performed according to it. For this reason, keep the following in mind when typing a formula:

• Equal sign: Every formula in Excel should start with an equal sign (=). If you miss it, Excel will mistake your formula as regular text.

• Space before equal sign: You are not supposed to enter any space before equal sign. Maybe a single space will be hard for us to detect, but it breaks the rule of writing formulas for Excel.

• Formula wrapped in quotes: You need to make sure that your formula is not wrapped in quotes. People usually make this mistake of writing a formula in quotes, but in Excel, quotes are used to signify text. So your formula won’t be evaluated. But you can add quotes inside formula if required, for example: =SUMIFS(F5:F9,G5:G9,”>30″).

• Match all parentheses in a formula: Arguments of Excel functions are entered in parenthesis. In complex cases, you may need to enter more sets of parenthesis. If those parentheses are not paired/closed properly, Excel may not be able to evaluate the entered formula.

• Nesting limit: If you are nesting two or more Excel functions into each other, for example using nested IF loop, remember the following rules:

  • Excel 2019, 2016, 2013, 2010, and 2007 versions only allow to use up to 64 nested functions.
  • Excel 2003 and lower versions only allow up to 7 nested functions.

Solution 5 – Enter Numbers without any Formatting

When you use a number in the formula, make sure you don’t enter any decimal separator or currency sign, e.g. $, etc. In an Excel formula, a comma is used to separate arguments of a function and a dollar sign makes an absolute cell reference. Most of these special characters have built-in functions so avoid using them unnecessarily.

What to Do If the Manual Solutions Don’t Work?

If you’ve tried out the manual solutions mentioned above but still unable to resolve the ‘Excel formula not showing result’ issue, you can try repairing your Excel file with the help of an automated Excel repair software , such as Stellar Repair for Excel.

This reliable and competent software scans and repairs Excel files (.XLSX and .XLS). It also helps recover all the file components, like formulas, cell formatting, etc. Armed with an interactive GUI, this software is extremely easy to work with, and its advanced algorithms allow it to fend off Excel errors with ease.

Conclusion

This blog outlined the possible reasons that may cause ‘Excel not showing formula results’ issue. Check out these reasons and implement the manual fixes, depending on what resulted in the problem in the first place. If none of these fixes help resolve the issue, corruption in the Excel file might be preventing the formulas from showing the actual results. In that case, using Stellar Repair for Excel tool might help.

Solved - The File is Corrupted and Cannot be Opened - Excel

Summary: Unable to open Excel file due to the error ‘The file is corrupted and cannot be opened’? Read this blog to find more details about the error, possible reasons behind it, and solutions to fix the error. In addition, the blog mentions about Stellar Repair for Excel software that can help fix the Excel error in a few clicks. Download the software now and see free preview of the file.

About the Error

Microsoft Excel is a widely used spreadsheet application that comes bundled with MS Office. Users tend to update the application with new security patches and features. Sometimes these updates can cause problems, and result in “The file is corrupted and cannot be opened” error.

Figure 1 – Excel File Corrupted Error Message

Other Possible Reasons behind ‘The File is Corrupt and Cannot Be Opened’ Excel Error

• Opening an older Excel version file in a newer version of Excel. For instance, opening Excel 2013, 2010, or earlier versions in Excel 2016.
• When attempting to open a Microsoft Office (Excel) email attachment in Microsoft Outlook 2010, MS Office 2010 reports a problem with the file preventing it from opening.

How to Fix the ‘Excel File is Corrupt and Cannot Be Opened’ Error?

Here are a few possible solutions that you can try to fix the ‘Excel file is corrupt and cannot be opened’ issue and open your Excel file.

Solution 1: Changing Component Services Settings

Solution 2: Changing the Protected View Settings

Solution 3: Repair Excel Files using Excel Repair Software

Solution 1: Changing Component Services Settings

[Caution] Changing Component Services settings requires making changes to the registry, and any mistake can harm your computer.

Follow these steps to change ‘Component Services’ settings:

• Click ‘Start’ or ‘Win+R’ and type ‘dcomcnfg’ and press ‘Enter’.

• In the navigation pane, expand the ‘Component Services’, and then expand ‘Computers’.

---

Figure 2 – Component Services Settings

• Next, right-click on ‘My Computer’, and then click ‘Properties’.

When the ‘My Computer Properties’ dialog box appears, click on the ‘Default Properties’ tab and then set the following values:

• Default Authentication Level: Connect
• Default Impersonation Level: Identify

Figure 3 – Illustrates My Computer Properties

• Click ‘OK’ to change ‘Default Properties’

Solution 2: Changing the Protected View Settings

[Caution] Disabling the ‘Protected View’ can put your system at high risk. Viruses attached to the Excel files can attack and infect your system. Be careful before using this option.

Excel 2010 file cannot open due to the ‘Protected View’ setting in Microsoft Outlook 2010. And so, changing the setting may help fix the error. For this, perform these steps:

• Open MS Excel 2010, go to the ‘File’ menu and click ‘Options’.

Figure 4 – Options

• When the ‘Excel Options’ window opens, click on ‘Trust Center’ and then on ‘Trust Center Settings’.

Figure 5 – Open Trust Center Settings

• Next, choose ‘Protected View’ and uncheck all the options including ‘Enable Protected View for Outlook attachments’ if you use Outlook for email.

Figure 6 – Uncheck Protected View Settings

• Click ‘OK’. Restart the application and try opening the Excel file again.

If none of the above solutions works for you, your Excel file is likely severely corrupt. To repair corrupt Excel files, you need to use advanced options like Stellar Repair for Excel tool. It repairs corrupt and damaged Excel files and helps in retrieving lost data.

Solution 3: Use Excel File Repair Tool

Considering the risks associated with the above solutions, it’s better to use an Excel repair tool to repair single or multiple corrupt Excel files at once. The process is simple, and even a novice can use the Excel file repair tool to repair Excel files with the help of the following steps:

• Download Stellar Repair for Excel and install it.

• Launch the tool. In the tool’s main interface, click ‘Browse’ to select the file. If you don’t know the file location use the ‘Search’ option.

Figure 7 – Illustrates Selecting Corrupt Excel File in Stellar Repair for Excel

• Select the file, and then click on Repair.

Figure 8 – Illustrates Initiating Excel File Repair in Stellar Repair for Excel

• The software scans and lists the Excel file in the left pane. Click on the file to preview its recoverable objects in the right pane.

Figure 9 – Illustrates Preview of Recoverable Excel File Objects

• Save the repaired file at either the default location or a user-specified location.

Figure 10 – Illustrates Saving Repaired Excel File in Stellar Repair for Excel

• Click ‘OK’ to save the repaired Excel file. After the repair process is completed, browse to the location and open it with MS Excel 2010 or any other version.

Figure 11 – Illustrates Saving Complete Message in Stellar Repair for Excel

You will be able to access your Excel file from the selected location.

Conclusion

You can use the first two possible solutions to fix the “The file is corrupted and cannot be opened” error. If you can access the file, save its data and restore the default settings. However, if the file is corrupt and the data retrieved using the first two solutions is inconsistent or incomplete, use Stellar Repair for Excel. This tool can help you recover Tables, Charts, Chart Sheets, cell comments, Images, and Formulas while preserving the worksheet properties and cell formatting. You can also preview the file and verify the data inside the file before saving it.

Also read: