Author:
Peter Berry
Date Of Creation:
16 February 2021
Update Date:
1 July 2024
Content
This wikiHow teaches you how to automatically report data in Microsoft Excel. This article also talks about how to query and generate reports from an external source (MySQL, Postgres, Oracle, etc.) right on the worksheet using an Excel plugin to link your spreadsheet to the source. that data.
As for the data stored in Excel spreadsheets, we will use macro commands to create and export reports into various file types with the touch of a button. Luckily, Excel has this feature built in, so you won't need to create the algorithm yourself.
Steps
Method 1 of 2: With external data (MySQL, Postgres, Oracle, etc.)
Download Excel plug-in by Kloudio from Microsoft AppSource. The above utility will allow you to create a continuous connection between the source or external database and your workbook set. This plugin also works with Google Sheets.
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 choose any security / encryption options if you are working with confidential or corporate data.
After you create a connection between the spreadsheet and your database, you'll be able to query and create reports from the external data right in Excel. Create a custom report from the Kloudio portal, then select the document in the right drop-down menu in Excel. Any filter and refresh rate can be applied to reports (business spreadsheets can be automatically updated weekly, daily, or even hourly).
In addition, you can import data into linked spreadsheets and update to an external data source. Create an upload template from the Kloudio portal, then you will be able to upload changes from the spreadsheet to an external data source manually or automatically. advertisement
Method 2 of 2: With data available on Excel
If the data to be reported is being stored, updated, and maintained in Excel, you can automate the reporting process with Macros. Macros are a built-in feature that allows us to automate complicated and repetitive tasks.
Open Excel. Double-click (or click with a Mac) in the white "X" Excel app on a green background, then click Blank Workbook (Blank spreadsheet) on the sample page.
- On a Mac computer, click File (File) and select New Blank Workbook (New blank spreadsheet) 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 it in Excel.
Enter the spreadsheet's data if needed. If you haven't added numbers or labels to the column for which you want to automate results, proceed before continuing.
Turn on the card Developer (Developers). By default, the tag Developer 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 (Optional)> Customize Ribbon (Customize the 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 ... (Customizable)> Ribbon & Toolbar (Toolbar & Ribbon) and check the "Developer" box in the "Main Tabs" list, then click Save.
Click Developer. This tab now shows the top of the Excel window. A toolbar will appear here.
Click Record Macro (Record Macro) in the toolbar. A window will pop up.
Enter a name for the macro command. In the "Macro name" text box, enter a name for the macro command. This will help you recognize the macro command later.
- For example, if you create a macro order to export a chart from existing data, you can name the order "Chart1" or something similar.
Create keyboard shortcuts for macros. Press ⇧ Shift simultaneously with another key (such as letters T) to create keyboard shortcuts for macro commands later.
- On a Mac, the keyboard shortcut would be ⌥ Option+⌘ Command and certain keys (eg ⌥ Option+⌘ Command+T).
Save the macro command to the current Excel document. Click the "Store macro in" drop-down box and select This Workbook (Gather this workbook) to make sure that the macro command will be 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.
Click OK at the bottom of the window. After the macro command settings are saved, you will be put into recording mode. Any action you perform from now until the recording stops will be saved.
Take the steps you want to automate. Excel tracks any clicks, keystrokes to format the options you enter, and adds them to the list of macros.
- For example, to select data and create a chart from it, you need to highlight the data, click Insert (Insert) at the top of the Excel window, select the chart type, click the chart format you want to use, and edit the chart if needed.
- If you want to use the macro command to add values from cells A1 come A12, click in the empty cell, enter = SUM (A1: A12) and press ↵ Enter.
Click Stop Recording (Stop recording). Options are in the toolbar of the card Developer. The program stops recording and saves all operations you performed during recording as a macro command.
Save the Excel workbook as a macro trigger file. Click File, choose Save As (Save As) and change the file format to xlsm instead of xls. Then you can enter a file name, choose a save location, and click Save (Save).
- If you do not do this, the macro command will not be saved as part of the spreadsheet, which means that users on another computer will not be able to use the macro if you send the workbook to them.
Execute the macro command. Press the key combination you created as part of a macro command to proceed. The spreadsheet automatically performs the steps in the macro command.
- You can also execute a macro command by clicking Macros in the card Developer, select the name of the macro and click Tremor (Enforcement).
Advice
- You should only get the Excel plug-in from Microsoft AppSource or a trusted third-party provider.
- Macros can be used for anything from simple (for example, adding values or creating charts) to complex (e.g. calculating the value of cells, creating charts from results, labeling charts, and print results).
- When opening a spreadsheet with macros, you may need to click Enable Content (Content Enable) in the yellow banner at the top of the front window can macro use be used.
Warning
- The macro will take a single step while recording.Make sure you haven't accidentally entered the wrong value, opened any unnecessary programs, or deleted something.
- Macros can be used for malicious purposes (such as deleting data on your computer). Do not execute macro commands from untrusted sources.
