Understand Ranges in Microsoft Excel

Two Types of Range

There are two types of ranges in Excel -- contiguous and noncontiguous. These are described below:

Contiguous Range -- A contiguous range is a simple connected rectangular group of one or more cells.

Noncontiguous Range -- A noncontiguous range consists of two or more non-connected contiguous ranges.

Range Addresses

Single Cell Range Address-- A single cell range address consists of a column address followed by a row address. For example, the range address D16 means the cell at column D and row 16.

Multi-Cell Range Address-- A multi cell range consists of the starting corner of a range, followed by a colon, and then the ending corner of a range. It typically defines a rectangle in multiple rows and multiple columns, but it could be just a portion of a row or a portion of a column. See the examples below:

A1:C3 is an example of a typical multi-row multi-column range; it defines a 3 x 3 connected group of cells that extends from cell A1 to cell C3 inclusive.

A1:J1 is an example of a single-row multi-column range; it defines a 10 x 1 connected group of cells that extends from cell A1 to cell J1 inclusive.

A1:A10 is an example of a typical multi-row single-column range; it defines a 1 x 10 connected group of cells that extends from cell A1 to cell A10 inclusive.

Range Address Including Sheet -- Both single cell and multi-cell range addresses can contain a sheet name. If the sheet name is not included, the range is assumed to refer to the current sheet. Examples follow:

Sheet3!D16is an example of a single cell range that is located on worksheet 3.

Sheet3!A1:C3 is an example of a multi-row multi-column range that is located on worksheet 3.

Noncontiguous Range Addresses -- Noncontiguous range addresses consist of a comma separated group of range addresses. An example follows:

Sheet1!A1:C3, Sheet2!A1:C3, Sheet3!A1:C3 is an example of a noncontiguous range address that consists of the cells in the range A1:C3 on each of Sheet1, Sheet2, and Sheet3.

Selecting a Range

There are several ways to select a range. Descriptions follow:

-- Click on a cell, hold the left mouse button down, and drag to select a range.
-- Select a cell, press [F8], and use the arrow keys to select the range.

Selecting a Noncontiguous Range

To select a noncontiguous range, select the first part of the range with the mouse, then hold down the[Ctrl] key and select the second group of cells in the range. Repeat until you have selected all portions of the range.

Summary

This article defined the different types of ranges in Excel, gave some examples, and described basic methods for selecting ranges.

Customize the Layout - XIII

Split a Worksheet
You can split a worksheet into multiple resizable panes for easier viewing of parts of a worksheet. To split a worksheet:

• Select any cell in center of the worksheet you want to split
• Click the Split button on the View tab
• Notice the split in the screen, you can manipulate each part separately

Page Properties and Printing - XII

Set Print Titles
The print titles function allows you to repeat the column and row headings at the beginning of each new page to make reading a multiple page sheet easier to read when printed.  To Print Titles:

• Click the Page Layout tab on the Ribbon
• Click the Print Titles button
• In the Print Titles section, click the box to select the rows/columns to be repeated
• Select the row or column
• Click the Select Row/Column Button
• Click OK

Developing a Workbook - XI

Format Worksheet TabYou can rename a worksheet or change the color of the tabs to meet your needs.
To rename a worksheet:

• Open the sheet to be renamed
• Click the Format button on the Home tab
• Click Rename sheet
• Type in a new name
• Press Enter

Formatting a Worksheet - XI

Convert Text to Columns
Sometimes you will want to split data in one cell into two or more cells.  You can do this easily by utilizing the Convert Text to Columns Wizard.

• Highlight the column in which you wish to split the data
• Click the Text to Columns button on the Data tab
• Click Delimited if you have a comma or tab separating the data, or click fixed widths to set the data separation at a specific size. 

Charts - X

Charts allow you to present information contained in the worksheet in a graphic format. Excel offers many types of charts including: Column, Line, Pie, Bar, Area, Scatter and more.  To view the charts available click the Insert Tab on the Ribbon.

Create a Chart
To create a chart:

• Select the cells that contain the data you want to use in the chart
• Click the Insert tab on the Ribbon
• Click the type of Chart you want to create

Graphics - IX

Adding a Picture
To add a picture:

• Click the Insert tab
• Click the Picture button
• Browse to the picture from your files
• Click the name of the picture
• Click Insert
• To move the graphic, click it and drag it to where you want it

Sort and Filter - VIII

Sorting and Filtering allow you to manipulate data in a worksheet based on given set of criteria.

Basic Sorts
To execute a basic descending or ascending sort based on one column:

• Highlight the cells that will be sorted
• Click the Sort & Filter button on the Home tab
• Click the Sort Ascending (A-Z) button or Sort Descending (Z-A) button

Macros - VII

Macros are advanced features that can speed up editing or formatting you may perform often in an Excel worksheet. They record sequences of menu selections that you choose so that a series of actions can be completed in one step.

Recording a Macro
To record a Macro:

• Click the View tab on the Ribbon
• Click Macros
• Click Record Macro
• Enter a name (without spaces)
• Enter a Shortcut Key
• Enter a Description

Performing Calculations - VI

Excel FormulasA formula is a set of mathematical instructions that can be used in Excel to perform calculations.  Formals are started in the formula box with an = sign.

There are many elements to and excel formula.

