Expert Insights for Using Excel Automation | Smartsheet (2024)

Try Smartsheet for Free

Get a Free Smartsheet Demo

By Joe Weller | January 22, 2019 (updated August 27, 2021)

Why manually perform repetitive data-related tasks in Excel when you can use the power of the application to automate them? In this article, we will explain how you can save time, reduce errors, and automate basic tasks with Excel automationand provide shortcuts and professional tips for getting started.

Included on this page, you'll find details on what excel automation is, steps on how to automate excel dashboards and reports, and more.

What Is Excel Automation?

Using Excel’s automation tools, you can accomplish a variety of steps - like importing CSV files, adding headers, moving columns, formatting text, and more -with a single click.

Additionally, if you go on vacation, you could send a spreadsheet to a fellow employee who can do the same thing without having to reinvent the wheel. More advanced versions of automation allow you to add custom logic, so that the automated process can take different steps based on the results of prior steps.

While there is some overlap, there are three main ways to automate tasks in Excel:

  • Macros: A sequence of actions that have been recorded and can be reused. They can be user-generated or received from other users or downloaded.

  • Visual Basic: A programming language built into Microsoft Office products. Visual Basic (also knowns as Visual Basic for Applications or VBA) allows a user to to choose and modify pre-selected pieces of code via a graphical user interface, called Visual Basic Editor (VBE). Macros are saved in VBA, and can be viewed and edited in the VBE.

  • Add-ins: Third-party programs that can be downloaded and connected to Excel. Add-ins (sometimes referred to as plug-ins) can be created in Visual Basic, or in other programming languages.

Some common uses for automation include the following:

  • Adding standard formatting cells, ranges, rows, columns, or worksheets

  • Saving a worksheet in a particular format (like PDF) with a particular file name format (e.g., DailySalesReportxxxxxxxx, where the x’s are replaced with the current date)

  • Moving columns or rows into a certain order

  • Creating and formatting a chart based on data in a worksheet

  • Creating and formatting a table

  • Creating UserForms

Benefits of Using Excel Automation

In addition to saving time and reducing the effort expended, there are advantages to automating tasks in Excel. These include the following:
Standardized document formats and consistent results

  • Error reduction

  • Better reporting

  • No programming knowledge required (if macros or add-ins are used)

  • Integration with apps such as Microsoft Word and PowerPoint

  • Reduction in brain drain due to staff turnover

  • Ability to get work done when someone is out of the office

Expert Insights for Using Excel Automation | Smartsheet (1)

Jon Peltier is President and Owner of Peltier Technical Services, a company that provides commercial and custom solutions for Microsoft Excel that enhance information presentation, integrate with other applications in Microsoft's Office suite, and save valuable time through automation.

Peltier explains, “Automation means it’s done the same way every time and it's done a lot faster. I won’t say that Visual Basic programs don't have any errors, but you don't have the kind of errors that you get with a user who is bored out of his mind doing the same stuff time and again.”

Challenges to Using Macros for Automation

There are a few issues to be aware of when using macros and VBA code, but that doesn’t mean it’s not worth the initial investment in time to make Excel automation work for you. Keep the following in mind when using macros:

  • They can do things you don’t expect (such as deleting data), so ensure you understand how they work.

  • They can be malicious, so it’s imperative to verify that you can trust the source of the macro or code.

  • Not all macros will work correctly on all platforms, and some platforms (such as browser-based versions of Excel or Excel on mobile devices) don’t run macros at all.

Saving a workbook as a template does not save the macros. In order to save a macro in a standard template, you’ll need to save it as a macro-enabled template, which has the extension XLSM.

See how Smartsheet can help you be more effective

Expert Insights for Using Excel Automation | Smartsheet (2)

Watch the demo to see how you can more effectively manage your team, projects, and processes with real-time work management in Smartsheet.

Watch a free demo

How to Use Excel Macros to Automate Tasks

First, decide which steps you want your macros to perform. The next step is to record yourself performing the selected steps. The last step is to run the macro and verify that it performs as expected. Follow the tutorial below to learn how to perform each of these steps in Excel.

Recording a Macro

Macros are created by using the macro recorder. The macro recorder remembers a series of steps a user performs, which can be repeated whenever needed. It’s easy to create macros in this way because users just need to perform actions that they already know how to do. Open an Excel workbook that you want to automate to get started.

  1. From the Tools menu, click Macro, click Record New Macro.

Expert Insights for Using Excel Automation | Smartsheet (3)

2. Click in the box under Macro name in the pop-up window, and type a name for your Macro.

