Author: Jessica

Have you ever tried working an extremely lengthy formula, made a tiny bit of an update, and pressed Enter only to get the #NAME error? It can be pretty nerve-racking, right? 

In this article, I’ll show you all the possible causes for the #NAME error and the steps you could apply to correct it.

What causes the #NAME error in Excel?

The #NAME error is Excel’s way of telling you that there is a text inside your formula that it does not recognize.

It could be caused by:

• a typo in the function name
• a syntax error                                     
• an invalid range address
• a reference name that does not exist
• a missing macro or add-in
• incompatible Excel version

Unlike other Excel errors, the #NAME error should be addressed and not just masked with error-handling functions like IFERROR(). Otherwise, your data could end up with inaccurate values.

Fix the #NAME Error

The first step in fixing the #NAME error in Excel is to identify what particular text inside the formula is causing the error.

To do this:

1. Select the cell that contains the #NAME error.

2. Go to Formulas >> (Formula Auditing Section) >> Evaluate Formula

3. The Evaluate Formula menu will appear. You should see your formula inside its textbox.

4. Click on the Evaluate button. Notice that it processes the formula one section at a time.

5. Click on the Evaluate button a couple more times until you see the #NAME error.

6. Take note of where the #NAME error appeared in your formula.

7. You may need to redo the steps if you’ve missed the text where the error occurred.

Once you’ve identified the text that caused the error, the next step is to apply the appropriate solution.

1. If the function name is invalid

There are three reasons why a function name is considered invalid:

• It has a typo.
• It requires a particular add-in or macro.
• It is not compatible with your version of Excel.

Of course, if the function name has a typo, you only need to correct it.

To check the spelling of a function, you could take advantage of Excel’s Formula Wizard. To do this:

1. Select a blank cell.

2. Type an equal sign ‘=’ followed by the Excel function.

3. Notice that as you type in the name, the Formula Wizard provides a list of Excel functions that might be what you intend to use.

4. Select from the list and add in the arguments, if needed. Once that is done, press Enter.

If you did not encounter any error, copy the formula and paste it onto your original cell.

If your Excel function doesn’t have any match from the Formula Wizard’s list of suggestions, it could be that:

• It only works on selected versions of Excel.
• It requires an Excel add-in.
• It is a customized function that requires a macro for it to work.

If you want to double-check to see if it indeed is not available (and not just a typo), you could:

1. Select a blank cell.

2. Go to Formulas >> Insert Function.

3. The Insert Function menu should appear. Type part of the function name inside the “search textbox” and press Enter.

4. All possible matches will appear in the dropdown list.

5. If it does not match any of the Excel functions, you may want to ask your colleagues if the formula works on their computers.

If it works on them, but not on yours, then it’s likely that it is not compatible with your version of Excel. Or you might be just missing an add-in or macro.

Ask them for their version of Excel and see if they installed an add-in or added any macro to run it.

Install the add-in or add the macro, if necessary. Then test it out again.

If these two options do not work, you may need to upgrade Office.

If you can’t afford to do that and you’re short on time, you could, in the meantime, borrow a computer with the same version of Excel as your colleagues and work on the data.

2. If the range is invalid

A range can be considered invalid for the following reasons:

• The range argument has a typo; or
• The specified named range no longer exists.

Look into each of the Range Addresses specified in your formula and see if they are correct. Check if they are not missing any row, column, or colon (:) for ranges with more than one cell.

In the screenshot above, notice that the end row is missing for the first set of ranges of SUMIFS function. (it should be F1:F4).

If you have used named ranges in your formula, check if they still exist in the list of named ranges. Check also their spelling. 

One quick way to do this is to select the cell with the formula and click on the formula bar.

Notice that the ranges in the formula bar have colored fonts.

Invalid named ranges will have a black font color (see “Items”).

To edit the list of named ranges:

1. Go to Formulas >> Name Manager

2. The Name Manager menu will appear. There you’ll see the list of all named ranges.

3. See if the named range in your formula exists in the list.

If you see it, close the Name Manager and correct the spelling in your formula.

If the desired Named Range doesn’t exist, add it. Or, adjust your formula to point it to the equivalent range address.

3. If the text argument in the formula is invalid

In adding text arguments to your formula, remember to enclose texts with double quotation marks (“).

Because if not, Excel will treat them as named ranges. And if it does not have a matching named range, you’ll have the #NAME error.

Conclusion

The #NAME error mainly happens if it sees a text in your formula that it does not recognize. To fix this, find the text that caused the error. Once that is covered, apply the appropriate solution.

We add passwords in an Excel file to restrict access and prevent unauthorized changes.

In this tutorial, I will go over the steps you can take to remove these passwords so you can access and make the necessary changes to your password-protected data.

Must Know:
For Mac Users: Know that Excel Mac has a 15-character password limit. You cannot open a file with a password that exceeds 15 characters. Ask a Windows user to open it and change the password to a maximum of 15 characters.

Remove a password:

1. From an Excel File

1. When you open a password-encrypted file, you will see a form pop-up asking for the password (as shown below).

2. Type in the correct password and click OK.

3. After opening it, go to File >> Info >> Protect Workbook.

Select the small down arrow next to the Protect Workbook option.

From the list of selections that appears, select Encrypt with Password.

4. The Encrypt Document pop-up menu will appear. Highlight the current password and press DEL.

Once you’ve cleared the password textbox, click OK.

And that’s it! Your Excel file is no longer password-encrypted.

To test it, save your changes, close the file and open it again.

2. From a Workbook

1. Go to File >> Info >> Protect Workbook.

2. Select the small down arrow next to the Protect Workbook option.

Select Protect Workbook Structure from the list of options that appear.

3. The Unprotect Workbook pop-up menu will appear.

Type in the correct password and click OK.

And that’s it. You have removed the password in a Workbook.

You can now add/edit/rename/delete the sheets inside it.

After making the necessary changes, if you want to re-add the password, follow the same steps specified above.

You could also do the following shortcut.

Go to Review and select Protect Workbook.

Type in the password, and voila! The workbook is again password-encrypted.

3. From Individual Worksheets (with a password)

1. Select the worksheet that you would like to edit.

2. Go to Review and select Unprotect Sheet.

3. The Unprotect Sheet pop-up menu will appear.

Type in the correct password and click OK.

After that, the current worksheet no longer has a password; therefore, you can start making the necessary changes.

If you want to re-add the password after making the changes, follow the same steps mentioned above.

If you want to unlock more cells that you like to be available for edits even without a password, follow the steps below. (Remember to do them before adding the password again.)

How to unlock cells for edits?

1. Select the cell(s) that you would like to unlock.

To select multiple cells, hold on to the CTRL key while clicking on the cells.

2. Press CTRL + 1. The Format Cells menu will appear.

3. Go to the Protection tab. Uncheck the Locked checkbox and click OK.

