• DE
  • EN
  • Blog

    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.
    Read more

    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.
    Read more

    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!
    Read more

    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.
    Read more

    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.
    Read more

    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.
    Read more

    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.
    Read more

    Help! My formula doesn‘t work!

    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.
    Read more

    Easily get exchange rates and stock prices in Excel

    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.
    Read more

    Spice up your dashboards with icons

    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”.
    Read more

    Analyse quickly with the Quick Analysis tool

    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.
    Read more

    Fix your formulas!

    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.
    Read more

    The problem with copied Excel charts

    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…
    Read more

    Excel calculation options and shortcuts

    Did you know that for large, slow workbooks you can switch the calculations to manual and only recalculate when necessary? This can speed up working with your file dramatically. To recalculate, there are useful shortcuts you should know.
    Read more

    Gary’s Golden Ground Rules

    Nearly 90% of spreadsheets contain errors and many are also hard to use! These can be expensive in terms of money, time and reputation. So how can you avoid these issues? For my new book I went back to basics and completely overhauled my best practice advice. The result is my new, improved Gary’s Golden Ground Rules.
    Read more

    Do you COUNT?

    COUNT = Calculate Once, Use Numerous Times. If you need to use a value multiple times, you should calculate it just the once and then link back to that value in all subsequent calculations. This can make your calculations shorter, easier to understand and faster.
    Read more

    Complete Excel tasks in a second

    Pit stop times have been dramatically reduced over the years by following a set of six principles. I have adapted these to Excel and explain them in this blog. If you follow them, I estimate that you can easily save 10% of your time working in Excel. If you worked full-time in Excel, you would save four hours a week or 22 days a year!
    Read more

    Save time with the Quick Access Toolbar

    You can perform many tasks faster if you set up and use the Quick Access Toolbar. In this blog you will learn how to set up your Quick Access Toolbar so you can speed up your work!
    Read more