Excel Workbook: Best Practices and Steps

Eugen Melnic

Author: Eugen Melnic, FP&A Manager and Community Captain

We all know that Excel is often the source of business frustration and confusion that leads to the start of a successful transformation through the power of Board. Excel can also be the end point too, as once the business data and processes are in Board, business users will have specific needs that require the power of Board in an Excel interface.   

Too often, the initial transformation projects keep such deliverables to a future phase or part of a continuous improvement component that Board allows with its flexibility. End users can find this very frustrating as their needs appear just within reach. So, instead of extracting and modifying Excel workbooks every week or month, why not have them integrated with your latest Board data?

Eugen Melnic has delivered reliable and effective reporting to the Finance team at HH Global using the Board Excel add-in. Today, he's sharing his best practices for successfully working with Board and Excel to support others who have similar needs.

___________________________________________________________________________________________

General Setup

• Always build the Excel file offline, not on MS SharePoint.
• Keep a backup copy of the latest working version in case of corruption.
• Avoid:
  • Sharing file on SharePoint and
  • Having multiple active users editing at the same time and
  • Adding/removing tabs or modifying layouts — this can corrupt the workbook.
    
• Safe actions while shared:
  • Users can view or refresh sheets simultaneously without issue.

Creating Layouts

1. Start the Layout:

• Select required cubes → click Apply.
• Do necessary sheet/workbook selections.
• Go back to layout → add dimensions in Axes tab → click OK.

2. Optimize Performance:

• If using a lower-level dimension, add higher-level ones from the same tree in Data tab > Blocks.
• Avoid putting too many dimensions from one/multiple trees in Axes — this affects performance.

3. Copying Layouts:

• Never use “Move or Copy” if the tab contains a layout.
• Use “Copy layout” functionality instead.

4. If Errors Occur:

• Check if too many dimensions are used in Axes.
• If multiple cubes with different dimensions are used, remove some cubes/dimensions.

5. Reusing Existing Views:

• Export from Web → tick “Microsoft Excel Workbook for Board Add-in” → copy tab into main report.

6. Display Issues:

• If entity shows as member count, try:
  • Rearranging dimensions in Axes or Blocks.
  • Removing cubes/entities to simplify the layout.

Operating Layouts

• Use “Copy layout” and “Paste layout” – not Ctrl+C/V.
• Avoid using Row templates – can impact performance.
• To delete a layout:
  • Click “Clear Layout”, then Delete layout cells.
  • If prompted “Do you want to remove layout?”, click Yes.

Excel Formatting

• Set layout formatting to Manual (not default Automatic).
• Disable block “Format” and “Alert” to avoid Excel formatting conflicts.
• Layouts don’t retain “wrap text” after refresh:
  • Duplicate and format a row above layout → hide layout header row.
    
• Format Painter:
  • Single-click: works fine.
  • Double-click: may override layout on certain Board versions.

Selections

1. Workbook vs Sheet:

• Use Workbook selection for consistent selections across tabs.
• Use Sheet selection for tab-specific filtering.

2. Set Selection Function (Preferred Method):

• Create a Control tab with dimension members listed.
• Use Name Manager to name cell references.
• In each tab: type =SetSelection() and add named elements.
• To update:
  • Change in Control tab → go to each tab → press F2 + Enter.
  • Or automate with a macro.
    
• If Set Selection is used, do not use Sheet or Workbook Selection.

Refreshing Data

• Use “Refresh Sheet” – ensure no active Excel filters.
• Avoid “Refresh Workbook” – may freeze Excel.

Data Entry

• Only for users with appropriate security settings.
• Useful for bulk uploads from unstructured sources.
• Ensure correct cubes, dimensions, and selections are used.
• Entries are static – procedures must be run in Board Web to process data.

________________________________________________________________________________________

Thanks, Eugen, for sharing these tips and tricks with the Community!

You might also like:
All best practices
The importance of documentation for long-term success: Deployment documentation
‘Run as administrator’ feature: What you need to know

Simon Gettings

Very helpful, thank you for the post.