Stacy's Notes

PowerPoint: Why is my video playing as a white box?

2010-03-04T23:48:00.001-05:00

Here's an interesting little problem. You've inserted a video into a slide of your presentation, positioned it where you'd like it, then run your presentation and... it doesn't work. It's showing up as a white box. What's the deal?

You try playing the video outside of PowerPoint, and it works just fine. You check to see that it's in a PowerPoint-compatible video format, and it is. So why won't it play?

The answer is this: the filepath for the video is too long. PowerPoint can only handle media files with filepaths of 128 or fewer characters. So if the video is hiding away in C:\\A\Very\Obscure\Folder\Somewhere\Deep\Inside\Your\Computer\Or\Perhaps\With\A\Ridiculously\Long\Name, PowerPoint will be unable to locate and play the video in the presentation.

Solutions?

One would be to move the video file to a location with a smaller filepath. Or, move it to the same folder in which your PowerPoint presentation is saved. Even if the filepath is still more than 128 characters long, the video file will play if it's in the same directory as the PowerPoint file.

N.B.: This isn't the only problem that can occur with videos not working in PowerPoint, and I will address some other common issues in future posts or as requested.

Excel: Count the Number of Words in a Cell

2010-02-25T23:43:00.003-05:00

Yet another great question came to me by e-mail this evening: "how do I count the number of words in a given cell?".

You can get this answer using a formula! Here it is (please substitute 'A1' with the cell reference of your choosing):

=LEN(TRIM(A1))-LEN(SUBSTITUTE((A1)," ",""))+1

And for those of you that are curious as to what exactly this formula is doing:

LEN returns the number of characters.
TRIM removes extra spaces at the beginning or end of the string.
SUBSTITUTE replaces the character of your choosing with something else. In this case it's replacing spaces (" ") with nothing ("").

So, this formula is taking the number of characters in cell A1 (disregarding any extra spaces at the beginning or end), and subtracting from it the number of characters in cell A1 if it had no spaces. Add one and you get the number of words.

Wait, what?

You're right, I'm not very good at explaining this one. So I'm going to show you an example:

If cell A1 contains "Stacy DuBois", the formula is taking 12 (how many characters there are) minus 11 (how many characters there are without the space in between my first and last name). That gives you 1. Add 1 and you get 2. That's how many words are in that cell. This will work with any number of words in a cell. Ah, the wonders of math!

PowerPoint: Supported Video File Formats

2010-02-18T23:27:00.000-05:00

Tried to insert a video in PowerPoint and it didn't work? Maybe it was in a file format that isn't supported by PowerPoint. Here's a list of supported file formats:

Motion Picture Experts Group
.mpg
.mpeg
.m1v
.mp2
.mpa
.mpe

Microsoft streaming format
.asf
.asx

Microsoft Windows Media Video
.wmv

Audio Video Interleave
.avi

QuickTime (v1 or v2.x)
.mov
.qt

If you have a video in any other format, I'd suggest finding and using software to convert it to one that will work in PowerPoint.

OneNote: Can you print the ruler lines?

2010-02-11T02:15:00.004-05:00

A fair amount of digging has revealed to me the unfortunate fact that no, OneNote does not include the option to print the ruler lines on your pages. The only workaround I can see is to actually insert an image of ruler lines on the page yourself, and put your content over it. That's kind of sloppy though, isn't it?

Maybe if we all write nice letters.

Dear Microsoft:

Please let us print ruler lines in the next version of OneNote. Some of us really like the way they look both on and off the screen.

Thanks,

Stacy

Access: Bulleted Lists

2010-01-28T23:31:00.000-05:00

This week's question comes all the way from Germany! I like that I've got people coming to my blog from all around the world, that's pretty neat.

"How do I make bulleted lists on my Access reports?"

This is a very good question! You'll notice that, unlike Word, there's no easy option on the ribbon to make bulleted lists. Thankfully, there's a somewhat simple workaround for that.

Go to design view for your report. In the detail section, go to the text box of the item you'd like to appear in a bulleted list format. As an example, let's say that the text box currently contains ItemName. Now, you're going to trick Access into making bullets next to this item by concatenating a bullet character, a space, and the ItemName. Here's what you'll want to enter into the text box:

= Chr(149) & " " & [ItemName]

Don't worry! It looks like mumbo jumbo, but I promise you it'll work. Chr(149) is the code for a bullet character (like this: • ), then it's putting in a space, and finally the ItemName. If the concept of concatenation is foreign to you, I suggest taking a look at this post where I explain concatenation in Excel.

As a sidenote, if you want to just type out that bullet character (in Windows, anyway), all you need to do is type Alt + 0149. Cool, eh?

Word: Open Documents As Final (Without Reviewing Markup)

2010-01-07T19:54:00.000-05:00

The last question I got, a very good one, was from a very frustrated guy who was sick of opening up his Word documents and having them appear with all of his reviewing markup showing (additions, deletions, comments, etc.). He wanted a way for it to automatically show the 'Final' version. And here is a solution for you! But fair warning, if you use this bit of VBA code, it will not immediately be apparent if changes have been tracked in a document and if there are comments and other markups in the file. Why might this be a problem? If you're planning on sending this file on to someone outside of your company, they might be able to see all of the changes you've made as you've been working on the document... which isn't so great. So please, if you use this code... make sure you accept all changes made and remember to delete your comments before sending it off.

Obviously that doesn't apply if you're printing or making a PDF of the document.

Anyway! On to the code. I'm writing this post with the assumption that you know how to add a macro to your "Normal" template in Word. If this sounds foreign to you, I suggest doing a quick Google search and learn a bit about macros and VBA code in Word. Or, if you're patient, you can wait and I'll eventually make a post explaining that stuff.

There are two codes here, as there are two instances when you might want Word to automatically switch the view to 'Final': the first being when you open an existing document, and the second being when you create a new document.

Sub AutoOpen()
'Sets the revisions view to 'Final' when opening a document
With ActiveWindow.View
.ShowRevisionsAndComments = False
.RevisionsView = wdRevisionsViewFinal
End With
End Sub

Sub AutoNew()
'Sets the revisions view to 'Final' when making a new document
With ActiveWindow.View
.ShowRevisionsAndComments = False
.RevisionsView = wdRevisionsViewFinal
End With
End Sub

Happy New Year, my friends! Here's hoping your 2010 is a safe, happy, and healthy year for you all.

Excel: Join Together Strings Using Concatenate

2009-10-15T17:39:00.004-04:00

Concatenate! It's a bizarre-looking word if you've never seen it before, but the application's actually pretty simple AND very useful.

So here's a scenario for you: You have a spreadsheet listing contact information for hundreds of people. Last names are in column A, first names are in column B. But you need the full name now, for some reason. Here's where concatenate comes in!

Concatenate lets you basically take different pieces of text (called "strings") and put them together. Yes, you can do this with numbers as well, just know that the end result will be formatted as text (there's a way to make it a number, but that'll be a topic for another post). In this case, you're wanting to take the first name, put a space in between, and then have the last name show at the end. Here's the formula to use:

=CONCATENATE(item1,item2,item3,item4,etc.)

Just write "=CONCATENATE", then put in brackets each item you'd like to join together, separated by commas. You can put in cell references, formulas, or even other pieces of text. If you want specific text to show up, make sure you put it in quotes.

So for my example, assuming there's a last name in cell A2 (DuBois), and a first name in cell B2 (Stacy), I'd enter this formula into cell C2:

=CONCATENATE(B2," ",A2)

That's saying: take whatever is in cell B2, then put in a space, then put in whatever's in cell A2. So for this example, my formula would return:

Stacy DuBois

And of course, you're free to fill this formula down through your datasheet and watch as all of the names (or whatever it is you're doing) are automatically generated.

Some people like to use this to generate usernames. You can use the LEFT function to pull the first letter of the first names, and use concatenate to join it with the last name.

The possibilites are endless!

Thank You

2009-09-29T19:19:00.002-04:00

This message is coming later than I'd intended, as I've been rather busy lately!

I'm pleased to say that I placed 9th for Canada at WorldSkills Calgary 2009, and my high score earned me a Medallion of Excellence. What an amazing experience it was! Thanks so much to everyone who has helped me along the way, particularly the fantastic people at Skills/Compétences Canada, Susan Gwin, and Paul Drye from 1-on-1, who helped me prepare for competition.

The opportunities I've had along the way have been incredible. Over the past year, I've received letters and e-mails of support from numerous organizations, software and equipment from sponsors, speaking engagements at various events, and a bit of media attention (which has been pretty neat!).

Last, but certainly not least, I'd like to thank my friends and family who put up with my stressed out self for the past year - I couldn't have done this without their constant support.

Congratulations to Team Canada for bringing back 8 medals and 12 medallions of excellence! To anyone out there who's considering a career in a skilled trade or technology - go for it! It's amazing how far a pile of hard work will get you.

-Stacy

Word: Non-Breaking En Dashes and Spaces

