Creating Custom Dashboards with Excel Metrics and KPIs

Creating Custom Dashboards with Excel Metrics and KPIs

Are you performing data analysis in Excel? This blog is for you! Bring your Excel spreadsheet files to Bold BI. Explore your business data, prepare a dashboard, and play with its widgets to see the story that lives in your data.

Let’s see how to structure an Excel workbook, integrate Excel files as tables and worksheets, and connect Excel files directly from local and cloud storage to a Bold BI dashboard.

Structured Excel Spreadsheet

The first row values of your worksheet will be considered column names by default in Bold BI. So, provide a unique and meaningful column name in your spreadsheet that will make it easy to identify your data.

Structured Excel workbook
Structured Excel workbook

File size and types

Bold BI supports connecting to Excel workbooks created with version 2007 and later with .xlsx and .xls file extensions. You can upload a maximum of file size 75 MB.

Getting data from an Excel workbook with Bold BI

Bold BI allows you to connect Excel files from local storage and cloud storage to the dashboard designer. Let’s see how to connect Excel files from a local disk.

To create the Excel data source from the dashboard designer, open the Data Sources configuration panel and select Excel.

File-type data source listing page
File-type data source listing page

Now the Excel connection window opens as shown in the following image.

Excel configuration window
Excel configuration window

In the connection window, provide a unique name for your Excel data source and choose the Excel workbook from your local storage.

Window for choosing an Excel file
Window for choosing an Excel file

Excel data sources have an additional Import Type feature. Choose Tables to fetch tables from Excel worksheets. Choose Worksheets to fetch an entire worksheet of data along with tables. The import type feature is only available in later versions of Excel.

Excel configuration window
Excel configuration window

Bold BI Excel data connector allows you to connect multiple tables from one worksheet. In the following workbook, Order Details worksheet has two different tables. Now you can connect both tables in the designer.

Excel workbook—multiple tables in one sheet
Excel workbook—multiple tables in one sheet

To connect one or more tables from one sheet, choose Tables in import type. Click Preview & Connect. As a result, a window opens listing the data results as seen in the following image.

Preview raw data window
Preview raw data window

Here each sheet of the Excel file will be shown as a table on the left side of the window. You can view the raw data for each table by choosing the table at the top of the right-side window.

Choose the desired columns and click Connect. Now your selected data will move to the intermediate database specially reserved for your tenant. Once it’s done, you will be directed to the Bold BI’s data source designer. The Excel schema is listed in a tree view on the left side of the editing window. Drag and drop the table you want to analyze and save the data source.

Data source editing window
Data source editing window

Now, let’s see how to connect an Excel file from cloud storage.

Connecting an Excel workbook from cloud storage

To connect an Excel file from cloud storage, select Cloud Storage from the categories list in the data source listing page.

Data source listing page
Data source listing page

You can connect an Excel workbook available in any of the listed cloud storage with Bold BI.

Let’s start to connect a workbook from Microsoft SharePoint.

Select the Microsoft SharePoint connector from the list.  Follow the steps here for authentication with Microsoft SharePoint. After successfully connecting your account, enter a name for the data source and select the site from the drop-down in the window. Choose the file you wish to preview and connect the data.

Microsoft SharePoint connection window
Microsoft SharePoint connection window

Click Refresh Settings in the window to trigger the request periodically and keep the data up to date in the dashboard.

Refresh setting window
Refresh setting window

In the Refresh Setting window, you can schedule the data to refresh hourly, daily, weekly, or monthly. This will automatically trigger a call to the API configured in the data source to keep the data up to date. If you do not want to sync your new data, disable the Enabled option.

Disabled refresh setting
Disabled refresh setting

You can import one or more worksheets and tables from cloud storage, also.

Microsoft SharePoint connection window
Microsoft SharePoint connection window

Click Select. A window opens listing the data results.

Preview window
Preview window

Click Connect. Drag the table and save the data sources. Finally, the created data sources will be added to the DATA SOURCES panel.

Data source listing page
Data source listing page

Joining multiple data sources

Joining data from multiple data sources is a common requirement for various online applications. One example is using data from Google Analytics and Stripe, which is discussed more in the blog post, “Track Website Conversions and Revenue Together.”

You can create a new data source by joining the already-created two data sources. Bold BI allows you to join tables in two or more different data sources provided they were extract-mode connections.

To combine multiple data sources, use the Shared Tables panel at the bottom of the data source designer.

Data source editing window
Data source editing window

This pane displays the data sources you have permission to access. By default, the data sources you created previously for this or any other dashboard will be listed here. You can drag them to the canvas, as shown in the image, to join them.

Data source editing window
Data source editing window

Scheduling data refresh

You can schedule refreshes to keep your data on the dashboard up to date for already created data sources as well.

To update data from an Excel source in local storage, edit the data source. From the data source designer, use the Edit Connection option and select the Excel file, which should be the same source file with updates or a different file name but with the same schema.

Data source editing page—edit connection
Data source editing page—edit connection

With a cloud-stored Excel file, use the Refresh Settings option to create a schedule to move data from your cloud location to an intermediate database location.

Data source editing page— refresh settings
Data source editing page— refresh settings

Note: In cloud storage, you have to either replace the latest file with one of the same name or edit your existing file and save it with the modified content before a refresh triggers. Deleting a file and then uploading a file with same name will make the scheduler consider that file a new one, which results in data loss of the table created from the old file.

Let’s visualize the product and supplier data using a Bold BI dashboard through the Excel data connection. Also, let’s schedule the data for refresh and see it updated in Northwind Products and Suppliers dashboard.

Visualizing Northwind Products and Suppliers metrics

Refer to the following Northwind Products and Suppliers analysis dashboard whose source data comes from an Excel spreadsheet placed in an online storage like Microsoft SharePoint.

Northwind Products and Suppliers dashboard
Northwind Products and Suppliers dashboard

This dashboard showcases product and supplier data through the following metrics, tracking sales performance.

  • Total sales
  • Order placed by country
  • Top 5 products and suppliers by order
  • Top 5 categories sold
  • Least-demand countries based on order
  • Least-sold top 5 categories

The data source is scheduled to update the data every five minutes in the Northwind Products and Suppliers dashboard.

Refresh Setting window
Refresh Setting window

Data is updated in the dashboard every five minutes, as shown in the following image.

Updated data in Northwind Products and Suppliers analysis dashboard
Updated data in Northwind Products and Suppliers analysis dashboard

The Northwind Product and Supplier Dashboard can be viewed in this demo where it showcased with additional metrics. You can download the Excel spreadsheet used for this blog from Google Drive to prepare the dashboard on your own.”

Conclusion

We hope this blog article guides you in preparing Excel data and integrating it with Bold BI. If you have any questions, please post them in the comments section below. You can also contact us by submitting your questions through the Bold BI website or, if you already have an account, you can log in to submit your support question.

Access live 30-minute demo with our experts

Leave a Reply

Share

Access live 30-minute demo with our experts

Related Blogs

Up arrow icon
Live Chat Icon For mobile
Hugo Morris

We are here. Let’s chat!

Live Chat Icon