Microsoft Excel

 
 
Microsoft Excel Files can be used as database to store information and this data can be visualized on the dashboards.
 
In order to bring data from Excel to the dashboards there are some basic rules to be followed
 
Organize your data
 
 
 
Rules to be followed
  1. The first row in excel should be the column names
  2. Make sure the column names contain only alphanumeric characters
  3. It should not contain any special characters
  4. The worksheet name should contain only alphanumeric characters
  5. There should be no blank characters
  6. Make sure the first row immediately following the column names contains actual data (this will dictate what type of data the column contains)
  7. If your data is all around in Excel, then create a named range for the section
 
 
You can have  multiple sheets within excel and each worksheet is treated like a database table.
 
For our example, consider this simple Excel file
 
 
It contains one sheet1, two columns (period and amt)
 
Create a connection the Excel File
 
Go to Actions >> Manage Data Sources/Connections
 
 
 
 
 
Define Connection Handle
 
Give a proper name to the connection handle
 
Give the exact path to the xls file on your windows machine
 
Select what type of windows and Excel version you have
 
Click on "Add new Connection Entry"
 
Once clicked it will show the above successful message. If you had a previous connection handle defined with the same name then it will overwrite it.
 
How to Query your Excel File
 
Drag a grid component from left to the right panel
 
 
Right click on the Grid and select "Data Source"
 
 
 
From the drop down, select the newly defined connection
 
NOTE: You will need to refresh the application from the browser if you don't see your connection
 
 
 
 
Enter the Query (SQL)
 
NOTE: Before you execute the query make sure your excel file is closed. Excel files are locked when open and we cannot query it. So close the excel file.
 
Each worksheet is referenced like [sheet_name$] and you can execute quite advanced SQL against this table.
 
For our case the sql would be to list all the columns
 
select * from [sheet1$]
 
The [*] in the above query tells it to bring all the columns in the worksheet
 
 
Click OK to execute the SQL
 
 
As you see the grid is now populated with the data from the sheet
 
How to select specific columns from the Sheet
 
select period , amt
from [sheet1$]
 
The above will result in the same display as above as it contains only those two columns
 
If you select only period you will get this
 
 
 
You can perform aggregate functions as well
 
select sum(amt)
from [sheet1$]
 
 
Notice how it automatically gives a column name. To avoid such non-functional column names we can provide a name in the query itself
 
 
 
Dial Chart Example
 
Drag a dial chart to the center
 
 
Right click to select "Data Source"
 
this time we enter average of the amount
 
 
You can modify the dial by double clicking on it. Refer the Dial section for detailed explanation on how to modify it
 
NOTE: The visualizations behave the same irrespective of what data source you choose. So the techniques shown in other sections such as MySQL or other examples are perfectly applicable to Excel Dashboards. If you need help with writing SQL queries then you can leverage the visual query builder of InfoCaptor Desktop
Use online Web Dashboards in the cloud or download and host your own Dashboards