When should I stop analysing supermarket data in Excel?

Never?!

Excel remains a great tool for many reporting and analysis purposes – such as building summary reports or conducting a simple ‘what-if’ analysis of two or more scenarios. There are, however, many cases where regular Excel use may not meet all of your needs, including when:

  • Your data volume exceeds what you can reasonably handle on your personal computer
  • You want to analyse mixed data grain
  • You need to combine data from multiple sources
  • You want to provide applications to casual users

In these cases, other tools and techniques may offer more powerful, speedy and insightful analysis, including:

Let’s explore some of these approaches and common scenarios.

How do I analyse large data volumes in my spreadsheets?

Once data volumes reach 100,000 rows or more, traditional spreadsheets like Excel become very unwieldy. Whilst you can manage even 1m rows of data in Excel, it’s rarely a pleasant experience. You may think that you will never reach this sort of scale, but if you want to detect and react to daily trading patterns for tens of products in hundreds of stores, you can reach 1m rows surprisingly quickly. 10 products sold in 1,000 stores will generate 10,000 rows of ‘time-down’ data per day – so anything more than 3 months (90 days = 900,000 rows) will present a problem.

To understand, react to and predict long-term product demand you need to collect, manage and analyse millions of rows of sales, stock and service data over 2+ years.

Modern data analytics tools are comfortable handling billions of data rows, so the natural path is to move from a desktop spreadsheet to an online equivalent. For example, you can connect Google Sheets to Google BigQuery (Google’s multipurpose data platform) to explore, summarise, analyse and report over millions of data rows comfortably. Whilst Google tools can be used this way, configuration requires considerable technical expertise and it can be challenging to generate results quickly. 

An alternative spreadsheet experience comes from Sigma Computing, whose analytics platform supports huge data volumes, is accessible in real-time, and has a familiar Excel-like interface, including support for much of the Excel formula language and interactive visualisations. Sigma is gaining popularity with spreadsheet users and has a growing user base in consumer packaged goods.

If your IT, or data, team already uses a cloud data platform (like BigQuery, Databricks, Snowflake etc.) and can provide you with automated tools to load your data then you can use your preferred business intelligence tool (like Looker, PowerBI, Qlik, Tableau, ThoughtSpot etc.) to interrogate the data accessible in your data platform. The combination of flexible business intelligence tools plus an enterprise data platform used to be the preserve of global food producers, but the rise of cloud services since the mid-2010s means that any business of any size can afford to use this technology to manage and analyse huge volumes of data.

How should I analyse mixed-grain data?

We refer to data grain as the lowest level of detail in your data. For example, data about daily product sales across an entire retail estate is considered Item x Day but would be described as Item x Store x Day if that same sales data is split across every store in the retailer’s estate.

Mixing data grain presents challenges for all analysis tools; it’s important to ensure data isn’t accidentally repeated, double-counted or misconnected. Some of the most common challenges encountered when connecting and analysing mixed-grain data include:

  • Over-reporting aggregated values
    When joining low-grain data to high-level (e.g. store-level sales to depot-level stock) it’s easy to forget that low-grain data will be replicated for each high-grain data row

  • Missing connected data
    It’s possible that high-grain and low-grain data don’t cover the same underlying data. For example, a daily depot stock summary may only contain information about items present in the depot, and daily store sales data may only list those items that sold in-store that day; linking these datasets may result in missing sales (for items with no stock) or stock (for items with no sales)

Most modern data analytics tools assist in handling mixed-grain data. Moving beyond the ubiquitous VLOOKUP of Excel towards a more managed, reliable model within a business intelligence tool, will help to minimise risks from mixed data and ensure that you can make the most of the insights available.

How can I analyse data from multiple sources?

As soon as you want to analyse data from various sources you will encounter not only the mixed-grain data challenge (see above) but also discover further matching issues:

  1. Data column terminology
    Both measures (e.g. sales value, sales volume, stock level etc.) and descriptors (e.g. product code, category, store number etc.) may have different names in different data sources.
  2. Data semantics
    Worse still, similar-sounding columns may represent subtly different concepts – for example, does “Availability” mean on-shelf availability (available for shoppers to purchase) or in-store availability (available in-store to be merchandised on the shelf)?
  3. Descriptor coding
    Even if two systems use the same column name to mean the same concept, they may employ different coding systems. For example,  “Product Code” could be a proprietary code in one data source and a GTIN in another.

Thankfully, standard practices have emerged to address these and related issues:

  1. Basic data dictionary
    A simple text document that describes, in human-readable form, what each measure or metric means. Many organisations author such documents in a word processor or wiki site to provide a centralised record of definitions and how the organisation interprets the data it receives.
  2. Advanced data dictionary
    More advanced data dictionary solutions will be both human- and machine-readable; enabling software applications to interrogate and interpret data without data-source-specific coding. This type of solution moves well beyond simple spreadsheets, although some organisations – and enterprising spreadsheet jockeys – attempt to build complex multi-layer, multi-document spreadsheet systems to manage this complexity. In most cases, such complex spreadsheet systems increase the organisation’s risk and dependency on key individuals and are best avoided in all but the very smallest businesses.
  3. Lookup tables
    Cross-reference ‘lookup tables’ can link product or store codes (reference data) across two or more data sources. In the simplest situation (with just 2 differing data sources) a 2-column lookup that expresses the code from source A and relates it to the equivalent in source B will suffice. Be aware, however, that this basic model starts to break down once you encounter multiple pack types (consumer packs, shipping cases, pallets etc.) or need to map reference data across various data sources – for example, your internal product coding (from SAP, Microsoft Dynamics etc.) to many different customers in many channels. 

These techniques are best implemented in a modern data platform rather than in spreadsheets. Spreadsheets tend to perform slowly when handling very large data sets and the row-by-row nature of most spreadsheet formulae present a far greater risk of inconsistency than a column-based data platform. 

How can I provide casual users with analytical applications?

It’s possible to build simple end-user applications in Excel – in particular, it’s pretty easy to create attractive interactive dashboards for simple reporting tasks. These are ideal for casual users because Excel is such a widely used and available software application.

There are many great tutorials and training programmes that will help you to build Excel-based applications. YouTube is your friend here – try searching for terms like:

However, as you want to add more data, or more functionality, to your reporting dashboards – in support of specific tasks and workflows, for example – it can become cumbersome and limiting to use Excel. There are many different ways to approach this, depending on your preferences and the type of application(s) you want to build, including:

Low-code / no-code application builders like Appian, AppSheet, OutSystems, Power Apps, QuickSight etc.