Excel MCP Server Tutorial- Introduction
Everyone can be a data analyst in 10 minutes. This is the first video of this tutorial series. It draws a big picture of how the Excel-MCP-Server works.
1: Prompt to generate data overview of the Orders worksheet.
Use excel-mcp-server to read the Orders worksheet in NorthwindTradersfull.xlsx and do the data overview analysis.
2: Prompt to do missing value analysis
Use excel-mcp-server to read the Orders worksheet in NorthwindTradersfull.xlsx and do the missing value analysis.
3: Prompt to generate a new worksheet including calculation in a new file
Use mcp-excel-server tools to execute below steps. Don't guess column names. Always use list_columns tool to find the column names.
(1): use mcp-excel-server tools to read all worksheets in NorthwindTradersFull.xlsx.
(2): copy all the original worksheets into a new file named NorthwindTradersFull_New.xlsx.
(3): merge Orders and Order Details worksheets into a new worksheet called OrdersAll in file NorthwindTradersFull_new.xlsx.
(4): add a column 'Total' by multiplying 'Unit Price' and 'Quantity' counting in discount and save to the OrdersAll worksheet in file NorthwindTradersFull_new.xlsx.
(5): then use 'Total' to create column 'Tax' with a 10% rate and save to the OrdersAll worksheet in file NorthwindTradersFull_new.xlsx.
(6): finally create column 'FinalAmount' by adding 'Total' and 'Tax' and save to the OrdersAll worksheet in file NorthwindTradersFull_new.xlsx.
4: Prompt to find the top 2 customers, bottom 2 customers, top 2 employees.Then generate a dashboard.
use excel-mcp-server to read NorthwindTradersfull.xlsx. Make sure to use tools to find the column names. Don't guess. Don't create extra files. Just use analyze_data tool find the below information only in the Orders and Order details sheets.
(1): the top 2 customers who bought the largest value of products. The value of the purchased products can be calculated as (Unit Price) * Quantity * (1-Discount) . The format of the values should be integers ignoring all the decimals.
(2): the bottom 2 customers who bought least quantity of products.
(3): the top 2 employees who sold the most quantity of products.
Finally, create a dashboard including bar charts only based on the above result using plot_pyecharts_dashboard tool. Place all the generated files into the output folder under the root folder.