## MID Formula

This post follows on from our post dated 2 June 2017 and deals with extracting data from the middle of a string of information. Previously we used a forename and surname and extracted the left part of the name. Now I've added a middle name so that the string of data is: Surname Forename Middle Name. If I want to extract the middle part of the name, Stephen in this case, we can use a MID formula. Using the formula: =MID(A2,FIND(" ",A2)+1,FIND(" ",A2)) it will extract all the i

## DOUBLE CLICKING TO A FORMULA

Double Clicking in a cell to take you directly to the source of data of the formula is possibly one of the most useful tricks available when working with a spreadsheet with multiple worksheets and many formulae. In the example below there is a formula in cell D2 which references somewhere else in the workbook. By Double Clicking the cell it will take you to the source of the formula. Double Clicking the cell not only takes you to the cell, but in this case it also highlights

## Many (thousands of) Rows

We often analyse files containing many hundreds or thousands of rows, with multiple columns of data. With this amount of data it can be difficult to make sense of the information contained within the file. The first thing we usually do is to reduce the magnification so that we can fit as much information as possible on to the screen, but this can add to the problem of visualising the data. All is not lost and there are a couple of simple, yet very effective, things we can d

## Spreadsheet v Database

A question that we are being asked more and more is “When should I use a database rather than a spreadsheet?” Excel and Access are, in their own rights, powerful tools and enable users to store large amounts of data, run powerful queries and provide analysis tools to slice and dice data, and perform sophisticated calculations that return the information that you need. So here is the dilemma; which application should I be using? Is one application better than the other? Each

## Hash # Errors and IFERROR

One of my bugbears is a report that includes a # error, such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! My eye is drawn to the error and I'm always thinking what other errors are there in the analysis rather than focussing on the information presented (which is probably correct!). In Excel 2010 onwards the IFERROR function is available to resolve this problem, previously and I still see it today, the IF function is used to evaluate the formula, for example =IF

## Excel SUBTOTAL Function

The SUBTOTAL function can be used with a variety of calculations, such as SUM, AVERAGE, COUNT and many more. We will look at the most common used calculation SUM in this example. The SUBTOTAL function is useful when you have a list where there are multiple sum totals included in it. By using the SUBTOTAL function you can add up the individual items and exclude the totals so that they are not double counted. Why Use SUBTOTAL It allows you to return the subtotal of a list, inc