How do I Divide in Excel?

How do I Divide in Excel?

https://youtu.be/fe34GZCrIwM Link to YouTube video How do I Divide in Excel? We've covered multiplication and subtraction in previous tutorials and videos, but how do we divide in Excel?  This is another commonly searched-for question on Google, and the technique is the same in Excel as it is in Google Sheets.  Once you're familiar with this technique you will have a much better understanding of how Excel works. So let's start off with a very simple calculation; 4 ÷ 2. The number 4 is in cell A1, and number 2 is in cell A2: To begin with, you need to understand that division in Excel is a kind of formula, and whenever you type a formula in Excel you must begin it with the equals symbol =, and the formula is after the equals sign.  Secondly, you need to know that the symbol for divide in Excel is not ÷, it is / (the forward-slash symbol).  So if we want the answer to this sum to be in...
Read More
How do I multiply in Excel?

How do I multiply in Excel?

https://youtu.be/zw8P3eXVd40 Link to YouTube video How do I multiply in Excel? To an Excel expert, this is extremely easy - but it's a commonly searched-for question on Google, so let's get stuck in. Once you're familiar with this technique you will have a much better understanding of how Excel works. (The technique is exactly the same in Google Sheets). So let's start off with a very simple calculation; 4 x 2. The number 4 is in cell A1, and number 2 is in cell A2: To begin with, you need to understand that multiplication in Excel is a kind of formula, and whenever you type a formula in Excel you must begin it with the equals symbol =, and the formula is after the equals sign.  Secondly, you need to know that the symbol for multiply in Excel is not x, it is * (the star symbol).  So if we want the answer to this sum to be in cell A3, we click in cell...
Read More
How do I subtract in Excel?

How do I subtract in Excel?

https://youtu.be/EQKOczg9l6I Link to YouTube video. How do I subtract in Excel? This is another very simple question to answer, but a commonly searched-for problem, so you're not alone if you're stuck on this.  The method is exactly the same if you want to know how to subtract in Google Sheets. Let's take a very simple example, we have 100 and we want to subtract 10.  The number 100 is in cell A1, and the number 10 is in cell A2: To begin with, you need to understand that subtracting in Excel is a kind of formula, and whenever you type a formula in Excel you must begin it with the equals symbol =, and the formula is after the equals sign.  So if we want the answer to this sum to be in cell A3, we click in cell A3 and type: =A1-A2 and then press ENTER on your keyboard: And there we have it, the answer to 100 minus 10 is now in cell A3: So in a...
Read More

How to rename a worksheet (a tab) in Excel or Google Sheets

Renaming a worksheet (or a tab as I prefer to call them) in Excel or Google Sheets is very easy to do; In Excel, simply right-click the tab and select Rename: In Google Sheets, you left-click the tab (instead of right clicking as you do in Excel) , again just select Rename: Or as a shortcut, in either Google Sheets, or Excel, just double-click the tab name and the name will become highlighted, just overtype with your new worksheet name and press return. Limitations: Excel will prevent you from using certain characters in a worksheet name such as  / ? * : [ ]  and you cannot begin or end an Excel worksheet name with an apostrophe '   Also you cannot name a worksheet "History" as Excel reserves this name internally.  Although none of these restrictions reply in Google Sheets.  You are limited to a name length of 31 characters in Excel or 50 characters in Google Sheets.  Check out our video for a practical demonstration: ...
Read More

Using INDEX / MATCH instead of VLOOKUP

Many of you may be familiar with using VLOOKUP to search for data on your spreadsheets; it's easy to use and easy to remember the arguments for the formulas.  So why should you switch to using the INDEX/MATCH combo instead?  Using INDEX/MATCH means you no longer have to be looking up on the leftmost column of an array and returning a value from somewhere on the right - instead you have the freedom to have your table set up with the columns in any order.  These functions work exactly the same in Excel as it does in Google Sheets. So let's jump into how to use it: The formula is nested into two parts, for the first part of the INDEX function, just highlight the array you want to get your final result from, then the second part of INDEX (after the comma) is MATCH, just click the cell that contains your lookup value, comma then highlight the array where you can...
Read More

Using the INDIRECT() formula to refer to a cell reference from a text value

Every once in a while I come up against a challenge where I have to refer to data from multiple sources, but they're not already handily linked.  BUT I may have a handy list of names or references in some kind of text format.  (Note, the formula is exactly the same in Microsoft Excel as it is in Google Sheets; I have used Excel for these screenshots). For example, I may have a workbook with multiple tabs formatted the same e.g.:  And a summary table ready to fill in:  We know that we could quite easily click in cell B2 on our summary sheet, type the equals key, then click on cell B2 on the Lancaster tab to fill in that cell.  And dragging the formula down would fill in the rest of the months.  But if we only did this, we'd need to repeat the process for the other four tabs.  No big deal for this example, but these kind of workbooks...
Read More

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

VLOOKUP in Google Sheets

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

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
12