4. That’s it. You have successfully unlocked the selected cells for editing.

You can now re-add the password to the worksheet to prevent changes in locked cells.

4. From Individual Worksheets (without knowing the password)

If you have forgotten or lost the password in your worksheet, don’t worry. There is a workaround for this.

1. Open the folder where you have placed a copy of your Excel file.  

2. On top of the Folder Explorer, click on View and tick the File name extensions checkbox.

3. Notice that the file extensions appear. 

4. Change the file extension of your file from .xlsx to .zip.

You will see a prompt warning you of the effect of the changes that you have made.

Click Yes to continue.

5. You should now see your Excel file converted into a zip file (as shown below).

6. Right-click on the zip file and select Extract All…

7. Another prompt will appear asking you for the destination of the extracted files. Click on the Extract button.

8. Open the extracted files and go to xl > worksheets.

Select the worksheet that you want the password to be removed from.

9. Open the file with a text editor (e.g., Notepad, WordPad, Word, etc.)

Right-click on it and select Open With >> (your desired text editor).

10. Once the text editor opens, look for the “sheetProtection” tag inside the file.

Press CTRL + F and type the word “sheetprotection” (without the double quotes).

11. Highlight the entire sheetProtection tag and delete it.

Start by highlighting the opening tag (<) before the word “sheetProtection”.

Drag the mouse until you see the closing tag (/>).

Once highlighted, press DEL to delete it.

12. Save the changes to the XML file and close the text editor.

13. Re-zip the extracted files and change the file extension from .zip to .xlsx.

Differences in Restrictions for Password Protection in Excel

Excel provides different types of data protection depending on where you add the password. Data restrictions are different when you add the password in an Excel file, in a Workbook, and in a Worksheet.

Hopefully, this will give you an idea if there is a real need for you to unprotect the Workbook or the Worksheet for the changes that you intend to apply.

File Protection

When opening a password-encrypted file, you have no choice but to enter the password. You cannot open it and view its contents without the password. We usually apply this type of protection to files containing highly confidential data.

Workbook Protection

When a Workbook is password-encrypted, you are free to view the contents and make some changes. Your only restriction is that you cannot add a new Worksheet, edit, delete or even rename the existing Worksheets unless you enter the correct password. In short, Workbook Protection prohibits unauthorized users from changing the Workbook structure.

Worksheet Protection

When a Worksheet is password-encrypted, the changes allowed within that particular worksheet are limited. Only selected cells are open for edits.

All cells are, by default, locked for editing. But you can unlock cells to enable regular users to edit them freely without the password. This restriction is helpful for worksheets that require user inputs.

If you are an authorized user and there is a need for you to update the contents of locked cells within the worksheet, then you will have to enter the password.

Conclusion

There are three types of protection that you can add in Excel — File Protection, Workbook Protection, and Worksheet Protection. Where you place the password determines how your data is protected and how restrictions are applied. Follow the steps above to add, change, or remove the passwords in your file.

Also Read: How to Center a Worksheet Horizontally in Excel?

The #DIV/0 error is probably the most simple error to fix in Excel.

There is only one main reason for it, but there are a ton of ways that you could do to remove it.

In this article, I’ll show you the root cause of this error and some examples of solutions that you could do to correct it.

What causes the #DIV/0 error in Excel?

The #DIV/0 error will only appear when you try to divide a number by zero —  it’s that simple.

We all know that a number can’t be divided by zero.

But then you might ask, “My formula does not even have the division operator; why am I still having this error?”

If this is so, it could be because one of the cells referred to in your formula has the #DIV/0 error; thus, you need to remove the error from these cells.

Also, if you are working on the AVERAGE() function, ensure that the ranges specified in your formula have numbers.

If all the cells contain texts (or are left blank), you will have the #DIV/0 error.

If you are working on the AVERAGEIF() or AVERAGEIFS() function, check if your “criteria” is within the specified range of data.

The #DIV/0 error can occur in these functions if the “criteria” does not match any item in the range of data.

Once you’ve identified the cell where you need to make the changes, the next step is to apply the suitable solution.

Fix the #DIV/0 Error

If you are working on a complex formula with more than one Excel function, break it down into small chunks. Try to find where exactly the error occurs.

To help you find it, remember that the #DIV/0 error will likely appear in the following functions:

• Division (÷)
• AVERAGE()
• AVERAGEIF() or AVERAGEIFS()

If you are not using these functions, look into the cells specified in your formula. These cells may contain the #DIV/0 error. If so, then you have to remove the error from there.

1. Division formula with no divisor

If your divisor is a fixed number in the formula and is equal to 0, then the only way to fix the error is to change it to a non-zero number.

If your divisor is referring to a cell that is either blank or equal to zero, you could either:

• Edit the contents of the cell referred to in the formula. Change them into a non-zero number; or
• Edit your current formula so that it displays (or does) something else when the “source cell” is blank or equal to zero.

If you want to display a number or a text whenever an error occurs, you can use either of these formulas:

If you want to have a secondary divisor that you could use if the primary divisor is zero, then you could use the following formula:

=IF([divisor 1]=0, [dividend]/[divisor 2], [dividend]/[divisor 1])

This formula tells Excel to use Divisor 2 if Divisor 1 is equal to zero (or is left blank). 

What if Divisor 2 is left blank too? You could configure the formula to display, for example, a text to indicate that all the divisors are missing.

=IF([divisor 1]=0, [dividend]/[divisor 2], IF([divisor 2]=0, “Divisors Missing”, [dividend]/[divisor 1]))

2. Excel formulas with the AVERAGE() function

The #DIV/0 error in AVERAGE() functions will only happen if the cells referred to in the formula are either ALL blank or ALL texts (no numbers).

This error can occur whenever you try to copy cell formulas with no absolute references (no ‘$’ in the ranges).

To fix this, you only need to adjust the formula to point it to the correct cells – those with numbers.

3. Excel formulas with the AVERAGEIF() or AVERAGEIFS() function

The #DIV/0 error in AVERAGEIF() or AVERAGEIFS() can happen if the “criteria” does not match any of the values in the data range that you specified.

There are two ways to fix this:

• Change the “criteria” to match at least one of the values in the selected data set.
• Update the formula so that it displays (or does) something else whenever an error occurs. You can use the IFERROR() function for this.

Conclusion

The #DIV/0 error will only occur if a number has nothing to divide by (divisor = 0). There are, however, different reasons why this can happen.

Remember to check the range of cells specified in your formula. They might have the #DIV/0 error, so you’ll have to fix the issues from there first.

If you’ve been working on formulas in Excel, you’ll probably agree that one of the most dreadful things to see is the #VALUE error. I mean, what does #VALUE exactly mean, right?

In this article, I’ll show you the possible causes of #VALUE error and provide you with some solutions that you can do to fix it.

