valiantys-logo
Back

Excelling in Confluence with Elements Spreadsheet and Table Filter and Charts

Valiantys Software is now Elements, a Valiantys company

We might have changed our name, but we’re the same team behind Elements. The new Elements brand and visual redesign reflect our identity as a software publisher and are a  reminder of the connectivity between our different apps. As part of the rebranding, our products have new names: nFeed is now Elements Connect, Exocet is now Elements Copy & Sync, Elements is now Elements Checklist, and Spreadsheets is now Elements Spreadsheet.

To read more, check out our announcement from March 20, 2019.

 

The following guest blog is written by Vadim Rutkevich, Business Analyst at StiltSoft


An advanced user of Microsoft Office can feel a bit frustrated once he moves to Atlassian Confluence. The conventional tools for table management are not present in the full extent in this team collaboration platform. Though the rich Atlassian Marketplace provides a variety of tools and apps to extend the native functionality with smart and robust capabilities.

If you’re already used to formulas, cell formatting, filtration of complex data, pivot tables, and dynamic charts, you cannot miss the two apps on the Atlassian Marketplace. They are Elements Spreadsheet from Elements and Table Filter and Charts from StiltSoft team. In this post we will show you how to work with them in junction and achieve greater results in table analysis in no time.

In this blog post, we will feature a use case that can be quickly and easily accomplished with Elements Spreadsheet and Table Filter and Charts apps.

So let’s get started!

The integration between Elements Spreadsheet and Table Filter and Charts is currently only available on the server version of the apps

Spreadsheets for Confluence

Out of the box Confluence does not support Excel spreadsheets. Of course, you can copy their content on the page, but that’s all. This way you can ping-pong your files between Confluence and Excel, but it is quite inconvenient and time-consuming.

The Elements team has designed the Elements Spreadsheet app to support Excel workbooks, preserving their current cell formatting along with using formulas for dynamic data calculation. The available cell formatting toolset is quite sufficient to highlight the specific data arrays and indicate threshold values among the ones within specification limits.

You can either create a new spreadsheet on the fly or import the existing Excel book with all its formulas and formatting into Confluence. Elements team did their best to preserve the refined Excel experience in Confluence and keep all the most widely used features. When dealing with data you may want to perform some calculations or analytical operations, and here you can take advantage of hundreds of formulas supported by the app.

Table Filter and Charts for Confluence

Table Filter and Charts is an app that can greatly simplify evaluation of table data and its further transformation into a clear and straightforward interpretation. So what capabilities does this solution provide?

First of all, you get a variety of options for filtering almost any kind of data that can be created and managed in Confluence, as follows:

Additionally, you get the capability to aggregate data in multidimensional pivot tables and visualise this summarised data with dynamic charts and graphs.

Tracking monthly product orders

Product orders from different office supply stores are tracked in a single Excel spreadsheet. At the end of the month, the collected data is passed to a sales manager who further imports it in Confluence through the Elements Spreadsheet app. The sales manager inserts the Elements Spreadsheet macro onto the page and selects the file to upload.

The resulting spreadsheet contains the following columns:

  • Order date – date when products were ordered for the store.
  • Store – number of the store where the products will be delivered.
  • Item – defines the product item which is ordered.
  • Regular Price – price for one unit.
  • Order Qty – the number of ordered items for the store.

 

The original file contains only raw data, though the sales manager needs to do some calculations with the Elements Spreadsheet app. First, he applies the currency format to the pricing column and then inserts a new column for calculating the wholesale price for supplies with over 50 units ordered. The manager can quickly copy the formula to the rest of cells by dragging it down.

 

 

Afterwards, the manager adds the Subtotal column and applies the formula with the IF condition for calculating subtotal for ordered items depending on their regular or discount price, as follows:

=IF(F2>=50;F2*E2;F2*D2)

 

 

The manager applies this formula to the rest of cells in the Subtotal column. Afterwards, he decides to evaluate revenue from each store, getting insights into how the orders are structured.

Filtering product orders

Initially, the sales manager wants to see product items ordered by each store. He places the Elements Spreadsheet macro within the Table Filter macro and adds a dropdown filter in the page view mode. The manager also uses the free text filter to view data about specific product items.

 

Aggregating revenue data for stores

For summarising revenue for each store over each month, the sales manager adds the Pivot Table macro and moves the current set of macros within it. Then he adds the thousand separator for better look of numerical values, adjusts the date format, and selects the appropriate period for data aggregation. After saving the macro and the page, he builds the pivot table.

He selects Order Date as a row label, Stores as a column label, Subtotal as a calculated column, and changes the operation to Sum. Below is the pivot table showing the revenue from all stores by month.

 

 

But it is quite difficult to understand whether there is an increase or decrease in revenue. So the sales manager decides to visualise the resulting table with the time line chart.

Visualising revenue from stores with a chart

The sales manager edits the page and inserts the Chart from Table macro which will wrap the currently used combination of macros. While editing the macro, he sets the chart type, defines the date format as ‘m/yy’ for correct operation with aggregated monthly data, and enables the trend lines for showing the prevailing direction of revenue from each store. Once the page is saved, the sales manager adds the columns with aggregated values and adjusts the dimensions of the chart. If needed he can temporarily disable display of information about specific stores.

 

 

As a result, he gets a time line chart showing the monthly revenue from each store along with the trend lines indicating the increase or decrease in the revenue over the six months. So now the sales manager knows which stores require more attention by looking at the decrease in the revenue.

Using in Junction

Combining both Elements Spreadsheet and Table Filter and Charts apps can give you a lot of benefits:

  • excellent management of spreadsheets in Confluence;
  • retention of the cell formatting and formulas during file import;
  • support for different arithmetic and analytical operations on cell data;
  • instant filtration of the spreadsheet and the ability to search for dynamic ranges of numerical or date values;
  • aggregation of raw and calculated data in multidimensional pivot tables;
  • visualisation of data with dynamic charts and graphs indicating data trends and threshold values.

If this seems like a useful use case, you can try Elements Spreadsheet and Table Filter and Charts apps on the Altassian Marketplace.

Try Elements Spreadsheet Try Table Filter and Charts

Cutted Triangle

Subscribe to Valiantys Newsletter

Registered request ! Subscribing... This is not an email An error occured

In accordance with our privacy policy, we are committed to respecting your personal data.

Contact us

Our Atlassian certified consultants will be happy to answer you.

Join us

We're building the next dream team - Are you in?

Follow us

We use cookies for the operation of our website. This is to improve its use, to personalize your experience, and to compile visitor statistics. By continuing to use this site, you consent to this policy. You can manage the settings and choose whether or not to accept certain cookies whilst browsing. For more information, see our privacy policy. Our privacy policy

Privacy settings

In order to facilitate your navigation and to provide you with the best possible service, we use cookies to improve the site to the needs of our visitors, particularly according to the number of visitors. For more information, please read our privacy policy. Our privacy policy

Recaptcha

Google reCAPTCHA is a system designed to distinguish humans from computers, so that bots are unable to maliciously fill out forms on behalf of a human being.

Analytics

Used to send data to Google Analytics about the visitor's device and behavior. Tracks the visitor across devices and marketing channels. Used by the social sharing platform AddThis to store the user's usage history of the AddThis sharing widget. Registers a unique ID that is used to generate statistical data on how the visitor uses the website.