3. Choose where to store the macro from the dropdown menu. There are three options; choose one that fits your needs:

  • This Workbook: The macro is only available in the current workbook.

  • New Workbook: A new workbook opens and the macro is only available in that workbook.

  • Personal Workbook File: This allows a user to use a macro from any Excel workbook opened on that same computer. These files will have the extension .xlsb.

If desired, enter a shortcut key (more on this later). If you enter a letter that’s used by the OS, you’ll get a politely-worded message to change it. Click OK.

Expert Insights for Using Excel Automation | Smartsheet (4)

4. The recording indicator appears below the navigation buttons.

Expert Insights for Using Excel Automation | Smartsheet (5)

5. Perform the steps you want to automate, and click the macro recording indicator when done. It’ll change appearance and look like this.

Expert Insights for Using Excel Automation | Smartsheet (6)

From this point forward, you can also click this button to start recording a new macro.

Running a Macro

Once a macro is recorded, you’ll want to run it. Excel provides a few different ways to run your macros. Use this list to decide which method is right for you:

  • Menu: Click Tools, click Macro, click Macros…, then click the title of the macro. In Windows, simultaneously press alt+F8 to bring up this option.

    Expert Insights for Using Excel Automation | Smartsheet (7)

Select the macro you want and click Run. If you don’t see your macro, it may be stored in a different workbook.

Expert Insights for Using Excel Automation | Smartsheet (8)
  • Keyboard Shortcut: If a shortcut was created when you first named the macro, you can run the macro by keying that shortcut. The key combination for Windows is Control+Shift+ the letter you chose; for Mac, it is Option+Command+ the letter you chose.

    • You can also add a shortcut after the fact. Simply click Tools, click Macro, click Macros…, click the title of the macro, and then click Options.

  • Worksheet Button: You can add a button to the spreadsheet that can be assigned to run a macro. If you send the spreadsheet to another person, they can easily see how to run the macro. To add a button:

    • Click the Developer tab (if the Developer tab is not showing, here are the instructions to add it).

    • Click Button. A button will appear on your spreadsheet (the first one will be called Button1, etc.). Click on it, and assign the desired macro.

      Expert Insights for Using Excel Automation | Smartsheet (9)
      Expert Insights for Using Excel Automation | Smartsheet (10)
    • Double click on the button to edit its display name.

      Expert Insights for Using Excel Automation | Smartsheet (11)
  • Toolbar Button: For quick access, you can add a button to a toolbar. You can find instructions for this task here.

How to Use Visual Basic to Do Excel Automation

Visual Basic is more complicated to use than macros because it requires some knowledge of the Basic programming language. When macros are created, Excel saves them as Visual Basic; existing macros can be edited in the Visual Basic Editor (VBE), which is the interface in Excel.

How to Do Excel Automaton without Macros

Add-ins are third-party programs that can be downloaded and connected to Excel. Some examples are AutomateExcel, HelpSystems Automate, Kloudio, Analytics Canvas, Insight Software, PowerOLAP, and Domo. They are also available from companies like Peltier Technical Services, Inc..

If you have programming chops or know someone who does, add-ins that interact with Excel can be developed in languages such as Python, SQL, C#, M, Java, C++, and D. These can be customized to do many things.

How to Automate Excel Reports

The creation of reports can be automated via any of the available methods. If it’s a simple report, it can be done with a macro. For more advanced reports, some VBA coding or an add-in will be required.

For reports, steps like opening a source file, removing unneeded data, formatting cells, adding titles, and adding the current date are all doable.

How to Automate Excel Dashboards

Dashboards can also be automated via any of the available methods. For a dashboard, steps like accessing a source file, copying data from cells in the source file to cells in the dashboard, and updating the date and time are all feasible.

How to Automate Excel Using Visual Basic

You can use Visual Basic to automate tasks in Excel. Visual Basic gives users more control over the steps than do macros; however, users need to have knowledge of the programming language. You can find more information on how to get started with Visual Basic here.

How to Automate Word Documents from Excel

If you want to add data in an Excel worksheet to a Word document, and keep the data update in the Word document as it changes in the worksheet, you simply need to copy and paste.

When you paste the Excel cells into the Word document, click on the Paste Options button in Word, and select one of the options that say Link to Excel. As long as the documents are on the same computer, the Word document will automatically update as the Excel data changes.

Expert Insights for Using Excel Automation | Smartsheet (12)

For more complicated data sets, use Visual Basic or other programming languages to open a Word document and input Excel data into the document. Instructions for this are beyond the scope of an introductory-level article, but they can be found online for anyone looking for a challenge.

How to Learn Excel Automation

Like any other skill, education and practice are key. Find a resource that explains what you are trying to learn, and then practice, practice, practice. Macros are fairly easy to learn on the job, especially for a series of simple steps. You can also get a manual, find an online tutorial, or take a class.