References:  The cell or range of cells that you want to use in your calculation
Operators:  Symbols (+, -, *, /, etc.) that specify the calculation to be performed
Constants:  Numbers or text values that do not change
Functions:  Predefined formulas in Excel

Modifying a Worksheet - V

Insert Cells, Rows, and Columns
To insert cells, rows, and columns in Excel:

• Place the cursor in the row below where you want the new row, or in the column to the left of where you want the new column
• Click the Insert button on the Cells group of the Home tab
• Click the appropriate choice: Cell, Row,  or Column

 

Delete Cells, Rows and Columns
To delete cells, rows, and columns:

• Place the cursor in the cell, row, or column that you want to delete
• Click the Delete button on the Cells group of the Home tab
• Click the appropriate choice:  Cell, Row, or Column

Find and Replace 
To find data or find and replace data:

• Click the Find & Select button on the Editing group of the Home tab
• Choose Find or Replace
• Complete the Find What text box
• Click on Options for more search options

Go To Command
The Go To command takes you to a specific cell either by cell reference (the Column Letter and the Row Number) or cell name. 

• Click the Find & Select button on the Editing group of the Home tab
• Click Go To

Spell Check
To check the spelling:

• On the Review tab click the Spelling button

Manipulating Data - IV

Excel allows you to move, copy, and paste cells and cell content through cutting and pasting and copying and pasting.

Select Data
To select a cell or data to be copied or cut:

• Click the cell

• Click and drag the cursor to select many cells in a range

Select a Row or Column
To select a row or column click on the row or column header.

Copy and Paste
To copy and paste data:

• Select the cell(s) that you wish to copy
• On the Clipboard group of the Home tab, click Copy

• Select the cell(s) where you would like to copy the data
• On the Clipboard group of the Home tab, click Paste

Cut and Paste
To cut and paste data:

• Select the cell(s) that you wish to copy
• On the Clipboard group of the Home tab, click Cut

• Select the cell(s) where you would like to copy the data
• On the Clipboard group of the Home tab, click Paste

Undo and Redo
To undo or redo your most recent actions:

• On the Quick Access Toolbar
• Click Undo or Redo

Auto Fill
The Auto Fill feature fills  cell data or series of data in a worksheet into a selected range of cells. If you want the same data copied into the other cells, you only need to complete one cell.  If you want to have a series of data (for example, days of the week) fill in the first two cells in the series and then use the auto fill feature. To use the Auto Fill feature:

• Click the Fill Handle
• Drag the Fill Handle to complete the cells

Working with a Workbook - III

Create a Workbook
To create a new Workbook:

• Click the Microsoft Office Toolbar
• Click New
• Choose Blank Document

Customize Excel - II

Excel 2007 offers a wide range of customizable options that allow you to make Excel work the best for you.  To access these customizable options:

• Click the Office Button
• Click Excel Options

Getting Started - I

Getting started with Excel 2007 you will notice that there are many similar features to previous versions.  You will also notice that there are many new features that you’ll be able to utilize.  There are three features that you should remember as you work within Excel 2007:  the Microsoft Office Button, the Quick Access Toolbar, and the Ribbon.  The function of these features will be more fully explored below.

Spreadsheets
A spreadsheet is an electronic document that stores various types of data.  There are vertical columns and horizontal rows.  A cell is where the column and row intersect.  A cell can contain data and can be used in calculations of data within the spreadsheet.  An Excel spreadsheet can contain workbooks and worksheets.  The workbook is the holder for related worksheets.

Microsoft Office Button
The Microsoft Office Button performs many of the functions that were located in the File menu of older versions of Excel.  This button allows you to create a new workbook, Open an existing workbook, save and save as, print, send, or close.

Ribbon
The ribbon is the panel at the top portion of the document   It has seven tabs:  Home, Insert, Page Layouts, Formulas, Data, Review, and View.  Each tab is divided into groups.  The groups are logical collections of features designed to perform function that you will utilize in developing or editing your Excel spreadsheets. 

Commonly utilized features are displayed on the Ribbon.  To view additional features within each group, click the arrow at the bottom right corner of each group.

Home:  Clipboard, Fonts, Alignment, Number, Styles, Cells, Editing
Insert: Tables, Illustrations, Charts, Links, Text
Page Layouts: Themes, Page Setup, Scale to Fit, Sheet Options, Arrange
Formulas: Function Library, Defined Names, Formula Auditing, Calculation
Data:  Get External Data, Connections, Sort & Filter, Data Tools, Outline
Review:  Proofing, Comments, Changes
View: Workbook Views, Show/Hide, Zoom, Window, Macros

Quick Access Toolbar
The quick access toolbar is a customizable toolbar that contains commands that you may want to use.  You can place the quick access toolbar above or below the ribbon.  To change the location of the quick access toolbar, click on the arrow at the end of the toolbar and click Show Below the Ribbon.

You can also add items to the quick access toolbar.  Right click on any item in the Office Button or the Ribbon and click Add to Quick Access Toolbar and a shortcut will be added.

Mini Toolbar
A new feature in Office 2007 is the Mini Toolbar.  This is a floating toolbar that is displayed when you select text or right-click text.  It displays common formatting tools, such as Bold, Italics, Fonts, Font Size and Font Color.