Infographic: What's in and out of Office 2013

Takeaway: Best STL describes some of the new features of Office 2013 and some of features that have been removed.

By Best STL

Best STL has produced an infographic describing which features have been added to Microsoft Office 2013 and which features have been removed. Office 365 and Office 2013 differ from previous versions of the productivity suite in many ways. We highly recommend you take some time to acquaint yourself with the latest version of Office.

Credit: Best STL

Read More

Use Word macros to save your place in a document

By Susan Harkins

Takeaway: Use these two simple Word macros to temporarily bookmark a spot you want to return to later.

Large documents offer a few navigating challenges, especially when you need to jump back and forth between two areas. You could split the document, but that splits the screen and that might not be the right solution for you. Or, you could use any of the normal navigation tools and shortcuts, but it’s easy to get lost that way. Using VBA, you can insert a bookmark that acts as a placeholder. Then, when you’re ready to return, a single quick click is all that’s required.

This technique requires two quick macros, which follow:

Public Sub InsertBookmark()

  'Insert bookmark for ssh.

  Call Bookmarks.Add("ssh", Selection.Range)

End Sub

Public Sub ReturnToBookmark()

  'Return to previously inserted bookmark.

  ActiveDocument.Bookmarks("ssh").Range.Select

End Sub

I used my initials to identify the bookmark, but you can use any string you like.
To add the macros, press [Alt]+[F11] to launch the Visual Basic Editor (VBE). In the Project Explorer, find ThisDocument for the current document. Then, enter the two sub procedures shown above.
Next, return to the document and add the macros to the Quick Access Toolbar (QAT), as follows:

1. From the QAT dropdown, choose More Commands.
2. From the Choose Commands From dropdown, choose Macros.
3. In the list on the left, find InsertBookmark.
4. Click Add to add the macro to the list of commands on the right.
5. With the macro still selected, click Modify.
6. In the resulting dialog, enter Mark in the Display Name control, and click OK. You could also change the macro’s display icon.
7. Repeat steps 3 through 6 to add ReturnToBookmark to the list on the right and add the display name, Return.
8. Click OK.

In Word 2003, drag the macros to the toolbar as follows:

1. Launch the Customize dialog box by double-clicking a toolbar or menu or by choosing Customize from the Tools menu.
2. Click the Commands tab.
3. Choose Macros from the Categories list.
4. Find InsertBookmark in the Commands list and drag it to the toolbar.
5. Find ReturnToBookmark and drag it to the toolbar.
6. Click Close.

Using the macros is simple. When you want to mark an area, just click the Mark macro on the QAT. Then, go wherever you want. When you’re ready to return to the marked spot, click Return.

Read More

Implement a search offset in an Access form

By Susan Harkins

Takeaway: Adding search capability to an Access form is pretty easy, but users might appreciate a bit more flexibility by letting them also set an offset value.

When searching a continuous form, Access highlights the matching record without moving any records, if possible. When Access must change the visible records in order to select a matching record, Access will position the matching record at the top of the form. Some users would rather see the matching record along with a few of the previous records instead. To accommodate these users, you can add a simple offset value to the search, and doing so is easier than you might think.

To illustrate this flexible searching solution, we’ll add a couple of search controls to the simple continuous form (a phone list) shown below. This form displays data from the Customers table in Northwind.accdb, the sample database that comes with Access.



To add the search controls, open the form in Design view and expose the header section. Then, insert two text controls; the positioning isn’t particularly important. Name the search string control, txtSearch; name the offset control txtSearchOffset. Then, set txtSearchOffset’s Default Value property to -3. Check the form’s Has Module property - it must be set to Yes.

Now you’re ready to add the code that runs the search. Click the View Code option in the Tools group (in Access 2003, the button’s on the toolbar) to launch the form’s module in the Visual Basic Editor and add the following code:

Private Sub txtSearch_AfterUpdate()

  'Find record based on contents of txtSearch

  'using an offset value.

  Dim strSearch As String

  Dim intOffset As Integer

  Dim varBookmark As Variant

  On Error GoTo errHandler

  'Delimited for text values.

  strSearch = "Company = " & Chr(39) & Me!txtSearch.Value & Chr(39)

  'Set offset value.

  intOffset = Me.txtSearchOffset

  'Find the record.

  Me.RecordsetClone.FindFirst strSearch

  Me.Bookmark = Me.RecordsetClone.Bookmark

  On Error Resume Next

  Me.Recordset.Move intOffset

  Me.Bookmark = Me.RecordsetClone.Bookmark

  Exit Sub

errHandler:

  MsgBox "Error No: " & Err.Number & "; Description: " & _

   Err.Description

End Sub

Return to Access and view the form in Form View. The offset control defaults to -3, which your users can change to suit themselves, but let’s work with the default value for now. Access won’t change the current record set if you search for a company that’s visible in the form. It’ll just change the selected record.




Now, search for a company that’s not visible on screen, such as Company AA. After defining the search string and offset variables, the FindFirst method finds the first matching record. The next statement sets the Bookmark property to the current record. Then, the Move method changes the selection using the offset variable (-3, which means three records before the currently selected record). Finally, the last line uses the previously defined bookmark setting to reposition the selection.




If a user searches for the first record in the form’s recordset, Access selects the first record. If there are more previous records showing than necessary to satisfy the offset value, the offset value has no effect. If a user changes the offset to 0 or a positive value, Access will select the first matching record, with no offset. Access ignores an offset value that’s greater than the number of records.

If a user enters a text value, Access will display an error message. Right now, the error handling routine is basic. Test the procedure thoroughly and enhance this routine to reflect your application’s use.

Read More

