If you import data into Excel then you will have probably used Excel’s CSV import routines and have no doubt got frustrated that Excel’s default is to see all the number columns as numbers and format them accordingly. Sounds like a reasonable idea but if you are importing data where a zero at the front is important then it is not sure a great idea. Take for example Zip Codes. 07047 is different to 7047 and that leading zero is important. Excel CSV import routine is explained in this video and the all important step where most of us click [More]
Suppose you want to adjust a project’s schedule date by adding two weeks to see what the new completion date will be, or you want to determine how long a single activity will take to complete in a list of project tasks. And you might be thinking now how you can do this as simple as possible…. …. and sometimes Excel Date’s seem a but of a mystery …. The DATE function is the solution. There are a lot of cool functions for dealing with dates in Excel. But this video shows you how to add Years, Months or Days [More]
Simple shortcuts likes the ones shown on this video can save lots of time each day. How many times do you cut and paste data around Excel? If you are like me then I guess your answer is “I cut and paste in Excel all the time”. If so then you should watch this video as it shows you two cool tricks for cutting and pasting data in Excel and will introduce you to two really cool shortcut keys. Very simple video and really well explained
Protecting your worksheet and formulas is important if you are going send other users your spreadsheet to use. Chances are they may change one of your award winning equations either on purpose or by mistake. That makes the QA of the worksheet difficult to gauge and can be a nightmare for you to unravel later. This is a great video – the first two minutes show you some pretty cool skills on making a dummy dataset (RANDBETWEEN is a new one for me) and it is worth watching what he does here some great tips. After this the video [More]
A nested IF statement is an IF statement that sits inside of another IF statement and this video shows you how to created a nested IF statement. I have to be honest here – in this video you will learn how to use the insert Function dialogue to insert nested functions and I did not know you could do. In fact over the 5 years I have been teaching Excel to my classes I have told them this is not possible! Opps I had better change that! It just goes to show you that no matter how long you have been using [More]
This video shows you all the options for adding a hyperlink to your spreadsheet. I like this video because it covers everything you may want to do with simple hyperlinks in just one video. The video starts with linking to a webpage, then shows you how to link to a different sheet or cell within the same file and then how to link to a different file or word document. Watch out for the navigation tip at around 5.40 – I never knew you could do that! If you have found this video useful please let me know using the [More]
This video is taken from the Excel Dashboard course run by Mytraininghub – read my full review of this course here The video shows how you can add text labels to the title of a graph so that when the data the graph is plotting changes the title keeps in sync with the data. It is a surprisingly simply idea and as you will see from the video it is easy to do and the presenter explains it very clearly.
This is one of the longest videos on the site but it shows one of the most useful in showing you how you can make charts in Excel to be dynamic. The video contains a lot of information and it uses excel scroll bars to make a slider on the spreadsheet, then it shows how to use Vlookup to take the value from the slider and look up the values to be plotted. Finally it shows a small VBA routine that shows you how to animate the graph at the click of a button.
This video shows you how to create a frequency distribution chart using an array formula. there are a number of ways to create a frequency distribution chart in Excel, you can use a pivot chart, create a set of equations using countif or the method contained within this video. You need to pay special attention at the part when the formula is copied down – the Ctrl Shift Enter combination is vital when you copy the equation.
I have added this video to inspire you to great things rather than teach you how to do a specific task. This is well work watching if you have heard people use the phrase Excel Dashboard but do not fully understand what it means. Unfortunately there is no sound with this video – but I hope it gets you thinking what you could do using some of the Excel dashboard techniques on this site. Please let me me know in the comment box if this has inspired you …. Almost everything you see here (except the animation) can be done without [More]
I love this features in Excel – it is one of the hidden gems of the program. This feature was called the Excel Camera in Excel 2000 but is now referred to as a linked picture. The video below shows how to “take pictures” in Excel 2003 but this feature works in Excel 2007 and Excel 2010. The image to the left shows where to find this feature in Excel 2007 and Excel 2010. The Linked Picture options in Excel will remove all you column and row sizing nightmares and will help you create stunning Excel Dashboards.
The Excel Goal Seeking options allow you to play with your data and find out how your parameters need to change to make the result you need. There are two ways to do this in Excel, one is trial and error (this is the most popular way – and the least productive) and the other is to use the Goal Seeking options in Excel. Many users do not even know that this feature exists in excel. The file used in this video can be downloaded from here This video is one of Clayton Lock’s 47 videos – to find out [More]