Free Website Builder: Create free websites | Webs



1. Introduction

1. About Microsoft Excel

Microsoft Excel is a spreadsheet program which allows one to enter numerical values or data into the rows or columns of a spreadsheet, and to use these numerical entries for such things as calculations, graphs, and statistical analysis. When you will look at the excel screen you will find the rectangular grid of columns and rows. The columns are named as “A, B, C, D….” and so on and rows are numbered as 1, 2, 3, 4…. and so on. There are 256 columns and 65335 rows in Excel 2003 and 1048576 rows and 16384 columns in Excel 2007. The intersection of rows and columns is called a “Cell”. A Cell is a unit where we can store data in excel and the excel spreadsheet consist of thousands of cells.

2. Excel Window at Startup

3. Excel 2007 : An Outlook

More rows and columns

To enable you to explore massive amounts of data in worksheets, Office Excel 2007 supports up to 1 million rows and 16 thousand columns per worksheet. Specifically, the Office Excel 2007 grid is 1,048,576 rows by 16,384 columns, which provides you with 1,500% more rows and 6,300% more columns than you had available in Microsoft Office Excel 2003.

Office themes and Excel styles

In Office Excel 2007, you can quickly format the data in your worksheet by applying a theme and by using a specific style. Themes can be shared across other 2007 Office release programs, such as Microsoft Office Word and Microsoft Office PowerPoint, while styles are designed to change the format of Excel-specific items, such as Excel tables, charts, PivotTables, shapes, or diagrams.

Easy formula writing

The formula bar automatically resizes to accommodate long, complex formulas, which prevents the formulas from covering other data in your worksheet. With Function AutoComplete, you can quickly write the proper formula syntax. From easily detecting the functions that you want to use to getting help completing the formula arguments, you will be able to get formulas right the first time and every time.

Improved sorting and filtering

In Office Excel 2007, you can quickly arrange your worksheet data to find the answers that you need by using enhanced filtering and sorting. For example, you can now sort data by color and by more than 3 (and up to 64) levels. You can also filter data by color or by dates, display more than 1000 items in the AutoFilter drop-down list, select multiple items to filter, and filter data in PivotTables.

New look for charts

You can use new charting tools to easily create professional-looking charts that communicate information effectively. Based on the theme that is applied to your workbook, the new, up-to-date look for charts includes special effects, such as 3-D, transparency, and soft shadows.

Easy-to-use PivotTables

In Office Excel 2007, PivotTables are much easier to use than in earlier versions of Excel. By using the new PivotTable user interface, the information that you want to view about your data is just a few clicks away—you no longer have to drag data to drop zones that aren't always an easy target. Instead, you can simply select the fields that you want to see in a new PivotTable field list. You can now undo most actions that you take to create or rearrange a PivotTable.

New file formats

In 2007 Microsoft Office system, Microsoft is introducing new file formats for Word, Excel, and PowerPoint, known as the Office Open XML formats. These new file formats facilitate integration with external data sources, and also offer reduced file sizes and improved data recovery. In Office Excel 2007, the default format for an Excel workbook is the Office Excel 2007 XML-based file format (.xlsx).

2.1 Menus

As in Windows, we have many Menus available in Excel. Also the shortcuts to these Menus are available in various Toolbars. The picture below shows the Menus and most used Toolbars at a glance:

[pic]

So we divide this chapter into two parts:

1. Standard Menus

▪ File

▪ Edit

▪ View

▪ Insert

▪ Format

▪ Tools

▪ Data

▪ Windows

▪ Help

2. Most Frequently used Toolbars

▪ Standard Toolbar

▪ Formatting Toolbar

▪ Visual Basic Toolbar (covered later in VBA chapter)

▪ Pivot Toolbar (covered later in Pivot chapter)

Standard Menus

1. File

[pic]

New : Create a new, blank spreadsheet

Open : Open a previously saved spreadsheet

Close : Close the current excel spreadsheet

Save : Save your current spreadsheet

Save As : Save the current spreadsheet with a new name

File Search : Search for any saved spreadsheet

Page Setup : Define the margins and other page preperties

Print Preview : Preview the spreadsheet you want to print

Print : Get print out the spreadsheet

