Working with Excel Ranges, Sheets, and Named Cells in Power Automate Desktop: Reading Tables, Writing Data, and Switching Worksheets Without Macros

Introduction

Picture this: your company has a master sales workbook with twelve monthly sheets. Every Monday morning, someone manually opens the file, flips to the current month's tab, copies the data into a summary sheet, and pastes totals into a dashboard. It takes forty minutes, introduces copy-paste errors, and everyone hates doing it. The task is perfect for automation — but your IT department won't allow macros, and the file doesn't live in SharePoint where cloud-based Excel connectors could help. It's just a plain .xlsx file sitting on a network drive.

This is exactly where Power Automate Desktop's built-in Excel actions shine. You don't need macros, you don't need VBA, and you don't need a cloud connector. PAD's Excel action group gives you precise control over ranges, named cells, and worksheets entirely through a no-code action palette. By the end of this lesson, you'll be able to open an Excel file, navigate between sheets, read a full data table into memory, write processed results back to a specific range, and reference named cells — all without touching a single macro.

What you'll learn:

• How to open an Excel file and attach PAD to an existing workbook
• How to switch between worksheets programmatically
• How to read a table or a specific cell range into a PAD data table variable
• How to write data back to specific cells or ranges on any sheet
• How to use Excel named ranges to make your flows resilient to layout changes

---

Prerequisites

Before working through this lesson, you should be comfortable with the PAD designer interface and understand how to drag actions into a flow. If you're brand new to PAD, start with Getting Started with Power Automate Desktop: Installing, Recording, and Running Your First Desktop Flow first.

You should also understand what a variable is and have a basic familiarity with data tables — the structure PAD uses to hold multi-row, multi-column data in memory. The article on Variables, Lists, and Data Tables in Power Automate Desktop: A Complete Practitioner's Guide covers exactly that.

You'll need:

• Power Automate Desktop installed and licensed (free with a Microsoft account for attended flows)
• Microsoft Excel installed locally (not just the web version)
• A sample Excel workbook to practice with — we'll describe the exact layout to create below

---

Understanding How PAD Talks to Excel

Before touching any actions, it helps to understand the mental model. When PAD works with Excel, it doesn't simulate mouse clicks on the spreadsheet (at least, not in the Excel actions group). Instead, it communicates directly with Excel's COM object model — the same interface that VBA macros use under the hood. This means PAD can read and write data at speeds a human could never match, and it works even if the Excel window is minimized.

The key concept is the Excel Instance. Think of it like a handle — a unique identifier that PAD holds so it knows which open workbook it's talking to. Every Excel action you use requires you to pass in this instance variable. If you open two workbooks, you get two instances, and you must be explicit about which one you're addressing at any given moment.

A second important concept is that PAD's Excel actions work in two modes: reading entire worksheets as data tables and reading or writing specific ranges. Knowing which mode to use for a given task will save you a lot of frustration.

---

Setting Up Your Sample Workbook

To follow along, create a new Excel file called SalesData.xlsx and set it up as follows:

Sheet 1 — named "January": Add headers in row 1: SalesRep, Region, Units, Revenue. Add five rows of sample data beneath them. For example, "Alice Chen", "North", 120, 14400.

Sheet 2 — named "Summary": Leave this sheet mostly empty. Put the label "Total Revenue" in cell A1 and leave B1 blank — you'll write a calculated value there.

Named Range: Select cells A1:D6 on the January sheet (headers plus five data rows). In the Name Box (the small box to the left of the formula bar that normally shows the cell address), type JanuaryData and press Enter. This creates a named range you'll use later.

Save the file somewhere accessible, like C:\AutomationFiles\SalesData.xlsx.

---

Opening a Workbook and Getting an Instance

Open Power Automate Desktop, create a new flow, and name it Excel Range Demo.

In the Actions panel on the left, expand the Excel group. You'll see a rich library of actions. Start by dragging Launch Excel into your flow canvas.

Configure it like this:

• Launch Excel: "and open the following document"
• Document path: C:\AutomationFiles\SalesData.xlsx
• Make instance visible: Toggle this ON while you're building — it lets you watch Excel respond in real time
• Keep instance open after the flow finishes: Leave this OFF for now

When you click Save on this action, PAD automatically creates a variable called %ExcelInstance%. That's your handle to the workbook.

---

Switching Between Worksheets

Now let's teach the flow to navigate sheets. After your Launch Excel action, drag in a Set active Excel worksheet action.

Configure it:

• Excel instance: %ExcelInstance%
• Activate worksheet with: "Name"
• Worksheet name: January

This is the PAD equivalent of clicking the "January" tab at the bottom of the workbook. The action doesn't return a variable — it simply changes which sheet is active before the next read or write operation.

Add a second Set active Excel worksheet action below it, but change the worksheet name to Summary. Run the flow with the debug stepper (the play button with a pause icon). Watch Excel flip between tabs in real time. That's COM automation doing the work, no mouse clicks involved.

---

Reading a Full Worksheet as a Data Table

Switch back to the January sheet (add another Set active Excel worksheet with name January). Now drag in Read from Excel worksheet.

This action has a few important options:

• Excel instance: %ExcelInstance%
• Retrieve: Choose "All available values from worksheet"
• First line of range contains column names: Toggle ON — this tells PAD that row 1 is a header row, so it names the columns SalesRep, Region, Units, Revenue instead of Column1, Column2, etc.

Click Save. PAD creates a variable called %ExcelData% — a data table holding all five rows from the January sheet.

Now you can loop over it. Drag a For each action onto the canvas after the read action. Set the variable to loop over as %ExcelData% and the iteration variable as %CurrentRow%. Inside the loop, you can reference individual cells as %CurrentRow['Revenue']% or %CurrentRow['SalesRep']% — the column names become the dictionary keys.

This "read everything, loop over it" pattern is the workhorse of most Excel automation scenarios. You'll use it constantly — to validate data, to feed records into another system, or to summarize results before writing them back.

---

Reading a Specific Cell Range

Sometimes you don't want the whole worksheet. Maybe you only need rows 2 through 6, columns A through D. Or maybe you want to check a single cell before deciding what to do next.

Drag another Read from Excel worksheet action onto the canvas. This time, change Retrieve to "Values from a cells range." New fields appear:

• Start column: A
• Start row: 2
• End column: D
• End row: 6