Peltier advises, “I would suggest that people take a project they really think would benefit from automation and just start working on it. Start out with a small piece of it, like opening the file and copying the data in the right worksheet. Figure out how to automate that, then figure out the next step, and find a few web sites where they have a lot of good examples and just go back to them. But do it with that specific project in mind so that your motivation is there; when you finish you'll have something that'll save you an hour a day or more.”

Visual Basic coding is more difficult, but can be learned the same way. There are also many tutorials online and in manuals that can help you learn how to use VBA. Or, if you’re a learn-by-doing person, record some macros and look at the code to figure out how it works. There are also classes available.”

To view the code for a particular macro, click Tools, click Macro…, click the name of the macro, and click Edit.

Says Peltier, “I started learning it about 20 years ago by recording my own macros and then seeing what code was recorded, then saying, ‘Ok, I want it to work a little bit differently than what I recorded.’ So you go and you play around with that. There is a wealth of information about Visual Basic for Excel on the internet. People end up reading examples on my site or other sites like Stack Overflow, or tMrExcel.com to find all kinds of stuff.”

What Are Excel Automation Servers?

An automation server (sometimes called an automation component) is a subtype of automation add-ins that are built into Excel. An automation server is an application that opens programmable objects (aka automation objects) in other applications (aka automation clients).

Developers use automation add-ins as a library of functions that can be called from cells in a worksheet. There are two ways to control an automation server: late binding (the objects are not declared until run-time) and and early binding (the objects are declared during design). Early binding is considered better because the application knows in advance what’s coming.

How to Use the Excel Object Model in Automation

The Excel Object Model is each entity in Excel, which has properties that can be called on or changed via add-ins, VBA, or macros. Objects include an entire workbook, a worksheet, a row, a column, a range of cells, an individual cell, a pivot table, a chart, and the active cell or range of cells.

Some common properties are Name, Value, Row Number, Column Number, if it’s protected, if it contains a formula, and its display properties.

Code can reference the properties of an entity and use that to make a change. For example, it can look at the value of a cell and if it’s less than a target, it can change the color of the text in that cell to red. Code can also change the properties of an entity. For example, you can rename a worksheet from the default (e.g. Sheet1) to something else (e.g. February 2018 Expenses).

Other Tasks that Can Be Accomplished via Excel Automation

Dashboards and reports aren’t the only tasks you can automate in Excel. In fact, there are a variety of other things that can be automated as well. The following list of actions can be automated in Excel — use it to get ideas of how you can implement automation in your own workflow.

Enhancing Data Readability

  • Create a chart

  • Create and format tables

  • Create a table of contents

  • Create a cover page

  • Display a UserForm

  • Expand pivot table data

  • View locations that are listed in spreadsheet as points on a map

Manipulating Data

  • Delete rows that meet certain criteria (e.g., blank or negative values)

  • Schedule obtaining data from other sources and insert it in a cell

Editing and formatting

  • Format text

  • Format cells

  • Insert a formula

  • Add headers to a column

  • Open a document (e.g. csv file, xlsx file)

  • Remove duplicate rows

  • Add or remove sheets

  • Convert a vector (i.e. a row) to a matrix

  • Convert a matrix to a vector

  • Copy a cell’s format

  • Select the contents of a cell and copy it to other cells

Notifications

  • Send an email when a cell value hits a certain threshold

  • Send an email whenever a particular macro is run

Other

  • Return the column or row of a cell based on its contents (i.e., the cell with value 5309 is in column AA)

  • Show the formula in a cell

  • Find a word in a string that meets criteria (e.g., 10 characters, starts with L, etc,)

  • Find the last used cell

  • Save and close all open workbooks

  • Manage data sets larger than excel size limits

Connecting Data Across Your Work with Cross-Sheet Formulas in Smartsheet

Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change.

The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed.

When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time.Try Smartsheet for free, today.

Expert Insights for Using Excel Automation | Smartsheet (2024)

FAQs

What are the benefits of Excel automation? ›

Increased efficiency: Automation streamlines repetitive tasks as digital workers handle manual data entry, formatting and analysis of Excel spreadsheets — saving time and effort. Improved accuracy: Working from set rules, digital workers minimize human errors and inconsistencies in data processing and analysis.

How to do automation using Excel? ›

In Excel on the web, for Windows, or for Mac, open an Excel workbook. Select Automate > Automate a Task. Select the template you want to use. Sign in, provide the required information, and then select the Create button.

What are Excel automation capabilities? ›

Office Scripts in Excel allows you to automate your Excel tasks by recording and replaying your actions on different workbooks and worksheets. Use Office Scripts to combine multiple workbooks into a single workbook, control when calculations happen within your workbook, and so much more!

