Excel and Spreadsheet Design Tips for Better Reporting with Microsoft Dynamics

Every organization needs modern, impactful financial reporting to analyze and manage company data in smarter ways in order to remain competitive in their industry. Microsoft Excel is arguably the most trusted spreadsheet application that is around today. More specifically, most of us have been using Excel since at least college. Given how familiar with Excel most are, it might be surprising to you that a lot of people design spreadsheets without planning.  This translates to poor infrastructure and room for error.  This article will zoom in on planning and designing smarter with Excel-powered financial reporting processes, so you are able to more efficiently utilize your reporting tool to better analyze and manage company data using Microsoft Dynamics. 

In our work with Dynamics ERP customers, we on the Solver team have developed 10 best practices for spreadsheet design:

1. Create your worksheet 

Attempt to have all related information within one tab. If you do not follow this, spreadsheets are restricted in their ability to utilize Excel features and functionality, such as functions, worksheet formulas, PivotTables, and subtotals.   

2. Lay out your data

It's important to consider that the bigger items in your spreadsheet will get the viewer's attention first.  People have a tendency to swiftly skim the column and rows to understand how the data is organized in the worksheet.  A clean layout assists your viewers to see what is more important on the page and where they should start when managing and evaluating information.  

3. Improve the performance of your workbook

Go the route of fewer, bigger workbooks instead of relying on multiple smaller, linked workbooks.  This is the more streamlined approach, and when linked workbooks are required, open ...