Skip to Content
Author's profile photo Former Member

How to use Excel formulas in BPC Input Schedules

I noticed that one of the unanswered frequently asked question on the SCN forum regards the use of Excel formulas in Input Schedules.

There is an option “Keep formula on Data”, it helps if only refresh occurs, it is good for static reports without dynamic expansions, but in real life in most cases there is at least one expansion per report, so this option is useless.

I wonder why this functionality is not documented (at least I could not find it), and in general I do not think that EPM add-in is well-documented, especially the part about formatting (using of EPM Formatting Sheet).

Step 1.

Create proper Excel formula inside your report, use upper left cell, here you are able to use absolute or relative referencing.


Step 2.

Go to the EPMFormattingSheet->Dimension Member/Property Formatting section – > Column

     2.1 Select “Apply” check-box on “Formatting on Specific Member/Property”

     2.2 Specify members (Budge and Forecast)

     2.3 Copy the resulting (on Step 1) formula to “Data” column, but in this case use apostrophe before the formula: ‘=C10*1,2

     2.4 Select or enter manually “Content” in “Use” column


Step 3.

Use this EPM Formatting Sheet for your report:

     3.1 Edit Report -> Options -> Check Apply Dynamic Formatting

     3.2 Select your EPM Formatting Sheet

Step 4.

Refresh Worksheet

During the refresh EPM add-in automatically adjusts the cell reference to refer to different cells relative to the position of the formula, as well as Excel does.


See the sample file if you need more details:

Note: Even if you use localized version of Excel,  still on EPM Formatting Sheet you should write Excel functions in English. During the refresh it will be translated to your local language. I do not consider it as a bug, as for me It was really stupid Microsoft’s Idea to translate function names : )

Best Regards,

Assigned tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thank you!!

      I was searching a solution for this a long time!!!

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      You're welcome!

      Author's profile photo Former Member
      Former Member

      Hi Sergey,

      thank you very much for this very good how to guide!

      Is it also possible to have more complex formulas, e.g. VLOOKUP formulas in the formatting sheet? Because I cannot get it to work with VLOOKUP...



      Author's profile photo Former Member
      Former Member

      VLOOKUP in local members is this what you are looking for?

      Author's profile photo ANIL AGRAWAL

      Good Work...


      Author's profile photo Former Member
      Former Member

      Hi Sergey,

      Thank you very much for this guide.

      I would like to ask if its possible to apply this method for two reports at the same time. I have two report in a specific sheet and this method only calculate the first report.
      How can i use it for both of them?

      PS: I have tired to have two different formating sheet for both but it didnt work.

      Thank you in adnvance,