Send To : Send the file through mail this can be used to send current file as

attachments, current worksheet as body of the mail, etc

Properties : Edit the properties of the spreadsheet

Exit : Quit from Excel

2. Edit

[pic]

Undo : Undo the last step

Redo : Redo the step that was set to undo

Cut : Cut the selected cells

Copy : Copy the selected cells

Paste : Paste the cut/ copied cells

Paste Special : Paste the copied cells with some specification

Fill : Fill the adjacent cells with the same value or in the form of series

Clear : Clear the cell

Delete : Delete cells/row/column

Delete Sheet : Delete the current worksheet

Move or Copy Sheet : Move the current sheet to another workbook or the same workbook

Find : Find the particular value in the active worksheet

Replace : Replace the particular value with another in the active worksheet

Go To : Go to particular cell

3. View

[pic]

Normal : View the active worksheet in the Normal view

Toolbars : Hide/Unhide any specific toolbar

Formula Bar : Hide/Unhide the Formula bar

Status Bar : Hide/Unhide Status Bar

Header and Footer : Set Header and Footer for the spreadsheet

Comments : Displays the comments in the worksheet

Custom Views : Allows modifying your view according to need

Full Screen : View the spreadsheet in full screen mode

Zoom : Increase/Decrease the zoom level for the active workbook

4. Insert

[pic]

Cells : Inset cell

Rows : Insert entire row

Columns : Insert entire column

Worksheet : Insert a worksheet

Chart : Insert chart

Symbol : Insert symbol

Page Break : Break the active worksheet into various parts

Function : Insert Function

Name : Give a name to the cell

Comment : Insert comment

Picture : Insert picture (text format/ clip art)

Diagram : Insert specialized diagrams (used for presentations)

Hyperlink : Insert Hyperlink to the selected cell

5. Format

[pic]

Cells : Format the selected cells (Value format, alignment, font, cosmetic

formatting, etc)

Row : Format the selected rows (Height of the row, hide/unhide)

Column : Format the selected columns (Width of the column, hide/unhide)

Sheet : Format the active worksheet (Rename, hide/unhide sheet, color the

sheet tab)

Auto format : Format the selected cells in predefined format

Conditional Formatting: Format the cells according to specific criteria

Style : Choose the style (Font, border, alignment, etc) for the selected cells

6. Tools

[pic]

Spelling : Check the spelling

Error Checking : Check if any error in the entire worksheet

Share Workbook : Share the entire workbook with other users

Track Changes : Tracks the changes done by other user on shared workbook

Protection : Protect the active worksheet/workbook

Macro: Create/Edit/Use Macro : It is used for recording, writing & running the macro

Add-Ins : Make available the in built functions to use

AutoCorrect Options : Typing mistakes automatically.

Customize : Used to customize tool bar

Options : Make default settings for the workbook

Data Analysis : Use Statistical methods to analyze the data

7. Data

[pic]

Sort : Sort the data in ascending/descending order

Filter : Apply filter to the data

Form : Used for entering data into table

Validation : Used to put a rule for input data for any cell

Table : Used for creating & editing table

Text to Columns : Delimit the text to fit into the cells

PivotTable and PivotChart Report : Make Pivot to analyze the data

Import External Data : Used to import external data to pivot table

Refresh Data : Refreshes the Pivot report

8. Window

[pic]

New Window : Open the same worksheet in a new window

Arrange : Arrange all opened windows (horizontal/vertical etc)

Compare Side by Side with.. : Compare all opened worksheets by viewing them together

Hide : Hide the active workbook

Unhide : Unhide the hidden workbook

Split : Add splits to the active worksheet

Freeze Panes : Freeze the specified row/column

Most Frequently used Toolbars

1. Standard Toolbar

[pic]

1. New : Create a new, blank spreadsheet

2. Open : Open a previously saved spreadsheet

3. Save : Save your current spreadsheet

4. Permission : Used to secure documents

5. Print : Prints the current document.

6. Print Preview : Preview the potential print of the current document.

7. Research : Microsoft has enabled Information Rights Management (IRM) within the

new version of Excel, which can help protect sensitive documents from

being copied or forwarded. Click this for more information and options.

