Managing large datasets in IBM Planning Analytics Workspace (PAW) often means dealing with complex lists of elements—from thousands of SKUs to extensive customer databases. In this edition of our IBM Planning Analytics Tips & Tricks series, Lee Lazarow, IBM Planning Analytics Practice Leader at Revelwood, demonstrates a simple, one-click solution to clean up your subsets by removing duplicate elements.

Watch the Video

See this tip in action! Watch the full video below to follow along step-by-step as Lee Lazarow demonstrates how to quickly identify and eliminate duplicates in Planning Analytics Workspace.

Whether you are a developer staring at a long list of elements or a modeler managing static subsets created by users, this tip will help you maintain a clean and accurate Planning Analytics environment.

Why Duplicates Happen in PAW

Duplicates often creep into explorations and subsets, especially when dealing with:

• Large Dimensions: When viewing thousands of elements like SKUs or customers, it’s easy to miss a repeating entry.
• Static Subsets: Unlike dynamic subsets, static lists created manually by users may accidentally include the same member multiple times.
• Manual Modeling: Even experienced developers can overlook duplicates when manually building complex views.

How to Remove Duplicate Elements

Lee Lazarow shows the following process in the video:

1. 1. Identify the Duplicate: In your exploration, notice if an account or element (such as “Rent”) appears more than once in your rows or columns.
2. 2. Open the Set Editor: Click on the dimension in your rows (or columns) and select the Set Editor.
3. 3. Find the “Remove Duplicated Members” Button: Within the Set Editor, look for the specific icon designed to clean up your list.
4. 4. Execute with One Click: Simply click the “Remove duplicated members” button. PAW will instantly scan the list and remove any redundant entries.
5. 5. Apply Changes: Click “Apply” to update your view. Your exploration is now modified to show only unique elements.

Why This Feature Matters

Using the built-in duplicate removal tool helps:

• Save Time: You don’t have to manually scan thousands of rows to find errors.
• Improve Accuracy: Ensure your reports and explorations aren’t double-counting or cluttering your data view.
• Simplify Maintenance: Clean up user-created static subsets with a single click.

By leveraging this built-in PAW feature, you can let the system do the heavy lifting, allowing you to focus on analyzing your data rather than cleaning it.

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.

Read more IBM Planning Analytics Tips & Tricks:

How to Create Picklists in Planning Analytics Workspace

Performance Monitoring in Planning Analytics Workspace

Linking Textboxes in IBM Planning Analytics Workspace

IBM Planning Analytics Workspace lets you create pop-out boxes in your books to make key numbers stand out. You can watch this tip in action in our “Make Numbers Pop with Linked Textboxes | IBM Planning Analytics Tips & Tricks from Revelwood” video.

Want to achieve the same effect in Excel? You can easily do this using text boxes—here’s how.

Here’s how to link a cell to a text box:

1 – Create a textbox on a worksheet using the icon within the Insert, Text ribbon.

2 – Click on the textbox border.  Once clicked, the blinking cursor will disappear.

3 – Enter an equal sign into the formula bar and then click on the cell that you want to display in the textbox.

You will now see the text box linked to the cell in the formula bar. From here, you can change the format of the textbox to make the number stand out (ex: larger fonts, background colors, etc.).

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.

Read more IBM Planning Analytics Tips & Tricks:

Linked Data Types in Excel

Previewing Changes on Bulk Updates in IBM Planning Analytics

Accessing PAX Exploration Headers in Excel

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.

Read more IBM Planning Analytics Tips & Tricks:

Previewing Changes on Bulk Updates in IBM Planning Analytics

Accessing PAX Exploration Headers in Excel

TM1SET Excel Formula

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.

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.

Read more IBM Planning Analytics Tips & Tricks:

TM1SET Excel Formula

The TRIMRANGE Excel Formula

Learn the Excel Focus Cell

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.

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.

Read more IBM Planning Analytics Tips & Tricks:

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

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

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

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. 1. Go to the View tab in Excel.
2. 2. Select Focus Cell.
3. 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.

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.

Read more IBM Planning Analytics Tips & Tricks:

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

IBM Planning Analytics Tips & Tricks: Excel Focus Cell

IBM Planning Analytics Tips & Tricks: PAW Default Settings

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.

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.

Read more IBM Planning Analytics Tips & Tricks:

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

IBM Planning Analytics Tips & Tricks: Excel Focus Cell

IBM Planning Analytics Tips & Tricks: PAW Default Settings