Videos

  • Standard Chartered was fined £46.5m by the Bank of England over reporting failures, due in part to a spreadsheet error in which a positive amount was included when a zero or negative value was expected. This type of error and more can be prevented using flexible data validation to restrict inputs. In this video I explain how using four examples.
  • Create a fun Excel file to slowly reveal a picture of your choice using the magic of conditional formatting and a snazzy spinner button. In this video I show you how using a Christmas picture, but you can select any picture you like. As a bonus, you can download the completed file for free under downloads. Have fun! PS Thanks to Leila Gharani for the techniques used.
  • Forget VLOOKUP! Use the powerful and flexible combination of the INDEX and MATCH functions for lookups. Available in all common versions of Excel. INDEX defines which column (or row) you want to look in, MATCH finds the correct position - simple, flexible and powerful. In this short video, I show you how.
  • A simple setting change will enable you to jump to a cell formula precedent by simply double-clicking. In this video I explain how to set-up a calculation for clarity and ease of checking, how to make the necessary setting change and how best to use it… all in just a few minutes!
  • Hyperlinks are a great way to let users easily jump to selected sheets or specific locations in your file. In this video I explain how to create hyperlinks using the Excel menu function (insert link) or using a flexible formula.
  • Heat maps can be a great way to present large amounts of data in a compact yet easily understandable way, in this example for new confirmed Corona virus cases. In this video I explain how to import the data in CSV format and to create a heat map of new cases by country and date. The demonstrated Excel file is available under downloads.
  • On 1st April 2020 my business Knott Consulting achieved five successful years in operation. To celebrate, I created a birthday candle simulation, in Excel of course! In this video I show you how, using random numbers and conditional formatting. The file is available under downloads.
  • The press is full of Corona virus stories and graphs, but what is the basis of the projections? In this video I explain the concept of exponential growth and show you how to create your own Covid-19 infection rate simulation. The Excel file is available under downloads.
  • Office 365 has a new lookup function XLOOKUP, which solves several problems with the popular function VLOOKUP. In this video, I show you the main problems with VLOOKUP and how these are solved with XLOOKUP.
  • Filters are very useful for data analysis. In this video I show you some useful tips including how to filter a column using multiple criteria and how to simply record this selection for future use. I also show you how to very quickly filter a list for errors or blank cells.
  • Gary Knott | how2excel - Excel Schulungen Blog
    This video demonstrates an Excel file which creates a fun snowfall for Christmas using a macro with nested loops to carry out actions in every column and every row. You can download the file and an explanatory PDF under downloads.
  • Good formatting can make a spreadsheet easier to use and improve understanding and decision-making. In this video, I show you some valuable formatting tips and tricks as well as a quick data analysis.