Automate your repetitive tasks in Excel without VBA macros

As a frequent Excel user, you probably have some daily routine operations. In this case Excel macros helps record the sequence of the user’s actions as a VBA script. This approach is ideal for automating uncomplicated operations. For more sophisticated tasks, users with programming skills can create a VBA project.

The Automation add-in offers a completely new approach to automation of Excel routines:

  • Write commands in a simple Excel table instead of bulky VBA modules
  • Automate even complex and multistep operations
  • Automate XLTools operations: SQL Queries, Export to CSV, Unpivot Table, etc.
  • Create your own custom buttons on the toolbar
  • For advanced users and developers

You do not have to be a VBA-guru. If some of your business processes in Excel take too much time, our XLTools team will help you automate them!

Before you begin, add the Automation tool to Excel

Automation is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Get started with XLTools ribbon
– free trial gives you 14 days of full access to all features.

How to automate operations in Excel without VBA [Download Tutorial]

Excel VBA macros often grow into hundreds lines of script, very inconvenient to work with. XLTools Automation add-in allows you to write commands in simple and concise Excel tables. Table display is more informative, visual and easier to edit. You can also assign an automation command to a custom button on the ribbon.

The Automation add-in is a universal tool to automate virtually any command and their sequences:

  • Automate SQL Queries against Excel tables: SELECT, GROUP BY, JOIN ON, etc.
  • Automatically unpivot a crosstab table to a flat list
  • Automatically export an Excel table to a CSV file
  • Automatically extract data from other Excel or CSV files
  • Automate table filtering, etc.

Here is how you create an automation command in Excel table:

XLTools.SQLSelect – type the name of the command exactly; place it in two merged cells.

XLTools.SQLSelect
SQLQuery:Write the query as you normally would.
ApplyTableName:Type the name for the resulting table.
OutputTo:Specify where to place the result.
Tip: instead of manually typing the query, use the intuitive SQL Queries editor and then copy-paste the script to automation table.
Note: in order for Automation or SQL add-ins to identify references, do not use spaces in the names of worksheets, workbooks, or tables.

We have prepared a Tutorial file with examples, syntax, code and line-by-line transcripts.

Simply write a command using the Tutorial Click Execute Commands Done!

Example: how to automate an SQL query against Excel tables

Let’s take an example of a retail store. Suppose you have to prepare a quarterly sales report. One option is to use the SQL Queries add-in and run a query against your Excel data. But if you have to prepare similar reports regularly, you can automate this SQL query.

  1. Prepare you primary data for SQL query. Format it as a named table, otherwise SQL query will not be able to process data:

    • Select the range "Historical Product Price List and Sales".
    • On the Home tab click Format as Table Apply a table style.
    • On the Design tab, type the table name "Sales2014".
    Format range as table before SQL Queries Automation
  2. Add a new worksheet, e.g. “AutoCommands”, and write the SQL automation command.

    XLTools Automation: Execute SQL Query

  3. Execute the SQL automation command:

    • Select the commands table Click the Execute Commands button on XLTools tab.

    • Done, the result is generated immediately.


      After the command is executed, the query result will be generated in table format. If necessary, you can address this table in further queries.

      In this example, the SQL query has extracted sales data for 3rd Quarter 2014.

    XLTools Automation: SQL Query Output
  4. Add your own buttons to Excel ribbon.

    In each Excel workbook you can create up to 3 custom buttons assigned to your own automation commands:

    • Click Add Custom buttons on XLTools tab.
    • Name the button, e.g. "Generate Q Reports" Validate the range of the commands table.
    • Click Save The custom button will appear on XLTools tab.

    Now, you can easily create a quarterly report in just one button click – literally!
    XLTools Automation: How to add custom buttons to Excel
