Printing an Excel Function Reference Sheet

A how-to for Excel users.

My Visual QuickStart Guide books for Excel — from the edition for Excel 95 for Windows through the edition for Excel X for Macintosh — have always included an Excel function reference. In an effort to make pages available for other content, however, we’ve cut that appendix from the Excel 2007 edition of the book. I don’t feel badly about the cut, since this information is readily available in Excel Help and on the Web.

Here’s how you can find a function reference for your version of Excel and print it for your own hard-copy reference guide.

In Excel 2007 for Windows

  1. Click the Help button in the upper-right corner of the Excel window to display the Excel Help window.
  2. Enter function list in the search box and press Enter.
  3. Excel Help search resultsAmong the search results that appear, you should see an item titled “List of worksheet functions (by category).” Click its link.
  4. Function List for Excel 2007A help document titled “List of worksheet functions (by category) appears in the help window. It includes a complete list with brief descriptions of all Excel 2007 functions. You can read through this document and click links within it to learn more about specific functions.
  5. To print the reference sheet, click the Print button in the Excel Help window’s toolbar. Use the Print dialog that appears to set printing options and click the Print button.

Note that you may need a connection to the Internet to access the Function List from within Excel Help. And remember that you can always resize the Excel Help window to better read what’s inside it.

Get this Information Online

This reference information is also available online for some versions of Excel. Click this one of these links:

These pages contain clickable links to details about specific functions. They can also be printed from within your Web browser; use the Print command.

RevenuSense

A Mac OS X Widget to track AdSense Revenue.

One of the things that bugs me about AdSense is that checking my AdSense reports is a multistep process:

  1. Launch my Web browser.
  2. Go to the AdSense home page.
  3. Enter login information. (My browser won’t remember it because I have an AdSense account and an AdWords account.)
  4. Click to log in.
  5. Select the report period.

Okay, so that’t not so much work. But wouldn’t it be nicer to just press a key and have yesterday’s, today’s, and the current month’s AdSense Revenue magically appear?

The RevenuSense WidgetThat’s basically what the RevenuSense widget does. Once installed and configured with your AdSense account’s e-mail address and password, it automatically queries Google for basic revenue information. Just leave the widget open on your Dashboard and press F12. The AdSense revenue information appears with all your other open widgets.

Additional configuration options include update frequency and currency conversions, so you can fine-tune how the display works.

I think this is a great tool for any Mac user with an AdSense account — especially bloggers, who often depend on AdSense revenue to pay for their blogging habits.

iCal GTD Widget

Another way to add To Do items to iCal.

One of the the goals of the Getting Things Done methodology is to get tasks out of your head and into a place where you can track them. That means making extensive use of organized lists.

Although iCal isn’t the best tool for GTD, it certainly is workable. (I have yet to find a better solution, although I admit I haven’t been looking very hard.) The iCal GTD Widget makes it quick and easy to add a To Do item to iCal without launching iCal.

The iCal GTD WidgetItem added to the To Do ListInstall the iCal GTD widget and place a copy of it on your Dashboard. Then, when an idea flashes into your mind, press F12 to display the Dashboard (and the widget) and enter a reminder for your idea into the widget’s edit box. You can choose a calendar from the pop-up menu if desired. Press return and the note is added to iCal as a To Do list item for the calendar you chose.

Can’t be much simpler than that.

Mac OS X 10.4.9: Media Eject key delay

So that’s what’s going on!

One of the first things I noticed after updating Mac OS X Tiger to 10.4.9 was that my CD eject key didn’t seem to work anymore. Normally, I’d just push it and the CD drawer would open. But it just didn’t work like that anymore. Not having time to troubleshoot it, I decided not to update my 12″ PowerBook.

Well, this article on the Apple support site explains it all: “Mac OS X 10.4.9: Media Eject key delay“:

Issue or symptom

After installing Mac OS X 10.4.9, pressing the Media Eject key on the Apple keyboard does not immediately eject media from the optical disc drive nor display the on-screen eject symbol.

Apparently, too many people were pressing that key by accident, thus ejecting media. Apple decided to add a delay to the Media Eject key before it takes effect.

To eject a disc, hold the Media Eject key. The disc will eject normally and the eject symbol will appear.

