Conditional Formatting – Highlight duplicates.

by JamieNo Comments
There are many ways to format your data in Excel such as data that is greater than or less than a number, data that falls between specific dates, cells that contain certain words or numbers, the list is endless. Many of the above are built in to Excel and can be achieved with just a few clicks but conditional formatting can also be applied as a result of a formula entered. In this example, we are going to highlight cells where any duplicate values appear in our column. In the data below I would like to highlight any names in the Sales Person column that appear more than once. First, select our data range, in this case B2:B11. Then, on the Excel ribbon we go to the Conditional Formatting icon and select ‘New Rule’. The below will appear. Be sure to select the rule type ‘Use a formula to determine which cells to format’. In the box titled ‘Edit the Rule Description:’ type the...
Read More

Grouping Excel Worksheets

by JamieNo Comments
Grouping Excel worksheets can be a huge time saver. It can be used to apply formats and enter things like title headers across all your sheets to ensure they are consistent. Firstly, to select all of the sheets that you want to edit together there are a few options on how to achieve this. Hold down the CTRL key on your keyboard and left Click on the mouse on each tab (worksheet title) you want to select. If you want to select all sheets, select the first sheet, hold down the SHIFT key and select the last sheet in your Workbook. This will automatically select all sheets in between too. Or a 3rd option is to Right click on any sheet title and choose ‘Select All Sheets’ from the menu that appears. Again this will select all sheets in the Workbook. Once all sheets are selected they will turn...
Read More

Formatting Dates for Excel and Google Sheets

by AlexNo Comments
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

by Alex2 Comments
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)

by Alex1 Comment
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

VLOOKUP in Google Sheets

by Alex1 Comment
VLOOKUP in Google Sheets VLOOKUP is a very commonly used formula within business and is easy to use in Google Sheets.  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 Google Sheets and all versions of Excel; these screenshots are from Google Sheets. 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( Google Sheets will then give you a handy...
Read More

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

by Jamie3 Comments
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

by Alex2 Comments
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.

by Alex2 Comments
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.

by JamieNo Comments
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
12345