Microsoft Office 2011 14.2.2 (Mac)

Microsoft Office for Mac 2011 gives you a familiar work environment that is more intuitive than ever. The suite provides new and improved tools that make it easy to create professional content, along with improvements in the speed and agility of Office 2011 you will notice from the moment you open the applications. From managing home projects and planning important gatherings, to helping your kids polish their homework, Office helps your family make the most of every opportunity, every day. Create great-looking documents, spreadsheets, and presentations. Communicate and share with family, friends, and colleagues, whether they're on Macs or PCs. And access your files whenever you need them, using any computer with a web browser and the free Office Web Apps.Improved compatibilityShare files with confidence knowing that the documents you create using Office 2011 on your Mac will look the same and work seamlessly when opened in Office for Windows.Co-authoring allows you to save time and simplify your work by allowing you to edit the same Word document or PowerPoint presentation at the same time as others in different locations who are using Office 2011 on a Mac or Office 2010 on Windows. (Co-authoring requires Microsoft SharePoint Foundation 2010 for enterprise use, or a free Windows Live ID for personal use, to save and access files via Windows Live SkyDrive.)Office Web Apps allow you to get things done when and where you want, from virtually any computer with an Internet connection.Sparklines visually summarizes your data using tiny charts that fit within a cell near its corresponding values. Microsoft Excel for Mac 2011 Sparklines is compatible with Microsoft Excel 2010.Create professional contentPublishing Layout view combines a desktop publishing environment with familiar Word features, giving you a customized workspace designed to simplify complex layouts.Visual styles provide you with consistent formatting that is easy to apply.Picture Editing gives you tools throughout the suite to crop, recolor, remove background and compress photos within your document.Charts and SmartArt gives you dozens of SmartArt layouts from more than 130 diagram layouts, ranging from organization charts to lists, processes and relationship diagrams.Dynamic Reorder helps you simplify complex layouts. Get an instant, 3-D view of all layers on your Publishing Layout view page and PowerPoint presentation.Familiar, intuitive toolsThe new ribbon creates an intuitive experience for the Mac user. Familiar Office for Mac tools are still available so you can take advantage of the new ribbon without reinventing the wheel.Template Galleries give you easy, organized access to a wide range of online and custom templates and recently opened documents.Media Browser allows you to access your iPhoto libraries and iTunes playlists directly from the Photos and Audio tabs on the Media Browser in Word, PowerPoint, Excel and Outlook. You can also access movies and iMovie projects right from the Movies tab.Rich presence and on-the-spot communication lets you instantly connect with your contacts without leaving your work. Presence and communication are available while editing documents with others.


Link Download 

Read More

10 annoying Word features (and how to turn them off)

By Jody Gilbert

Takeaway: Word can be a little unruly sometimes, making inexplicable changes, inserting text you didn’t ask for, and hijacking your formatting. Here are some common stunts that Word tries to pull on unwary users, along with a cure for each one.

One of the most common complaints about Microsoft Word is its insistence on taking control of the wheel. Many users get completely blindsided by some of Word’s automatic changes, and even the more experienced among them often just live with Word’s shenanigans because because they don’t know how to disable them.

If you’ve gotten more than your share of support calls from users trying to wrestle Word into submission (or pulled out your own hair on a few occasions), the list below will help you quickly cut Word down to size.
A few things to keep in mind: First, many of the options you need are located in the AutoFormat As You Type tab. A similar set of options exists in the AutoFormat tab — but disabling those won’t do you any good with Word’s on-the-fly changes. Users sometimes don’t make that distinction and can’t understand why the changes are still happening after they thought they’d turned off the necessary settings.

Second, some of these options may actually sound appealing to your users but might currently be disabled. You can use this list to help them selectively activate the features they want, not just to turn things off. It’s not always the features themselves that are annoying — it’s just not knowing how to control them.
And finally, Word 2007 offers the same feature set described here, but accessing the options is a little different. The section at the bottom explains how to find them in that version.
This information is also available as a PDF download.

---

Cheap solution: Undo
If you haven’t had a chance to disable an automatic feature (or you want to leave it enabled and override it only occasionally), remember that pressing Ctrl+Z or clicking the Undo button right after Word makes a change will undo that action. So, for instance, if Word inserts a smart apostrophe where you want to retain the straight character to denote measurement, just hit Undo to straighten it back out.

---

The annoyances

