Posts Tagged ‘excel’

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

Techie Tip of the Week: Conditional Formatting in Excel

Friday, August 12th, 2011

In Excel, conditional formatting allows you to set up rules to format cells based on a set of criteria.

For example, let’s say you want to quickly figure out how well students are doing in a class. In the example below, using conditional formatting, the students who score 90% or higher are highlighted in green; the students who score between 80 and 89% are highlighted in yellow; the students who score less than 80% are highlighted in red.

In Excel 2003 and earlier, to use conditional formatting, select the cell, and then on the Format menu, select Conditional Formatting, and then set the conditions.

In Excel 2007 and newer, select the cell, and then on the Home tab, click Conditional Formatting and then click Manage Rules. Then, set the conditions.


Without Conditional Formatting:


With Conditional Formatting: