Archive for the ‘Excel’ 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: Create Pop-Up Text in Excel

Friday, November 23rd, 2012
In Excel, you can have a pop-up appear when someone selects a particular cell. This pop-up can provide information to the person entering data — e.g., instructions on how to enter the data.
To create a pop-up in Excel:
  1. Select the cell in which you want the pop-up text to display.
  2. Click the Data tab.
  3. In Data Tools, click Data Validation, and then click Data Validation.
  4. In the Input message tab, check Show input message when cell is selected.
  5. In Title, enter a title for the pop-up window.
  6. In Input Message, enter the text you want to appear in the pop-up window.
  7. Click OK.
  8. Now when the cell is selected, the pop-up appears:

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: 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?

Use AutoCorrect in MS Office to Save Typing Time!

Friday, October 21st, 2011

Here’s a great time-saving technique in Microsoft Office — use AutoCorrect to automatically enter text that you often use. Most people realize that AutoCorrect automatically corrects misspelled words — for example, the misspelled word abbout is automatically corrected to about.

But did you know that you can create your own set of keyboard shortcuts to automatically write phrases or words you often use? For example, if you often type Stanford University in a document, you can set the letters su to automatically be replaced with the phrase Stanford University.

Here’s how to add phrases and words in the AutoCorrect options:

Office 2003/2004/2008/2011

  1. On the Tools menu, click AutoCorrect.
  2. In the Replace text as you type section, in the Replace field, enter the shortcut text (e.g., su).
  3. In the With field, enter the text you wish it to be replaced by (e.g., Stanford University).
  4. Click Add, and then click OK.

Office 2007/2010

  1. Click the Office Button (2007) or the File button (2010).
  2. Click Options.
  3. Click Proofing.
  4. Click AutoCorrect Options.
  5. In the Replace text as you type section, in the Replace field, enter the shortcut text (e.g., su).
  6. In the With field, enter the text you wish it to be replaced by (e.g., Stanford University).
  7. Click Add, and then click OK.

MS Office Interactive Command Reference Guides – 2003 to the new interface!

Friday, September 23rd, 2011

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

Microsoft has put together a handful of interactive web applications that will help ease the transition. Check them out!

Techie Tip of the Week: Inserting Images in Microsoft Office – Layout Status

Friday, August 26th, 2011

Original blog posting: http://bit.ly/q0Fx3v

Have you ever noticed that when you insert a photo or other image in a Microsoft Office application (Word, PowerPoint, Excel) that it sometimes “floats” around the document, but sometimes it stays inline with the rest of the document?

That’s because in MS Office, there are 5 layout statuses you can use when inserting an image, some that keep the image inline with the rest of the document, and others that allow the image to float.

Here’s how to change the Layout status of an image:

  1. Insert the image.
  2. Right-click the image and select Format Picture.
  3. Click Layout, and then click the desired Wrapping Style:

    1. In line with text – puts the image in the same line as the text (as if it were another character in the document)
    2. Square – floats the image, and places the surrounding text in a square shape around the image
    3. Tight – floats the image and puts the surrounding text as close as possible to the image
    4. Behind text – floats the image behind the text (as in a watermark)
    5. In front of text – floats the image on top of the text (without moving the text around it)
  4. Then, click OK.

Here are examples of the 5 wrapping styles:

  1. In line with text:
  2. Square:
  3. Tight:
  4. Behind text:
  5. In front of text: