Generate Advanced Excel Report using Pivot Charts

[rahulsharma]

Presenting the output is equally important as the accuracy of the output. Here is a mini-tutorial for presenting your output files in a better way using Excel and Refresh Pivot Table activity in Excel, a simple feature often overlooked by many while it comes to the implementation of real-life projects.

If you are using an Excel file as an output then you may make it more presentable if you show Dashboard including Graphs, Tiles, etc. That will be easier for business users to understand the outcome and analyze efficiently.

• We can integrate many BI tools such as Power BI, Kibana, Tableau with UiPath to create the advanced Visualization for the output, to understand and analyze quickly and easily but, we may also simply use Excel and its Pivot Table and Charts/Tiles.

• Also, we can create some amazing charts using Marketplace Package by @balupad14 and a few charts are also available in the Modern Excel Package. But if the marketplace packages are not feasible to use in your scenario or you need a more customized report along with the alignment, company-coded color, tiles, etc. then there are other simple ways to get it done, with the help of Excel alone.

• This snap shows the Output Report in normal excel vs with Pivot Charts, both created by UiPath Robot

We can create an Excel template for Output and have as many Pivot tables/Tiles as we wish to have, link it to the sheet where the tabular output data can be stored, and while generating the output, the data we paste in a normal excel can be pasted simply in the sheet of the Excel template which has the pivot tables and use the “Refresh Pivot Table” to generate the Graphs of the actual output data.

Below is a small example that shows the process in action:

The dummy report data is created and looks like this:

We have created an Excel Template, added 3 Pivot Tables and 2 Tiles, created their corresponding Pivot Chart.

We copy the template to generate the new output file, then just write the output data in the “Output Data” Sheet and use Refresh Pivot Table to Generate the Pivot Charts for the newly added data. It will look like this

The Output Excel report has Pivot that can even be used to filter data easily if the user wants

Also, we may save only the charts and tiles as PDF. This can be done by selecting the dedicated Print Area, we may even exclude the tables and just keep graphs for PDF Reports.

This way we can make use of the Excel Features and create a customized Dashboard/Report. Hope this adds some value to your reporting stage.

In my opinion, Excel is a powerful application and can do wonders if used properly, even for Data Visualization Solutions.

Do share your thoughts/feedbacks/queries.

Thank you, Happy Automation!

Download the files for reference:

Project file:
UiPath_Reporting.zip (221.2 KB)

Output Excel Report File:
Report.xlsx (44.3 KB)

Output PDF Report File:
Report.pdf (159.3 KB)

[makelifeeasywithtina]

Very nice and useful…

[rahulsharma]

Thank you so much!
Glad you liked it!

[alpharobot]

after checking the screenshot, was curious to know how we add those tiles in excel and update the values in it… it looks like Power BI card

Thanks for sharing the project file, it helped to understand it, amazed to know we can do all this by just using excel and basic activity by UiPath… thanks for this amazing tutorial @rahulsharma

[ken_infield]

This is google translate.
@rahulsharma
Thank you for the great solution.
Output Detail of OutputReportTemplate.xlsx
I tried to make it a table.
This eliminates the need to change the data range of the pivot table.
Please try this.
OutputReportTemplate.xlsx (37.2 KB)

[rahulsharma]

Thanks @ken_infield for giving time and considering to try out this mini-tutorial.

Yes → we can either have tables or the range as per requirement. That’s an excellent catch

The table will have the entire data selected, and if entire data is not required and we need only few columns to create pivot then we can go for range.

Appreciate your time and ideas!