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]
You can bring data from an excel workbook to an access database in many ways. You can copy data from an open worksheet and paste it into an Access datasheet, import a worksheet into a new or existing table, or link to a worksheet from an Access database. If you want to work with data in Access but still maintain the data in Excel, you need to link the data. This process is a bit shorter and this video shows you how. The advantage is that it maintains a live link to the Excel worksheet. Any changes you made in [More]
There are times when you show something in Excel and their reaction is simply stunned silence. Last week I had one of those moments when I found a video on YouTube that showed a very powerful mapping application INSIDE EXCEL! Excel Power Map is a new tool for Excel that has been released for Excel 2013 and Excel 2010. I understand that it is going to be included as part of the main program in Excel 2016. Part of my day job involves visiting customers and leads. One of my biggest problems until today has been trying to combining visits [More]
Excel Power Query is free and can be installed for Excel 2013 or Excel 2010. It is the golden nugget of hidden Excel features, so hidden in fact that many people have never even seen it. This video is created by Mynda Treacy, Microsoft Excel MVP, who in my opinion is the Queen of Excel Dashboards. In it she shows just two of the uses of this very powerful tool and shows you the complete power of reusing power query functions. Power Queries are like VBA but easy to create and just a powerful when working with datasets. Mynda is [More]
Excel provides many ways to count cells in a range that meet various criteria. This short video shows 7 ways to count in excel. It uses the functions COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS. You can see at 00:50 of this video how to use the COUNT function. This function simply count cells in a range that contain number. Also, in 4:51 of the video, the count function was used in a filtered list/range. COUNTA counts all non-empty cells while COUNTBLANK counts the number of empty cells. COUNTIF was used in this video to count cells that contain a specific [More]
If you have to type the same data into cells all the time and want to make your data entry easier, then adding a dropdown list to your spreadsheet could be the answer. This video will help you to learn how to do it. Dropdown lists are very helpful. They are used to enter data from a pre-defined list and it limits data entry to specific set of items. Dropdowns also improves the efficiency of your data entry as it prevents spelling mistakes and makes data input faster. Fortunately, it’s easy to implement. You need only two things: a list and [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]
Excel provides many formulas for finding a particular string or text in a list or array. In this video, you will learn how to use the MATCH function. Excel defines MATCH function as “Returns the relative position of an item in an array that matches a specified value in a specified order”. In simple plain language Match function searches for a value in a defined range and then returns its position. You will definitely love this video as it not only shows how to compare 2 lists but also demonstrate 2 ways how to handle errors when the comparison finds [More]
Pivot tables is the most powerful feature in Excel. But what do you do if your data has already been turned into a Pivot table when you get it? The Answer? use Unpivot – the most hidden feature in Excel! I am really impressed that excel can convert a number of hours of work into seconds thru the use of Unpivot. Unpivot is a feature included in Power Query Excel add in. Power Query Excel add-in lets you discover, combine and refine data. And since this is an add-in feature, you may need to enable to use this. Or you [More]
Every wondered why Pivot Tables are such a powerful feature of Microsoft Excel or how quickly pivot tables and Pivot Charts can make your data come alive? If so you much watch this video. In it John Michaloudis shows some fantastic techniques for using pivot tables to see data coming to life. In just 3 minutes he creates a number of pivot table charts for a mass of sales data. It is a joy to watch and you may want to grab a pen to make notes as he goes through tons of content in his 3 minute challenge. Watch this [More]
I think the IF statement in Excel is a perfect example of how a little knowledge in Excel can go a long way to making your life so much easier. And this helpful video will show you how to do it. By using the “IF” function of Excel, you can create an output based on the conditional statement you specified. The IF function processes a logical test and delivers a specified value based on whether that test is true or false. To see how the function is inserted in the spreadsheet, you may jump to 1:01 of this video. The video [More]
This useful video shows how to use a graphical representation of data in a single cell through the use of Sparklines. I think Sparklines are one of the exciting and cool charting features introduced in Excel 2010. Sparklines allow you to view and analyze data without creating an entire chart. You can quickly and easily spot trends at a glance and add rich visualization capability to tabular data without taking up too much space. We choose this video as it shows how you can create and customize sparklines and how you can highlight certain points like:- High and low points, Negative [More]
