Posts Tagged ‘excel 2007’

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

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 — Minimizing the Ribbon

Friday, July 29th, 2011

Ever work in an office document and need more screen real estate to see all of your large document? Want to hide the Ribbon in one of the Office 2007/2010/2011 programs (Word/Excel/PowerPoint/Access)? Have you ever accidentally hidden it and didn’t know how you did it?

To minimize the Ribbon, you can do one of the following:

1) In Office 2007, click the Customize Access Toolbar button and then click Minimize the Ribbon.

Click the Quick Access Toolbar, and then click Minimize Ribbon

2) In Office 2010, click the Minimize the Ribbon button (the upwards-pointing button located in the upper-right corner). To restore, click the Expand the Ribbon button (the same location, but now the button is pointing downwards).

Hide Ribbon:
Minimize Ribbon button - 2010

Expand Ribbon:

3) Right-click the Ribbon, and click Minimize the Ribbon.

Right-click and select Minimize Ribbon

4) Use the keyboard shortcut CTRL+F1.

Techie Tip of the Week: Use the Fill Handle to Copy Data in Excel!

Friday, March 18th, 2011

The Fill Handle in Excel is found at the lower-right corner of the cell you have selected. When the mouse is hovering over that corner, the pointer will change to a dark, black, bolded plus sign. That’s the Fill Handle. One of the best uses of the Fill Handle is to quickly and easily copy data in your spreadsheet.

For example, suppose you are creating a list of all of the people in your department, separated by affiliation. Instead of copying and pasting “student” or “faculty” or “staff” over and over again, you can use the Fill Handle to quickly copy the affiliation for you.

Here’s how:

  1. In Excel, enter the data in the cell you wish to copy. In this case, we are copying the word “student” in Column C, row 2.
    Screenshot of a spreadsheet with a list of names and one cell with affilation (the rest of the affilations are blank)
  2. Hover the mouse over the lower-right corner of the cell you want to copy until it changes into a bold plus sign.
    Screen shot of step #2.
  3. Click and drag the mouse to the last cell to be copied.
    Screen shot of step #3
  4. Release the mouse button. The data have been copied!

For more tips like these, come take one of the upcoming IT Services Technology Training Excel classes!

View our current schedule

Techie Tip of the Week: Getting Excel Charts into PowerPoint – don’t just copy/paste!

Saturday, February 5th, 2011

Need to import an Excel chart into a PowerPoint Presentation? Don’t just copy and paste the chart! This can let viewers of your PPT file gain access to the data and change the way the chart looks.

Instead:

  1. In Excel:
    1. Select the chart.
    2. Copy the chart (Control/Command – C).
  2. In PowerPoint:
    1. Create the slide in which you wish the chart to be displayed.
    2. Bring up the Paste Special dialog box:
      • PowerPoint 2003/2004/2008/2011
        Click Edit>Paste Special
      • PowerPoint 2007/2010
        On the Home tab, click the arrow under Paste, and select Paste Special
  3. Choose Picture.
  4. Click OK.

Now your chart will appear as just a picture, the chart won’t have the data embedded inside of it. This helps prevent viewers of your presentation from gaining access to the data and possibly manipulating the chart without your permission. Plus, it’ll make your PowerPoint file smaller in size!

To learn more about Excel, come take one of our upcoming Excel classes!
View our current schedule

Techie Tip of the Week: Repeat row(s) in Excel on printed pages

Thursday, January 13th, 2011

Need to repeat row(s) in Excel on printed pages?

Try this:

  • Excel 2003/2004/2008/2011
    1. On the File menu, click Page Setup.
    2. Click the Sheet tab.
    3. In Rows to repeat at top, enter the rows you wish to repeat, using the $ to indicate the row.
      Examples:

      • To repeat just the first row, enter $1:$1.
      • To repeat rows 1 and 2, enter $1:$2.
      • To repeat rows 1-5, enter $1:$5.
    4. Click OK.
  • Excel 2007/2010
    1. Click Page Layout.
    2. In the Page Setup group, click Print Titles.
    3. Click the Sheet tab.
    4. In Rows to repeat at top, enter the rows you wish to repeat, using the $ to indicate the row.
      Examples:

      • To repeat just the first row, enter $1:$1.
      • To repeat rows 1 and 2, enter $1:$2.
      • To repeat rows 1-5, enter $1:$5.
    5. Click OK.