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 theContinue reading “How to… Consolidate Quarterly or Monthly Data Using Power Query”
Category Archives: How to
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 asContinue reading “Micro How to… Format Data as a Table”
Quick Steps to Make Effective Data Visualisations
This is an example of the default settings for a bar chart in Excel. This is functional but could be far more effective; here are my main steps I use to create quick, effective data visualisations.
HOW TO… Excel: Create a hierarchical series of drop down boxes
As a result of prodding around the excel subreddit I found a query from user innocuous_gorilla about creating drop downs that are dependent upon each other. Due to working at NHS England where we have a massive array of organisation types and hierarchies, I quickly learnt how to do this! For example, in NHS EnglandContinue reading “HOW TO… Excel: Create a hierarchical series of drop down boxes”
HOW TO… VBA: Compare VBA Combo Box Values with Their Lists
Preamble So, a recent issue I’ve had using VBA to create UserForms to handle data input is data validation. Throughout my UserForm I’ve included a variety of Combo Boxes that contain lists of information the user can select. In most instances I would like the user to have inputted exactly one of the options withinContinue reading “HOW TO… VBA: Compare VBA Combo Box Values with Their Lists”
HOW TO… Excel: Create a Dynamic Data Range for a Chart
Continuing the theme of automatically updating charts, we’re going to look at how to make a chart deal with a dynamically changing data range. This is useful for situations where you have multiple measures that each have a different number of independent variables (the variables plotted on our x-axis, which was the month of theContinue reading “HOW TO… Excel: Create a Dynamic Data Range for a Chart”
HOW TO… Excel: Create an Automatically Updating Chart
Following on from our last series of HOW TO… Excel, we’re going to expand the concepts used to create the dynamic data table and create another sheet for the same workbook that will be the home of an automatically updating chart! Unlike pivot charts, this will be able to update as soon as new dataContinue reading “HOW TO… Excel: Create an Automatically Updating Chart”
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 beContinue reading “HOW TO… Excel: Using SUMIFS to Create a Dynamic Data Table”
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 touchContinue reading “HOW TO… Excel: Create a Dynamic Data Table Using INDIRECT and Combo Boxes”
Static Interest 