Overview

1010data makes it very easy to create dashboards in Excel that can leverage the power and speed of data analysis using the 1010data engine.

1010data's Excel Add-in allows you to incorporate user-specified parameters in a query that is run on 1010data. The results then display directly in an Excel dashboard with charts and conditional formatting.

Let's say we want to build a dashboard in Excel that displays the results of two 1010data queries. One simply aggregates total sales for a specified department and date range, while the other aggregates total sales by date for the same department. We want to give the user the ability to specify the start and end dates for the date range and to select the department for which they want to see total sales figures. In addition, we want to give the user the choice to aggregate either by group description or by brand. Finally, we want to see visualizations of the results in the dashboard using Excel's charting capabilities.

When we're finished with this tutorial, we want our dashboard to look something like the following:

The tutorial guides you through the following steps to create a dashboard:
  • Run a1010data query from Excel using the 1010data Excel Add-in
  • Add static inputs to the dashboard for the date range
  • Target the results of the query to the dashboard
  • Format the query results using basic and conditional formatting
  • Add a simple chart to the dashboard
  • Create a static drop-down to allow the user to select whether they want to aggregate on group description or brand
  • Incorporate a dynamic drop-down to allow the user to select a particular department for the query
  • Add another query that aggregates total sales by date for the same department
  • Add a button to run both queries from the dashboard
  • Chart the results of both queries dynamically on the dashboard
  • Incorporate the use of 1010data libraries and blocks
  • Hide the worksheets and lock the workbook

Let's build the dashboard one step at a time.

We'll use the 1010data Excel Add-in to run a 1010data query from Excel and download the results directly into an Excel worksheet.