Category Archives: Microsoft Office 2007/2010 Tips

Rearranging Paragraphs in a Word Document

Shifting or rearranging paragraphs in Microsoft Word can be tedious.  If using drag and drop you may go too far or not far enough.  Then you must start the process again.  When copying and pasting, formatting is often changed erroneously.  Is there another way?  Of course!

Instead of copying and pasting paragraphs, which can wreak havoc on your formatting and is generally a bit clunky, try using a keyboard shortcut.

Highlight the paragraph to be moved and click ALT-SHIFT. While holding down the ALT & SHIFT keys, use the up or down arrows to move the paragraph around the document. The entire section will move as if it’s a single block.

Could it be any easier?

_________________________________________________________________________________

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


IF: A Very Powerful Excel Function

The IF function evaluates a condition (a logical test), and then returns one value if the condition is true and another value if the condition is false.

Use the following information as a guideline for the IF Function.  It is also helpful to use the Function Arguments dialog box (shown above) to build the If Function.  (Click fx button to left of Formula Bar and Locate IF function.)

The IF function has three arguments:

A condition that is evaluated as true or false.

The value to be returned if the condition is true.

Value may be Text (surrounded by quotation marks; “” denotes leave blank)

Value may be a Number

Value may be a Formula

The value to be returned if the condition is false.

Value may be Text (surrounded by quotation marks; “” denotes leave blank)

Value may be a Number

Value may be a Formula

The condition (first argument) uses one of the following six relational operators:

Equal To: =
Not Equal To: <>
Less Than: <
Greater Than: >
Less Than or Equal To: <=
Greater Than or Equal To: >=


The format of the IF function is:

=IF(condition to be evaluated or logical test, value if true, value if false)

For example, if your employees were to receive a bonus or $250 if their sales for the month of May were greater than or equal to $1000, then here is what the IF function would look like in cell C8 for Ben (the first employee):

Ben’s sales for the month were $1500 (B8) and the bonus cell is C8.

=IF(B8>=1000, 250, 0)  or to leave bonus cell blank  =IF(B8>=1000, 250, “”)

Condition to be evaluated: B8>=1000

Value if true:  250

Value if false:  0  (or “” to leave cell blank)

You could then copy the formula to determine which employees were to receive a bonus of $250 and which employees were to receive no bonus.

If you have never used the IF function in Excel, give it a try.  The function is a real time saver when performing certain types of calculations using any version of Microsoft Excel.

______________________________________________________________________

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


Let Excel’s Workspace Feature Help Save You Time!

Do you need to work with several workbooks (files) at one time in a particular arrangement on the screen? If so, did you know that you can group them so they can be opened in one step by creating what is known as a workspace?

A workspace is a file that groups all of the workbooks in your group and opens them all at once in the desired arrangement. The workspace feature creates a file that has an .xlw file extension. You save time because you don’t have to open each file individually and then re-size. This feature comes in handy if you are working with three or four files at a time.

To create workspace, just complete the following steps:

  1. Open all of the workbooks that you want to reside in the workspace.
  2. Size and position them as would like them to appear on the screen.
  3. Click the View tab and click the Save Workspace button in the Windows group.
  4. Type a name for your workspace file and navigate to the file location where you want your workspace file stored.

IMPORTANT:

The workspace file does not contain the workbooks themselves, so you will still need to save any changes you make to your workbooks to the actual workbooks. Hint: You can add the Save All and Close All icons to your QUICK ACCESS TOOLBAR to help you quickly save and close all of the files.

Remember, if you need to work at another computer, you need to have the workspace file as well as all of the workbook files—so it’s a good idea to save all of the files in one handy folder.

Source: Microsoft Excel 2010 Illustrated Series: Complete by Elizabeth Eisner Reding and Lynn Wermers, Course Technology/Cengage Learning, Boston: 2011, p. 145.

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.


Conditional Formatting in Excel 2010

Conditional formatting is formatting that Excel automatically applies to selected cells if a particular condition is true. Let’s say that you are a project manager for a city and you want to see which of your projects have a budget of more than $400,000.

To add conditional formatting to the Budget column, select all of the budgeted amounts, click the Home tab, and in the Styles group click Conditional Formatting. Select Highlight Cell Rules and choose the comparison phrase, “is greater than.” In the text box below the phrase, Format cells that are GREATER THAN, type 400000.

Use the default formatting, Light Red Fill with Dark Red Text, and click OK. All of the selected cells in the Budget column that are greater than 400000 will be formatted with a light red background and dark red text. This allows you to quickly see the cells that match the condition.  If one of the numbers in the range of cells changes, Excel will automatically apply the formatting if the new value meets the condition or remove the formatting if the new value does not meet the condition.

You may also use a formula to set conditional formatting. Select the cells you wish to conditionally format using a formula, click Conditional Formatting on the Home Tab, click Highlight Cells Rules, and at the bottom of the list click More Rules.

In the New Formatting Rule dialog box, select the last item (Use a formula to determine which cells to format) and enter the formula in the text box below Format values where this formula is true. Click Format and select the formatting you wish to apply where the formula is true. Click OK twice to accept and exit the new conditional formatting setting.

______________________________________________________________________

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


Formula AutoComplete in Excel

Would you like to enter an Excel function in a cell without typing the entire function name? It is easy to do if you are familiar with the Formula AutoComplete feature in Excel.

To use this feature, click to select the cell in which you wish to enter the function. Type an equal sign and begin to type the function name. As you begin typing the function name, a list of functions appears below the active cell. The functions listed match the letters you have typed. Continue typing until you see the function you want.

Then, double-click the name of the function you want to use. The function and its arguments appear in a ScreenTip below the cell; you can use the ScreenTip as a guide to enter the necessary function arguments.

Give it a try! FormulaAutocomplete is another way to help you get your spreadsheets completed quickly and accurately.

______________________________________________________________________

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


Changing the Default Chart Type in Excel

You may already be aware that the fastest way to create a chart in Excel is by using the F11 key.  You simply select the data on the Excel spreadsheet that you want to chart and then press the F11 key.  Your chart is created and placed on a new sheet tab.

When you press F11, the default chart type is created—a 2D Column Chart.  What if, however, you want to change the default chart type for Excel so that when you press F11 a different type of chart is created?

Your first step is to open the Change Chart Type dialog box (a chart must be selected on the screen), click the Chart Tools Design tab, in the first group on the Ribbon (Type), then click Change Chart Type, which will open the dialog box. 

 

Choose the chart you want to use as the default.  Click the Set as Default Chart button at the bottom of the dialog box. 

The next time you use the F11 chart shortcut key, it will present the type of chart you selected as the new default!

______________________________________________________________________ 

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


Excel and Subtotals

If you frequently click on the Data tab in either Excel 2007 or Excel 2010, you may have noticed a button in the Outline group called “Subtotal.”  It is a feature used when summarizing repeating data in a list of information is important.  Many users of Excel’s database features are unfamiliar with this feature.  However, its utilization will save a great deal of time if the task you have been asked to perform involves summarizing data.

Here is a link to an article by Susan Harkins published as part of the TechRepublic Blog.  The article not only explains the Subtotals feature but also shows easy to follow examples on how the feature is used.  The files she uses to explain the feature are also available for you to download and practice.

Click here to view the Susan Harkins article on the Subtotals feature in Excel.

You will notice that the Subtotals feature provides a quick, easy way to group and summarize a range of data.  It also lets you create not only subtotals using the SUM function, but other statistics including COUNT, AVERAGE, MAX, and MIN.  Enjoy exploring and using this versatile Excel feature!

______________________________________________________________________

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


Want to Change the Default Font for New Word Documents?

Many Microsoft Word users change the font for every new document they create simply because they do not know how to change the default font used for all new documents.

Let’s assume that you want to use Times New Roman, 12 point type for all new documents.

It is very simple to do; just follow the steps listed below:  

  1. Open Microsoft Word (2007 or 2010)
  2. Launch the Font Dialog Box by clicking the diagonal arrow at the right end of the Font Group name on the Home tab of the Ribbon
  3. Select to font and font size that you wish to use for all new word documents
  4. At the bottom of the Font Dialog Box, click Set AS Default
  5. When the question, “Do you want to set the default font to Times New Roman, 12 pt  for:  This document only? or All documents based on the Normal.dotm template?” Click to select All documents based on the Normal.dotm template

Each time you create a new Word document the font will be set to Times New Roman, 12 point type.  How easy is that?

______________________________________________________________________

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


Stay Organized by Using the Excel COUNTIFS Function

Have you ever been faced with the task of keeping a running tally of items that may have a change in status over time? If so, the COUNTIFS function can help you stay current with the status of your data/population.

Scenario: Let’s say your company has implemented a policy that requires all employees to attend a training session on safety and then to pass the safety exam that follows. Let’s say that employees are expected to attend the required training session, which will be offered over a five-month period during the year. Additionally, the exam must be taken and passed by the end of the calendar year.
If your company has different departments and wants to see how many people have attended one of the training sessions and also wants to find out how many people from the various departments have passed the exam, someone will need to go through the data periodically and determine who has attended by department and who has passed by department. This is not hard to do if you have a few employees, but let’s say there are over 200 people who work in the company, and remember they work in different departments! So much for tick marks!
The COUNTIFS function can help you look at multiple conditions at once. If you want to determine (1) who has attended by department and (2) who has actually passed the exam by department, the COUNTIFS (not to be confused with the COUNTIF function, which only looks at one condition) can be a life saver if you have large amounts of data to analyze.

However, there are two important guidelines that must be observed–(1) the ranges you are examining must be consistent in terms of the way data is entered, e.g., use the same case and wording—if you use IT once, then be consistent and type the same wording for this item—don’t type It. or it the next time; (2) the Criteria Ranges should be made absolute ranges, e.g., $C$3:$C$350 or the ranges should be given specific names so they remain constant when copied.

You will find as you update your data—in this example the date a session was attended and if a person passed the exam, you can get your updated information by totals for each department.
The “Percentage Completed by Department” was calculated by using the COUNTIF function of
=(E31/COUNTIF($C$2:$C$27,”IT”))
For more information on BOSS software offerings, the BOSS degree and certificates, and how the BOSS program can help you with your career, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.

Customize and Adjust Your Word 2010 Settings (Part II)

In our July 30th post, you read about some ways to customize your Word 2010 application.  There are several more of these that might be of interest to you!

Along with changing the font defaults in Word 2010, you can also change layout defaults.  These include margins, orientation, and many others.  For example, open the Page Setup dialog box by clicking the launcher button in the Page Setup group on the Page Layout tab. (The launcher button is located in the bottom right corner of a group of buttons.  The group name is below the group of buttons.  The launch looks like a little arrow with a line to the top and left of it.)  After making your adjustments, click the “Save as Default” button, then choose Yes and hit the OK button.

Another customization you can make is to the Paragraph settings.  Use the keyboard shortcut Alt + O, then hit P to open the Paragraph dialog box.  You can also click on the Home tab and then on the Launcher in the Paragraph group to open it, too.  Change the spacing and other options, then click “Save as Default”, Yes, and OK.

One of the buttons that I really miss is the Autoformat command button. If someone sends you a horribly formatted document, the Autoformat feature can be a time-saver.  I’ve experienced this when someone sent me the text in the body of an email which I then had to add it into a Word document.   The Autoformat button is not on the ribbon in Word 2010 (nor was it in Word 2007).  You can bring back the button on the Quick Access toolbar, though.

The toolbar appears above the ribbon (directly over the File and Home tabs).  Click the down arrow at the right end of the toolbar.  Click More Commands.  You will see a drop-down menu under “Choose commands from”. Select “Commands Not In the Ribbon”.  Then, choose Autoformat, click the Add button, and then OK.  While you are here, add any other commands that you use regularly.

______________________________________________________________________

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