Is Smartsheet better than Excel for project management? ›

Easy work management

Smartsheet has flexible views to display data including grid, card (Kanban), Gantt, and calendar, empowering teams to work together with agility, speed, and accountability. Excel is limited to a grid view, which makes it difficult to plan, track, and report on key workflows.

What are the three main benefits of using Excel? ›

Other than great flexibility, superior number crunching powers and easy access MS Excel also provide its users many benefits including;
  • Layout your data. ...
  • Easy to reformat and rearrange data. ...
  • Process data and analyse with graphs and charts. ...
  • Identify and analyse trends and patterns in large amounts of data.

What is the main advantage of using Power Automate? ›

Power Automate helps your business save time, improve productivity and ultimately work much more effectively. By automating repetitive tasks that take up time within the day, employees can work smarter and improve overall business performance.

What is Excel automation called? ›

​One way organizations attempt automating Excel tasks is through VBA (Microsoft's programming language), otherwise known as macros. While users turn to macros to save time by writing VBA code to run specific Excel tasks, they aren't always the best solution.

What is the best way to automate Excel reports? ›

Pivot tables and charts are a great way to automate reporting in Microsoft Excel. By using a pivot table, teams can quickly create organized reports by summarizing data from multiple sources into one concise view.

Is Excel an office automation tool? ›

Excel automation uses robotic programming to automate Microsoft Excel processes and functions. Since 1982, Microsoft Excel has been supporting businesses with its amazing ability to calculate data across any number of Excel spreadsheets (AKA workbooks) and tabs (AKA worksheets).

What code does Excel automate use? ›

Scripting language: TypeScript or JavaScript

Office Scripts are written in TypeScript, which is a superset of JavaScript. The Action Recorder generates code in TypeScript and the Office Scripts documentation uses TypeScript.

What tasks within the spreadsheet can you automate? ›

They allow users to automate a series of commands or actions within Excel, such as formatting, data entry, and calculations. Macros are particularly useful for automating tasks that involve multiple steps.

What is the limitation of Power Automate in Excel? ›

As mentioned above there is a default limit of 256 rows in Excel connector so in order to increase this limit click on the […] and choose Settings. Enable Pagination and provide a Threshold number that should accomodate the maximum expected number of rows.

What can Smartsheet do that Excel cannot? ›

While Excel is great for basic tasks, Smartsheet is like having a whole set of tools that work together. It's built for teams who need to keep track of projects with lots of moving parts. It automates the repetitive stuff, talks smoothly with other programs like ArcGIS, and keeps everyone on the same page in real-time.

Why is Smartsheet so great? ›

Resource Management by Smartsheet

It gives you all the tools you need to plan, schedule, and optimize your resources. It exists to help you simplify resource allocation and improve planning accuracy. It even offers tools for time tracking, budgeting, forecasting, dynamic reporting, and more.

Who is the competitor of Smartsheet? ›

The best overall Smartsheet alternative is ClickUp. Other similar apps like Smartsheet are monday.com, Asana, Wrike, and Teamwork.com. Smartsheet alternatives can be found in Project Management Software but may also be in Project Collaboration Software or Markup Software.

What are the benefits of automating Excel-based processes or tasks using automation 360 Excel actions? ›

Efficiency: Excel automation reduces the time and effort required to perform tasks, allowing users to focus on more value-added activities. Accuracy: Manual data entry is prone to errors. Automation ensures data accuracy, reducing the risk of costly mistakes.

What can Power Automate do with Excel? ›

Integrate Power Automate with Excel for the web to automate repetitive tasks, reduce errors, and improve productivity. For example, you can track customer feedback, manage projects, or analyze data.

What is MS Excel in office automation? ›

Excel automation is a way of using an application to automatically do tasks without any manual help. With robotic process automation or RPA, you can leverage its power to integrate excel tasks into automated processes. Some actions that Excel is able to automate include: Extracting data from a website to store in Excel.

Top Articles
Latest Posts
Recommended Articles
Article information

Author: Kieth Sipes

Last Updated:

Views: 6010

Rating: 4.7 / 5 (47 voted)

Reviews: 94% of readers found this page helpful

Author information

Name: Kieth Sipes

Birthday: 2001-04-14

Address: Suite 492 62479 Champlin Loop, South Catrice, MS 57271

Phone: +9663362133320

Job: District Sales Analyst

Hobby: Digital arts, Dance, Ghost hunting, Worldbuilding, Kayaking, Table tennis, 3D printing

Introduction: My name is Kieth Sipes, I am a zany, rich, courageous, powerful, faithful, jolly, excited person who loves writing and wants to share my knowledge and understanding with you.