8. Copy : Copies the current selection to the clipboard, which can then be pasted

elsewhere in the document.

9. Paste : Takes the current clipboard contents and inserts them.

10. Undo : Undoes the last action in the document, reverting “back” a step in time.

11. Insert Hyperlink : Inserts a hyperlink to an Internet location.

12. AutoSum : A drop-down menu of available mathematical operations to perform.

13. Sort Ascending : Sorts the current selection in ascending order.

14. Chart Wizard : Opens the “Chart Wizard,” which will walk you through the creation of a

chart/ diagram using the currently selected information.

15. Microsoft Excel Help: Brings up the Excel Help window, which will allow you to type in a

key-word for more information, or click anything on screen to directly

bring up further information on that subject.

16. More Options : There are a variety of extra options you can call or add to the toolbar,

such as Spell Check, Sort Descending, Cut, Redo, etc. By clicking the

triangle, you can access these options; at the same time, you can

drag this toolbar outwards more to make more available space for

these options directly on the toolbar.

2. Formatting Toolbar

[pic]

1. Font : Change the font of the selected cell(s)

2. Size : Change the font size of the selection

3. Bold : Put the selection in bold face

4. Italics : Italicize the selection

5. Underline : Underline the selection

6. Align Left : Align the current selection to the left

7. Center : Align the current selection to the center

8. Align Right : Align the current selection to the right

9. Merge & Center : Combine two selected cells into one new cell that spans the width of

both and center the contents of this new cell

10. Currency Style : Change the style in which currency is displayed

11. Percent Style : Change the style in which percents are displayed

12. Decrease Indent : Decrease the indent of a cell by approximately one character

13. Border : Add or alter the style of borders to format a cell with

14. Fill Color : Select a color to fill the background of a cell with

15. Font Color : Select a color to apply to a selection of text

2.2 Shortcuts

Open File (Cntrl + O)

Allows the user to open another excel workbook. Please note that this option will only work if you have the excel workbook already open

Copy (Cntrl+C)

Allows the user to copy the selected data

Paste (Cntrl+V)

Allows the user to paste the copied data

Cut (Cntrl+X)

Allows the user to cut the selected data. This is nothing but moving the data.

Paste Special Values only (Alt + E+ S+ V)

Allows the user to special paste the copied values. Please note that this won’t work with the data which is being cut. Special paste only works for copied data.

Paste Special Format (Alt + E + S +T)

Allows the user to special paste the formats copied. This is helpful if we have a need to copy a particular applied format from one worksheet/workbook to another worksheet/workbook

Undo (Cntrl+Z)

This is a common and very frequently used function do what changes have been made. The function is limited to certain steps only.

Redo (Cntrl+Y)

This function is used when one has used the function ‘Undo’ and wants to perform the same step again. The function is limited to certain steps only.

Select All (Cntrl+A)

Allows the user to select the worksheet

Save (Cntrl+S)

Allows user to save the excel workbook

Save As (Alt +F +A) OR (F12)

Allows the user to save the file with same or different extension (format) and at selective location

Fill Down (Cntrl+D)

Allows the user to fill the series down. It fills down the formulas or copies the same data

Fill Right (Cntrl+R)

Allows the user to fill the data on right side of the selected cell.

New Workbook (Cntrl+N)

Allows the user to open the new workbook. This will only work if an excel file is already open.

Close Workbook (Alt + F+ C) (Multiple) OR (Alt + F4) (Single)

Allows the user to close the open workbook. Very useful when multiple excel workbooks are open and user needs to close only few of them. For single workbook Alt + F4 will also work. Alt + F4 in case of multiple workbooks will close all the workbooks

New Worksheet (Alt + I + W)

Allows the user to insert the new worksheet in the active workbook

Bold (Cntrl+B)

Formats the selected data to bold

Italics (Cntrl+I)

Formats the selected data to Italics

Underline (Cntrl+U)

Underlines the data for selected cells

Edit Cell (F2)

Just press F2 to edit the contents of the selected cell.

Pivot Wizard (Alt+D+P)

Opens the wizard to prepare the pivot.

Auto Filter (Alt + D+ F + F)

A very important function and most commonly used. This puts the auto filter on selected data. It is advisable to select the whole data and then apply the filter or whole of the data won’t be covered if we have some blank cells

Format Cells Window (Cntrl+1)

The shortcut opens the format window where user can perform various formatting functions

Series Fill (Alt + I + E + S)

This allows user to fill in the linear series (default). For any other type of series fill user may choose accordingly

Find (Cntrl + F)

Allows the user to search for the specific data

Replace (Cntrl + H)

Allows the user to find the specific data and replace it with other data

Print (Cntrl+P)

Allows the user to print the data. This opens the print wizard

Auto Width (Alt + O + C + A)

Again a very frequently used function. This is used to Auto size the columns. This also Un-hides the columns if there are any hidden columns among the selected data

Select Whole Row (Shift + Spacebar)

It allows the user to select the whole Row

Select Whole Row (Cntrl + Spacebar)

It allows the user to select the whole column

Hide Column (Alt + O + C +H) OR (Cntrl + 0)

Allows user to hide the selected columns

Hide Row (Alt + O + R +H) OR (Cntrl + 9)

Allows user to hide the selected Rows

Insert Hyperlink (Cntrl + K)

Allows the user to insert the hyperlink

Strikethrough (Cntrl + 5)

Allows the user to strikethrough the selected cells.

Format to Percent (Cntrl + Shift + 5)

Allows the user to convert the numbers into percentages

Delimit (Alt + D + E)

This is again a very useful function and very frequently used. This allows the user to delimit the data by different delimiters (comma, semi colon, hyphen etc...)

Shift Cells Down (Alt + I + E)

Allows the user to insert the cells. The user can choose to insert the entire row or column as per the requirements

Shift Cells Up (Alt + E + D)

Allows the user to delete the selected cells. The user can choose to delete the entire row or column as per the requirements

Move or Copy Sheet (Alt + E + M)

Allows the user to Move the sheet within a workbook or to a new or existing workbook

Move within Sheets (Cntrl + Page Up) OR (Cntrl + Page Down)

Allows the user to move through the various sheets i.e. Forward or Backward. This is a very useful function to quickly scroll through the sheets.

Delete Sheet (Alt + E + L)

This function can be used to delete the worksheet.

Clear All (Alt + E + A + A)

This function allows the user to clear all the data including the formats. The data can be cleared using delete button but this function in addition removes the formatting as well

Insert Symbol (Alt + I + S)

This allows user to insert the symbol. The user can pick from the specific set of symbols

Insert Row (Alt + I + R)

This allows the user to insert the Row

Insert Column (Alt + I + C)

This allows the user to insert the Column

Hide Sheet (Alt + O + H + H)

Allows the user to hide the active sheet

Unhide Sheet (Alt + O + H + U)

Allows the user to unhide the hidden sheets

Renaming Sheet (Alt + H + O + R)

Allows the user to rename the active sheet

Conditional Formatting (Alt + O + D)

Allows the user to open the wizard for conditional formatting. The user can put the specifications for formatting according to his needs

Remove Gridlines(Alt + T + O + (Alt + G))

Allows user to remove the Gridlines from the active workbook. First the users have to use Alt + T + O to open the wizard and use Alt + G to remove the gridlines.

Insert Current Date (Cntrl + ;(semi colon))

Allows the user to insert the current date in the selected cell

3. Basic Excel Functions

Following chapter will take you through some of the frequently used basic excel functions

SUM

This function creates a total from a list of numbers.

It can be used either horizontally or vertically.

The numbers can be in single cells, ranges are from other functions.

Syntax

=SUM(Range1,Range2,Range3... through to Range30).

Formatting

No special formatting is needed.

[pic]

Count

This function counts the number of numeric entries in a list.

It will ignore blanks, text and errors.

Syntax

=COUNT(Range1,Range2,Range3... through to Range30)

Formatting

No special formatting is needed.

[pic]

CountA

This function counts the number of numeric or text entries in a list.

It will ignore blanks.

Syntax

=COUNTA(Range1,Range2,Range3... through to Range30)

Formatting

No special formatting is needed.

[pic]

Left

This function displays a specified number of characters from the left hand side of a

piece of text.

Syntax

=LEFT(OriginalText,NumberOfCharactersRequired)

