Wednesday, April 23, 2008

How do I change the colour of Excel's grid lines?


Above you see Excel sheets with pink gridlines and green gridlines. How do you do that? Watch the screencast below or scroll down for text instructions.




To change the colour of your gridlines, click on the Office Orb and select Excel Options from the bottom right corner of the menu.


With Options now open, locate the Advanced option as shown on the left-hand side of the menu.


Scroll down the page until you come to Display Options. Here you will find a Gridlines colour picker. You can choose to apply the colour to any sheet in your workbook .... you can even have a different colour on each page.



Click OK and you are done. That's all that there is to it.

To change it back to the default, repeat the steps Excel Options > Advanced> Gridline Colour and this time select "automatic" on the colour picker.

Sunday, April 20, 2008

How do I apply themes in Excel 2007?

Applying a theme to an Excel document has never been easier. Example from John Walkenbach's Excel 2007 Bible.

Themes are a quick and simple way to uniformly format your document (see more examples below or view the clip above to explore the possibilities).


To apply a theme, select the Page Layout tab on the Excel 2007 ribbon. The first group of options relate to themes.

Excel comes with several preset themes you can choose from. Browse through these by using the down-arrow on the Themes button.


Simply select the theme that appeals to you or individually apply theme changes using the nearby Colours, Fonts of Effects buttons. You can even create a personal custom theme and save it to your computer for super-swift formatting the next time.

How do I stop text from running into other cells in Excel?

There are a few ways to go about tackling this problem. The simplest method is to use simple text wrapping.



Text wrapping will extend the height of your cell in order to accomadate the text instead of having it run accross the page and into adjacent cells.


To wrap a cell, click into the cell you want to wrap and select the Home tab. Scroll across to the Alignment section. Here you will find the Wrap Text button.

Select it and then you're done! It's as simple as that.

How do I write off bad debts in MYOB?

Writing off bad debts in MYOB involves creating a negative invoice and applying it to the outstanding balance. Care should be taken to adjust for any GST previously recorded. View a video demonstration or scroll down the page for illustrated text instructions.


The example images assume an outstanding invoice to be written off totalling $1,210 GST inclusive.

Step 1: From the Sales command centre, select Enter Sales. You may need to change the invoice layout by selecting the Layout button and opting for a Miscellaneous invoice style.

Step 2: Proceed to fill in details regarding customer and date as you would normally.


As shown in the above image (click to enlarge), on the first line, you will want to use the income account the sale was first applied to (such as sales or services revenue, etc). Leave the amount field here blank.

Next, you want to record the tax-exclusive amount to the Bad Debts expense. To do this, you will enter the figure as a negative.

Finally, to write back the GST collected, you record the GST portion of the invoice to your GST Collections account as a negative value.
MYOB will warn you that this is not usually the account you would use - ignore the warning box by clicking OK. Record the invoice.

Step 3: From the Sales command centre, select Sales Register. We need to locate the negative invoice we just generated. The Sales and Return tab is usually the quickest way to find it. Select the invoice and click on the "Apply to Sale" button at the bottom of the screen.



You will be presented with a list of open invoices for this debtor. Enter the amount you are applying (the total of the negative invoice should insert itself by default) and record in order to write of this bad debt.


That's all there is to it! Be sure to check the balance of your accounts afterwards to verify you have recorded everything properly.

Thursday, March 6, 2008

How do I set different page orientations within the one Word document?

Setting different page orientations seems to be a great source of frustration for a lot of people, but it doesn't have to be. To make some pages portrait and others landscape is simply a matter of using the right breaks.

Take a look at the video demonstration below or scroll down for text instructions.


In order to change the orientation of just SOME of our pages, we need section off the area. A common mistake people make is to go after the page break. What we want to do is use a Next Page Section Break.

Position the cursor at the end of the page before the section you want to create. Select the Page Layout tab on your ribbon and click the arrow next to Breaks. Under sections breaks, choose Next Page (see image below). Repeat this process again at the end of the section you are creating.



You should now have three sections. Place your cursor into the section you want to change. Now, change the orientation to landscape (Page Layout >Orientation). If your breaks were set correctly, you should now have some pages as portrait and the rest in landscape view (see below).


This is just one of the practical uses for section breaks. We'll explore more in the coming weeks.



Monday, March 3, 2008

How do I enable text-to-speech in Excel 2007?

I like to multi-task so there are times when having Excel read back to me the values of columns or rows is a great time saver. Of course, this is fabulous option for anyone with accessibility issues too.

Text-to-speech is not available in Word yet, but you can turn it on in Excel. Here's how:



You'll see a little drop-down arrow next to your Quick Access Toolbar. Select this and choose More Commands (see image above).

Excel will open your Excel Options menu. By default, you'll be presented with the Most Popular choices. Change this to choose commands from All Commands. Now just scroll down the list and select the text-to-speech commands you want to add (they all start with Speak as seen below). Add and click OK.


I've found Excel's text-to-speech to be quite clear in reading back everything from dates to plain text to data sets. Now, when you want to use it simply select the command you need from your Quick Access Toolbar.

Sunday, March 2, 2008

How do I change Excel 2007 to save in 97-2003 format by default?

Excel 2007 will save in the default file format. In most cases this will be as an Excel 2007 workbook. This can be really frustrating if you forget to change the file type on saving and realise it won't be compatible to the person you are sending it to.

Fortunately, you can save yourself this aggravation by changing the default save format to 97-2003 if you prefer. To do this, click the main office button and locate Excel Options on the bottom right of the menu (see below).

Select Save from the left side menu. From this panel you can customise how workbooks are saved. The first thing you'll notice is a drop down list next to where it says 'Save files in this format.' (see below). The default Excel Workbook refers to your 2007 version. Scroll down a couple to select 97-2003 Workbook.

Click OK and you are done. Now Excel will automatically save in 97-2003 format by default. Of course, you can change this back again or choose to save an individual workbook in another format at the time of saving.

Saturday, March 1, 2008

How do I refresh all my tabbed pages at once?

If you are like me, you like to load several pages at the same time. If your connection drops out, you don't want to have to refresh each page again. Fortunately, you only have to right click.


Simply right click on any tab. A drop-down menu will appear and you have the option to 'refresh all'. Easy!

How do I change Word's default font?

Users new to Word 2007 will soon realise that the default font is no longer Times New Roman, but has changed to Calibri. It's my understanding that Calibri is best suited to onscreen reading, but not necessarily ideal for printing.

If, for whatever reason, you want to change the default font back to Times New Roman - or anything else for that matter - here's how to go about it.




On the Home tab of the ribbon, click the drop-down arrow near the Font group (as shown above). You'll be presented with the Font menu (below) and can enter your preferred font options.

Once you have the options set (you can see I have selected Times New Roman, regular, 12pt) click the Default button at the bottom left of the box. Word will pop up with a warning box (below) to confirm that you really do want to change the default font to the current selection.





Click Yes to confirm and now you have changed the default font for all future Word documents.

How do I protect my private Word documents?

If you have private information in a Word document, you might want to encrypt and password protect it. To do this, simply select the Main Office button and scroll down to Prepare. From here, select Encrypt Document (see image below).

Word will ask you to put in a password. It is important that you do not lose or forget the password you choose because passwords are not recoverable! Word will promt you to re-enter the password again to confirm.


The next time you (or someone elsle) tries to open this Word document, a password prompt will appear like the one shown below.

No password = no access to the file. It's as simple as that.

How do I add background colours and effects to my Word docs?

Adding background colours and/or effects to liven up your documents is simple.

Background colour: Select the Page Layout tab on your ribbon, locate Page Colour (it's included in the Page Background section). Word 2007 allows you to quickly run your mouse over the colours in order to preview what each will look like on your page before committing to a selection (see below).


Background Effects: Background effects are just as simple and can really jazz up the appearance of your page. With the page colour menu open, scroll down to Fill Effects. Here you will have several options. I have decided to use a two colour gradient fill (see image below).

You can play around with mixing styles, colours, textures and even images. Select OK when you are happy with your selection and now you have a colour background to your document.

Friday, February 29, 2008

How do I get the ruler back in Word 2007?


Wondering what happened to the ruler accross the top of your page in Word 2007? In order to show/hide the horizontal ruler, you need to click on the tiny little "view ruler" icon on the right of your document (I've highlight it above).

The vertical rulers should also come on by default, but if not this must be changed through Word Options. To access this select the main Office button>Word Options.

Select Advanced and then scroll down to Display. Here you will notice an option to turn vertical ruler on when in print layout view. Make sure there is a tick next to this.


Now you can turn all your rulers on and off as needed when in print layout simply by clicking the little ruler icon at the top of your right scroll bar.