How to… Consolidate Quarterly or Monthly Data Using Power Query

Do you get regular data files that you need to consolidate/compile together for a report or dashboard? This method will help you keep your reporting files lightweight and portable, while making updating their data as easy as pressing Refresh. The basics can be done in five easy steps. For our example, we’ll be using the

Micro How to… Format Data as a Table

How Step 1. Highlight the data table Step 2. On the Ribbon, open the Insert tab, and select ‘Table’ (Shortcut CTRL + T). Step 3. Hit Ok on the Create Table dialogue box. You should now have a table that looks like the following: Why should you format data as a table? Data formatted as

HOW TO… Excel: Using Index and Match to Create Dynamic Data Tables

This is a follow-up to the last tutorial. Thanks to reddit user /u/small_trunks for noticing my blunder!

HOW TO… Excel: Using SUMIFS to Create a Dynamic Data Table

This is a continuation of the previous tutorial. Due to the volatility of the INDIRECT function, it may be necessary to avoid using it when trying to create large dashboards which will be handling and outputting large amounts of data. So, we’ll need an alternative in these cases. One of the ways this can be

HOW TO… Excel: Create a Dynamic Data Table Using INDIRECT and Combo Boxes

Creating a full and cohesive user experience in an Excel based dashboard can be really difficult if you don’t know where to start. Often, the easiest way to approach the situation will be to utilise Pivot Tables and Pivot Charts, which come with useful tools like slicers to dynamically filter the data with a touch