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