When working with Excel files, most of the automation comes down to three main actions.

• Reading the data: retrieving rows, columns, or entire sheets from the file;

• Processing the data: letting AI analyze or transform the information;

• Writing the results back: updating or modifying the file with new values.

The first step is to add a tool that reads data, detects sheets, and returns rows or columns in a usable format. To build your first tool, start by creating a new Python script and setting up the basic MCP server structure.

From there, define a function that can read a full Excel sheet. You don't need to build it entirely yourself; with the right prompt, Claude can generate the code in seconds. For example, ask it to create a function that loads all data from a given sheet and returns it in a usable format.

The key is to be specific in your prompt so that the generated code is concise and does exactly what you need. Claude will generate something like this.

Once you have the function, copy it into your script and check the imports. Run it in your terminal with a simple print statement to verify the output. If everything is set up properly, you'll see the Excel data returned in dictionary form. This confirms that the reading part of your MCP tool works.

When you try to read an Excel file from Claude, you will encounter the issue that the tool doesn’t know where the file is located on your computer. This means you have to manually type or copy full file paths every time you run a task, which quickly becomes inconvenient if you work with multiple files.

The solution is to add a file system MCP server. By updating Claude's configuration file and pointing it to a folder on your machine, you give Claude the ability to browse files, find the one it needs, and pass the correct path into your Excel tool.

The next improvement is to add a tool that lists all the sheets in an Excel file. Just like the file system MCP, it eliminates the manual step of typing sheet names into the prompt and improves automation.

The result of the prompt is a minimal Python function that takes the path to an Excel file and returns all available sheet names. With this tool in place, Claude can first check which sheets exist before running any analysis or summaries.

What's left is to add a writing tool. As with the previous steps, you can simply prompt Claude and it will generate the code for you. This tool should take the file path, sheet name, cell reference, and the new value as inputs, then update the Excel file accordingly. Once added, your MCP server won't just read and list data but will also be able to make changes directly inside the file, completing the full cycle of Excel automation. The current script should look something like this.

Now imagine having a folder full of Excel files. With only two or three simple tools reading data, listing sheets, and writing updates you can save hours of repetitive work. Claude can automatically identify sheet names, summarize their contents, and even apply changes directly to the files. What begins as a small setup quickly becomes a powerful automation system.