Category Archives: Microsoft Office 2007/2010 Tips

Five Easy Word Tips Every User Should Know

We all have our favorite Microsoft Word tips and shortcuts! My favorite shortcut is that pressing the F12 key opens the Word Save As dialog box.

In this article, Susan Harkins shares five easy-to-implement tips that will simplify your Microsoft Word tasks. Another of my favorites, which she discusses in her first tip, explains how to quickly select a sentence. This tip is especially handy when the sentence begins and ends in the middle of a line.

Please click the link below to read all of the tips from Susan on the Tech Republic Web site; save time and have fun using your newly acquired knowledge!

5 Easy Word Tips by Susan Harkins

____________________________________________________________________________________________________

For more information on the Business Office Systems and Support department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.


Using the Data Validation Feature in Excel  

Have you ever wished for a way to limit the data in an Excel cell to match certain parameters?  If so, the Data Validation feature located under the Data tab in the Data Tools group is for you!  It is a feature that is simple to use and offers several options.

Let’s say that the name of your column in Excel is Paid and the correct response in the cells of this column is either Yes or No.  To limit the values for this column to either Yes or No, do the following:

Select the cells you wish to limit to a value of either Yes or No.  Click the DATA tab; in the Data Tools Group click the Data Validation button.  The dialog box shown below will open.  Click the down arrow in the Allow text box and choose List.  Click in the Source box and type Yes, No as shown below.  This will limit the data input for the selected cells to accepting only Yes or No in the cell.

Validation Yes No box

The cells to which the validation is applied will also have a drop down arrow allowing the user to select either Yes or No from the drop down list when the cell is selected.

If you click the Input Message tab, you may enter a short message telling the user the values that may be entered in the selected cell.  When one of the restricted cells is selected, the message you see at left will appear.

Validation Message

Could it be any easier?

______________________________________________________________________

For more information on the Business Office Systems and Support (BOSS) department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.


Excel Formula Problems? Try These Troubleshooting Tips!

