Graphed LogoGraphed
Login

How to Export Data from Tally to Excel

Cody Schneider•

Getting your financial data out of Tally and into Microsoft Excel can open up a world of advanced analysis, custom reporting, and clear data visualization. While Tally is fantastic for accounting, Excel offers unmatched flexibility for manipulating and presenting your numbers. This guide will walk you through the most common methods for exporting your Tally data, from a simple click-and-save to creating a live data connection.

Why Bother Exporting from Tally to Excel?

You might be wondering why you'd even need to move your data from a powerful accounting tool like Tally. The answer comes down to flexibility and familiarity. Excel is the go-to tool for ad-hoc analysis and custom dashboarding that goes beyond standard financial statements.

  • Advanced Analysis: Once in Excel, you can use tools like PivotTables, Goal Seek, and complex formulas to slice and dice your data in ways Tally can't. You can easily build sales forecasting models or detailed expense breakdowns.

  • Custom Visualization: Excel's graphing capabilities are far more extensive than Tally's. You can create bar charts, line graphs, pie charts, and combo charts to visually track performance and spot trends.

  • Custom Dashboards: You can build interactive dashboards that pull data from multiple exported Tally reports into a single, at-a-glance view of your business health.

  • Easy Sharing and Collaboration: Virtually everyone in a business environment has Excel. Exporting your reports makes them easy to share with colleagues, managers, or stakeholders who may not have access to Tally.

Before You Export: A Quick Prep Checklist

Before you hit the export button, a little prep work inside Tally can save you a ton of cleanup time later in Excel. The key is to start with the cleanest, most relevant report possible.

Think of it this way: the more you filter inside Tally, the less you have to delete in Excel.

  1. Navigate to the Right Report: Start by opening the specific Tally report you need, whether it's the Day Book, a ledger account, a Sales Register, the Balance Sheet, or the Profit & Loss Statement.

  2. Set the Date Range: Press F2 to set the period for your report. Exporting data for an entire year when you only need last month's numbers will unnecessarily bloat your file.

  3. Use the Configuration Options: This is the most important step. Press F12 (Configure) to see the display options for your current report. Here you can turn columns on or off, decide whether to include opening balances, show transaction types, and more. Tweak these settings until the report on your screen shows exactly the data you want in your final Excel file.

Taking a minute to do this ensures your exported sheet isn't filled with columns and rows you're just going to delete anyway.

Method 1: Using Tally's Built-in Export Function

This is the most straightforward method and will be your go-to for most day-to-day reporting needs. It involves exporting the current report you're viewing directly into an Excel file.

Step-by-Step Instructions:

  1. With your desired report open and configured in Tally, press Alt + E on your keyboard or click the "Export" button in the top menu.

  2. A small menu will pop up. Select "Current" to export the report you are currently viewing.

  3. This will bring you to the Export Configuration screen. Here, you'll finalize the settings for your file. Press "C" (Configure) to see all options.

  4. Choose the File Format: Navigate to "File Format" and select Excel (Spreadsheet) from the list. Press Enter.

  5. Set the Export Location: In the "Export to" field, you can specify the full folder path where you want to save your file (e.g., C:\Users\YourName\Documents\Tally Exports). You can click "Select from Drive" to browse for a folder easily.

  6. Name Your File: Adjust the default "Output File Name" to something descriptive, like Sales_Register_October_2023.xlsx.

  7. Adjust Export Details (Optional but Recommended): This is where you fine-tune the output.

    • Show formatted text and background colour: Set this to "No" if you want raw, unformatted data, which is better for analysis. Set it to "Yes" if you want the export to look exactly like the Tally report.

    • Update existing file: If you're running the same report every week, you can set this to "Yes" to overwrite last week's file or "No" to create a new one each time.

    • Open file after export: Set this to "Yes" for immediate access.

  8. Once you're satisfied with the configurations, press Esc to return to the previous screen and then click E (Send) to begin the export.

Tally will process the data and, if you chose the option, will automatically open the brand-new Excel file for you.

Method 2: Creating a Live Data Connection with ODBC

If you find yourself exporting the same data from Tally multiple times a week to refresh a report, then the ODBC method is a game-changer. ODBC (Open Database Connectivity) is a standard technology that lets different applications (like Excel) talk to databases (like Tally's). By enabling this, you can pull your Tally data directly into Excel and simply hit "Refresh" to get the latest numbers without ever performing another manual export. This is a bit more technical, but well worth the setup time.

Step 1: Enable ODBC in Tally

First, you need to tell Tally that it's okay for external programs to request its data. Tally also needs to be open and running on your computer for this connection to work.

  1. In Tally's main Gateway screen, press F12 (Configure), then select Data Configuration.

  2. Set "Enable ODBC server" to "Yes".

  3. Note the "Port" number listed right below it (the default is usually 9000). You'll need this number in a moment.

  4. Press Ctrl + A to accept the settings and return to the Gateway.

Step 2: Connect Excel to Tally

Now, let's switch over to Excel to create the connection.

  1. Open a new, blank workbook in Excel.

  2. Go to the Data tab on the Ribbon.

  3. In the Get & Transform Data section, click Get Data > From Other Sources > From ODBC.

  4. In the DSN (Data Source Name) dropdown menu, choose TallyODBC_9000 (your port number may be different).

  5. A Navigator window will pop up, showing you all the Tally data tables you can access. This is where you can select data points like Ledger, Voucher, Company, and more.

  6. Select the data table you want to import (e.g., "Ledger" for your chart of accounts) and click Load.

Excel will import the data into a new worksheet as a formatted table. The best part? Whenever you want to update this data, just go to the Data tab in Excel and click the Refresh All button. Excel will automatically pull the fresh data from Tally in seconds.

Tips for Working with Tally Data in Excel

Once your data is in Excel, a few smart steps can make it much easier to analyze.

  • Format as a Table: The first thing you should do is convert your data into an official Excel Table. Select any cell in your data range and press Ctrl + T. Tables give you automatic filtering, sorting, and easier formula writing.

  • Clean the Data: Tally exports sometimes include unwanted summary rows or merged cells. Take a moment to delete these so you have a clean, uniform block of data to work with. Use the "Find & Replace" feature to remove unnecessary characters like currency symbols if they're interfering with formulas.

  • Leverage PivotTables: The PivotTable is arguably Excel's most powerful feature for summarizing data. With your data in a clean table, go to the Insert tab and click PivotTable. You can instantly create summaries of your data, such as sales by month, expenses by category, or outstanding payments by customer.

  • Save a Template: If you regularly export a report and apply the same cleaning steps, charts, and PivotTables to it, save the file as an Excel Template (.xltx). The next time you export, you can just paste the new Tally data in, and all your formatting and reports will update automatically.

Final Thoughts

Moving your data from Tally to Excel doesn't have to be a complicated task. Whether you use the simple direct export for a quick report, a flexible CSV file for large datasets, or create a live ODBC connection for automated refreshes, you can easily unlock the analytical power of your financial data.

While exporting to Excel is a great way to perform custom analysis, we know the cycle of manually pulling reports and cleaning data every week can become tedious. We built Graphed to eliminate this repetitive work entirely. By connecting directly to your various sales and marketing tools - or even pulling data from a centralized Google Sheet - you can use plain English to build real-time dashboards and get instant answers without ever having to export a CSV again.