• 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.
  • Data are necessary for all spreadsheets and it is essential to enter the data efficiently to save time and reduce the risk of error. This video shows you some valuable data entry tips and tricks.