How to Automate Reporting in Excel

Today, Mytour will guide you through automating data reporting in Microsoft Excel. This article also explains how to query and generate reports from external data sources like MySQL, Postgres, Oracle, and others directly within your spreadsheet (worksheet) by using an Excel plugin to link your sheet with those external data sources.

Download the Kloudio Excel Plugin from Microsoft AppSource. This tool allows you to establish a continuous connection between your external data source or database and your workbook. The plugin also works with Google Sheets.

To establish a connection between your spreadsheet and an external data source, click the + icon on the Kloudio portal. Enter the database details (database type, login information) and select any security/encryption options if you are working with sensitive or enterprise data.

Once the connection is made between your spreadsheet and the database, you can query and generate reports from the external data directly in Excel. Create custom reports from the Kloudio portal, then select the document from the dropdown menu in Excel. You can apply filters and set refresh frequencies for the reports (business spreadsheets can be updated automatically weekly, daily, or even hourly).

Additionally, you can input data into the connected spreadsheet and upload it to the external data source. Create an upload template from the Kloudio portal, and then you will be able to upload changes from the spreadsheet to the external data source manually or automatically.

If the data you need to report is stored, updated, and maintained within Excel, you can automate the reporting process using Macros. Macros are an integrated feature that allows you to automate complex and repetitive tasks.

Open Excel. Double-click (or click with a Mac) on the Excel app icon, a white "X" on a green background. Then, click on Blank Workbook on the template page.

• If you are on a Mac, click on File and choose New Blank Workbook from the dropdown menu.
• If you already have an existing Excel report to automate, double-click on the file to open it in Excel.

Enter data into the spreadsheet if needed. If you haven't yet added numbers or labels to the columns you wish to automate, do so before proceeding.

Activate the Developer tab. By default, the Developer tab is not visible in the Excel window. Depending on your operating system, you can enable this tab as follows:

• For Windows — Click File > Options > Customize Ribbon on the left side of the window, check the "Developer" box at the bottom-left of the window (you might need to scroll down), and click OK.
• For Mac — Click Excel > Preferences... > Ribbon & Toolbar, and check the "Developer" box under "Main Tabs", then click Save.

Click on the Developer. Now that the tab is visible, a new toolbar will appear at the top of your Excel window.

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

Enter a name for your macro command. In the "Macro name" text box, type a name for your macro command. This will help you recognize the macro later.

• For instance, if you are creating a macro to generate a chart from existing data, you might name it something like "Chart1" or something similar.

Create a shortcut key for the macro command. Press the ⇧ Shift key together with another key (for example, the letter T) to assign a keyboard shortcut to your macro command.

• On Mac computers, the shortcut will be ⌥ Option+⌘ Command and any other key (for example, ⌥ Option+⌘ Command+T).

Save the macro command in your current Excel workbook. Click on the "Store macro in" dropdown and select This Workbook to ensure the macro command will be available for anyone using the current workbook.

• Make sure to save the Excel file in a format that supports macros for the command to be saved.

Click on the OK button at the bottom of the window. After you save the macro settings, you will enter the recording mode. Every action you perform from this point forward until you stop the recording will be saved.

Perform the actions you want to automate. Excel will track every mouse click and key press you make, recording them as part of the macro command.

• For example, to select data and create a chart, highlight the data, click on Insert at the top of the Excel window, choose a chart type, click on the chart formatting option you want to use, and edit the chart as needed.
• If you wish to use the macro to sum values from cells A1 through A12, select an empty cell, type =SUM(A1:A12) and press ↵ Enter.

Click on Stop Recording to halt the recording. This option is located in the Developer tab of the toolbar. The program will stop recording and save all actions performed during the recording as a macro command.

Save the Excel spreadsheet as a macro-enabled file. Click File, select Save As, and change the file format to xlsm instead of xls. Afterward, you can name the file, choose a save location, and click Save to finish the process.

• If you don’t do this, the macro command won’t be saved with the spreadsheet, meaning other users on different computers won’t be able to use the macro if you send them the workbook.

Execute the macro command. Press the key combination you set as part of the macro command to run it. The spreadsheet will automatically carry out the steps outlined in the macro.

• You can also run the macro by clicking Macros in the Developer tab, selecting the macro name, and then clicking Run.

• Only download Excel add-ins from trusted sources like Microsoft AppSource or reputable third-party providers.
• Macros can be used for tasks ranging from simple (such as entering values or creating charts) to complex (like calculating cell values, generating charts from results, labeling charts, and printing the outcomes).
• When opening a workbook with macros, you might need to click on Enable Content in the yellow banner at the top of the window before you can use the macro.

• The macro will perform every step exactly as recorded. Make sure you don’t accidentally enter incorrect values, open unnecessary programs, or delete important files.
• Macros can be used maliciously (such as deleting files from your computer). Do not run macros from untrusted sources.