Formatting

No special formatting is needed.

[pic]

Mid

This function picks out a piece of text from the middle of a text entry.

The function needs to know at what point it should start, and how many characters to pick.

If the number of characters to pick exceeds what is available, only the available characters

will be picked.

Syntax

=MID(OriginalText,PositionToStartPicking,NumberOfCharactersToPick)

Formatting

No special formatting is needed.

[pic]

RIGHT

This function displays a specified number of characters from the right hand side of a

piece of text.

Syntax

=RIGHT(OriginalText,NumberOfCharactersRequired)

Formatting

No special formatting is needed.

[pic]

LEN

This function counts the number of characters, including spaces and numbers, in a piece of text.

Syntax

=LEN(Text)

Formatting

No Special formatting is needed.

[pic]

MAX

This function picks the highest value from a list of data.

Syntax

=MAX(Range1,Range2,Range3... through to Range30)

Formatting

No special formatting is needed.

[pic]

MIN

This function picks the lowest value from a list of data.

Syntax

=MIN(Range1,Range2,Range3... through to Range30)

Formatting

No special formatting is needed.

[pic]

Average

This function calculates the average from a list of numbers.

If the cell is blank or contains text, the cell will not be used in the average calculation.

If the cell contains zero 0, the cell will be included in the average calculation.

Syntax

=AVERAGE(Range1,Range2,Range3... through to Range30)

Formatting

No special formatting is needed.

[pic]

Median

This function finds the median value of a group of values.

The median is not the average, it is the half way point where half the numbers in the group are

larger than it and half the numbers are less than it.

If there is no exact median number in the group, the two nearest the half way point are

added and their average is used as the median.

Syntax

=MEDIAN(Range1,Range2,Range3... through to Range30)

Formatting

No special formatting is needed.

[pic]

Mode

This function displays the most frequently occurring number in a group of numbers.

For it to work correctly there must be at least two numbers which are the same.

If all the values in the group are unique the function shows the error #N/A.

When there is more than one set of duplicates, the number closest to the beginning

of the group will be used. (Which is not really an accurate answer!)

Syntax

=MODE(Range1,Range2,Range3... through to Range30)

Formatting

No special formatting is needed.

[pic]

Product

This function multiples a group of numbers together.

It is the same as using 2*3*5*10*3*7, which results in 6300.

Syntax

=PRODUCT(Number1,Number2,Number3... through to Number30)

or

=PRODUCT(RangeOfNumbers)

or

=PRODUCT(Number1,Range,Number2...)

Formatting

No special formatting is needed.

[pic]

VLOOKUP

This function scans down the row headings at the side of a table to find a specified item.

When the item is found, it then scans across to pick a cell entry.

Syntax

=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)

The ItemToFind is a single item specified by the user.

The RangeToLookIn is the range of data with the row headings at the left hand side.

The ColumnToPickFrom is how far across the table the function should look to pick from.

The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.

Formatting

No special formatting is needed.

[pic]

Concatenate

This function joins separate pieces of text into one item.

Syntax

=CONCATENATE(Text1,Text2,Text3...Text30)

Up to thirty pieces of text can be joined.

Formatting

No special formatting is needed, the result will be shown as normal text.

[pic]

[pic]

Trim

This function removes unwanted spaces from a piece of text.

The spaces before and after the text will be removed completely.

Multiple spaces within the text will be trimmed to a single space

Syntax

=TRIM(TextToTrim)

Formatting

No special formatting is needed.

[pic]

SUMIF

This function adds the value of items which match criteria set by the user.

Syntax

=SUMIF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal)

=SUMIF(C4:C12,"Brakes",E4:E12)

=SUMIF(E4:E12,">=100")

Formatting

No special formatting is needed.

[pic]

Date

This function creates a real date by using three normal numbers typed into separate cells.

Syntax

=DATE(year,month,day)

Formatting

The result will normally be displayed in the dd/mm/yy format.

By using the Format,Cells,Number,Date command the format can be changed.

[pic]

COUNT BLANK

This function counts the number of blank cells in a range.

Syntax

=COUNTBLANK(RangeToTest)

Formatting

No special formatting is needed.

[pic]

Transpose

