How to Automate Reports in Excel

This wikiHow teaches you how to automatically report data on Microsoft Excel. This article also talks about how to query and generate reports from an external source (MySQL, Postgres, Oracle, etc) right on the spreadsheet using the Excel plug-in to link your spreadsheet to the source. that data.

As for the data stored on the Excel spreadsheet, we will use macro commands to create and export reports into different file types with just one key press. Fortunately, Excel has this built in, so you won’t need to create the algorithm yourself.

Table of Contents

With external data (MySQL, Postgres, Oracle, etc.)

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/4c/Automate-Reports-in-Excel-Step-1-Version-3.jpg/v4-728px-Automate-Reports- in-Excel-Step-1-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/4c/Automate-Reports-in-Excel-Step-1- Version-3.jpg/v4-728px-Automate-Reports-in-Excel-Step-1-Version-3.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

Get Kloudio’s Excel plug-in from Microsoft AppSource. The above utility will allow you to create a persistent connection between an external source or database and your workbook. This plug-in also works with Google Sheets.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/87/Automate-Reports-in-Excel-Step-2-Version-3.jpg/v4-728px-Automate-Reports- in-Excel-Step-2-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/87/Automate-Reports-in-Excel-Step-2- Version-3.jpg/v4-728px-Automate-Reports-in-Excel-Step-2-Version-3.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

Make a connection between your spreadsheet and an external data source by clicking the + sign on the Kloudio portal. Enter the details of the database (database type, credentials) and select any security/encryption options if you are working with confidential or corporate data.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/b6/Automate-Reports-in-Excel-Step-3-Version-3.jpg/v4-728px-Automate-Reports- in-Excel-Step-3-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/b6/Automate-Reports-in-Excel-Step-3- Version-3.jpg/v4-728px-Automate-Reports-in-Excel-Step-3-Version-3.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

After creating a connection between the spreadsheet and the database, you’ll be able to query and generate reports from external data right in Excel. Create a custom report from the Kloudio portal, then select the document in the drop-down menu right in Excel. You can apply any filter and refresh frequency to the report (business spreadsheets can be updated automatically weekly, daily or even hourly).

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/7/74/Automate-Reports-in-Excel-Step-4-Version-3.jpg/v4-728px-Automate-Reports- in-Excel-Step-4-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/7/74/Automate-Reports-in-Excel-Step-4- Version-3.jpg/v4-728px-Automate-Reports-in-Excel-Step-4-Version-3.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

You can also import data into a connected spreadsheet and update it to an external data source. Create an upload template from the Kloudio portal, then you’ll be able to upload changes from the spreadsheet to an external data source manually or automatically.

With data available on Excel

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/9b/Automate-Reports-in-Excel-Step-5-Version-3.jpg/v4-728px-Automate-Reports- in-Excel-Step-5-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/9/9b/Automate-Reports-in-Excel-Step-5- Version-3.jpg/v4-728px-Automate-Reports-in-Excel-Step-5-Version-3.jpg”,”smallWidth”:460,”smallHeight”:259,”bigWidth”:728,”bigHeight” :410,”licensing”:”<div class=”mw-parser-output”></div>”}

If the data to report is being stored, updated and maintained in Excel, you can automate the reporting process with Macros. Macros are built-in features that allow us to automate complex and repetitive tasks.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/e/e7/Automate-Reports-in-Excel-Step-6-Version-3.jpg/v4-728px-Automate-Reports- in-Excel-Step-6-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/e7/Automate-Reports-in-Excel-Step-6- Version-3.jpg/v4-728px-Automate-Reports-in-Excel-Step-6-Version-3.jpg”,”smallWidth”:460,”smallHeight”:259,”bigWidth”:728,”bigHeight” :410,”licensing”:”<div class=”mw-parser-output”></div>”}

Open Excel. Double-click (or click with a Mac) the Excel application a white “X” on a green background, then click Blank Workbook on the template page.