Please note that the #VALUE error can be very generic and could mean many things, so you might need to test out one or more of the solutions below to fix it.

What causes the #VALUE error in Excel?

The #VALUE error provides a bit of a clue on what is causing the problem – the value.

The problem, however, is that this error can mean different things in different Excel functions.

• The #VALUE error in the AVERAGE calculated, SUM, CONCATENATE, and IF functions may mean that one of the cells referred to in the formula contains an error (#VALUE, #DIV/0, etc.).

• The #VALUE error in the VLOOKUP (especially if you have multiple IF statements), COUNTIF, and MATCH functions may mean that the lookup value exceeds the 255-character limit.

• The #VALUE error in the SUMIFS and SUMPRODUCT functions may mean that the rows in the range arguments do not match.

• The #VALUE error in the FIND and SEARCH functions may mean that the find_text does not exist in the within_text. For the SEARCH function, it may also mean that the value set for start_num is invalid.

• For the other functions, the #VALUE error may mean that one of the values specified in the formula has the wrong data type.

Fix the #VALUE Error

If you are working on a complex formula with more than one Excel function, break it down into small chunks first. Copy part of the formula in one cell and see if that results in an error.

But before doing that, ensure that your formula contains absolute cell references (one with ‘$’ before the range addresses) so that it still points to the same cells.

These will narrow things down and make it easier for you to identify where the error started.

1. AVERAGE, SUM, CONCATENATE, and IF functions

If you’re working on AVERAGE, SUM, CONCATENATE, and IF functions, the #VALUE error may occur if one of the cells referred to in your formula contains an error (e.g., #VALUE, #DIV/0).

To fix this, you can do either of the following options:

1. Go to the range of cells specified in your formula and fix the errors that appear. If there are inevitable errors, update the cells’ formula to make it display something else when these errors occur.

Make sure that the replacement you select for the error is a valid value for your formula. You could, for example, use zero to replace the error.

=IFERROR([insert your formula], 0)

2. If you’re unable to update the “source cells” (the ones described above), update your formula so that it displays (or does) something else when an error occurs.

In the screenshot above, the text “Update Price List” appears whenever there’s an error with the total. 

2. VLOOKUP, COUNTIF, and MATCH functions

If you’re working on VLOOKUP, COUNTIF, and MATCH functions, the #VALUE error may occur if your lookup_value contains more than 255 characters. 

To fix this, you have to shorten the lookup_value.

If you’re open to having only the first part of the text used as the basis for the data matching, you could do the following:

1. Adjust the lookup value so that it only contains the first 255 characters. You could do this using this formula: =LEFT(A2, 255)

2. Go to the reference table. Insert a new column. This column will store the first 255 characters of the original text. Add the same formula as above.

3. Go back to your VLOOKUP, COUNTIF, or MATCH formula. Update it so that it points to the new column inserted.

Your lookup value and reference table should now be able to find a match.

3. SUMIFS and SUMPRODUCT functions

If you’re working on SUMIFS and SUMPRODUCT functions, the #VALUE error can occur if the rows in your range arguments do not match.

Adjust the range in the formula so that the total rows match. It should then look something like this:

4. FIND and SEARCH functions

If you’re working on FIND and SEARCH functions, you could encounter the #VALUE error if:

• The start_num is invalid.
  • start_num = 0
  • start_num > length of within_text

• The text searched for could not be found.

To fix this, check your formula to see if start_num is not invalid. It should not be equal to zero and should not be greater than the length of within_text.

If you still have the #VALUE error, there’s a chance that the text you’re trying to search for is not within the other text. If this is the case, you can update the formula to make it display something else whenever this happens.  

5. Other functions

If the above solutions do not apply to your function, check the values in your formula and see if they are in the correct data type.

Excel expects a particular data type for every value entered in the formula (whether it should be a number, a text, or a date). If one of the values is not in the expected format, you will get the #VALUE error.

To fix this, figure out the data type expected for each argument in your formula. See if it requires a number, a date, or a text.

If you have fixed values in your formula, check if their data type is correct.

• Texts and Dates should be inside double quotes (“).
• Numbers should not have any special characters (like leading apostrophes).  

If you have ranges inserted in your formula, check ALL of the cells within that range and see if they have the correct data type.

The tricky part about this one is that even though a cell may look like, for example, a date, it doesn’t necessarily mean that Excel sees it as a date. Excel may see it as a text.

This disparity happens when the cell contains special characters or spaces that are not so obvious to the human eyes.

Use the following formulas to check the data types of the cells.

If the cells are not in the expected format, select the cells within the specified range and update the Number Format (Press CTRL + 1).

If the cells are still not in the correct data type even after changing the Number Format, go over each cell and check for special characters. Check also for extra spaces in between. You may need to use formulas or macros to clean up the data.

To convert a cell into text, add a single quote (‘) at the start of the cell.

For the dates, ensure that they follow the date format on your computer.

Conclusion

The #VALUE error can mean a lot of things. So, the first thing that you need to do is figure out the most probable cause for the error in your formula.

Once you have that figured out, apply the appropriate solution. If the suggested solution doesn’t work, try the others. Just be patient. You’ll figure it out.  

Need to generate a list of all the files inside a particular folder? Don’t worry. You don’t need to go through all the files one-by-one to copy their name. That would be too time-consuming! We can automate this process using Excel.

The methods introduced here are functions that we don’t commonly use in Excel, so I suggest going over them, so you’ll learn some new techniques. You never know. You might find your favorite method in the latter part of the article.

Copy Filenames Inside a Folder and Paste Them on Excel Using the ‘Copy as Path’ Method

1. From the File Explorer, open the folder which contains the files you want to get the filenames from.

If you need to unsort the data first, go ahead.

2. Select all the files you want to get the names from.

If you want to select all the files inside the folder, click on one file first and press CTRL + A to select the rest.

3. Hold the SHIFT key and right-click one of the selected files.

IMPORTANT: Ensure that you press the SHIFT key while right-clicking one of the selected files. If not, you won’t be able to perform the next step.

From the dropdown list that appears, select “Copy as Path”.  

4. Open the workbook (can be the workbook you saved without formulas) where you want to paste the list of files.

Select one of the cells and press CTRL + V to paste.

5. And that’s it! You should now have a list of all the filenames. All done in a few seconds.

PRO TIP:

If you want to only keep the filename, not the entire folder path, you can use Excel’s Find and Replace function to search for the folder path and replace it with a blank to remove it.

To do this:

1. Select one of the cells with the filename and go to the formula bar.

Highlight the entire folder path and press CTRL + C to copy it.

2. Once it’s copied, select all the cells that contain the filenames.

To make things easier, you can select the entire column by pressing the corresponding column letter on top.

3. Afterward, press CTRL + H. This will open the Find and Replace menu.

1. Go to the Find What textbox and press CTRL + V to paste the folder path there.
2. Leave the Replace with textbox blank.
3. Click the Replace All button.