Any questions or suggestions?
KDataScienceHey xltools i am new on this filed can u make a blog about Ms Excel automation
September 30, 2019 at 07:19
Maria BalobanovaSure, we have actually already started! You can find case studies here and here.
September 30, 2019 at 09:11
tobiloba taiwohow do I know what commands to write now that I have the software?
January 22, 2019 at 20:21
Maria BalobanovaHi and thank you for your interest in our automation add-in! Using the add-in does require some knowledge of SQLite language and VBA. To help you get started, we have a brief tutorial available for download in the article above. For those who purchase the GURU license we also provide help with automation of a basic routine and consulting services. You can also find examples of automated routines here and here. To check that the add-in meets your Excel automation needs, we'll need to have some more info. I have sent you an email.
January 23, 2019 at 10:12
vyshali Hey, Thanks for this amazing tool. i was wondering if you could help me with something. Can this tool be used for my requirement?  I am working on a matrix of numbers and i require certain steps be made on each and every element and output be given as another matrix.this result will be used for the next step.   Can this tool help me? Thanks and have a nice day!
January 19, 2019 at 07:36
Maria Balobanova

Hi vyshali, thanks for your feedback! This could be possible - depends on the steps you require. I have emailed you for details.

January 21, 2019 at 10:49
Clint Butler Can this duplicate things that I can do in Google Sheets?  I don't mind using Google Sheets, but if you lose your account you loose all your sheets.
January 8, 2019 at 00:22
Maria Balobanova

Hi Clint, XLTools Automation is a dektop add-in for Microsoft Excel, it is not compatible with Google Sheets. So if you do prefer desktop Excel to Google Sheets, you may indeed find XLTools useful.

January 8, 2019 at 11:54
shruthiHello, I have one master sheet which i updates frequently. But I need the data to be automated to another sheet with only required rows and columns.Whenever I update the master sheet it should automatically takes the updates to the other sheet where ever i needed (not all the data only few rows and columns) Can you please help here how to make that.
November 13, 2018 at 05:48
Maria BalobanovaHello Shruthi, To answer that we'll need your worksheet example and a bit more detailed workflow description. I have sent you an email.
November 13, 2018 at 10:56
hp printer supportExcellent article. Getting started with these little coding projects is a great way to make one's work easier.
August 18, 2018 at 05:50
Maria BalobanovaThank you! Happy to hear that Automation helps you in your Excel work.
August 20, 2018 at 09:24
N.S.Narayana Rao - Manager IT, Dynam Electro Controls, Bangalore, IndiaI have a task, wherein data of raw materials is stored in an excel sheet(Master Price list). Using this everyday I have to prepare estimations(Offers) for various projects referring to the price list of the raw materials.  I will open an blank worksheet and the moment I enter raw meterial code, data from master sheet must be inserted into the current blank(new) worksheet. Request to let me know how can I accomplish this using Excel 2007 and VB scripts.
January 10, 2018 at 13:02
Maria Balobanova

Hello and thank you for your interest in XLTools! Could you please send a sample file (please remove any confidential data) and a detailed description of what you have – and what need as a result. We’ll take a look and see of this can be accomplished with XLTools Automation. It works with Excel 2007.

January 15, 2018 at 09:38
SonamThanks XLtools for "Create your own buttons on Excel ribbon" currently m using your automation technique No question beacuse it clearly mentioned every single step with note.
July 19, 2017 at 13:47
Maria BalobanovaThank you for your feedback, Sonam! Happy to hear XLTools Automation is helpful for your projects!
July 19, 2017 at 14:38
Madan Hello Maria,   I perform a task only daily basis which consumes 2 hours of time at the start of the day ,to input mobile  numbers from excel sheet to terminal T3270, as the activity is at start of the day it exhaust all my energy , kindly help out any code for this 
June 22, 2019 at 21:35
Maria BalobanovaHello Madan, Thank you for your interest in XLTools. We'll need some more details - I have sent you an email. I do hope we can make this daily routine of yours a bit easier!
June 24, 2019 at 08:19
How can we help?