I just wish Apple had the foresight to mention this change in the update’s release notes rather than requiring us to fish around for the answer online.

So yes, I’ll be updating my PowerBook shortly.

How to Add WordPress Category Feed Links to Posts

Another Informit article.

Sheesh. I forgot all about this one. I just checked in at Informit and there it was — an article I’d written at least a month ago.

It’s called “How to Add WordPress Category Feed Links to Posts” and it provides detailed information about how I get those little RSS icons to appear beside category names for each post on this site. I referred to this article in a post on this site, “WordPress Category Feeds.” That post explains why I wrote the article and why I didn’t publish it here. (It basically comes down to my need to pay bills and eat.)

I think that’s the last Informit article in the pipeline. They’ve asked me for some Leopard articles, but I’m going to wait a wee bit longer before I start churning those out.

Anyway, enjoy. And if you have questions about the article, you can post them in Comments here.

How to Create a Slide Show for iDVD with iPhoto

The details.

Greetings, visitors! I noticed that this rather old post has been getting a lot of hits lately. Sadly, it’s not up-to-date with the current version of iPhoto. I’m pretty sure it’s based on iPhoto ’06. I’m looking into how I can do this with iLife ’09; when I get new instructions written, I’ll post a link to them here. Until then, just be aware that these instructions probably won’t work for the current version of iPhoto.

Last week, I wrote a brief article about how I was creating slide shows for an iDVD project. After struggling too long with an iMovie bug that manifests itself with the Ken Burns effect applied to high resolution photos, I turned to iPhoto. My article didn’t go into details about it, but I asked readers to let me know if they wanted more information. Lauren commented that she did. So here’s a bit more detail.

The Goal

I wanted to create a DVD that included still images we’d taken during several trips to points of interest in the Southwest. The DVD would be displayed at a trade show, so it needed to look good.

I like the Ken Burns effect that’s part of various Apple software programs. This effect, named for filmmaker Ken Burns, combines movement with zooming to add motion to still images. Mr. Burns uses this technique extensively in his historical documentaries to make old, still images more visually appealing and interesting to viewers. It, in effect, makes motion possible in images created before motion picture technology was available.

At least three Apple software programs utilize this effect: the System Preferences Screen Saver, iPhoto, and iDVD. I’m sure others do, too. If you know what they are, please don’t hesitate to list them in the Comments for this post.

The idea was to build a slide show using the effect, add music and titles, and include the resulting “movie” on my DVD.

iMovie Fails Miserably

I’d been using iMovie to build a slideshow using this effect, but the high-resolution photo bug was preventing me from getting the job done. This bug, which is under discussion on the Apple Support forums, blanks out an image when you attempt to apply the Ken Burns effect to it. This makes it impossible to manually apply the effect. Extremely frustrating.

I hope Apple does something to fix the bug soon.

iPhoto to the Rescue!

The solution I came up with was to create the slideshow movie in iPhoto and bring it into iMovie as a clip for a longer movie. I could also add chapter markers and title pages and all sorts of cool iMovie things to the project before pulling it into iDVD for menu stuff and burning.

Here’s one way to create a slide show movie in iPhoto. (Of course, there are other ways. This is the way I did it.)

  1. Open iPhoto.
  2. Create a new album and give it the name you want to use for your slideshow.
  3. Drag photos that you want to include in the slide show from any other album or the Library into the new album.
  4. Open the album you created.
  5. Drag the photos around to put them in the order in which you want them to appear.

    Rearrange the photos

  6. With the album’s contents displayed and no single image selected, click the Slide Show button at the bottom of the window. A new Slideshow album is created and displayed.

    A New slide show

  7. Click the Settings button to display default settings for all the slides in the slide show.

    Default settings

  8. Choose a desired transition from the Transition pop-up menu.
  9. Toggle the check boxes so only the following ones are turned on:
    • Scale photos to fill screen
    • Automatic Ken Burns Effect
  10. Select the Fit slideshow to music radio button.
  11. Choose a format from the Slideshow Format pop-up menu. Don’t leave it set to Current Display; pick the option that corresponds to the format of your iMovie project.
  12. Click OK.
  13. Back in the slideshow album’s window, click the Music button.
  14. In the dialog sheet that appears, make sure the Play music during slideshow check box is turned on. Then use the dialog to locate and select the song you want to play during the slideshow and click OK.

    Choose music

