[Office Development]Build Pro Excel Tools with C# + Excel-DNA

Summary: Let AI help you build a professional Excel add-in with C# + Excel-DNA that you can use directly as a formula inside Excel.

1. Pain Points

If you've been using Excel long enough, you'll eventually run into these three walls:

Wall #1: VBA just isn't "professional." VBA is easy to pick up, but it gets slow on big data, is hard to debug, and code ends up scattered across workbooks. Want to send your tool to a colleague? They have to enable macros—and corporate security policies may block it. VBA feels more like a "personal script" than real software.
Wall #2: Built-in functions aren't enough. You need a function that follows your rules—for example, "split a Chinese address into province/city/district," or "call our company's internal API to calculate a price." You can't write it with built-in formulas; nested IFs will drive you mad.
Wall #3: Real-time data won't come in. Stock prices, sensor readings, live order counts—Excel formulas only update when you refresh them. They can't do "when the data changes, the cell updates automatically." The professional term for this is RTD (Real-Time Data).

So is there a way to use a real programming language to build a tool that double-clicks straight into Excel, works as a formula just like a native feature, and can stream real-time data? Yes—and that's exactly what this article is about: building professional Excel tools with C# + Excel-DNA.

First, two terms to keep you grounded later:

C# (pronounced "C Sharp"): a modern programming language from Microsoft. More disciplined than VBA, less painful than C++, and the primary language on the .NET platform. You can think of it as "a more powerful, more engineering-grade VBA replacement."
Excel-DNA: an open-source framework (think of it as a "translator") that does one thing—lets C# code you write be recognized by Excel directly as formulas and an add-in. Without it, C# and Excel are separated by a wall. With it, what you compile is a .xll file (Excel add-in); double-click once, and your functions appear.

Note: this article is L4 hardcore. You'll need Visual Studio and basic C# concepts (knowing what a "class," "method," and using are is enough). It's not magic, but it also isn't "one-click generate"—every step below requires real hands-on work to get running.

2. Target Output

If you follow this article through, you'll end up with:

A machine with Visual Studio (2019 or later recommended) and .NET Framework (this article uses .NET Framework 4.7.2—see Section 4 and the Pitfalls section for why) installed;
A real Visual Studio project with a clean structure that compiles on your own machine;
An Excel-DNA add-in that compiles to .xll, containing two things:
- A custom function: usable directly as a formula in any cell, like =MyAdd(1,2);
- An RTD (real-time data) function: a cell that auto-refreshes and shows "live" values;
A prompt template for "let AI help scaffold the code," plus the corresponding C# code skeleton (a real runnable structure, not pseudocode).

End result: open Excel → load this .xll → type =MyAdd(10,20) in any cell and get 30; then type =GetLivePrice(), and the cell updates itself with a new number every 1–2 seconds. That's the seed of a "professional Excel tool."

3. Hands-On Case

Environment Prerequisites (Confirm First)