This function copies data from a range, and places in it in a new range, turning it so

that the data originally in columns is now in rows, and the data originally in rows

is in columns.

The transpose range must be the same size as the original range.

The function needs to be entered as an array formula.

To enter an array formula you must first highlight all the cells where the formula is required.

Next type the formula, such as =TRANSPOSE(A1:A5).

Finally press Ctrl+Shift+Enter to confirm it.

If changes need to be made to the formula, the entire array has to be highlighted, the edits

can then be made and the Ctrl+Shift+Enter used to confirm it.

Syntax

=TRANSPOSE(Range)

Formatting

No special formatting is needed.

[pic]

FREQUENCY

This function compares a range of data against a list of intervals.

The result shows how many items in the range of data fall between the intervals.

The function is entered in the cells as an array, that is why it is enclosed in { } braces.

Syntax

=FREQUENCY(RangeOfData,ListOfIntervals)

Formatting

No special formatting is needed.

[pic]

UPPER

This function converts all characters in a piece of text to upper case.

Syntax

=UPPER(TextToConvert)

Formatting

No special formatting is needed.

[pic]

FIND

This function looks for a specified letter inside another piece of text.

When the letter is found the position is shown as a number.

If the text contains more than one reference to the letter, the first occurrence is used.

An additional option can be used to start the search at a specific point in the text, thus

enabling the search to find duplicate occurrences of the letter.

If the letter is not found in the text, the result #VALUE is shown.

Syntax

=FIND(LetterToLookFor,TextToLookInside,StartPosition)

LetterToLookFor : This needs to be a single character.

TextToLookInside : This is the piece of text to be searched through.

StartPosition : This is optional, it specifies at which point in the text the search should begin.

Formatting

No special formatting is needed, the result will be shown as a number.

[pic]

SUBSTITUTE

This function replaces a specified piece of text with a different piece of text.

It can either replace all occurrences of the text, or a specific instance.

The function is case sensitive.

Syntax

=SUBSTITUTE(OriginalText,TextToRemove,TextToInsert,InstanceToUse)

The InstanceToUse is optional, if it is omitted all instances will be substituted.

Formatting

No special formatting is needed.

[pic]

4. Formatting In Excel

Introduction

The Formatting function in excel is a very important function. It enables the user to present the data in different format than usual which makes the data easier to look at and read. The basic function of Formatting in excel can be divided into two parts:

• Cell Formatting

• Spread Sheet Formatting

Formatting Tool Bar:

It is advisable to have the formatting tool bar switched on in excel which makes the one-click formatting possible. It saves the time of the user and provides the shortcut for formatting just in a single mouse click.

How to Switch On the Formatting Tool Bar:

To switch on the Formatting Tool Bar, right click on the blank area below your menu bar and select Formatting. This will show/hide your formatting tool bar in excel (Refer the screen shot above to understand it in detail)

Formatting Tab in Menu Bar:

The formatting of cells, rows, columns and sheet can be done by using Format tab in Menu bar. The shortcut for formatting tab is Cntrl+1. Functions like AutoFormat and Style are not used very frequently but are important part of formatting. We will see all of these one by one next in the module.

Cell Formatting:

Cell Formatting can be used by pressing Cntr+1. This will open the new window (Refer screenshot below) which will allow user to use different formatting as per requirement for selected cells.

Format Cells: Number Formatting

The first tab in Format Cells window is “Number”. This tab allows user to format the way one wants to present the numbers. All the ways in which the number can be formatted is shown in the screenshot below but amongst all the most used ones are:

Number:

It allows user to remove or add the decimal places, use comma separator and the way they want to look at the negative numbers. Specify the number of decimal places you want to see in the box near decimal places. If you don’t wish to see any decimal places set this to 0. You can use comma separator to separate the values after every 1000 places in the number. Tick the box in front of “Use 1000 separator (,). Last is the way you want to see your negative numbers. You can select the appropriate option out of the 4 available.

Date:

The next most frequently used function is formatting the dates. Sometimes excel is not able to recognize the dates by itself. If we know that a particular column or row contains dates but we see some strange values (Like left corner of the screen shot below) it just requires the conversion of format to Dates. The column on right hand side of the screen shows the same column after it has been formatted. The user can choose the way he wants to format the date e.g.: dd/mm/yyyy or mm/dd/yyyy etc...

