• Skip to main content
  • Skip to footer
Revelwood Logo

Revelwood

Your SUPER-powered WP Engine Site

  • Who We Are
    • About Us
      • Our Company
      • Our Team
      • Partners
    • Careers
      • Join Our Team
  • What We Do
    • Solutions
      • Workday Adaptive Planning
      • IBM Planning Analytics
      • BlackLine
    • Services
      • Implementation Services
      • Customer Care
        • Help Desk
        • System Administration as a Service
      • Training
        • Adaptive Planning Training
        • IBM Planning Analytics / TM1 Training
    • Products
      • DataMaestro
      • LightSpeed
      • IBM Planning Analytics Utilities
  • How We Help
    • Workday Adaptive Planning Use Cases
    • IBM Planning Analytics Use Cases
    • BlackLine Use Cases
    • Client Success Stories
  • How We Think
    • Knowledge Center
    • Events
    • News
  • Contact Us

Excel

IBM Planning Analytics Tips & Tricks: Excel Workbook Stats

May 3, 2022 by Lee Lazarow

Have you ever wanted to know details about your existing worksheet or workbook such as the number of cells that contain data or the number of total sheets? In 2021, Microsoft Excel introduced a feature called Workbook Statistics that will provide details about your file.

The Workbook Statistics icon is found within the proofing area of the Review ribbon.

Excel Workbook Stats

When the icon is clicked, a window appears to show details about the sheet. The following statistics are shown:

Worksheet level  

  • End of sheet / Last cell
  • Number of Cells with Data
  • Number of Tables
  • Number of PivotTables
  • Number of Formulas
  • Number of Charts
  • Number of Images
  • Number of Form Controls
  • Number of Objects
  • Number of Comments
  • Number of Notes

Workbook level  

  • Number of Sheets
  • Number of Cells with Data
  • Number of Tables
  • Number of PivotTables
  • Number of Formulas
  • Number of Charts
  • Number of External Connections
  • Number of Macros

Here is an example of a simple sheet that contains  some input cells, some calculations, and a header row:

Excel Workbook Stats

Not only will these details help you with generic Excel related questions, but the “end of sheet” and “cells with data” details can also assist when reviewing your dynamic reporting setups.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more Excel Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Correctly Sum Rounded Numbers in Excel

IBM Planning Analytics Tips & Tricks: Excel Keyboard Navigation

IBM Planning Analytics Tips & Tricks: Excel OFFSET Function

Home » Excel

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: Excel, Excel tips & tricks, excel tricks, Excel workbook stats

IBM Planning Analytics Tips & Tricks: Excel DATE Function Updated

August 17, 2021 by Lee Lazarow Leave a Comment

Some of you may remember an Excel feature that I posted about last year which explained how the DATE function will convert invalid dates into real ones. For example, DATE(2020,7,35) will be converted to August 4, 2020 since there are not 35 days in July.

Here is where the fun begins!

  • This week includes the 20th day of the month of August
  • August is the 8th month of this year, which is also the 20th month of last year
  • Last year was 2020

Put this all together and we have a special event happening this week …

On August 20, 2021,

the resulting date will be 20/20/2020

Excel Date Function Updated Aug 2021

And if we take it a step further …

At 8:20p and 20 seconds on 8/20/2021,

the resulting time will be 20:20:20 on 20/20/20

This is why we love Excel!

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more Excel tips from our IBM Planning Analytics team:

IBM Planning Analytics Tips & Tricks: Manipulating Text in Excel

IBM Planning Analytics Tips & Tricks: Excel TYPE Function

IBM Planning Analytics Tips & Tricks: Excel Keyboard Navigation

Home » Excel

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: cool Excel tricks, Excel, Excel date function, Excel tips & tricks, IBM Cognos TM1, IBM Planning Analytics

Workday Adaptive Planning Tips & Tricks: Excel Substitute

March 3, 2021 by Michelle Song Leave a Comment

We have something different for today’s Workday Adaptive Planning Tips & Tricks post – an Excel tip! Our Adaptive Planning users also use Excel, so we thought they’d find this helpful.

Have you ever wanted to get rid of spaces in some of your Excel cells? Maybe you do not want a cell to have separate words and instead want to use an underscore character. Or maybe you are in a situation where you want to adjust a prefix for a certain range of cells. You can use Excel’s find and replace functionality, but this approach could lead to a time consuming effort if you want to pick and choose the cells where it applies.

Excel’s SUBSTITUTE function can help you solve this problem. The SUBSTITUTE function is used to find a specific set of characters and replace it with something else while also giving you the ability to define details within cells.

The syntax of the function is:

=SUBSTITUTE (text, old_text, new_text)
  • text
    • This is the source that will be changed; this is typically a cell reference.
  • old_text
    • This is the subtext that will be replaced.
  • new_text
    • This is what will replace the old subtext

If the cell in A5 consists of “Happy Birthday” then it can be updated to “Happy_Birthday” via the following:

=SUBSTITUTE (A5, “ “, “_”)

In addition, the parameter that defines the new text can also be a cell reference.  This gives you the ability to quickly change the results of a large set of data by simply updating a single cell.

This approach will allow you to quickly find and replace characters within specific cells instead of having to manually go through a set of cells via Excel’s find and replace functionality.

The team at Revelwood has been recognized by Adaptive for its thought leadership in the space, commitment to its Adaptive Insights practice, and its rapid achievements of milestones. Visit Revelwood’s Knowledge Center for our Adaptive Insights Tips & Tricks or sign up here to get our Adaptive Insights Tips & Tricks delivered directly to your inbox. Not sure where to start with Adaptive Insights? Our team here at Revelwood can help! Contact us info@revelwood.com for more information.

Read more Workday Adaptive Planning Tips & Tricks:

Workday Adaptive Planning Tips & Tricks: Override Formulas in Sheets

Workday Adaptive Planning Tips & Tricks: Trigger for a Cube Calculated Account

Workday Adaptive Planning Tips & Tricks: Alternate Time Tree

 
Home » Excel

Filed Under: Workday Adaptive Planning Tips & Tricks Tagged With: Adaptive Insights, adaptive insights tips & tricks, enterprise performance management, Excel, Excel tips & tricks, Financial Performance Management, Workday Adaptive Planning, Workday Adaptive Planning Tips & Tricks

IBM Planning Analytics Tips & Tricks: Manipulating Text in Excel

February 2, 2021 by Lee Lazarow Leave a Comment

Have you ever wanted to manipulate text within Excel? Excel has a series of functions that allow you to parse text strings based on whether you want to reference the left, the right, or the middle of the cell contents.

For example, assume cell A2 contains the text “Planning Analytics”. The functions LEFT and RIGHT will allow you to view characters at the start of the string or at the end of the string.

=LEFT(A2,3) will give a result of “Pla”

=RIGHT(A2,3) will give a result of “ics”

The function MID will allow you to look within the string by defining both a starting point and the number of characters in the result.

=MID(A2,3,3) will give a result of “ann”

But what if you didn’t know the number of characters? You can use functions to find a specific set of characters and to determine the overall length of the string.

=FIND(“A”,A2)-1 will give a result of “9”, which signifies that the string begins at the 9th character

=LEN(A2) will give a result of “18”, which signifies that the string contains 18 total characters

You can then put these pieces together to find a specific set of data, such as the second word in the string. Assuming that D6 contains the LEN function and D5 contains the FIND function:

=RIGHT(A2,D6-D5) will give a result of “Analytics”

These Excel functions will allow you to quickly parse out pieces of a text string which can then be used in your spreadsheets.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more Excel tips in our IBM Planning Analytics Tips & Tricks series:

IBM Planning Analytics Tips & Tricks: Excel’s NETWORKDAYS Function

IBM Planning Analytics Tips & Tricks: Excel Keyboard Navigation

IBM Planning Analytics Tips & Tricks: Excel TYPE Function

Home » Excel

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: enterprise performance management, Excel, excel tips, Excel tips & tricks, excel tricks, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, Planning Analytics tips, Planning Analytics Tips & Tricks, TM1

IBM Planning Analytics Tips & Tricks: Excel’s NETWORKDAYS Function

January 26, 2021 by Lee Lazarow Leave a Comment

Have you ever wanted to define the number of working days between two calendar dates? Do you define “working days” simply as non-weekend days or do you also include holidays? Did you know that you can calculate this information using Excel’s NETWORKDAYS function?

The NETWORKDAYS function is used to determine the number of whole working days between start_date and end_date. Working days are defined as the exclusion of weekends, which in essence computes the number of Mondays through Fridays. The function also includes an optional parameter to exclude holidays and any other specific non-working days.

The syntax of the function is:

NETWORKDAYS (start_date, end_date, [holidays])
  • Start_date: This is required
  • End_date: This is required
  • Holidays: This is optional
    • This consists of a range of one or more dates to be excluded from the calendar. The list entails a range of cells that contains the dates.

Note: All dates are entered in date format or in serial number format

This information can be used to help calculate any/all of the following pieces of your model:

  • Benefit accruals and eligibility
  • Ratios such as “Revenue per day” or “Units per day”
  • Days remaining for end-of-service dates
  • Anything else that requires the number of days within the formula

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more Excel tips in our IBM Planning Analytics Tips & Tricks series:

IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula

IBM Planning Analytics Tips & Tricks: Excel TYPE Function

IBM Planning Analytics Tips & Tricks: Excel Keyboard Navigation

Home » Excel

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: enterprise performance management, Excel, Excel NETWORKDAYS, excel tips, Excel tips & tricks, excel tricks, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, lee lazarow, Planning & Forecasting, Planning & Reporting, Planning Analytics tips, Planning Analytics Tips & Tricks, TM1

IBM Planning Analytics Tips & Tricks: Excel TYPE Function

January 12, 2021 by Lee Lazarow Leave a Comment

Have you ever referenced a list in Excel and needed to know whether each value was a number or a string? This may be required when another function depends on the type of value in each cell.

Excel has a function called TYPE that will give you this information. The function contains a single parameter, which is simply the cell you want to check. The formula will output a number that defines the type of value such as number, text, logical value or an error.

Here are results from four iterations of the TYPE formula:

IBM Planning Analytics Tips & Tricks: Excel Type

You can then use the type result within an IF statement to perform various functions.

The TYPE function will not determine whether a cell contains a formula. TYPE only determines the type of the resulting, or displayed, value. If value is a cell reference to a cell that contains a formula, TYPE returns the type of the formula’s resulting value.

This approach will help you when determining the use of IBM Planning Analytics formulas such as DBS vs. DBSS.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more Excel tips & tricks:

IBM Planning Analytics Tips & Tricks: Excel Keyboard Navigation

IBM Planning Analytics Tips & Tricks: Excel’s FILTER Function

IBM Planning Analytics Tips & Tricks: Excel’s CONCAT and TEXTJOIN Functions

Home » Excel

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: enterprise performance management, Excel, Excel tips & tricks, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, lee lazarow, Planning Analytics tips, Planning Analytics Tips & Tricks, Revelwood, TM1

IBM Planning Analytics Tips & Tricks: Excel Keyboard Navigation

January 5, 2021 by Lee Lazarow Leave a Comment

Have you ever wanted to quickly go to the last used cell within a spreadsheet but did not know how? Have you ever wanted to determine where the next available row or column resides? Have you ever wondered why a spreadsheet with only a few cells of data may appear as a large file? All these answers can be provided with a single keystroke combination.

Pressing Ctrl and End at the same time will bring you to the last cell within a spreadsheet that Excel is referencing. The definition of “last” entails the rightmost column and the lowest used row on the worksheet. The last cell may not always contain data, but it will always be registered by Excel as something that is utilizing memory.

Here are some other keystroke combinations that will help you quickly navigate around your spreadsheet:

  • Ctrl and Home at the same time: this will bring you to the first cell (A1) in the worksheet.
  • End then an arrow key: this will bring you to the next empty value in any direction. For example, pressing End and then the down arrow will bring you to next empty cell in the column where your cursor currently resides.
  • Ctrl and PageUp : this will bring you to the previous sheet in the workbook.
  • Ctrl and PageDn: this will bring you to the next sheet in the workbook.

Keyboard shortcuts can easily help you navigate your Excel files and help you determine the “ending” cell within a spreadsheet.  This will be useful in situations such as dynamic reports where rows and columns are automatically generated via automation.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more Excel Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula

IBM Planning Analytics Tips & Tricks: Excel’s IFS Function

IBM Planning Analytics Tips & Tricks: Excel LET Function

Home » Excel

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: enterprise performance management, Excel, Excel tips & tricks, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, lee lazarow, Planning Analytics tips, Planning Analytics Tips & Tricks, Revelwood, TM1

IBM Planning Analytics Tips & Tricks: Excel’s FILTER Function

June 23, 2020 by Lee Lazarow Leave a Comment

Tips & Tricks

Have you ever wanted to filter a list of data to look at a subset of the entire table? Many of you know that you can use the filter button within Excel’s data section to do this.

IBM Planning Analytics Tips & Tricks: The Excel FILTER Function

But what if you wanted to create a filter that users can change without having to navigate through ribbons? Did you know that you can do this using Excel’s FILTER function?

The FILTER function allows you to filter a range of data based on criteria you define. This allows you to create selector cells which can be used within a formula to filter the selected data. The function is written as follows:

FILTER (range, formula for inclusion, if empty)

  • The range consists of the entire table to be checked
  • The formula for inclusion consists of criteria to be filtered
  • The last parameter (optional) defines a value to be returned when no results are found

The example below shows a table on the left, an input cell, and the FILTER command in cell F5. In this case, the function is being used to look at the entire table (cells B3 through D11) and checking to see if the value in column B is the same as the value in cell G2.

Learn about Excel's FILTER Function

The formula for inclusion can use more complex approaches to check multiple values. This is done by using the multiplication operator as the “and” statement. The example below expands upon the first example by allowing the user to independently select both a state and a city.

IBM Planning Analytics Tips: Excel's FILTER Function

This functionality allows you to replicate Planning Analytics selector concepts into an existing spreadsheet.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: The Excel FORMULATEXT Function

IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula

IBM Planning Analytics Tips & Tricks: New Excel Feature – Map Charts

Home » Excel

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: Analytics, Excel, Excel tips & tricks, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, Revelwood, TM1

IBM Planning Analytics Tips & Tricks: The Excel FORMULATEXT Function

June 16, 2020 by Lee Lazarow Leave a Comment

Tips & Tricks

Have you ever looked at a value on a report and wanted to know the formula that was used, even though you may not have access to the report? Have you ever wanted to easily include a formula as part of the report? Have you ever wanted to show people how complicated your calculation was as an attempt to get them to better appreciate you?

Well, all of this can be done in Excel with the use of a simple formula: FORMULATEXT.

The FORMULATEXT function is used to return a formula as a string. The single parameter of the function is the cell to be converted to text.

The following example shows the use of the formula in cell C6.

IBM Planning Analytics Tips & Tricks: The Excel FORMULATEXT Function

Not only can you now show your co-workers how complicated your nested-if formulas have become, but you can also end those recurring nightmares about your middle school math teachers who always said “show me your work” (ok, maybe that’s just me).

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: The Excel DATE Function

IBM Planning Analytics Tips & Tricks: New Excel Feature – XLOOKUP

IBM Planning Analytics Tips & Tricks: Excel’s IFS Function

Home » Excel

Filed Under: IBM Planning Analytics Tips & Tricks Tagged With: Analytics, Excel, Excel tips & tricks, Financial Performance Management, IBM Cognos TM1, IBM Planning Analytics, Revelwood, TM1

  • Go to page 1
  • Go to page 2
  • Go to Next Page »

Footer

Revelwood Overview

Revelwood helps finance organizations close, consolidate, plan, monitor and analyze business performance. As experts in solutions for the Office of Finance, we partner with best-in-breed software companies by applying best practices guidance and our pre-configured applications to help businesses achieve their full potential.

EXPERTISE

  • Workday Adaptive Planning
  • IBM Planning Analytics
  • BlackLine

ABOUT

  • Who We Are
  • What We Do
  • How We Help
  • How We Think
  • Privacy

CONNECT

Contact:

25B Vreeland Road, Suite 111 Florham Park, NJ 07932
201.984.3030
info@revelwood.com

Copyright © 2023 · Revelwood Inc. All rights reserved. Revelwood® and the Revelwood logo are registered marks of Revelwood Inc.