Ms-Excel 2007

Introduction

Excel is a spreadsheet software in which tabular format of work area can be found. 

usages of Excel:
1. Stores large volume of data in tabular format
2. Manage data (rearrangement, filtering etc)
3. Query and perform calculations
4. Analyse and prepare reports like charts
5. Print data

Versions of Excel: 2007, 2010, 2016, 2019 etc.

Parts of Excel User Interface

  1. Title bar
  2. Quick Access Toolbar
  3. Tabs, Ribbons and Groups
  4. Name box
  5. Formula bar
  6. Sheet Area
  7. Sheet Navigation Buttons
  8. Scroll bars
  9. Status bar

 

The work area in excel is known as worksheet which is grid of horizontal and vertical segments.  Horizontal segments are rows and Vertical segments are columns.  Each intersection point of a row and column is known as cell. 

Each Excel worksheet consists of 16,384 columns and 10,48,576 rows.  Columns are labelled with letters from A to Z, AA to AZ, BA to BZ,…, XFD.  Rows are given numbers from 1 to 1048576.

Default filename of excel is workbook.  It can be saved with extention of .xlsx but also supports .xls format for previous versions of Excel.  

A workbook contains many worksheet but one sheet can be activated to work with.

 

inserting new Worksheet:

Right Click on Sheet Tab–>Insert–>worksheet–>OK

(or)

click on new sheet tab at right side after all sheets

(or)

Home tab–>cells group–>insert button–>insert sheet

 

Deleting a worksheet:

Right Click on Sheet Tab–>Delete

 

Renaming Worksheet:

right click on sheetTab–>Rename–>Type the name

(or)

double click on sheetTab–>Type the name

 

Moving between worksheets:

ctrl+pageup/pagedown

(or)

click on the sheet tab

(or)

use navigation buttons beside sheet tabs

Data Entry

The cell which is a rectangular box formed by the intersection of row and column can be named with the names of it’s column and row.

Example: Cell name A5 means Cell in Column A and Row 5.

 

The name of cell is known as Address or name of cell.  One cell can be activated to accept data which is known as active cell.  Active cell has thick border than others.

 

The name of active cell is displayed in the name box.

 

Activate any cell:

Click inside the cell

(or)

type name of cell in name box–>press enter

 

Entering Data into Cells:

  1. Select the cell
  2. Type required data
  3. press enter/tab/arrows to accept data
  4. press Esc to reject data

 

Editing contents of cell:

Double click on cell

(or)

activate cell–>Click in formula bar

(or)

activate cell–>press F2

Move & Select in Worksheet

Moving around worksheet or between cells:

Use tab/Enter/arrow keys to move to one adjacent cell.

Ctrl+arrows to move to end of data contained cells

ctrl+home to activate first non-blank cell

ctrl+End to activate last non-blank cell

 

selecting range of cells:

A group of adjacent cells are called as Range. 

The range of cells is denoted by the firstcellof range:last cell of range.

Eg: A3:H15

 

select non adjacent cells:

  • Hold shift, use arrows to select adjacent cells one by one
  • hold ctrl+shift, use arrow keys to select entire range of cells containing data.
  • type address of range in name box; press enter
  • select any cell, press ctrl+A

 

select non adjacent cells: hold ctrl, click on each cell

 

deselect selection:

click any other cell in the worksheet (or) press any arrow key

 

select entire row:

click on row number (or) press shift+space

 

select entire column:

click on column label (or) press ctrl+space

 

select entire sheet:

ctrl+A (being in a blank cell)

(or)

Click on triangle icon at top left corner of the worksheet

Adjusting Worksheet

change width of column:

place mouse pointer at right edge of column label which is to be resized;

drag upto required size;

double click to fit to largest content cell.

(or)

Home tab –> cells group –> format –> columnwidth/Autofit column width

change height of row:

place mouse pointer at lower edge of row number which is to be resized; drag upto required size; double click to fit to largest content cell.

(or)

Home tab –> cells group –> format–>row height/Autofit row height

 

We can insert or delete Cells, rows and columns in a worksheet but total number of columns and rows doesn’t change. While the insertion or deletion the data in the existing cells will be adjusted into adjacent blank cells.

 

Insert/delete cells/Rows/Columns

  1. right click on cell where new cell to be inserted –> click Insert/delete
  2. In Insert/delete dialog select
  1. Shift cells right/left to adjust the cells
  2. Shift cells down/up to adjust above/below cell
  3. Entire row to adjust entire row to inserted/deleted place
  4. Entire column to adjust entire column

(or)

Home tab –> cells groups –> insert/delete button

Text Formatting

Font formatting:

Home tab–>Font group

Change background color for cells:

Home tab–>Font group

Change border for cells:

Home tab –> Font group –> borders button

The grid lines that are displaying in the worksheet are only for working purpose.  These lines won’t get printed by default. 

hide/view gridlines in worksheet:

View tab –> show/hide group –> gridlines

(or)

Pay Layout Tab  –> sheet options group –> Gridlines –> view

 set gridlines to print:

Pay Layout Tab –> sheet options group –> Gridlines –> print

Alignment and indentation will be applied with respect to the cell.  Data in the cell can be aligned either horizontally or vertically.

Set Alignment

Home Tab –> Alignment Group

Changing direction of data in cell:

Home Tab –> Alignment Group –> Orientation Button

Display Text into Multiple Lines:

Home Tab –> Alignment Group –>Wrap Text button

Display more than one cell as single cell:

Home Tab –>Alignment Group –> Merge &Center Button

Numeric Data Formatting

Data in the Excel cell can be text, number, formula or a function.

Change data format of Numeric data:

right click –> Format cells  –> number tab

(or)

Home tab –> number group

(or)

ctrl+1 –> number tab

Move or Copy Data in Cell

Autofill is the feature of Excel that can be used to fill a series of data continuously through rows and columns without typing.  The active cell contains a black dot at lower right corner that is known as Fill Handle. 

Fill handle can also used to copy cell content to another cell.  Fill handle can be used either horizontally or vertically.

Using AutoFill to fill series of numeric data:

  1. Enter two numbers in the series
  2. Select both cells
  3. Place mouse pointer on Fill handle of active cell
  4. drag to fill remaining numbers in the series.

 

Using Autofill to fill Custom List:

  1. Office button — >excel options –> popular –> Edit custom lists
  2. Click in List Entries box
  3. Enter each custom list entry followed by enter
  4. click add the created custom list to Excel –> click OK
  5. type any one entry of your list in a cell
  6. use fill handle to fill remaining entries

Functions

A Function is a predefined formula whose definition is already known to Excel.  We have to just select the desired function and supply arguments or values to it.  Excel can do all the required arithmetic operations to complete required calculation and gives out the result.

syntax of function

=funcName(arg1,arg2,…)

arguments can be numbers, cell addresses or cell range.

Each argument is separated by a comma and total list of arguments is enclosed within parentheses. 

Each function has a syntax that specifies the number and order of its arguments. 

Example of Fuction:

=SUM(a1,c4)

=SUM(3,5)

=SUM(C4:C9)

Functions Library

There are 15 groups of functions in Excel those are useful for different purposes.

Formulas Tab–>Function Library Group

Entering Function:

  1. Formulas Tab –> Functions Library Group
  2. Select desired function Name from the Function Groups

(or)

  1. Click on Insert Function Button beside formula bar
  2. Insert Function Dialog Appears
  3. Enter the description of function you want in “Search for a function” Text box (Or) Select a category of function from Category drop down list
  4. Then select your required function from the list box below

(or)

Directly type the function along with its arguments followed by equal to sign

Some of the Commonly used important functions :

SUM: returns the sum of values in the specified range of cells. 

Syntax: =SUM(list of arguments)

using Autosum:

  1. select range of cells including a blank cell in which total to be appear.
  2. click on Autosum button on standard toolbar (or) press alt+=

AVERAGE: returns the average of values in the specified range of cells.

Syntax: =AVERAGE(argument list)

MAX: returns the maximum value in the specified range of cells.  It only considers numeric data.

Syntax: =MAX(arg list)

MAXA: returns the maximum value in the specified range of cells.  It considers text values as ‘0’.

MIN: returns the minimum value in the specified range of cells.  It only considers numeric data.

MINA: returns the minimum value in the specified range of cells.  It considers text values as ‘0’.

COUNT: returns the number of cells containing numeric data.

COUNTA: returns the number of cells containing data (both numeric and text).

