One issue that I’ve had a few times is trying to create a list of files. Why would I need to do that? Because then I can create a macro that will go through all of those files one by one to do specific operations. How do I do it? First, I’ll open a DOS cmd window by clicking the start button of my pc and typing “cmd”.

Then, I can simply type the following to get the list of files in my L: drive for example:

L:

Then I could go to a subdirectory such as ETF by typing:

cd ETF

Then, I’d type:

dir > list.txt

I’d then get the list of files in that directory in a file named list.txt, which I can open and start playing around with in excel. Easy enough? See how I did it here (given the fact that a .txt file is not as easy to use.

Good morning to all of you! As may of you know, I’ve been looking into ways to improve the way I can help everyone improve their excel skills. More than ever, in a tough economy, being able to distinguish ourselves through Excel can be key in not only finding and keeping a job but getting a step ahead on the competition.

I did end up writing an excel ebook which has now helped hundreds of users. I also make a point to ask every single reader how the ebook has helped and it could get better. I’m looking into many different options in order to help you improve your skills. I have pretty much confirmed the importance of using examples, but there are other things I’d like to know.

-Do you feel like having a broad training course would help or do you prefer using it “on demand” for specific functions when needed?
-How helpful would having videos with demos of the functions be if at all?
-Would having a more structured online excel training course bring value?
-In such a structure, would having “assignments” be helpful? More work but more feedback, etc?
-Would you want some kind of exam that would test your knowledge at the end?

I’m looking at building something in early 2014 and will likely be reaching out to some volunteers who would like to get early access to the material! I’d probably build 2-3 different courses because it seems unlikely that those looking for “beginner’s advice” will be looking for the same kind of info as more advanced users so trying to build a one “cover-it-all” excel course doesn’t seem like the best of ideas.

I’d also love to hear from you if you’ve tried some other courses that are offered online. What did you like of their format and their content? What could be improved?

Last week, I pubished a post about customizing your toolbar in Excel in order to increase efficiency. Today is another very quick and easy tip. I know that many of you use dozens of different excel speadsheets. Some are more common than others though and being able to access them very easily can save some time. When you click on the top left corner of excel 2007, here is what you’ll see:

You certainly recognize the screenshot and you probably know that the recent documents are generally placed in order of last use. The issue of course is that some documents that you use every day could end up not showing up here because you’ve used so many other spreadsheets. What you can do however is click on that little “pin” next to a document.

Once that pin is green, the document will remain on the list no matter what which has saved me time and useless energy.

Do you use this or other productivity tips to increase your efficiency in excel?

For many of you, working with large sets of data presents many challenges and today I thought I’d give you one way to make life much easier. Here is an example that probably occurs on a regular basis. Look at this spreadsheet:

It’s quite easy to work with this of course. But what if you have thousands of lines and you end up going further down you’d get:

If you add a few columns, you’ll end up with a problem that you’ve probably encountered in the past. You’re stuck going back to line 1 all the time to figure out which column means what.

There Is A Better Way

In such a case, what you do is simple. If you’re looking to always keep that first line visible, you can select cell A2 as follows:

Then, you’d select the menu folder and click on freeze panes:

As you can imagine, either of these 2 options would work:

As you can see, no matter where I go in that sheet, I will still see that first line (with the column headers). The same can be done for a column, etc.

One of the tricky challenges in using excel is dealing with different text formats. I’ve discussed dates in the past but hours can also prove to be very tricky. Why? Basically, excel can understand all of these:

09:30
03:30 PM
03.50

What is cannot do though is guess which format you (or the end user) is using. I was asked to create a wage calculator that would be able to manage all formats. There are no easy ways to do this simply because excel cannot guess what the user is actually thinking.. Of course, I could look for “AM” or “PM” and do something when I find those, but it does remain difficult to deal with. Much easier is to make it very clear to the end user how you’d like him to enter the data.

Let’s take a simple wage calculator where the user has to enter the hours that he worked in order to for me to know how much I owe him. You can see a screenshot here:

In this case, I made it very clear that I expected him to enter the hour in one cell and the minutes in another. That makes it much easier for me to manage the data. I then can simply use a nested if condition to determine the actual time:

=A5+B5/60+IF(AND(C5=”pm”,A5<>12),12,0)

Why? First of all, I deter,ine the time by adding the hours to minutes/60. I also want to add 12 hours if it is “pm”.

I did pretty much the same thing to determine the end hour:

=D5+E5/60+IF(AND(F5=”pm”,D5<>12),12,0)

Then, I can simply substract one from another right? Almost.. but what if the user worked from 6pm (or 18:00) to 3am? Then you would get a negative number. Instead, I simply look and add 24 hours if that is the case:

=IF(H5-G5<0,H5-G5+24,H5-G5) After that, it becomes fairly straight forward, as you can see:

You can also download the spreadsheet