If the business owner is keeping a complete track of the sales, then they will also get an idea about how long it takes for them to reach break even or additional profit levels. Small business establishments and organizations use MS Excel spreadsheet for storing contact information of their clients and customers. What is MS Excel used for in the workplace? Some of the most common business uses are business analysis, managing human resources, performance reporting, and operations management. We know this for a fact after analysing job data (using MS Excel by the way). Keep reading to find out the top 10 business uses of Excel.
- Yes, Excel is a spreadsheet program, but it’s also serves as a multifunctional tool. You can use it for a number of business-related tasks like tracking and planning or simple analysis and reporting. And the best part is that, in some cases, you don’t even have to start from scratch. MS Excel has a number of templates to get you started.
- Feb 03, 2011 This is a guest article by Matt, who works as a Business Analyst with allrecipes.com. He shares with us how he is using Excel to become a rockstar business analyst. In his own words, 'At Allrecipes.com we use excel for a variety of purposes. Analyzing site trends, forecasting traffic, charts, dashboards, and slide shows; you name it, we use excel for it. That's why Chandoo's tips have been so.
Excel Online (Business) connector lets you work with Excel files in document libraries supported by Microsoft Graph (OneDrive for Business, SharePoint Sites, and Office 365 Groups).
This connector is available in the following products and regions:
Service | Class | Regions |
---|---|---|
Logic Apps | Standard | All Logic Apps regions except the following: - Azure China regions |
Power Automate | Standard | All Power Automate regions except the following: - US Government (GCC High) - China Cloud operated by 21Vianet |
Power Apps | - | Not available |
Contact | |
---|---|
Name | Microsoft |
URL | Microsoft LogicApps Support Microsoft Power Automate Support |
Connector Metadata | |
---|---|
Publisher | Microsoft |
Website | https://products.office.com/excel |
Known Issues and Limitations
- The maximum size of an Excel file that is supported by the Excel Online (Business) connector is 25 MB.
- The data may not be up to date (there may be some delay) when filtering or sorting is used in the
List rows present in a table
action. - The
List rows present in a table
action supports basic filtering and sorting:- Supports the following filter functions: eq, ne, contains, startswith, endswith.
- Only 1 filter function can be applied on a column.
- Only 1 column can be used for sorting.
- An Excel file may be locked for an update or delete up to 6 minutes since the last use of the connector.
- A single Excel file should be used by a single connection to prevent writing data from multiple connections and possible data inconsistency.
- Simultaneous file modifications made by other connectors or manual edits are not supported.
- The connector supports files only in Microsoft Excel Open XML Spreadsheet format (*.xlsx).
- The
File
property expected by the connector's actions should be filled using one of the following options:- Pick a file from the file picker.
- Use an output from the OneDrive for Business connector's triggers/actions (file's
Id
orFile identifier
property depending on which one is present for the particular OneDrive for Business's action or trigger). - Use an output from the SharePoint connector's triggers/actions (file's
Id
orIdentifier
property depending on which one is present for the particular Sharepoint's action or trigger).
- The connector retrieves rows for 500 columns maximum in the
List rows present in a table
action. Rows for first 500 columns are returned by default. You can provide comma-separated list of specific columns to retrieve inSelect Query
parameter. - The connector doesn't support using OData parameters for tables that contain hidden columns.
- The connector timeout caused by re-calculations can occur, either there are complicated formulas or there are too many rows in the worksheet. In both cases, data can be inserted multiple times because of retry policy.
- The connector will return the Bad Gateway error if the spreadsheet is in a read-only mode. In order to disable read-only mode, please learn more here.
Filter Query
/Order By
/Select Query
operation parameters support only alphanumeric column names.- Pivot tables are not supported due to Graph API limitations.
- The connector always returns all document libraries available under the Document Library drop-down control. For most users, only one document library will be returned, but if there are multiple available, all of them will be listed. For more information about document library (e.g. Drive resources) and expected behavior for users, Groups, and Sites, please view this page.
- If a user makes too many requests within a short period of time, it is possible to get a 429 response. The throttling limit is unique to each request and it varies based on the request count, memory used, and other important factors.
- In operations such as
Delete a row
, thekey column
field iscase-sensitive
. - In case of multiple matches in operations such as
Update a row
,Delete a row
operations, only the first row will be updated/deleted.
Column names handling
Please note that column names in the action's response results may be transformed in order to be compatible with OData format:
Character | Encoded value |
---|---|
. | _x002e_ |
@ | _x0040_ |
: | _x003a_ |
# | _x0023_ |
E.g. Column [one]#1
-> Column [one]_x0023_1
General Limits
Name | Value |
---|---|
Maximum number of identity column variants that can be used to Get/Insert/Update/Delete a row from a single excel table. Current value is set to 2, which means that up to two column name variants should be in use across workflows for one particular table. | 2 |
Concepts and examples
Key column
: column in a table that will be use to search a value (key value
)Key value
: value in thekey column
that will be used to identify a specific row.
Take the following table as an example. To perform a row operation in the second row of the table, the key column
should be Column 1
and key value
should be 200
.
Column 1 | Column 2 | Column 3 |
---|---|---|
100 | A-2 | A-3 |
200 | B-2 | B-3 |
300 | C-2 | C-3 |
Throttling Limits
Name | Calls | Renewal Period |
---|---|---|
API calls per connection | 3 | 10 seconds |
Actions
Add a key column to a table | Add a key column to an Excel table. The new column will be appended to the right. |
Add a row into a table | Add a new row into the Excel table. |
Add a row into a table [DEPRECATED] | This action has been deprecated. Please use Add a row into a table instead.
|
Create table | Create a new table in the Excel workbook. |
Create worksheet | Create a new worksheet in the Excel workbook. |
Delete a row | Delete a row using a key column. |
Get a row | Get a row using a key column. |
Get tables | Get a list of tables in the Excel workbook. |
Get worksheets | Get a list of worksheets in the Excel workbook. |
List rows present in a table | List rows present in a table. |
Run script (Preview) | Runs a script against an Excel workbook. |
Update a row | Update a row using a key column. The input value will overwrite the specified cells and columns left blank will not be updated. In order to append (instead of overwrite) a value, use the 'Get a row' action to retrieve the content first. |
Add a key column to a table
Add a key column to an Excel table. The new column will be appended to the right.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
source | True | string | Select from the drop-down or specify one of the following:- 'me'- 'SharePoint Site URL'- 'users/someone's UPN'- 'groups/group Id'- 'sites/SharePoint Site URL:/teams/team name:' (the colons are required). | |
Document Library | drive | True | string | Select a document library from the drop-down. |
file | True | string | Select an Excel file through File Browse. | |
Table | table | True | string | Select a table from the drop-down. |
idColumn | string | Provide the key column name. |
Add a row into a table
Add a new row into the Excel table.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
source | True | string | Select from the drop-down or specify one of the following:- 'me'- 'SharePoint Site URL'- 'users/someone's UPN'- 'groups/group Id'- 'sites/SharePoint Site URL:/teams/team name:' (colons are required). | |
Document Library | drive | True | string | Select a document library from the drop-down. |
file | True | string | Select an Excel file through File Browse. | |
Table | table | True | string | Select a table from the drop-down. |
item | True | dynamic | Row to add into the specified Excel table. | |
DateTime Format | dateTimeFormat | string | DateTime Format. |
Returns
Add a row into a table [DEPRECATED]
This action has been deprecated. Please use Add a row into a table instead.
Add a new row into the Excel table.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
source | True | string | Select from the drop-down or specify one of the following:- 'me'- 'SharePoint Site URL'- 'users/someone's UPN'- 'groups/group Id'- 'sites/SharePoint Site URL:/teams/team name:' (colons are required). | |
Document Library | drive | True | string | Select a document library from the drop-down. |
file | True | string | Select an Excel file through File Browse. | |
Table | table | True | string | Select a table from the drop-down. |
item | True | dynamic | Row to insert into the specified Excel table. |
Returns
Create table
Create a new table in the Excel workbook.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
source | True | string | Select from the drop-down or specify one of the following:- 'me'- 'SharePoint Site URL'- 'users/someone's UPN'- 'groups/group Id'- 'sites/SharePoint Site URL:/teams/team name:' (the colons are required). | |
Document Library | drive | True | string | Select a document library from the drop-down. |
file | True | string | Select an Excel file through File Browse. | |
Table name | TableName | string | Enter the Excel table name. | |
Range | True | string | Enter the table address using A1 notation. | |
Columns names | ColumnsNames | string | Enter the columns names separated by ';' or ','. |
Returns
Table metadata
Create worksheet
Create a new worksheet in the Excel workbook.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
source | True | string | Select from the drop-down or specify one of the following:- 'me'- 'SharePoint Site URL'- 'users/someone's UPN'- 'groups/group Id'- 'sites/SharePoint Site URL:/teams/team name:' (colons are required). | |
Document Library | drive | True | string | Select a document library from the drop-down. |
file | True | string | Select an Excel file through File Browse. | |
Name | name | string | Worksheet name. |
Returns
Delete a row
Delete a row using a key column.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
source | True | string | Select from the drop-down or specify one of the following:- 'me'- 'SharePoint Site URL'- 'users/someone's UPN'- 'groups/group Id'- 'sites/SharePoint Site URL:/teams/team name:' (the colons are required) | |
Document Library | drive | True | string | Select a document library from the drop-down. |
file | True | string | Select an Excel file through File Browse. | |
Table | table | True | string | Select a table from the drop-down. |
idColumn | True | string | Select a column from the drop-down. | |
Key Value | id | True | string | Enter the key value. |
Get a row
Get a row using a key column.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
source | True | string | Select from the drop-down or specify one of the following:- 'me'- 'SharePoint Site URL'- 'users/someone's UPN'- 'groups/group Id'- 'sites/SharePoint Site URL:/teams/team name:' (the colons are required) | |
Document Library | drive | True | string | Select a document library from the drop-down. |
file | True | string | Select an Excel file through File Browse. | |
Table | table | True | string | Select a table from the drop-down. |
idColumn | True | string | Select a column from the drop-down. | |
Key Value | id | True | string | Enter the key value. |
dateTimeFormat | string | DateTime Format. |
Returns
Get tables
Get a list of tables in the Excel workbook.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
source | True | string | Select from the drop-down or specify one of the following:- 'me'- 'SharePoint Site URL'- 'users/someone's UPN'- 'groups/group Id'- 'sites/SharePoint Site URL:/teams/team name:' (colons are required). | |
Document Library | drive | True | string | Select a document library from the drop-down. |
file | True | string | Select an Excel file through File Browse. |
Returns
Name | Path | Type | Description |
---|---|---|---|
value | value | array of object | |
value.id | string | Table Id. | |
Name | value.name | string | Table name. |
value.showBandedColumns | boolean | Show banded columns. | |
Highlight first column | value.highlightFirstColumn | boolean | Highlight first column. |
value.highlightLastColumn | boolean | Highlight last column. | |
Show banded rows | value.showBandedRows | boolean | Show banded rows. |
value.showFilterButton | boolean | Show filter button. | |
Show headers | value.showHeaders | boolean | Show headers. |
value.showTotals | boolean | Show totals. | |
Style | value.style | string | Table style. |
Get worksheets
Get a list of worksheets in the Excel workbook.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
source | True | string | Select from the drop-down or specify one of the following:- 'me'- 'SharePoint Site URL'- 'users/someone's UPN'- 'groups/group Id'- 'sites/SharePoint Site URL:/teams/team name:' (colons are required). | |
Document Library | drive | True | string | Select a document library from the drop-down. |
file | True | string | Select an Excel file through File Browse. |
Returns
Name | Path | Type | Description |
---|---|---|---|
value | value | array of WorksheetMetadata |
List rows present in a table
List rows present in a table.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
source | True | string | Select from the drop-down or specify one of the following:- 'me'- 'SharePoint Site URL'- 'users/someone's UPN'- 'groups/group Id'- 'sites/SharePoint Site URL:/teams/team name:' (the colons are required) | |
Document Library | drive | True | string | Select a document library from the drop-down. |
file | True | string | Select an Excel file through File Browse. | |
Table | table | True | string | Select a table from the drop-down. |
$filter | string | An ODATA filter query to restrict the entries returned. | ||
Order By | $orderby | string | An ODATA orderBy query for specifying the order of entries. | |
$top | integer | Total number of entries to retrieve (default = all). | ||
Skip Count | $skip | integer | The number of entries to skip (default = 0). | |
$select | string | Comma-separated list of columns to retrieve (first 500 by default). | ||
DateTime Format | dateTimeFormat | string | DateTime Format. |
Returns
Run script (Preview)
Runs a script against an Excel workbook.
Parameters
Why Use Excel For Business
Name | Key | Required | Type | Description |
---|---|---|---|---|
source | True | string | Select from the drop-down or specify one of the following:- 'me'- 'SharePoint Site URL'- 'users/someone's UPN'- 'groups/group Id'- 'sites/SharePoint Site URL:/teams/team name:' (colons are required). | |
Document Library | drive | True | string | Select a document library from the drop-down. |
file | True | string | Select an Excel file through File Browse. | |
Script | scriptId | True | string | Select the script you want to run from the drop-down. |
ScriptParameters | True | dynamic | Provide the script's parameters. |
Returns
Excel For Business Accounting
Update a row
Excel For Business Expenses
Update a row using a key column. The input value will overwrite the specified cells and columns left blank will not be updated. In order to append (instead of overwrite) a value, use the 'Get a row' action to retrieve the content first.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
source | True | string | Select from the drop-down or specify one of the following:- 'me'- 'SharePoint Site URL'- 'users/someone's UPN'- 'groups/group Id'- 'sites/SharePoint Site URL:/teams/team name:' (the colons are required) | |
Document Library | drive | True | string | Select a document library from the drop-down. |
file | True | string | Select an Excel file through File Browse. | |
Table | table | True | string | Select a table from the drop-down. |
idColumn | True | string | Select a column from the drop-down. | |
Key Value | id | True | string | Enter the key value. |
item | True | dynamic | Provide the item properties. | |
DateTime Format | dateTimeFormat | string | DateTime Format. |
Returns
Triggers
For a selected row | Triggers a flow for a selected row in an Excel table. (Available only for Power Automate.) |
For a selected row
Triggers a flow for a selected row in an Excel table. (Available only for Power Automate.)
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
Location | True | string | Select from the drop-down or specify one of the following: 'me', 'SharePointSiteURL', 'users/someone's UPN', 'groups/group id', 'sites/SharePoint Site URL:/teams/team name:' (colons are required) | |
DocumentLibrary | DocumentLibrary | True | string | Select a document library from the drop-down |
File | True | string | Select an Excel file through File Browse | |
Table | Table | True | string | Select a table from the drop-down |
Returns
Definitions
TableMetadata
Table metadata
Name | Path | Type | Description |
---|---|---|---|
name | string | Table name | |
title | title | string | Table title |
x-ms-permission | string | Table permission | |
x-ms-capabilities | x-ms-capabilities | TableCapabilitiesMetadata | Metadata for a table (capabilities) |
schema | Object | ||
referencedEntities | referencedEntities | Object | |
webUrl | string | Url link |
TableCapabilitiesMetadata
Metadata for a table (capabilities)
Name | Path | Type | Description |
---|---|---|---|
sortRestrictions | TableSortRestrictionsMetadata | Metadata for a table (sort restrictions) | |
filterRestrictions | filterRestrictions | TableFilterRestrictionsMetadata | Metadata for a table (filter restrictions) |
selectRestrictions | TableSelectRestrictionsMetadata | Metadata for a table (select restrictions) | |
isOnlyServerPagable | isOnlyServerPagable | boolean | Server paging restrictions |
filterFunctionSupport | array of string | List of supported filter capabilities | |
serverPagingOptions | serverPagingOptions | array of string | List of supported server-driven paging capabilities |
Object
TableSortRestrictionsMetadata
Metadata for a table (sort restrictions)
Name | Path | Type | Description |
---|---|---|---|
sortable | boolean | Indicates whether this table has sortable columns | |
unsortableProperties | unsortableProperties | array of string | List of unsortable properties |
ascendingOnlyProperties | array of string | List of properties which support ascending order only |
TableFilterRestrictionsMetadata
Metadata for a table (filter restrictions)
Name | Path | Type | Description |
---|---|---|---|
filterable | boolean | Indicates whether this table has filterable columns | |
nonFilterableProperties | nonFilterableProperties | array of string | List of non filterable properties |
requiredProperties | array of string | List of required properties |
TableSelectRestrictionsMetadata
Metadata for a table (select restrictions)
Name | Path | Type | Description |
---|---|---|---|
selectable | boolean | Indicates whether this table has selectable columns |
WorksheetMetadata
Name | Path | Type | Description |
---|---|---|---|
id | string | Worksheet Id. | |
Name | name | string | Worksheet name. |
position | integer | Worksheet position. | |
Visibility | visibility | string | Worksheet visibility. |