Overview
QQube makes it possible to drag and drop QuickBooks fields into Excel by using the Select Assistant from the QQube Excel Add-In.
The Add-In handles the underlying table relationships through Microsoft Query so you do not have to link tables manually or learn their relationships before building a report.
For advanced Excel users
The QQube Excel Add-In acts as a front end for Microsoft Query. Advanced users can review and work with the underlying queries when necessary.
Traditional Excel compared with database data
Traditional Excel
Traditional Excel workbooks commonly place manually entered data and formulas in separate cells.
Advanced users may also use array formulas, macros, and VBA to create report-like results.
Excel connected to a database
Data returned from a database, including QQube, is not connected to individual, unrelated cells.
Instead, the data is returned as a contiguous block of rows and columns. Sorting or filtering affects the entire data set rather than one isolated cell.
If every database value were retrieved into a separate cell, each cell would require its own query, filter, and dependency. That approach would be impractical for thousands of records.
How QQube interacts with Excel
The QQube Select Assistant can return data to Excel in two primary formats:
Dynamic Range
A Dynamic Range returns the selected QQube fields as a contiguous Excel list.
The range expands or contracts automatically when the number of returned rows changes.
PivotTable
A PivotTable also begins with fields selected through the QQube Select Assistant.
Creating the report requires two steps:
- Select the fields you want to retrieve.
- Place each field in the appropriate PivotTable area.
Dynamic Ranges compared with PivotTables
| Feature | Dynamic Range | PivotTable |
|---|---|---|
| Row Changes | Expands and contracts automatically. | Refreshes from the underlying QQube data. |
| Sorting and Filtering | Supports sorting and advanced filtering. | Supports interactive filtering and rearrangement. |
| Subtotals | Excel Data-tab subtotals cannot be added to the dynamic list. | Creates subtotals automatically as row fields are added or rearranged. |
| Best Use | Detailed lists, filtering, formulas, and row-level analysis. | Summaries, grouping, subtotals, and interactive analysis. |
Dynamic Range limitation
Dynamic Ranges update automatically when the returned row count changes and support sorting and advanced filtering.
However, Excel does not allow the standard Data > Subtotal feature to be applied to the dynamic list.
PivotTable limitations
PivotTables provide automatic subtotals and are well suited to summarizing QQube data in Excel.
They have two important limitations:
- Standard PivotTable calculated fields cannot perform the same conditional, relationship-based, or dynamic-filtering calculations available in Power Pivot.
- Row-label fields, such as Customer, Item, or Account, must appear in the leftmost columns. Measures must appear in the columns to the right.
Advanced capabilities beyond standard Excel
Power Pivot
Power Pivot supports larger datasets and advanced calculations that are not possible with standard PivotTable calculated fields.
For example, inventory models can display Quantity on Hand beside sales or consumption measures for multiple date periods.
Power BI
Power BI supports interactive visual reporting while still providing familiar tables and matrices for detailed analysis.
Learn more about QQube and Excel Power Pivot.
Learn more about QQube and Microsoft Power BI.
Expected result
Use the QQube Select Assistant to retrieve QuickBooks data into Excel as either a Dynamic Range or a PivotTable.
Choose a Dynamic Range for detailed row-level work. Choose a PivotTable for summaries, grouping, and automatic subtotals. Use Power Pivot or Power BI when the report requires more advanced calculations or interactive visual analysis.
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article