Had a great question the other day. One of my delegates on a course had a task to enter two dates from her spreadsheet with the word ‘to’ in the middle to a different programme. I showed her a way that is saving her lots of time now. Watch the video below and see how to convert a date to text and join using concatenate.
Have you ever hidden multiple Excel worksheets in one go and then tried unhiding them in one go. The unhiding normally is frustrating as you have to do one at a time. Here is a way you can get around the issue by using Custom Views.
If you carry out data analysis on Excel spreadsheets you will probably come across the need to clean the data first. For example, removing leading and trailing spaces from words. Rather than cleaning data, it’s much easier to try and prevent the mistakes in the first place. This video shows you how to prevent a user from entering unwanted spaces.
How to add Data Bars with Conditional Formatting
Conditional Format and Minimum Function to Find Best Quote
This video show one way to identify the best price from a range of products and a range of suppliers using conditional formatting and the Minimum Function
Find Formula issues with the Evaluate Formula Tool – How to use the tool
This video shows you how to use the Evaluate Formula Tool in Microsoft Excel. This is a great tool if you have created a formula and it’s not quite working properly or you have inherited a spreadsheet and you want to see what is happening with a rather complex formula.
Change Prices or Values on Spreadsheet without using Formulas
If you have an Excel spreadsheet with lots of values and you want to change them all very quickly then watch this video. You can add, subtract, multiply or divide all the values in one go.
Remove Page Break Lines in Excel
This video explains how to remove those really annoying page break lines on an Excel worksheet using one line of VBA code. Any level of user can do this.
Sub PrintLines() ' 'Removes page break dotted lines 'Create in Personal Macro workwook 'PERSONAL.XLSB 'Place link in Quick Access Tool Bar ' 'InITLearning.net ' ActiveSheet.DisplayPageBreaks = False End Sub
F12 Key – Save As
The F12 key is a great way to select the Save As dialog box.
Ctrl + S is the shortcut for Save.
Pressing the F12 function key gets you to the Save As dialog much faster than clicking the File ribbon tab for the backstage view and then clicking Save As.
VLOOKUP – Approximate Match
This video shows how to use an approximate match Vlookup to find year groups of pupils based on their date of birth.