TM1

Linked Data Types in Excel

December 2, 2025 by Ivan Cepero

Excel supports Linked Data types, which are cells that connect to the internet and retrieve additional data. Excel contains a series of built-in linked data types such as Stocks, Currencies, and Geographies. These are found within the Data ribbon in Excel.

Here is an example using the Geography data type:

1 – Select a few cells and type in the following US state abbreviations: FL, CA, TX.

2 – Highlight the cells and click on Geography. Excel will add the linked data type icon in the cells.

Hover your mouse on the new icon and click and a data card with information on that State will pop up.

A new ‘Insert Data’ icon appears when you select a linked data type. You can click on a data item from the item list to insert that data into Excel.

You can also integrate geographical data via a formula. When you point to a cell with a linked data type and enter a period, the list of available data will appear in a drop down menu.

This approach will allow you to make your Excel reports and input templates more interactive by pulling additional data from a single value.

Revelwood is an IBM Gold Business Partner with more than 30 years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. We focus on solutions for the Office of Finance and have partnered with clients of all sizes across all industries to optimize, enhance and expand their use of Planning Analytics. Revelwood’s Planning Analytics team consists of PA experts with decades of experience, and we have been recognized via awards including IBM Champion status.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Previewing Changes on Bulk Updates in IBM Planning Analytics

November 18, 2025 by Ivan Cepero

Have you ever changed values from an IBM Planning Analytics for Excel (PAfE) exploration and wanted to confirm what is getting loaded into the model? You can do this by using the “Preview changes” link.

Users can make multiple inputs into a PAfE exploration and then click the Commit button to send all the changes to the TM1 database in one action. After clicking Commit, users will see this confirmation window:

Instead of clicking Yes to commit the changes, users can click on the “Preview changes” hyperlink. This will generate a pop up window that displays the current value of the data points and the proposed new values.

This approach is a great way to validate updates before sending them into the database.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Accessing PAX Exploration Headers in Excel

November 4, 2025 by Ivan Cepero

Explorations are a great way to perform ad-hoc analyses in your IBM Planning Analytics for Excel (PAfE) environment. However, the title selections in the Exploration ribbon are not directly accessible. Here is an approach to access the title selections and make your reports more dynamic.

1. Explorations have connect information in hidden rows. In the example below, we can find this data by unhiding rows 3 through 12.

2. The titles (context) data is in located in cell C9

3. We can use the TEXTSPLIT and TRIM formulas to parse the context into individual cells:

=TRIM(TEXTSPLIT(C9,”:”,”,”))

The formula will update every time you make a change to the Exploration. This approach can be merged with lookup formulas in the other sheets to make your reports more dynamic.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

TM1SET Excel Formula

October 21, 2025 by Ivan Cepero

The TM1SET Excel formula is similar to the SUBNM formula except that TM1SET is hierarchy aware and has formula reference traversal.

The syntax for TM1set is:

TM1set(uri_identifier,serverName,dimensionName,hierarchyName,setExpression,selectedElement,sessionSetOut,activeDisplay,displayModeEnum)

Follow these steps to build a file with the TM1SET formula.

1 – In a new workbook, set up the following labels:

2 – Uri Identifier: the URL to the TM1 server
In cell c2, enter =TM1PRIMARYDATASOURCE()

3 – Server Name: the name of the TM1 server
In cell C3, enter the name of the TM1 server (Demo2 in this example)

4 – Dimension Name: The dimension TM1SET will use
In cell C4, enter the name of the dimension (Account in this example)

5 – Hierarchy Name: The name of the hierarchy TM1SET will use. If not using hiearchies, repeat the dimension name.
In cell C5, enter the name of the hierarchy (Account in this example)

6 – Set Expression: The Set expression for TM1Set. This expression can be hardcoded or copied from the subset editor.
In cell C6, input the set expression. In this example, we are looking for all children of the Advertising parent, the set expression is:

{DRILLDOWNMEMBER({[Account].[Account].[Net Income After Allocations^Net Income^EBITDA^Operating Expenses^Advertising]} , {[Account].[Account].[Net Income After Allocations^Net Income^EBITDA^Operating Expenses^Advertising]} , RECURSIVE)}

7 – Selected Element: The initial element to display
In cell C7, enter Advertising

8 – Session Set Out: TM1SET will fil this in with a unique identifier
Leave cell C8 blank

9 – Active Display: The alias to use for element display
In cell C9, enter the alias name (CodeName in this example)

10 – Display Mode Enum: You can alter the output of the TM1SET formula. Valid options for this parameter are:

SESSIONSET: a unique session set ID created by the server
MUN: the MDX member unique name
MEMBERDISPLAY: the element name or alias

In cell C10, enter MemberDisplay

Once filled in, your template should look like this:

Build the TM1SET formula using the parameters. In cellB14, input this TM1SET formula:

=@TM1SET(C2,C3,C4,C5,C6,C7,C8,C9,C10)

The TM1SET formula will show a drop down which is the sets of elements returned by the Set expression parameter.

Set Editor

TM1SET will not automatically open the subset editor when double clicked. To enable this functionality, you will need to assign the TM1SET formula to a specific named range

To enable the Set Editor:

1 – Select the TM1SET formula

2 – Go to Formulas > Name Manager

3 – Click on New

4 – Create a new name range, named tm2\\_tracked

5 – The scope must be the sheet name

6 – Click OK

7 – Close the Name Manager

Now you can double click on the TM1Set formula and the set Editor will appear

Formula Reference Traversal

This is a feature where TM1 set will change any cell reference that it is using. In this example, we are using the CodeName alias in C9.

1 – Double click the TM1SET formula

2 – In the set editor change the alias to Member ID

3 – Click on Apply

Note how C9 now shows MEMBER_NAME and the Session Set Out cells have been filled out. TM1SET will ‘reach back’ to the source cells and fill them out with the selections made in the set editor.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

The TRIMRANGE Excel Formula

October 7, 2025 by Ivan Cepero

Have you ever referenced an entire row or column and wondered if Excel is doing too much additional processing? If so, the TRIMRANGE formula can help!

The TRIMRANGE formula makes your Excel file more efficient by removing extra rows and columns from a formula. This can be used to create row and column references without impacting Excel performance.

The syntax is written as:

=TRIMRANGE(range,[trim_rows],[trim_cols])

Range: The range to be trimmed

Trim_rows: defines which rows should be trimmed

0 – None

1 – Leading blank rows

2 – Trailing blank rows

3 – Leading and trailing blank rows (default option)

Trim_columns: defines which columns should be trimmed

0 – None

1 – Leading blank columns

2 – Trailing blank columns

3 – Leading and trailing blank columns (default option)

For example, instead of using a full column reference:

=XLOOKUP(“CRP – Corporate”,A:A,C:C)

You can instead use TRIMRANGE:

=XLOOKUP(“CRP – Corporate”,TRIMRANGE(A:A),TRIMRANGE(C:C))

Trim References

Shortcuts can be used when defining the range by using a period before and/or after the colon in a range reference.

A.:.A trims leading and trailing blanks

A.:A trims leading blanks

A:.A trims trailing blanks

This approach will make your spreadsheets more efficient and improve the overall performance.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

IBM Planning Analytics Tips & Tricks: Learn the Excel Focus Cell

September 23, 2025 by Revelwood

It’s easy to lose track of your active cell when working with large worksheets in Excel. Sure, you can always glance at the Name box in the upper-left corner to see the cell reference (for example, L19), but that’s not always convenient or clear when you’re presenting to others.

That’s where Focus Cell comes in. You can watch us demonstrate this in a recent IBM Planning Analytics Tips & Tricks video.

Make Your Presentations More Interactive

Excel’s Focus Cell feature highlights the active cell with a distinct border grid, making it crystal clear where you are on the sheet. This not only helps you navigate large reports faster but also makes your presentations easier for your audience to follow.

How to use Focus Cell

1. Go to the View tab in Excel.
2. Select Focus Cell.
3. Excel will now highlight your active cell with a grid around it.

Customize the Highlight Color

Don’t like the default highlight color? No problem. Simply use the Focus Cell Color drop-down to change it to something that fits your preferences — or even matches your corporate colors.

When showcasing Planning Analytics reports in Excel, clarity matters. Highlighting the active cell makes it easier for your viewers to see exactly what you’re working on — whether you’re drilling into actuals vs. plan, walking through a variance analysis or demonstrating forecasting models.

Try Focus Cell in your next Planning Analytics Excel presentation — it’s a simple way to keep your audience engaged and eliminate confusion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Exploring Consecutive Squares in Today’s Date: 9-16-25

September 16, 2025 by Lee Lazarow

Sheldon Cooper may like to have fun with flags, but I prefer a different approach: fun with dates! In my periodic (and made-up) series, I previously wrote blogs about the use of Excel’s DATE function and about July 1.

Today is September 16, 2025. So what’s special about that?

