How to create a chart with Pine BI
As already mentioned, there are two ways of creating charts with Pine BI - add one based on your data, or add a template, which you can modify.
​
If going the first way, you'll be asked to select the data, based on which the chart will be created. If needed, Pine BI will automatically add all necessary calculations for the chart at the end of the data range - sometimes there will be none, but in some cases an additional 12 columns of formulas will be needed for the more advanced charts. However, you won't have to worry about that, as all the formulas and calculations are taken care of automatically.
​
If you choose to add a chart template, an example data range will be generated with all necessary calculations in place. You'll then be able to modify the data range as you need.
​
Both ways of creating a chart with Pine BI will create a dynamic, editable and shareable visualization. It is up to you to decide which method is best for a specific situation.
​
To illustrate how easy it is to add an advanced chart wit Pine BI, we're going to show an example with the Actual Vs. Target Dynamic Color Chart
1. Raw Data
We're going to add a chart based on the example data we have on the left.
​
It is important to note, that the order of the columns doesn't have to be the same. The Actual data can be after the Target and the Months can be in the middle. The Target can even be in a separate sheet than the Actual results, and the Chart will still be created without any issues.
2. Select the Chart
Select the chart you want to add. In this case it is going to be the Dynamic Color AvT chart.
Direction "Up" means that we want to have the green columns if the Target has been reached. Otherwise the columns should be red.
If we chose "Down" the columns over the Target will be red.
3. Select the range with the Actual data
A prompt menu will pop up, asking you to select the range with the Actual results.
​
Select the range, than click OK.
4. Select the range with the Target
Next, a prompt menu will pop up, asking you to select the range with the Target.
​
Select the range, than click OK.
5. Select the range with the Categories
A prompt menu will pop up, asking you to select the range with the Categories.
​
This step is optional, as the Categories are not necessarily required for the chart.
​
Select the range, than click OK.
6. The Chart has been created!
Success!
The chart has been generated and a brand new column called "PBI Over Target" has been automatically created at the end of the data range. This column contains the calculations necessary for the chart to work.
​
In order for the chart to stay dynamic and to update whenever the data is changed, the cells in the new column are not absolute values, but formulas. This allows all Pine BI charts to be truly dynamic and ensures that they can be shared in PowerPoint or with stakeholders, who don't have the Add-In installed.
​
But what if there is data already after the Target?
No worries!
​
Pine BI will try to automatically find the first available range where the calculations will be added.
​
Your data is safe and will never be overwritten!
In some cases the automatic detection will not work.
Then, the required range size for the calculations will be highlighted and you will be asked to select a cell from where the calculations will be automatically generated.
The calculations don't have to be next to the other data ranges. They can be anywhere in the Workbook!
What if my data range is horizontal and not vertical?
Pine BI can create charts from both vertical and horizontal data ranges.
​
All of the above mentioned features, like the automatic range detection will work.
What if my data is in a Pivot Table?
The data set doesn't have to be a simple data range. Pine BI can create a chart from Tables and from Pivot Tables as well.
​
In this case, the additional calculations will be added outside the Pivot Table and will not be a part of it.
​
This would also mean, that the generated chart will not be a Pivot Chart, but a normal one instead.
​
Still, if the values within the Pivot Table change, the chart will be updated as well!
What about the Templates?
As mentioned, you can add a chart template in a single click!
​
This will create an example data set and will automatically add all necessary calculations for the chart.
​
You will then be able to modify the data range, so that it fits your needs.
​
Templates are very useful to review the required data structure and data set for a specific chart.
​
To add a Chart Template, simply select a blank cell with enough free space and click on the button.
As simple as that, a chart is generated, along with the underlying data table.
This works for all charts, no matter how complex they are.
In case there is not enough free space to add the Chart Template, the requested range size will be highlighted and a message box will let you know that the chart couldn't be generated.
​
Your data will not be overwritten!
How to edit a chart?
Pine BI is built on entirely native Excel features. Therefore, you can edit all charts just like the default Excel ones.
​
How to share a chart?
Users without Pine BI will still be able to see and edit the charts you've created. Charts can be easily copied to PowerPoint and Word.
​