5 Top Tips for working with Planning Analytics for Excel

by Adam Bakhtiar

Working with IBM Planning Analytics for Excel day in and day out means that we know lots of tips and tricks for getting the best out of the software. The Excel add in for Planning Analytics is a widely used tool with many functions for data analysis, so in this short blog, I will outline our top 5 tips for IBM Planning Analytics for Excel to make your life easier.

This article will focus on the functions within the Cube viewer in Planning Analytics for Excel, so make sure you are in the right place when testing these out for yourself.

1 – Play in the Sandbox

A sandbox in Planning Analytics for Excel is a copy of your database. You can use sandboxes to create multiple scenarios without impacting your base data. Sandboxes let you try out different changes to the data before making those changes public to other users and before committing those changes to the base data. Sandboxes are only visible to you until you commit them.

To create a sandbox, click on the sandbox icon:

Name the sandbox, and then choose whether you want to create a sandbox from the base data or to create a copy of an existing sandbox, and then click OK.

Now are you in the sandbox! You can move between different sandboxes by selecting them from the drop-down list. When you are satisfied with the data in a sandbox and you want to commit it to the base, click Commit data.

You can delete a sandbox by clicking Sandbox, Delete Sandbox, select the Sandbox that you want to remove, and tap Delete.

2 – Show Values as Percentages

The ‘Show cell values as…’ can be useful if you want to see cell values displayed as a percentage of a total.

You can show cell values as a percentage by right-clicking in a cell, and select ‘Show cell value as…’

You can also display values as:

% Row Total displays all the values in each row as a percentage of the total for the row.

% Column Total displays all the values in each column as a percentage of the total for the column.

% Grand Total displays values as a percentage of the total of all the values or data points in the report.

Advanced – % Parent Row Total displays the values in each row as a percentage of the parent.

Advanced – % Parent Column displays the values in each column as a percentage of the parent

To return to the actual cell values, in the Show cell value window, select As-is.

3 – Hide Columns and Rows

You can hide columns and rows quickly for simpler analysis, or when if you are creating an asymmetric view. You can either hide a selected column or keep a selected column and hide the other.

To hide columns or rows, select a single or multiple columns or rows. right click and select either:

Hide to hide selected column or rows

Keep to hide unselected column or rows

To return all hidden columns or rows select unhide all

4 – Quick Maths

In Cube Viewer, you can quickly calculate values in columns or rows. If you create a column for calculation, Cube Viewer will create an additional column to store the calculation values. This calculation will not be stored in the database, but you can export to excel for reporting.

To create calculation, select the column or rows header, then right click and Select ‘Create calculation’. Select the calculation you would like.

There are lots of different calculations based on your selection of columns or rows, so take a look and see what you think is useful.

5 – Summarize it for me

Planning Analytics for Excel has a smart function to quickly summarize visible columns or rows. When you summarize columns or rows, additional columns or rows will be created at the end of your view.

To summarize columns or rows, select the rows or columns, right click and select Summarize all.

There are various summation options to choose from, so make your selection and then click OK

The result is then shown at the end of columns or rows:

There are lots of other helpful features in Planning Analytics for Excel, including different data spreading methods which we and many of our customers use. Have a play and let us know which features you think are most helpful.