Percentage:

The percentage format allows the user to convert the numbers into percentages. The user can also choose the number of decimal places the similar way it was available in number tab

Format Cells: Alignment

Alignment is the second tab in cell formatting which not only allows the user to align the text but also to control the text. Left or Right Indent, Wrap Text and Merge Cells are the most frequently used functions.

Text Alignment:

Text Alignment allows user the way data in selected cells can be presented. The alignment can be horizontal or vertical. The user can choose left, right, center or justified (most common) as horizontal alignment while Top, Center, Bottom, Justified or Distributed as Vertical Alignment. The spacing of Indent can also be selected by choosing the value in box below Indent (Refer Screenshot below). The shortcuts can also be used for text alignment from Formatting toolbar

Text Control:

In text control the user can choose to wrap the text, Shrink fit or Merge cells. The most common used function out of the three are Wrap text and Merge cells. The shortcut for wrap text is Alt+W while for Merge cells is Alt+M. Wrap text is useful to present the data in a single cell if the user is facing difficult to scroll the screen towards right to read the complete data (Refer Screenshot below) while merge cell combines all the selected cells and convert them into a single big cell (Refer Screenshot below). **The user also have to keep this in mind that if the cells selected have data values, one cannot merge the cells as excel will display only the content of first cell in the merged cell. [pic]

Right-to-Left & Orientation:

The text direction can be changed by using Right-to-Left function. This function allows the way user wants to experience typing way. Left to Right is the standard way for typing.

Orientation will decide the degree in which user would like to see the data. If the user would like to see the selected data slanting downwards or upwards, it can be done by using Orientation.

Format Cells: Font

Deciding the Fonts of data is one of the most important things for presenting the data. Highlighting the fonts by making them bold or changing the type of font attracts the eyes of reader. Thus it makes easy to highlight the important areas.

Font, Font-style, Size and Color:

The user can change the font using this option. The default font set is Arial. The user can use the different font styles like Bold (Cntrl+B), Italics (Cntrl+I) or Underline (Cntrl+U) separately or can apply all of them together as well.

The user can also change the color of the fonts by selecting it from the drop down menu in format cells – font option.

All the above mentioned font related changes can be done by a single click using the formatting toolbar:

Effects & Preview:

The effect in font includes strikethrough, superscript and subscript. The strikethrough will draw a line across words/numbers while superscript and subscript are used to represent the numbers as Indices.

Format Cells: Border

The Border formatting is used to draw borders across selected cells. The borders can be drawn outside, inside or on selected sides be it right, left, up or down. The line style can also be chosen according to user preference and different colors can be also used by selecting from the drop down menu from color tab.

Format Cells: Patterns

Pattern/Cell shading allows the user to use different colors to highlight the cells. Color coding is one of the important aspects of data presentation. Apart from using different colors the user can also use different shading patterns by selecting from the patter tab below the color options. The sample window helps the user to preview the selection. Color formatting can also be used from formatting toolbar by clicking on color tab in the tool bar.

Format Cells: Protection

The protection tab allows user to lock all the cells or selected cells or hide the formulas applied. To activate the function of locking the cells or hiding the formulas it is important for an user to go to Tools – Protection and click protect sheet. Without sheet being protected one cannot lock the cells or hide the formulas.

After the user click protect sheet the following window opens:

In the above mentioned window the user can choose to keep the password (it’s optional) and can also decide the rights to be allowed for other users using the worksheet.

Format Rows and Columns:

We earlier in the module saw that Rows and Columns together make up the excel worksheet. Formatting of Rows and Columns include adjusting the height and width of the columns and hiding and un-hiding. The user can choose to keep the customized height of the row or column while width can be adjusted using the AutoFit function. The rows and columns can be hidden by selecting them and just clicking on to hide rows / hide columns. To unhide them just click on to unhide rows to show the hidden rows and unhide columns to show the hidden columns.

Format Sheet:

The formatting for sheet allows the user to rename the sheet, hide / unhide the sheet, select the background presentation for sheet and selecting the tab color. The user can choose to rename the sheet by selecting the rename option as shown in screenshot below. Please note that excel 2003 and excel 2007 doesn’t accept more than 31 letters as sheet name. The user can click on Hide to hide the sheet and to unhide the sheet after the click the user will have an option to choose from the hidden sheet/s.

The background option allows user to select the customized background for excel sheet. The background image selected will only change the background of active sheet. Clicking on Tab color will allow user to select a color to highlight the sheet from the pop up window having all the colors available in excel. The tab color can also be changed by right clicking on the sheet tab and selecting Tab Color.

AutoFormat:

Clicking the AutoFormat will open a pop up window which will allow the user to select the formatting from number of ready templates from excel. This is a built in function in excel and user can choose to apply any one from 17 different given formats. The user can also select the parts of format to be applied by clicking in “Formats to Apply” tab in AutoFormat pop up window.

Conditional Formatting:

Conditional Formatting is one of the important functions used in excel. Let’s imagine a worksheet full of numbers and you are asked to filter out the specific ones. Searching whole worksheet will take lot of time and effort. Conditional formatting will make it simple. As the name itself suggest Conditional Formatting allows the user to format the numbers (highlight them or shade them) with a condition. For e.g.: search and highlight the numbers whose value is less than 25 or is equal to 50. The user can apply the condition based on cell value or formula applied.

How to Apply Conditional Formatting:

- Choose the basis for your formatting: cell value or formula

- Choose the condition: between, not between, equal to, not equal to, less than, greater than, greater than and equal to or less than and equal to

- Input your range

- Select the format by clicking on Format button. This will open another small window where user will have the option to use different types of formatting. This will be similar to what has already been shown in module before. (Reference: Format Font, Format Border, Format Patterns)

Format Style:

Format style will allow the user to modify the Numbers, Alignment, Font, Border, Patterns and Protection. The user can choose to select the criteria’s he or she would like to include in styling. The style can also be modified by clicking the Modify button. This will open another window where user can modify and customize the style of the worksheet. The important thing to note here is that styling is not limited to specific sheet. This means that the modification will affect all the sheets in the workbook. This option is very useful in situations where a particular format is to be followed across entire workbook.

5. Introduction to CSV

CSV: Comma Delimited File or Comma Separated File

A CSV file is commonly described as a ‘Comma Delimited File’ or a ‘Character Separated File’. The second description is more accurate since any character including the comma, can be used to delineate each piece of data. Since a CSV file is a simple text file (ASCII or Unicode) it can be easily opened by Notepad which is included in all versions of MS Windows. A typical CSV file, where a comma is used to distinguish each record, looks like this in Notepad:

Name1, Address1, Telephone1

Name2, Address2, Telephone2

Name3, Address3, Telephone3

Name4, Address4, Telephone4

It's important that the individual "records" within a CSV file NOT contain commas, as this may break the simple formatting when using the file in another application.

CSV files are often used as a simple way to transfer a large volume of spreadsheet or database information between programs, without worrying about special file types.

Excel escapes these values by embedding the field inside a set of double quotes, generally referred to as text qualifiers, i.e. a single cell with the text apples, carrots, and oranges becomes "apples, carrots, and oranges".

Sometimes due to the limitation of excel (limited number of rows and columns) large data is saved or transferred from the systems in CSV format. Excel 2003 and 2007 supports the CSV formats and can open the CSV files but up to the limit of the number of rows and columns present in one worksheet. So does that mean CSV file doesn’t contain complete information? The answer is No. CSV file downloaded from the system have complete information, it’s just the excel which is not able to open the file. Thus in this situation if one would like to work with the data, he or she can open the CSV file using “Get Data” option from Pivot table. The function helps the user to get the whole data into pivot format and user can accordingly select the required data. But here too, if the unique number of records in pivot exceeds the total number of rows or columns in excel, pivot won’t be able to load the file completely or show all the records.

More about the pivots and how to load external data using Pivots will be shared in the next module.

-----------------------

Workbook

Spreadsheet

Row

Column

Intersection of Rows and columns

is called as “Cell”

Visual Basic Toolbar

Standard Menu bar

Formatting Toolbar

Standard Toolbar

Pivot Toolbar

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

Formatting Tool Bar

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download