This reads exactly the rectangle you defined, without headers (since row 1 isn't included). The result is still a data table, but the columns will be named Column1 through Column4 unless you handle headers separately.

For a single cell read, change Retrieve to "The value of a single cell," then set Column to B and Row to 1. The output variable %ExcelData% will now contain just that one value as a plain text or number — not a data table.

This is exactly how you'd check a "Last Updated" timestamp in a control cell before deciding whether to run the rest of your flow.

---

Using Named Ranges for Resilient Flows

Here's where things get genuinely useful for production automations. Earlier, you created a named range called JanuaryData covering A1:D6. PAD can read named ranges directly, which means if someone inserts a column or moves the table, your flow doesn't break — as long as whoever manages the workbook updates the named range definition.

Drag in a Read from Excel worksheet action. Set Retrieve to "Values from a named cell." In the Name field, type JanuaryData.

PAD queries Excel for the coordinates of that named range, then reads the data from those coordinates. The flow becomes self-documenting too — JanuaryData is far more meaningful than "A1:D6."

Named cells work equally well for individual control values. If you have a cell named ReportMonth that contains "January", your flow can read it and branch accordingly — without hardcoding the sheet layout into your automation. This is the difference between a flow that works for three months and one that works for three years.

---

Writing Data Back to Excel

Reading data is only half the job. Let's write results back to the Summary sheet.

First, calculate a total revenue. After your For each loop, you need a running total. Before the loop, add a Set variable action to create %TotalRevenue% with an initial value of 0. Inside the loop, add another Set variable action:

• Variable: %TotalRevenue%
• Value: %TotalRevenue + float(CurrentRow['Revenue'])%

After the loop ends, switch the active sheet to Summary using Set active Excel worksheet. Then drag in a Write to Excel worksheet action.

Configure it:

• Excel instance: %ExcelInstance%
• Value to write: %TotalRevenue%
• Write mode: "On specified cell"
• Column: B
• Row: 1

Run the flow. Open the Summary sheet — cell B1 should now contain the sum of all revenue values from January. You've just built a cross-sheet data pipeline without a single formula or macro.

---

Writing an Entire Data Table to a Sheet

Sometimes you want to write a whole processed dataset back, not just a single value. Maybe you've filtered the January data to only North region reps and want to paste those rows somewhere.

PAD's Write to Excel worksheet supports writing an entire data table in one action. Configure it like this:

• Value to write: %FilteredData% (your data table variable)
• Write mode: "On specified cell"
• Column: A
• Row: 2

PAD will write the data table row by row, column by column, starting at the cell you specified. If you want to write the column headers too, there's a separate toggle: Write column headers — turn it on and headers will land in the row above your starting row.

---

Saving and Closing the Workbook

After writing data, you almost always want to save and close. PAD has dedicated actions for this:

Save Excel — saves the workbook in its current format (no dialog box). Close Excel — closes the instance. Set "Before closing Excel" to "Save document" to save-and-close in one step.

Add a Close Excel action at the end of your flow, set to save before closing. This is important: if you skip closing properly and just let the flow end, Excel might remain open in the background and lock the file for other users.

For more complex scenarios involving multiple workbooks — like reading from a source file and writing to a separate destination file — you'll manage two instances simultaneously. Each action always requires you to specify which instance you mean. If you want to go deeper into advanced Excel automation patterns including running macros when macros are permitted, the article on Automating Excel with Power Automate Desktop: Reading, Writing, and Running Macros covers the full picture.

---

Hands-On Exercise

Build this complete flow from scratch using your SalesData.xlsx file:

1. Launch Excel and open SalesData.xlsx
2. Set the active sheet to "January"
3. Read all values from the worksheet into %JanuaryData% (with headers)
4. Create a variable %TotalRevenue% set to 0
5. Loop over %JanuaryData% — for each row, add %float(CurrentRow['Revenue'])% to %TotalRevenue%
6. Set the active sheet to "Summary"
7. Write the text "Calculated by PAD" to cell A2
8. Write %TotalRevenue% to cell B2
9. Save and close Excel
10. Add a Display message action at the end that shows: Total Revenue Written: %TotalRevenue%

Run it, check your Summary sheet, and verify the values are correct. Then extend the exercise: try reading the named range JanuaryData instead of reading the whole sheet in step 3, and confirm the results are identical.

---

Common Mistakes & Troubleshooting

"Cannot find worksheet" error: Almost always a name mismatch. Right-click the sheet tab in Excel and verify the exact name, including any leading or trailing spaces (which are invisible but cause failures).

Data table columns named Column1, Column2 instead of real headers: You forgot to enable "First line of range contains column names" in your Read action. Go back into the action and toggle it on.

Numbers reading as text: PAD reads Excel cell values as strings by default. Use %float(CurrentRow['Revenue'])% or the Convert Text to Number action before doing arithmetic. This is the single most common calculation bug in Excel flows.

Excel opens but the flow immediately errors: Check that the file path is correct and the file isn't already open in a protected mode. Files downloaded from email or the internet sometimes open in Protected View, which blocks COM access. Open the file manually first, click "Enable Editing," save it, and then re-run your flow.

Written data lands in the wrong row: PAD row numbering is 1-based, matching Excel's own row numbers. Row 1 in PAD is the same as row 1 in Excel. The confusion usually comes from forgetting that when you write with headers enabled, PAD uses the row above your specified start row for headers, so your data starts one row lower than you expect.

Flow works in testing but fails overnight unattended: This is often a visibility issue — some Excel COM operations behave differently when no user is logged in. If you're running unattended, you may need to ensure a session is available. See the guidance on Attended vs Unattended RPA: Choosing a Run Mode and Configuring Machines in Power Automate for how to handle this properly.

---

Summary & Next Steps

You now have a solid working foundation for Excel automation in PAD without macros. Let's recap what you've covered:

• Opening workbooks and managing instances — the gateway to all Excel actions
• Switching worksheets by name — navigating multi-sheet workbooks programmatically
• Reading whole sheets and specific ranges — getting data into memory as a data table
• Using named ranges — building flows that survive layout changes
• Writing single values and entire data tables — pushing results back into Excel
• Saving and closing cleanly — leaving no zombie Excel processes behind

These building blocks combine in powerful ways. A flow that reads a twelve-sheet workbook, aggregates data from each sheet, and writes a consolidated summary is just a loop around the patterns you practiced here.

For your next steps, consider learning how to handle errors gracefully when a sheet doesn't exist or a file is missing — Error Handling in Desktop Flows: On Block Error, Retry Policies, and Recovery Screenshots will show you how to build flows that recover instead of crash. If you want to package your Excel logic into reusable components that multiple flows can call, Subflows and Reusable Logic in Power Automate Desktop is the natural next lesson. And if the goal is to eventually trigger these Excel flows automatically from a cloud trigger — like a new file arriving in SharePoint — Triggering Desktop Flows from Cloud Flows: Passing Inputs and Returning Outputs shows you exactly how to wire that up.