Posts Tagged ‘excel 2004’

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