Excel Quiz Answers 26 Nov 2014

Excel Quiz Answers

Answers for the quiz on our 26th Nov 2014 Newsletter. To subscribe to the newsletter visit the subscribe page.

Excel Questions from the Newsletter

  1. What is the easiest way to set up a range so that it automatically grows as you add more data for things like graphs, pivot tables etc
    1. Use the Excel TABLES tool
    2. Use a NAMED RANGE with an Offset
    3. Leave extra cells open for more information
  2. Which of the following actions are NOT already available as a formula or can be achieved by combining some formula in Excel
    1. Find the position of a certain character in a cell e.g. a ‘#’
    2. Find the position where the font size is different in a cell e.g. AB
    3. Find the position where the capital letters are in a cell e.g. AdrianMiric
  3. If you are calculating VAT, which is the best formula to use assuming that cell A1 has the selling price excluding VAT and cell A2 has the VAT rate of 14%
    1. =A1*A2
    2. =A1*14%
    3. =A1*$A$2

Learn more about all these features and issues at the Excel courses we offer


  1. A- Although you can use an OFFSET within a NAMED RANGE to achieve the same thing, the Tables feature in Excel is the easiest way to do it.
  2. B- You can use the FIND function in Excel to find the position of any character in a cell and by combining CODE and MID you can work out where the capital letters are. To determine where the font size changes though you need to use some VBA
  3. C- The first one will work fine except if you need to copy it elsewhere. Option B is bad practice and you should avoid including numbers in a formula (what happens if the VAT rate changes?). Option C applies good practice as the VAT rate is in a separate cell and absolute referencing is used to ensure that no matter where the formula is copied to it will always look at that rate.