Excel Basics for Libraries' Applications

Table of Contents

Familiarize Yourself with the Excel Display Action Icons Formatting Icons Create a New Worksheet Format Columns/Rows Format Data in Cells Add Data to Cells Update Data Create Formulas Sorting Printing Appendix A: Practice Exercises Attachment 1: Sample Spreadsheet for Recording Student Hours/Wages

Introduction

Excel is a spreadsheet program that can be used for managing budgets, monitoring statistics over time, or a variety of other purposes for which you have one or more columns of data that you wish to have sorted in different ways based on the data held in various columns. Among the uses that have been made in the Libraries are monitoring student hours/wages use, Onsearch Reports for missing items, serial cancellation project, identification of items to be sent to STX, statistical reports for a variety of purposes, as well as simple alphabetization of a list such as osu.edu e-mail addresses or simple calculations involving numbers that are too big to be handled by handheld calculators. The following is an attempt to provide information that will allow you to make use of Excel's basic capabilities.

Familiarize Yourself with the Excel Display

Figure 1

The spreadsheet is divided into thousands of cells, each of which has a unique address consisting of a letter identifying the column (Column Designator) and a number identifying the row (Row Designator). The cell(s) that is/are active will be outlined, and the identification of the single cell, or the cell in the upper left hand corner of a block of cells, will be identified in the window on the left side of the display immediately below the toolbar. The contents of that cell will be reflected in the Formula/Edit Bar located in the right of the display under the toolbar. These attributes are identified with blue labels on the left side of Figure 1.

The Toolbar contains many icons that are the same as are found in other software with which you are familiar; the ones that are specific to Excel, and which will be used most often, are identified and described below:

• Action Icons (red labels in the middle of Figure 1)
  • AutoSum--The software will automatically calculate the sum of the values in a series of contiguous cells in a single column or row.
  • Sort Ascending--The software will automatically sort the data in ascending order in a single highlighted column or in the leftmost column of a group of highlighted columns.
    NOTE: If you have more than a single column of data that needs to be kept together, you must highlight the information in all of the attached columns, or the software will merely sort the column you have indicated and leave the remaining data in its original order.
  • Chart Wizard--If you wish to make a chart from your data, the Chart Wizard will lead you through the required steps.
  • Help--If you have questions or need assistance, the help information that is available is quite extensive.

• Formatting Icons (green labels on the right of Figure 1)
  • Merge and Center--If you have a major heading that should be centered over multiple columns, you highlight the appropriate cells and click on this icon and the cells will be merged into a single cell and the heading will be centered.
    NOTE: To highlight a group of cells, click on a cell that is on the edge or corner of the group, hold down the left mouse button, and drag the mouse across the group of cells.
  • Currency--If the data you are entering should be reflected as currency, the digits can be entered and then formatted automatically by clicking on this icon. This formatting can be applied to a single cell, a group of cells, or an entire column or row.
  • Percentage--If the data you are entering should be reflected as percentages, the digits can be entered and then formatted automatically by clicking on this icon. This formatting can be applied to a single cell, a group of cells, or an entire column or row.
  • Decrease/Increase Indent--The indention of data within a cell can be decreased or increased by clicking on the appropriate icon.
  • Borders--Place borders around cells, between columns, etc.

Create a New Worksheet

When you open a new workbook in Excel all the cells are of a uniform size and shape, but they can be modified to meet the needs of the data you wish to incorporate. Figure 2 shows basic data that has been input before any reformatting. NOTE: Data that has been entered in a cell that has empty cells following it will display in full (cell B1), but if there is data in the immediately following cell, the amount shown will be truncated to the cell width (cells A4-A9). The following steps will provide the necessary information to transform that data into the more useful form as seen in Figure 3.

Figure 2		Figure 3

