Category Archive 'Excel spreadsheets (.xls)'

Using Excel Keyboard (and other) Shortcuts To Dramatically Increase Productivity

Excel function tutorials, Excel spreadsheets (.xls)

I talk about productivity and improving the efficiency of Excel spreadsheets on this blog but I`ve never actually discussed one of the primary ways that I save time when working in Excel. In the past, I`ve talked about adding the most common things that I do to save time. There`s no doubt that creating macros and using powerful functions in order to automate some processes are key but I probably save as much if not more time by simply “not searching” for functions in the Excel spreadsheets. How? You’ve probably heard of the most famous keyboard shortcuts:

Ctrl-C: Copy
Ctrl-V: Paste

These are clearly the ones I use most, not only in Excel but on my computer in general. Here are a few others that I use a lot:

CTRL+PgUp:Switches between worksheet tabs, from left-to-right.
CTRL-F: Find
Shift+Space: Select entire row

You can also get a full list on this PDF from the Microsoft website.

This certainly solves most of my issues but there are some functions that are more complex to use as shortcuts but that I use over and over. What do I do with those? Simply add the icons to the top of my Excel bar in order to make it much easier to do. How? Let’s take the example of “Paste as Values” which I tend to do all the time. How?

-Click on File

-Click on options
1

-Click on “Quick Access Toolbar”
3

-Find the exact function and click add
4

After that it will appear at the top of your “Excel” application:
5

Easy enough? I promise that you will be very surprised at how much time this will end up saving you.

Transforming Addresses Into Label Formats With Excel

Excel macros, Excel spreadsheets (.xls)

Today, a reader sent me a list of 400 addresses that were all entered in a format like this one:

P.S: I am not publishing the actual file for privacy purposes and don’t feel like writing down 400 fake ones:)

Her hope was to transform the addresses into what you’d use on a label such as:

I thought this would be a good VBA practice. So what I wanted to do was use a “loop” function to go through each line and for each one, write down the label over 4 lines. How did I manage? Here is the code I used:

And the end result? Simple enough? You bet

Separating Data In Excel – A Real Case Using Addresses (Part 2 – Managing Exceptions)

Excel function tutorials, Excel spreadsheets (.xls)

In my previous post, I started off from one column and used different functions such as left, right and mid to separate my data into columns that will be easier to work with. when that is done, you will be able to look through all of the data and see how things look, what kind of exceptions came in, etc.

One data that happened in my case is that I could have a few cities where the data had a neighborhood that was specified. Just take a look:

y

This might be something that happens all over my spreadsheet and could cause different issues. I only want the exact city name to appear because that is what I’d use for stats, to send out mail, etc. How can I get rid of it? There are 2 main options:

#1-Adjusting the initial formula (depending on what the type of error is, this could certainly work)

#2-Adding Exception Management to that formula: This is the road I will take here and while it might *look messy, it will be done fairly easily. So as you know, my current formula is:

=MID(A2,B2+3,C2-B2-3)

I first want to know if there is a “(” in that formula. I will use the “find” function here:

=FIND(“(“,MID(A2,B2+3,C2-B2-3))

Here, I either get a number or an error. I will then simply manage that error by adding an “if” function:

=IF(ISERROR(FIND(“(“,MID(A2,B2+3,C2-B2-3))),0,1)

Basically, if there is an error, it means no adjustment is needed so I will replace the 0 in that formula by what I had.

However, if I do not get an error, I will adjust that answer by only capturing what is left of that “(“:

So I would get the city only:

=IF(ISERROR(FIND(“(“,MID(A2,B2+3,C2-B2-3))),MID(A2,B2+3,C2-B2-3),LEFT(MID(A2,B2+3,C2-B2-3),FIND(“(“,MID(A2,B2+3,C2-B2-3))-1))

I know it looks messy but take the time to look at the formula. It resolves my issue and would “clean” up my data for all such cases.

Here is the new result:

You can also download the spreadsheet

Excel Financial Modelling Competition? Yes, I’m Serious

Excel spreadsheets (.xls)

Haha.. I was personally surprised. I’m very much into competitions, especially for anything sports-related. I’m also very interested in competitions for other activities such as chess, or even video games. But I had never heard of an “excel” competition… But it exists and has over $30,000 in prizes which is certainly not bad. I’d imagine it’s also a great place to showcase ability to get a job or a contract right? I can’t even imagine what type of candidates give it a try but it must be pretty fascinating:)

Using A .TXT File In Excel

Excel spreadsheets (.xls)

In my last post, I looked into using a DOS or CMD program to list all files in a given directory. That worked out well and it generated a .txt file that you should be able to use if you’re able to use the other functions that I’ve discussed on this blog. So let’s give it a start. First off, to answer a question, yes you can open .txt files in excel. You simply click on “file/open” and then select all files:

Then, you’ll be asked if you want to convert the information into columns. Sometimes, especially for some .csv files, that can work. But in this case I’ll just click on finish which will give me the result:

Depending on what I’ll be doing, I could need to combine this and other lists but it’s important to have the full path for each file. How? Simply by going into cell G8 and using:

=”L:/ETF Mmaking/Pete/Hist/” & F8

This is the result:

Then, I could simply write a macro that would go through each of the files with a loop for example to do search and replaces, add a logo or anything else:)