This guide describes how to automate data reporting on Microsoft Excel. For external data, you will learn how to create queries and reports from any data source (MySQL, Postgres, Oracle, etc.), directly within the spreadsheet, using Excel plugins that allow you to link the program to external sources.
For data already in the Excel spreadsheet, you'll use macros to create reports and export them to various formats with a single keystroke. Fortunately, Excel offers a built-in macro recording tool, so you don't have to write code yourself.
Steps
Method 1 of 2: For Data Already Exists in Excel
Step 1. If the data you need for the report is already stored, updated and maintained in Excel, you can automate the operations with macros
These tools are integrated functions that allow you to automate complex and repetitive tasks.
Step 2. Open Excel
Double-click (or once if you're using a Mac) on the Excel app icon, which looks like a white "X" on a green background, then click Blank workbook on the templates page.
- On Mac, if necessary, click on File, then on New blank workbook in the drop-down menu that appears.
- If you already have an Excel report that you want to automate, you will need to double-click the report file in order to open it in Excel.
Step 3. If necessary, enter the data in the spreadsheet
If you haven't added the column labels and numbers whose results you want to automate, do so now, before continuing.
Step 4. Activate the Developer tab
By default, the Development it is not shown at the top of the Excel window. You can activate it with the following steps, which vary according to your operating system:
- Windows - click on File, then on Options, on Customize ribbon on the left side of the window, check the "Development" box in the lower right part of the window (scroll down if necessary), then click on OK;
- Mac - click on Excel, then on Preferences…, on Ribbon and toolbar, check the "Development" box in the "Main cards" list, then click on Save.
Step 5. Click on Development
This tab should be at the top of the Excel window. Press on it to open a toolbar at the top.
Step 6. Click Record Macro
You will see this button in the toolbar. Press it to open a window.
Step 7. Enter the macro name
In the "Macro name" text field, type the name you want to give the macro. This way, you will be able to recognize it later.
For example, if you are creating a macro that generates a chart based on available data, you can name it "Chart1" or something similar
Step 8. Create a key shortcut for the macro
Press the Shift key together with another key (for example T) to create the keyboard shortcut. You will use this key combination to run the macro later.
On Mac, the shortcut will be ⌥ Option + ⌘ Command plus the key you have chosen (for example ⌥ Option + ⌘ Command + T)
Step 9. Save the macro within the current Excel document
Click on the "Save macro in" drop-down menu, then click on This workbook, so you can make sure that the macro is available to all users who will open the workbook.
To save the macro, you will need to assign a special format to the Excel file
Step 10. Click OK
This button is located at the bottom of the window. Press it to save the macro settings and enter record mode. All the steps you take from now on will be recorded, until you stop recording.
Step 11. Perform the steps you wish to automate
Excel will record every click, key pressed, and formatting option you enter, adding them to the macro list.
- For example, to select data and create a chart, you will need to select the data, click insert at the top of the Excel window, click on the type of chart you want to use and modify it according to your needs.
- If you want to use the macro to sum the cell values between A1 And A12, you will need to click on an empty cell, type = SUM (A1: A12), then press Enter.
Step 12. Click Pause Registration
You will find this button in the tab Development of the toolbar. Press it to stop recording and save all the steps you've taken as a single macro.
Step 13. Save the Excel sheet as a macro-enabled file
Click on File, then on Save with name, finally change the file format from xls to xlsm. At this point, you can enter the file name, select the save path and click on Save.
If you don't, the macro won't be saved as part of the spreadsheet. As a result, users on computers other than yours will not be able to use the macro when they receive the workbook
Step 14. Run the macro
To do this, press the key combination you chose when creating the macro. You should notice that the spreadsheet completes automatically, following the steps in your macro.
You can also run a macro by clicking on Macro in the card Development and selecting the name of the macro, finally clicking on Run.
Method 2 of 2: For External Data (MySQL, Postgres, Oracle, etc.)
Step 1. Download the Kloudio Excel plugin from Microsoft AppSource
This add-on allows you to create a permanent link between an external database or data source and a workbook. It also works on Google Sheets.
Step 2. Create a link between your workbook and the external data source by clicking on the + button on the Kloudio portal
Type in your database details (type, credentials) and select security / encryption options if you are working with confidential or corporate data.
Step 3. Once you have created a link between your workbook and the database, you can run queries and create reports using external data, without leaving Excel
Create custom reports on the Kloudio portal, then select them from the Excel drop-down menu. At that point, you can apply any additional filters you like and choose the frequency of the report updates (this way, you can have sales sheets that update automatically every week, day or hour).
Step 4. In addition, you can also insert data into the linked workbook and update the external data source
Create an upload template from the Kloudio portal and you will be able to automatically or manually upload the changes you make within the workbook, directly to the external data source.
Advice
- Download Excel plugins only from Microsoft AppSource or another third-party provider that you trust.
- You can use macros for all kinds of tasks, from simple operations (for example adding values or creating graphs) to more complex ones (for example, calculating cell values, creating a graph based on results, assigning labels to a graph and print the results).
- When you open a spreadsheet that contains a macro, you will need to click on before you can use it Enable content in the yellow bar at the top of the window.
Warnings
- Macros can be used for dangerous purposes (for example, to delete files on your computer). Do not run macros from untrusted sources.
- Macros will literally go through all the steps you take while recording. Make sure you don't inadvertently enter an incorrect value, don't open a program you don't want to use, and don't delete a file.
