Help, information, and products for Excel users
with a focus on Business Intelligence


Make light of year-end reporting with Excel Cube Formula Reports

Excel Cube Formula Reporting with Microsoft Analysis Services

Excelcraft's multi-media training kits focus on building cube formula reports and dashboards with data sourced either from Power Pivot models or from Analysis Services Multidimensional cubes/ Tabular models. Learn more...

Power Pivot for accountants and financial managers

The degree of success in analyzing and reporting on accounting data in a BI tool depends to a significant degree on how the data repository is architected. There is not always a clear right answer; there are pros, cons, and compromises. This page has links to articles describing how accountants, finance managers and profgessionals might architect a Power Pivot data model to accommodate accounting and planning data in a way that meets their reporting and analysis needs.

Excel 2013 Data Model

This video will help you learn:

  • if your version of Excel 2013 has it
  • some advantages the Data Model offers

Using Slicers in Excel Cube Formula Reports

Users of Excel 2010/2013 may be familiar with using Slicers to filter data in a PivotTable. But they can also significantly enhance the user experience with reports and dashboards built from Excel cube functions.

Two ways to create a PivotTable from an Excel range using Excel 2010

Learn how to build your PivotTable and get the benefits of Excel's OLAP Tools

Booking reporting journal entries in Power Pivot

After importing account balances or a trial balance into Power Pivot, it is likely that some adjustments will be needed, possible closing adjustments in the preparation of interim financials, or adjustments that have not yet made their way into the general/nominal ledger. This article and video explains how to record these reporting journal entries in your Power Pivot data model.

Small Business Intelligence

In addition to providing self-service, personal BI in larger organizations Power Pivot for Excel is an inexpensive BI tool for finance professionals who manage the accounting and reporting for small businesses using QuickBooks. The section on Small Business Intelligence will includes a growing collection of articles on leveraging QuickBooks data using Power Pivot for Excel.

PowerPivot or Power Pivot?

In the middle of 2013, with the introduction of Power BI, Microsoft changed the name of PowerPivot to Power Pivot. For the most part this web site has been updated accordingly but you may find remnants of the old PowerPivot name every once in a while as you might in Microsoft's web site and software.

Slicing and dicing text values in a PivotTable

From time to time you may want to retrieve text (as opposed to numeric) values into a PivotTable to slice-dice-pivot the text. Or you want to get properties/attributes of a dimension member in a Power Pivot model using Excel cube functions. Here is one solution.

Excel 2013 vs Excel 2010: Differences with regard to Power Pivot and Cube Formula reports

Excel 2013 is now publicly available. If you have the Office 2013 Professional Plus, Office 365 ProPlus, or Excel 2013 Standalone versions and you enable the Power Pivot add-in you can open and modify Power Pivot models from Excel 2010 (but you'll have to upgrade them - and that is a one-way street so be sure to have back-up copies).

Building Actual vs. Budget comparisons into your Excel dashboards - Part 1

The frequent requirement to compare actual performance to budget can be difficult to achieve with a PivotTable because Actual and Budget data often come from two different sources and at different levels of detail. With Power Pivot or the Excel 2013 Data Model you can leverage cube functions to obtain the layout you need. Adding sparkline charts and KPI status/ trend icons is explained in Part 2

Excel Balance Sheet

Cell Mate

Check out Cell Mate for a various wish-I'd-known-that-before video Excel tips, trickery, and shortcuts, many demonstrated with videos.

Locating words or phrases in a worksheet

Conditional formatting of text

This trick can be more useful than Find as it identifies all cells that include the words/phrases at the same time. Great for auditing descriptions in a list of transactions.

Excel 2013 vs Excel 2010: Incompatibilities relating to Linked Tables

If you upgrade an Excel/Power Pivot 2010 workbook to Excel 2013 your Linked Tables may not behave the way you expect.


© 2011-15 Microsoft and Excel are registered trademarks of Microsoft Corporation in the USA and other countries.
Template design by Andreas Viklund

Terms of Use            Privacy            Cookies            Contact