| The capabilities described on this page are included in Appian's standard capability tier. Usage limits may apply. |
Microsoft Excel is a widely used spreadsheet program available in most business settings. It's likely that you use Excel to complete a variety of tasks, such as data analysis, financial modeling, and project management, among others. With just a few clicks, Appian RPA can automate tedious and time-consuming tasks in Excel. This means the Excel: License Required module can read, write, and manipulate data within an Excel workbook for you.
Because the strategy you use to interact with Excel depends on your purposes and the data involved, we've assembled guidance and tips to improve your design experience. Learn more about the many ways to use Excel with Appian, based on your goals.
Note: The Excel: License Required module is only available on Windows machines.
The Excel License Required module is compatible with the following Excel file types.
Excel actions are listed in the Excel: License Required area of the palette. The actions described in this section require a licensed version of Excel on the host machine.
Tip: You can't mix low-code actions from the Excel: Licensed Not Required and Excel: License Required modules together. The technologies used in each module can't interact with the same workbook at the same time.
| Does your host machine have a licensed version of Excel? | Then use the… |
|---|---|
| No | Excel: License Not Required module. |
| Yes | Excel: License Required module. The Excel: License Required module can complete more advanced actions and is better at managing large (in terms of file size or number of rows) Excel files. Note: The Excel: License Required module is only available on Windows machines. |
This action allows you to reset tables and ranges to their unfiltered state.
In the CONFIGURATION area of the action pane, configure the following parameters:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action allows you to reset pivot tables to their unfiltered state.
In the CONFIGURATION area of the action pane, configure the following parameter:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action closes the Excel workbooks that you choose and can optionally save changes.
In the CONFIGURATION area of the action pane, configure the following parameter:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action allows you to copy and paste data from a specific cell, column, or range.
In the CONFIGURATION area of the action pane, configure the following parameters:
Source Type: Choose what you want to copy: Cell, Column, or Range. Depending on the source you choose, additional parameters display.
| Source Type | Additional Parameter(s) |
|---|---|
| Cell | Cell: This field identifies the cell you want to copy. This field lets you enter a text string, choose a variable, or use the expression editor. |
| Column | Column: This field identifies the column you want to copy. This field lets you enter a text string, choose a variable, or use the expression editor. Copy only visible values: When this checkbox is selected and the column's data is filtered, only values from the visible rows (in other words, rows that aren't hidden) are copied. |
| Range | Starting Cell: This is the first column letter and row number for the cell range you want to copy. Ending Cell: This is the last column letter and row number for the cell range you want to copy. You can enter a text string, choose a variable, or use the expression editor for these parameters. Copy only visible values: When you select this checkbox and the data in the range is filtered, only values from the visible rows and columns are copied. |
| Row | Row: This field identifies the row you want to copy. This field lets you enter a text string, choose a variable, or use the expression editor. Copy only visible values: When this checkbox is selected and the row's data is filtered, only values from the visible columns (in other words, columns that aren't) are copied. |
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action adds a new worksheet in the active workbook.
In the CONFIGURATION area of the action pane, configure the following parameters:
Sheet#, using your language settings and regional formatting preferences.Specify worksheet location: Mark this checkbox to indicate where you want to insert the new worksheet.
| If you… | Excel will insert the new worksheet… |
|---|---|
| mark the checkbox | after the active worksheet that you specify in the Worksheet location. Depending on the Worksheet location you choose, you will enter the Worksheet index or Worksheet name after which you want to create the new worksheet. |
| do not mark the checkbox | before the active worksheet. |
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action deletes a worksheet within the Excel workbook.
In the CONFIGURATION area of the action pane, configure the following parameters:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action deletes a row or column from the active worksheet. Any data that comes after the deleted rows or columns will move up or left to fill the empty space. So, you don't have to worry about the data getting lost. It automatically adjusts itself to fit the new layout of the worksheet.
In the CONFIGURATION area of the action pane, configure the following parameter:
Component Type: Select the component type you want to delete: Column or Row. Depending on the component you choose, additional parameters display.
| Component | Additional Parameter(s) |
|---|---|
| Column | Starting Column: This field identifies the column you want to delete. This field lets you enter a text string, choose a variable, or use the expression editor. If you want to delete a range of columns, also mark the Specify ending column checkbox and fill in the Ending Column field. Ending Column: This field identifies the last column you want to delete and lets you enter a text string, choose a variable, or use the expression editor. If not specified, only the Starting Column is deleted. |
| Row | Starting Row: This field identifies the row you want to delete. This field lets you enter a text string, choose a variable, or use the expression editor. If you want to delete a range of rows, also mark the Specify ending row checkbox and fill in the Ending Row field. Ending Row: This field identifies the last row you want to delete and lets you enter a text string, choose a variable, or use the expression editor. If not specified, only the Starting Row is deleted. |
In the AFTER COMPLETION area of the action pane, configure the following parameters:
The Filter data action filters cell values within a designated range or table. If a table lacks a filter, it automatically adds one to the table header. This action can streamline tasks such as transferring data. For instance, if your team maintains a monthly worksheet and wants to transfer data to a centralized database at the end of each month or move specific entries to another sheet for quarterly analysis, the Filter data action facilitates this. By refining and organizing the data first, the transfer to the database or between sheets becomes more efficient.
When you apply multiple filter actions to a range, they are cumulative, meaning each new filter is combined with the previous filters using an AND operation.
Tip: If you need to clear filters before applying new filters, use the Clear data filters action.
In the CONFIGURATION area of the action pane, configure the following parameters:
Range or Table: Select whether you want to filter a specific Range or Table. Depending on the option you choose, additional parameters display.
| Option | Additional Parameter(s) |
|---|---|
| Range | Starting Cell: This field identifies the row and column where your range starts. This field lets you enter a text string, choose a variable, or use the expression editor. Ending Cell: This field identifies the last row and column of the range you want to sort. This field lets you enter a text string, choose a variable, or use the expression editor. |
| Table | Table Name: The name of a specific table within the worksheet. This field lets you enter a text string only. |
C. Ensure the column contains only text values.Operator: This field identifies how the data will be filtered. The options in this dropdown list are determined by the Filter Type you select.
In the AFTER COMPLETION area of the action pane, configure the following parameters:
The Filter pivot table action allows you to create filters on a pivot table. It lets you focus on specific sections of your dataset by adding filters. This action ensures that your pivot table displays only the necessary data, making the information clear and concise.
Tip: If you need to clear filters before applying new filters, use the Clear data filters action.
In the CONFIGURATION area of the action pane, configure the following parameters:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action retrieves the position of the first cell or the last cell within a range or the total number of rows or columns up to and including the last cell. The result is stored in a robotic task variable that you must create. You could use this action to determine the limits for a loop or use it with the Get Value action.
In the CONFIGURATION area of the action pane, configure the following parameters:
In the OUTPUTS area of the action pane, configure the following parameters to store this result:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
Use the Find value action to search a worksheet by cell value, with exact case or full content matching options.
In the CONFIGURATION area of the action pane, configure the following parameters:
The output is a list of the cells where the item is found (e.g., "C10", "D11") or an empty list if it is not found.
In the OUTPUTS area of the action pane, configure the following parameters to store this result:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action customizes the format of a workbook's cells, including alignment and color. You can also use this action to merge and unmerge cells. Merging cells is often used to create headings or titles that span multiple columns or rows. It can also be used to format cells for a more organized and visually appealing layout, such as merging cells to create a larger blank space between two sets of data.
In the CONFIGURATION area of the action pane, configure the following parameters:
Component Type: Select the component type that you want to format. Depending on the component you select, additional parameters display.
| Component Type | Additional Parameter(s) |
|---|---|
| Column | Starting Column: This is the column letter; e.g., A, B, C, for the column you want to format. Specify Ending Column: Mark this checkbox if you want to format more than one column. When this checkbox is marked, the Ending Column field displays and you can enter the letter for the last consecutive column you want to format. You can enter a text string, choose a variable, or use the expression editor for these parameters. |
| Cell | Cell: This is the column letter and row number for the cell you want to format. This field lets you enter a text string, choose a variable, or use the expression editor. |
| Row | Starting Row: This is the number for the row you want to format. Specify Ending Row: Mark this checkbox if you want to format more than one row. When this checkbox is marked, the Ending Row field displays and you can enter the number for the last consecutive row you want to format. You can enter a text string, choose a variable, or use the expression editor for these parameters. |
| Range | Starting Cell: This is the first column letter and row number for the cell range you want to format. Ending Cell: This is the last column letter and row number for the cell range you want to format. You can enter a text string, choose a variable, or use the expression editor for these parameters. |
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action retrieves data from a workbook and stores it in a robotic task variable that you must create.
In the CONFIGURATION area of the action pane, configure the following parameters:
Component Type: Select the component from which you want to retrieve data. Each component type returns a different type of data. Depending on the component you select, additional parameters display.
| Component | Returns | Additional Parameter(s) |
|---|---|---|
| Cell | primitive system data type | Cell: This is the column letter and row number for the cell from which you want to return data. This field lets you enter a text string, choose a variable, or use the expression editor. |
| Column | an array with a data type determined by the file's content | Column: This is the column letter; e.g., A, B, C, for which you want to return data. You can enter text or select a variable. This field lets you enter a text string, choose a variable, or use the expression editor. |
| Range | map array | Starting Cell and Ending Cell: These fields represent the start and end of the range for which you want to return data. These fields let you enter a text string, choose a variable, or use the expression editor. |
| Row | map array | Row: This is the row number; e.g., 1, 2, 3, for which you want to return data. You can enter text or select a variable. This field lets you enter a text string, choose a variable, or use the expression editor. |
Specify header row: When this checkbox is marked, the robotic task uses the column headers as the key in the returned map. If the starting cell, ending cell, or any fields in the specified header row are empty, the column names (e.g., A, B, C) are the key in the returned map.
In the OUTPUTS area of the action pane, configure the following parameters to store this result:
When storing custom-formatted numbers, particularly those representing currency, save them as decimal numbers rather than text to maintain accuracy.
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action returns the worksheet names, including hidden worksheets, in the same order as they appear in the file. The returned value is an array of strings ordered by index; e.g., tab 1, tab 2, and is stored in a robotic task variable that you create.
In the OUTPUTS area of the action pane, configure the following parameters to store this result:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action inserts a row or column into the active worksheet.
In the CONFIGURATION area of the action pane, configure the following parameters:
Component Type: Select the component type you want to insert: Row or Column. Depending on the Component Type you select, additional parameters display.
| Component | Additional Parameter(s) |
|---|---|
| Row | Selected Row: Specifies where the row(s) should be inserted. This field lets you enter a text string, choose a variable, or use the expression editor. Number of Rows: Specifies the number of rows to be inserted. This field lets you enter a text string, choose a variable, or use the expression editor. Insert Options: Specifies whether the row(s) should be inserted above or below the Selected Row. Options are: Insert rows above selected row and Insert rows below selected row. |
| Column | Selected Column: Specifies where the column(s) should be inserted. This field lets you enter a text string, choose a variable, or use the expression editor. Number of Columns: Specifies the number of columns to be inserted. This field lets you enter a text string, choose a variable, or use the expression editor. Insert Options: Specifies whether the column(s) should be inserted to the right or to the left of the Selected Column. Options are: Insert columns right of selected column and Insert columns left of selected column. |
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action opens an existing Excel workbook or creates a new workbook.
In the CONFIGURATION area of the action pane, configure the following parameters:
Workbook Action: Select from one of the following options. Depending on the workbook action you select, additional parameters display.
| Workbook Action | Additional Parameters |
|---|---|
| Open existing workbook |
File Path: This is the folder path and name of the Excel file, including its extension, to be opened. For example, C:\Users\YourUsername\Documents\ExcelFiles\SampleWorkbook.xlsx. This field lets you enter a text string, choose a variable, or use the expression editor.Sign in to protected workbook: If the Excel workbook is password-protected, this option allows you to enter the password. This field lets you enter a text string, choose a variable, or use the expression editor. |
| Create new workbook | Folder: This is the path to the folder where you want to save a new workbook. This field lets you enter a text string, choose a variable, or use the expression editor. File Name: This is the name of the new workbook you are creating. This field lets you enter a text string, choose a variable, or use the expression editor. File Type: This field identifies the Excel file type for the new workbook. Options include .xlsx, .xls, .xlsm, .xslb, or .csv. This field only displays if you are creating a new workbook. |
In the AFTER COMPLETION area of the action pane, configure the following parameters:
Use the Refresh data action to quickly refresh pivot tables and data connections by using a pivot table's name or Power Queries. This action also allows you to refresh all data connections at once. Depending on your specific needs and the data sources you're working with, you might want to refresh all data, only Power Queries, or exclusively PivotTables. Evaluate which sections of your data need updating and select one of the refresh type options.
In the CONFIGURATION area of the action pane, configure the following parameters:
Refresh Type: Depending on your specific needs and the data sources you're working with, you might want to refresh All data, only Power Queries, or exclusively Pivot tables. Depending on the type you choose, additional parameters display.
| Refresh Type | Additional Parameter(s) |
|---|---|
| Power Queries | All Power Queries in workbook Specific Power Query: This field identifies the specific Power Query you want to use to refresh the data. This field lets you enter a text string, choose a variable, or use the expression editor. |
| Pivot Tables | All pivot tables in workbook Specific pivot table: This field identifies the specific pivot table you want to use to refresh the data. This option refreshes a specific pivot table on the currently active worksheet. If there are other pivot tables on the same worksheet, they are also refreshed. Pivot tables with the same name might exist on different worksheets within the workbook, but they are not refreshed. This field lets you enter a text string, choose a variable, or use the expression editor. |
In the AFTER COMPLETION area of the action pane, configure the following parameters:
We recommend disabling the Enable background refresh option on all power queries in your Excel file to ensure your robotic tasks wait until the data refresh is complete before proceeding. This will help avoid errors with outdated information.
The Run macro actions allows you to call macros in a workbook. This action is a powerful option that you can use to automate even the most complex Excel processes, work with formatted templates, and manipulate files with minimal manual effort. Macros are a great way for you to extend Excel's capabilities and expand what you can do with RPA. For example, if a function isn't supported, you can create a macro in Excel to perform the task instead. By executing predefined macros, Appian RPA enables you to standardize your processes and manage more data in less time with higher accuracy.
This action is compatible with XSLM and XSLB files.
Tip: Before using the Run macros action, ensure that your Excel macro is predefined and working as intended. This includes testing your macro within Excel to confirm that it performs the desired tasks without errors.
In the CONFIGURATION area of the action pane, configure the following parameters:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
After a workbook is opened or created, this action saves the workbook with the name and to the location that you specify in the parameters. If the file name in the configuration is different than the opened file, the robotic task saves the workbook as a new file.
Note: This action works with .xlsx and .xls files only.
Tip: You can't use this action to rename an existing workbook. However, if you want to rename an existing workbook, you can use the Rename a file or folder File System action to do that.
In the CONFIGURATION area of the action pane, configure the following parameters:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action selects a specific worksheet within an Excel file so that the robotic task can interact with it.
In the CONFIGURATION area of the action pane, configure the following parameters:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
The Sort data action lets you arrange cell values in a range or table based on one column, making data organization swift and straightforward.
In the CONFIGURATION area of the action pane, configure the following parameters:
Range or Table: Select whether you want to sort a specific Range or Table. Depending on the option you choose, additional parameters display.
| Option | Additional Parameter(s) |
|---|---|
| Range | Starting Cell: This field identifies the row and column where your range starts. This field lets you enter a text string, choose a variable, or use the expression editor. Ending Cell: This field identifies the last row and column of the range you want to sort. This field lets you enter a text string, choose a variable, or use the expression editor. |
| Table | Table Name: The name of a specific table within the worksheet. This field lets you enter a text string only. |
Sort By Column: Use this field to specify the column you want to sort by. You can use the column name or its reference like C. Ensure the column contains only text values.
Order: This field identifies the sort order for the column and includes the following options:
"Jan", "Feb", etc. This field lets you enter a text string, choose a variable, or use the expression editor.In the AFTER COMPLETION area of the action pane, configure the following parameters:
The action allows you to switch between two open workbooks without needing to close either one, allowing actions to be performed across both workbooks. Remember, you can only switch to a workbook that was previously opened using the Open or create workbook action.
In the CONFIGURATION area of the action pane, configure the following parameter:
File Path: This is the complete path to the workbook you want to switch to. For example, C:\Users\JohnDoe\Documents\FinancialReports\Q1_2023.xlsx. This field lets you enter a text string, choose a variable, or use the expression editor.
Tip: Save workbook paths in variables to easily switch workbooks without retyping the full path.
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action writes data or a formula into a single cell or a range.
In the CONFIGURATION area of the action pane, configure the following parameters:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
{"id": 1, "name": "banana"}, {"id": 2, "name": "apple", "color": "red"}, {"id": 3, "shape": "round", "botanicalName": "hesperidium"}, RPA may not write the data in the column order you intended. To have more control over the column order, use the cast() function to convert the CDTs to a map before writing to the worksheet.cast() function with a!listType(type!Map) and a!queryRecordType as its arguments, and wrap it around the queryRecordTypes expression; e.g., cast(a!listType(type!Map), a!queryRecordType()).a!queryRecordType to select the fields you want to return.fn!remove() function as IDs are returned automatically with record queries.a!flatten() and a!map() functions to ensure the output is in a flat structure suitable for writing into an Excel document.1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
fn!with(
local!dataWithoutIds: fn!remove(
a!queryRecordType(
recordType: recordType!Department,
fields: {recordType!Department.fields.name,
recordType!Department.relationships.employee.fields.status},
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 100
)
).data,
{recordType!Department.fields.id, recordType!Department.fields.employeeId}
),
a!flatten(
a!forEach(
items: local!dataWithoutIds,
expression: a!map(
name: fv!item [recordType!Department.fields.name],
status: fv!item[recordType!Department.fields.status]
)
)
)
