Excel Introduction Training

Microsoft Excel is a powerful information management tool. The step by step instructions below walk you through manipulating columns and rows, sorting, filtering, and printing the output.

Goals

Upon completion of this 1-on-1 training, you will be able to:

Training

Assess the initial spreadsheet and visualize desired results

What are you starting with? Before you know what to do with a spreadsheet, you need to know what you have as a starting point. To determine this, do the following:

  1. Scroll to the right to see all the columns.
  2. Select Office Button > Print > Print Preview to see how it will print.
  3. Scroll up/down and left/right to view the entire report.

What do you want in the end? Now that you know what you have, you need to decide what goals you have for the spreadsheet. For example, you might have the following goals:

  • A new spreadsheet based on the initial spreadsheet (i.e. keep the old spreadsheet).
  • Some of the columns of information (e.g. Name, City, State).
  • Sorted by State then City then Last Name.
  • For some of the rows (e.g. individuals from IA, MN).
  • Formatted for Printing.

How do you get from here to there? You know what you have. You know what you want. Now you “just” need to manipulate the spreadsheet to get the desired results. The remaining steps will guide you through that process.

Save the File to a New Name

By saving the file now, you will keep an original version of the data in case you need to start over. Also, the file would be available if you want to start with the same data, but want a different report in the end.

  1. Select Office Button > Save As > Excel Workbook.
  2. Choose the location for the file.
  3. Choose a name for the file and enter this in the “File name” box.

Manipulate Columns

The spreadsheet that you are starting with may have more columns than you want displayed on your report. It could be important information, just information that is not necessary for this report.

  • You can Hide the columns that are not wanted right now (or Unhide):
    1. Click on the letter of a column to hide.
    2. Right-click then select Hide (or Home > Cells > Format > Hide & Unhide > Hide Columns).
    3. To unhide a column, select the columns on either side of the hidden column, then right-click and select Unhide.
  • You could instead Delete the columns that are not wanted at all:
    1. Click on the letter of a column to remove.
    2. Right-click then select Delete (or Home > Cells > Delete > Delete Sheet Columns).
    3. To undelete a column if it was the last action taken, select the Undo icon in the Quick Access Toolbar, or use the shortcut Ctrl + Z.
  • You could also choose to add information that was not part of the original spreadsheet. To Insert new columns:
    1. Click on the letter of a column to the right of the new column.
    2. Right-click then select Insert (or Home > Cells > Insert > Insert Sheet Columns).
    3. Enter the appropriate information into the new column.

Sort the Spreadsheet

Spreadsheets can be sorted by any column, ascending or descending. If the entire spreadsheet is to be sorted, all of the cells must first be selected. Otherwise, the highlighted data will be sorted and the non-highlighted data will remain as it was which might not be what you want. To sort the entire spreadsheet,

  1. Select the entire spreadsheet by clicking on the top left cell.
  2. Select Data > Sort.
  3. Specify whether or not the spreadsheet contains a header row.
  4. Identify the sort (State then City then Last Name).
  5. Indicate whether the sort items are ascending or descending.

Manipulate and Filter Rows

There are various ways in which to alter which rows of data are displayed. Each method has a different purpose.

  • You can Hide the rows that are not wanted right now (or Unhide):
    1. Click on the number of a row to hide.
    2. Right-click then select Hide (or Home > Cells > Format > Hide & Unhide > Hide Rows).
    3. To unhide a row, select the rows on either side of the hidden row, then right-click and select Unhide.
  • You could instead Delete the rows that are not wanted at all:
    1. Click on the number of a row to remove.
    2. Right-click then select Delete (or Home > Cells > Delete > Delete Sheet Rows).
    3. To undelete a row if it was the last action taken, select the Undo icon in the Quick Access Toolbar, or use the shortcut Ctrl + Z.
  • You could also choose to add information that was not part of the original spreadsheet. To Insert new rows:
    1. Click on the number of a row to the right of the new row.
    2. Right-click then select Insert (or Home > Cells > Insert > Insert Sheet Rows).
    3. Enter the appropriate information into the new row.
  • You could also Filter out the rows that are not wanted for this report, but may be for the next report:
    1. Select Data > Filter > AutoFilter.
    2. Click on the dropdown for one of the columns (e.g. State) and select a particular state (e.g. IA). Notice the rows that remain all have state of IA.
    3. Click on the dropdown for another one of the columns (e.g. City) and select a particular city (e.g. Cedar Rapids). Notice the rows that remain all have state of IA and city of Cedar Rapids.
    4. Click on the dropdown and select All to reset the filter.
    5. Other options include Top 10, Custom. For more advance filtering, see Data > Filter > Advanced.

Format the Spreadsheet for Printing

Once you have all of the data that you want displayed, and none of the data that you do not want displayed, you are ready to format your report for printing.

  1. First of all, select Office Button > Print > Print Preview (and then scroll) to see how it will print without you making any changes. As you are reviewing the Print Preview screen, consider what you would like to adjust and then alter accordingly. Some common adjustments include the following:
  2. Page Layout
    • Page Setup
    • Orientation: Landscape (default is Portrait)
    • Paper size: 8.5 × 14” Legal (default is 8.5 × 11” Letter)
    • Margins
      • Top and Bottom: .75 (default is 1” Top/Bottom)
      • Left and Right: .3 (default is .75” Left/Right)
    • Sheet
      • Print: Gridlines (default is off)
      • Print: Row and column heading (default is off)
  3. Insert
    • Header & Footer
      • Header: Filename.xls (default is blank)
      • Footer: username, Page 1, mm/dd/yyyy (default is blank)

Additional Resources

Review the Help within MS Excel

  1. Press the F1 key, or click the question mark in the upper right corner of the screen.
  2. Enter a keyword and click Search
  3. Review the displayed information

Consult Lynda.com

Lynda.com is a provider of web-based software training videos and resources. If you are interested in access to Lynda.com please contact the Technology Help Desk at [email protected] or 563-387-1000.

Review the Technology Help Desk web site

Contact the Technology Help Desk