Excel: Basic Features and Functions

  • Updated

Microsoft Excel is a valuable tool in organizing and analyzing information. The basic structure of the application consists of a Workbook comprised of individual Worksheets/Spreadsheets, represented by tabs at the bottom of the application.  Worksheets/spreadsheets (the terms are used interchangeably) are comprised of Rows (numbered) and Columns (lettered) containing individual Cells where data is housed. 

Excel_Basic_Structure.png

 

Editing Cells

Selecting Cells

  • To select a single cell, click on the cell.
  • To select a range of cells, click on the first cell in the range and drag the cursor over the range of cells, or click on the first cell, hold the Shift key and click on the last cell in the range.
  • To select multiple nonadjacent cells, hold the Ctrl key and click on each cell you want to select.
  • To select all the cells in a worksheet, click the Select All button in the upper-left corner of the worksheet.

Excel_Select_All.png

Inserting Cells

  1. Select the cell or range of cells where you want to insert the new blank cells.
  2. Under the Home tab, find the Cells group
  3. Click the dropdown arrow on the Insert Cells button 
  4. Select Insert Cells from the resulting menu 
  5. Select the desired Shift Cells option and click OK

NOTE: Follow the same process to insert Rows, Columns, or an entire Sheet

Excel_Insert_Cells.png    Excel_Cell_Placement.png

Editing a Cell

When you double-click a cell it goes into Edit mode.  You can then make changes without overriding all the information currently in the cell. Alternatively, you can select a cell, then click in the Formula Bar to enter information.

Excel_Edit_in_Formula_Bar.png

 

Entering and Managing Data

Entering Data in a Range of Cells

Copying Data

To copy data into a range of cells:

  1. Enter data into a cell
  2. Place cursor on the square at bottom right of active cell until it becomes a "+"
  3. Drag cursor down through desired range of cells to copy data into all cells

Excel_Copy_Cells_Start.png

Excel_Copy_Cells_Finished.png

Data can be copied across columns as well as down rows

  1. Select a cell > place cursor at the bottom right-hand corner of the cell until a “+” appears
  2. Drag to the right over desired cells  

Data Series

To continue a series:

  1. Enter the first two numbers in the series in consecutive cells
  2. Select the two cells; drag square at bottom right through range desired

Excel_Data_Series_Start.png      Excel_Data_Series_Finished.png

Formatting Cells

  • Dollar signs, percent signs, forward slashes, commas, or exponents in a cell will automatically invoke a specific number format to be applied to that cell
  • Numbers longer than the cell width will appear in scientific notation automatically
  • Dates entered in a cell will be converted to the date format that has been applied to the cell.

To format numbers a cell, 

  1. Under the Home tab, click the dropdown arrow next to the Format box in the Numbers group
  2. Select the desired option from the list displayed, or click More Number Formats 
  3. Set options as desired

Excel_Number_Formats.png  Excel_More_Number_Formats.png

Sorting & Filtering Data

Sort: Organizes data by desired characteristics.

  1. Select the row or column to be sorted, navigate to the Data tab, Sort & Filter group, and select Sort.
  2. If related data exists adjacent to your selection, a warning pop-up will appear; select appropriately.

Excel_Sort.png Excel_Expand_selection_popup.png

 Filter: Displays the rows that meet selected criteria and hides rows you do not want to display.

  1. Select the Data to filter
  2. Navigate to the Data tab, Sort & Filter group, and select Filter.
  3. A dropdown arrow appears in the Column header; click to select Filter
  4. Uncheck Select all, select the desired criteria and click OK to apply

NOTE: The same actions may be performed in the Editing group under the Home tab

Excel_Filter.png   Excel_Filter_Options.png

Find & Replace Data

A helpful tool when you need to locate and/or replace a piece of data in your Worksheet, particularly if it appears in multiple locations. 

Find

  1. Under the Home tab, click the Find & Select button in the Editing group
  2. Select Find
  3. In the Find dialog box enter the text you want to find in the Find What field and select Find Next or Find All
  4. Results display at the bottom of the dialog box

Excel_Find___Select.png  Excel_Find_Results.png