4. And that’s it! You’re now left with just the filenames inside the selected folder.

Copy the Filenames Inside a Folder Using Excel’s FILES() Function

FILES() is a special Excel function that allows you to get the list of files inside a folder without VBA or Power Query.

You won’t, however, just find it in the list of available functions in Excel. It can only be used in Named Ranges.

To use it:

1. On a blank cell, enter the folder path where you want to pull the list of files from and add an asterisk (*) at the end of it.

Example: D:\Users\ACER SWIFT 3\Desktop\Work\Client Files\*

2. Go to the Formulas tab and click Name Manager.

3. When the Name Manager menu appears, click the New button.

4. We will now create a named range that will get the files in the specified folder.

To do this:

1. In the Name textbox, type “Filenames”.
2. In the Scope dropdown menu, select “Workbook”.
3. In the Refers To textbox, enter this formula: =FILES([range]). Change [range] with the address of the cell where you entered the folder name.
4. Once done, click OK.

5. You should now see “Filenames” in the list of named ranges. Click the Close button to continue.

6. We’ll now add a formula to refer to this named range.

On a blank cell, enter the following formula: =IFERROR(INDEX(Filenames, ROW()-2),””)

Change “2” with your current row minus 1.

In my example above, I’ve added the formula in row 3, that’s why I added 2 in my formula.

If you added the formula in row 1, remove “-2” altogether.

7. Drag the Fill Handle Down to copy the formula to the remaining rows.

Keep dragging the Fill Handle down until you see the last filename added to the list.

Another way to find this out is that if you start seeing blank cells, it means that you’ve reached the last filename.

And that’s it! You have successfully collected all the filenames inside the file.

What’s great about this method is that if you want to view the files in another folder, you only need to change the folder specified. Just don’t forget to add the asterisk at the end.

Copy the Filenames Inside a Folder Using Excel’s Power Query

Another quick way to get the filenames inside a folder is by using the Power Query in Excel.

1. Go to the Data tab and click Get Data >> From File >> From Folder.

2. Wait until the Power Query has fully loaded.

The Folder Explorer will then appear. Select the folder where you want to pull the list of files from.

Once you see your desired folder in the Folder name textbox, click Open.

3. You’ll then see a preview of the data collected by Power Query. Notice that it contains not only the filenames but also other details of the files.

Click the Transform Data button.

4. Delete the columns that you don’t need by clicking on the column header and pressing the DEL key.

5. Once you’re happy with the remaining fields, press the Close & Load button.

That’s it! You now have a complete list of all the files inside the selected folder.

What’s great with this method is that if you need to get the updated list of files inside the folder, you only need to right-click on the table and select Refresh. 

Copy the Filenames Inside a Folder Using Excel VBA Code

If you prefer automating things using VBA, you’ll like this method. Here, we’ll be creating a custom function and you can even configure it to only show files in a particular format.

1. Press ALT + F11 to open the VBA Editor.

2. Go to the Insert menu and select Module.

3. Copy the following code onto the new module added.

Function GetFiles(ByVal FolderPath As String) As Variant
'returns an array of the filenames within the specified folder path

    Dim objFSO As Object, flder As Object, fl As Object, ctr As Long, flArr() As Variant
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set flder = objFSO.GetFolder(FolderPath)
    
    ReDim flArr(1 To flder.Files.Count)
    
    For Each fl In flder.Files
        ctr = ctr + 1
        flArr(ctr) = fl.Name
    Next
    
    Set flder = Nothing
    Set objFSO = Nothing
    
    GetFiles = flArr
End Function

4. Close the VBA Editor and go back to your worksheet.

5. On a blank cell, enter the folder path which you would like to pull the list of files from.

In my example above, I’ve added it in cell A1.

6. Now, it’s time to use the custom function we just added.

On a blank cell, enter the following formula: =IFERROR(INDEX(GetFiles($A$1), ROW()-2),””)

Change “$A$1” with the address of the cell where you placed the folder path.

Change “2” with the current row number minus 1.

I’ve added the formula in cell A3, that’s why I subtracted ROW() with 2.

7. Drag the Fill Handle down to copy the formula to the remaining cells.

Keep dragging it down until you see the last file in the folder listed.

And that’s it! You should now have a list of all files (including DBF files) inside the folder.

If you want to only get the filenames of files in a specific format (e.g., Excel files only), you can do so by using the following code:

Function GetFilesByExt(ByVal FolderPath As String) As Variant
'returns an array of the filenames within the specified folder path based on a file extension

    Dim objFSO As Object, flder As Object, fl As Object, ctr As Long, flArr() As Variant, fileExt As String
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set flder = objFSO.GetFolder(FolderPath)
    
    fileExt = ".xls"    'change this with the file extension of files you want to include in the list
    
    For Each fl In flder.Files
        If InStr(1, fl.Name, fileExt, vbTextCompare) > 0 Then
            ctr = ctr + 1
            
            ReDim Preserve flArr(1 To ctr)
            flArr(ctr) = fl.Name
        End If
    Next
    
    Set flder = Nothing
    Set objFSO = Nothing
    
    GetFilesByExt = flArr
End Function

Just don’t forget to change the file extension specified in the code if you want a different file format (see highlighted text below). You can, for example, change “.xls” to “.doc”.

Also, to use this function, enter the following formula on a blank cell: =IFERROR(INDEX(GetFilesByExt($A$1), ROW()-2),””)

            Don’t forget to change “$A$1” and “2” with the appropriate values.

For other things that you need to copy in Excel, refer to the following articles.

Conclusion

Getting the list of filenames inside a folder is a very simple task, but it can take a long time if you don’t use the right tools to do it. Who would have thought that you can do it with Excel? I hope you were able to find the method that you were looking for.

Checkboxes are great for making a sheet interactive. They make it easy for anyone to change a value and select or unselect an item.

However, if you no longer need them, you may find that they are not that easy to remove from the sheet (which probably explains why you’re reading this now). But don’t worry! I got you.

Deleting them may not be as straightforward, but once you have gone through the methods described below, I’m sure you will see that it’s not that difficult to do.

Two Types of Checkboxes

Before we can delete a checkbox, we must first determine the type of checkbox added to our sheet.

The type of checkboxes we have will determine the method we need to use to remove them.

There are 2 types of checkboxes:

1. Form Control Checkbox – this is built-in in Excel and is recommended for those who want a checkbox for simple purposes. If you don’t have any background on VBA but want to add checkboxes to your sheet, you should use this one because it doesn’t require any code. You can link it to a cell with just a few clicks.

2. ActiveX Control Checkbox – this is not built-in in Excel, so it runs separately when you open the file. This type of checkbox is perfect for advanced users who want to customize the appearance and other properties of the checkbox. This checkbox may require a strong knowledge of VBA for it to work well.