On a Mac, click File and select New Blank Workbook in the drop-down menu that appears.
If you already have an Excel report that needs to be automated, double-click the file to open the document in Excel.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/6e/Automate-Reports-in-Excel-Step-7-Version-2.jpg/v4-728px-Automate-Reports- in-Excel-Step-7-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/6e/Automate-Reports-in-Excel-Step-7- Version-2.jpg/v4-728px-Automate-Reports-in-Excel-Step-7-Version-2.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

Enter spreadsheet data if necessary. If you haven’t added a number or label to the column for which you want to automate the results, proceed before continuing.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/e/e6/Automate-Reports-in-Excel-Step-8-Version-3.jpg/v4-728px-Automate-Reports- in-Excel-Step-8-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/e6/Automate-Reports-in-Excel-Step-8- Version-3.jpg/v4-728px-Automate-Reports-in-Excel-Step-8-Version-3.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

Enable the Developer tab. By default, the Developer tab does not display the top of the Excel window. Depending on your operating system, you can enable this card as follows:

On Windows — Click File > Options > Customize Ribbon on the left side of the window, check the “Developer” box in the bottom left of the window (you may need to scroll down first) then click OK . ^{[1] X Research Source}
Mac — Click Excel > Preferences… (Customize) > Ribbon & Topbar and check the “Developer” box in the “Main Tabs” list, then click Save . ^{[2] X Research Source}

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/cc/Automate-Reports-in-Excel-Step-9-Version-3.jpg/v4-728px-Automate-Reports- in-Excel-Step-9-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/c/cc/Automate-Reports-in-Excel-Step-9- Version-3.jpg/v4-728px-Automate-Reports-in-Excel-Step-9-Version-3.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Developer . This tab is now displayed at the top of the Excel window. A toolbar will appear here.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/cf/Automate-Reports-in-Excel-Step-10-Version-2.jpg/v4-728px-Automate-Reports- in-Excel-Step-10-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/c/cf/Automate-Reports-in-Excel-Step-10- Version-2.jpg/v4-728px-Automate-Reports-in-Excel-Step-10-Version-2.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Record Macro in the toolbar. A window will pop up.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/d4/Automate-Reports-in-Excel-Step-11-Version-3.jpg/v4-728px-Automate-Reports- in-Excel-Step-11-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/d4/Automate-Reports-in-Excel-Step-11- Version-3.jpg/v4-728px-Automate-Reports-in-Excel-Step-11-Version-3.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

Enter a name for the macro command. In the “Macro name” text box, enter a name for the macro command. This will help you identify the macro command later.

For example, if you create a macro command to export a chart from existing data, you can name the command “Chart1” or similar.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/5/5f/Automate-Reports-in-Excel-Step-12-Version-3.jpg/v4-728px-Automate-Reports- in-Excel-Step-12-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/5/5f/Automate-Reports-in-Excel-Step-12- Version-3.jpg/v4-728px-Automate-Reports-in-Excel-Step-12-Version-3.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

Create keyboard shortcuts for macro commands. Press the ⇧ Shift key at the same time as another key (such as the letter T ) to create a shortcut combination for the macro command later.

On a Mac, the keyboard shortcut would be ⌥ Option + ⌘ Command and something (eg ⌥ Option + ⌘ Command + T ).

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/6f/Automate-Reports-in-Excel-Step-13-Version-3.jpg/v4-728px-Automate-Reports- in-Excel-Step-13-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/6f/Automate-Reports-in-Excel-Step-13- Version-3.jpg/v4-728px-Automate-Reports-in-Excel-Step-13-Version-3.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

Save the macro command to the current Excel document. Click the “Store macro in” drop-down box and select This Workbook to make sure the macro command is available to anyone using the current workbook.