IF: It takes 3 arguments.  Checks the condition(1st argument) and returns 2nd argument if condition is true and returns 3rd argument  if condition is false.

Syntax: =IF(condition,Value_if_True,value_if_false)

Eg:

=IF(G2>250,”PASS”,”FAIL”)

=IF(AND(B2>35,C2>35,D2>35),”PASS”,”FAIL”)

RANK: returns the size of value relative to other values in the list.

Syntax: =RANK(number,ref,order)

where number is the values to be compared

            ref is the range of values in which number is a member

            order can have values either ‘0’ (descending) or ‘1’ (ascending)

 

Unique ranking when having duplicate values (if the data is existed from A1 to A7)

=RANK(A1,$A$1:$A$7,0)+COUNTIF($A$1:A1,A1)-1

Sorting

Sorting the data:

  1. select the data to be sorted (or) select a cell within the data
  2. Data tab–>Sort & filter tab–>sort button/AtoZ/ZtoA button
    1. Sort by: Select name of the column by which sorting is to be done
    2. Ascending/Descending order to be selected
    3. Then by: Select name of the column by which further sorting is to be done
    4. My dataRange has: Select Header Row to skip first row from sorting

Filtering

Filtering data:

  1. select the data to be sorted (or) select a cell within the data
    1. Data tab–>Sort & filter tab–>filter button
    2. Drop down arrow marks appear at the top of each column
    3. Click on drop down arrow to select the criteria by which the data to be filtered
    4. Removing Filter: Data tab–>Sort & filter tab–>filter button

Charts

Chart is a graphical representation of data stored in excel sheet.  It is always easy for us to read, understand and analyse the data when the data is presented in graphical manner than text and numbers.  This is true since our minds fond of colors and symbols rather than text and numbers.

Excel 2007 allows us to create various types of charts based on the data selected.  The creation, modification of charts is very easy in Excel.

The various types of charts available in Excel 2007 are:

  1. Column chart
  2. Line chart
  3. Pie chart
  4. Bar Chart
  5. Area Chart
  6. Scatter Chart

Representing Data as Chart (or) Creating a Chart:

  1. Select a cell or the range of cells for which chart is to be prepared.
  2. Insert tab–>Chart group–>Click on your desired Chart Model button
    (or)
    Alt+F1 (default Chart model i.e. column Chart will appear)

Insert chart in the same sheet: alt+F1

Insert Chart as separate sheet: F11

Once chart is inserted anywhere (either in the same sheet or separate sheet) and selected then three additional contextual tabs will be appear Design, Layout and Format.

Design Tab provides options to change the chart model, chart data and chart layout

Layout Tab provides options to add, modify or remove various chart elements or components

Format Tab provides options to format different Chart Elements.

Chart Elements are:

Chart Title

Chart Area

Plot Area

Axis Title

Legend

Grid Lines

Page Setup

Since the data in excel is stored in large volume, the total data might not required get printed always. The main focus of Excel is to store and manage data. Even though, at the time of printing we should determine the page dimensions and determine the data to be printed from the sheet.

Setting Page Setup:
Page layout tab–>Page setup group

Page button:
Scaling: using this we can reduce or enlarge the printable area to fit into specified page

Margins button:
Specify margins for the page and header and footer
Specify horizontal and vertical centering of data
Print Titles: Specify rows or columns to be repeatedly printed in each page

Setting/clearing printing area:
Page layout tab–>Page setup group–>Print Area

Printing worksheet/workbook:
office button–>print–>print

Options in Print Dialog box:
1. Printer: Select the name of the printer
2. Print Range: Select the number of pages to be printed
a. Copies: Specify the number of copies of each page
b. Print What:
c. Selection: prints only selected part of worksheet
3. Entire workbook: prints entire workbook
4. Active Sheet: Prints selected sheet or sheets

Click preview button to check how the sheet is going to be printed with your selected options

Changing view of Workbook:
view tab–>workbook views group–>Pagebreak preview/page layout

Displaying headings with all records(Freeze Panes):
view tab–>window group–>Freeze panes button

Printing Heading in every page:
Page layout tab–>sheet options group–>check print checkbox
(or)
click on right arrow of sheet options group of page layout tab–>specify rows to repeat at top in the dialog

You cannot copy content of this page