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.
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.
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.
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.
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.
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.
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.
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!
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.
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.
I recommend that you calculate both SUM and SUBTOTAL for important data columns. Find out why and how in this quick tip blog.
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.
We’ve all been there… a formula either isn’t accepted by Excel (syntax error), or it gives incorrect results (logic error). In this blog we look at these two cases in turn and see how you can best avoid or fix them.
If you have Office 365 then you can use the function STOCKHISTORY. As the name implies, its main purpose is to enable you to retrieve stock prices, but it can also be used to retrieve historical exchange rates. This can be very useful e.g., for converting historical transactions to the reporting currency or assessing the volatility of foreign currency cash flows.
You can easily add icons, stickers, illustrations and more to your Excel file. You can use these to spice up your dashboards to show an icon next to key output figures such as “Sales”.
Did you know? Excel now has a Quick Analysis tool. Select your data and analyse it quickly with lots of options organised in the following areas: Formatting, Charts, Totals, Tables and Sparklines (mini charts in individual cells). Find out how, find out now in my blog with short video.
Cell references in copied formulas are automatically amended by Excel, e.g. to refer to cells in the same relative row or column. Often this makes sense, e.g. you want a SUM formula to always add the values in the same column as the SUM formula. But sometimes this leads to errors! In this blog, you will learn how to avoid such errors.
You have customised a chart, then copied both chart and related data to another area ready for new data. You then amend the copied data, e.g. to show sales of a different business and…. oh! There is a problem…