addina

Free Excel Add-In: 20+ Macros to Automate Tasks and Make You More Efficient

 
 
 
 

This add-in is completely free and includes over 20 macros that I have worked on myself and that I hope will help you. Any feedback is welcome, as well as any suggestions for other macros you would like to see added.

Disclaimer
These macros have not been tested exhaustively so I don’t offer any guarantees that they will work under every possible scenario. However, if you run into any issues please let me know and I will work to correct them. When using macros you should always save your work before executing them, as there is no undo button if something doesn’t go as expected.

If you understand and accept these risks, please feel free to download the file here

Below is an overview of all the different macros in the file.

Toggling Workbook and Worksheet Calculations

For those that work on large spreadsheets, this can make it easy for you to not only turn off and on calculations for a workbook, but for individual worksheets. It will also allow you to see whether or not they are set to on or off.

One of the things people sometimes don’t realize is if you turn off calculations in Excel at the workbook level, that disables it for all other workbooks. The danger is if you switch to another file you’re working on you may not realize calculations are still off, by seeing the toggle and whether it is set to on or off can help prevent that.

If you only need an individual worksheet to be off, you can do that as well. However, note that if the workbook calculations are set to off, then all the worksheet calculations will be off as well, regardless of whether or not they say on or off. Workbook settings will supersede any worksheet settings.

Very Hidden Tabs

 

Hiding tabs in Excel may seem pointless since even an average user would know that you can right-click and select un-hide. However, not many know that you can set them to be ‘very hidden’ and where right-clicking won’t do anything.

I’ve covered this in a post before here, and in this add-in I’ve made it so that you can easily both hide and unhide very hidden tabs.

 

Removing Excess Spaces

 

This macro will delete any trailing, leading, or extra spaces in a cell and will help to clean up your data.

Converting Formulas to Values

In some instances you may want to get rid of your formulas and replace them with their results (values), this macro will do that for you. Just select the cells and click the button and the formulas will be gone.

Converting Numbers to/from Text and Changing Signs

These buttons will allow you to choose whether you want to convert numbers that are stored as text into numbers, switch numbers back into text, or just flip the signs from positive to negative or vice versa.

Filtering Out Zero Values From Tables

 

If you’ve got a table or pivot table that has a lot of zero values in it that you don’t want to see, this will filter them out. This won’t get rid of errors, just zeros, and for it to work in a table, it assumes that the table will start in column A, otherwise it won’t filter the right column for you. The zero values will be removed from the column where your active cell is, so you have to make sure you’ve got the right cell selected before clicking this button.


















Multiplying and Dividing by a Factor of 1,000

This is pretty straightforward and is mainly here since dividing can be helpful if you’re dealing with financials and want to cut down the number of placeholders. Multiplying will simply undo those changes.

Combining Columns

If you have data across multiple columns and want to combine it, you can do that with this macro. You don’t have to select entire columns, it can just be a selection. The columns don’t even have to be right next to one another.

 

Cycling Through Errors

 

If you want to find all the errors on the worksheet you’re on, this macro will cycle through all of them for you. You can correct an error, and click the Next button to go onto the next error in the sheet.


Removing Merged Cells

When you’re trying to do data analysis, merged cells can be a nightmare, and this will unmerge the cells and put the value into each of the cells as well.

 

Protecting Your Data


This will help convert your sensitive data into a random number preceded by a series of X’s. There’s a post here detailing how that process works.

 

Filtering Pivot Tables

If you’ve got a pivot table and want to select multiple items, it can be a tedious process. This macro will allow you to select what selections you want to filter by and apply them for you. But the first cell in the selection needs to match the field name in the pivot table.

 

Adjusting the Default Pivot Table Format

One of the more annoying things in Excel is that when you create a pivot table, it defaults to a format that isn’t very useful. This is what the macro will help you do:

 

Quickly Formatting Pivot Table Fields

If you’ve ever needed to change how fields are formatted in a pivot table you know that simply selecting the column and changing the format is a temporary fix. You need to actually go into the field settings. This macro will do that for you, and will set the settings to either comma or accounting format.

Quickly Extracting Unique Values

 

There are plenty of ways you can get unique values, but I thought an even easier way would be to select the cells and specify where you want those unique values to be output.

Counting Unique Values

If you just want to quickly count how many unique values are in your selection, this macro will do that for you.

Do a Reverse Lookup

Everyone knows how to do a VLOOKUP, but doing the reverse is another story. Take for example a credit card statement. You could have a lot of detail in the string, but only a certain few characters relate to the actual vendor or detail you want:

Using this lookup function the cells you select will be compared against a list you have specified, and if there are any matches, the corresponding field will be returned:

 

The result:



If you’re doing this with a lot of cells and have a big list, it could be time consuming, and that’s why I added a progress bar to this macro.

Comparing Sheets

This macro essentially looks at two sheets and tells you what is different, and will highlight the differences in them.

 

Updating Links

If you want to update the link for a cell, it’s not an easy process and involves you right-clicking on the cell and putting the link in there. This macro will do that step, and for the link it will put the cell’s value there, so if you put in the url you want in the cell and then run the macro on those cells, the links will be updated.

Adding the Location to the Footer

Clicking this button will add the path to the workbook you’re working on into the footer so when you
print it out it’s easy to see where the file is saved.

Macro to Replace Cell References with Formulas

When creating a complex formula sometimes you use multiple cells to get to a desired result. Then at the end you may want to join all those cells into one large formula. You can do this by copying formulas to replace the cell references but it’s not as easy as it should be.

The macro below does exactly that – it will replace the cell references with the formula in the referenced cell. For example, in cell A1 I have a simple formula of =5*5. 
In cell A2 the formula is =A1+10. 

