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.
We wanted to ensure that the correct exchange rate was used to calculate the payment amount in Euros as shown in the image below: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
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.:
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).
This what the formula looks like at the end:
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:
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
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.
Read more on Excel Vlookup using TRUE…
Wednesday, July 8th, 2015
Written by Jane Hames
I have been an IT trainer for over 17 years now, and during that time I have worked with many organisations who are upgrading their software or changing to a new system. In all projects of this type, I have found that the idea of change does unsettle many people. I can only assume that this is human nature, there’s nothing wrong it, and it certainly shouldn’t be ridculed or punished. The question I want to help answer is – how can we reduce the amount of fear, resistance and doubt that results from news of a different way of working?
I am writing specifically about the introduction of new IT systems and how organisations can help prepare their staff to embrace and even to become excited and positive about the change, but it could apply to other areas of business too. Sometimes, new software also brings about the introduction of new business processes too. So, not only are users being given a tool that they’ve never used before but they are also having to re-think and learn new processes in order to be seen as successful in their role.
Commonly, by the time I am introduced to users, it is on the day of the training course. If it is to be effective, training should be an enjoyable and positive experience, but if delegates arrive feeling the pressure of having to re-learn the job that they’ve been doing for years, it can result in a negative attitude towards the training before it’s even started. This, of course, has a negative impact on how users feel when they have to get on with their job using the new software. I want to help organisations to avoid these pitfalls, in order to reduce resistance, reduce lost working time, reduce stress and to increase happiness and engagement for all involved.
I have been thinking about things that I have heard at the start of training on a new or upgraded system. I have listed these below along with some simple ways that may have helped people to have felt more on-side and positive before we got to this stage:
“There’s nothing wrong with the old system, why change?”
What a valid question! If the users doesn’t understand why there’s a need to change, then how can they be expected to embrace the change? New software and systems are a big investment for a company both financially and logistically. So, those who have made the decision to invest in a new tool must know what is “wrong” with the old system and how a new system will improve the way that individuals and the organisations work. Someone needs to SELL the new system to the users! Using clear, non-techy language, explain what’s wrong with old system and what’s better about the new system. Explain the BENEFITS, not just the features. Allow users to ask questions and make suggestions. Involving users is essential to ensure that their needs are accounted for. It might be too late once the system is up and running!
“I can’t do my job properly with this new software” “I won’t have time get my work done because I don’t know where anything is on this new tool”
This could raise some serious questions about the new software’s ability to allow users to do their job, however, let’s assume that the decision makers have done their research and have ensured that users will still be able to do their job as well, if not better with the new software. If this is the case, this needs to be clearly communicated. Why should should users take someone’s word for it? Loyal, valuable staff will question decisions – that’s one of the things that makes them valuable – they care. I think that concerns about being able to work as efficiently and comfortably as before are very common and perfectly natural. Perhaps users should know that they will be allowed some slack to get used to the new software. Maybe some contingency for less efficient working needs to be put in place while users are still learning. I know this can be difficult for organisations as “business as usual” is usually still expected. How about putting in place incentives for users to do a bit of overtime or put in some extra learning time?
“Why weren’t we consulted about this?”
Consulting ALL users is essential. I don’t mean speak to each employee one by one, but give people opportunities to have a voice should they wish to. Surveys, meetings, road shows, posters, demos, examples of organisations who successfully use the software are just some of the ways users will feel like their opinion is valued and that they have been given the opportunity to have their say. You can’t please everyone, but communication about HOW decisions were made involving users will certainly help to please the majority.
“It’s OK for the young ones, but I’m too old to learn new things”
Another very natural reaction to change, and something that people have said to me a lot. Generational differences do have an impact. New school leavers will usually find using new technology much easier than users who grew up in a very different technological age. It might be that the older, longer serving employees have more experience in business – approach them for their input on how the change in software might affect the business. Take their concerns onboard and use them to maximum effect. Ensure that they know their input is valued and is being used.
I know that training a whole organisation is a huge under taking but it is totally necessary if the investment in the software is going to be worthwhile. Moving as seamlessly as possible from one system to another is key, so try to group users sensibly so that they attend the training with users of a similar level of IT literacy. Consider job roles in these groupings and also which features will be used by different groups of people. I have been involved with helping to organise these groupings many a time. It’s not necessarily a quick job, but it is well worth while as it will ensure the training is a focussed to the individuals as possible so that they can get on with their job as quickly as possible.
I have worked with company IT experts to learn their in-house software and design training for their users. I have helped business experts to design training and prepare for training delivery, I have shown people what’s new in software upgrades to give them a more positive and effective way to work with software. For any more advice or to discuss an upgrade you are planning do get in touch with me. jane@glidetraining.com 01273 956200
Written by Jane Hames
I have been an IT trainer for over 17 years now, and during that time I have worked with many organisations who are upgrading their software or changing to a new system. In all projects of this type, I have found that the idea of change does unsettle many people. I can only assume that this is human nature, there’s nothing wrong it, and it certainly shouldn’t be ridculed or punished. The question I want to help answer is – how can we reduce the amount of fear, resistance and doubt that results from news of a different way of working?
Read more on Nothing wrong with the old system, why change? Getting people on-side….
Thursday, May 21st, 2015
Written by Jane Hames
Need to remove underline from hyperlinks in PowerPoint? Here’s an easier alternative
If you have added a hyperlink to text in PowerPoint, the default theme will make it blue and underlined. I often get asked how to change this, and one option is to create your own theme with a custom colour scheme. The alternative I am going to guide you through is often more popular with the people I meet on training as it is a bit quicker and easier than creating a custom theme.
Here’s what a hyperlink on text looks like when using the default theme and colour scheme:
So, if you need to remove underline from hyperlinks in PowerPoint, you may want to consider inserting a shape next to the text and turn that into a hyperlink (The shape could be made invisible on the slide if required). Here’s how:
Tip: You could make the shape invisible by choosing No Fill and No Outline, or something very pale so that only the presenter is aware of the shape. When running the slide show in Slide Show view, you will see the hyperlink hand shape when you move over the shape so you can just click to follow the hyperlink.
-->Written by Jane Hames
Need to remove underline from hyperlinks in PowerPoint? Here’s an easier alternative
If you have added a hyperlink to text in PowerPoint, the default theme will make it blue and underlined. I often get asked how to change this, and one option is to create your own theme with a custom colour scheme. The alternative I am going to guide you through is often more popular with the people I meet on training as it is a bit quicker and easier than creating a custom theme.
Read more on Need to remove underline from hyperlinks in PowerPoint? Here’s an easier alternative…
Wednesday, May 20th, 2015
Written by Jane Hames
When you have a busy workload, giving up a day or two to attend training can sometimes be a struggle and to prepare for the training doesn’t always make it to the top of the priority list. The tips I will share with you here really won’t take much time at all. Just a little bit of investment will enable you to get the best you can from your course.
As an IT trainer, I do find that, once people are in the swing of the training they are always pleased to have given up their time to come along. The main reason for this is that they very quickly realise how much time they will save and how much more value they will be able to get out of their work. (The idea of gaining a reputation for being “good at the software” is often a motivation too!)
Is just turning up and hoping for the best going to enable you to really achieve your objectives. Do you even know what the course is about yet or have you “been sent”? Here are a few things to think about prior to the training (even if the only time you’ve had is on your journey there). Having answers to these questions will ensure you get the most from the day and that your trainer can properly cater to your needs.
1. What are your objectives?
2. Do you know how you want to develop your skills both short and long term?
3. Does your manager or organisation have some plans for your development that aren’t clear to you?
Most importantly – training should be enjoyable! At Glide Training we aim to make it a great day that is highly productive and leaves you full of confidence and enthusiasm. We hope to see you soon and if you have any questions about what training is best for you, we would love to have a chat about it, so please do give us a call.
Written by Jane Hames
When you have a busy workload, giving up a day or two to attend training can sometimes be a struggle and to prepare for the training doesn’t always make it to the top of the priority list. The tips I will share with you here really won’t take much time at all. Just a little bit of investment will enable you to get the best you can from your course.
Read more on Easy ways to prepare to get the most from your training…
Wednesday, April 29th, 2015
Written by Jane Hames
Turn a positive number into a negative in Excel using Paste Special
During Excel training last week, I was asked how to turn a positive into a negative in Excel. The Excel user was calculating balances but the expenses came in form Sage as positive values, whereas they needed to be negative values.
The ABS function can be used to turn a negative into a positive (see how to do that here). To turn a positive into a negative you can use Paste Special. This is what we did:
Excel training is the most commonly requested subject at Glide Training. We have helped hundreds of people to become more efficient and to enjoy their Excel use.
Written by Jane Hames
Turn a positive number into a negative in Excel using Paste Special
During Excel training last week, I was asked how to turn a positive into a negative in Excel. The Excel user was calculating balances but the expenses came in form Sage as positive values, whereas they needed to be negative values.
Read more on Turn a positive number into a negative in Excel using Paste Special…
Tuesday, April 21st, 2015
Written by Jane Hames
Microsoft Outlook Shortcut Keys
When delivering training courses, the particpants are often very keen for me to share keyboard shortcuts with them. The invention of the touch screen does not seem to have reduced requests for help with saving time using keyboard shortcuts. I find that Outlook shortcut keys are not commonly known, so here’s a few that should save a bit of time. Have a practise of these as soon as you can so they they become embedded in your mind…they’ll soon become second nature!
Outlook can be used for so much more than just sending emails. Why not give as a call discuss some Outlook training for you, your team, or find ways to improve the way your organsation as a whole communicates with this great tool.
Written by Jane Hames
Microsoft Outlook Shortcut Keys
When delivering training courses, the particpants are often very keen for me to share keyboard shortcuts with them. The invention of the touch screen does not seem to have reduced requests for help with saving time using keyboard shortcuts. I find that Outlook shortcut keys are not commonly known, so here’s a few that should save a bit of time. Have a practise of these as soon as you can so they they become embedded in your mind…they’ll soon become second nature!
Read more on Microsoft Outlook Shortcut Keys…
Monday, April 6th, 2015
Written by Jane Hames
Reset a PowerPoint Slide to its Slide Master Settings
If you have edited the formatting of a PowerPoint slide individually you may have detached it from its slide master. This means that it will be inconsistent with other slides. Also, if you change the formatting on the slide master, the changes may not be carried through to this slide. You can reset a PowerPoint slide to its slide master settings. Here’s how:
Written by Jane Hames
Reset a PowerPoint Slide to its Slide Master Settings
If you have edited the formatting of a PowerPoint slide individually you may have detached it from its slide master. This means that it will be inconsistent with other slides. Also, if you change the formatting on the slide master, the changes may not be carried through to this slide. You can reset a PowerPoint slide to its slide master settings. Here’s how:
Read more on Reset a PowerPoint Slide to its Slide Master Settings…
Monday, April 6th, 2015
Written by Jane Hames
How to copy formatting in Excel, Word and PowerPoint
I regularly want to copy formatting in Excel, Word or PowerPoint but rather than having to carry out the same formatting steps again, I use the Format Painter. This is a quick and easy tool to use, you just to need to know about it and then remember to use it.
Tip: In step 2, if you want to be able to copy the formatting to more than one area, double click on the format painter. You will then to remember to click on it again to switch it off once you have finished.
-->Written by Jane Hames
How to copy formatting in Excel, Word and PowerPoint
I regularly want to copy formatting in Excel, Word or PowerPoint but rather than having to carry out the same formatting steps again, I use the Format Painter. This is a quick and easy tool to use, you just to need to know about it and then remember to use it.
Read more on How to copy formatting in Excel, Word and PowerPoint using the Format Painter…
Thursday, March 19th, 2015
Written by Jane Hames
Customise the Toolbar in Word, Excel, PowerPoint, Outlook
The toolbar in Word, Excel, PowerPoint, Outlook or other Microsoft Office programs is called the Quick Access Toolbar. It contains a few buttons ready for you to use, but it can be customised to include other things that you use frequently.
The quickest way to do this is:
Another method is:
Written by Jane Hames
Customise the Toolbar in Word, Excel, PowerPoint, Outlook
The toolbar in Word, Excel, PowerPoint, Outlook or other Microsoft Office programs is called the Quick Access Toolbar. It contains a few buttons ready for you to use, but it can be customised to include other things that you use frequently.
Read more on Customise the Toolbar in Word, Excel, PowerPoint, Outlook (Quick Access Toolbar)…
Tuesday, March 10th, 2015
Written by Jane Hames
Excel number formatting for when working with users with a different international format
As often happens during Excel training, recently a question came up that I had not been asked before. The way people use Excel is so personal to their own style, the type of work they do and what their organisation does, so I love it when a question is asked that enables me to do some troubleshooting and experiment with new ways of working.
The question this time, was from an Excel user who works in the UK and – as we do here – uses a comma to separate thousands and a dot as the decimal place. The user was receiving workbooks from colleagues elsewhere in Europe where they were using a dot to separate the thousands and a comma instead of a dot for decimals. So, as you can imagine the user was getting very frustrated and even had to resort to re-typing the numbers!
My challenge was to solve this problem, and this what I did:
Written by Jane Hames
Excel number formatting for when working with users with a different international format
As often happens during Excel training, recently a question came up that I had not been asked before. The way people use Excel is so personal to their own style, the type of work they do and what their organisation does, so I love it when a question is asked that enables me to do some troubleshooting and experiment with new ways of working.
Read more on Excel Number Formatting For International Users…