10 Tips for Better Reporting with Excel for Dynamics 365

This article discusses reporting tools and best practices on spreadsheet design for companies looking to expand their Microsoft Dynamics 365 experience.

Companies need powerful financial reporting solutions to help remain competitive in their industry by analyzing and managing their data without any issues or disruptions. Microsoft Excel is probably the most well-known spreadsheet software program that exists. Everyone has probably used it at least once in his or her life. However, many people often design their spreadsheets with no planning at all. This most likely means that most spreadsheets have a poor infrastructure and some room for error. This article will explore how you can plan and design smarter with Excel-based financial reporting options, so you are able to better use your reporting solution to navigate your company in analyzing and managing your data using Dynamics 365.

 
Here are the top 10 best practices based on Best Practices for Spreadsheet Design, a whitepaper created by Solver’s Training Manager, Tina Lee:
1)      Build your worksheet
Try to keep all related data within one tab. Spreadsheets that do not follow this, restrict their ability to take advantage of Excel’s features, such as PivotTables, worksheet formulas, functions, and subtotals.
2)     Lay out your data
Keep in mind the larger items on your spreadsheet will get the first attention. Viewers have the tendency to quickly scan the headings of columns and rows to get a sense of how the information is structured on the worksheet. A clear layout helps others to see what is important on the page and where they should begin when managing and analyzing data.
3)     Improve your workbook performance
Try using fewer, large workbooks rather than using several smaller linked workbooks. This will be more efficient, and when you find it necessary to use linked workbooks, open all linked workbooks in order to boost performance.
4)     Use data validation
Data validation helps minimize data entry errors and makes it easier to review and report on the data. Data validation enables the workbook designer to limit what is entered into selected cells with dropdown lists and with error or informational messages. Data validation can also help identify future errors by using the invalid data circle option. This can be configured from the Data tab and Data Tools group.
5)     Use absolute or relative references
Microsoft Excel will automatically change your calculation based on the new cell location when you copy and paste a formula that contains cell references. This may be the reason why the calculations in the new cell are incorrect or blank. The way to prevent this shift is by using an Absolute reference with the use of the ‘$’ sign.
6)     Protect important cells
Protection options enable the designer of the workbook to choose specific users who are allowed to select or edit a cell or range. Excel helps the designer to define which cells in your workbook should be edited. It can also enable the designer to control which users are able to insert columns and rows as well as remove data. Formatting can also be protected.
7)     Use the color tools
For those who are not numbers people, this will add a little excitement to your spreadsheet design process as working on a black and white grid with a large amount of numbers can be mundane and overwhelming. Paint some color on the worksheet to show where data entry is required or highlight important information on your spreadsheets! My tip is to choose colors such as bright green, orange, or purple with tools like Cell Styles, Conditional Formatting or standard color options.
8)     Avoid merging cells
Merging cells within your spreadsheet is not the best idea. This is because, when it comes time to sort through your data, merged cells within that data range will prevent you from being able to properly sort through the data. Instead, I suggest you go to Format Cells, select the Alignment tab, and from the Horizontal drop box, select Center Across Selection to have a label centered across multiple cells. This creates the same image, but will not interfere with your sorting abilities.
9)     Hide unnecessary calculation sheets
It is common for Excel files to have many worksheets. Some contain raw data, others with calculations, and only a couple with the final output. To make things clear when sharing and creating formulas, name your ranges as it makes it much easier to select large areas of data and can make formulas easier to understanding with descriptive names. Keep in mind that when creating a formula, the name range populates much like the function names. This reduces the formula overhead and creates a faster workbook process.
10)  Use named ranges and formulas
Rather than pointing every tab to reference a “total” cell, create a named range to group the totals from each sheet together. This enables the designer to easily select the named range from the function menu and create a summary total sheet, which automatically picks up the total from each tab.
 
When looking to upgrade your Dynamics 365 reporting tool with Excel and Web-based interfaces, you should evaluate modern Excel add-ins like BI360. BI360 is a business data warehouse, so you can combine your Dynamics 365 with any other data source – and use it in the budget process, in the reporting process, or in the dashboard analysis. Solver, Inc. is happy to answer questions and generally review BI360’s web-powered, easy-to-use Excel and mobile BI tools with both real-time or data warehouse integrated analysis, budgeting and collaboration as a way to accelerate company performance management using Microsoft Dynamics 365.
Solver enables world-class decisions with BI360, a leading web-based CPM suite made up of budgeting, reporting, dashboards, and data warehousing, delivered through a web portal. Solver is reinventing CPM with its next generation solution. BI360 empowers business users with modern features including innovative use of Excel in the model design process. If you’re interested in learning more, our team is excited to hear about your organizational needs and goals.