Visual Studio 2019+ (the free Community edition is fine). When installing, check the ".NET desktop development" workload.
.NET Framework 4.7.2 Developer Pack (4.8 also works; this article uses 4.7.2). Why not a newer .NET (i.e., the .NET 5/6/7/8 line)? Because Excel-DNA's support for .NET Framework is the most mature and least painful. You can do it with .NET (Core), but the project setup differs—this article takes the steadiest path. (Note: refer to the exact behavior of the Excel-DNA version you installed; see Pitfall #2.)
Excel of any reasonably recent version (2016+ works).
Normal access to NuGet (Visual Studio's package manager—"the app store for code"—used to one-click install libraries others have written).

Not sure if you have .NET Framework 4.7.2? In VS, when you create a new project, if you see .NET Framework 4.7.2 in the "Target framework" dropdown, you're good.

Step 1: Create the Project and Install Excel-DNA

Open Visual Studio → "Create a new project" → search for and select "Class Library (.NET Framework)" (note: the one with ".NET Framework" in the name—not the plain "Class Library") → Next.
Name the project MyExcelAddIn, choose a folder location you can remember, pick .NET Framework 4.7.2 as the framework → Create.
Once the project is created, in the "Solution Explorer" on the right, right-click the project name MyExcelAddIn → "Manage NuGet Packages".
In the window that opens, go to the "Browse" tab, search for ExcelDna.AddIn → select it → click "Install." (This is the Excel-DNA library in the "app store" mentioned earlier.)
After installation, VS automatically does a few key things for you: it generates a .dna config file in the project and wires up the "output .xll on build" rule. You don't configure anything manually—this matters.

Quick tip: what NuGet installs is ExcelDna.AddIn (with the .AddIn suffix). It's the "full bundle" version; install it and everything needed to run is included. Don't accidentally install just the core ExcelDna package instead. For beginners, .AddIn is the safest choice.

Step 2: Understand the Visual Studio Project Structure

After installing the package, your project in "Solution Explorer" looks roughly like this (this is the real structure, not a diagram):

MyExcelAddIn/                      ← your project root
├── MyExcelAddIn.csproj            ← project file (maintained by VS; you usually don't edit it)
├── MyExcelAddIn.dna               ← Excel-DNA config (auto-generated when the package is installed; see Step 3)
├── Class1.cs                      ← default empty class; safe to delete—we'll replace it with our own files
├── MyFunctions.cs                 ← one you create: holds the custom functions
├── LivePriceServer.cs             ← one you create: holds the RTD server
└── bin/
    └── Debug/
        └── MyExcelAddIn.xll       ← appears after you press F6 to build; this is what Excel loads

How-to: in Solution Explorer, right-click the project → "Add" → "Class" — create two files named MyFunctions.cs and LivePriceServer.cs. The default Class1.cs can be deleted.

Key point: what Excel ultimately recognizes is not the .cs source, not the .dll, but the compiled .xll file. .xll is the dedicated Excel add-in format, produced automatically by Excel-DNA at build time.

Step 3: Excel-DNA Configuration (`.dna` File)

VS already generated MyExcelAddIn.dna for you when the package was installed. Open it—the contents look roughly like this XML (details may vary by version; trust whatever your machine generated):

xml
<DnaLibrary Name="MyExcelAddIn" RuntimeVersion="v4.0">
  <ExternalLibrary Path="MyExcelAddIn.dll" Pack="true" />
</DnaLibrary>

A plain-English walkthrough:

<DnaLibrary ...>: the root tag of the entire config file—this is what Excel-DNA looks for.
Name="MyExcelAddIn": the add-in's display name in Excel. Pick whatever you like; using no spaces or Chinese is safer (some older Excel versions display Chinese inconsistently, so English or pinyin is the safer choice).
RuntimeVersion="v4.0": tells Excel-DNA to use the .NET Framework 4.x runtime. Since we chose .NET Framework, writing v4.0 is fine.
<ExternalLibrary Path="MyExcelAddIn.dll" Pack="true" />: this is the core line. It says "my C# code was compiled to MyExcelAddIn.dll; please embed it in the add-in." Pack="true" means pack-it-in—at build time the .dll gets bundled into the .xll, so when you hand it to a colleague you only need to send one .xll file—you don't have to attach a pile of .dlls. (In professional terms, this is "single-file deployment"—the cleanest option.)

In general, you don't need to edit this file manually—it's already wired up. We explain it here so you understand "how my code becomes something Excel recognizes." If you later want to reference other libraries, you'll need to add <Reference> configs here—but that's advanced usage and out of scope.

Step 4: Write Your First Custom Function (C# Skeleton)

Open MyFunctions.cs and paste the following code in whole (this is a real, compilable skeleton; when AI assists it produces roughly this):

csharp
using ExcelDna.Integration;   // Excel-DNA's core namespace; all custom-function magic lives here

namespace MyExcelAddIn
{
    // The class that hosts custom functions is conventionally written as a static class
    // (the function methods themselves must be public static—see the line-by-line notes below)
    public static class MyFunctions
    {
        // The [ExcelFunction] "attribute" tells Excel-DNA: register the method below as a usable formula in Excel
        [ExcelFunction(
            Name = "MyAdd",                                   // the formula name you type in Excel, =MyAdd(...)
            Description = "Adds two numbers and returns the result",   // tooltip shown on hover
            Category = "My Tools")]                           // which category it belongs to in the Function Wizard
        public static double MyAdd(
            [ExcelArgument(Name = "Number1", Description = "The first addend")] double x,
            [ExcelArgument(Name = "Number2", Description = "The second addend")] double y)
        {
            return x + y;   // The actual computation: just addition here—swap in any logic C# can express
        }
    }
}

A plain-English walkthrough:

using ExcelDna.Integration;: pulls Excel-DNA's toolbox in. Without this line, [ExcelFunction] won't be recognized.
public static class MyFunctions: the custom function methods themselves must be public static—this is a hard requirement of Excel-DNA (COM reflection only calls static methods). The hosting class is conventionally written as a static class (public static class), so the intent—"this class only holds functions, it can't be instantiated"—is obvious at the code level. If you don't want a static class, a regular class is fine too, as long as the function methods inside are public static.
[ExcelFunction(...)]: the "magic tag." Stick it on a method, and Excel-DNA registers a same-named formula in Excel. Name is what the user types in a cell; Description is the hover tooltip; Category groups your function under a category in the Function Wizard, making it easier to find.
[ExcelArgument(...)]: stuck on parameters, gives each a friendly name and description—better user experience.
return x + y;: the function body, just addition for now. Swap in any business logic—address parsing, internal pricing, dictionary lookups, anything. This is the essence of a custom function: expose "your C# logic" as "an Excel formula."

Step 5: Write an RTD Real-Time Data Function (C# Skeleton)

Real-time data (RTD) is a bit trickier. Excel-DNA provides a base class ExcelRtdServer. Inherit from it, and you get a data source that "pushes new values on a schedule."

Open LivePriceServer.cs and paste in the following skeleton (this is the real structure, timer and push logic are wired up—you just need to swap the "mock data" for a real data source, e.g., calling your company's API):

csharp
using ExcelDna.Integration;
using ExcelDna.Integration.Rtd;   // RTD real-time data classes live here
using System.Timers;

namespace MyExcelAddIn
{
    // Inherit ExcelRtdServer—Excel-DNA will use this class as a real-time data source
    public class LivePriceServer : ExcelRtdServer
    {
        private Timer _timer;          // .NET's built-in timer; used to trigger refreshes on schedule
        private double _lastValue = 0; // the most recent value

        // Called when the add-in starts this RTD server (ExcelRtdServer.ServerStart is a parameterless virtual method; there is no overload with a ServerStartEventArgs parameter)
        protected override bool ServerStart()
        {
            _timer = new Timer(1000);  // Fires every 1000 ms (1 second)
            _timer.Elapsed += (s, e) =>
            {
                // The line below is "mock data": in a real scenario, swap it for an API call / sensor read / DB query
                _lastValue = new Random().NextDouble() * 100;

                // Update every currently subscribed topic to the latest value (use GetActiveTopics() to retrieve the active topic set)
                foreach (var topic in GetActiveTopics())
                {
                    topic.UpdateValue(_lastValue);   // Tell Excel: this value changed—please refresh
                }
            };
            _timer.Start();
            return true;  // Returning true means the server started successfully
        }

        // Called when the server shuts down—clean up
        protected override void ServerTerminate()
        {
            _timer?.Stop();
            _timer?.Dispose();
        }

        // Called the first time Excel subscribes to a topic—returns the initial value
        protected override object ConnectData(Topic topic, System.Collections.Generic.IList<string> topicInfo, ref bool newValues)
        {
            newValues = true;
            return _lastValue;
        }

        // Called when Excel no longer needs a topic—clean up
        protected override void DisconnectData(Topic topic)
        {
            // Usually nothing special to do here; can stay empty
        }
    }
}

Then, in MyFunctions.cs, add a "wrapper function" so the user can simply type =GetLivePrice() in Excel to get the live value—instead of the hard-to-remember =RTD(...) syntax:

csharp
// Place this inside the MyFunctions static class, alongside MyAdd
[ExcelFunction(Description = "Gets the live price (RTD real-time data example)")]
public static object GetLivePrice()
{
    // XlCall.RTD is Excel-DNA's way of invoking real-time data
    // The first argument is the RTD server's "full name" (namespace.classname); the rest are variable topic parameters
    return XlCall.RTD("MyExcelAddIn.LivePriceServer", null, "PRICE");
}

About the first argument to XlCall.RTD (the so-called ProgId, a "program identifier"): this article follows the convention of namespace.classname (MyExcelAddIn.LivePriceServer). Different Excel-DNA versions may differ slightly in how RTD is registered—please make sure the server's full name on your machine stays consistent. If it doesn't match up, defer to the official documentation for the Excel-DNA version you installed (see "Technical Accuracy Risks" at the end and Pitfall #4).

Step 6: Build and Load into Excel

Press F6 (or use the menu "Build" → "Build Solution"). If the "Output" window at the bottom shows "Build succeeded," compilation passed.
Open bin\Debug\ under your project folder—you should see **MyExcelAddIn.xll**—that's the finished artifact. **Load whichever .xll shows up in that directory.** (If your project name differs, or you have multiple configurations, the filename will change with them—don't memorize MyExcelAddIn.xll literally.)
Open Excel. Main path (recommended, works on every version): File → Options → Add-Ins → at the bottom in "Manage" pick "Excel Add-ins" → Go → Browse → select the .xll under bin\Debug → OK. Legacy shortcut reference: on very old Excel you can also use Alt+T+I to open the "Add-Ins" manager (historical reference only—varies by version/language, don't rely on it). Note that Alt+F11 opens the VBA editor, which has nothing to do with .xll add-ins—don't press the wrong key.
- Even easier: just drag the .xll file into the Excel window—Excel will ask whether to load it; say yes.
Once loaded, in any cell type =MyAdd(10,20), press Enter, and you get 30. Then type =GetLivePrice()—every 1–2 seconds the cell will update with a new number (Excel throttles RTD updates at ~2-second intervals by default, so it won't tick every second—that's normal), confirming RTD push is working.

If Excel says "macros have been disabled" or fails to load: first make sure the file is being opened from local disk (not directly from email or cloud share)—Excel usually allows local .xll. If you're on a corporate machine with restrictions, ask IT to add the add-in to the trusted list. This is not a code issue, it's a security policy—see the Pitfalls section.

Appendix: Prompt Template for Letting AI Write the Code

You don't need to memorize C# from scratch. Send the following prompt to any AI assistant and it'll scaffold the structure above for you (then verify and run against this article):

I'm building an Excel add-in (.xll) with C# + Excel-DNA. Please give me a directly-compilable code skeleton with these requirements:
1. A custom function marked with [ExcelFunction], function name MyAdd, two double parameters,
   with English Name / Description / Category, that adds two numbers;
2. An RTD server inheriting from ExcelRtdServer, using a Timer to push a random number every 1 second,
   and a wrapper function GetLivePrice() that invokes it via XlCall.RTD;
3. The corresponding .dna configuration example (ExternalLibrary, Pack=true);
4. Use .NET Framework 4.7.2, namespace MyExcelAddIn.
Please note: provide only a real, runnable skeleton, and clearly indicate which spots need to be swapped for real data sources.

Prompt tips: lock down "framework / version / namespace / what to do" first, then explicitly tell AI to mark "what needs swapping for real data." That way the code it returns is grounded in reality, not invented out of thin air. After receiving the code, verify against Steps 4 and 5 of this article line by line—don't run it blindly.

4. Principle Recap

One sentence on what's happening under the hood: when your C# project builds, Excel-DNA compiles your code into a .dll, then packages it into an .xll add-in according to .dna. When you load this .xll in Excel, the Excel-DNA runtime scans every method you tagged with [ExcelFunction] and registers each one as a formula Excel understands—so =MyAdd(...) behaves just like SUM. The RTD part uses ExcelRtdServer to spin up a timer in the background; every time data updates, it calls topic.UpdateValue(...) to actively push the value to Excel. Excel receives the notification and refreshes the corresponding cell automatically. That is the fundamental difference between "real-time data" and ordinary formulas, where "if you don't touch it, it doesn't move."

5. Pitfall Guide

Wrong NuGet package: be sure to install ExcelDna.AddIn (the full bundle), not the bare ExcelDna. Installing the wrong one means .xll won't be produced at build time, or you'll get runtime errors. Beginners: look for the one with the .AddIn suffix.
Wrong .NET version: this article uses .NET Framework 4.7.2—the steadiest combo with Excel-DNA. If you pick .NET (Core) / .NET 5+, the project template, the .csproj syntax, and the RuntimeVersion in .dna all differ, and COM/RTD support has extra requirements. If in doubt, start with .NET Framework—don't try to wrestle a new runtime on day one.
Add-in loads but functions don't appear: usually the code didn't compile (first check the "Output" window in VS for errors), or the Path of ExternalLibrary in .dna doesn't match the actual generated .dll name (after installing the package this matches automatically—don't edit it wrong). Another possibility: Excel 64-bit / 32-bit doesn't match your "target platform"—in the project's properties, under "Build" → "Platform target," set it to Any CPU or to match your Excel's bitness.
RTD registration / ProgId mismatch: the first argument to XlCall.RTD is the RTD server's full name (namespace.classname). Different Excel-DNA versions may differ in how RTD auto-registration works behind the scenes; if =GetLivePrice() returns #VALUE! or #NAME?, first double-check the server class name spelling, and consult the official docs for your installed version to confirm the registration mechanism. (The code in this article is a general skeleton—the exact behavior depends on the version you installed.)
Corporate machine blocks loading: .xll is essentially an executable add-in; corporate security software may block it. This is not a code bug—you need to place the file in a local trusted location, or have IT whitelist it. Get it working on your own machine first; worry about distribution later.

6. Advanced Extensions

Reference the basics: if you haven't systematically tried Excel's "lightweight power trio" yet, start by reading B2/B26 (Power Query for data cleansing), B21/B23 (Power BI reports & data model), and B22 (DAX measures) from this series—they're the foundation for "huge efficiency gains without writing code." The C# + Excel-DNA path in this article goes one level deeper: you write your own add-in only when formulas and pivot tables can no longer reach your needs.
Go one level deeper: once you've got this article running, the next things to learn are: ① use Excel-DNA's Ribbon to customize Excel menu buttons—so your tool isn't just formulas, but also button-triggered actions; ② swap the "mock random number" for real data sources (HTTP APIs, databases, message queues) to build truly useful live dashboards; ③ use ExcelDnaPack to bundle all dependencies into a single-file .xll—easy to distribute across the company; ④ add async support (async/await) to your functions—so slow API calls don't freeze Excel.
Cross-track comparison: this article is the "hardcore development" track; if you only want to batch-process existing Excel files without writing an add-in, jump back to B25 (Python + openpyxl)—that's the lighter path.

Recap: today, with a real, compilable C# + Excel-DNA project, you built the seed of a "custom function + RTD real-time data" professional Excel tool. It doesn't rely on macros or nested formulas—it's a proper .xll add-in—and that's your first step from "Excel user" to "Excel tool developer."

🛠️ Build Pro Excel Tools with C# + Excel-DNA