1. Format Columns/Rows
   1. Modify Column Width
      
      1. Single Column
         1. Using the mouse
            1. Move the cursor over the Column Designator of the column you wish to change.
            2. Move the cursor to the right edge of the column until a double pointed arrow appears.
            3. Hold down the left mouse button and the current width of the column will be displayed.
            4. Holding down the left mouse button, make the column wider or narrower, as desired.
            5. Release the mouse button when the desired width is achieved.
               
         2. Using the pulldown menu
            1. Highlight the column you wish to make wider by clicking on the Column Designator.
            2. Select Column from the Format pulldown menu.
            3. Select Width.
            4. Type in the desired width.
               NOTE: The width is identified as the average number of digits that will fit in a cell.
         NOTE: After data is input, you can optimize the column width by 1) highlighting the column (click on the Column Designator), clicking on Format/Column and selecting AutoFit Selection or 2) positioning the cursor on the line separating the Column Designators for the desired column and the one immediately to the right until a double-pointed arrow appears and then double-clicking with the left mouse button.

      2. Multiple Columns
         1. Highlight all adjacent columns that you wish to be the same width.
         2. In the Format pulldown menu, select Column/Width.
         3. The Column Width Dialog Box will display the current width.
         4. Enter a new width and click on OK.
   2. Add Column(s)
      1. Highlight the column(s) immediately to the right of the location where you want to add a column(s) (click on the Column Designator(s)).
      2. Select Columns from the Insert pulldown menu.
           OR
         Right click on the highlighted column(s) and select Insert from the resulting menu..
         NOTE:The column(s) that is/are inserted will be the same width as the column to the left of the highlighted column(s).
   3. Delete Column(s)
      1. Highlight the column(s) you wish to delete (click on the Column Designator(s)).
      2. Select Delete from the Edit pulldown menu.
           OR
         Right click on the highlighted column(s) and select Delete from the resulting menu.
         NOTE: Do not use the Delete key, as this will only delete the contents of the cells, not the entire column and its contents.

   4. Modify Row Height
      
      1. Single Row
         1. Move the cursor over the Row Designator of the row you wish to change.
         2. Move the cursor to the bottom edge of the row until a double pointed arrow appears.
         3. Hold down the left mouse button and the current height of the row will be displayed.
         4. Holding down the left mouse button, make the row taller or shorter, as desired.
         5. Release the mouse button when the desired height is achieved.
            NOTE: If you wish to add more data to a cell than can be accommodated by the column width, you have two options: 1) if you want the data to wrap within the cell, go to Format Cells/Alignment/Wrap Text or 2) if you want specific line breaks, insert Alt/Enter at the end of each line. The Row will automatically expand to the needed height when you exit the cell.
      2. Multiple Rows
         1. Highlight all adjacent rows that you wish to be the same height.
         2. In the Format pulldown menu, select Row/Height.
         3. The Row Height Dialog Box will display the current height.
         4. Enter a new height and click on OK.
   5. Add Row(s)
      1. Highlight the row(s) immediately below the location where you want to add row(s) (click on the Row Designator(s)).
      2. Select Rows from the Insert pulldown menu.
           OR
         Right click on the highlighted row(s) and select Insert from the resulting menu.
   6. Delete Row(s)
      1. Highlight the row(s) you wish to delete (click on the Row Designator(s)).
      2. Select Delete from the Edit pulldown menu.
           OR
         Right click on the highlighted row(s) and select Delete from the resulting menu.
         NOTE: Do not use the Delete key, as this will only delete the contents of the cells, not the entire row and its contents.

2. Format Data in Cells
   1. Highlight the cell, range of cells, row, or column that is to be formatted.
      NOTES: To highlight a range of cells, click on the first cell, hold down the left mouse button, and drag through the range of cells.
      An entire row or column can be highlighted by clicking on the Row or Column Designator.
   2. Open the Format Cells dialog box by one of the following:
      1. Select Cells from the Format pulldown menu.
      2. Right click within the highlighted area and select Format Cells.
   3. Assign appropriate format parameters.
      1. Select a Number format as appropriate.
         NOTE: Currency or Percentage formats can be chosen via icons on the Toolbar.
      2. Select the Alignment for data within the cell.
         NOTE: Alignment can also be set via icons on the Toolbar.
      3. Select the Font style/size/color.
         NOTE: Many of these attributes can also be set via icons on the Toolbar.
      4. Place borders around cells or groups of cells.
         NOTE: Borders can also be set via an icon on the Toolbar.
      5. Apply Patterns or shading to cells or groups of cells.