What I do next is to select cell A1 and run the macro. It will look in the spreadsheet to see everywhere that cell A1 is referenced and replace it with the formula in A1. Below is the result in cell A2 after the macro has been run:

You’ll notice I added parentheses to make sure that the result would not get altered – not applicable in this example since order of operations would ensure the calculation is correct but in a more complex example it might not be the case.
Below is the code for this macro:
Sub copyformula()

Dim whattocopy As String
Dim whattoreplace As String

whattoreplace = Replace(ActiveCell.Address, “$”, “”)
whattocopy = “(” & Replace(ActiveCell.Formula, “=”, “”) & “)”

   Cells.Replace What:=whattoreplace, Replacement:=whattocopy, LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False
    
   Cells.Replace What:=ActiveCell.Address, Replacement:=whattocopy, LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False

End Sub

Cleaning up data – getting rid of blank or 0 values in Excel

This function is designed to help clean up a spreadsheet if you want to either delete or hide cells that have 0 or empty values. How this works:

I select a range of data that I want to get 0s and blank cells out of (it doesn’t have to be a column) and run the macro.

The macro will hide everything that is a blank cell or a zero value in my range:

What if I only wanted 0s, or blank cells only? What if I wanted to delete the entire row that has that cell?

In the code below, you can change these options based on what you want the function to do. I have created two variables called option1 and option2. Their values are bolded in red.

If I change option1 from 1 to 2, then only 0 value cells will be affected, a value of 3 will mean only blank cells are.

For option 2 I can choose to hide the entire row (1), hide the entire column (2), delete the row (3), delete the column (4), clear the cells (5), delete the specific cells and shift cells up (6), or delete the cells and shift left (7).

Changing the values in red allows you to make any of the above changes.

The code for the macro is below. I suggest assigning a shortcut for this macro to run it quickly.
———————————————————————————————————————–

Sub cleanupdata()

Dim option1, option2 As Integer

option1 = 1
‘Option1
‘1 = blanks and 0s
‘2 = 0s only
‘3 = blanks only

option2 = 1
‘Option2
‘1 = hide row
‘2 = hide column
‘3 = delete row
‘4 = delete column
‘5 = clear cells
‘6 = delete cell, shift up
‘7 = delete cell, shift left

Dim activerange As Range
Dim cl As Range
Dim selectedcells As Collection
Dim numberofitems As Integer
Set selectedcells = New Collection

Set activerange = Selection

For Each cl In activerange

Select Case option1

    Case Is = 1 ‘Blanks and 0s
            If Len(cl) = 0 Or (Len(cl) > 0 And cl = 0) Then
                selectedcells.Add cl
               
            End If
           
    Case Is = 2 ‘0s only
            If Len(cl) > 0 And cl = 0 Then
                selectedcells.Add cl
            End If
                     
    Case Is = 3 ‘Blanks only
            If Len(cl) = 0 Then
                selectedcells.Add cl
            End If
           
End Select
Next cl

numberofitems = selectedcells.Count

On Error Resume Next
Select Case option2
    Case Is = 1
        For counter = 1 To numberofitems
        selectedcells.Item(counter).EntireRow.Hidden = True
        Next counter
    Case Is = 2
        For counter = 1 To numberofitems
        selectedcells.Item(counter).EntireColumn.Hidden = True
        Next counter
    Case Is = 3
        For counter = 1 To numberofitems
        selectedcells.Item(counter).EntireRow.Delete
        Next counter
    Case Is = 4
        For counter = 1 To numberofitems
        selectedcells.Item(counter).EntireColumn.Delete
        Next counter
    Case Is = 5
        For counter = 1 To numberofitems
        selectedcells.Item(counter).Delete
        Next counter
    Case Is = 6
        For counter = 1 To numberofitems
        selectedcells.Item(counter).Select
        Selection.Delete shift:=xlUp
        Next counter
    Case Is = 7
        For counter = 1 To numberofitems
        selectedcells.Item(counter).Select
        Selection.Delete shift:=xlToLeft
        Next counter
End Select

End Sub

———————————————————————————————————————–

Brief Intro to Macros and VBA

If you do a lot of repetitive tasks (and even if you don’t), macros can help save lots of time. In some cases you can be talking hours worth of work in one macro.

Recording a Macro

Now, if you’re not familiar with macros or VBA at all, a good way to learn is through the macro recorder tool (this is under the View tab in the ribbon). When you record your macro, you can see what was created on the VBA side by pressing ALT+F11 – you can also edit your macro here. You can do much more in VBA by coding straight from that screen, as the macro recorder is limited (e.g. it can’t create variables or other complex codes). But the macro recorder helps you learn how to reference different items.

When you create a macro – whether directly from VBA or the macro recorder, you can assign a shortcut (or a button to it) to it, saving you time in executing it.

To assign a shortcut: select the view macros option under the view tab and then select options for the macro you want. There it will allow you to assign a shortcut (see below)

For a button, you will need the developer tab enabled. If you don’t have it, go under Excel options and under ribbon options you will see an option to enable it.

Once enabled, under the developer tab you will see an insert controls button where you can insert a button (see below)

Once you do that you will create the size of the button, and when you are done it will ask you to assign a macro to it. And now when you click on that button your macro will run.

Inserting VBA Code

If you run across VBA code that you want to copy into your spreadsheet, you want to make sure it is put in the proper section.

If it is a custom function, you want to make sure that is put in a module to work properly. If you do not see a module section in VBA, you will need to add one from the Insert menu.

If it is just a macro (denoted by ‘Sub’ and then the name of the procedure) it can be put in a module, the worksheets, or the workbook section. If you want the macro to work in all worksheets, then I would recommend putting it in the workbook rather than the individual sheets.