How to Perform Workflow Automation in Excel (4 Easy Examples)

Get FREE Advanced Excel Exercises with Solutions!

Doing repetitive work is exhausting sometimes in Excel. We are going to show Excel workflow automation to make your work better. Repetitive work can be done by creating Macros with VBA code in Excel. There are some examples of repetitive work are given below:

  • Doing  the same type of calculation on all sheets
  • Creating and Renaming sheets
  • Refreshing Pivot Table when main table data has changed

Overview for automating excel

Download Practice Workbook

You can download the practice workbook from the following download button.

4 Effective Examples to Perform Workflow Automation in Excel

In this article, we are going to show some Excel workflow automation processes so that you can apply them to ease your work. If you can understand this article you can easily have a better view of this topic and we are providing better suggestions for automating boring work to make your life better.

1. Automating Profit Calculation Using VBA

In this example we are going to automate Profit Calculation using VBA code in every single sheet you will add in your work space easily. We are using Number of Sales of different -product of mobile, Price per mobile and Cost per Mobile to calculate total profit and automate the calculation through all sheets.


  • Here, we are going to use the dataset of Sales of Mobile of different companies.

Product sales and cost dataset to automate profit Calculation

  • Later, we will go to Developer >> Visual Basic.

Running Visual Basic program

  • Subsequently, we will go to Insert >> Module . Then we insert VBA codes in the new Module.

uploading VBA code

  • Thereafter, a VBA Module for the  sheet will appear. We are going to use the VBA code given below to find out Total Sales, Total Cost and Profit. You can calculate these parameters in all sheets if you use the similar data table in those sheets.
Sub RunThroughAllSheet()
    Dim jm_ws As Worksheet
    Application.ScreenUpdating = False
    For Each jm_ws In Worksheets
        Call ProfitCalculation
    Application.ScreenUpdating = True
End Sub
Sub ProfitCalculation()
    Range("G5") = "=SUMPRODUCT(C:C,D:D)"
    Range("H5") = "=SUMPRODUCT(C:C,E:E)"
    Range("I5") = "=G5-H5"
End Sub

code snippet for Profit Calculation

Code Explanation:

  • First, we have created a RunThroughAllSheet subroutine to go through all sheets to find out the data chart and if it is found it will call ProfitCalculation subroutine.
  • In the ProfitCalculation subroutine, we set some formulas in the cells G5, H5 and I5. The SUMPRODUCT formula in G5 and H5 will return the Total Sales and Total Cost respectively. The formula in I5 will show us the overall Profit. We used the VBA Range property to set the formulas in the corresponding cells.
  • Now we will create a button to automate the profit calculation. For that purpose, we select Developer >> Insert >> Button (Form Controls)

Adding Option for the Macro

  • Then, the Assign Macro dialog box will appear.
  • After that, we will choose the Macro named RunThroughAllSheet.
  • Later, click OK.

selecting the macro

  • Then, we will get an option embedding that Macro and we are going to rename it as Profit Calculation.
  • Click on the button and you will see the Total Sales, Total Cost and Profit.

creating and Renaming the option

  • Consequently, if you change any of the data, Total Sales, Total Cost , Profit will change.

Automating profit calculation

Now every time you click on the option it will calculate profit for you in every available sheet.

2. Automating Refresh Pivot Table and Charts

In this section, we are going to Refresh a PivotChart automatically. You do not have to Right Click and refresh anymore to update the PivotChart if you change any data. Let’s have a look at the description below to get a better idea of this type of workflow automation.

  • First, we are going to create Data Table like below.

Giving the dataset for automating Refresh

  • Then, we will go to Insert >> PivotTable and Select from Table Range.

creating Pivot Table

  • After that, we are going to use the whole table and select OK.

creating Pivot Table

  • Then we will get a blank PivotTable.

creating Pivot Table

  • After that, you can see the PivotTable Fields at the right side of the Excel sheet. Check Product, Number of Sales and Cost per Mobile.

creating Pivot Table by selecting data

  • Now, you will get the Pivot Table .

creating Pivot Table

  • Then, we will select PivotTable Analyze >> PivotChart.

Creating Pivot chart

  • After that, you will get the graph automatically.

Getting Pivot Chart

  • Now, we are going to rename the newsheet.

Renaming Sheet

  • Then , we will go to View Code.

Renaming sheet Containing Pivot Table

  • After that we are going to upload the code below.
Private Sub Worksheet_Change(ByVal jm_Target As Range)
End Sub


  • This will refresh the PivotTable instantly if any changes are made in the mother chart
  • Let’s make a change in the data of the mother table.


  • Now, Data from Pivot Datatable will change without Refresh.

Refreshing Data automatically

  • After that, the graph will automatically change without Refresh.

Finally automatic refresh shown in chart

After that you will not need to refresh data. It will always refresh itself.

Thus you can automate the workflow of a PivotChart.

3. Creating and Renaming Worksheets Automatically

In this article we are going to create a Custom name when you create new sheet, It will easily rename every sheet as soon as you give the custom Shortcut Key.


  • Later, we will go to Developer >> Visual Basic.

Inserting VBA code

  • Subsequently, we will go to Insert >> Module . Then we insert VBA codes in the new Module.

Inserting Vba code

  • Then,  you upload the following Code . This code will create new sheet and rename the sheet as Sales1 and Sales2 not Sheet1 and sheet2.
Sub CreateAndRenameSheet()
    If ThisWorkbook.Sheets.Count < 255 Then
        Dim newSheet As Worksheet
        Set newSheet = ThisWorkbook.Sheets.Add
        newSheet.Name = "Sales" & ThisWorkbook.Sheets.Count
        MsgBox "The number of sheets in the workbook has reached the limit."
    End If
End Sub

vba code for crate and rename worksheet simultaneously

Code Explanation:

  • Here, we have created variable newSheet  for Worksheet
  • Then ,we added newSheet by  ThisWorkbook.Sheets.Add. This method will dynamically create a new worksheet in your workbook.
  • Subsequently, newSheet.Name will dynamically rename your sheet as you want.
  • You can change name from  newSheet.Name = “Sales” & ThisWorkbook.Sheets.Count.
  • Then we are going to create a Shortcut Key as ctrl + p. Every time you use ctrl+p  it will automatically create a new sheet naming Sales1, Sales2, Sales3 and so on.

selecting Shortcut

It will automatically rename files according to what you want.

4. Removing Hyperlink Using Excel Automate Option

In this article we are going to create an arbitrary dataset and embed hyperlinks and after that we are going to remove Hyperlinks using Automate >> Remove Hyperlinks from Sheet.


  • First, we are going to create an arbitrary data table like below.

Creating Data Table for Removing Hyperlink

  • Then we will embed Hyperlinks in data.

inserting hyperlink

  • After that, we are going to press Insert Link.. .and add it to your file name.

inserting hyperlink

  • Then we finally get the Hyperlinked data.

inserting hyperlink

  • Now we are going to Automate >> Remove Hyperlinks from Sheets then get rid of the Hyperlinks.

Removing hyperlink

  • Then, we will Select Run from Code Editor.

Removing hyperlink

  •  Then finally we will get the hyperlink removed.

Hyperlink Removed


In this article, we have given you some ideas and examples of Excel workflow automation. You can automate anything you want using VBA Macro. For more custom tutorials like this, please visit

Joyanta Mitra
Joyanta Mitra

I am Joyanta Mitra. I graduated from BUET EEE in 2021. My college is Notre Dame College. My hobby is to play high graphics computer games. And I am going to pursue my career in your company Softeko. I am working and doing research on Microsoft Excel and here I will be posting articles related to this.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF