Mastering Excel Advanced Package - Episode 1: Build a Profit Calculation Bot

Mastering Excel Advanced Package – Episode 1: Build a Profit Calculation Bot

In this series, we will guide you to use different actions available in Excel advanced package.

Before we get into the details about the bot, let’s understand the basics of the Excel advanced package.

The Excel advanced package contains actions that enable you to automate many of the repetitive tasks when working with Microsoft Excel spreadsheets.

You can automate tasks related to the workbook, worksheet, rows, columns, and cell operations. You can use these actions when Microsoft Excel is installed on the device that you want to use to automate the Microsoft Excel-related tasks.

Actions in the Excel advanced package support Microsoft Office 2016 through Microsoft Office 2019 and .xls, .xlsx, .xlsb, and .xlsm formats. You can also use some of the actions with the .csv format.

To use the actions in Excel advanced package, use the following sequence

1. Open the Microsoft Excel spreadsheet that you want to use to automate the Microsoft Excel-related tasks. Use the Open action to open the spreadsheet.
2. Use the different actions available in the Excel advanced package to automate the Microsoft Excel-related tasks.
3. After you have automated all the Microsoft Excel-related tasks, close the spreadsheet using the Close action.

Coming back to this episode, we will discuss about how to use the following 5 actions to build a Profit Calculation Bot. As part of the exercise, we will add 2 columns ‘Margins in Percentage’ and ‘Profit’, add values in both these columns for each transaction, and then hide the column ‘Margins in Percentage’ and protect the spreadsheet using a password.

Here are the 5 actions used to build this bot along with Open and Close actions which are mandatory to work with the Excel Advanced Package.

1. Insert columns – Use this action to insert one or more columns into the worksheet.
2. Set cell – Use this action to set a value in the Active cell or Specific cell in a Microsoft Excel spreadsheet. You can even enter a cell range in the Specific cell field to set a particular value in all the cells of the range.
3. Set cell formula – Use this action to set formula in the Active cell or Specific cell in a Microsoft Excel spreadsheet
4. Hide columns – Use this action to hide columns in the current worksheet. You can hide one or more columns at a time.
5. Protect workbook – Use this action to Protect the workbook and its structure using a password. Protecting a workbook prevents other users from opening the workbook without the password, and protecting the structure of a workbook prevents other users from adding, moving, deleting, hiding, and renaming worksheets within that workbook.

Conclusion:: Excel Advanced package is a very powerful package and consists of more than 50 actions and can assist you to automate a lot of your repetitive tasks using Excel. This is one of the most used A2019 package and a must-learn for anyone building bots with Excel.

Here is another tutorial on Excel that showcases how to “Work With Large Excel Datasets in a Bot”

You can download the bot code and the Excel spreadsheet used in this bot here.

Packages Used

Excel Advanced package
Loop
Number