You’re now ready to preview the slide show. Click the Play button, sit back, and watch the entire show.

Fine-Tuning the Presentation

While you watch the show, you may want to make notes about the order of the slides, the amount of time each slide appears (the longer the music, the move time per slide; the more slides, the shorter the time per slide), and the way the Ken Burns effect works on your images — particularly the vertical orientation (portrait) images. You can fine tune each image if you like by setting slide options. (This is where iMovie failed me, but iPhoto doesn’t seem to have the same bug.)

To change the order in which slides appear, drag them into a different order at the top of the slide show album’s window.

The following changes override the default settings for the slide show for a specific slide.

To set basic options for a specific slide:

  1. Select the slide you want to change.
  2. Choose options from the Effect or Transition pop-up menu.

To set Ken Burns Effect options for a specific slide:

  1. Select the slide you want to change.
  2. Turn on the Ken Burns Effect check box.
  3. If necessary, drag the slider to the Start position.
  4. Drag the zoom slider to the desired start magnification.
  5. In the image window, drag the image to the desired start view.

    Ken Burns Start

  6. Drag the slider to the End position.
  7. Drag the zoom slider to the desired end magnification.
  8. In the image window, drag the image to the desired end view.

    Ken Burns End

The effect will move and zoom the image smoothly from the start to end settings.

Saving the Slide Show as an iDVD-Ready Movie

This is the painfully simple part of the job. Just choose Share > Send to iDVD.

iPhoto will create a slide show movie in QuickTime format and save it to your hard disk in the Movie folder. This may take some time — enough for a coffee or bathroom break.

When iPhoto is finished, it launches iDVD and places the movie in whatever Project was last used. You can use it there if you like or do what I did: delete it from the project, close the project without saving it, and quit iDVD. Then open your iMovie project and drag the icon for the movie into it. You can then add titles and chapter markers and other iMovie things to fancy it up.

Conclusion

What I like most about this technique is the quality of the movie. It’s pretty darn good — certainly good enough for DVD use on a regular television. And I don’t know if I’m imagining things, but it seems to be better than the slide shows I’ve created with iMovie.

Best of all, if you like the show with the default settings, it only takes minutes to create. In fact, your Mac is likely to take more time saving the slide show as a movie than you took to put it together.

Message from Page Sponsor:

Full-fletched internet services companies has many ways to make money on the internet. Webmasters who need instant search engine traffic will pay for the ppc management program. Before opting for this method, the webmasters need a good web design. There are many web design tools that you can get for free. To get their websites online, they need to sign up for good hosting plans which provide free domain and domain parking. Generally, there are two types of hosting, i.e., php hosting and asp hosting.

WordPress as a CMS: Table of Contents

Making it a bit easier to find the articles.

I decided to put together a quick table of contents for my 7-part series about using WordPress as a CMS. This should make it a bit easier for readers to find and read the related articles in the order in which they were written.

Heres the list:

I refer to this series in quite a few other pieces here.

Creating a Slide Show for iDVD

A shortcut with good results.

Over the past few weeks, I’ve been banging away at an iDVD project. The goal is to show still images from my helicopter tours and excursions as slide shows with music. I’m rather picky about these things and decided that I wanted to use the Ken Burns effect with a dissolve transition to add motion to my slide shows. That means I can’t simply use the Slide Show feature in iDVD.

Let me take a moment to discuss the scope of this project. Back in October, I took the Southwest Circle Helicopter Adventure with professional photographer Richard Noll. Richard took just under 1000 still images and several hours of video. I took about 400 still pictures. After deciding that I simply didn’t have time to go through all the video, I settled on the nearly 1400 photos plus about 200 I had already in stock for the area.

The first task was weeding out the junk. Like 30 pictures of ducks and fish swimming together in Lake Powell. And duplicates of shots that differ only in exposure. It took me about 4 hours to narrow down the library to 358 photos.

Then I discovered that there’s some kind of bug in iMovie HD that makes the Ken Burns effect controls choke on images over a certain resolution. Of course, Richard’s camera shot everything at 10 megapixels. That meant cropping or down-sampling his photos for the job. Even then, iMovie was acting unreliably.

I was getting nowhere fast and quickly running out of time.

I burned a DVD with two movies on it. I wasn’t very happy with the results.