2009-09-24T19:00:00.000-04:00

Ever type something like a phone number in Word with hyphens, and get irritated when the phone number gets broken up on two separate lines like this?:

You can contact me any time you like! My phone number is 555-555-
5555.

Sometimes you'd prefer the number to stay together in one piece, like this:

You can contact me any time you like! My phone number is
555-555-5555.

In instances like this, most users would just press the Enter key before the phone number to make sure it's on its own line. But that's kind of sloppy, because you might add text later that makes it unnecessary for it to be the start of a line. It would be best to use what's called a "non-breaking en dash". It's a way of putting in a dash and telling Word to make the ends "sticky" (so to speak) and keep everything next to it together.

How? Instead of just pressing the '-' key like you normally would, hold down 'Control', 'Shift', and '-'. This will insert a non-breaking en dash.

You can also make a "non-breaking space" using the same technique. Just hold down 'Ctrl', 'Shift', then press the space bar. This is pretty useful for things like legal documents where an entire name needs to stay together on one line. You can put a non-breaking space in between the parts of the name to make sure they'll stay together.

Gone for now, back in a couple of weeks!

2009-08-25T10:20:00.004-04:00

I'm very, very busy getting ready to represent Canada next week at WorldSkills Calgary 2009. Unfortunately I won't be posting any new tips and tricks until everything's over - but you can expect much more interesting/advanced content later! I haven't been sharing my best tricks yet, since I don't want to give the other competitors any great ideas.

Check out the official WorldSkills Calgary 2009 web site, and cheer on Team Canada as we take on the world next week! I'll be there to show my skills in the IT/Software Applications category.

Ciao for now!

Stacy

Excel: Currency Format Shortcut

2009-08-13T09:05:00.001-04:00

Last week, someone asked me if there's a keyboard shortcut to change a cell's number format to currency - and I'm pleased to say that yes, there is! And it's pretty easy to remember too:

Ctrl + Shift + $

This will change the number format of whatever cell(s) you have selected to currency format (with the default dollar sign and two decimal places).

Word: Where did my ruler go?

2009-08-06T11:50:00.002-04:00

This is a really common question I get from new Word 2007 users. I'll answer it in today's entry: where did my ruler go?

Don't worry! It's still there, you just need to make it visible. Go to the 'View' tab on your ruler, and check off the 'Ruler' box (it's in the 'Show/Hide' group on the left-hand side of the ribbon).

How to Save as PDF

2009-07-30T11:52:00.003-04:00

Want to save something in Office 2007 as a PDF?

Click here to go to the Microsoft site and download their PDF add-in (the grey 'Download' button).

Once installed, you'll have the option to save as a PDF in the 'Save As' menu option.

This add-in works with the following programs:

Access 2007
Excel 2007
InfoPath 2007
OneNote 2007
Office PowerPoint 2007
Office Publisher 2007
Office Visio 2007
Office Word 2007

Excel: Add a Background Picture

2009-07-23T11:15:00.000-04:00

Ever wanted to use an image as a background in your spreadsheet?

Just click on the 'Page Layout' tab of the ribbon, then click the 'Background' button (it's in the 'Page Setup' group). Look through your folders until you find the image you'd like to use, then click 'Insert'.

PowerPoint: Shortcut to Insert New Slide

2009-07-16T17:33:00.005-04:00

Adding a new slide to your PowerPoint presentation is as easy as hitting:

Ctrl + M

If you're wanting to use your mouse and you're not in the 'Home' tab of the ribbon, you can right-click on the left-hand side of your screen where your slides are all listed and click 'New Slide' from there.

Excel: Prevent Auto Open Macro from Running

2009-07-09T21:22:00.002-04:00

Have an Excel workbook you'd like to open, but don't want an auto open macro to execute? Press your Shift key as you open the file, and keep it pressed until it's completely open. This will prevent the auto open macro from running.

Inserting a Comment in Excel (and shortcuts too!)

2009-07-02T03:02:00.004-04:00

This week's question: "How do I insert a comment into my Excel spreadsheet?"

As with most of Microsoft Office, you have a few different options. If you click on the 'Review' tab on your ribbon, you can click 'New Comment' (fifth button from the left).

Another way to add a comment is to right-click the cell you'd like the comment in, and choose 'Insert Comment' from the menu that pops up.

Lastly, I'll share with you the keyboard shortcut. Hit Shift + F2, and you'll find a new comment waiting for you in whichever cell you currently have selected.

Excel: Insert a Picture

2009-06-25T14:55:00.003-04:00

Want to include a picture on your spreadsheet? It's quick and easy. Go to the 'Insert' tab, and you'll see the third button from the left is 'Picture'. If you click it, you can browse through the folders on your computer until you find the picture you'd like to include. Click 'Insert', and you'll find the image sitting in whichever worksheet you're currently working on. You can resize it and move it around just like any other object until it's positioned as you'd like.

Alternatively, you can copy and paste pictures from other sources - like the Internet, or another file.

Word: Convert Fields to Regular Text

2009-06-18T12:47:00.002-04:00

This handy shortcut will basically stop a field from being a field. It's sort of like changing a formula into a straight value in Excel. Select the field in Word and press:

Ctrl + Shift + F9

So, if you have a field in Word that performs a calculation, or maybe it's a picture brought in through mail merge, you can select it and use this shortcut to take away the field entirely and just leave you the text (or image). You'll no longer be able to "update" it, as it won't be a field anymore.

Turn Off Gridlines in Excel

2009-06-11T12:30:00.003-04:00

Those gridlines you see in Excel can be turned off, if you'd like. You can still go ahead and put borders around cells, but to turn off the display of the entire grid, go to the 'View' tab on the ribbon, and look in the second group - Show/Hide. Uncheck 'Gridlines', and poof! They'll disappear from view. Should you decide you'd like to see them again later, just go back to this same spot and check the box again.

Word: Select All Shortcut

2009-06-04T10:24:00.002-04:00

If you'd like to select your entire document in Word, there's always the keyboard shortcut control A:

Ctrl + A

Something a lot of users don't know, though - if you're more of a mouse user, you can actually just move your mouse over to the left margin on whichever page you're on. You'll see your pointer turn into an arrow pointing a bit to the right. Click three times, and you'll have selected your entire document.

Word: Remove Hyperlinks and Stop Automatic Hyperlinks

2009-05-28T17:43:00.002-04:00

Ever typed a web or e-mail address and been annoyed that it's been turned into a blue, underlined hyperlink when you'd rather it just look like regular text?

My good news for you is that it's quick and easy to undo: just right-click the hyperlink, and click 'Remove Hyperlink'.

If it's not a one-off thing, and you find yourself constantly removing the hyperlinks, you may want to turn that feature off entirely. Click the Microsoft Office button, and choose 'Word Options'. On the lefthand side of the box that comes up, click 'Proofing'. At the top, click the 'AutoCorrect Options...' button. Click the 'AutoFormat As You Type' tab, and uncheck 'Internet and network paths with hyperlinks'. Click 'OK' to get out of both open windows, and now you'll notice your hyperlinks don't automatically get formatted as you type.

Word: Quick Way to Edit Headers and Footers

2009-05-21T08:43:00.003-04:00

Rather than using the ribbon, there's actually an easy way to get into your headers and footers in Word using your mouse. Just right-click anywhere at the very top or bottom of your document, and you'll see you can click 'Edit Header' or 'Edit Footer'. You need to be in the 'Print Layout' view for this to work (that's the default view). It's not that much shorter than using the ribbon, but a lot of people find it easier to get into their headers and footers using this method.

Add Line Numbers to Access Reports

2009-05-14T15:26:00.003-04:00

Someone e-mailed me this week asking me about adding line numbers into a report in Access. Not only is it possible, but it doesn't take very long to set up. Here's how to number each item on your report sequentially:

1. In Design View, add a Text Box control to the 'Detail' section (where you'd like the numbers to show up). You'll probably want to delete the label associated with it.

2. Right-click the control, select 'Properties' (you can skip this step if your property sheet is already open). On the Properties sheet that appears, click the 'Data' tab.

3. In 'Control Source', enter an equals sign and the number one: =1

4. In 'Running Sum', choose 'Over Group' or 'Over All'. Choosing 'Over Group' means that it will start over at 1 when a new group starts. 'Over All' will keep numbering up over the entire report, regardless of grouping.

That's it!

Word: Print Without Highlighter Marks

2009-05-07T11:15:00.002-04:00

Sometimes you're working on a document and you've highlighted all sorts of things in it, but you'd like to print it without all the highlighter marks. You don't have to go through and unhighlight everything, you can actually temporarily turn them off. Here's how:

Click the Microsoft Office button, and choose 'Word Options'. On the lefthand side of the box that comes up, click 'Display'. The second checkbox from the top lets you 'Show highlighter marks'. If you uncheck it, you won't be able to see the highlighter marks in your document. That also means that if you print, you won't see them. And then, when you're done, you can go back in and check it again and all of your highlighter marks will come back. Simple!