3. Add Data to Cells
   1. New data in a single cell.
      1. Click on the cell to which you wish to add data.
      2. Enter the data.
         1. Alphanumeric data should be typed in exactly as you want it to appear.
         2. Numbers should be typed as digits only, and the format you have chosen will add the dollar sign ($), thousands separators (,), and decimal spaces as appropriate.
            NOTE: If you want a number or date to appear exactly as typed rather than being formatted automatically, precede the data with an apostrophe ('). For example, if you want the fraction "1/4" to show in a cell, you will have to enter it as "'1/4"; otherwise the software will treat it as a date and it will appear in the default date format, e.g., 4-Jan.
      3. To save the data in a cell and move to another one, do one of the following:
         1. Hit Enter and you will move to the next cell down.
         2. Hit the Tab key and you will move to the next cell to the right.
         3. Hit an arrow key in any appropriate direction.
         4. Click on another cell.
      4. If you wish to add more data to a cell than can be accommodated by the column width, you have two options: 1) if you want the data to wrap within the cell, go to Format Cells/Alignment/Wrap Text or 2) if you want specific line breaks, insert Alt/Enter at the end of each line. The Row will automatically expand to the needed height when you exit the cell.
   2. Same data in multiple cells
   1. If you wish to copy a cell or cells:
      1. Highlight the cell(s)to be copied.
         NOTE: To highlight multiple cells, click on a cell that is on the edge or corner of the group, hold down the left mouse button and drag the mouse across the desired cells.
      2. Copy the cell(s) by one of the following:
         • Right clicking on the highlighted cell(s) and selecting Copy from the resulting menu.
         • Clicking on the Copy icon in the Toolbar.
         • Selecting Copy from the Edit pulldown menu.
         • Using the keyboard shortcut Alt C.
      3. Paste the cells by one of the following:
         • Highlighting the destination cell(s).
           NOTE: If you are pasting multiple cells, you only need to highlight the leftmost or top cell for contiguous cells in a single row or column, or the cell in the upper lefthand corner for a block of cells in multiple rows and columns.
         • Paste the cell(s) by:
           • Right clicking on the highlighted cell(s) and selecting Paste from the resulting menu.
           • Clicking on the Paste icon in the Toolbar.
           • Selecting Paste from the Edit pulldown menu.
           • Using the keyboard shortcut Alt V.
      4. If you wish to add the same value to a number of contiguous cells in a column or row:
         1. Put the value in the first cell of the row or column.
         2. Highlight that cell and the remainder that you wish to contain the value.
         3. Select Fill from the Edit pulldown menu.
         4. Select the appropriate direction for the fill to occur.

4. Update Data
   NOTE: If at any time you want to undo an action you have just taken, you can do one of the following:
   • Select Undo... from the Edit pulldown menu.
   • Click on the Undo icon in the Toolbar.
     NOTE:If you click on the arrow at the right side of the icon, you will display a list of the actions that have been taken, and one or all of the most recent actions can be deleted.
   1. Update data in cells.
      1. Highlight the cell you wish to update.
      2. Add, change, or delete data in cells by one of the following:
         1. Position the cursor in the Formula/Edit Bar and make the necessary changes.
         2. Double click on the cell and position the cursor in the cell itself and make the necessary changes.
         3. Click on function key F2 and position the cursor in the cell itself and make the necessary changes.
            NOTE: If you begin typing in a cell without either double clicking on it or using the F2 function key, you will automatically delete the contents that were there to begin with.
      3. If you want to add data to a cell that would extend beyond the column size you want to maintain, you can cause a line break by entering Alt+Enter. The row of cells will automatically resize to fit the data. Single lines of data in adjacent cells will automatically display at the bottom of the cells; if you wish to change the alignment, go to Format/Cells/Alignment.
   2. Delete contents of cell(s)
      1. Highlight the cell(s) containing the data to be deleted.
      2. Hit the Delete key, Backspace key or Spacebar.
   3. Delete the cell and its contents.
      1. Highlight the cell(s) you wish to delete.
      2. Select Delete from the Edit pulldown menu
             OR
         Right click on the highlighted cell(s) and select Delete from the resulting menu.
         NOTE: If you delete a cell, it will cause a resultant shift of the remainder of the cells in that Row or Column.

   Go to Practice Exercise 1.

