Using FIND() to separate text strings.

In this example we are going to use the Excel function FIND() combined with LEFT() to separate some text. If you aren’t familiar with LEFT(), RIGHT() and MID() you should visit our tutorial on this first before diving into this one. You can find this by clicking here or using the Blog search. A common task is peoples full name (both first name and last name) being populated in one single cell. You may want to separate these in to single cells, a column for first name, a column for last name. So, let’s take our example below. As you can see we have 4 names of varying length in characters. In order for Excel to know where to split the names we must first find out where the space “ “ is between the first and last name. For example the name Joe Bloggs, the space is character number 4. To do this we use the FIND() function. There are 3 arguments in...
Read More

The fastest and easiest way to make a graph/chart in Excel

How do I make a graph or chart in Excel? If you've never made a graph or chart in Excel then it can be quite daunting the first time, but Excel has made it very easy to make a professional looking graph with just a few clicks. The first stage is to make a spreadsheet of your information; the important thing here is to make sure you put column headings because these will be used as the Axis names on your graph.  Also, making sure you format your numbers correctly (e.g. if it's in pounds then format 10000 pounds as £10,000) as this will also give Excel some clues as to what is the best format to use in the graph. Then highlight all your data, including the column headers, click on the Insert tab, and "Recommended Charts" and Excel will give you a gallery of the charts it thinks are the most appropriate.  Select one you like, click OK, and Excel will...
Read More

Using the Excel Array() Formula.

Ok first things first, the example below is very basic and there are alternate methods available to get the same result. However, for the purpose of this example we need to keep it simple and this will hopefully make it easier to understand. There are some very specific rules about the Excel array() formula it is important to know before starting: You must enter an array by pushing Ctrl+Shift+Enter. You cannot add the {} (braces) that surround an array yourself by typing them in on the keyboard. You cannot use an array formula on an entire column. Each argument within an array must have the same amount of rows and columns. Another important point to add is that array formulas can significantly slow down your work. Eg. Opening, saving, recalculating. All that said, array formulas are a very powerful tool and allow much more complex calculations to be done than standard formulas. OK, let’s get to our example. In our sample data below we...
Read More
Making an auto-refreshing PivotTable in Excel

Making an auto-refreshing PivotTable in Excel

Making an auto-refreshing PivotTable in Excel This topic may be a little advanced, but if you follow this through step by step, even if you don't understand every formula and the line of VBA code, you will be able to convert any PivotTable into an auto-refreshing table.  For this you will need to know how to make a PivotTable, and how to record a macro. Pivot tables are fantastic for quickly analysing information from a larger data source, but often we add data to the data source and either forget to increase the Data Range that the PivotTable is referring to, or forget to refresh the table.  This solution addresses both of these problems, meaning that when you switch to the PivotTable tab, you will be looking at the latest information every time. This technique has two parts; firstly an auto-expanding named range which provides the range for the PivotTable to refer to, and secondly a single line of VBA code to refresh...
Read More

Using the Excel function =SUBTOTAL()

In the example below using the formula =SUM(E4:E10) is a perfectly acceptable way to get a total for the data displayed. Let’s focus on Gross Sales.     …But what if you applied a filter to show only the Area ‘North’?       As you can see from the graphic above, the Gross Sales total does not change when a filter is applied.   Instead use the built in function  =SUBTOTAL This is broken down by: The function number required. In this case the number ‘9’ is the SUM function. You can also choose from a variety of other options including ‘MIN’, ‘MAX’, ‘COUNT’, and several others. The range of cells you want to evaluate. In this case E4:E10 (The unfiltered list) So, to calculate Gross Sales so that the total...
Read More

Using =TRIM() in Excel.

The Excel function =TRIM() removes extra spaces from the beginning, middle or end of text strings. It also removes unwanted line breaks leaving just a single space between words. This is especially useful when data is exported from other applications and the output has extra line breaks and/or spacing. The example below illustrates the formula. The syntax for =TRIM() is: =TRIM(text) The argument (text) is the text from which to remove the extra space. There are many other built in functions in Excel for cleaning up , or extracting specific parts of a text string. Check out our tutorial on extracting text strings using ‘=LEFT()’, ‘=RIGHT()’ and ‘=MID()’ by clicking here. ...
Read More

Using the Excel Function OFFSET() to return a range.

  OFFSET() returns a reference to the portion of a range of cells or single cell based on 5 parameters. Sound like nonsense? Hopefully a simple example will be much easier to follow.     Let’s break down the formula =OFFSET(A1,5,2,2,3) in our example. Cell A1 is the reference we will offset from. 5 is the number of rows to offset from reference cell A1. 2 is the number of columns to offset from reference cell A1. 2 is the number of rows to return. 3 is the number of columns to return.   Remember, OFFSET() returns a range so make sure you pass the value to another function like SUM that accepts ranges or your result will be #VALUE! Using the same example let’s populate our range with some numbers and combine OFFSET() with SUM().   ...
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