Sunday, May 4th, 2008

Excel – reuse chart formats

charttype 711030 Excel   reuse chart formats

You’ve gone to all the trouble to format a chart nicely and you’d like to reuse the format again some time in the future. Instead of recreating the format each time, save it so you can apply it with a single click.

In Excel 2003, right click your chart and choose Chart Type > Custom Types tab and click the User-Defined button. When you do this an Add button appears – click it and type a name and description for your chart when prompted to do so. Click Ok twice when you are done.

Now, in future, when you create a chart you can select this format from the Chart Wizard options or apply it to an existing chart by selecting the chart, right click and choose Chart Type > Custom Types and click User-defined. Select your format and click OK to apply it to the chart.

One word of warning, for some reason, Excel includes chart titles as a format so you’ll lose your existing chart title if you have one when you apply the new format to it. It’s not a big deal but it helps to know that it’s going to happen.

Helen Bradley

Wednesday, April 30th, 2008

Sorting data in Excel

sort 794744 Sorting data in Excel

Sometimes the data in your worksheet doesn’t appear in the order you want it to. You can reorder the data by sorting it.

To sort data in a worksheet, select it using your mouse, or click inside the data area and press Control + Shift + * to select all the cells in the current block. In Excel 2003, choose Data, Sort and then choose the column which contains the data to sort from the Sort by: dropdown list. To sort on more than one column choose the second column from the dialog’s Then by: dropdown list. For example to produce a ‘phonebook’ type of sort, first Sort by the column containing the last name and Then by: the column containing the first name. If you do this, two people with the same last name will be grouped together but appear in alphabetical order by first name.

You can also sort in order of a custom list. So, for example, if you wanted all the people who live in Victoria to appear at the top of a list then those who live in NSW, etc.. create a custom list with the state names (or abbreviations) in the order you want to use for sorting. Then choose the State column in your data list as the Sort by: column and click Options. From the First key sort order dropdown list choose the custom list containing the state details and click Ok, and Ok again. The list will be sorted in order of the entries in your custom list and any entries which don’t match an item in the list will appear at the end, in alphabetical order.

Helen Bradley

Saturday, April 19th, 2008

Kiosk Presentations

pp kiosk 789255 Kiosk Presentations

You’re not always going to be there when a presentation shows. Some PowerPoint presetations are shown on computers where not one is in attendance or you might want to send an autorunning slideshow to mum to show off your latest pix.

These work if they’re set up as Kiosk presentations. You need to set the presentation so the slides more forward after a period of time and not by mouse click. Do this on the PowerPoint 2007 Animation tab. Then choose the Slide Show tab and click Set Up Show. Choose Browsed at Kiosk and make sure the Advance slides option is set to Use Timings if present. The the show will run automatically without needing attention.

There you have it, hands free PowerPoint slide shows.

Helen Bradley

Friday, March 21st, 2008

What’s in a name? Auto_Open or AutoOpen

Sometimes you wonder if the folks up at Redmond are laughing at us behind our backs. Really, do they deliberately set out to confuse us or are they just that plain disorganised?

Today my quandary involves Auto_Open and AutoOpen. These are two special macro names. The first, Auto_Open is Excel’s special named macro that runs automatically when the workbook containing it is opened. AutoOpen is the Word equivalent. It makes no sense that one has an underscore and the other doesn’t – it just makes life for us VBA folk a little more confusing than it should be.

The other macros Auto_Close and AutoClose work the same way, Auto_Close is the Excel macro name – call a macro by this name and save it in your workbook and it will run whenever you close the workbook. In Word, the name is AutoClose.

To add to the confusion, PowerPoint doesn’t support either of the naming conventions, in fact, you can’t create auto running macros in PowerPoint the same way you do in Word and Excel. The workaround is cumbersome, you need to create a PowerPoint add-in that includes the Auto_Open subroutine. Load the Add-in and PowerPoint will run the code in Auto_Open it loads and ditto for subroutine called Auto_close – it runs when the add-in is unloaded – which happens automatically when you exit PowerPoint. Learn more about how to do this in this KnowledgeBase article.

Thanks Redmond, we are now officially confused!

Helen Bradley

Thursday, March 13th, 2008

Zap those hyperlinks

hyperlinks 708479 Zap those hyperlinks

When you copy and paste text from the web into a Word document, typically the hyperlinks come too. If you don’t want them – if blue underlined nonsense peppering your text offends your sensibilities (as it does mine) – here are some thoughts for removing hyperlink formatting from your text.

One option is to select the link and choose Insert > Hyperlink (or press Control + K) and click the Remove Link button in the dialog. That’s the hard way in my book.

Easier still is to select the text and just press Control + Spacebar. That strips the formatting from the text – leaving it as plain text. It also works to strip formatting from any text, a handy shortcut to know.

Now, if you get hyperlinks whenever you type a URL or email address you can stop this from happening by choosing Tools > AutoCorrect Options > AutoFormat as you Type tab and disable the Internet and network paths with hyperlinks option. Now you can type all you like and the AutoFormat won’t affect your text.

Helen Bradley

Wednesday, March 12th, 2008

PowerPoint to Go – on your mobile or iPod

You can put a PowerPoint presentation on almost any mobile device including your iPod.

Provided your mobile device supports JPEG format images – most will – open your presentation in PowerPoint and choose File, Save As and select the JPEG format, choose All Slides and PowerPoint will save the slides as JPEG format files that you can now upload to your mobile device as you do any other photos.

If your mobile lets you play images as a slideshow – voila! PowerPoint to go!

Helen Bradley

Saturday, March 8th, 2008

Microsoft offers free accounting

I found this recently and it’s a pretty smart offer for anyone who is used to using Microsoft products and who needs an accounting tool.

Microsoft Accounting 2008 Express Edition is free and you can download it here. It includes tools for creating quotes, invoices and receipts as well as tracking expenses and managing online bank accounts. It has two main attractions, one is that it’s free and second – with the look and feel of other Microsoft products its easy to get up and running.

Helen Bradley

Saturday, March 8th, 2008

No new line in PowerPoint

When you are entering text on a PowerPoint slide if you want to create a new line but not apply a bullet to it press Shift + Enter at the end of the preceding line.

This creates a new line but does not start a new paragraph which is the trigger for the bullet to be created.

This also works in Word – you can create a new line in a numbered paragraph but without adding a new number by pressing Shift + Enter.

Helen Bradley

Friday, March 7th, 2008

MS Publisher Viewer – workaround

pdfonline 729916 MS Publisher Viewer   workaround

One of the most active keyword searches that I have with my blog is for a Microsoft Publisher Viewer. If you’re familiar with Publisher you’ll know that the short answer is that there is no Publisher viewer application. If you’re familiar with my blog, you’ll know I post cool stuff too.

So, with this in mind, today I have a sort of solution for the Publisher viewer program. It’s a free online PDF document creator. Visit it at www.pdfonline.com. Here you can upload files in a variety of formats including Microsoft Publisher and files can be up to 2Mb in size – that’s the real limit here. The file will be converted into a pdf format file and emailed direct to you.

It’s simple and effective and a workaround for the Publisher Viewer problem – you get to see and read the contents of the file and that’s what you’re looking for.

Helen Bradley

Thursday, March 6th, 2008

Should be easy – but it isn’t

Ok, I got this question from someone asking how to print a very large pdf file (think measured in feet not inches). They can’t print onto letter or A4 without needing a magnifying glass to see the text. Solution? Well my head says tile it and it should be a feature of the Reader software.

Nice try but no cigar! It isn’t a feature of the software. Adobe if you’re listening, you so have to add this as a feature, it’s almost inexcuable that it isn’t there. You should be able to specify the number of pages tall and wide to print a single page onto.

Ok, so next thought is a poster program. One that takes an image/file and breaks it into pieces. I’d settle for one of two methods of operation – ideally it would open a PDF file and just print it for me – ok, enter fantasy land Helen! There are poster programs but they take images and not PDFs – not entirely unexpected but it was worth a look.

Other accepted method would be for the program to operate as a printer driver. I would print from Reader to the driver and then it would handle the tiling and final printing. Good concept and, as it happens there are a few options out there. Funny thing is, most I downloaded and installed and tried didn’t work. Very strange, frustrating and annoying. Final solution worked and did so pretty well. It’s called Click2Poster and it’s from Blue Squirrel. It installs as a printer drivers so you print to it from your applicaiton. This overcomes the file compatibility issues as, provided you can open a file in something and print it, this program will capture it. Then it opens automatically (I liked this feature), with your print job on the screen. You choose how many tiles and the printer and send it off to print. It works, which by that stage of the day put it in the “miracle software” category!

The trial version slaps its own stuff on the page but shell out a measly $19.95 and all your poster printing problems will be solved. I can see it would be useful for printing large versions of your images for wall art and all sorts of things. In the meantime, my happless reader can print and read his/her pdf files so I have another happy reader. I like those!

Helen Bradley

Tuesday, March 4th, 2008

MIA – Word 2007 loses Versions

versions2 767370 MIA   Word 2007 loses Versions

There was a handy utility in Word prior to and including Word 2003 which disappeared without ceremony in Word 2007 called Versions. The premise was that you could save multiple versions of a document – like a snapshot of the document at different stages of its development – inside the one file. The document took with it a history of what it had looked like at various times in its past. To use it, choose File > Versions and then just save a version now or set it to save one automatically each time you close a file.

If you didn’t use versioning, you may not mourn its passing. If you did use it you are left wondering why it got axed. It was a handy tool, it worked, it did a job that meant many folk who might otherwise have lost work permanently actually had the backups of their work on hand when they needed it. Nice work Microsoft – NOT!

Anyway the clever folk at Edenic Software have created a neat little add-in which puts Versions back into Microsoft Word. The tool is called Document.Versions and it installs as a new Ribbon entry. It works pretty much like the old tool but this one is nice and visible and made by folks who care so it’s likely to be around for quite some time. I’ve been trialling a Beta version and the final release is out very soon.

If you loved versions and if you’re annoyed (I’m trying to be polite here) that Microsoft took away a great tool, then take yourself over to OfficeVersions.com and grab a trial version of the add-in. You can trial it for 30 days and then buy a licence for less than it will cost in your time to get back information you’ve lost when someone edits your document without turning on Tracked Changes, for example!

Oh, and look out for other .Version tools coming soon. Versions add-ins for Excel and PowerPoint are planned – called Spreadsheet.Versions and Slideshow.Versions. While versioning was never a feature of either Excel or PowerPoint that doesn’t mean these won’t be very handy additions to Excel 2007 and PowerPoint 2007 too.

Helen Bradley

Monday, February 25th, 2008

Leading zeros in Excel

This is a fun solution. You want to put a series of numbers in a range in Excel and you want them to have leading zeros. So you want, 001, 002, 003 etc. Problem is that Excel drops the zeros when you type them. It makes sense, leading zeros aren’t required and really don’t aid comprehension. However, for your own reasons, you want them.

Here’s how to do this:

Select the cells and choose Format > Cells > Number tab. Select Custom from the Categories list and in the box marked Type:, type this:

00#

This tells Excel that there must always be 3 digits showing which forces leading zeros to be displayed. It doesn’t do anything to the numbers so they are still numbers which is just as you would want it to be.

Helen Bradley

Tuesday, February 19th, 2008

Calibrate your HP Tablet screen

I am the happy owner of a HP T1000 tablet but till today I had a screen that was a bit off. The stylus had to be used just to the left and a little up of whatever you wanted to select – very annoying indeed. The problem should have been easily resolved, simply recalibrate the screen. Duh! but where is the recalibrate option? I don’t know. I checked the Control Panel and the pen tools. There is plenty of information on how to use the pen and how to flick and tap it, but no detail on how to make the screen work properly with it.

My screen has been out of calibration for some time now. Finally, today I found the solution. Choose Start > All Programs > Tablet PC Settings and click the General tab. There you’ll find the Calibrate button – click it and then calibrate the screen and save the resulting settings. Easy when you know how but devilishly hard to find when you don’t.

Helen Bradley

Wednesday, January 30th, 2008

Outlook: Managing Blocked Senders

blockedsender 741545 Outlook: Managing Blocked Senders

I’m sure you’ve had that “that was a dumb thing to do” feeling before. You do something that you thought was smart but ends up being very silly indeed.

I got that recently when I added someone to my Outlook Blocked senders list. Yikes! they were so not supposed to go there!

Luckily it’s fairly easy to undo the damage. In Outlook choose Tools > Options > Preferences tab and under the E-mail options click Junk E-mail. Click the Blocked Senders tab and locate the email address that shouldn’t be there and click Remove. Simple!

Now, if you need to go in reverse and add someone, here’s how (believe me, I’m astonishingly good at this step): In Outlook choose Tools > Options > Preferences tab and under the E-mail options click Junk E-mail. Click the Blocked Senders tab. This time click Add, type the email address or an entire domain name (but be sure you really want to do this!) and click Ok.

If you have an email from the person in your inbox there is an even easier solution. Right click the email, choose Junk E-mail > Add Sender to Blocked Senders List.

There you have it.. get someone onto the Blocked senders list and get them off again. Better still, it works in Outlook 2002, Outlook 2003 and Outlook 2007.

Helen Bradley

Saturday, January 26th, 2008

Excel 2007 makes Lovely Lists

lists 737621 Excel 2007 makes Lovely Lists

Lists were a big addition to Excel 2003 as they allowed you to work with list data in Excel more easily than ever before. One key plus was that they let you create charts that expanded automatically as the data in the list grew. This was something you simply couldn’t do before very easily.

Now in Excel 2007 lists are called tables and they are simple to create using the Format As Table option on the Home tab on the Ribbon. One gotcha is that you shouldn’t use a table format if you don’t want to create a list, instead use the much more cumbersome and much less pretty Cell Styles options.

When you create a list you automatically get Filter buttons for the list. If you don’t like or want them, disable them by clicking to disable the Filter button on the Data tab – just make sure your cell pointer is somewhere in the list when you do this. Like in Excel 2003, if you create a chart based on your table, it expands when you add new data to it.

Helen Bradley