How to Automatically Report on Excel

Today's TipsMake will guide you how to automatically report data on Microsoft Excel. This article also talks about how to query and create reports from external sources (MySQL, Postgres, Oracle, etc.) right on your spreadsheet using the Excel plug-in to link your spreadsheet to the source. That data. As for data stored on Excel spreadsheets, we will use macro commands to create and export reports into many different file types with just one keystroke. Luckily, Excel has this feature built in, so you won't need to create your own algorithm.

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

How to Automatically Report on Excel Picture 1

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 collection. This plug-in also works with Google Sheets.

How to Automatically Report on Excel Picture 2

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

How to Automatically Report on Excel Picture 3

After creating the connection between the spreadsheet and the database, you'll be able to query and create reports from external data right in Excel. Create custom reports 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 automatically updated weekly, daily, even hourly).

How to Automatically Report on Excel Picture 4

Additionally, you can 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 your spreadsheet to an external data source either manually or automatically.

With data available in Excel

How to Automatically Report on Excel Picture 5

If the data to be reported 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.

How to Automatically Report on Excel Picture 6

Open Excel. Double-click (or click on a Mac) the Excel app that looks like 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 from 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.

How to Automatically Report on Excel Picture 7

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

How to Automatically Report on Excel Picture 8

Enable the Developer tab . By default, the Developer tab is not displayed at 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 at the bottom left of the window (you may need to scroll down first), then Click OK .

On Mac — Click Excel > Preferences. > Ribbon & Toolbar and check the "Developer" box in the "Main Tabs" list, then click Save .

How to Automatically Report on Excel Picture 9

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

How to Automatically Report on Excel Picture 10

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

How to Automatically Report on Excel Picture 11

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 macro commands later.

For example, if you create a macro command to export a chart from existing data, you might name the command "Chart1" or something similar.

How to Automatically Report on Excel Picture 12

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

On a Mac computer, the shortcut key combination will be ⌥ Option+ ⌘ Commandand some key (for example ⌥ Option+ ⌘ Command+ T).

How to Automatically Report on Excel Picture 13

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 a specific format for the macro command to be saved.

How to Automatically Report on Excel Picture 14

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

How to Automatically Report on Excel Picture 15

Proceed with the steps you want to automate. Excel tracks every mouse click and 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'll 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 a macro command to add values from cells A1 to A12 , click the empty cell, type =SUM(A1:A12), and press ↵ Enter.

How to Automatically Report on Excel Picture 16

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

How to Automatically Report on Excel Picture 17

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

If you don't do this, the macro command won't be saved as part of the workbook, which means users on other computers won't be able to use the macro if you send them the workbook.

How to Automatically Report on Excel Picture 18

Execute macro commands. 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 .