Behavior	How to turn it off
#1: Word creates a hyperlink when you type a Web page address.	Go to Tools | AutoCorrect Options and select the AutoFormat As You Type tab. Under Replace As You Type, deselect the Internet And Network Paths With Hyperlinks check box and click OK.
#2: Word changes capitalization of text as you type it.	A host of settings can trigger this behavior. Go to Tools | AutoCorrect Options and select the AutoCorrect tab. Here, you can deselect whichever check boxes govern the unwanted actions: Correct Two Initial Capitals Capitalize First Letter Of Sentences Capitalize First Letter Of Table Cells Capitalize Names Of Days Correct Accidental Use Of Caps Lock Key
#3: Word inserts symbols unexpectedly, such as trademark or copyright characters or even inserts an entire passage of text.	Go to Tools | AutoCorrect Options and select the AutoCorrect tab. This time, find the Replace Text As You Type check box. Either deselect it to suppress all replacements or select and delete individual items in the list below it.It might make sense to keep the feature enabled and selectively remove items, since the list includes scores of common misspellings that are actually nice to have corrected for you.
#4: Word superscripts your ordinal numbers, such as 1st and 2nd.	Go to Tools | AutoCorrect Options and click the AutoFormat As You Type tab. Deselect the Ordinals (1st) With Superscript check box and click OK.
#5: Word converts fractions into formatted versions.	Go to Tools | AutoCorrect Options and click the AutoFormat As You Type tab. Deselect the Fractions (1/2) With Fraction Character option.
#6: Word turns straight apostrophes and quote marks into curly characters.	Go to Tools | AutoCorrect Options and click the AutoFormat As You Type tab. Deselect the Straight Quotes With Smart Quotes check box and click OK.
#7: When you try to select a few characters within a word, the highlight jumps to select the entire word.	Go to Tools | Options and click the Edit tab. In the right column under Editing Options, deselect the When Selecting, Automatically Select Entire Word check box and click OK.
#8: When you type three or more hyphens and press Enter, Word inserts a border line.	Go to Tools | AutoFormat and select the AutoFormat As You Type tab. Deselect the Border Lines check box and click OK.A similar option exists for inserting a table, but it’s generally not going to sneak up on you: When the Tables check box is selected, typing a series of hyphens and plus marks before pressing Enter will insert a table (with the hyphens representing cells). You can turn off that option if you think you might stumble into an unwanted table insertion.
#9: Word automatically adds numbers or bullets at the beginning of lines as you type them.	There are two flavors of this potential annoyance. First, if you start to type something Word thinks is a bulleted list (using asterisks, say) or type 1, a period, and some text, it may convert what you type to bulleted or numbered list format when you press Enter.To prevent this, go to Tools | AutoCorrect Options and select the AutoFormat As You Type tab. Then, deselect the Automatic Bulleted List and/or Automatic Numbered list check boxes and click OK.A related aspect of this behavior is that once you’re entering automatic list items, pressing Enter will perpetuate it — Word will keep inserting bullets or numbers on each new line. To free yourself from this formatting frenzy, just press Enter a second time, and Word will knock it off.
#10: When you type hyphens, Word inserts an em dash or an en dash.	If you type a word, two hyphens, and another word (no spaces), Word will convert the hyphens to an em dash. If you type a space before and after the hyphens, it will convert them to an en dash.To disable this feature, Go to Tools | AutoCorrect Options and select the AutoFormat As You Type tab. Deselect the Hyphens (–) With Dash (-) check box and click OK.

Bonus fixes

Word may cause your users some additional grief in various other ways besides automatic behaviors. It goes a little something like this:
User: My document if full of weird code stuff and my pictures are gone.
Culprit: Field code display has been toggled on.
Solution: Suggest that the user press Alt+F9 to restore the display of field code results.
User: I’m seeing gray brackets around a bunch of my text.
Culprit: Bookmark display has been enabled.
Solution: Go to Tools | Options and select the View tab. Then, under the Show options, deselect the Bookmarks check box and click OK.
User: I’m typing and everything in front of the cursor is disappearing.
Culprit: The evil Overtype mode has been activated.
Solution: Go to Tools | Options and select the Edit tab. Then, under Editing Options, deselect the Overtype Mode check box and click OK. (It might be quicker to double-click OVR on the status bar, if you can point the user to it.)
User: Everything’s gone, all my toolbars and menus and everything — there’s nothing here but text.
Culprit: The user has landed in Full Screen view.
Solution: Direct the user’s attention to the Close Full Screen View button at the bottom of the window (depending on the version) or tell them to press Alt+V to display the View menu. They can then select Full Screen to turn off that view mode and return to familiar territory.

---

Accessing the options in Word 2007

All the settings we’ve discussed here are accessible via the Office button in Word 2007:

• To get to the AutoCorrect dialog box, click the Office button, select Word Options at the bottom of the menu, and choose Proofing from the pane on the left. In the pane on the right, click the AutoCorrect Options button, and Word will display the AutoCorrect dialog box containing the AutoCorrect and AutoFormat As You Type tabs.

• To get to editing options, click the Office button, select Word Options at the bottom of the menu, and choose Advanced from the pane on the left. Word will display Editing Options at the top of the pane on the right. In that section, you’ll find the When Selecting, Automatically Select Entire Word check box and the Use Overtype Mode option. If you scroll down to the Show Document Content section, you’ll find the Show Bookmarks check box.

• The successor to Full Screen view in Word 2007 is Full Screen Reading view. Users shouldn’t get stuck there, but if they do, the Close button in the upper-right corner of the window will take them back to Print Layout view.

Read More

A few ways to customize Excel to increase your productivity

By Susan Harkins

Takeaway: Working with Excel’s out-of-the-box defaults can waste your time. Change default settings to work more productively and efficiently.

Customizing a new install of Excel can help you work more efficiently from the get-go. How you customize Excel depends on you, but here are a few tweaks that most users will benefit from.

Number of sheets

By default, Excel launches a new workbook with three sheets. There’s nothing wrong with that number, but if you routinely work with more or less, change this default, as follows:

• 2010: Click the File tab and choose Options. Select General on the left. In the When Creating New Workbooks section, choose the appropriate number from the Include This Many Sheets control. Click OK.
• 2007: Click the Office button and then choose Excel Options. Select Popular in the left pane. In the When Creating New Workbooks section, choose the appropriate number from the Include This Many Sheets control. Click OK.
• 2003: Choose Options from the Tools menu. Click the General tab and set the number of sheets in the Sheets In New Workbook control. Click OK.

Default file location

Many users don’t use the default file folder, My Documents, and they spend a lot of time browsing folders looking for files. Using the instructions above, you can control where Excel saves your workbooks. You can still change that location on the fly, but having Excel default to your most commonly used folder will save a lot of time.

User name

Many IS departments install Excel without customizing it at all. One of the first things you’ll want to check is the user name and reset it if necessary (use instructions above to access this property). If everyone’s using the same generic user name there’s no way to claim ownership. Changing this property in Excel will change it in all of your Office apps.

Display the Developer tab

If you’re using Excel 2007 or 2010, you’ll probably want to display the Developer tab, which Excel hides by default. You don’t have to be a developer to want quick access to recording macros or inserting form controls. To enable this tab, do the following:

• 2010: From the Quick Access Toolbar dropdown, choose More Commands. In the left pane, choose Customize Ribbon. In the Customize The Ribbon list (to the right), check Developer. Click OK.
• 2007: Click the Office button and then click Excel Options. Choose Popular in the left pane. In the Top Options For Working With Excel section, check the Show Developer Tab In The Ribbon option.

File format

If you’re sharing 2007 or 2010 files with pre-ribbon versions, you might want to save your workbooks in the pre-ribbon format. There are other options, but if you’re not making use of features specific to the new ribbon features, doing so might save your co-workers a bit of aggravation. To save files in another format, do the following:

• 2010: Click the File tab and choose Options. Select Save in the left pane. In the Save Workbooks section, choose Excel 97 - 2003 Workbook (.xls). Click OK.
• 2007: Click the Office button and then click Excel Options. Select Save in the left pane. In the Save Workbooks section, choose Excel 97 - 2003 Workbook (.xls). Click OK.

AutoCorrect options

These default settings try to help users by correcting entries that Excel finds inappropriate. Unfortunately, these settings probably annoy more users than any others. There are too many to review individually, but knowing you can disable them is a start. To change an AutoCorrect setting, do the following:

• 2010 and 2007: Click the File tab and choose Options. Then, select Proofing in the left pane. Click the AutoCorrect Options button in the AutoCorrect Options section. Click tabs to review the different options.
• 2003: Choose AutoCorrect Options from the Tools menu.

What Excel settings have you customized and why?

Read More

Create an Excel data entry form that includes check boxes

By Mary Ann Richardson

Takeaway: You can keep a lot of information on an Excel data entry form, but you can make it easier for your users — and yourself — to create tables by adding check boxes. Here’s how to set up check boxes that you can later translate into a report you want.

Excel provides a simple data form to enter data into an Excel database. The form only only contains text box controls, which can be cumbersome when entering certain types of data.
For example, you want to create a table that lists the educational level of your latest batch of job applicants. Rather than typing high school, college, or graduate school, you can create a form that allows you simply to check a box to indicate the education level. Follow these steps:
1. Open a blank worksheet of your workbook.
2. Press [Alt][F11].
3. Go to Insert | User Form.
4. If necessary, press [F4] to display Userform1 properties.
5. Click in the Caption property box and change the name to Get Name and Education.
6. Click the Label control and drag it to the form.
7. Click in the Label’s Caption property box and type Name:.
8. Click the Text Box control in the Control toolbox and drag it to the form. Locate it to the right of the Label control.
9. Click in the Name property of the Text Box control and type TextName.
10. Click and drag the Frame control from the Control toolbox to the form and locate it below the text box.
11. Click in the Caption property of the Frame control and type Education Level:.
12. Click and drag the Check box control from the Control toolbox to the form and locate it within the frame.
13. Change the Check box Name property to OptionHS.
14. Change the Caption property of the Check box control to High School.
15. Click and drag the Check box control to the form and position it below the first check box.
16. Change the Check box Name property to OptionCollege.
17. Change the Caption property of the Check box control to College.
18. Click and drag the Check box control from the Control toolbox to the form and locate it below the second check box.
19. Change the Check box Name property to OptionGrad.
20. Change the Caption property to Graduate School.
21. Click and drag the right border of the form to increase the form’s size.
22. Click the Command Button control in the Control toolbox, drag it to the form, and locate it next to the TextName control.
23. Change the Caption property of the Command button to OK.
24. Change the Name property to OKButton.
25. Click in the Default Property box and select True.
26. Click and drag the Command Button control to the form from the Control toolbox and locate it below the OK button.
27. Change the Caption property to Cancel.
28. Change the Name property to CancelButton.
29. Click in the Cancel property box and select True.
30. Press [Alt][F11].
31. Right-click a toolbar and select Control Toolbox.
32. Click the Command tool in the Control Toolbox.
33. Click and drag to create the command button in your worksheet.
34. Right-click the Command button, point to Object, and then select Edit.
35. Select the default name and change it to Data Entry.
36. Click outside the button to deselect it and then double-click the button.
37. Enter Userform1.Show at the prompt.
38. Double-click Userform1 in the VBA Project Window. (To open the form so you can add code to the rest of the form objects. Double-click the name of the form — Userform1 — listed under the Forms folder in the VBA Project window.)
39. Double-click the Cancel button.
40. Enter Unload UserForm1 at the prompt.
41. Press [Shift][F7].
42. Double-click the OK button.
43. Enter the following code at the prompt:
Sheets("Sheet1").Activate
NextRow =Application WorksheetFunction.CountA(Range("A:A"))+1
Application.WorksheetFunction.CountA(Range("A:A")) +1
Cells(NextRow, 1)=TextName.Text
If OptionHS Then Cells(NextRow, 2) ="High School"
If OptionCollege Then Cells(NextRow, 2) ="College"
If OptionGrad Then Cells(NextRow, 2)="Grad School"
TextName.Text =""
TextName.SetFocus
44. Press [Alt]Q.
45. Click the Exit Design Mode button in the Control toolbox.
To enter the data into the blank worksheet, press the Data Entry button, fill in the name, click the Education Level, and then click OK. The data will automatically be entered in columns A and B. When you finish entering data, click Cancel to close the form.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Read More

How to create an electronic form using Word 2010 content controls

By Susan Harkins

Takeaway: Use Word 2010’s content controls to create easy-to-use electronic forms. They’re similar to Word 2003’s form fields, but better.

Word forms allow you to gather information in an easy way. They’re easy to create, easy to use, and have almost unlimited possibilities. I’ll show you how to create these useful forms by creating a simple evaluation form - the type you might fill out after attending a workshop or seminar.
Before you can create the form, you have to find the controls. To create a form in Word 2003, you must display the Form toolbar. You’ll use what we call form fields. (These aren’t the same as coded fields that you insert by choosing Field from the Insert menu; these are controls.) In Word 2007 and 2010, you’ll find form fields on the Developer toolbar in the Controls group. Click the Legacy option’s dropdown to see form fields ( Word 2003) and ActiveX controls.
If Word 2010’s Developer tab isn’t visible, do the following to display it:

1. Click the Quick Access Toolbar dropdown (you’re not adding anything to the QAT, but this route gives you quick access to the ribbon options).
2. Choose More Commands.
3. In the left pane, click Customize Ribbon.
4. In the list to the right under Main Tabs, check Developer and click OK.

In the Controls group (on the Developer tab), you’ll see a number of other controls - some seem to duplicate the Legacy option’s controls. These new controls are content controls. We’ll be working with content controls, but you can replicate some of their functionality in Word 2003 using form fields.

You can insert form controls directly into a document, but consider inserting them into a table instead. They’re a bit easier to corral that way. For instance, I’ve inserted a two-column, five-row table into the document shown below. In the left column, I’ve entered descriptive labels for the content controls. (Choose Table from the Insert menu/tab.) We’ll enter the appropriate content controls into the right column. Specifically, we’ll enter two text fields, one date field, and two lists.

First, let’s enter a text field for the class ID, as follows:

1. Select the first cell in the right column (to the right of the label, Class).
2. Click Plain Text Content Control in the Controls group (on the Developer tab). In Word 2003, click Text Form Field on the Forms toolbar.

That’s all there is to it, although you can click Properties (in the Control group) to better define the control. For now, the default options are fine. Enter a second Plain Text Content Control for the instructor’s name. Next, add a Date Picker Content Control as follows:

1. Select the third cell in the right column and click Date Picker Content Control in the Controls group. (There’s no comparable field in Word 2003. Use a text field and set its type to Date.
2. Click the Properties option in the Controls group, set the format to m/d/yy, and click OK.

At this point, you have three controls and you’ve not had to work very hard. You can add a title and flag a few settings, but there’s just not a lot that has to be done.
Now, add a control that lets users choose an item from a list. Specifically, add a list of ratings so the students can score the instructor on preparation and content. Specifically, create a list of five possible scores, from very poor to excellent, as follows:

1. Select the fourth cell in the right column and click Drop Down List Content Control in the Controls group (Drop Down Form Field in Word 2003).
2. Click Properties.
3. Click Add.
4. Enter Very poor in the Display Name control.
5. Enter 1 in the Value control.
6. Click OK.
7. Repeat steps 3 through 6 to create the list shown below. When you’re done, click OK.

Repeat the above instructions to add another Drop Down List Content Control for the last cell in the right column. Or, simply copy it—yes, you can copy a control!
Once you’ve inserted all the content controls (or form fields), protect the document as follows:

1. Press Ctrl+A to select the entire document (or select only the table if you’re working in a larger document).
2. Choose Group from the Controls menu. (You’ll skip this in Word 2003.)
3. Click Restrict Editing in the Protect group. (In Word 2003, choose Protect Document from the Tools menu).
4. Check 2. Editing restrictions.
5. From the dropdown, choose Filling In Forms.
6. Save the form, close it, and reopen it.

To use the form, simply press tab to select the first content control. (When adding the content controls, you can change the default instructions.) Enter a class ID and press tab. Enter the instructor’s name and press tab. To enter a date, click the dropdown and click a date. Word will enter the date in the format you selected when you set the control’s properties. Press Tab to move to the next control.

The next two controls are list controls. Click the dropdown for each and select one of the list items.


Word content controls and form fields are an excellent way to implement a bit of control and consistency when gathering information.

Read More

Two ways to build dynamic charts in Excel

By Susan Harkins

Takeaway: Users will appreciate a chart that updates right before their eyes. In Excel 2007 and 2010 it’s as easy as creating a table. In earlier versions, you’ll need the formula method.

If you want to advance beyond your ordinary spreadsheet skills, creating dynamic charts is a good place to begin that journey. The key is to define the chart’s source data as a dynamic range. By doing so, the chart will automatically reflect changes and additions to the source data. Fortunately, the process is easy to implement in Excel 2007 and 2010 if you’re willing to use the table feature. If not, there’s a more complex method. We’ll explore both.

Automatically sign up for our Microsoft Office newsletter!

The table method

First, we’ll use the table feature, available in Excel 2007 and 2010-you’ll be amazed at how simple it is. The first step is to create the table. To do so, simply select the data range and do the following:

1. Click the Insert tab.
2. In the Tables group, click Table.
3. Excel will display the selected range, which you can change. If the table does not have headers, be sure to uncheck the My Table Has Headers option.
4. Click OK and Excel will format the data range as a table.

Any chart you build on the table will be dynamic. To illustrate, create a quick column chart as follows:

1. Select the table.
2. Click the Insert tab.
3. In the Charts group, choose the first 2-D column chart in the Chart dropdown.

Now, update the chart by adding values for March and watch the chart update automatically.

The dynamic formula method

You won’t always want to turn your data range into a table. Furthermore, this feature isn’t available in pre-ribbon versions of Office. When either is the case, there’s a more complex formula method. It relies on dynamic ranges that update automatically, similar to the way the table does, but only with a little help from you.
Using our earlier sheet, you’ll need five dynamic ranges: one for each series and one for the labels.  Instructions for creating the dynamic range for the labels in column A follow. Then, use these instructions to create a dynamic label for columns B through E. To create the dynamic range for column A, do the following:

1. Click the Formulas tab.
2. Click the Define Names option in the Defined Names group.
3. Enter a name for the dynamic range, MonthLabels.
4. Choose the current sheet. In this case, that’s DynamicChart1. You can use the worksheet, if you like. In general, it’s best to limit ranges to the sheet, unless you intend to utilize them at the workbook level.
5. Enter the following formula: =OFFSET(DynamicChart1!$A$2,0,0,COUNTA(DynamicChart1!$A:$A))
6. Click OK.

Now, repeat the above instructions, creating a dynamic range for each series using the following range names and formulas:

• SmithSeries: =OFFSET(DynamicChart1!$B$2,0,0,COUNTA(DynamicChart1!$B:$B)-1)
• JonesSeries: =OFFSET(DynamicChart1!$C$2,0,0,COUNTA(DynamicChart1!$C:$C)-1)
• MichaelsSeries: =OFFSET(DynamicChart1!$D$2,0,0,COUNTA(DynamicChart1!$D:$D)-1)
• HancockSeries: =OFFSET(DynamicChart1!$E$2,0,0,COUNTA(DynamicChart1!$E:$E)-1)

Notice that first range reference starts with row 2. That’s because there’s a row of headings in row 1. The second set of references refers to the entire column, enabling the formula to accommodate all values in the column, not just a specific range. The addition of the -1 component eliminates the heading cell from the count. The first formula (for the labels in column A) doesn’t have this component.
It’s important to remember that you must enter new data in a contiguous manner. If you skip rows or columns, this technique won’t work as expected.
You might be wondering why I added the Series label to each range name. Using the name, alone, will confuse Excel. The series headings in row 1 are also names. Because the chart defaults will use the label headings in each column for each series name, you can’t use those labels to name the dynamic ranges. Don’t use the same labels for both your spreadsheet headings and your dynamic range names.
Next, insert a column chart, as you did before. If you enter new data, the chart won’t yet reflect it. That’s because the chart, by default, references a specific data range, DynamicChart1:A1:E3. We need to change that reference to the dynamic ranges we just created, as follows:

1. In the chart, right-click any column.
2. From the resulting submenu, choose Select Data.
3. In the list on the left, select Smith and then click Edit. (Remember the naming conflict I mentioned? Excel uses the column heading (cell B1) to name the series.)
4. In the resulting dialog, enter a reference to Smith’s dynamic range in the Series Values control. In this case, that’s =DynamicChart1!SmithSeries.
5. Click OK.

Repeat the above process to update the remaining series to reflect their dynamic ranges: DynamicChart1!JonesSeries; DynamicChart1!MichaelsSeries; and DynamicChart1!HancockSeries.
Next, update the chart’s axis labels (column A), as follows:

1. In the Select Data Source dialog, click January (in the list to the right).
2. Then, click Edit.
3. In the resulting dialog, reference the axis label’s dynamic range, DynamicChart1!MonthLabels.
4. Click OK.

You don’t have to update February; Excel does that for you. Now, start entering data for March and watch the chart automatically update! Just remember, you must enter data contiguously; you can’t skip rows or columns.

This formula method is more complex than the table method. Be careful naming the dynamic ranges and updating the series references. It’s easy to enter typos. If the chart doesn’t update, check the range references.
For a dynamic chart technique that takes a different route, read Create a dynamic Excel chart and make your own dashboard. Two example Excel worksheets demonstrating these versions of dynamic charting are available as a free download.

Read More

Convert your Word documents into PowerPoint 2007 presentations

By Mary Ann Richardson

Takeaway: If you need to make a PowerPoint presentation from a Word document, don’t worry about endless cutting and pasting. Mary Ann Richardson shows how you can use Word’s built-in formatting to make an easy transition from one document to another.

PowerPoint 2007 lets you create slides from your Word documents without having to retype or copy any text. All you need to do is to apply Word’s built-in heading styles to your text, and PowerPoint will do the rest. For example, say you have typed the following text in your Word document:

Questions to ask about your financial investment:

Is principal guaranteed?

What is the market risk?

Is interest compounded free from current income taxes?

Can you withdraw without penalty?

Will it provide guaranteed lifetime income?

ABC Financial Ratings

A.M. Best A+ (Superior)

Fitch rating: AA (Very Strong)

Moody’s Investors Service, Inc. rating: A1 (Good)

Standard & Poors’ rating: AA (Very Strong)

For each slide in this example, the line to be the slide title received the Heading 1 style, and the remaining lines of text for each slide received the Heading 2 style. (PowerPoint 2007 can only convert text formatted with a heading style, but you can use any of Word’s built-in heading styles.)
To convert this text into two PowerPoint slides, follow these steps:

1. Open PowerPoint 2007.
2. On the Home Ribbon, in the Slides group, click the arrow below New Slide.
3. Click Slides From Outline, then navigate to the Word document containing your slides.
4. Click Insert.
5. Click the Design tab.
6. Click the theme of your choice.
7. Add a title to your title slide and save the file.

Miss a Word tip?
Check out the Microsoft Word archive, and catch up on other Word tips.

Read More

How to use Excel 2010's new conditional formatting with references

By Susan Harkins

Takeaway: Excel 2010’s new referencing option makes conditional formatting more powerful than ever before!

Excel 2010’s conditional formatting feature lets you reference different sheets—something you couldn’t do before. In earlier versions you had to copy or link data to the same sheet. Now, you just include the reference to another sheet as you would any other reference!

To illustrate this new feature, we’ll use the simple products sheet shown below. (I based this example on a subset of the Products sheet in the Northwind database, but changed it considerably for this example.) There are two regions, Northwest (shown below) and Southwest. Each region has its own sheet for tracking product sales.

Now, let’s suppose you want to see where the Northwest region is outselling the Southwest. Using Excel 2010’s new referencing option, it’s easy to reference another sheet, as you’ll see:

1. Select the sales values in Northwest, that’s D2:11.
2. Click the Home tab.
3. In the Styles group, click Conditional Formatting | Highlight Cells Rules | Greater Than. The resulting dialog box will display a default format.
   
4. Click the RefEdit icon (circled in the above picture).
5. Click the Southwest tab.
6. Click cell D2 (in Southwest).
7. Alter the absolute cell reference $D$2 to $D2—that way the row number can adjust to accommodate the entire column in Northwest. In other words, every product in Northwest will evaluate the same product in Southwest.
8. Click the RefEdit icon.
9. Choose an appropriate format, such as Green Fill With Dark Green Text.
10. Click OK. According to the conditional formatting, the Northwest region is outperforming the Southwest region in four products.

This new referencing option doesn’t work with grouped sheets, which makes sense. If you want to see where the Southwest is outperforming the Northwest, just repeat the same process, but start by selecting the sales values on the Southwest tab. Then, in step 5, click the Northwest tab instead of the Southwest tab.

Both Excel 2003 and 2007 will let you go through the motions of selecting a cell or range on another sheet, but when you try to commit the reference, Excel displays an error.
This new conditional formatting option is easy to implement and one you’ll probably find many uses for. Try it out and let us know how you like it!

Read More

Transpose Excel data from rows to columns, or vice versa

By Jeff Davis

June 23, 2009, 12:00 AM PDT

Takeaway: When you need to flip-flop data in an Excel worksheet, don’t waste time doing it manually. Excel offers a handy Transpose option that will quickly take care of the task.

Here’s a tip that eliminates the need to rekey data. Suppose you’ve entered your data with three column headings running across Row 1 and four row headings running down Column A, like the ones shown in Figure A.

Figure A

After working with the data for a while, you decide you’d rather have the current set of row labels (months) running across the columns. Whatever you do, don’t even think about rekeying the data.
You’ll find the best solution on the Paste Special menu. Start by selecting and copying your entire data range. Click on a new location in your sheet, then go to Edit | Paste Special and select the Transpose check box, as shown in Figure B. Click OK, and Excel will transpose the column and row labels and data, as shown in Figure C.

Figure B

Figure C

Note

You aren’t limited to using the Paste Special | Transpose option to rearrange multiple rows and columns of data. It works just as well when you need to turn a single row of labels into a column, or vice versa.

Read More

How to add a drop-down list to an Excel cell

By Susan Harkins

June 12, 2008, 12:00 AM PDT

Takeaway: Drop-down lists can greatly facilitate data entry. Here’s a look at how to use Excel’s data validation feature to create handy lists within your worksheets.

In Access, you can limit user entries by forcing users to choose a value from a list control. Office applications use the same functionality in built-in drop-down lists. For instance, the Highlight and Font Color controls on most Formatting toolbars use this flexible tool. Simply click the small triangle to the right of the icon to display a list of choices.

You can create the same type of control for your users in an Excel sheet, but the process isn’t intuitive. The option is in the Data Validation feature. Fortunately, once you know the feature exists, it’s easy to implement. You need only two things: a list and a data entry cell. The following sheet shows a simple drop-down list in an Excel sheet.

Users click the drop-down arrow to display a list of items from A1:A4. If a user tries to enter something that isn’t in the list, Excel rejects the entry. To add this drop-down list to a sheet, do the following:

1. Create the list in cells A1:A4. Similarly, you can enter the items in a single row, such as A1:D1.
2. Select cell E3. (You can position the drop-down list in most any cell or even multiple cells.)
3. Choose Validation from the Data menu.
4. Choose List from the Allow option’s drop-down list. (See, they’re everywhere.)
5. Click the Source control and drag to highlight the cells A1:A4. Alternately, simply enter the reference (=$A$1:$A$4).
6. Make sure the In-Cell Dropdown option is checked. If you uncheck this option, Excel still forces users to enter only list values (A1:A4), but it won’t present a drop-down list.
7. Click OK.

You can add the drop-down list to multiple cells. Select the range of data input cells (step 2) instead of a single cell. It even works for noncontiguous cells. Hold down the Shift key while you click the appropriate cells.
It’s worth noting that the drop-down arrow is visible only when the cell is active.

Read More

How to find duplicates in Excel

By Susan Harkins

January 12, 2009, 12:00 AM PST

Takeaway: You’ll need more than one trick up your sleeve to find duplicates in Excel.

In the duplicate world, definition means everything. That’s because a duplicate is subjective to the context of its related data. Duplicates can occur within a single column, across multiple columns, or complete records. There’s no one feature or technique that will find duplicates in every case.
To find duplicate records, use Excel’s easy-to-use Filter feature as follows:

1. Select any cell inside the recordset.
2. From the Data menu, choose Filter and then select Advanced Filter to open the Advanced Filter dialog box.
3. Select Copy To Another Location in the Action section.
4. Enter a copy range in the Copy To control.
5. Check Unique Records Only and click OK.

Excel will copy a filtered list of unique records to the range you specified in Copy To. At this point, you can replace the original recordset with the filtered list (the copied list) if you want to delete the duplicates.

Finding duplicates in a single column or across multiple columns is a bit more difficult. Use conditional formatting to highlight duplicates in a single column as follows:

1. Using the example worksheet, select cell A2. When applying this to your own worksheet, select the first data cell in the list (column).
2. Choose Conditional Formatting from the Format menu.
3. Choose Formula Is from the first control’s drop-down list.
4. In the formula control, enter =COUNTIF(A:A,A2)>1.
5. Click the Format button and specify the appropriate format. For instance, click the Font tab and choose Red from the Color control and click OK. At this point, the Conditional Formatting dialog box should resemble the following figure:

6. Click OK to return to the worksheet.
7. With cell A2 still selected, click Format Painter.
8. Select the remaining cells in the list (cells A3:A5 in the example worksheet).

The conditional format will highlight any value in column A that’s repeated. If you want Excel to highlight only the copies, leaving the first occurrence of the value unaltered, enter the formula =COUNTIF($A$2:$A2, A2)>1 in step 4.
The conditional format works great for a single column. To find duplicates across multiple columns, use two expressions: One to concatenate the columns you’re comparing; a second to count the duplicates. For example, if you wanted to find duplicates of both first and last names in the example worksheet, you’d enter the following formula in cell D2 to concatenate the first and last name values:
=A2&B2
You could insert a space character between the two names if you liked, but it isn’t necessary. Copy the formula to accommodate the remaining list items.

Next, in cell E2 enter the following formula and copy it to accommodate the remaining list:
=IF(COUNTIF(D$2:D$7,D2)>1,”Duplicated”,”")

Notice that the worksheet has a new record (row 6). This record duplicates the first name, Susan, but not the last name. The conditional format highlights the first name because it’s a duplicate in column A. However, the formula in column E doesn’t identify the combined values across columns A and B as a duplicate because the first and last names together aren’t duplicated.

Read More

A quick and dirty way to compare columns of Excel data

By Susan Harkins

April 6, 2012, 11:41 AM PDT

Takeaway: Using this little-known feature in Excel, you can quickly compare one column of data to another.

There are a number of ways to compare values. For example, conditional formatting is an easy way to highlight differences as they occur. However, there’s a quicker way if you don’t need a dynamic and more permanent solution. When you need just a quick one-time comparison, use Go To instead.

Automatically sign up for our Microsoft Office newsletter!

Now, let’s work through a simple example, as follows:

1. Select the multi-columnar data. This technique works with two or more columns. It’s important to remember that the feature compares all selected columns to the first column in the selection (as anchored, which isn’t necessarily the left-most column). In this case, you’d select cells A27:C27 (see below).
2. Press [F5] to launch the Go To dialog.
3. Click the Special button at the bottom.
4. In the resulting dialog, select Row Differences.
5. Click OK and Excel highlights the values in columns B and C that don’t match their corresponding values in column A.

In step 1, I mentioned anchoring. If you anchor the selection by selecting from the last cell in the right-most column and then highlighting from right to left, Excel will compare values in columns B and A to the values in column C.

The selection is temporary, but if that’s all you need, this is a quick and easy way to find differences between columns.
An example Excel worksheet of this technique is also available.

Read More

Mengubah Format Penulisan Angka Windows 7

Untuk mengubah format penulisan angka pada windows xp, windows 2000, windows vista mungkin anda sudah biasa tetapi untuk mengubah format penulisan angka pada windows 7, mungkin anda seperti saya juga masih mengalami kesulitan. Hal ini disebabkan karena tampilan windows 7 berbeda dengan tampilan pada windows versi sebelumnya sehingga membuat kita sulit menemukan cara mengubah format penulisan

Read More

Ilmu Komputer Mengubah Format Inch Menjadi Centimeter Office 2007

Mengatur margin adalah hal yang penting termasuk ilmu komputer tentang cara mengubah format inch menjadi centimeter office 2007 adalah hal yang mutlak diperlukan dalam melakukan editing artikel, khususnya orang indonesia yang sehari menggunakan besaran meter dalam menghitung besaran panjang sehingga format default microsoft office word 2007 dalam inch harus diubah menjadi centimeter agar sesuai

Read More

Yuk Belajar Menghitung Jumlah Jenis Kelamin Pada Microsoft Office Exel

Sebagai seorang guru atau seorang administrasi atau karyawan di sebuah lembaga pendidikan atau sekolah merupakan salah satu hal yang diwajibkan untuk mengetahui jumlah siswa yang terdaftar pada sekolah tersebut. Baik dari besar keseluruhan jumlah siswa, kemudian berapa banyaknya siswa yang berjenis kelamin laki-laki dan perempuan.

Meski bisa dilakukan dengan cara menghitung secara manual

Read More

Installasi 2Dua Office Dalam Satu Komputer

Microsoft sebagai produsen microsoft office tidak merekomendasikan untuk menggunakan lebih dari satu office di dalam satu komputer, namun microsoft tidak melarang para pengguna office untuk mencobanya. Namun apabila ingin melakukan installasi office lebih dari satu dalam sebuah komputer bisa baca syarat yang disampaikan oleh microsoft sendiri bisa di baca di sini http://support.microsoft.com/kb/

Read More

Install Dua Office Dalam Satu Komputer

Cepatnya perkembangan microsoft office membuat pengguna komputer kewalahan mempelajarinya sehingga tidak bisa menguasai microsoft office versi seblumnya dan kita sudah dihadapkan dengan microsoft office versi terbaru.


Belum sempat belajar secara tuntas microsoft office versi yang lebih kecil sudah ada microsoft office versi terbaru sehingga kita sebagai pengguna microsoft office dalam

Read More