Create an Excel-based Dashboard in Dynamics GP
Dashboards are a fast and effective way for users to take data and make it readily accessible and visual for decision-makers within their organization.
Microsoft Excel is highly-effective at accomplishing this task due to its enhanced charting, graphing, and ability to handle large volumes of data. Fortunately, Excel is a preferred report tool for many businesses so it’s likely in your organization. By learning how to create an Excel-based dashboard from Excel reports, you’re adding a very useful skill to your toolkit.
Excel reports are Excel spreadsheets that have a secure connection to your Microsoft Dynamics GP database. This increases the integrity of the data, and in order to create the report, Excel uses a select statement in the connection settings to know which fields and tables to return information from. Users also have their choice of 200 standard reports to choose from.
The following example will take you through the steps to create a dashboard* containing:
- Overdue Receivables
- Top 10 Receivables
- Top 10 Payables
*Experience in creating pivot tables is recommended
Building the Dashboard
- Step 1: Create a new workbook in Excel called Dashboard with a dashboard worksheet
- Step 2: Open Past Due Customer Balance Excel data connection workbook
- Step 3: Copy and paste into the dashboard workbook
- Step 4: Create a pivot table based on the data (include aging buckets 1, 2, 3, 4)
- Step 5: Change the titles of columns to Current, Over 30, Over 60, Over 90
- Step 6: Insert a column graph
- Step 7: Remove the legend and hide all field buttons on the chart
- Step 8: Move the chart to the dashboard worksheet
- Step 9: Open Customer Balance Excel data connection workbook
- Step 10: Copy and paste into the dashboard workbook
- Step 11: Sort from largest to smallest and apply a filter by Top 10
- Step 12: Create a reference to the Top 10 customer data in the dashboard worksheet
- Step 13: Open Vendor Balance Excel data connection workbook
- Step 14: Sort largest to smallest and filter on Top 10
- Step 15: Create a reference in the dashboard worksheet to the Top 10 vendor data
- Step 16: Add formatting and tables to the Top 10 customer and vendor data
If you’ve taken these steps, you should have an Excel-based dashboard that looks similar to the one at the beginning of this post. Feel free to explore the potential of this impressive tool once you’re more comfortable creating Excel-based dashboards. If you intend to present your work to an executive team, there’s also a way to link your dashboard to your PowerPoint presentation.
We hope this post on how to create an Excel-based dashboard allows you to create a dashboard of your own.
Questions?
If you require assistance with any of the information described above, or with your Microsoft Dynamics GP accounting software, please contact Stoneridge Software. We’re here to help you with all of your business software and network support needs.
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.