You will need to save the Excel file in its own format for the macro command to be saved.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/14/Automate-Reports-in-Excel-Step-14-Version-3.jpg/v4-728px-Automate-Reports- in-Excel-Step-14-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/14/Automate-Reports-in-Excel-Step-14- Version-3.jpg/v4-728px-Automate-Reports-in-Excel-Step-14-Version-3.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

Click OK at the bottom of the window. After the macro command’s settings are saved, you will be put into recording mode. Everything you do from now until the recording stops will be saved.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/5/57/Automate-Reports-in-Excel-Step-15-Version-2.jpg/v4-728px-Automate-Reports- in-Excel-Step-15-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/5/57/Automate-Reports-in-Excel-Step-15- Version-2.jpg/v4-728px-Automate-Reports-in-Excel-Step-15-Version-2.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

Proceed to the steps that you want to automate. Excel tracks every click, keystroke to format the options you enter and add them to the macro list.

For example, to select data and create a chart from it, you’d highlight the data, click Insert at the top of the Excel window, choose a chart type, and click the chart format you want to use. Use and edit the chart if necessary.
If you want to use the macro command to add a value from cells A1 to A12 , click in a blank cell, type =SUM(A1:A12) and press ↵ Enter .

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/0/07/Automate-Reports-in-Excel-Step-16-Version-2.jpg/v4-728px-Automate-Reports- in-Excel-Step-16-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/0/07/Automate-Reports-in-Excel-Step-16- Version-2.jpg/v4-728px-Automate-Reports-in-Excel-Step-16-Version-2.jpg”,”smallWidth”:460,”smallHeight”:259,”bigWidth”:728,”bigHeight” :410,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Stop Recording . The option is in the toolbar of the Developer tab. The program will stop recording and save all the operations you have done during the recording as a macro command.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/7/7d/Automate-Reports-in-Excel-Step-17-Version-2.jpg/v4-728px-Automate-Reports- in-Excel-Step-17-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/7/7d/Automate-Reports-in-Excel-Step-17- Version-2.jpg/v4-728px-Automate-Reports-in-Excel-Step-17-Version-2.jpg”,”smallWidth”:460,”smallHeight”:258,”bigWidth”:728,”bigHeight” :409,”licensing”:”<div class=”mw-parser-output”></div>”}

Save the Excel workbook as a macro-enabled file. Click File , choose Save As and change the file format to xlsm instead of xls . You can then enter a filename, choose a save location, and then click Save .

If you do not do this, the macro command will not be saved as part of the workbook, which means that users on other computers will not be able to use the macro if you send the workbook to them.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/96/Automate-Reports-in-Excel-Step-18-Version-2.jpg/v4-728px-Automate-Reports- in-Excel-Step-18-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/9/96/Automate-Reports-in-Excel-Step-18- Version-2.jpg/v4-728px-Automate-Reports-in-Excel-Step-18-Version-2.jpg”,”smallWidth”:460,”smallHeight”:259,”bigWidth”:728,”bigHeight” :410,”licensing”:”<div class=”mw-parser-output”></div>”}

Execute the macro command. Press the key combination you created as part of the macro command to proceed. The spreadsheet will automatically perform the steps in the macro command.

You can also execute a macro command by clicking Macros in the Developer tab, selecting the name of the macro, and clicking Run .

Advice

You should only get the Excel plug-in from Microsoft AppSource or a third-party vendor you trust.
Macros can be used for anything from simple (e.g., adding values or creating a chart) to complex (e.g. calculating cell values, generating charts from results, labeling charts, and print results).
When you open a spreadsheet with a macro, you may need to click Enable Content in the yellow banner at the top of the first window to use the macro.

Warning

The macro will perform without missing a step during your recording. Make sure you don’t accidentally enter the wrong value, open unnecessary programs, or delete certain files.
Macros can be used for malicious purposes (such as deleting data on your computer). Do not execute macro commands from untrusted sources.

Steps

With external data (MySQL, Postgres, Oracle, etc.)

With data available on Excel

Advice

Warning

Related Posts