Data Import & Export

How to Copy Excel Formula Results as Values with Power Automate

Guest Writer Sep 30, 2026 868 views

Question details

The user needs to copy Excel formula results as static values into another workbook or a SharePoint list using a scheduled Power Automate flow, without exposing the underlying supporting worksheets.

How to Copy Excel Formula Results as Values Using Power Automate

Product: Microsoft Excel
Device & OS: not provided
Scenario: Setting up a daily scheduled automation to publish calculated Excel data as static values to a secure destination workbook.
Observed behavior: Power Automate does not natively or directly convert formula results into static values when copying data between Excel workbooks.

Before you start

Ensure that your source Excel file is stored in a cloud location accessible to Power Automate, such as OneDrive for Business or SharePoint, and that the data is formatted as an Excel Table.

Solution 1Recommended

Map Excel Rows to a New Worksheet in Power Automate

By reading the rows from the source table and inserting them into a destination table, Power Automate transfers the evaluated static values rather than copying the raw formulas.

This method avoids copying the entire worksheet. Instead, it reads the data cell by cell. When Power Automate pulls data using the 'List rows present in a table' action, it retrieves the calculated results (values) of any formulas by default.

List source rows

In your Power Automate flow, add the 'List rows present in a table' action and configure it to point to your source Excel file and specific table.

Add an apply to each loop

Insert an 'Apply to each' control and select the 'value' dynamic content from the previous step to loop through every row.

Insert rows into the destination

Inside the loop, add the 'Add a row into a table' action, pointing to your destination, values-only workbook.

Map the columns

Click into the fields of the destination table action and map them to the corresponding dynamic content from the source table to pass the static values.

Map Excel Rows to a New Worksheet in Power Automate

Advanced Formatting: If you need to preserve specific number formatting (like currency or dates), ensure the 'DateTime Format' in the action settings is set to ISO 8601 or use an Office Script to handle exact formatting transfers.

Solution 2

Seek Custom Flow Guidance in the Power Automate Community

For complex scheduling needs or highly specific workbook architectures, consulting the official community is the best approach to finding tailored flow designs.

Free Microsoft Office alternative

Try WPS Office for Lightweight Spreadsheet Management

Dealing with complex cloud automation tools just to copy formula values can be overwhelming. WPS Office provides a lightweight, highly compatible alternative for managing your spreadsheets. Easily copy and paste formula results as static values manually or using built-in macro tools, without relying on external cloud flows.

1. Download WPS Office: Visit the official WPS website to download the free office suite for your operating system.
2. Install and Launch: Follow the straightforward installation wizard and open WPS Spreadsheets.
3. Open Your Excel File: Open your existing .xlsx files directly in WPS Office to manage your formulas and values effortlessly.

100% compatibility with Microsoft Excel (.xlsx) formatsFree and lightweight alternative to Microsoft OfficeBuilt-in powerful tools to manually or automatically convert formulas to static valuesFamiliar user interface ensuring seamless migration with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does copying a worksheet duplicate the formulas instead of values?

When you copy an entire worksheet or file, the raw underlying data structure is duplicated, which includes all original formulas. To get only the values, the data must be extracted and written into a new destination, or explicitly pasted using the 'Paste as Values' function.

Can I use Office Scripts to paste values in Power Automate?

Yes. You can write a TypeScript in Excel Online that copies a range and pastes it as values, then trigger this script automatically using the 'Run script' action in your Power Automate flow.

How do I manually paste formula results as values in a spreadsheet?

Highlight the cells containing the formulas, copy them (Ctrl+C), right-click the destination cell, and select the 'Paste as Values' icon (typically represented by a clipboard with the numbers '123').

Excel & Data Problems