November 2020

Online Google Sheets Training

Monday, November 30th, 2020

Online Google Sheets Training

We provide on-site Google Sheets training to users of all levels, from beginners who require basic spreadsheet skills to experts who wish to get the best out of the more advanced features. We also provide transition training for those who are moving from Excel.

On Site Google Sheets Training

Monday, November 30th, 2020

On-Site Google Sheets Training

Online Microsoft Excel Training

Monday, October 26th, 2020

Online Microsoft Excel Training

We deliver on-site Excel training for users of all levels. We can help everyone from beginners with little experience of spreadsheets, to expert users looking to build complex macro-enabled workbooks that link to external data sources.

Online Microsoft Office Training

Thursday, October 22nd, 2020

Online Microsoft Office Training

We design and deliver bespoke Microsoft Office training courses tailored to your requirements. We can cover any version from Office 2010 to the latest 2019 release (including Office 365), depending on which is used in your organisation.

On Site Microsoft Office Training

Thursday, October 22nd, 2020

on-site Microsoft Office Training

On Site Microsoft Excel Training

Thursday, October 22nd, 2020

On-Site Microsoft Excel Training

Find Blank Cells In Excel

Wednesday, November 9th, 2016

Written by Jane Hames

I am often asked how to find blank cells in Excel within a specific range of data. Just having a look through your data could be time consuming, but there is a quick and easy way to find blank cells in Excel. Here’s what you need to do:

Select the data that you want to check.
From the Home tab on the ribbon, click on the Find & Select button.

Find blank cells in Excel

Select Go To Special and you will see the following box:

Find blank cells in Excel

From the Go To Special Box, click on Blanks.
Click on OK, and any blank cells in your range will now be highlighted for you.

Find blank cells in Excel

If you would like to learn how to manage data in or just to learn more about Excel or Microsoft Office in general please do get in touch.

-->

Written by Jane Hames

Read more on Find Blank Cells In Excel…

Go to linked cells in an Excel formula (Quickly!)

Monday, July 4th, 2016

Written by Jane Hames

If you have set up a formula in one workbook that uses cells from a different workbook, the two workbooks become linked. It is useful to be able to go to linked cells in an Excel formula, and it would be great to be able to do that both quickly and easily!

Here’s the slow way: If you are on the cell that contains the formula, you can use Trace Precendents, double click on the dotted line and then use Go To to go to the cells used in that formula. go to linked cells in an excel formula

There is quicker way! It takes several clicks to get there using the method described above, but there is a very quick keyboard shortcut that does the same thing.

If you have tried to use Google to find this shortcut key, you might have trouble. I found it very tricky to find a search phrase that explained what I wanted to do. Asking amoungst a group of Excel users it seemed that this shortcut was a mystery until one of the group had a sudden recollection of what the shortcut is. Now, those of you who know me and/or follow my posts, I certainly don’t believe in keeping these kind of software secrets as secret, so here’s what you need to know:

Click on the cell that contains the formula.
On the keyboard, press, CTRL [
You will taken to the first cells used in the formula. (Regardless of whether or not the linked workbook was open already.)

-->

Written by Jane Hames

Excel Vlookup using TRUE

Monday, September 28th, 2015

Written by Jane Hames

This guide is aimed at Excel users who can already use the VLookup function and want to learn more about it. This is how to use the Excel Vlookup using TRUE

If you use a VLookup, the chances are you will use FALSE as the Range Lookup finding an exact match for the Lookup Value in the first column of your Table Array. I often get asked when you would use TRUE as your Range Lookup and this is good question as it’s not used half as often as FALSE.

Last week I was able help one of my Excel course delegates to solve a problem she had, and the solution to the problem was to use a Vlookup with TRUE. I wanted to share what we did with my readers and followers as it might give you some good ideas of things you could do with your own work. All data in the guide below is fictional, but based on the real world scenario:

The scenario I was given, is that as shown in the image below, there are a list of date ranges with different exchange rates for different date ranges. Excel Vlookup using TRUE

We wanted to ensure that the correct exchange rate was used to calculate the payment amount in Euros as shown in the image below: Excel Vlookup using TRUE In order to do this, I used a Vlookup to pick up the correct exchange rate for the specified payment date. This payment date would need to sit in one of the date ranges listed. This is what we used to do this calculation:

LOOKUP_VALUE: The payment date Excel Vlookup using TRUE

TABLE_ARRAY: The table which shows the date ranges listed in ascending order (the Date From in the first column of the table_array is what Excel will use). The exchange rate is in the third column of the table_array.: Excel Vlookup using TRUE

COL_INDEX_NUMBER: 3 This is the column in the table_array that contains the exchange rate we are looking for.

RANGE_LOOKUP: TRUE This ensures that Excel will look at the earliest date in the list that is close to the payment date (our lookup_value). Excel Vlookup using TRUE

This what the formula looks like at the end: Excel Vlookup using TRUE

Note that the payment date is shown as its reference number rather than the date. This is because I have selected SHOW FORMULAS in order to show you the formulae on the sheet. I then copied the formula down so that I have ended up with this end result: Excel Vlookup using TRUE

If you would like help with the Vlookup or other functions in Excel, please get in touch.

-->

Written by Jane Hames

This guide is aimed at Excel users who can already use the VLookup function and want to learn more about it. This is how to use the Excel Vlookup using TRUE

Read more on Excel Vlookup using TRUE…

How To Prohibit Weekends Using Data Validation

Monday, June 23rd, 2014

Written by Jane Hames

How to prohibit weekends using Data Validation

The standard Data Validation offers some really useful options such as controlling the dates, text and numbers that can be entered in to cells. A few times recently, I have been asked how to set up data validation so that it only allows weekdays to be entered and sees weekends as invalid.