There had to be a better way.

Long story short: I stumbled upon the Send to iDVD command in iPhoto. I soon discovered that this command exports an iPhoto slide show (which is infinitely easier to put together than an iMovie slide show) as a high resolution QuickTime move. That movie can be imported into iMovie or iDVD.

So since about 1 PM today, I’ve been knocking out slideshow movies. I’m creating them on my MacBook Pro and sending them over the network to my dual G5 where I’m adding them to an iMovie project. I’ve done seven slideshow movies so far and have three more to go. Looks like I might actually finish today.

More details on request. Use the Comments link.

How to Display Category-Specific Introductory Content

Another WordPress how-to.

The other day I got an e-mail message from a site visitor that was full of complements about my site. He especially liked the fact that different introductory content appeared for my Book Support categories. For example, if he was looking at the category for our WordPress book, he’d see the book cover and information about that book, as well as links related to that book. But if he looked at the category for my Mac OS book, he’d see information related to that title.

Two Category Archive PagesHow, he wanted to know, did I do that?

With conditional statements in my WordPress template files.

Why Bother?

First, let me take a moment to talk about why you might want to go through the trouble of customizing category archive pages.

In my case, my Web site combines information of interest to readers of my books (ie., book support) with my personal WebLog. Since some visitors come solely for book support, I needed to make them feel as if they’d reached their desired destination when they clicked a book support category link. I also had to assure them that they’d reached the pages for the specific book they’d come for.

This required two kinds of custom information:

  • A welcome message that explained the kind of information they could expect to get. I decided to put this information in the body of the page, right beneath the category heading.
  • Book specific information directly related to what they were promised in the book in the way of support, including links, downloadable files, etc. I put this in the sidebar, with book cover images to make it clear which book they were getting support for.

How could you use this on your site? Well, suppose your blog covers a variety of very different topics and you want to provide information for each topic on its category archive pages. Or suppose you’re using WordPress as a CMS and want to use categories for different product lines; you could have product line-specific content on each category archive page.

It Isn’t Difficult to Do…But it isn’t Exactly Easy, Either

Setting up different category information is as easy as inserting conditional statements in your WordPress template files with category specific information included.

WordPress 2 (Visual QuickStart Guide)Of course, if you’re not familiar with rolling up your sleeves to insert that code, it might not be easy for you. That’s why Miraz and I wrote our WordPress book. To explain things like that. And that’s why I won’t repeat what’s in that book here. (Sorry!)

With that said, here’s my code with an explanation of what it does and how it works. If you know how to modify your WordPress template files, you should have no trouble customizing this to meet your needs and inserting it on the proper places in your template files to get the same results.

In the Page Body

My site’s theme, a heavily modified version of Exquisite, includes a category.php file. This is the template that’s used whenever someone requests a category archive page by clicking a link to a category (as opposed to a single entry or date).

If your theme does not include a category.php file, it probably includes some code in an archive.php file or even the index.php file to specify what should appear on category archive pages. That’s what you should zero in on because that’s where this first bit of code needs to be inserted.

(Frankly, it’s a lot easier if you have a separate category.php page, so why not consider whipping one up? It’s great practice!)

<?php if (is_paged() ) { ?>
<!-- don't display the intro info -->
<?php } else { ?>
<?php if ( is_category('24') || is_category('25') || is_category('26') || is_category('27') || is_category('28') || is_category('29') || is_category('30') || is_category('31') || is_category('32') ) { ?>
<?php include (TEMPLATEPATH . '/langerbooksintro.php'); ?>
<?php } ?>
<?php } ?>

Let’s take it in sections.

<?php if (is_paged() ) { ?>
<!-- don't display the intro info -->

This determines whether the visitor is viewing something other than the first page of the category archive. If he is, all this stuff is skipped. I didn’t want the intro to appear at the top of every page for the category — just the first page.

<?php } else { ?>
<?php if ( is_category('24') || is_category('25') || is_category('26') || is_category('27') || is_category('28') || is_category('29') || is_category('30') || is_category('31') || is_category('32') ) { ?>

This says that if it isn’t paged (in other words, it is the first category page), which category is it? Is it one of the ones listed here by number?

<?php include (TEMPLATEPATH . '/langerbooksintro.php'); ?>

Well, if it is one of those, then display the contents of the file containing the intro (langerbooksintro.php). That’s a separate PHP file I created in the same directory to hold the book support introductory text. I like to keep my template files neat and this was my solution. I could also have replaced this statement with the information I wanted to appear on that first category page.

<?php } ?>

This closes up that second IF statement.

<?php } ?>