Replace

  1. Selecting Replace also opens the Find and Replace dialog box with the Replace tab selected
  2.  Enter what you want to replace in the Find what box
  3. Enter what you want to replace it with in the Replace with box
  4. Select Replace or Replace All  (Replace All replaces the data everywhere it appears in the Worksheet)
  5. Data is replaced and a confirmation popup appears

Excel_Replace_All_confirmation.png

Rows & Columns

Inserting a Row or Column

  1. Select the row heading below or the column heading to the right of where you want a new row or column to be placed.
  2. Under the Home tab, in the Cells group, click the dropdown arrow on Insert Cells 
  3. Select Insert Sheet Rows or Insert Sheet Columns from the resulting menu. 

NOTE: If you have selected a Row, the Insert Sheet Columns option will not be active, and vice versa. 

Excel_Insert_Column.png

Adjusting Row Height or Column Width

  1. Select the row or column you want to adjust.
  2. Under the Home tab do any of the following:
    • To adjust height or width with the mouse, place the cursor over the boundary line of the row or column heading until it shows as a double-sided arrow. Click and drag the boundary to adjust the row or column.
    • To adjust the height or width to fit cell data, click the Format button in the Cells group and select AutoFit Row Height or AutoFit Column Width from resulting menu.
    • To change the default column width for a worksheet, click on the sheet tab for the worksheet. Click the Format button in the Cells group and select Default Width from the resulting menu.  Enter a value in the Standard column width box and click the OK

Excel_Column_Width.png       Excel_Format_Row_and_Column.png

Deleting a Row or Column

  1. Select the row (s) or column(s) to be deleted 
  2. Under the Home tab, click the dropdown arrow on the Delete Cells button in the Cells group and select Delete Sheet Rows or Delete Sheet Columns from the resulting menu.

NOTE: If you have selected a Row, the Delete Sheet Columns option will not be active, and vice versa.

Excel_Delete_Row.png

 

Managing Multiple Worksheets

Inserting a Worksheet

  • To insert a new Worksheet at the end of existing Worksheets, click the Insert Worksheet button (+) to the right of existing Worksheet tabs.

Excel_New_Sheet.png

  • To insert a new Worksheet before an existing Worksheet, click the dropdown arrow on the Insert button in the Cells group; select Insert Sheet from the resulting menu.

Excel_Insert_New_Sheet.png

  • To insert a new Worksheet based on a template, right-click a Worksheet tab and select Insert from the shortcut menu.  Select the template you want to use and click the OK button

NOTE: Templates can be difficult to modify/customize to your specific needs.

Excel_Insert_Sheet_Template.png

Renaming a Worksheet

  1. Right click the Worksheet tab 
  2. Select Rename from the shortcut menu.
  3. Enter a name for the Worksheet and press the Enter key.

Excel_Rename_Worksheet.png

Moving a Worksheet

  1. Click the tab for the Worksheet you want to move.
  2. Drag tab to desired location in the Workbook

Excel_Move_Worksheet.png  Excel_Worksheet_Moved.png

Copying a Worksheet

  1. Right click on Worksheet tab
  2. Select Move or Copy
  3. Select copy destination 
  4. Check box for Create a Copy
  5. Click OK

Excel_move_or_Copy.png  Excel_Copy_Sheet.png

Deleting a Worksheet

  1. Select the Worksheet you want to delete.
  2. Under the Home tab, click the dropdown arrow on the Delete button in the Cells group.
  3. Select Delete Sheet from the resulting menu.
  4. If the Worksheet contains data, click the Delete button to confirm deletion.

Excel_Delete_Sheet.png  Excel_Permanently_Delete_sheet.png

Viewing a Worksheet

Freeze Panes

For easier viewing of large data sheets, you can “freeze” horizontal or vertical panes to keep Row and Column labels or other data visible as you scroll through the worksheet.

  1. Select the View tab
  2. Do one of the following:
    • To freeze Rows, select the Row below where you want the split/freeze to appear.
    • To freeze Columns, select the Column to the right of where you want the split/freeze to appear.
    • To freeze both Rows and Columns, select the cell below and to the right of where you want the split/freeze to appear.
  3. Click the Freeze Panes button in the Window group 
  4. Select Freeze Panes from the resulting menu
  5. To Freeze view without selecting a Row or Column, select Freeze Top Row or Freeze First Column from the options

Excel_Freeze_Panes.png