Data Import & Export

How to Archive Excel Cells as Values Without Formulas or Macros

Muhammad Talha Sep 25, 2026 869 views

Question details

The user wants to copy a selected range (e.g., A1:H30) to another sheet as values, create a custom file name, and trigger the action with a button, without using traditional formulas or VBA macros.

How to Archive Excel Cells as Values Without Formulas or Macros

Product: Excel
Device & OS: not provided
Scenario: Automating the extraction and archiving of a specific data range into a separate file or sheet as static values, triggered by a simple button click.
Observed behavior: Standard formulas are only capable of displaying conditional copies of data and cannot push, save, or trigger the creation of a separate archive file. Alternative automation tools are required.

Before you start

Before automating your archiving process, ensure your source data range (e.g., A1:H30) is clearly defined and that your organization allows the use of Microsoft 365 cloud automation tools like Power Automate or Office Scripts.

Solution 1Recommended

Use Power Automate for Data Movement

Power Automate can extract data from your Excel worksheet and save it as a new file with a dynamic, customer-based name without writing any VBA code.

Power Automate is ideal for moving data between files and cloud storage (like OneDrive or SharePoint). Since traditional formulas cannot create files or push data outward, a cloud flow acts as the perfect bridge.

Create a new flow

Log into Power Automate and create an 'Instant cloud flow' so it can be triggered manually.

Extract Excel data

Add the 'Excel Online (Business)' connector and use the 'List rows present in a table' action to pull the data from your specified range or table.

Generate a dynamic file name

Use dynamic content from your Excel data (such as a customer name field) to define the file name variable.

Create the archive file

Add a 'Create file' action for OneDrive or SharePoint. Map the dynamic file name to the File Name field, and insert the extracted Excel data into the File Content field.

Data Formatting: Power Automate extracts data as raw values, meaning formulas from the original workbook are automatically stripped out in the newly created archive.

Solution 2

Use Office Scripts with a Button Trigger

If you need a physical button within your Excel workbook to trigger the archiving process without relying on legacy VBA macros, Office Scripts is the modern solution.

Solution 3

Manually Copy and Paste Special as Values

If automation tools are unavailable or too complex for your current setup, using the built-in Paste Special feature is the most reliable manual method.

Easily Archive Cells as Values in WPS Spreadsheet

WPS Office provides a highly compatible, lightweight environment to manage and archive your spreadsheet data. You can effortlessly strip formulas using Paste Special or automate repetitive archiving tasks using modern WPS JS Macros directly linked to buttons.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing the data you want to archive.
2. Select and copy the range: Highlight the specific range (e.g., A1:H30), right-click, and select 'Copy'.
3. Paste as values in a new sheet: Open a new sheet or workbook, right-click the destination cell, select 'Paste Special', and choose 'Values'.
4. Automate with JS Macros (Optional): Navigate to the Tools tab, click 'JS Macro', and record or write a script to copy values automatically.
5. Assign to a button: Insert a shape from the Insert tab, right-click it, and select 'Assign Macro' to trigger your archive process with one click.

Free and lightweight Microsoft Office alternativeFully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Built-in JS Macro support for secure, modern automationIntuitive interface for creating shapes and clickable buttons

microsoft office alternative - wps office

Frequently Asked Questions

Can a standard Excel formula save data to another file?

No, standard Excel formulas are designed to pull data or display conditional results within an open workbook. They cannot push data outwards, trigger file creation, or overwrite cells as static values. Automation tools like Power Automate or macros are required for pushing data to new files.

What is the difference between Office Scripts and VBA macros?

Office Scripts use TypeScript and are primarily designed for web-based automation and seamless integration with cloud services like Power Automate. VBA (Visual Basic for Applications) is an older language built into desktop Excel. Office Scripts are often preferred in modern, cloud-first environments.

How do I dynamically name an archived file based on a cell value?

If using Power Automate, you can extract the specific cell value using the 'Get a row' or 'List rows present in a table' action. You can then insert that extracted dynamic content into the 'File Name' field of the 'Create file' action, ensuring every archive is named automatically based on your customer data.

Excel & Data Problems