Pine BI | Create

How to create a chart with Pine BI

There are two ways to create charts with Pine BI: you can either add one based on your data or use a template, which you can then modify.

Whichever method you choose, a user form will be displayed to guide you in customizing the chart. This form allows you to tailor the chart’s features, appearance, colors, and more. Specific options are available for different types of charts to ensure maximum flexibility.

If you decide to create a chart based on your data, you’ll need to specify the data ranges in the user form. If you choose to use a template, you’ll only need to specify the starting cell where the template data will be added.

Both methods of creating charts with Pine BI result in dynamic, editable, and shareable visualizations. It’s up to you to decide which approach best suits your specific situation.

To demonstrate how easy it is to create advanced charts with Pine BI, we’ll use an example featuring one of the Advanced Column Variance Charts.

1. Raw Data

We’re going to add a chart based on the example data shown on the left.

It’s important to note that the order of the columns doesn’t need to be the same. The Actual data can appear after the Target, and the Months column can be in the middle. In fact, the Target data can even be located on a separate sheet from the Actual results, and the chart will still be created without issues.

2. Select the Chart

Select the chart you want to add. In this case it is going to be the Advanced Column Variance Chart 1.

3. Review the User Form options

The userform for the selected visualization will appear, allowing users to customize the visualization to best fit their needs.

At the top of the userform, a short description of the visualization is provided. By clicking on the More Info & Help. link, the user will be directed to a dedicated webpage on our website with detailed information and guidelines for the specific chart.

On the right side, an image of the selected chart is displayed. This image is dynamic and updates based on selections made in the menu. Please note that not all dropdown menus will affect the image - for example, in this case, only the Chart Type option will switch between displaying the chart as a bar or column chart (vertical or horizontal).

In the Chart Options dropdown menus, the main controls for the visualization are provided:

Create: Choose whether to create a chart based on data or as a template. If As Template is selected, irrelevant options will disappear, leaving only the ones applicable to this selection.
Chart Type: Decide whether to create this visualization as a column or bar chart.
Variance Type: Select whether to display variance as actual values or percentages.
Marker Type: Choose the desired marker type.

Next is the Select Data area. Here, users specify the data ranges for the visualization or indicate where the template data should be added. In our example, we need to specify the Actual Data Range and the Target Data Range. There’s also an option to specify the Categories Data Range, but this is not required.

Finally, we have the Chart Colors area, which allows users to customize chart colors before creating the visualization to save time. First, users can select the desired Color Theme.

There are six themes to choose from, plus the option to select colors directly from the Active Workbook Palette. This makes it easy to align with corporate branding if you have a custom color palette installed in Excel.

The color preview updates based on the selected Color Theme. If users want to mix colors from different themes, they can deselect the Sync Color Box With Theme checkbox. This enables changes to the Color Theme while retaining previously selected colors.

By default, the following colors are typically available: Blue, Green, Yellow, Amber, Red, Grey, and Black. Of course, users can specify any custom colors they wish.

Finally, users have the option to set the background color of the chart. By default, it is set to white, but it can be changed to light grey, transparent, or any custom color as needed.

Please remember that all Pine BI visualizations are fully editable, and their colors can be adjusted even after creation.

4. Customize the visualization & select data

Here are the selections we’ve made:

Create: We’re creating a visualization based on existing data, not as a template.
Chart Type: We’ve chosen to create the chart as a column chart.
Variance Type: We’ve specified that the variance should be displayed as a percentage, not as actual values.
Marker Type: We want the marker to be shown as a diamond.
Select Data: The required data ranges have been selected from our worksheet.
Chart Colors: We’ve chosen Color Theme 2 and selected a Light Grey background color for the chart.

Now that we’re satisfied with our selections, we can click Create.

6. The Chart has been created!

Success!

The chart has been successfully generated, and all the necessary additional calculations to make it work have been automatically created at the end of the data range.

To ensure the chart remains dynamic and updates whenever the data changes, the cells in the new columns contain formulas rather than absolute values. This approach allows all Pine BI charts to be truly dynamic and ensures they can be easily shared in PowerPoint or with stakeholders who don’t have Pine BI 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, as we can see in this example with an Advanced Column Markers Chart.

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 with just a single click!

This action will create an example dataset and automatically add all the necessary calculations for the chart. You can then modify the data range to suit your specific needs. Templates are especially useful for understanding the required data structure and dataset for a particular chart.

To add a Chart Template, simply select As Template from the Create dropdown menu for your chosen visualization - for example, the Dumbbell Chart in the example above. Next, specify the cell where the template data will be added, adjust the visualization options to your preference, and click Create.

And that’s it! A chart will be generated, complete with the underlying data table. This process works seamlessly 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.