Formatting Dates for Excel and Google Sheets

When we write down a date, there are many different ways we can write it and it still means the same thing to us: 25/12/2017 Monday 25th December 2017 25/Dec/2017 25.12.2017 25-12-2017 25 Dec ‘17 25-Dec-17 But if the dates are not entered into Excel (or Google Sheets) in a format that it recognises, this can cause problems if we want to use these dates to perform calculations, such as the difference between two dates, or NETWORKDAYS() for example.  This is because dates are stored automatically as a single number, but displayed as a date.  For instance 25/12/2017 is stored as 43094 in Excel or Google Sheets.  Curiously, the date for number 00001 is 01/01/1900 in Excel but 31/12/1899 in Google Sheets.  If the date has been entered in a format that Excel or Sheets recognises, then you can change the date formatting to whatever you like, but changing a format that Excel or Sheets doesn’t like to a format it does...
Read More

Difference between two dates

This is a common task to do in Excel or Google Sheets, and it can be very easy.  It can also begin to get a bit difficult depending on the data you have and what exactly you need it to tell you, so sit tight and let’s begin. As simple as it gets:   You have 2 cells, and the dates have been entered in standard format (i.e. 25/12/2017 otherwise known as DD/MM/YYYY because it is the day, month, then year separated by slashes. Simply click in a blank cell and begin your formula with the equals symbol = , click the most recent date, type the subtract symbol - , then the older date and press enter, and you are done: But what if this isn’t working? ·        This is most likely caused by the dates not being formatted as a date that Excel recognises Click here for our tutorial on formatting dates correctly for Excel ang Google Sheets What if I want to only count working...
Read More

Sorting Data in Excel (Basic)

Link to YouTube video. Frequently, our data needs to be sorted into a logical order so that we can either find things quickly or to rank things in order of value etc. In this example we have a table of cricket records I copied randomly from the internet.  The data has not been sorted in any order I can tell: We might want this sorted in a number of ways:  Perhaps sorted alphabetically by Team or Player name, or maybe ranked in order of number of games; high to low, or low to high.  Let’s go through the steps needed. There are a few ways to do this, the quick and logical way, and an almost equally quick, but less logical way that is faster for any future sorts you might make.  There is also a method to sort by two or more columns at once, but we will leave that for another tutorial, to keep this one reasonably short. 1)     Quick and logical: Let’s say...
Read More

Extracting text strings in Excel using ‘=LEFT()’, ‘=RIGHT()’ and ‘=MID()’

Let’s take a well-known phrase: The cat sat on the mat. Below are 3 examples using ; ‘=LEFT()’, ‘=RIGHT()’ and ‘=MID()’ and the results. These formula’s can be extremely useful for things like separating first and last names in cells, or splitting postcodes or telephone numbers from area codes. Now let’s break each formula down to see how it works. LEFT(text,num_chars) Returns the specified number of characters from the start of a text string. So in our example text is cell B3, and the numbers of characters we returned is 7 (remember to include any spaces as characters.) The result is The cat   RIGHT(text,num_chars) Returns the specified number of characters from the end of a text string. So in our example text is cell B3, and the numbers of characters we returned is 4 (remember to include any special characters, in this case the full stop.) The result is mat.   MID(text,start_num,num_chars) Returns the character from the middle of a text string, given a starting position and length. So in...
Read More

VLOOKUP in Excel

VLOOKUP is a very commonly used formula within business.  Once you realise how powerful it can be, you will begin using it for more and more time-saving exercises that would have otherwise taken ten times longer.  The formula is exactly the same in all versions of Excel and with Google Sheets; these screenshots are from Excel 2013 version. In this example we have a column of reference codes in Column A, and the associated addresses in Column B.  We need to return the correct address when we type in the reference code into cell C1. VLOOKUP has 4 “arguments” (parts that need to be filled in to make the formula work, separated by commas), these are: lookup value, table array, column index, and range lookup.   In this example, we want the result to appear in cell D1.  So begin by clicking in cell D1 and and typing: =VLOOKUP( Excel will then give you a handy “tooltip” to remind you what information it needs to make...
Read More

Using NETWORKDAYS() to calculate the number of working days between two dates.

This is a common question particularly in Construction when you have a start date, and at some point in the distance an end date, and you need to know the number of working days between – so excluding all weekends and perhaps some holidays too.  This can be a time consuming task using a calendar if the dates are far apart, and is compounded if you have a long list of dates you need to compare.  Here’s where Excel or Google Sheets can really save you some time. NETWORKDAYS works exactly the same in Excel as it does in Google Sheets.  The arguments are NETWORKDAYS(Start Date, End Date, [Holidays]).  You will notice that the “Holidays” argument is in square brackets, this is indicating that this argument is optional. In our first example, let’s assume we’re ignoring the holidays but we want to exclude the weekends from our calculation: So we can see it is taking the start date from Cell A2, and the...
Read More

Creating a ‘Named Range’ in Excel.

Using Named Ranges in excel is an efficient way to create your formulas and should become best practice to create clean, easy to follow formulas for you and your colleagues. In the table below we would normally use the following formula to SUM the Net Sales column: =SUM(F3:F9)     This works perfectly fine but when you have multiple data sets you can easily lose track of what formulas are doing. Using a named range can help simplify this. To create a named range for this example we would select cells F3:F9 and type the name we would like to call our range in the Name Box (circled below)       You can now refer to this range in all formulas in the workbook without the need to refer back to the cell range every time. Eg. =SUM(NetSales)...
Read More

Sorting by 2 or more columns at the same time

In an earlier blog post we talked about how we can sort our data in Excel and Google Sheets by alphabetical or numerical order.  This was assuming you only wanted to sort by 1 column (e.g. sort a table of personnel records by Name in alphabetical order). But there are times when you will want to sort by 2 or more columns at once.  The technique is largely the same in Excel as it is in Google Sheets, although the menus are in different locations.  In this example we have a table I randomly downloaded from the Office for National Statistics, as we can see there are a number of ways we might want to sort this data: So let’s say for example we want this first sorted by “NUTS level” (whatever that means…) and then by Index from low to high.  First we select the table:  in Excel we click the “Sort & Filter” button in the Home tab and choose...
Read More

Compare two tables and find records without matches

So in this scenario, you have two lists, and you want to check one list against the other and look for missing values.  There could be many situations you could be faced with this when using Excel or Google Sheets; probably most frequently when error-checking. The way to do this is to use VLOOKUP, either alone or combined with IFERROR, or IF and ISERROR if you want the end result to look a bit more professional. So to begin with, let’s just start by using VLOOKUP (we have a blog post just about using VLOOKUP, so head on over to there to familiarise yourself [LINK]).  The technique will be exactly the same in Excel as it is in Google Sheets. So here we have List 1 and List 2 – we can clearly see that List 2 is shorter, and so therefore some items from List 1 are missing – but which ones? Using VLOOKUP alone: We can start by putting a checking column into...
Read More