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.

How do I quickly see which cells I have used in a formula?

Excel 2007 has a few handy functions to find what cell's you have used in a formula. The quickest and simplest method is through the ribbon. On the formulas tab, above the heading Formula Auditing, you'll find Trace Precedents and Trace Dependents.





Precedents affect the value of the currently selected cell.
Dependents are those cells affected by the currently selected cell.




In the example below I have summed the values B2:D2 in cell D4. If I select D4 and click Trace Precedents, Excel will display blue arrows from the cells that affect the formula in this one. This can be a great help when you are dealing with a lot of data on the sheet.


Determining dependents is just as simple. Below, I have select cell A4 and clicked Trace Dependents. A blue arrow appears from A4 to let me know that the formula in B7 is dependent upon this cell.


Tracing cell precedents and dependents can be a great way to view the relationships between values and quickly identify any origiins of errors.

Thursday, February 28, 2008

How do I use Excel's IF function for simple look-ups?

The IF function is a versatile tool in Excel. You can use the IF function for simple lookups, such as returning certain numbers with specified text. Watch the demonstration below to see just how simple it is or scroll down for an overview. Example from John Walkenbach's Excel 2007 Bible.


In the Excel sheet below I have 10 student names in column A and their final marks in column B. To determine the grades in column C, I would type the following into cell C2:


=IF(B2>=50, "PASS", FAIL")



This tells Excel to return the text 'pass' if a student's mark is greater than or equal to 50, otherwise return a 'fail.' Once you have filled in the first cell with your formula, simply fill down the rest of the column.



Excel also allows you to nest IF functions to provide even more choices. In the table above, I have typed into cell c2:


=IF(B2<=49, "FAIL", IF(B2<=74, "GOOD", IF(B2<=84, "GREAT", IF(B2>84, "OUTSTANDING"))))

Wednesday, February 27, 2008

How do I get a large letter at the start of a paragraph in Word?


This is known as a drop cap. You have probably seen drop caps in books and magazines before. Drop caps can be an attractive decorative addition to your page and help to break up the text.

To use drop cap in Word 2007, select the Insert tab on the Word ribbon. Glance across the ribbon until you come to the Text menu options and locate Drop Cap (see image below).


Click the little down arrow in order to bring up the Drop Cap options. You can insert your drop cap dropped into your text (as seen at the top of this post) or place it into the margin area.

You will also notice that you can select Drop Cap Options. This allows you to select the font of your letter, choose how many lines to drop it and how far spaced from the other text you want your drop cap to be.

Have fun playing with the different fonts styles to create very different looks and feels to your documents.

How do I enable check boxes to select files in Vista?


One of my favourite Vista options is the ability to enable checkboxes in order to select files and folders in Explorer. Here's how you do it:

1. Click the Organize button at the top left of your explorer screen and scroll down to select Folder and Search Options (see image below).


2. Click on the View tab and scroll down until you come to the option Use Check Boxes to Select Items. Place a tick next to the option and Apply>Ok.

That's all there is to it! If you would like to see a visual demonstration of this process and the end result, have a look at the video below (my apologies as the audio quality is not as great I would have liked it).


Tuesday, February 26, 2008

How do I ensure Word updates linked data before printing?

I can't tell you how many times I have been typing up a project in Word that has newly changed linked Excel data and I have hit the print button before I update the information in my Word document. It's frustrating to say the least!


To prevent this hapenning, Word 2007 has a great setting you can use to ensure your linked data is updated before printing. If only I had found it sooner.

In Word 2007, click the Office button (that's the big multi-coloured one). At the bottom of the main menu you will notice a button called Word Options (see image below). Click this to bring up your settings.




With Word options now open, look down the left-hand side to select Display. Display options allow you to control how Word presents your document on screen and in print.

Under printing options, place a check in the final option box which says 'Update Linked Data Before Printing.' Select OK.



If you link data into your Word documents, this can be a great option to prevent accidental printing before updates.

How do I copy column widths in Excel?

One of the annoying and time-consuming things about Excel in the past has been the need to set up column widths on each sheet of a workbook. When you copied information accross, the columns widths didn't replicate themselves and you had to format them all over again.

The good news is that those days are over. Excel does allow you to copy and paste your column widths, saving time and keeping everything uniform.

Once you have the columns set up on a sheet you want to have the same widths in other sheets, simply select the columns and copy. Head to the sheet you want to paste those widths into. Instead of the paste command use Paste Special. The Paste Special dialogue box will appear as shown below.


You will notice that there is an option to paste Column Widths. Select this and click OK. Now you can quickly pre-format all the column widths in your worksheet!

How do I revert back to the old pivot table layout?


If you open your Excel 2007 in order to create a Pivot Table, you will no doubt be faced with the new layout (pictured above).

If you feel more comfortable using Excel's familiar pivot table layout, you can alter your settings to show the older style pivot table drag and drop box (pictured below).



To enable the previous version's look, select Pivot Table Tools> Pivot Table Options > Options. When the options box opens, click on the Display tab. Place a check into the box next to Classic Pivot Table Layout.

Now you have all the new functionality with an old faithful look and feel.

How do I create pivot tables in Excel 2007?


Pivot tables are an excellent way of organising large amounts of data, customised to suit your present needs. The pivot table layout is a little different in Excel 2007, but you'll soon find it has really improved and pivot tables are easier to use than ever before. Example from John Walkenbach's Excel 2007 Bible.

I'll include specific written instructions at a later time. For now, the best way to show you just how pivot tables are created is via the above video demonstration.

How do I make a thermometer chart in Excel?

A thermometer chart is a great way of visually showing the percentage of task that is completed or progress towards a specific goal. View the video demonstration below to learn how to create your own thermometer chart in Excel 2007.

What you will need to do is create a chart that uses a single cell reference as a data series (this will be your percentage).

You will then do a little formatting and tweaking of the Data Series (gap width to zero) and Axis options (minimum zero and maximum 1). Insert a textbox and link it to your percentage value (insert > textbox> (draw box where you want it) > F2 > =(select % value cell).

The rest is just cosmetic and you can play around with Excel's formatting options to suit yourself.

Monday, February 25, 2008

How do I add pictures to my chart in Excel?


Customising your excel charts with pictures is a fun and simple process. You can chose to have a picture background, picture plot area or even picture columns. See the video demonstration eblow or scroll down for a few basic instructions. Example from John Walkenbach's Excel 2007 Bible.


Background or plot area picture: Left click into the chart (or plot area only) to select it and then right click to bring up the menu. Choose Format Chart Area (or Format Plot Area if you are only adding the picture behind the series). Under fill options, select Picture or Texture Fill.

Insert from file to browse for the location of the picture on your computer and select the image you require. You can now make any stretch or transparency adjustments as you see fit. Close. There you have a picture background!

Columns as picture: Left click onto the columns in order to select them and right click to bring up the menu. Choose Format Data Series > Fill > Picture or Texture Fill > Insert From File. Locate the image on your computer and select it.

Several options are available to stretch the image, stack the image or scale it. Adjust these as suits your needs. Close. Now you have picture columns!

How do I open a group of favourite sites together?

Adding tab groups to your favourites is a great way to quickly access what you need. Once you have set up your tab group favourites, all pages in a group will open simultanously.

I like tab group favourites for projects that require me to frequent the same few sites. If you shop online, you can add your favourite stores into a tabbed group to open together when you're ready to browse.

From the menu choose Favourites > Add Tab Group to Favourites. Type in a folder name for the group (Fave Stores, assignment, etc) and click Add.

When you select the tab group from your favourites in the future, it will open all of the pages you add to that group. No more time wasted opening each page one at a time!

How do I see all my tabbed pages at the one time?

The quick tabs feature in Internet Explorer lets you see a mini version of each page you have open in your browser on a single page. This can be quite useful if you have a bit going on. Below is a screen shot showing six tabbed pages being viewed in Quick Tabs.




The quickest way to activate Quick Tabs is simply to use the keyboard shortcut CTRL+Q. The menu path is View > Quick Tabs.

Click any mini page to open it or click the small cross on a mini page in order to close it completely.

How do I add another series into a chart in Excel?

Sometimes you may need to add another data series into a chart you have already created. Below I have created the chart with January and February data when the March figures came in. How do I add this information to the chart too? There are a couple of ways of going about it. Let's take a look.



View the video demonstration below or scroll down for written directions on how this is done in Excel 2007.

Method 1:

If the data you want to include is located next to your other data series, click onto the chart to select it. You will notice that a blue outline will appear around the data series. Simply drag the blue outline to include the new data.

Method 2:

If it is not practical to use mthod 1 (maybe you want a data series not located next to the anothers), click into the chart and right click to bring up the menu. Select data > Add. Enter the relevant information. The optional series name usually will refer to the column heading. You can then choose the series value by highlighting the range of data you want to include.

That's all there is to it.

How do I create a combination chart in Excel?

A combination chart is a single chart that can plot series as different chart types. Sometimes this involves using a secondary axis. I have made one below in order to show you what a typical combination chart might look like.


Watch the video demonstration or scroll down for written instructions on how to create your own combination chart using MS Office Excel 2007. Example from John Walkenbach's Excel 2007 Bible.



Creating a combination requires you to change one or more of the data series to a different chart type. This can be done by selecting the series you want to change and then Chart Tools > Design > Type > Change Chart Type.

From there simply select the type of new chart format you would like for that series. I find that lines and columns work well together but you can play around to find what suits you.

What if your data series have very different ranges? In that case you might want to plot one of the series on a secondary value axis.

A secondary value axis will add a new numbered axis on the other side of your chart. To do this, left-click on one of the data series and right-click to bring up the menu. Choose Format Data Series > Plot Series on Secondary Axis.

How do I add a new user in MYOB?

This is one of the most common questions I get asked. MYOB allows you to add users with passwords and set the level of access the new users have. This can be quite useful if you have staff members who enter invoices but do not wish to permit them access to alter banking functions or other sensitive data.

Watch the video demonstration below or scroll down for written instructions.


Step 1: Open preferences by clicking the Setup pull down menu at the top of the screen and choosing Preferences.

Step 2: Click on the tab for Security.

Step 3: To add users and set passwords, click on the Password button at the bottom left of the security window (this is named User ID in version 17).

Only the person logged in as the Administrator can add or edit users and their passwords.

Step 4: To add a user click on the New button. The program will prompt you for the new user details.

Step 5: Enter a User ID and tab to the password field. Type in a password and TAB. Type in the password again to confirm. Click on the OK button.

Step 6: To stop a user accessing a particular function, make sure that user is selected on the left-hand side of the User Access window. Click in the column titled "Not Allowed" against the functions you want to bar. Clicking a function heading selects all of the functions under that heading. Click on the OK button when you are done and that's all there is to it.

How do I list accounts by their name instead of number in MYOB?

A useful MYOB preference you can change is the choice of listing and selecting accounts by either the name of the account or the account number. Personally, I prefer account numbers but I know not everyone does.

Altering the default settings and listing your accounts by name is quick and easy. In fact, you can do it in just three simple steps.

Step 1: From the top menu click set up and then scroll to preferences.

Step 2: The preference settings will open and you should select the "Windows" tab.

As you can see below, the fourth option is "Select and Display Account Name, Not Account Number."


Step 3: To activite this preference simply place a tick in the box. It really is that easy!

When the tick is removed, the accounts will be listed by account number again.

How do I print batch reports in MYOB?

Printing a batch for a selected period is quick and easy.

Step 1: From tne top menu bar go to Reports > Report Batches.

Step 2: In the period field, select the period to date that you want to print the batch for. Highlight the name of the batch you wish to print and click on the Print button.

As you can see, using batch reporting is a real time-saver and easily customised to suit your own needs.

Note: This instruction applies to MYOB Accounting and MYOB Accounting Plus Version 10 or later.

How do I set up batch reports in MYOB?

Do you find you are printing the same reports each time? You can save time by printing all of your nominated reports at the same and with just one click of the print button. How do you do that? It is called batch reporting and setting it up is simple.

Step 1: From the Reports drop-down menu at the top of the screen, select Report Batches.

Step 2: Click on the New button. In the Batch Name field, enter a name for the batch. In the example I have called the new batch "Usual Monthly Reports."

Step 3: Now you just need to put a click next to each of the reports you want to include and select OK. As you can see below, I have chosen a Trial Balance, Balance Sheet and Profit & Loss.



HINT: You can add or remove reports from batches at any time by selecting the batch from the list and clicking on the Edit button.

How do I delete unused tax codes in MYOB?

When you first install MYOB, the program is already set up with several tax codes. Some of these may not be necessary for your business. You can delete tax codes you do not need and "tidy up" the tax list if this makes data entry a little easier for you. Here is how to do it in three easy steps:


Step 1: Select the Lists pull own menu from the top of the screen and scroll down to choose Tax Codes.

Step 2: Click the detail arrow next to the code you want to delete. In this example I will delete the Luxury Car Tax code.
Step 3: Now simply select the Edit pull-down menu and click on Delete Tax Code as shown on the left.

Click OK to exit the Tax Code Information window and you're done.


HINT: A consolidated tax code is used to group together more than one code. A consolidated code will be identified in the Type column of the Tax Code List. You will find that a consolidated tax code must be deleted before the individual codes can be deleted.