And this closes up the first one.

Pretty simple, no?

One thing to remember: the category archive page heading is outside all this conditional stuff. It’s set to display the category name and description as you see formatted in the illustration above. It’s like that on every page of my site.

In the Sidebar

I did pretty much the same thing for the sidebar, but I had to break it down into individual categories. Here’s my code:

<?php if (is_home()) { ?>
<!-- <h2>Announcement</h2>
<ul>This is where I put announcements for the Home page only. It's currently commented out.</ul> -->
<?php } elseif ( is_category('24') ) { ?>
<!-- stuff for MAC OS VISUAL QUICKSTART GUIDE goes here -->
<?php include (TEMPLATEPATH . '/macosvqs.php'); ?>
<?php } elseif ( is_category('25') ) { ?>
<!-- stuff for EXCEL VISUAL QUICKPROJECT here -->
<?php include (TEMPLATEPATH . '/excelvqpj.php'); ?>
<?php } elseif ( is_category('26') ) { ?>
<!-- stuff for EXCEL VISUAL QUICKSTART here -->
<?php include (TEMPLATEPATH . '/excelvqs.php'); ?>
<?php } elseif ( is_category('27') ) { ?>
<!-- stuff for QUICKBOOKS VISUAL QUICKSTART GUIDE here -->
<?php include (TEMPLATEPATH . '/quickbooksvqs.php'); ?>
<?php } elseif ( is_category('29') ) { ?>
<!-- stuff for WORD VISUAL QUICKPROJECT GUIDE here -->
<?php include (TEMPLATEPATH . '/wordvqpj.php'); ?>
<?php } elseif ( is_category('30') ) { ?>
<!-- stuff for WORD VISUAL QUICKSTART here -->
<?php include (TEMPLATEPATH . '/wordvqs.php'); ?>
<?php } elseif ( is_category('31') ) { ?>
<!-- stuff for WORDPRESS VISUAL QUICKSTART GUIDE here -->
<?php include (TEMPLATEPATH . '/wordpressvqs.php'); ?>
<?php } elseif ( is_category('32') ) { ?>
<!-- stuff for OUT-OF-PRINT BOOKS here -->
<?php include (TEMPLATEPATH . '/oopbooks.php'); ?>
<?php } else { ?>
<!-- stuff for everything else goes here -->
<!-- Close All Conditional - THE FOLLOWING APPEARS ON EVERY PAGE -->
<?php } ?>

Again, let’s take it in sections.

<?php if (is_home()) { ?>
<!-- <h2>Announcement</h2>
<ul>This is where I put announcements for the Home page only. It's currently commented out.</ul> -->

This starts a section I set aside for announcements I might want to appear at the top of the sidebar on the Home page. I commented it out because I’m not currently using it.

<?php } elseif ( is_category('24') ) { ?>
<!-- stuff for MAC OS VISUAL QUICKSTART GUIDE goes here -->
<?php include (TEMPLATEPATH . '/macosvqs.php'); ?>

If it’s not the home page, then is it the category page for category 24? If so, I want to insert specific content for that category. I do that by calling a separate file with that information in it. (This helps keep my sidebar.php file small.

I do this for a bunch of book support categories, one after the other. Each one calls a separate file with content I can modify at any time to update book covers, titles, etc.

<?php } else { ?>
<!-- stuff for everything else goes here -->

Here’s where I start to wind up the if statement stuff. I created a catch-all area for all pages that don’t meet the previous criteria — either the home page or one of several specific categories. If I wanted to include sidebar content for those pages, I’d put them after this. I don’t, so there’s nothing here. But I’m prepared!

<!-- Close All Conditional - THE FOLLOWING APPEARS ON EVERY PAGE -->
<?php } ?>

And finally, this is where I close the conditional statements. Everything after this appears for all pages.

Conclusion

You can use these basic constructions your your WordPress theme files to display different content for different categories or types of pages. It’s the same basic formulas, so to speak, with different ingredients. Doing this makes it possible to completely customize your pages based on content.

Try it for yourself! Just remember to keep backup copies of all of your modified theme files — just in case you mess up and need to start over again.

Good luck!

Create an Automated Backup Plan with Fetch and iCal

Another one of my Informit.com articles goes online.

Create an Automated Backup Plan with Fetch and iCal” explains how you can use low-cost FTP software and iCal’s scheduling feature to create offsite backups of your important files. All you need is space on an FTP-accessible server.

The article is free at Informit.com.

I wrote this article before my February hard disk crash. If only I had taken its advice for more than just a handful of files!

How I Use the Readers Post Plugin

A brief how-to in response to a visitor comment.

In recent comment on my article about WordPress 2.1 Plugin Compatibility, Julie said that she was having trouble getting the Readers Post plugin to work. I added this plugin to my site a few months ago and wrote about it here. That piece didn’t include any how-to information.

What It Does

The Readers Post plugin by Stefan Groenveld is a three-trick pony that offers the following features:

  • readers() returns a count of the number of people who have read a post.
  • last_posts() displays a list of the most recently read posts.
  • hot_posts() displays a list of the post popular posts, including a reader count.

I use all three of these options on my site. But rather than go into detail about the parameters — you can find that on the plugin’s page; scroll down for English — I’ll provide the code I use as an example.

readers()

This function must be used within The Loop. (If you don’t know what the Loop is, look it up in the WordPress Codex or, better yet, buy our WordPress book, which explains it in detail.) I use it in my post “footer” to display the number of times a post has been read along with a bunch of other stuff. Here’s the code snippet for readers():

<?php if(function_exists('readers')) {readers('Read ',' Times'); } ?>

This code does two things:

  • ?php if(function_exists('readers')) checks to see if the Readers Post plugin is installed. (Actually, it checks to see if the readers() function is available.) If it is available,
  • {readers('Read ',' Times'); } displays the results of the readers() function with the simple before and after parameters I’ve provided.

The result looks something like this — I put a red box around the code’s actual output:

The readers() function in action

last_posts()

I use last_posts() in the sidebar to list the 8 most recently read posts. I find this list of posts fascinating. Each time I visit the site, I look at the list to see what people have been reading. Sometimes I’ll see an old post listed and click the link to revisit it myself. I hope other people find some of the titles intriguing and visit some of my older posts, too.

Anyway, the code I use is as follows:

<?php if (function_exists('last_posts')): ?>
&nbsp;&nbsp;&nbsp;<?php last_posts(8); ?>
<?php endif; ?>

This does the same thing as the code listed above for readers(), but it uses a different syntax for the conditional (if) statement. 8 is the parameter for the number of posts to display.

On my site, this code is enclosed within <ul> and </ul> tags for formatting — most templates use those tags to present information in the sidebar, but your theme may be different. I also put a heading over it to explain what it is.

The result, at this very moment, looks like this on my site’s Home page:

The last_posts() function in action

hot_posts()

I also use hot_posts() in the sidebar. It lists the 8 most popular posts — the ones read most. Keeping in mind how much I love stats, you can imagine how much I like this information. To me, it’s like a horse race, with certain posts racing to have the most hits. When a new post passes an old one, I’m always kind of tickled. (Yeah, I know what you’re saying. What a geek!)

Anyway, here’s my code:

<?php if (function_exists('hot_posts')): ?>
&nbsp;&nbsp;&nbsp;<?php hot_posts(8); ?>
<?php endif; ?>

Again, this code is enclosed within <ul> and </ul> tags for formatting. And since the Readers Post plugin has no way to count posts read before it was installed, I also included a note in small type that indicates when the counts started. I’ll probably remove that count sometime in the future. Here’s what it looks like on my site’s Home page:

The last_posts() function in action

Some Tips

If your blog doesn’t get many hits (yet), I don’t recommend using readers() or hot_posts(). The numbers they display might be depressing. I think (but am not sure) that the plugin starts counting as soon as it’s installed and activated, so you could always add these two functions at a later time and have counts displayed from the installation day rather than the first day the function is used. And site administrator visits are not counted, so you can re-read any post you like without skewing the count.

last_posts() will always be a great addition to the site, almost like a “suggested reading” list for visitors — and a re-reading list for you.