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
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

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

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

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

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