Work faster with dynamic arrays
Dynamic array functions are a great way to efficiently analyse and automate your data sets. In this blog I explain three of the best dynamic array functions.
Prevent user errors using data validation
Data validation is a great way to prevent user errors by restricting the inputs that users can make. In this blog, I explain how to set this up including the main options available and an example of when each one is relevant.
Size matters!
The size of the numbers you show in you workbooks can have a big impact on how easy they are to understand and update. In this blog, I show you a great example and give you some simple but effective tips.
Compare workbooks
It can be very useful to compare two versions of the same workbook to see what has changed. In this blog I give you some useful tips to help you do just that.
Prevent Excel user errors
User errors do not have to be simply accepted as unavoidable risks. There are a number of ways you can help prevent them and in this blog I explain my 6 top tips, which you can download as a useful checklist. You also get a link to the youtube recording of a one-hour video webinar I gave on the topic, which got great feedback.
Christmas candles
It's Christmas time again, the season of cheer. You can bring a little festive merriness to your PC by simulating Christmas advent candles using the magic of random numbers combined with conditional formatting. Why not have a go yourself? Or simply download and play with the finished file.
Create dynamic cards and chart titles
You can create dynamic outputs for your dashboards that automatically update when your data changes! In this blog, I show you how to create dynamic KPI cards and chart titles.
Define and use range names
Range names are easy to set up and use and can make your spreadsheets easier to use. In this blog, I explain how to set them up and use them to make formulas easier to write and understand, to easily select cells to be copied and to aid navigation.
Protect your spreadsheets!
To reduce the risk of user error, you can protect your worksheets so that users can only change input cells and perform certain other restricted actions such as select cells and use filters. In this blog I explain the two steps necessary.
Don’t confuse these calculations!
Profit percent, percentage changes and average prices – all three are very useful for business but for each one there are two calculation methods so there is a risk of confusion and error. In this blog I go through all three and in each case I explain the two calculation methods and recommend which one to use.
Custom lists help you enter and sort data
Wouldn't it be great if you could easily complete customised lists of e.g., brands, regions or priorities (high, medium, and low) in use in your company? Or use them to sort data? Well, you can! In this blog, I show you how.
An inexact match can be exactly right!
Normally when you are doing a lookup using MATCH, XLOOKUP or VLOOKUP, you want to find an exact match in your lookup table. In some cases, however, an inexact match can be exactly right, for example to get the sales discount from a simple table based upon any quantity sold. In this blog I show you how using four examples.
Use a BASE corkscrew to plan fixed assets and loans
In a financial model you need to plan balance sheet positions. There are various techniques to do this, and the most suitable technique depends upon the balance sheet position. For fixed assets and loans, a BASE corkscrew can be ideal. Find out more in this blog.
Easy-to-enter sheet and file name formulas
Here’s a great trick using autocorrect to easily enter a formula to show the name of the sheet or file. Set up once, use as often as you want with a simple two-letter entry. Check it out!
Bar chart with values above/below minimum
You have, say, plan bank balances for different scenarios and also a minimum or target value. Wouldn’t it be great, if you could show these clearly in a chart and the bar colours would automatically change, depending upon whether the scenario value was above or below the minimum? Well, you can! In this blog, I will show you how, step-by-step.
All models are wrong, but some are useful
A model is a simplified representation of reality. The act of simplification means that all models are wrong because they do not reflect all elements of reality. But if a model is good enough for a particular application, it can nevertheless be very useful. Find out more in this blog including useful tips.
Calculate sums and subtotals
I recommend that you calculate both SUM and SUBTOTAL for important data columns. Find out why and how in this quick tip blog.
Excel space tip
Did you know, you can add spaces to formulas to make them easier to read? Well, you can, and you should! 😊 Learn more in this quick-tip blog.