If you don’t know what type of checkbox is in your sheet, go to the Developer tab.

If you can’t find the Developer tab:

1. Go to File >> More >> Options.
2. Once the Excel Options menu appears, click on Customize Ribbon.
3. On the right side of the menu (where there are green checkboxes), look for Developer, tick the checkbox and click OK.

From the Controls section, check if the Design Mode is selected. If it’s not, click on it.

We need to turn on the Design Mode to be able to select the checkboxes.

Right-click on your checkbox.

A checkbox is a Form Control if you see Assign Macro in the right-click menu.

On the contrary, a checkbox is an ActiveX Control if you see Properties.

How to Delete a Form Control Checkbox?

To delete a Form Control checkbox:

1. While pressing the CTRL key, click on the checkbox you want to delete.
2. Once the checkbox is selected, press DEL.

If you have turned on the Design Mode, remember to turn it off after deleting the checkboxes.

This step is optional for Form Controls, but it’s a good practice just in case you have ActiveX Controls added to your sheet.

How to Delete an ActiveX Control Checkbox?

To delete an ActiveX Control checkbox:

1. Simply click on the checkbox you want to delete and press DEL.
2. If you can’t select the checkbox, it means that the Design Mode is switched off. From the Developer tab, click on Design Mode to activate it.
3. After deleting the checkboxes, don’t forget to switch off the Design Mode to have the remaining ActiveX controls in the sheet work again.

How to Delete Multiple Checkboxes in One Go (Regardless of Type)?

If you have multiple checkboxes that you want to delete and you’d like to remove them all at once (without clicking on them one by one), then this method is perfect for you.

1. Switch on the Design Mode.

To do this, go to the Developer tab and make sure that the Design Mode button is activated.

2. From the Home tab, go to the Editing section.

3. Click on Find & Replace >> Go to Special.

4. The Go To Special menu will appear. Select Objects and click OK.

5. You will be redirected back to your sheet. Notice that all the checkboxes and all other objects inside your sheet are now selected.

6. Go over each object selected in your sheet and make sure that only the checkboxes you want to be deleted are selected.

Unselect all other checkboxes and objects (e.g., buttons, shapes, tornado charts) that you don’t want to be removed.

You can do so by clicking on them while pressing the CTRL key.

In my example above, I only wanted to delete Checkbox2 and Checkbox3 from the Form and ActiveX Controls, so I made sure that they are the only ones selected.

7. Once the desired checkboxes are selected, press DEL.

And that’s it! The previously selected checkboxes should now be removed from your sheet.

After removing all the targeted checkboxes, remember to switch off Design Mode from the Developer tab. This step is necessary to have the remaining ActiveX Controls working again.

How to Delete All Checkboxes in One Go Using VBA?

This method is perfect for you if you have a lot of checkboxes in your sheet and would like to delete them all in one go using VBA.

1. Activate the sheet where you would like to delete the checkboxes.

This step is crucial because the code we will use will only delete the checkboxes from the active sheet.

2. Press ALT + F11. The VBA Editor will appear.

Click the Insert menu and select Module.

3. A new module will be inserted (shown on the left pane).

4. Paste the following code on that module.

Option Explicit

Sub DeleteAllCheckboxes_FormControl()
'use this to delete all Form Control checkboxes in the active sheet 

    Dim shp As Shape
    
    For Each shp In ThisWorkbook.ActiveSheet.Shapes
        If shp.Type = msoFormControl Then
            If shp.FormControlType = xlCheckBox Then shp.Delete
        End If
    Next
End Sub

Sub DeleteAllCheckboxes_ActiveXControl()
'use this to delete all ActiveX Control checkboxes in the active sheet. 

    Dim shp As Shape
    
    For Each shp In ThisWorkbook.ActiveSheet.Shapes
        If shp.Type = msoOLEControlObject Then
            If shp.OLEFormat.Object.OLEType = xlOLEControl Then shp.Delete
        End If
    Next
End Sub

5. Notice that there are two subroutines in the code. One is for deleting all Form Control checkboxes, while the other is for deleting all ActiveX Control checkboxes.

Run the subroutine that corresponds to the checkboxes you want to delete.

6. To run the code, click anywhere within the appropriate subroutine and press F5.

In my example above, I have placed the cursor in the second subroutine because I wanted to delete all the ActiveX Control checkboxes in my sheet.

7. After running the code, go back to your sheet and check if all the targetted checkboxes are removed.

Conclusion

Deleting checkboxes may not be as straightforward as expected, but I hope this article helps you see that it’s not actually that difficult to do. You only have to know first the type of checkboxes you want to delete. Once you’ve identified that, you can apply one of the methods described above.

And while you’re at it, learn how to delete other stuff in Excel, too with the following articles.

Need to transform your long list of negative numbers to positive? I got you.

Whether you need to permanently convert your list of negative numbers to positive or just want to have them appear as positive numbers (but still remain negative), we have ways for that.

I would suggest going through each method so you can find the best one that fits your need.

In the latter part of this article, I’ll also show you how you can automatically transform negative numbers into positive ones using VBA and Power Query.

Change Negative Numbers to Positive in Excel (Appearance Only)

If you need to keep your numbers to stay negative and only want them to appear as positive numbers in the cells, then this method is for you.

1. Highlight all cells that contain your negative numbers. 

Note: It doesn’t matter if some of the cells contain positive numbers. You can include them in the selection.

2. Go to the Home tab and click the Number Format button (see the small arrow in the Number section).

3. The Format Cells menu will appear.

1. Go to the Number tab.
2. From the list of Categories, select Custom.
3. Copy the following text to the Type textbox: #,###;#,###
4. Once done, click OK.

The custom format we entered states that both the positive and negative numbers will have the same number formatting — no negative sign, parentheses, or any symbol to highlight the negative numbers.

4. After clicking OK, you should immediately see the selected cells all contain positive numbers.

When you click on each cell and look at the formula bar, you should see that the negative numbers stay negative (but only appear positive in the cells).

Change Negative Numbers to Positive in Excel (Replace the Actual Values)

If you want to permanently replace all negative numbers with their positive counterpart, then we have two ways to do that.

Method #1: Using the Paste Special Method

Note that this method will only work best on cells that only contain negative numbers (no positive numbers in between).

1. On a blank cell, type -1.

2. Select this cell and press CTRL + C to copy it.

3. Select all the cells that only contain negative numbers.

Note: The cells that you select should only contain negative numbers.

If your list of numbers contains both positive and negative, please proceed to the next method as that will likely be more convenient for you.

4. Press CTRL + ALT + V to open the Paste Special menu.

In the list of Paste options, select Values.

In the list of Operations, select Multiply and click OK.

5. And that’s it! All the negative numbers in your selected cells should now turn positive.

Method #2: By Removing All the Dashes

We all know that the dash that comes before a number signifies that a number is negative. So, one of the fastest ways to change a number from negative to positive is to remove this symbol.

