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

Lightweight DND 5th Edition Item Tracker (Excel)

Taking inspiration from this old blog post from Rotten Pulp, here’s an (optional) item tracker that you can use to keep a more immersive record of what your character has equipped and where. The tool itself covers most of the rules you’ll need to be aware of for this new ruleset. I took some guidance

Lightweight Excel DND Dice Roller

To compliment my lightweight offline Excel DND character sheet, I’ve created a dice roller to be used when playing the game. It can be used completely offline, and should run on basically all versions of Excel (provided macros can be enabled). Please feel free to use this and share as required: For more Excel savvy

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

Lightweight Excel DND Character Sheet

Last year, after starting an online Dungeons and Dragons 5th Edition campaign with friends to get through the lockdown, I made an Excel Character Sheet so that my players could keep track of their in-game character stats and items. Please feel free to use this and share as required: Need a dice roller to go

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 England

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 within

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 the

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 data