How to Automate Reports in Excel

PDF download Download Article
Eliminate busywork with Excel macros to simplify and streamline reporting
PDF download Download Article

This wikiHow teaches you how to automate the reporting of data in Microsoft Excel. For external data, this wikiHow will teach you how to query and create reports from any external data source (MySQL, Postgres, Oracle, etc) from within your worksheet using Excel plugins that link your worksheet to external data sources.

For data already stored in an Excel worksheet, we will use macros to build reports and export them in a variety of file types with the press of one key. Luckily, Excel comes with a built-in step recorder which means you will not have to code the macros yourself.

A Quick Guide to Automating Reports in Excel

  1. Open Excel and enable the "Developer" tab, then click the "Developer" button.
  2. Click "Record Macro" and enter a name for the macro.
  3. Click "Store Macro" and then "This Workbook," then hit "OK."
  4. Perform the actions you want to automate for Excel to track.
  5. Click "Stop Recording" when you've finished doing the action, then save your file.
Method 1
Method 1 of 2:

For Data Already In Excel

PDF download Download Article
  1. If the data you need to report on is already stored, updated, and maintained in Excel, you can automate reporting workflows using Macros. Macros are a built in function that allow you to automate complex and repetitive tasks.
  2. Double-click (or click if you're on a Mac) the Excel app icon, which resembles a white "X" on a green background, then click Blank Workbook on the templates page.
    • On a Mac, you may have to click File and then click New Blank Workbook in the resulting drop-down menu.
    • If you already have an Excel report that you want to automate, you'll instead double-click the report's file to open it in Excel.
    Advertisement
  3. If you haven't added the column labels and numbers for which you want to automate results, do so before proceeding.
  4. By default, the Developer tab doesn't show up at the top of the Excel window. You can enable it by doing the following depending on your operating system: [1]
    • Windows — Click File , click Options , click Customize Ribbon on the left side of the window, check the "Developer" box in the lower-right side of the window (you may first have to scroll down), and click OK .
    • Mac — Click Excel , click Preferences... , click Ribbon & Toolbar , check the "Developer" box in the "Main Tabs" list, and click Save . [2]
  5. This tab should now be at the top of the Excel window. Doing so brings up a toolbar at the top of the Excel window.
  6. It's in the toolbar. A pop-up window will appear. [3]
  7. In the "Macro name" text box, type in the name for your macro. This will help you identify the macro later. [4]
    • For example, if you're creating a macro that will make a chart out of your available data, you might name it "Chart1" or similar.
  8. Press the ⇧ Shift key along with another key (e.g., the T key) to create the keyboard shortcut. This is what you'll use to run your macro later.
    • On a Mac, the shortcut key combination will end up being ⌥ Option + ⌘ Command and your key (e.g., ⌥ Option + ⌘ Command + T ).
  9. Click the "Store macro in" drop-down box, then click This Workbook to ensure that the macro will be available for anyone who opens the workbook.
    • You'll have to save the Excel file in a special format for the macro to be saved.
  10. It's at the bottom of the window. Doing so will save your macro settings and place you in record mode. Any steps you take from now until you stop the recording will be recorded.
  11. Excel will track every click, keystroke, and formatting option you enter and add them to the macro's list.
    • For example, to select data and create a chart out of it, you would highlight your data, click Insert at the top of the Excel window, click a chart type, click the chart format that you want to use, and edit the chart as needed.
    • If you wanted to use the macro to add values from cells A1 through A12 , you would click an empty cell, type in =SUM(A1:A12) , and press ↵ Enter .
  12. It's in the Developer tab's toolbar. This will stop your recording and save any steps you took during the recording as an individual macro. [5]
  13. Click File , click Save As , and change the file format to xlsm instead of xls . You can then enter a file name, select a file location, and click Save .
    • If you don't do this, the macro won't be saved as part of the spreadsheet, meaning that other people on different computers won't be able to use your macro if you send the workbook to them.
  14. Press the key combination which you created as part of the macro to do so. You should see your spreadsheet automate according to your macro's steps. [6]
    • You can also run a macro by clicking Macros in the Developer tab, selecting your macro's name, and clicking Run .
    15. Advertisement
Method 2
Method 2 of 2:

For External Data (MySQL, Postgres, Oracle, etc)

PDF download Download Article
  1. Download Kloudio's Excel plugin from Microsoft AppSource. This will allow you to create a persistent connection between an external database or data source and your workbook. This plugin also works with Google Sheets.
  2. Type in the details of your database (database type, credentials) and select any security/encryption options if working with confidential or company data.
  3. Once you've created a connection between your worksheet and your database, you will be able to query and build reports from external data without leaving Excel. Create your custom reports from the Kloudio portal and then select them from the drop-down menu in Excel. You can then apply any additional filters and choose the frequency that the report will refresh (so you can have your sales spreadsheet update automatically every week, day, or even hour.)
  4. Create an upload template from the Kloudio portal and you will be able to manually or automatically upload changes in your spreadsheet to your external data source.
    5. Advertisement

Video

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      • When opening a spreadsheet with your macro included, you may have to click Enable Content in a yellow banner at the top of the window before you can use the macro.
        Thanks
        Helpful 1 Not Helpful 0
      • Only download Excel plugins from Microsoft AppSource, unless you trust the third party provider.
        Thanks
        Helpful 0 Not Helpful 0
      • Macros can be used for anything from simple tasks (e.g., adding values or creating a chart) to complex ones (e.g., calculating your cell's values, creating a chart from the results, labeling the chart, and printing the result).
        Thanks
        Helpful 0 Not Helpful 0
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      Warnings

      • Macros can be used maliciously (e.g., to delete files on your computer). Don't run macros from untrustworthy sources.
        Thanks
        Helpful 1 Not Helpful 0
      • Macros will implement literally every step you make while recording. Make sure that you don't accidentally enter the incorrect value, open a program you don't want to use, or delete a file.
        Thanks
        Helpful 1 Not Helpful 0
      Advertisement

      You Might Also Like

      How to
      Group and Outline Excel Data: Easily Collapse Rows of Info
      How to
      Use Macros in Excel
      How to
      Set a Date and Time in Microsoft Excel
      How to
      Create a Custom Macro Button in Excel
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/show-the-developer-tab-e1192344-5e56-4d45-931b-e5fd9bea2d45
      2. ↑ https://support.microsoft.com/en-us/office/use-the-developer-tab-to-create-or-delete-a-macro-in-excel-for-mac-5bd3dfb9-39d7-496a-a812-1b5e8e81d96a
      3. ↑ https://support.microsoft.com/en-us/office/automate-tasks-with-the-macro-recorder-974ef220-f716-4e01-b015-3ea70e64937b
      4. ↑ https://support.microsoft.com/en-us/office/automate-tasks-with-the-macro-recorder-974ef220-f716-4e01-b015-3ea70e64937b
      5. ↑ https://support.microsoft.com/en-us/office/automate-tasks-with-the-macro-recorder-974ef220-f716-4e01-b015-3ea70e64937b
      6. ↑ https://support.microsoft.com/en-us/office/automate-tasks-with-the-macro-recorder-974ef220-f716-4e01-b015-3ea70e64937b

      About This Article

      Article Summary X

      1. Enable the Developer tab.
      2. Click the Developer tab.
      3. Click Record Macro .
      4. Create a shortcut key.
      5. Store the content in the current workbook.
      6. Click OK .
      7. Perform the steps you want to automate.
      8. Click Stop Recording .
      9. Use the shortcut key to run the same steps.

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 590,433 times.

      Is this article up to date?

      Advertisement