5. Create Formulas

   Excel can perform simple arithmetic calculations as well as calculations involving complex formulas; for our purposes here, we will only address the more simple calculations. The desired calculations are entered as formulas which may contain actual numbers or, more usually, the identification of the cells containing the values to be used in the calculation. By using the cell identifications, the result of the calculation will be automatically corrected if any of the values contained in the cells are changed. Formulas will also be corrected automatically if the addition of columns or rows causes the Cell Identifiers to change.

   All formulas will begin with an equal sign (=), and will use the following operators: "+" (addition); "-" (substraction); "*" (multiplication); "/" (division). The Cell Identifiers can be added to formulas either by typing them in or by clicking on the appropriate cells and typing in the appropriate operators. The following examples will describe the use of the operators:

   1. Addition
      1. Adding the values in cells A4 and B4 can be accomplished by any of the following formulas:
         1. =A4+B4
         2. =sum(A4,B4)
         3. =sum(A4:B4)
      2. Adding values in contiguous cells can be accomplished by one of the following means:
         1. Cells in a row, e.g., A11 through E11
            • Click on a cell to the immediate right of the row of cells that are to be added, e.g., F11 and do one of the following:
              • Enter the following formula: =sum(A11:E11).
              • Click on the AutoSum icon on the Toolbar.
         2. Cells in a column, e.g., A11 through A14
            • Click on a cell immediately below the column of cells that are to be added, e.g., A15, and do one of the following:
              • Enter the following formuls: =sum(A11:A14).
              • Click on the AutoSum icon on the Toolbar.
         3. Adding values in noncontiguous cells, e.g., B4, C7, and F10 can be accomplished by the following formula: =sum(B4,C7,F10).
   2. Subtraction
      1. To subtract the value in cell A5 from that in cell B5, enter the following formula: =B5-A5.
      2. To subtract the values in cells A14 and C14 from the sum that is housed in F14, enter the following formula: =F14-A14-C14.
   3. Multiplication--To multiply the value in cell A6 by the value in cell B6, enter the following formula: =A6*B6.

   4. Division--To divide the value in cell A7 by the value in cell B7, enter the following formula: =A7/B7.

   NOTE: For further examples and explanations, see Simple Spreadsheet Math for Microsoft Excel.

   Go to Practice Exercise 2.

6. Sorting
   1. If you wish to do an ascending sort on a single column, or the leftmost column of a group of columns:
      1. Highlight the cells in all rows and columns that should be kept together for the sort.
      2. Use the Sort Ascending icon in the Toolbar.
   2. If you wish to do a descending sort, or wish to sort by one or more columns other than the leftmost one:
      1. Highlight the cells in all rows and columns that should be kept together for the sort.
      2. Select Sort from the Data pulldown menu.
      3. Select the column(s) by which you wish to sort, determine the order in which you wish to have them sorted, and identify whether you wish the results to be shown in ascending or descending order.

7. Printing

   When you are ready to print a worksheet, it is always wise to do a Print Preview. When in Print Preview, you can make a number of changes that will improve the appearance of the printed output.

   1. Format of the Page--Unless you indicate otherwise, the worksheet will print in Portrait orientation as it appears on your monitor, minus the Row and Column Designators. The following changes can be made by clicking on the Setup... button at the top of the display:
      1. Page Orientation--If you wish to change to Landscape, select the Page tab in the Page Setup dialog box and click on Landscape.
      2. Center on the Page--Click on the Margins tab and click the appropriate box at the bottom left of the dialog box.
      3. Header/Footer--Click on the Header/Footer tab and enter the information you would like to print at the top or bottom of the page. For example, you may want to put the Title in the Header, or have a computer-generated date carried in a Footer.
         NOTE: This information will not show on the worksheet you see on your monitor; it will only be visible in Print Preview, or on the printed document.
      4. Suppress Gridlines--Click on the Sheet tab, and in the Print section, remove the checkmark in front of Gridlines.
      5. Show Row/Column Designators--Click on the Sheet tab and in the Print section, add a checkmark in front of Row and column headings
   2. Margins--To change the margins, click on the Margins button at the top of the display. The margins can be changed by positioning your cursor over the line until a double-headed arrow appears and then moving it to the desired location. Two margin lines show at the top and the bottom. The lines closest to the center of the page represent the area within which the data from the worksheet will print. The topmost line is the location of any header information you choose to add (e.g., the title of the document), and the bottommost line is the location of any footer information you choose to add (e.g., date).

---

Return to the Training Advisory Committee homepage
URL: http://library.osu.edu/sites/staff/training/excel/excelbasics.php
Send comments or questions to Henry Griffy
Last modified: 4/15/2003