Ribbon with FBWhen you work with formulas in Microsoft Excel, there are sometimes some glitches. Excel is good about letting you know when there is an error and even offering some help. The Error Checking feature on the Formulas tab in the Formula Auditing Group will find errors such as Divide by zero errors or errors in cell references (#VALUE!) where a cell referenced in the formula does not contain a number.

Also in the Formula Auditing group are the Trace Precedents, Trace Dependents, and Remove Arrows buttons. The Trace Precedents button shows arrows that indicate which cells affect the value of the currently selected cell. The Trace Dependents button show arrows that indicate which cells are affected by the value of the currently selected cell. The Remove Arrows button removes the arrows drawn by the Trace Precedents and Trace Dependent buttons. These buttons are very helpful when determining incorrect cell references in formulas.

However, with especially long formulas, it is sometimes necessary to examine small parts of the formula at a time to figure out the problem. You may use your mouse pointer to look at only a certain part of a formula and press F9 to evaluate just that section. Press Esc to go back and keep searching. The Evaluate Formula button on the Formula Auditing group will also allow you to examine small parts of a long formula.

Another common problem in Excel formulas is a circular reference. A circular reference in an Excel formula is one that depends on its own value. The most common type of circular reference occurs when you mistakenly refer in the formula to the cell in which you are building the formula itself. For example, suppose that cell B10 is active when you build this formula,  =A10+B10.   As soon as you enter this formula in cell B10 (assuming the program is in Automatic recalculation mode), Excel displays an Alert dialog box, stating that it cannot calculate the formula due to the circular reference.

Do not forget, too, that by clicking the down arrow at the right end of the formula bar you can expand the size of the formula bar to include more than one line. Use these tips to become an expert in troubleshooting Excel formula problems—something that we all encounter when using Excel’s powerful calculation features!

____________________________________________________________________________________________________
For more information on the Business Office Systems and Support department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.

Did Your Ribbon Disappear?

One of the new features introduced in Office 2007 was the Ribbon interface.  The Ribbon is still a vital part of Office 2010 and the newest version, Office 2013.

Some users become very frustrated because suddenly as they are hurriedly completing a task the buttons on the Ribbon are no longer visible and all that can be seen are the tabs across the top of the Ribbon, for example in Microsoft Word 2013, Home, Insert, Design, Page Layout, References, Mailings, Review,  and View.  You might call this the “mystery of the missing buttons,” which can be most annoying to the user.

Ribbon

Ribbon with Only Tabs Displayed

The cause of the problem and its solution are, however, very simple.  We have all become very accustomed to double clicking the left mouse button to issue a command.  However, if you double click a tab on the Ribbon, it hides the buttons on the Ribbon.  How do you get those buttons to reappear?  Simply double click any tab again, and, magically, everything is back as it should be!

_________________________________________________________________________________________________

For more information on the Business Office Systems and Support department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.


The VLOOKUP Function (A Mystery to Many Excel Users)

by Meggen Mills

The VLOOKUP Function is one of the most useful functions in Excel.  It is also one of the most confusing and least understood functions.  This article describes the formula syntax and usage of the VLOOKUP function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.

Example:

You are a new employee in the Benefits department of a large company with over 2500 employees.

Suppose your department has just sent a notice to selected company employees (approximately 750 employees) notifying them that they still have  vacation days available this year, and your boss has asked you to determine the department these selected employees work in. The only problem is that the notice you sent did not ask for their department; you only know their Name and Employee ID number. Continue reading


Customizing Bullets in Word

Word 2013I’m sure most of us have used Word’s bullet feature at some point and, no doubt, selected one of the pre-defined shapes and colors. However, if you want to add a little “zing” to one of your documents, consider using a customized bullet to emphasize your key points.

Let’s suppose I have a document that focuses on Richland College, and that I want to emphasize some important aspects of student life at Richland. The typical user might be tempted to use bullets in the document in the traditional manner:

04-28-2014 Inserted Text with Traditional BulletsHowever, if you want to use a look that ties all aspects of your document together, you may want to consider customizing the bullets. Since the Thunderduck logo has been used as part of the opening in this document, why not repeat this image as a bullet and as part of the focus?

To customize your bullets, simply complete the following steps:

1.  Highlight your bulleted list and click on the Bullet icon to turn the bullets off.

2.  Click the arrow next to the Bullet icon and select Define New Bullet (at the bottom) and choose the middle option Picture. Your screen should be similar to the one pictured below:

04-28-2014 Define New Bullet3.  Select the image file you want to use from the drive and folder where the file is located—the image file will then be uploaded to Word’s bullet library. Click OK to complete the upload process.

4.  Your screen should be showing your document, and you can then select the list of text that you want to have bulleted.

5.  Click the arrow next to the Bullet icon and then click on your custom bullet from the Bullet Library area. Your screen should look similar to the image below.

04-28-2014 New Custom Item in Define New Bullet Area.jpg6.  Your bulleted list should now show the custom bullet for each of your items in the list.

04-28-2014 Inserted Text with Custom Bullets

Take a Microsoft Office 2013 class that can help you improve your technology skills and productivity. You can choose from any number of career-enhancing classes in the BOSS program at Richland College. For more information, please contact Becky Jones, Associate Dean, bjones@dcccd.edu  972-238-6215.

Word logo image courtesy of Microsoft Corporation

 

 

 

 

 

 


Excel Worksheets: Flexible and Easy to Manipulate

When you open a new Microsoft Excel Workbook file, there should be three worksheets available by default, labeled Sheet1, Sheet2, and Sheet3. As your Excel files become more complicated, it’s important to rename each worksheet to appropriately reflect its respective content. To rename a worksheet, right-click its sheet tab and click Rename. Type in the new name of the worksheet and press the Enter key.

When three worksheets aren’t enough for your task, you can add worksheets. To add a single worksheet, right click on any sheet and choose Insert from the Quick Menu.  At the end of the last sheet in the Workbook file, there is also an Insert Worksheet button.  Here’s one more method:  the Shortcut key to insert a worksheet is SHIFT F11.

It is also easy to change the number of sheets in a new workbook file.  In Excel, click File, Options, and choose the General tab on the left.  Under “When creating new Workbooks” change the number 3 in the Include this many sheets box to the number of sheets you wish to have in each new Excel Workbook file that you create.

If you have a particular worksheet you’d like to move or copy to a different location, select the worksheet(s) you want to move/copy, right click and choose Move or Copy from the Quick Menu.  The following dialog box will appear.

Move or Copy Box

In the box above, the selected sheet is set to copy to the Workbook file named, EX D-Quarterly Tour Expenses.  It will be placed before the sheet named Sheet3.

If you forget to check the Create a copy box, the selected sheet will be moved rather than copied.

You are now on your way to being an Excel Worksheet Manipulation Expert!

_________________________________________________________________________________

For more information on the Business Office Systems and Support department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.


Word Tip: Use the Keyboard to Create a Simple Table

11-11-2013 Word Tip--Use the Keyboard to Create a Simple Word Table Image--153454864Have you ever been typing and found that you needed to put some of your information into a table to display it more attractively? Well, Word has a neat keyboard feature that allows you to do just that.

Although Word’s ability to create tables from the Insert tab or to draw them using the Table Draw feature is wonderful and very easy, your fingers don’t have to leave the keyboard if you use the handy tip described below.

For example, if you want a 2-column table:

Optional/Step 1: Make sure your Show/Hide feature is turned on so you can follow your cursor more easily —located in Paragraph group on the Home tab.

Step 2: Make sure your cursor is at the beginning of your left margin and type the “+” key (no quotes), and then use the Space Bar to space the approximate length of where you want the first column to end and then type another “+” (no quotes).

11-11-2013 Word Tip Step 2

Step 3: Use the Space Bar again to space out the length of the second column and type another “+” and press the Enter key and Presto! You have the first row in your table.

11-11-2013 Word Tip Step 3

Step 4: Your cursor should appear in the first column, and you are now ready to begin typing your text. Just press Tab key to move to the second column and type in that text.

11-11-2013 Word Tip Step 4

Step 5: After you have typed in your text in the second column, press the Tab key again and a new row will be created automatically

Hint: If you want three or more columns, use the Space Bar and the “+” key to space out the length for the third column, etc., accordingly, but make sure all characters appear on one line.

After you have created your table and if you want make it fancier, just select the table and use one of the Word styles in Table Tools >Design.

If you need to take one or more classes to help you enhance your technology skills and productivity, consider taking one of the Microsoft Office classes or other skills/productivity development classes from the BOSS area at Richland College.

For more information on BOSS software and productivity course offerings, the BOSS degree and certificates contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.

Join us on Facebook at https://www.facebook.com/groups/RLCBOSS/

 


Merging Cells in Excel

When centering headings on an Excel worksheet, most of us use a feature called Merge and Center.  Merging and Centering cells in Excel is easy. Just select the cells that you want to merge and click the Merge and Center button in the Alignment group on the Home tab (in Excel 2007, 2010, or 2013).

However, have you ever wanted to merge the cells in several rows, but not wanted to merge all of those cells into one gigantic cell? Did you select the cells in the first row, merge them, move on to the second row and so on?  This method works; but fortunately, there is a much simpler way to go about it. The answer is a seldom-used feature called Merge Across.

If you click the down arrow to the right of the Merge and Center command, you will see a command called Merge Across.  To use this feature, select all of the cells that you want to merge across but not down. Click the arrow to the right of Merge and Center and select Merge Across. Your cells will be nicely merged across, but each row will still be separate from those above and below them. If you wish to also center the data in the merged cells, simply click the Center button in the Alignment group on the Home tab.

You may have wondered what would happen if you clicked the Merge Across command.  Now you know!

______________________________________________________________________

For more information on the Business Office Systems and Support department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.


Excel’s Order of Operations in Formulas

In Excel the formula =10+20+30+40/4 will render a solution of 70; I think you would agree that 70 is not the average of the four numbers you have added together and then divided by the number of numbers you added (average).  However, =(10+20+30+40)/4 will render a solution of 25, which is, in fact,  the average of the four numbers.  How is one formula different from the other?  It is related to the order in which calculations are performed in formulas. 

Does anyone remember hearing his/her Junior High and/or High School Math teacher mention the phrase, “Please Excuse My Dear Aunt Sally?”  If so, you may also remember that this phrase is important in remembering the order in which formulas are interpreted. The following is the order in which mathematical operators and syntax are applied both in Excel and in general mathematics: 

  1. Parentheses
  2. Exponents
  3. Multiplication
  4. Division
  5. Addition
  6. Subtraction 

In the first example, Excel divided 40 by 4 (division before addition) and added the result to 10+20+30 equaling 70.  However in the second example, the parenthesis forced Excel to first add 10+20+30+40 and then divide the result of the addition by 4 equaling 25–the correct answer.

Do your formulas meet these criteria?  If not, your calculations might be suspect.  So, Please remember to Excuse My Dear Aunt Sally when creating a formula in Excel!

_________________________________________________________________________________

For more information on the Business Office Systems and Support department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.