To do this:

1. Highlight all the cells that contain negative numbers. It doesn’t matter if this group of cells contains positive numbers too.

2. If these cells contain formulas, you’ll need to copy and paste them as values first.

If you like, you can paste them on a different column to preserve the cells that contain formulas.

To do this:

1. Highlight the cells with numbers.
2. Press CTRL + C to copy them.
3. Select a cell where you’d like to paste them (it could be in the same column or in a different column)
4. Press CTRL + ALT + V to open the Paste Special menu.
5. From the Paste Special menu, select Values and click OK.
6. And that’s it. The formulas are now removed from the cells, and only the values remain.

3. With the cells highlighted, Press CTRL + H to open the Find and Replace menu.

1. In the Find What textbox, type a dash (-).
2. Leave the Replace with textbox blank.
3. Click the Replace All button.

4. And that’s it! All the negative numbers in your group of cells should now be converted to positive.

Change Negative Numbers to Positive in Excel (in a Separate Column)

If you prefer converting the negative numbers in a separate column, then you would like the following methods. With these methods, it doesn’t matter if some of the cells contain a positive number.

Method #1: Using the IF() Function

1. On a new column, type the following formula: =IF([range]>=0, [range], [range]*-1)

Example: =IF(A2>=0, A2, A2*-1)

What this formula does is that if it sees that the range contains a number greater than or equal to 0, it retains that value. Otherwise, it multiplies the number by -1 to get the positive value.

2. Drag the Fill Handle down (up to the last row in your dataset) to copy the formula to the remaining cells.

3. And that’s it! You have your negative numbers converted into positive numbers in a new column.

If you want, you can copy and paste this column as values to remove the formula and only retain the values.

Method #2: Using the ABS() Function

1. On a new column, enter the following formula: =ABS([range])

Example: =ABS(A2)

What this formula does is that it returns the absolute value of a number, that is, without the sign.

2. After entering the first formula, drag the Fill Handle down (up to the last row in the dataset).

3. And that’s it! You have successfully transformed all numbers into their positive format.

Method #3: Using Flash Fill

If you don’t like formulas as much, then this method might work for you.

1. Insert a blank column just next to the numbers you want to convert.

IMPORTANT: This new column should be just beside your numbers. Otherwise, this method will not work.

2. In the first data row, type the output you want to generate.

So, in our case, type the positive equivalent of the negative number in the original column (as shown in the example below).

3. Next, drag the Fill Handle down (up to the last row in the dataset).

4. Notice that the number you have entered will be repeated in the remaining cells.

Don’t worry. This is just temporary.

5. Click the AutoFill Options button (the one that appears in the last cell).

From the dropdown menu, select Flash Fill.

6. And that’s it! You should see the corresponding positive numbers in this new column.

Change Negative Numbers to Positive in Excel Using VBA

If you want to automate the process of converting negative numbers to positive ones, you can use the following code:

Option Explicit

Sub ConvertToPositive()
'convert numbers within selected cells to positive

    Dim rng As Range, cl As Range
    
    On Error Resume Next
    Set rng = Application.InputBox(prompt:="Select the cells to convert to positive:", Type:=8)
    If Err.Number > 0 Then Exit Sub
    
    On Error GoTo 0
    If Not rng Is Nothing Then
        For Each cl In rng
            cl.Value = Abs(cl.Value)
        Next
    End If
End Sub

To add this code to your workbook:

1. With your file open, press ALT + F11  to open the VBA Editor.

2. Go to the Insert tab and select Module.

To run the code, click anywhere inside the code to move the cursor there and click the Play button on top.

A prompt will then appear asking you to select the cells containing numbers that you’d like to convert to positive.

1. Go back to your worksheet and select the cells you want to convert.
2. The address of the cells that you have selected should appear in the input box.
3. If you’re happy with it, click OK.

And that’s it! All the numbers inside the selected cells should now become positive.

Change Negative Numbers to Positive in Excel Using the Power Query

If you prefer a Power Query over a VBA Code to automate the number conversion, you can do the following:

1. Select all the cells within your entire dataset.

2. Go to the Data tab and click the From Table/Range option under the Get & Transform Data section.

3. The Power Query will load for a few seconds. Then, a prompt will appear to confirm the range of cells that will be included in the table.

Click OK to continue.

4. The Power Query Editor will appear.

Right-click the header of the field that contains your negative numbers.

From the dropdown list that appears, select Transform >> Absolute Value.

5. And that’s it! All the negative numbers should be converted to positive ones.

You should also see “Calculated Absolute Value” as one of the Applied Steps.

The next time you run this power query, the selected field will automatically be converted to contain positive values.

Also, if you wonder how to convert different types of data within Excel, you can check these articles:

Conclusion

As you can see, there are a lot of ways to convert negative numbers to their positive equivalent in Excel. I hope you were able to find the solution that you were looking for.

Having macros that automate custom tasks in Excel can be huge time savers. Like any tool, it would be ideal to have these macros easily accessible so we can run them any time we need them.

We often add buttons (like radio buttons) in specific worksheets to run our macros. However, finding the button to run the macro would be too burdensome if you have loads of worksheets in your workbook.

This is why setting up our macro button in the Excel Ribbon is a good idea. Whatever sheet we may be on, we can always run the macro with just a few clicks.

Also, if you have macros that you want to be available in other workbooks as well, you can do so when you add them to the Excel Ribbon.

Steps to Permanently Add Macros in Excel Ribbon

Step #1: Add the Macros to the Personal Macro Workbook

A Personal Macro Workbook is a special workbook that runs in the background whenever we open the Excel app.

We will use this workbook to store the macros that we have created. This way, the macros are still accessible even when we open a different Excel file.

To create a personal macro workbook:

1. Go to the Developer tab and click the Record Macro button.

2. The Record Macro menu will appear.

Click the “Store macro in” dropdown list. Select Personal Macro Workbook. After that, click OK.

Excel will then proceed with recording whatever you do in the file and create its corresponding macro.

I’m assuming that you already have your macros ready, so all you need to do next is stop the recording by clicking the Stop Record button (just below the first worksheet).

(However, if you need to record a macro, please proceed with performing the steps you wish to be automated in the workbook and then click on the stop button once done.)

3. Once you click the Stop Recording button, the Personal Macro Workbook should now be created.

To verify this, press ALT + F11 to open the VBA Editor.

On the list of projects, you should see PERSONAL.XLSB.

If you did some steps while the Record Macro is turned on, you should see the macro recorded on Module1 of this file.

4. Highlight the entire code in this module and delete it (if needed).

Then, add the code of your macros there.

That’s it! Your Personal Macro Workbook should now all be set up.

Step #2: Customize the Excel Ribbon

Now, it’s time to add the button(s) in the Excel Ribbon.

