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 all workbooks at the onset to boost performance.

4. Utilize the data validation function

The data validation function helps to minimize any errors from data entry and simplifies reviewing and reporting on the data.  Data validations enables you as the workbook designer to pinpoint what is input into selected cells with dropdown lists, as well as error and/or informational messages.  Data validation can also help identify future errors by employing the invalid data circle option. This can be configured from the Data tab in the Data Tools group.

5. Use absolute or relative references

Excel automatically changes your calculation based on the new cell location after you copy and paste a formula containing cell references.  This is probably why the calculations in the new cell are blank or incorrect.  The way to avoid this is by employing an absolute reference, using the ‘$' sign.  

6. Safeguard important cells

Protection functions enable the workbook designer to select particular users who are allowed to edit a cell or range. Excel allows you to define which cells in your workbook should be edited, including which users are able to insert columns and rows or remove data. Even formatting can be protected.

7. Color Advantages

If you're not as number-oriented as the next accounting or finance professional, color advantages inject a little flair into your spreadsheet design process. Working on the typical black and white grid with a significant amount of numbers can be simultaneously overwhelming and mundane. Use some color in the worksheet to point to where data entry is requisite or where the important data resides. You can use Cell Styles, Conditional Formatting, or standard color options to splash bright green, orange, purple, or other colors on your worksheet.

8. Do not merge cells

Why is merging cells within the spreadsheet not the best idea? This is related to the exercise of sorting through your data - merged cells within that data range will prevent you from being able to adequately sort through the information.  As an alternative, go through Format Cells, select Alignment, and from the horizontal dropdown, choose Center Across Selection to have a label centered across multiple cells.  The produces the same effect, but does not interfere with your sorting abilities.

9. Hide unnecessary calculation sheets

It is not uncommon for Excel files to have multiple worksheets.  Some are comprised of raw data, some calculations, and a couple have the final output. To communicate clearly when sharing and creating formulas, label your ranges because it makes it much easier to select big amounts of data and make formulas easier for understanding with descriptive names. Remember that when you create a formula, the name range will populate just like the function names. This reduces formula overhead and expedites the workbook process.

10. Employ named ranges and formulas

Instead of having every tab reference a "total" cell, make a named range to cluster the totals from each sheet together.  This allows the designer to easily pick the named range from the function menu and produce a summary total sheet, which automatically pulls the total from each tab.

When evaluating your Dynamics reporting processes, consider how Excel and Excel add-ins can mobilize your team, especially with proper spreadsheet design best practices. There are plenty of options that offer comprehensive corporate performance management and business intelligence solutions, while still allowing Excel to drive the user experience. Some offer a commercial data warehouse, so you can combine your Dynamics data with any other data source in a single high performance space to use it in your reporting and budgeting processes or in dashboard analysis. 

With a combination of good design principles, data usage best practices, and access to the right reporting tools, Excel can remain a great platform for financial professionals. As we and our Dynamics customers have learned over years of experience that analysis, budgeting, and collaboration can drive better business performance.