BDI is proudly partnered with Sharperlight. Through using the Sharperlight Excel Add-in, users can easily access the Sharperlight Query Builder and Explorer from within the spreadsheet software.
Sharperlight provides reporting capabilities for the whole organisation, not just the finance team. The tool sits over ERPs such as SunSystems, therefore giving each user a complete picture of an organisation’s data. Sharperlight also allows users to quickly create and edit reports without an expert consultant or an IT team. This includes drawing data into Excel, too.
Through our guide, BDI hopes to provide useful support for Sharperlight Excel Add-in users.
Table of Contents
Join the BDI mailing list
Keep up to date with the latest events, webinars, and product launches from BDI.
Sign UpWhat is the Sharperlight Excel Add-in?
The Sharperlight Excel Add-in is an XLL file which seamlessly integrates Sharperlight with Excel. As a result, the Query Builder can create intelligent formulas within a spreadsheet workbook. The data values consequently refresh directly from a Datamodel and its underlying application database.
Users can access these tools through the new menu created within Excel when importing the add-in.
Conveniently, the Add-in automatically installs alongside the initial Sharperlight installation on the condition that Excel is already on the user’s computer. This includes the 2003, 2007, 2010, 2013, and 2016 versions.
What can the Excel Add-in do?
A key benefit of using the Excel Add-in is the ability for users to quickly access the Sharperlight Query Builder and Explorer from within Excel. The Query Builder interface allows formula construction for queries anchored within worksheets. These formulas additionally support cell referencing and automatic recalculation, meaning the result set refreshes as filters change or a recalculation is forced.
Some Datamodels also allow Excel to Writeback into the Sharperlight and ERP database. The author controls the Datamodel from within. They decide how the Writeback interfaces with the production system, as well as establishing security restrictions to control Writeback permissions for specific users.
Applying formulas on query filters and output values performs various statistical, logical, and mathematical operations. The Sharperlight formulas within Excel also allow Drilldown into data. Drilldown Summary and Detailed options are available when right-clicking on table formula entries,
Why use Sharperlight in Excel?
One strong appeal for spreadsheets is their ease and responsiveness in calculations and analysis. Multiple spreadsheets can reference multiple formulas, pulling data across and updating values as they change within a sheet. Just a few cell references can drive a workbook.
Sharperlight supports this capability, with the Query Builder supporting both relative and absolute formula references as well as named ranges. The Automatic Lookup function also easily allows users to change the cell referenced values, without relying on memory or searching through the Query Builder. Sharperlight formulas follow the same complex calculation hierarchy of dependent cells which trigger calculation logic.
For users who are already comfortable with data analysis through Excel, using the Sharperlight Excel Add-in allows them to continue to use a familiar platform but with added enhanced capabilities. Rather than manually updating data, potentially causing errors, Sharperlight automatically refreshes datasets. This creates accurate analysis, therefore improving the process for users.
What formulas can I use because of the Sharperlight Excel Add-in?
As a result of using intelligent formulas, Sharperlight anchors the extraction of data into Excel. The two main query formulas which support this are the Table Formula and a Value Formula.
Table Formula
This formula returns a table data set subject to the arguments in a query. This data set may span multiple rows and columns. Typically, the query name will reference an existing query template stored elsewhere in the workbook. However, some simple queries may be populated directly in the formula.
You can move Table Formulas anywhere on a worksheet after creation. They will continue to function if the Excel Table name and query template remain, even if moved to a different sheet. Query Builder’s formatting options only affect web content published to the Web Channel; Excel’s native controls define the formatting options.
Sharperlight’s Table Formula generates tables in Excel’s logical object format. This object therefore has a default name, and rows and columns can automatically change size to reflect the data.
As a result of building off the native table logic object format, tables can be stacked. We recommend building tables using a common query template with different filter ranges for each table. The Table Formulas will flow relative to each other. Creating a table without a header or footer row, positioned below another table, will override the sub-total issue which can sometimes occur.
Value Formula
This formula returns a single value into a worksheet cell, subject to the arguments used to filter the query. Like with the Table Formula, most formulas have a named query pre-existing in the workbook. However, formulas can also entirely contain simple queries.
The standard Excel method (the formula bar) allows editing and updating for cell references in a Value Formula. Changing cell references from relative to absolute (and vice versa) is easy. The change only affects the edited cell, and the seed query remains the same within the Query Manager.
Value Formulas offer a variety of benefits, such as:
- Provides the spreadsheet designer with complete control on layout and formatting for a report. However, this compromises the cells ability to expand and retract with the filtered range.
- Ideal for static summaries.
- Easily integrates into existing layouts.
- Provides a self-contained formula in a single cell.
- Easy combination of data from multiple sources.
- Greater control on cell formatting and layout.
What happens if a report is sent to someone without the Sharperlight Excel Add-in?
Users see limited information if sent a workbook containing Value or Table Formulas when they do not have the Sharperlight Add-in. They will see the formulas and the last calculated result, but the data cannot be refreshed. Attempts to edit the formula will return the value #NAME? to indicate the formula is not valid.
If Sharperlight workbooks are distributed to a non-Sharperlight audience, then users should choose the ‘Detach Formulas’ option in the menu. This will strip out all Table and Value formulas without compromising standard Excel formulas. Table Formulas have a ‘Detach Table Formula’ option which removes the anchor formula and converts the table into static data. This prevents a forced table refresh, which would happen if the formula was manually removed.
Top tips for using the Sharperlight Excel Add-in
Using the Sharperlight Excel Add-in leads to an easier experience of analysing your organisation’s data. However, there is a risk that a poorly documented, large, and complex workbook limits a user’s ability to access information, just like with regular spreadsheets. These simple tips will ensure your Sharperlight Excel workbook is easy to use and functional for your organisation:
Conclusion
In summary, using the Sharperlight Excel Add-in creates an easy way for users to analyse their important business data. Data is accurately updated and refreshed to reflect the latest snapshot of an organisation on a platform familiar to users. Incorporating native Excel tools, Sharperlight provides an integrated solution for users seeking easy analysis.
Installing the Sharperlight Excel plug-in is simple. BDI’s business experts are happy to work with your organisation to train staff in how to use the software, and provide support when needed. Sharperlight on a whole is a positive addition to many organisations (regardless of size and function) and increases accuracy whilst also saving time and money.
If you think Sharperlight is a good fit for your organisation, or you would like to know more, please get in touch. Our team can show you a demo and talk through your business needs to find the best business intelligence tool for your needs.
Ready to start?
Our team of data intelligence experts are ready and waiting to work with your organisation
Recent Comments