(Note: If you prefer adding a button in the Quick Access Toolbar, you can proceed to the next section of this article.)

1. Right-click anywhere on the Excel Ribbon.

2. A pop-up menu will appear. Select Customize the Ribbon.

3. In the Excel Options menu, click the New Tab button.

The new tab will then be inserted in the list of Main Tabs.

4. Right-click on the new tab and select Rename.

5. Enter your desired tab name.

In my example above, I’ve named it “Macros”.

6. Next, let’s rename the group under the current tab.

Right-click on New Group (Custom) and select Rename.

Type the appropriate name for the group.

In my example above, I’ve named the group “Worksheet” because the macros that I will add to it are worksheet-related functions.

If you have multiple macros to add, you can add more groups so you can group related macros.  

To do this, just click the New Group button at the bottom.

The new group will then be added to the current tab.

Don’t forget to give it a new name by right-clicking on it and selecting Rename.

7. After adding all the groups, it’s now time to add the macro buttons.

1. Click the first group you would like to add buttons to.
2. Click the “Choose commands from” dropdown list. From the list of options, select Macros.
3. You should see the macros added in the PERSONALXLSB file. Select the first macro you would like to add to the group.
4. Once selected, click the Add >> button.

Repeat these steps until you’ve added all macros to their appropriate group.

8. Next, let’s rename each of the macros to specify how they will appear on the buttons.

1. Right-click on the macro and select Rename.
2. Choose the symbol or the image that you’d like to appear on the button.
3. Edit the Display Name.
4. Once done, click OK.

Repeat these steps until you’ve renamed all your macros and added their corresponding symbols.

9. You can re-arrange the order of the macros, the groups, and the tabs.

You just need to select the object (whether it’s the macro, group, or tab) and click the up or down arrow on the right side.

10. If you’re happy with the names and the arrangement of the tab, click the OK button.

And that’s it! You have successfully added your macro(s) to the Excel ribbon.

You can click on these macro buttons to test them.

Also, try closing the current file. Then open a new one and see if you still have the macro buttons visible in the Excel Ribbon.

You can also add a custom autofill list after creating your customized Excel ribbon.

ADDITIONAL TIPS

• If you must update your macros and need to temporarily disable or hide the buttons from the Excel Ribbon, just uncheck the corresponding tab from the Customize Ribbon menu (as shown below).

• To permanently remove them from the Excel Ribbon, right-click on the corresponding tab and select Remove.

• To reset all the changes made to the Excel Ribbon, just click Reset >> Reset All Customizations.

Permanently Add Macros in the Quick Access Toolbar

Aside from the Excel Ribbon, you can also permanently add the macros in the Quick Access Toolbar (QAT).

By default, the QAT is located just above the Excel Ribbon.

With the QAT, you can add the macros as icons on top. Users can readily access them without browsing through the tabs. You won’t, however, see the names of each function unless you hover the mouse over each icon.

To add your macros to this toolbar, do the following:

1. Add the macros to the Personal Macro Workbook (same steps as above).

2. Click the Customize Quick Access Toolbar button (see the last icon in the QAT).

A pop-up menu will show up. Select More Commands.

3. You’ll then be redirected to the Excel Options for Quick Access Toolbar.

1. In the “Choose commands from” dropdown list, select Macros.
2. From the list of macros that appear, select the macro you want to add to the QAT.
3. Click the Add >> button.
4. You should now see your selected macro in the list of QAT functions.

4. Next, we’ll modify the macro to change its display name and icon.

1. Select the macro from the list of QAT commands.
2. Click the Modify button.
3. Select the icon that you’d like to appear in the QAT.
4. Edit the display name.
5. Once done, click OK.

5. You should now see the updated name and icon for your macro. If you’re happy with it, click OK.

6. That’s it! You should now see your macro added to the QAT.

Conclusion

If you want your macros to be accessible from any Excel workbook that you open, you need to add them to the Excel Ribbon (or the Quick Access Toolbar) as described in the steps above. Remember to add the macros to the Personal Macro Workbook – this is the only way the macros can run from any Excel file on your computer.

By default, Excel names worksheets in this format: Sheet1, Sheet2, Sheet3.

We could leave them as is. However, it would be more ideal to give them a more meaningful name so that when we use them as references in formulas, we can readily pinpoint where the values are taken from.

Also, when you have loads of worksheets in your workbook, other users can immediately locate the sheet they need to go to when the worksheets have descriptive names.

Rules in Naming a Worksheet

Before going into the steps on how to rename worksheets, please take note of the following rules when renaming a worksheet:

• It must not be left blank.
• It must only have a maximum of 31 characters.
• It must not contain any of these characters: / \ ? * : [ ]
  • If you want to add a date to be used for sorting later in the worksheet name, you can’t add it in this format: 01/01/2021 Inputs.
  • However, you can swap the slash with a dash: 01-01-2021 Inputs.
• It must not start (remove the apostrophe if it does) or end with an apostrophe (‘), but you can add the apostrophe in between texts or numbers.
• It must not be named “History”. This is a reserved word that Excel uses internally.
• It must not have the same name as any of the existing sheets.

Rename a Worksheet in Excel by Double-Clicking the Worksheet

This is probably the default method that most people use when renaming a worksheet:

1. Double-click on the worksheet. The entire worksheet name will be highlighted.

2. Enter the new name and press ENTER.

That’s it! It’s super quick and easy.

Just repeat the same steps to rename other worksheets.

Rename a Worksheet in Excel by Right-Clicking on the Worksheet

If the left button in your mouse has been overused and is not working as it’s supposed to be, double-clicking can already become a difficult task. Luckily, you can still rename a worksheet using the right mouse button.

1. Right-click on the worksheet you want to rename. A pop-up menu will appear. Select Rename.

2. Type the new name and press ENTER.

That’s it! The worksheet name will be updated if it doesn’t violate any of the naming rules.

Rename a Worksheet in Excel Using Keyboard Shortcuts

If you prefer doing things with keyboard shortcuts, then you will like this next method.

To rename a worksheet:

1. Activate the worksheet you want to rename.

2. Hold the ALT key and press H + O + R.

The name of the worksheet will be highlighted.

3. Type the new name and press ENTER.

PRO TIPS:

• If you need to also rename the next worksheet, just press CTRL + Page Up to activate the next sheet and repeat steps 2 and 3.
• If you need to go to the previous sheet, press CTRL + Page Down.

Rename a Worksheet in Excel from the Excel Ribbon

You can also rename a worksheet from the Excel Ribbon.

1. Activate the worksheet you want to rename.

2. From the Home tab, find the Cells section and click the Format button.

3. A drop-down menu will appear. Select Rename Sheet.

4. The current worksheet name will be highlighted.

Type the new name and press ENTER.

Rename a Worksheet in Excel Using a Macro or VBA

If you need to rename all worksheets in your workbook to add a prefix or a suffix, then this method is perfect for you.

