I’ll need your feedback on this. I have been creating PDF files from Excel for some time without an issue. But clearly, many others are having problems getting this one done so I thought I’d take a look. What I do is simply click “save as” and then select “PDF” as my file type.

And voila! It’s done. The one issue that you might encounter is not seeing the PDF format as an option when saving. If that’s the case, simply install Adobe Acrobat (follow instructions here). Then, you might need to close down Excel, reopen it and give it another try.

If that still does not work, please write a comment and let me know, I’d love to try to help out.

Suppose that you have a few hundred cells where you need to make a small change. Chances are that you will either require the help of a macro or an excel function in order to get it done. I was given a fairly simple task. A reader had a list such as the one you can see here:

He had to replace the “-“ by the following “.-.”. Basically, add a dot before and after. There are a few ways to get it done but doing it by hand was impossible (there were about 1000 different cells that needed to be changed). So what could I do? I decided to use the replace function. Herei s how it should be used:

=replace(old text,start num,num_characters,new text)

So basically for the first cell, I needed to do the following:

=REPLACE(A1,FIND(“-“,A1),1,”.-.”)

Why did I use “find”? Because I needed an easy way to find where the “-“ was located. You can see the end result here:

The replace function is fairly easy to use and can turn out to be very useful! You can download the spreadsheet here

I have to admit, I did not have much understanding of XML up until a few hours ago. But I had read about the ability to use the importXML function in Google Docs spreadsheets and it looked promising so I decided to give it a try. I did use a few resources which I’ll be sure to link to at the end of this post. So what is ImportXML? It’s a function made available in Google Docs that makes it easy to make different types of web queries

How Does It Work?

The one downside is that using it requires some understanding of the html. But it can be extremely powerful. You can see 2 examples here:

#1-Imagine that I’m trying to get access to the list of all links in this page on a blog that I visit:

It’s certainly possible for me to get them all one by one or to download them in one way. Another option though is to do a simple ImportXML after seeing that these links were located inside of this code:

I did the following query:

=importxml(“http://www.smartpassiveincome.com/archives/”,”//div[@class=’format_text’]//li/a/@href”)

And you can see the result here:

How about another use? I have a few dividend-related sites and imagine that I’d need to know the top 20 sites for the keyword: “dividend investing”

I added the keyword in cell A1 to make it easy to change and then used the following function in B1:

=”http://www.google.com/search?hl=en&gl=US&num=20&q=”&A1

This game me a list of 20 websites. Ideally, I wanted to have clickable links so I used the “find” function which I’ve discussed in Excel and then the “mid” function, you can see the result here:

Resources: ImportXML Guide for Google Docs (http://www.distilled.net/blog/distilled/guide-to-google-docs-importxml/#chapter1)

One of the features that I use quite a bit in Excel as most of you know is the ability to do web queries. This can be done either manually or through macros. I was happy to find out that to some extent, it can now also be done in Google Docs spreadsheets. Obviously, since macros cannot be used in Google docs, the only way is to do queries directly.

So suppose you’d like to get access to the price of a security on Yahoo Finance, let’s imagine Google (GOOG), the price can be found at:

http://finance.yahoo.com/q?s=GOOG

Then, you could simply use the following in your Google docs spreadsheet:

=index(importhtml(“http://finance.yahoo.com/q?s=CLF.TO”,”table”,2),1,2)

Most of that formula is generic and easy to understand. The tricky part is knowing which table to refer to. I’d say that in all honesty the best solution is often to just try. You can get a bit more info from Google’s help here:

http://support.google.com/drive/bin/answer.py?hl=en&answer=155182

Then, you can simply add a few different elements to make sure that the data refreshes at a regular interval, for example, if you’d like the data to refresh every hour you could:

Add this to cell B2:

=Minute(Now())

And then adjust the formula to:

=index(importhtml(http://finance.yahoo.com/q?s=GOOG&B2,”table”,2),1,2)

As you can see, the possibilities are significant

One of the more common questions that I get is managing dates in excel. I’ve already discussed the challenges of date formats but another common use is managing dates when you need to know what the next business day will be. Why?

Because you need to know if that next day will be:

-a weekend
-a holiday

Suppose you have a cell where you write today’s date by using the today() reference as follows:

Then, in the next line you need to know what the next business day will be so you do:

=TODAY()+1

And in that specific case, it would work. But that formula would not detect what type of day it is. Of course, I could use formulas such as =day(D1) which would tell me which day of the week today is.

Even better though is simply using the “workday” formula which will take out weekends.

Then, I can a holiday calendar that I will refer to in that formula as you can see here:

Easy enough? Let me know if you run into issues with this!