In numeric format, today is 9-16-25. When initially looking at these numbers, we see that each of these numbers are square numbers:

9 = 3 x 3

16 = 4 x 4

25 = 5 x 5

And when analyzing these square roots further, we find that they are consecutive integers:

3, 4, 5

Put this together and we find that today is a once in a lifetime event (for those of us that did not pay attention on Jan 4, 2009 or Feb 9, 2016) since the next combination is not possible … unless we create a new 16^th month called Lee-ember!

We all know that MATH IS AWESOME, but Math times Dates makes it EXPONENTIALLY AWESOME!

IBM Planning Analytics Tips & Tricks: Making Pivot Tables More Interactive

September 9, 2025 by Revelwood

Pivot tables are one of the most powerful tools in Excel when working with data in IBM Planning Analytics. They allow you to quickly summarize, slice and analyze large amounts of financial information. But sometimes, the default filters can feel a bit clunky — especially when working with time-based data. You can see how to do this in our latest IBM Planning Analytics Tips & Tricks video.

That’s where timelines (time slicers) come in. They give your users a much friendlier and more visual way to select dates. Here’s how you can add one to your Planning Analytics pivot tables:

Step 1: Insert a timeline

Click anywhere inside your pivot table, then go to the PivotTable Analyze tab. In the Filter group, select Insert Timeline.

Step 2: Choose your date field

Excel will automatically look for a field formatted as a date (such as “Period”). Select that field and click OK. A timeline widget will appear on your sheet.

Step 3: Interact with your data

Users can now click on individual months, or drag across a range of dates. The pivot table will update instantly, making analysis quicker and more intuitive.

Step 4: Control multiple pivot tables

If you’re working with more than one pivot table, you don’t need multiple timelines. Instead, click on the widget, go to Report Connections, and connect the same timeline to multiple pivot tables. Now a single time slicer can drive consistent filtering across all of them.

Why this matters for Planning Analytics users

If you’re distributing Planning Analytics reports in Excel, adding a timeline makes your workbooks more interactive, user-friendly, and professional-looking. Your finance team (and executives) will appreciate the ease of selecting periods without digging through dropdown filters.

Next time you build a pivot table on Planning Analytics data, try adding a timeline. It’s a small change that can make a big impact in how people experience and use your reports.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

IBM Planning Analytics Tips & Tricks: MDX to Copy a View

August 26, 2025 by Ivan Cepero

As a model developer, it’s very important to have separate development and production environments that replicate each other as much as possible. This sometimes leads to a situation where you may need to create the same view in both environments. In situations where there are only a few dimensions within a cube, it’s very easy to manually create a view in each environment. But what about situations when your cube has a lot of complexity such as a large number of dimensions with many elements?

The easiest way to replicate a view is to reference the related MDX expression. Revelwood has created many blog posts and webinars to learn MDX, but the easiest way to start learning – and to solve this issue – is to simply copy the expression. This is done by opening/creating a view and then clicking the MDX icon on your ribbon.

This can be done in both IBM Planning Analytics Workspace (PAW) and IBM Planning Analytics for Excel (PAfE):

Once clicked, an MDX editor will appear on your screen. The editor allows you to copy the expression and then paste it into another environment. As long as the dimensionality and elements are the same in each environment, the expression will work in both.

This approach will allow you to quickly replicate views between environments without having to perform any formal migration steps.

Linked Data Types in Excel

Read more IBM Planning Analytics Tips & Tricks:

Previewing Changes on Bulk Updates in IBM Planning Analytics

Read more IBM Planning Analytics Tips & Tricks:

Accessing PAX Exploration Headers in Excel

Read more IBM Planning Analytics Tips & Tricks:

TM1SET Excel Formula

Set Editor

Formula Reference Traversal

Read more IBM Planning Analytics Tips & Tricks:

The TRIMRANGE Excel Formula

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Learn the Excel Focus Cell

Make Your Presentations More Interactive

How to use Focus Cell

Read more IBM Planning Analytics Tips & Tricks:

Exploring Consecutive Squares in Today’s Date: 9-16-25

Today is September 16, 2025. So what’s special about that?

IBM Planning Analytics Tips & Tricks: Making Pivot Tables More Interactive

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: MDX to Copy a View

Read more IBM Planning Analytics Tips & Tricks:

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

PAW	PAfE

TM1

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Set Editor

Formula Reference Traversal

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Make Your Presentations More Interactive

How to use Focus Cell

Read more IBM Planning Analytics Tips & Tricks:

Today is September 16, 2025. So what’s special about that?

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT