Archive for the ‘ms office’ Category

Techie Tip of the Week: PivotTable Basics

Friday, April 26th, 2013

PivotTables are used to organize and summarize data in numeric form. If you have a large Excel spreadsheet with lots of information stored in it, a PivotTable can make the data easier to comprehend and manage.

 

To create a PivotTable:

  1. Select a cell in your spreadsheet.
  2. In Excel 2007, 2010, or 2013, click Insert, and then click PivotTable.
    In Excel 2008 or 2011, click Data, and then click PivotTable.
  3. Select the range of data you wish to be included in your PivotTable. Then, choose where you wish the PivotTable to be displayed (a new Worksheet or an existing Worksheet). Click OK.
  4. Select the fields you wish to be included in your Pivot by dragging them to the Row, Column, and Values sections. Be sure to put numeric data in the Values. The Report Filter can be used as a way of further filtering out the data.

 

For example, from this spreadsheet (fake data taken from the Sample Human Resources Report found in ReportMart1):

 

the following PivotTable was created by dragging the Salary to Values, the Dept Name to Column, and the Range to Row:

 

And this PivotTable was created by dragging the Dept Name into Column, the Employee Number into Values (changing it from Sum to Count by clicking the i button), and Range and Job Title to Row:

 

Techie Tip of the Week: Place and Manipulate Objects in PowerPoint

Friday, April 19th, 2013
PowerPoint allows you great flexibility in terms of manipulating where objects (e.g., text boxes, pictures, and drawings) will appear on the screen. These tips will help you use the object and drawing functions more effectively.
  1. To align multiple objects so they line up precisely using the Drawing toolbar:
    1. Make sure that the Drawing toolbar is checked. Under View, select ToolbarsDrawing should have a check to the left.
    2. Hold down the shift key while selecting the objects you wish to align.
    3. In the Drawing toolbar, under Drawchoose Align/Distribute and select the desired Alignment/Distribution.
  2. To align objects using Snap To, Grids, andGuides:
    • In the Drawing toolbar, under Draw, selectGrid and Guidelines.
    • To display the grid, check the box Display grid on screen.
    • To snap objects to the grid, check the boxSnap objects to grid. Objects will now snap to the grid when they are moved around.
  3. To move an object very precisely, by “nudging” it:
    • Select the object you wish to “nudge”.
    • On your keyboard, use the arrow keys to move the object. Hold down the control key, if you want even more control. The object will move in increments of 0.02 inches!
  4. To connect two objects with a drawn line:

    • In the Drawing toolbar, underAutoShapes, select Connectors, and choose the desired connector.
    • Move the mouse to the first object until you see a blue dot. Click to create a connection point.
    • Drag the mouse to the second object to create a connecting line. Click when you see the blue dot on the second object. The two objects are now connected.
  5. To group multiple objects together:
    • Hold down the shift key while selecting the objects you wish to group.
    • In the Drawing toolbar, under Draw, choose Group. The objects are now grouped, and can be moved, resized, or otherwise manipulated together.
  6. To change the order of objects that are on top of one another:
    • Select the object whose order you want to change.
    • In the Drawing toolbar, under Draw, choose Order and then the desired order (e.g., Send Backward, or Bring to Front).
  7. To create a text box, under the Insert menu, choose Text Box.
  8. To modify an existing text box:
    1. To resize the text box, select the box and click and drag the dot in the middle of the border (sizing handles) to the desired size. Dragging the corner will resize both the height and width; holding down the Shift key while resizing will retain the ratio between the height and width.
    2. To modify the content of a text box, select the box’s border twice — the border will change from diagonal lines to dots. Once selected, you can modify the text box’s content:
      1. To change the font, under Format, choose Font. Select the desired font changes (font face, style, size, color, and/or effects) and click OK.
      2. To move a text box, click and drag the box to the desired location.
      3. To change text box’s format, double-click the border.
        • In the tab Color and Lines, you can change the text box’s Fill,Lines, and/or Arrows styles.
        • In the tab Size, you can change the text box’s size and rotation and scale (height and width).
        • In the tab Position, you can change the location of the text box in the slide.
        • In the tab Text Box, you can change the anchor point of the text (top/middle/bottom), as well as the internal margins of the text box.

Techie Tip of the Week: Adding Dummy Text in Word

Friday, November 16th, 2012

Ever find yourself wanting to test out a new layout for a brochure, poster, or article but don’t want to use actual content? The typesetting industry has for centuries used latin text to do exactly this (generally starting with “Lorem ipsum dolor sit amet, consectetur adipiscing elit”).

Microsoft Word makes it easy to add this dummy text, and the newer versions of Word give you greater choice in the type of fake content you can add.

Here’s how to add dummy text using MS Word:

All Macintosh versions of MS Word and all versions of Word from 2003 and earlier:

To generate a number of paragraphs and sentences with the sentence “The quick brown fox jumps over the lazy dog”, use this command:

=rand(number of paragraphs, number of sentences)

For example, to generate 1 paragraph with 5 sentences of “The quick brown fox jumps over the lazy dog.”, use the command =rand(1,5):

The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog.

 


 

Word 2007 and 2010 – “Quick brown fox”

To generate a number of paragraphs and sentences with the sentence “The quick brown fox jumps over the lazy dog”, use this command:

=rand.old(number of paragraphs, number of sentences)

For example, to generate 1 paragraph with 5 sentences of “The quick brown fox jumps over the lazy dog.”, use the command =rand.old(1,5):

The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog.

 


 

Word 2007 and 2010 – Random text from the help sections

To generate a number of paragraphs and sentences from the help section, use this command:

=rand(number of paragraphs, number of sentences)

For example, to generate 1 paragraph with 5 sentences from the help section, use the command =rand(1,5):

On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look.

 


 

Word 2007 and 2010 – Lorem Ipsum

To generate a number of paragraphs and sentences using the classic Lorem Ipsum text, use this command:

=lorem(number of paragraphs, number of sentences)

For example, to generate 3 paragraphs with 2 sentences of Lorem Ipsum, use the command =lorem(3,2):

Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Maecenas porttitor congue massa.

Fusce posuere, magna sed pulvinar ultricies, purus lectus malesuada libero, sit amet commodo magna eros quis urna. Nunc viverra imperdiet enim.

Fusce est. Vivamus a tellus.

 

Techie Tip of the Week: LibreOffice and OpenOffice – Open Source Alternatives to MS Office

Friday, September 7th, 2012

Last summer, we talked about OpenOffice.org as a viable alternative to the Microsoft Office near-monopoly on office software.

This week, we’d thought we’d revisit OpenOffice.org and let you know about a particularly great version of OpenOffice – LibreOffice!

As they say on their website, LibreOffice is the power-packed free, libre and open source personal productivity suite for Windows, Macintosh and GNU/Linux, that gives you six feature-rich applications for all your document production and data processing needs.

Writer, Calc, Impress, Draw, Math and Base all look, feel, and work the same way that their Office 2003 equivalents did. And they can open, edit, and save all Office file formats, including the new 2007/2010 versions. You can save your documents in any office format, including PDF!

Some of the newest features:

 

LibreOffice /OpenOffice Equivalent Microsoft Office Equivalent
Writer Word
Calc Excel
Impress PowerPoint
Base Access
Draw An All-Purpose Diagramming and Charting Tool (no real MS Office Equivalent – similar to the drawing tools in PowerPoint)
Math MS Equation Editor

Watch a Tech Briefing video Open Source Tools, including OpenOffice and LibreOffice.

Techie Tip of the Week: Apply a New Slide Master to a PowerPoint Presentation

Friday, July 27th, 2012

Tired of the same-old PowerPoint templates that Microsoft offers? You can create your own, and then apply that new template to future presentations. Today’s tip will show you how to apply a new theme/design to your presentation.

  1. First, create (or find) a PowerPoint slide deck that looks the way you want all future slide decks to look.
  2. Next, create (or open, if it already exists) the PowerPoint slide deck you wish to change.
  3. If you are using PowerPoint 2003, on the Format menu, click Slide Design.
    If you are using PowerPoint 2007/2010, on the Ribbon, click Design.
  4. If you are using PowerPoint 2003, click the Browse button at the bottom of the task pane.
    If you are using PowerPoint 2007/2010, click the More button found in the lower-right corner of the default designs, and then click Browse for Themes.
  5. Locate the PowerPoint file you wish to use as the template.
  6. Click Apply.

Your new theme/design template is now applied to your presentation.

Techie Tip of the Week: Use Word’s Mail Merge to Send Email to Multiple Recipients

Friday, June 22nd, 2012

Did you know that you can easily create and send personalized email newsletters and messages to multiple recipients using Microsoft Word (coupled with Excel)?  Although the bulk of the content of the email will be the same, you can customize or personalize parts of the email.

Each message will be a unique email, and you can personalize each one — for example, by addressing each recipient by name. The names and email addresses would come from a data source, like an Excel spreadsheet. Since each email is a separate message, you can use the To: field instead of the Bcc: field! This can make it less likely that your recipient’s email servers will treat the email as spam.

For detailed step-by-step instructions:

 

 

Techie Tip of the Week: Creating Drop-Down Lists in Excel Part 2

Friday, June 8th, 2012

Last week we talked about creating drop down lists in Excel using data that is in the same worksheet as the drop-down menu. But that looks cluttered — what if you could put the data on a separate sheet and just have the drop-down menu? You can! Here’s how:

To create a drop-down menu with the data in a separate worksheet:

  1. In Excel, at the bottom of each workbook there are multiple worksheets you can use. Double-click the second sheet and give it a name (in this example, I’m using “data”).

    20120609-175926.jpg

  2. In the second sheet, enter the data for the pre-selected list. In this example, I’m entering the data in A1, A2, A3, and A4

    20120609-175943.jpg

  3. Return to the first sheet and click the cell in which you want the drop-down list to be displayed. In this example, I’m using cell B1.

    20120609-175953.jpg

  4. Click the Data tab. Then, in Data Tools, click Data Validation.
    screenshot of data validation
  5. In the Settings tab, in Allow, select List.
    in Settings, in Allow, select List
  6. In Source, enter the sheet and the cells with the data you entered in step 2, and then click OK.

    In this example, I have selected cells A1 – A4 from the sheet “Data” as the source by typing in the following:
    =Data!$A$1:$A$4

    20120609-180003.jpg

A drop-down arrow now appears in the cell you chose in step 3(in my case, B1). Clicking the arrow on the list displays the data pieces, and you can now select one of them.

20120609-180012.jpg

Techie Tip of the Week: Creating Drop-Down Lists in Excel

Friday, June 1st, 2012

When working in Excel, you may want (or need) to create a drop-down list to make it easier and faster to select data from a pre-created set of items — so you can just select them instead of having to type them in. This week’s tip will show you how to create a drop-down list in Excel.

To create a drop-down list:

  1. Enter the data for the pre-selected list. In this example, I’m entering the data in A1, A2, A3, and A4.
    cells a1-a4 have faculty, staff, student, and other
  2. Click the cell in which you want the drop-down list to be displayed. In this example, I’m using cell D1.
    screenshot of cell d1 selected
  3. Click the Data tab. Then, in Data Tools, click Data Validation.
    screenshot of data validation
  4. In the Settings tab, in Allow, select List.
    in Settings, in Allow, select List
  5. In Source, enter the cells with the data you entered in step 1, and then click OK.

    In this example, I have selected cells A1 – A4 as the source by typing in the following:
    =$A$1:$A$4

A drop-down arrow now appears in the cell you chose in step 2 (in my case, D1). Clicking the arrow on the list displays the data pieces, and you can now select one of them.

Next week, we’ll show you how you can create a list in Excel, but this time with data that is stored on a different worksheet!

Mac Users: MS Office Interactive Command Reference Guides – 2008 to the new interface!

Friday, December 2nd, 2011

A few weeks ago we spotlighted the Interactive Command Reference Guides for Windows users. Mac users, have no fear! Here are the links to the Interactive Command Reference Guides for you!

Finally making the leap from Office 2004 or 2008 to 2011? Wondering where your favorite commands are located in the new interface? Or just want to explore the new interface with a little guidance?

Techie Tip of the Week: Require a Password to Open MS Office Documents

Friday, October 28th, 2011

By adding password protection to an MS Office document, you will help prevent people from gaining access the document unless they knows the password. Don’t lose the password — if you misplace or forget it, YOU  will be unable to open the file!

To make an MS Office document password protected:

  1. Open the file.
  2. In Office 2003, on the Tools menu, point to Options, and then click Security.
    In Office 2007, click the Office button, then click Prepare, and then click Encrypt Document.
    In Office 2010, click the File button, then click Info, then click Protect Document, and then click Encrypt Document.
    In Office 2004, on the File menu, click Save As, then click Options, and then Security.
    In Office 2008 or Office 2011, on the File menu, click Save As, then click Options, click Show All, and then Security.
  3. In the Password to open dialog box, enter a password, and then click OK.
  4. In the Reenter password to open dialog box, enter the password again, and then click OK.