IMPORTANT:

Before doing the steps below, please secure a copy of your workbook for backup. Once the macro you added runs, the steps cannot be undone, so it’s better to have a backup just in case.

In my example below, I need to add the year as the prefix to all the worksheets inside the workbook.

To do this:

1. Press ALT + F11 to open the VBA Editor.

2. Go to the Insert menu and select Module.

3. Next, we’ll insert the appropriate code into your new module.

Copy the following code if you need to add a prefix to your worksheets:

Option Explicit

Sub AddPrefixToWorksheet()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.Name = "2020 " & ws.Name
        End If
    Next
End Sub

On the other hand, if you need to add a suffix, use the following code:

Option Explicit

Sub AddPrefixToWorksheet()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.Name = ws.Name & “ 2020”
        End If
    Next
End Sub

4. Once you’ve copied the appropriate code, change the text inside the double quotes with the prefix or suffix you want to add.

Just remember to leave the double quotation marks as is.

5. Once you’ve added the correct text, you can now run the code.

IMPORTANT:

Please note that the code above only updates the visible worksheets. If you need to update all worksheets inside the workbook, ensure they are all visible before running the code.

To run the code, place the text cursor anywhere inside the code and press F5.

6. That’s it! Close the VBA Editor and go back to your workbook.

You should now see the worksheets updated with the prefix or suffix you have added.

7. If you want to be able to use this macro next time, remember to save the file in .xlsm or .xlsb format.

On the other hand, if you intend to still save it in .xlsx format, when the following prompt appears when you save the workbook, click Yes.

Conclusion

Renaming worksheets is an essential task if you intend to make your workbook easy to read and use. I hope the methods listed can help you do this task on the fly.  Also, you can check the “How to Delete A Sheet in Excel” article.

You just finished setting up the formulas in your workbook and now you’re wondering if there’s a way to save the file without the formulas so you can share it with someone who doesn’t need to know how the calculations are performed. There sure is.

IMPORTANT:

Before trying any of the methods described below, please secure a copy of the file first. We want to preserve the formulas in the original workbook; otherwise, you might end up re-adding all the formulas one by one in case you need them again especially after accidentally deleting the sheet.

Save the Data or Workbook Without Formulas in Excel by Copying and Pasting the Cells as Values  

1. If you have multiple sheets in your workbook and you only want to share some of them, copy these sheet(s) first onto a new workbook. (Skip this step if you intend to share all sheets inside the workbook).

To do this, activate all the sheet(s) you want to share.

While pressing the CTRL key, select these sheets.

Right-click on one of the selected sheets. A pop-up menu will appear. Select Move or Copy.

When the Move selected sheets menu appears, select (new book) from the dropdown list.

Tick the Create a copy checkbox, and click OK.

You should now be redirected to a new workbook containing only the worksheets that you have selected.

2. Now, it’s time to select all cells within the sheet(s) and copy and paste them as values.

If you did Step 1 and have selected multiple sheets, leave these sheets selected or activated. Doing so will tell Excel to apply the same changes to these selected sheets.

Select all the cells within the sheet(s) by clicking the triangle located at the top left corner (near cell A1).

Once the cells are all selected, press CTRL + C to copy them.

You should now see moving broken lines surrounding the selected cells.

When that happens, press CTRL + ALT + V. This will open the Paste Special menu.

Tick the Values option, then click OK.

And that’s it! You have successfully removed all the formulas from the selected sheet(s) and only retained the resulting values.

Don’t forget to press ESC to deactivate the copy mode.

Also, if you have selected multiple sheet(s), remember to select one of the sheets to disengage multiple sheet selections.

You can now proceed with saving this file and sending it to the intended recipients.

Save the Data or Workbook Without Formulas in Excel Using the Quick Access Toolbar

If you need to regularly remove the formulas from your current workbook (and other workbooks as well), you can make use of the Quick Access Toolbar (QAT) so you can paste the cells as values with just one click.

To set this up:

1. Click the Customize Quick Access Toolbar button.

You’ll see this at the top left of the menu bar (near the workbook’s name).

From the menu that shows up, select More Commands.

2. The Excel Options menu will appear.

Select All Commands from the “Choose commands from”dropdown list.

From the list of items, look for Values [Paste Values].

If you can’t find it, you might be using the older version of Office. If so, try looking instead for Paste Values.

3. Once the “paste as values” command is selected, click the Add >> button.

When you see the command added to the list of QAT, click OK.

You should now see the “paste as values” icon in the QAT (as shown below).

Note that it is currently disabled. It will only be activated once you copy cells inside the workbook.

Using this new command from the QAT method, we can now quickly remove the formulas from the data within the workbook. To do this:

1. Select all the sheets you want to remove the formulas from.

2. With these sheets activated, select all cells within one of the sheets.

You can do so by clicking the triangle on the top left corner (just before cell A1).

3. Once all cells are selected, press CTRL + C to copy them.

Then, click the “paste as values” command that we have added in the QAT.

And that’s it! You have successfully removed all the formulas from all the selected sheets.

Save the Data or Workbook Without Formulas in Excel Using Macros

If you have a whole lot of sheets in your workbook and you want to remove the formulas from all of them, then the best method for you would most likely be by using macros.

1. With your workbook open, press ALT + F11. This will open the VBA Editor.

2. Copy the following code and paste it onto the new module added.

Option Explicit

Sub RemoveFormulasInWorkbook()
    Dim ws As Worksheet, answer As Integer
    
    answer = MsgBox(prompt:="You are about to remove all the formulas in this workbook." & vbNewLine & "Continue?", Buttons:=vbExclamation + vbYesNo)
    If answer <> vbYes Then Exit Sub
    
    For Each ws In ThisWorkbook.Worksheets
        ws.Cells.Copy
        ws.Range("A1").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    Next
    
    MsgBox prompt:="All formulas inside this workbook are removed.", Buttons:=vbInformation
End Sub

3. Once you’ve added the code, it’s time to run it.

Before running the code, please remember to secure a copy of the workbook first. Once the macro runs, all changes cannot be undone.

To run the macro, place the mouse cursor anywhere inside the code and press F5.

4. A prompt will appear to confirm this process.

Click Yes to continue.

5. Another prompt will appear to inform you once the macro is done running.

Click OK. And that’s it!

All formulas will be removed from all the worksheets inside the workbook. Only the values will be retained.

6. When you save the file, you will be seeing the following prompt if it’s in .xlsx format:

If you want to save the macro, click No and save the file in .xlsm or .xlsb format.

If not, just click Yes.

Related Tutorial: How to Save an Excel Table as an Image

Conclusion

Excel formulas are such a great help when setting up our templates and reports.

There are instances, however, when we need to hide these calculations so that they remain confidential, and the intended reader can’t just see how the numbers were processed.

I hope you find the suggested methods above helpful.