Office Day @ Angkasapuri, Kuala Lumpur

I was given the opportunity to do a presentation on New Features of Microsoft Office 2007 for more than 300 audients from different government agencies under Ministry of Information, Communication and Culture. Started with Microsoft Office PowerPoint 2007, follow by Microsoft Office Outlook 2007 and Microsoft Office Visio 2007 and finally end with Microsoft Office Excel 2007.

- Siti Suriani

Word Tips – Easiest Option to format a report using MS Word 2007

Normally user will spend more time to do formatting rather than focusing on the content. Here is how you can do it even faster….

Below is the sample report before formatting:-

Make sure the entire report is finalized already.

1. Click anywhere within the report.
2. From the Home tab, under Styles group, click on the Change Styles drop down arrow button – change the set of styles, colors and fonts (apply for the entire document).
3. Point to Style Set and move over to see the live preview of each style from the drop down list.
   
   

Style Set Samples:-

1. Distinctive
   
   


2. Elegant
   
   
   
3. Fancy
   
   
   
4. Formal
   
   
   
5. Manuscript
   
   
   
6. Modern
   
   
   
7. Simple
   
   
   
8. Traditional
   

- Siti Suriani

Excel Tips - Getting List from Other Spreadsheet for Display with Data Validation List

You can create your own pull-down list or combo box by using the data validation feature in Excel but the main problem is the list has to be in the same spreadsheet as the combo box that you want to use. What most of us will do is just create a list somewhere else in the same spreadsheet and then hide it either by hiding the columns or rows of the list or change the font colour to white so it is invisible.

You can actually make use of the range name for the list that is in other sheet tabs and there is no need to copy it to the spreadsheet that you want to put the combo box.

Here's how...

1. Select the list that you want to use (it could also be from the table array you use for VLookup or HLookup. If it is, just select the list excluding the header which is the column one for VLookup table array or row one for HLookup table array). It is best if you can first sort your list in ascending order because the list taken in data validation will follow your actual list without sorting them for you.
   
2. With the list selected, click in the name box located to the left of the formula bar and type a name (the name has to be unique and shouldn't be separated if it consists of more than one word) and then press Enter.




3. Return to the other spreadsheet and select the cell(s) you want to create combo box.
4. With the cell(s) selected, go to Data tab and select Data Validation from Data Tools group (for Excel 2003 or earlier, go to Data menu and select Validation...).
   



5. On the Data Validation dialog box, go to Settings tab.
6. Select List from Allow combo box.
   
   
   
7. Type the name you have given for the list in step 2, starting with the equal sign (=) in the Source field (I used FruitList for my example).




8. Click OK or you can continue setting the Input Message and/or Error Alert tab if you want to.
   
   

You can combine the usage of this combo box with any lookup function or use it alone.

- MRBAR

Excel Tips - Hide Formula from Being Displayed in the Formula Bar

You may want to hide the formula that you are using in your Excel from being seen by other viewers who are using your spreadsheet.

To hide the formula, you will have to protect your worksheet but before protecting, you'll have to set the cell's format.

1. Select the cell(s) with the formula(s) you want to hide.
2. From Home tab, click Format from Cells group and select Format Cells (for earlier Excel versions, go to Format menu and select Cells).
   
   
   
   
3. From the Format Cells dialog box, go to the Protection tab.
4. Select the Hidden check box (leave the Locked check box selected to protect from user changing or deleting the cell content).
   
   
   
   
5. Click OK.

(If you want to allow entering or editing data on certain cells, select those cells and follow step 2 and 3. Then uncheck the Locked check box and click OK.)

Now you can protect your worksheet.

1. From Home tab, click Format from Cells group and select Protect Sheet... or you can also find Protect Sheet button in the Changes group from Review tab (for earlier versions, go to Tools menu, point at Protection and select Protect Sheet...).
2. Apply your password to protect (optional) and repeat the same password after clicking OK.

REMINDER! Password is case sensitive. To unprotect, you must type the password exactly like the one you've created. If you forgot the password then you can say good bye to your worksheet :).

- MRBAR

Excel Tips – Protect Certain Part of Spreadsheet from Changes

Sometimes we want to block users from making any changes to our important data area but elsewhere within the same worksheet they can make any changes they wish for. By default when we set the protect worksheet, the entire worksheet is being protected. How do we actually make the setting, so that only selected area within the same worksheet cannot be modified?

1. Click on the top left of the worksheet or press Ctrl+A to Select All
   
   
   
   
2. Press Ctrl+1 to open up the Format Cells dialog box
3. Click on the Protection tab
4. Clear the Locked check box
5. Click OK
   
   
   
   
6. Highlight the area in the data area.
   
   
   
   
7. Open the Format Cells dialog box again
8. This time around, check the Locked check box
9. Click OK
   
   
   
   
10. Next step is to protect the worksheet
11. Click on the Review tab, Changes group, Protect Sheet command button (for MS Excel 2003 – Click on the Tools menu, Protection, Protect Sheet)
    
    
    
    
12. Type a password to unprotect sheet
13. Reenter the password
    
    
    
    
14. Try to make changes on the selected data area
15. The message box below will come out
    
    
    

Now all is being set, users are not allowed to make any changes on the selected data area.

- Siti Suriani

Word Tips - Create Table of Content

Microsoft Word 2007 and earlier version has the feature of creating table of content from the title of each chapter or sub-chapter.

Before able to use the TOC feature, you must first format your titles to heading style:

1. From your document, click anywhere in the chapter's title line.
2. Select Heading 1 from Styles group in the Home Tab (for previous Word version, goto Format menu and then select Styles and Formatting. You'll see Styles and Formatting appears normally on the right side of the window).
   
   

Repeat the steps above for all titles that you've created. You can actually perform these steps as you are creating your document. This way will save time rather that searching the titles from beginning to the end of your completed document.

Select Heading 2 style for sub-topic's title (level 2). You 'll see Heading 3 style (for sub-sub-topic) will automatically appear after Heading 2 and more heading styles will appear when you choose the new heading style.

Once you've done applying the style, you can add a new page as your first page. This is important.... Use insert section brake and don't use page break so you can customise the page numbering for TOC and the contents separately.

1. Select the new first page.
2. Go to References tab and select Table of Contents from the Table of Contents group (for earlier version, go to Insert menu, hover on Reference and select Index and Table... and then select Table of Contents tab).
   
   
   
   
3. Select one of the built-in TOC style or select Insert Table of Contents... if you want to display more that 3 levels of TOC by increasing the value in the Show levels (same for earlier version where you can see the Show level in the Table of Contents tab of the Index and Tables dialog box).

If you do any changes on the document's title(s) or move the chapter and its title, you can just update the TOC and it will change the writing and page number automatically.

Easiest way is to press Ctrl+A to select all and then press the F9 function key. You'll then have the option to update entire content or just the page number.

- MRBAR

Excel Tips – Copying or Cutting Formula

When you copy and paste formulas containing relative references, the references are adjusted.

Microsoft Office Excel 2003

1. Select cell with formula
2. Click Copy on the Edit menu
3. Select cell to paste
4. Click Paste on the Edit menu
   

Microsoft Office Excel 2007

1. Click Home tab
2. Click Copy command button under Clipboard group
3. Select cell to paste
4. Click Paste command button under Clipboard group
   
   
   

When you cut and paste formulas containing relative references, the references are not adjusted.

Microsoft Office Excel 2003

1. Select cell with formula
2. Click Cut on the Edit menu
3. Select cell to paste
4. Click Paste on the Edit menu
   

Microsoft Office Excel 2007

1. Click Home tab
2. Click Cut command button under Clipboard group
3. Select cell to paste
4. Click Paste command button under Clipboard group
   
   
   
   

- Siti Suriani

Excel Tips - If Colored areas on your worksheet do not print well

Microsoft Office Excel 2003

1. Click Page Setup on the File menu
2. Click the Sheet tab
3. Select the Black and White check box under Print category
   

Microsoft Office Excel 2007

1. Click Page Layout tab
2. Click Page Setup dialog box launcher button
3. Click the Sheet tab
4. Select the Black and White box under Print category
   
   

- Siti Suriani

Word Tips - Mixing Potrait and Landscape Orientation in a Document

Sometimes we want to have table or diagram such as organisation chart to be layed out in landscape orientation in our Microsoft Word document. What most people will do is they either have these diagrams in a separate document that has been changed to landscape or they will use Microsoft Excel or PowerPoint and then print them separately. Some may take a screenshot of their diagram and then paste it in the Word document as picture. The picture will then be rotated to fit the potrait oriented page.

Don't use Page Break when you want to create a new page for the diagram. Things will be alot easier if you use Section Break. By using Section Break, you will be able to change the orientation for the new page without effecting previous pages that is in potrait orientation.

Follow the steps below:

1. To create the new page for the diagram, go to Page Layout tab, Page Setup group (for earlier Word versions, go to Insert menu and select Breaks).
2. Click Breaks button and select Next Page under Section Breaks.
   
   
   
   You will now have a new page.


3. Click in the new page.
4. Click the Orientation button in the Page Setup group and select Landscape (for earlier versions, go to File menu and select Page Setup. You'll then find Orientation under Margins tab).
   
   

Now your new page will have the landscape orientation while all pages before it is still in portrait. To have the following page to be in portrait orientation again, follow step 1 through 4 but choose Potrait in step 4.

- MRBAR

Word Tips - Getting the whole content of a paragraph in a same page

You may have faced problem where your paragraph is being split into different pages. Mostly people will just press Enter until the whole paragraph is pushed to the next page or some may use Ctrl+Enter (page break) but if there is any changes done later on any part above the paragraph (maybe on the previous page), the paragraph that was push to the next page may have to be adjusted again.

Instead of wasting time adjusting the paragraph again and again, you can set the paragraph to be in the following page automatically if it cannot fit the current page.

Here are the steps:

1. Click anywhere inside the paragraph.
2. From the Home Ribbon, click the dialog box launcher for Paragraph group (for earlier Word versions, go to Format menu and select Paragraph).
3. On the Paragraph dialog box, go to the Line and Page Breaks tab.
4. Click the check box for Keep lines together.
5. Click OK.
   

The whole paragraph will be on the following page only if it cannot fit in the current page

- MRBAR

Malaysia Microsoft Office Championship 2009

For the first time, Malaysia is sending participant for the World Microsoft Office Championship. This year, one canditate has been chosen out of 33 shortlisted university students. This candidate will compete at Toronto this year in Microsoft Word 2007 category.

See more details: http://www.officecompetition.com

I'm honoured to be chosen to conduct the 2 days training on Microsoft Office Word 2007 for these 33 candidates from 8th. to 9th. July and they sat for MCAS test on the 10th. Highest scorer was chosen to represent Malaysia this year.

Malaysia decided to send only one participant for only one category this year due to lack of time to prepare for other categories. The new selection will begin early for next year so that Malaysia will be able to send participants for all categories for 2010 championship.

- MRBAR

Word Tips - Take advantage on AutoCorrect Options

If you noticed, most typing errors will be corrected automatically by your Microsoft Word application as you continue typing your document. For an instance, when you type "Teh", Microsoft Word will change it to "The". This is done by the AutoCorrect Option.

We can use the feature to help us to type long text such as our name, just by typing the initial.

This is how to do it...

1. Go to Office Button and then click on Word Option. From Word Option dialog box, go to the Proofing page.
2. Click the AutoCorrect Options button. Then you'll see the AutoCorrect Options dialog box (for earlier Word versions, go to Tools menu and select AutoCorrect Options).
3. Under the Replace field, type your initial.
4. Next, type your full name under the With field.
   
   
   
   
5. Once done, click Add and then click OK button to close AutoCorrect Options dialog box and then click OK again to close the Word Option dialog box.

Now you can just type your initial and then when you press the space bar or enter, the initial you've just typed will automatically be replaced with your full name.

- MRBAR

About Siti Suriani

Microsoft Certified Application Specialist in Word 2007, Excel 2007 and PowerPoint 2007.

Certified Shell IT Trainer & part of Shell Handyman team under Shell Global Solution for Shell International as Consultant on Microsoft office in Shell environment.

Holds Master's Degree in Computer Science (Distributed Computing) and Bachelor's Degree in Computing.

About Ridzuan (MRBAR)

Microsoft Certified Application Specialist in Word 2007, Excel 2007, PowerPoint 2007, Outlook 2007 and Access 2007.

Awarded Microsoft Office 2007 Master for having certificates on Word 2007, Excel 2007, PowerPoint 2007 and Outlook 2007.

Certified Shell IT Trainer & part of Shell Handyman team under Shell Global Solution for Shell International as Consultant on Microsoft office in Shell environment.

Experienced in IT since 1986 and started to be an in-house trainer in IT since 1997 for multi-national and foreign company. Moved on deeper into IT Corporate training as a freelance trainer since 2007 and at the same time, always looks for opportunity to gain more knowledge in IT.

An Information Technology Diploma holder and in pursue on Bachelor of Technology in Computer Systems and Networks (hons).

Experienced in many sectors of employment, from labour up to management level through working experiences with various company (local, multi-national and foreign).

Experienced in photography since 1998 and doing photography workshops and photo shooting as second job

Website : www.MRBARPhotoWorks.webs.com
E-mail : mrbar.my@live.com

THE BLOGGERS