Excel TipsNTricks

(1)

EXCEL

BOOK OF TIPS AND TRICKS

kcchantnd kcchantnd

(2)

1 Excel In General

--

Shortcuts

--TIP 1.0 : The Best Shortcut Keys in Microsoft Excel

TIP 1.0 : The Best Shortcut Keys in Microsoft Excel

Ctrl+Z Ctrl+Z Undo Undo Ctrl+C Enter, Ctrl+V Ctrl+X Ctrl+C Enter, Ctrl+V Ctrl+X

Copy, Paste, Multiple Paste, Cut

Ctrl+F, Ctrl+H

Find,

Find, Find&ReplacFind&Replacee

Ctrl+P, Ctrl+S, Ctrl+F4, Alt+F4

Print, Save, Close, Close Excel

Ctrl+Arrow

Move to edge of region

Ctrl+*

Select current region

Ctrl+A

Select all cells

Ctrl+Home Ctrl+End

Select A1, Select last cell in

Select A1, Select last cell in used rangeused range

Ctrl+Shift+End

Select from active cell to last cell

Select from active cell to last cell in used range.in used range.

Ctrl+Shift+Home

Select from active cell to A1

Ctrl+Page Down Ctrl+Page Up

Move to the next sheet, Move

Move to the next sheet, Move to the previous sheetto the previous sheet

Ctrl+Tab

Move to next open workbook

Ctrl+N

(3)

Alt+F11 Alt+F11 Open VBE Open VBE Ctrl+Shift+Enter Ctrl+Shift+Enter Array formula Array formula Ctrl+F3, F3 Ctrl+F3, F3

Define name, Paste name

Ctrl+Spacebar Shift+Spacebar

Select columns, Select rows

Ctrl+1, Ctrl+B, Ctrl+U

Format cells, Bold,

Format cells, Bold, UnderlineUnderline

Ctrl+; , Ctrl+shift+:

Current date, Current time

Tip 1.1 : Easily delete data from cells in the worksheet in Microsoft Excel

Tip 1.1 : Easily delete data from cells in the worksheet in Microsoft Excel The

The shortcutsshortcuts Ctrl+Shift+EndCtrl+Shift+Endandand Ctrl+Shift+HomeCtrl+Shift+Homeallow you to quickly select aallow you to quickly select a

cell that contains

cell that contains datadata and extend the selection to the and extend the selection to the beginning of thebeginning of theworksheetworksheet

or the last used cell in

or the last used cell in the worksheet.the worksheet.

Example: Example:

You want to clear the contents of

You want to clear the contents of all cells from cell A20 to all cells from cell A20 to the last used cell ( the last used cell ( cellcell

Z1000 for example ) in the worksheet, Select cell

Z1000 for example ) in the worksheet, Select cell A20, PressA20, PressCtrl+Shift+EndCtrl+Shift+End, Press, Press

Delete Delete..

Tip 1.2 : Hiding or

Tip 1.2 : Hiding or unhiding a row or rows/column or columns in Microsoftunhiding a row or rows/column or columns in Microsoft

Excel

Rows Rows Hide

Hide - select a cell or - select a cell or cells, and presscells, and pressCtrl+9Ctrl+9..

Unhide

Unhide - select the cells above and below the - select the cells above and below the hidden row, and presshidden row, and pressCtrl+Shift+9.Ctrl+Shift+9.

Columns Columns Hide

Hide - select a cell or - select a cell or cells and presscells and pressCtrl+0Ctrl+0..

Unhide

Unhide - select the cells to - select the cells to the left and right of the the left and right of the hidden column, and presshidden column, and press

Ctrl+Shift+0. Ctrl+Shift+0.

Tip 1.3 : Moving between open

Tip 1.3 : Moving between open workbooks in Microsoft Excelworkbooks in Microsoft Excel From the Window menu, select a

From the Window menu, select a workbook from the list of workbook from the list of open workbooks.open workbooks.

The keyboard shortcut for moving between open workbooks is

The keyboard shortcut for moving between open workbooks is Ctrl+TabCtrl+Tab..

To move back between open workbooks press

To move back between open workbooks press Ctrl+Shift+TabCtrl+Shift+Tab

Tip 1.4 : Searching all

(4)

To search for text, use the keyboard shortcut

To search for text, use the keyboard shortcutCtrl+FCtrl+F or chooseor choose Edit, FindEdit, Find..

To search and

To search and replacereplace text, use the text, use the keyboard shortcutkeyboard shortcutCtrl+HCtrl+H or chooseor choose Edit,Edit, Replace

Replace..

Searching and replacing all sheets in

Searching and replacing all sheets in the Workbookthe Workbook

1.

1. From sheet tab short cut From sheet tab short cut menu choosemenu chooseSelect all sheetsSelect all sheets..

2.

2. PressPress Ctrl+FCtrl+F oror Ctrl+HCtrl+H totofindfind and replace.and replace.

Note Note

The

The Ctrl+FCtrl+F keyboard combinakeyboard combination works in tion works in Excel 97 version Excel 97 version only single sheet.only single sheet.

Tip 1.5 : Copy & paste data / formulas to thousands of cells in Microsoft

Excel

Example:

Example: CopyCopy texttext from cell A1 to cells A2 from cell A1 to cells A2 through A5000 ( see screen shot).through A5000 ( see screen shot).

1.

1. In cell A1, type Excel Book.In cell A1, type Excel Book.

2.

2. Copy cell A1.Copy cell A1.

3.

3. Select cell A2.Select cell A2.

4.

4. Click the arrow beside theClick the arrow beside the Name BoxName Box..

5.

5. Type A5000.Type A5000.

6.

6. PressPress Shift+EnterShift+Enter (select an adjacent range of cells).(select an adjacent range of cells).

7.

(5)

Tip 1.6 : Quickly delete

Tip 1.6 : Quickly delete the Formula without deleting the calculation resultthe Formula without deleting the calculation result

in Microsoft Excel

Select a cell containing a formula, press

Select a cell containing a formula, pressF2F2 (edit) and then press(edit) and then press F9F9(calculate) and(calculate) and

press

press Ctrl+Enter.Ctrl+Enter.

Tip 1.7 : Creating Charts with F11

Tip 1.7 : Creating Charts with F11 in Microsoft Excelin Microsoft Excel To illustrate, look at the screen shot, which shows sales

To illustrate, look at the screen shot, which shows salesdatadata broken down by zone.broken down by zone.

Select a cell in

Select a cell in the table, and pressthe table, and pressF11F11..

The result The result::

Excel opens a

(6)

Tip 1.8: Setting the Default Chart

Tip 1.8: Setting the Default Chart Type in Microsoft ExcelType in Microsoft Excel You can change the default

(7)

around the range and begin dragging the range towards the

around the range and begin dragging the range towards the chartchart. When the mouse. When the mouse

pointer is over the

pointer is over the chartchart, release the mouse button., release the mouse button.

--

Files importing issues

Tip 1.10 : When importing text files a minus sign appears to the right of the

number in Microsoft Excel

Problem Problem

A negative number that is imported into Excel is often formatted as

A negative number that is imported into Excel is often formatted astexttext with thewith the

minus sign (-) on the right side instead of the

minus sign (-) on the right side instead of the left. Excel does not sum up negativeleft. Excel does not sum up negative

numbers that are formatted as text, and the results will not reconcile.

Solution Solution

Enter a formula to solve both problems. It will move the minus sign (-) to

Enter a formula to solve both problems. It will move the minus sign (-) to the leftthe left

side of the number, and it will format the

side of the number, and it will format the datadata in the cell as a in the cell as a number instead of asnumber instead of as

text.

The Function

The Function ( see screen shot):( see screen shot):

=VALUE(IF(

=VALUE(IF(RIGHT(A1,1)=RIGHT(A1,1)="-",RIGHT(A1,1)&"-",RIGHT(A1,1)&LEFT(A1,LELEFT(A1,LEN(A1)-1),A1))N(A1)-1),A1))

Tip 1.11 : When importing text files Data not formatted as a number or date

properly in Microsoft Excel

Problem Problem

Often, columns with

Often, columns with numericalnumericaldatadata or columns that containor columns that containdatedate data are formatteddata are formatted

as

as texttext columns.columns.

Changing the

Changing the formattingformattingof the column fromof the column fromtexttext to number format is often notto number format is often not

efficient.

Solution 1 - Multiply by 1 Solution 1 - Multiply by 1

Enter the number 1 into the

Enter the number 1 into the cell and copy it. Select the column that is cell and copy it. Select the column that is formatted asformatted as

text, right-click, and select

text, right-click, and select Paste SpecialPaste Special. Select. SelectMultiplyMultiply from the dialog box, andfrom the dialog box, and

click OK.

Solution 2 -Text to Columns Solution 2 -Text to Columns

Select the column that is formatted as text. From the

Select the column that is formatted as text. From theDataData menu, selectmenu, select Text toText to Columns

Columns, and then select the, and then select the Fixed widthFixed width option. Skip Step 2. In Step 3, select option. Skip Step 2. In Step 3, select thethe

General

General option from theoption from the ColumnColumn datadata format, and clickformat, and click FinishFinish. To change the. To change the

column

column formattingformatting toto datedate formatting, select theformatting, select theDateDate option (underoption (under ColumnColumn datadata format

format), and then click), and then click EndEnd..

--

Sheets, Workbooks

Tip 1.12 : Moving between sheets in

Tip 1.12 : Moving between sheets in a workbook in Microsoft Excela workbook in Microsoft Excel To move to the next sheet

To move to the next sheet in the workbook, pressin the workbook, pressCtrl+Page DownCtrl+Page Down..

To move to the previous sheet in

To move to the previous sheet in the workbook, pressthe workbook, pressCtrl+Page Up.Ctrl+Page Up.

Tip 1.13 : Changing the font size of the Sheet name in Microsoft Excel

Tip 1.13 : Changing the font size of the Sheet name in Microsoft Excel The Sheet tabs in a workbook are part of the

The Sheet tabs in a workbook are part of the Scroll BarScroll Bar..

Change the font size in the

(8)

The default settings for the window view in Excel are determined by the Windows

operating system and Excel.

1.

1. Minimize all applications, pressMinimize all applications, press Windows+ mWindows+ m and right-click the desktop.and right-click the desktop.

2.

2. SelectSelect PropertiesProperties, and select the, and select theAppearanceAppearance tabtab

Tip 1.14 : Create a hyperlink from a string in Microsoft Excel

To create a hyperlink for a file

To create a hyperlink for a file pathpath andand namename (for example), enter the(for example), enter the following

following texttext in cell A1:in cell A1: c:\ExcelTip\tips.xlsc:\ExcelTip\tips.xls

In cell A2,

In cell A2, enter the following enter the following formula to create the hyperlink: =HYPERLINK(A1)formula to create the hyperlink: =HYPERLINK(A1)

(Try Using Names to perform the

(Try Using Names to perform the same trick)same trick)

Names, Range Names

(9)

•

• You cannot use aYou cannot use a NameNamethat could otherwise be confused as a cell reference.that could otherwise be confused as a cell reference.

For example, you cannot use A1 or IS2002 because these are already cell

references.

•

• There is no limit on There is no limit on the number of names you can definethe number of names you can define

•

• Be sure to define unique names for a Be sure to define unique names for a specific workbook. Definspecific workbook. Defining aing aNameName

that resembles names in other

that resembles names in other sheets only complicates your work.sheets only complicates your work.

Tip 1.16 : Define a Range Name in Microsoft Excel

Tip 1.16 : Define a Range Name in Microsoft Excel There are two ways to define a Name:

There are two ways to define a Name:

Type the

Type the texttext directly in thedirectly in the NameName boxbox

1.

1. Select Cell A1.Select Cell A1.

2.

2. In theIn the NameName box, type the text.box, type the text.

3.

3. PressPress EnterEnter..

Use the Define Name dialog box Use the Define Name dialog box

1.

1. Select cell B1.Select cell B1.

2.

2. PressPress Ctrl+F3Ctrl+F3, or from the, or from the InsertInsert menu, selectmenu, select Name, DefineName, Define……

3.

3. Type theType the texttext in the Names in workbook box.in the Names in workbook box.

4.

4. ClickClick OKOK

Tip 1.17 : Automatically defining Names

Tip 1.17 : Automatically defining Names according to the text in the according to the text in the top rowtop row

and the left column in

and the left column in Microsoft ExcelMicrosoft Excel 1.

1. Open a workbook with aOpen a workbook with a datadata range (see screenshot). There isrange (see screenshot). There istexttext in the topin the top

row and in the left

row and in the left column.column.

2.

2. Select the current region and pressSelect the current region and press Ctrl+*Ctrl+*..

3.

(10)

4.

4. Select the options Top row and Left column.Select the options Top row and Left column.

5.

(11)

Explanation:

Explanation: The formula calculates and returns the The formula calculates and returns the number of the previous year -number of the previous year

-2002.

2002.

1.

1. PressPress Ctrl+F3Ctrl+F3..

2.

2. In theIn the Names in workbookNames in workbook box, typebox, type LastYearLastYear..

3.

3. Type the formulaType the formula =YEAR(TODAY())-1=YEAR(TODAY())-1 in thein theRefers toRefers to box.box.

4.

4. ClickClick OKOK..

Enter the formula into a cell in

Enter the formula into a cell in the sheet.the sheet.

1.

1. Type theType the == sign. Press F3.sign. Press F3.

2.

2. Select theSelect the name LastYearname LastYear, and click, and click OKOK..

Tip 1.19 : Saving values in the Define Name dialog box

Tip 1.19 : Saving values in the Define Name dialog box In the

In the Define NameDefine Name dialog box, you can save values in the Refers to… box, just asdialog box, you can save values in the Refers to… box, just as

you saved the formula in

you saved the formula in the example above.the example above.

Example:

Example: The rate of exchange for the Euro, defined with theThe rate of exchange for the Euro, defined with theNameName Euro, is set atEuro, is set at

0.88.Type the amount in Cell A1. In

0.88.Type the amount in Cell A1. In Cell B1, type the formula =A1/Euro. You do notCell B1, type the formula =A1/Euro. You do not

have to type the

have to type thetexttext Euro. Instead, use the F3 shortcut.Euro. Instead, use the F3 shortcut.

Updating a value that was saved as a Name Updating a value that was saved as a Name

Press

Press Ctrl+F3Ctrl+F3, select the, select thenamename Euro, change the value of the rate Euro, change the value of the rate of exchange inof exchange in

the

the Refers to…Refers to… box, and clickbox, and click OKOK

--

Security , Protection

Tip 1.20 : Password to prevent opening a

Tip 1.20 : Password to prevent opening a workbook in Microsoft Excelworkbook in Microsoft Excel In all Excel versions, you can use

In all Excel versions, you can use aapasswordpassword to prevent opening a to prevent opening a workbook.workbook.

1.

(12)

2.

2. SelectSelect OptionsOptions. In Excel 2002 you will. In Excel 2002 you willfindfind new option, selectnew option, selectTools,Tools, Options

Options,, SecuritySecurity tab..tab..

3.

3. Type theType the passwordpassword twice, and clicktwice, and click OK.OK.

Tip 1.21 : Password to protect a

Tip 1.21 : Password to protect a workbook in Microsoft Excelworkbook in Microsoft Excel Protecting a workbook prevents the

Protecting a workbook prevents the structure from being changed.structure from being changed.

By assigning a

By assigning a passwordpassword to a workbook, you prevent to a workbook, you prevent sheets from being deleted,sheets from being deleted,

new sheets from being inserted, and hidden sheets

new sheets from being inserted, and hidden sheets from being opened.from being opened.

From the

From the ToolsTools menu, selectmenu, select Protection, Protect WorkbookProtection, Protect Workbook. Type a. Type a passwordpassword inin

the

the PasswordPasswordbox, and clickbox, and click OKOK. Now confirm the. Now confirm thepasswordpassword, and click, and click OKOK again.again.

Tip 1.22 : Protecting a

Tip 1.22 : Protecting a sheet in Microsoft Excelsheet in Microsoft Excel

New in Excel 2002 xp. New in Excel 2002 xp.

From the

From the ToolsTools menu, selectmenu, select Protection, Protect SheetProtection, Protect Sheet..

In

In Excel 2002Excel 2002, the, the Protect SheetProtect Sheet dialog box allows you to select the operationsdialog box allows you to select the operations

you want users to be able to perform.

Under Allow all users of this

Under Allow all users of this worksheetworksheet to, select the checkboxes for the operationsto, select the checkboxes for the operations

you want to

you want to leave unprotected.leave unprotected.

--

Printing

Tip 1.23 : Printing Comments in

Tip 1.23 : Printing Comments in Microsoft Excel.Microsoft Excel. From the

From the FileFile menu, selectmenu, select Page SetupPage Setup, and click the, and click the SheetSheet tab. Before printing,tab. Before printing,

select one of the following options in

select one of the following options in thetheCommentsComments box:box:

•

• NoneNone - Will not print comments.- Will not print comments.

•

• At end of sheetAt end of sheet - Will print the comments on a separate page after- Will print the comments on a separate page afterprintingprinting

the sheet.

•

• As displayed on sheetAs displayed on sheet - Will only print the - Will only print the comments that are displayedcomments that are displayed..

Print a Single Comment Print a Single Comment

(13)

4.

4. SelectSelect LeftLeft area.area.

5.

5. Click theClick the PicturePicture icon (second from right). In the Inserticon (second from right). In the InsertPicturePicture dialog box,dialog box,

search for and select the logo or

search for and select the logo orpicturepicture you want to you want to add.add.

6.

6. ClickClick InsertInsert, Click, Click OKOK..

Tip 1.25 : Inserting a

Tip 1.25 : Inserting a watermark behind the text in Microsoft Excelwatermark behind the text in Microsoft Excel Reports such as a company's financial statements are, by

Reports such as a company's financial statements are, by their very nature,their very nature,

confidential. Insert the

confidential. Insert thetexttext "Confidential" behind the"Confidential" behind thedatadata in the report in such ain the report in such a

way that it does not interfere with

way that it does not interfere with reading the report.reading the report.

1.

1. Display theDisplay the WordArtWordArt toolbar. Select one of thetoolbar. Select one of thetoolbarstoolbars, right-click, and, right-click, and

select the

select the WordArtWordArttoolbar.toolbar.

2.

2. On theOn the WordArtWordArt toolbar, click the Inserttoolbar, click the Insert WordArtWordArt icon (A).icon (A).

3.

3. From theFrom the WordArt GalleryWordArt Gallery, select any example, and click OK., select any example, and click OK.

4.

4. In theIn the WordArt Edit TextWordArt Edit Text dialog box, type "Confidential" (or any other dialog box, type "Confidential" (or any other text),text),

and select the font and the font

and select the font and the font size.size.

5.

5. Right-click theRight-click the WordArtWordArt, and from the shortcut menu, select the, and from the shortcut menu, select the Colors andColors and Lines

Lines tab.tab.

6.

6. InIn FillFill, select, select ColorColor,, No FillNo Fill..

7.

7. InIn LineLine,, ColorColor, select a color that is not too light., select a color that is not too light.

8.

8. Click OK.Click OK.

9.

9. Right-click, and from the shortcut menu, selectRight-click, and from the shortcut menu, select OrderOrder, Send to, Send to BackBack..

10.

10.Adjust the object's size and location to suit the sheetAdjust the object's size and location to suit the sheet

Tip 1.26 : Preventing charts from

Tip 1.26 : Preventing charts from being printed in Microsoft Excelbeing printed in Microsoft Excel Select a

Select a chartchart, and right-click. From the shortcut , and right-click. From the shortcut menu, selectmenu, selectFormatFormat ChartChart AreaArea..

Select the

Select the PropertiesProperties tab, and clear the checkbox besidetab, and clear the checkbox beside Print ObjectPrint Object..

Click

(14)

--

Charts

Tip 1.27 : Replacing Data

Tip 1.27 : Replacing Data Markers with Pictures in Microsoft ExcelMarkers with Pictures in Microsoft Excel

Shaking up the normal routine every once in a while cannot hurt. Jazzing up

Shaking up the normal routine every once in a while cannot hurt. Jazzing upchartchartss

with interesting shapes makes a strong visual statement when

with interesting shapes makes a strong visual statement when displaying results. Indisplaying results. In

the figure below, a

the figure below, a number of pictures were added. You can add letters number of pictures were added. You can add letters totodatadata

markers in any order you like or

markers in any order you like or replacereplace data markers with flags or anything else;data markers with flags or anything else;

just use your imagination.

1.

1. In theIn the ChartChart, select the desired, select the desireddatadata markers (if you want tomarkers (if you want to replacereplace themthem

with the same picture).

2.

2. From theFrom the InsertInsert menu, select Picture.menu, select Picture.

3.

3. From theFrom the Insert Clip ArtInsert Clip Art pane (in Excel version 97 or 2000, choose frompane (in Excel version 97 or 2000, choose from FileFile

menu), select the

(15)

(16)

2 Excel Text, Editing and Formatting

--

Formatting

Tip 2.0 : Add text

Tip 2.0 : Add text to Number format in Microsoft Excelto Number format in Microsoft Excel Press

Press Ctrl+1Ctrl+1 (Format Cells), Choose(Format Cells), Choose NumberNumbertab, fromtab, from CategoryCategory ChooseChoose CustomCustom,,

in the

in the TypeType box enter :box enter :

"Credit" #,##0_;"Debit" [Red](#,##0); "Credit" #,##0_;"Debit" [Red](#,##0); Tip 2.1 : Color even

Tip 2.1 : Color even numbered rows in Microsoft Excelnumbered rows in Microsoft Excel Coloring even numbered rows in Microsoft Excel can

Coloring even numbered rows in Microsoft Excel can done using conditionaldone using conditional

formatting:

1.

1. Select the rows you want to apply the Select the rows you want to apply the colorcolor

2.

2. From the format From the format menu choose "Conditional Formatting"menu choose "Conditional Formatting"

3.

3. In Condition 1 In Condition 1 choose "Formula Is", enter the choose "Formula Is", enter the formula =MOD(Rformula =MOD(ROW(),2)<>0 inOW(),2)<>0 in

the box

Choose any format and press OK

(17)

Tip 2.2 : Formatting numbers according to criteria

Tip 2.2 : Formatting numbers according to criteria in Microsoft Excelin Microsoft Excel There are two ways

There are two ways to format numbers according to criteria:to format numbers according to criteria:

•

• CustomCustom formattingformatting for a number with criteriafor a number with criteria

•

• ConditionalConditionalformattingformatting(see screen shot)(see screen shot)

Using custom formats for numbers with criteria Using custom formats for numbers with criteria

Your options for coloring a

Your options for coloring a number are not limited to number are not limited to displaying negative numbersdisplaying negative numbers

red. You can color positive and/or negative numbers in any color you wish (do not

get excited - the number of colors is limited to

get excited - the number of colors is limited to eight, and most are difficult to read).eight, and most are difficult to read).

Do this by adding the

Do this by adding the namename of the color to of the color to the number format in brackets.the number format in brackets.

Example:

Example: [BLUE] #,##0 ;[RED](#,##0)[BLUE] #,##0 ;[RED](#,##0)

A positive number is displayed in

A positive number is displayed in blue; a negative number is displayed in blue; a negative number is displayed in red; and 0red; and 0

is displayed in blue (since there is no third section, 0

is displayed in blue (since there is no third section, 0 receives the format of thereceives the format of the

positive number by default).

Add a condition to the formatting, and have each section be displayed in a different

color:

[BLUE] [>5000]#,##0 ;[RED](#,##0); #,##0

(18)

--

Editing

Tip 2.3 : Transposing Data

Tip 2.3 : Transposing Data in Microsoft Excelin Microsoft Excel

Transpose is used to change a vertical range of cells to a horizontal range or vice

versa.

1.

1. Copy a range of cells (see cells A1:C9 in Copy a range of cells (see cells A1:C9 in the screenshot).the screenshot).

2.

2. Select a cell in the activeSelect a cell in the activeworksheetworksheet or any otheror any other worksheetworksheet andand right- right-click

click..

3.

3. From the shortcut menu, selectFrom the shortcut menu, select Paste SpecialPaste Special..

4.

(19)

Tip 2.4 : Extracting characters from text in

Tip 2.4 : Extracting characters from text in Microsoft ExcelMicrosoft Excel

Using

Using formulasformulas to extract characters from textto extract characters from text

See Screen shots - A budget item number or an

See Screen shots - A budget item number or anindexindex number in accounting is madenumber in accounting is made

up of three parts:

•

• a department number - the first a department number - the first three digits.three digits.

•

• the expense item number - the next three digits.the expense item number - the next three digits.

•

• and secondary description of the expense - and secondary description of the expense - the last two the last two digits.digits.

Use the

Use the formulasformulas shown in Row 6 to extract these portions from the tshown in Row 6 to extract these portions from the text. You canext. You can

find

find thethe formulasformulas for extracting characters in thefor extracting characters in theTextText category, under the Pastecategory, under the Paste

function.

(20)

Tip 2.5 Parsing characters from text without formulas in

Tip 2.5 Parsing characters from text without formulas in Microsoft ExcelMicrosoft Excel

Use

Use Text to Columns to parse text.Textto Columns to parse text.

1.

1. Select column A.Select column A.

2.

2. ChooseChoose Data,Data, TextText to Columnsto Columns..

3.

3. In step 1 of 3, selectIn step 1 of 3, select FixedFixed width.width.

4.

4. ClickClick NextNext..

5.

5. In step 2 of 3, you parse theIn step 2 of 3, you parse the datadata in the column by clicking the mousein the column by clicking the mouse

between columns you want to

between columns you want to parse.parse.

6.

6. ClickClick NextNext..

7.

7. In step 3 of 3, in theIn step 3 of 3, in the DestinationDestination box, enter cell B1.box, enter cell B1.

8.

(21)

Tip 2.6 : Separating first name and last name without using formulas in

Microsoft Excel

The cells in Column A contain a

The cells in Column A contain a list of names, firstlist of names, firstnamename and last name.and last name.

1.

1. Select Column A or the range of cells containing the Select Column A or the range of cells containing the list of names.list of names.

2.

2. From theFrom the DataData menu, selectmenu, select Text to ColumnsText to Columns..

3.

3. In Step 1 of 3, selectIn Step 1 of 3, select DelimitedDelimited..

4.

4. In Step 2 of 3, select theIn Step 2 of 3, select the SpaceSpace option.option.

5.

5. In Step 3 of 3, in theIn Step 3 of 3, in the DestinationDestination box, select Cell B1, and clickbox, select Cell B1, and clickFinishFinish

Tip 2.7 : Typing dates in cells quickly in Microsoft Excel

Tip 2.7 : Typing dates in cells quickly in Microsoft Excel Typing a large amount of

Typing a large amount of datadata in cells in the in cells in the sheet can be tiring if it sheet can be tiring if it includes a seriesincludes a series

of dates.

Minimize the work by typing the day of the current month and adding the following

formula to insert the month and year.

=DATE(YEAR(TODAY()),MONTH(TODAY()),A1) =DATE(YEAR(TODAY()),MONTH(TODAY()),A1)

Type a full number in the cell

Type a full number in the cell and change it to aand change it to adatedate by using the formula below.by using the formula below.

For example

For example, type 10222002. The result is , type 10222002. The result is 10/22/2002.10/22/2002.

=LEFT(A1,2)&"/"&MID(A1,3,2)&"/"RIGHT(A1,4) =LEFT(A1,2)&"/"&MID(A1,3,2)&"/"RIGHT(A1,4)

(22)

3 Excel Date and Time

How Excel Works with Dates

Excel considers dates as

Excel considers dates as numbers. Eachnumbers. Eachdatedate is assigned a uniqueis assigned a unique serial number serial number . For. For

example, the 27th September 1999 was

example, the 27th September 1999 wasdatedate

serial 36430. Fortunately, you don't need to

know this but the fact that all

know this but the fact that all dates havedates have

numerical values can be very useful.

numerical values can be very useful. WindowsWindows

uses the

uses the 19001900datedate system in which 1stsystem in which 1st

January 1900 is

January 1900 is datedate serial 1, 2nd Januaryserial 1, 2nd January

1900 is

1900 is datedate serial 2 and so on.serial 2 and so on.

When you type a

When you type a datedate into a cell, Excel showsinto a cell, Excel shows

you a

you adatedate but is thinking of a number.but is thinking of a number.

To

To findfind out the serial number of a date, selectout the serial number of a date, select

the cell containing the

the cell containing thedatedate then go tothen go to

Format > Cells

Format > Cells. Go to the. Go to the NumberNumber tab and clicktab and click GeneralGeneral in thein the CategoryCategory list. Thelist. The

date's serial number will appear in the

date's serial number will appear in the SampleSample box on the right.box on the right.

You can make use of these numbers in all sorts of

You can make use of these numbers in all sorts of ways. You can add a number to aways. You can add a number to a

date

date to give ato give a datedate that number of days later (or subtract a number to get athat number of days later (or subtract a number to get adatedate

before), you can take one

before), you can take one datedate from another tofrom another to findfind out how many days in out how many days in between.between.

There are lots of ready-made

There are lots of ready-made datedate functions too.functions too.

Tip 3.0 : Calculate difference

Tip 3.0 : Calculate difference between Dates in Microsoft Excelbetween Dates in Microsoft Excel Use the formula

Use the formula DATEDIFDATEDIF to calculate the difference between to calculate the difference between dates.dates.

The results of

The results of the calculation are displayed as days, full the calculation are displayed as days, full months, and full years.months, and full years.

The formula is not located in

The formula is not located in Paste FunctionPaste Function in thein the Date & TimeDate & Timecategory.category.

You must enter the

(23)

Tip 3.1 : Calculating the

Tip 3.1 : Calculating the quarter number in Microsoft Excelquarter number in Microsoft Excel

This section, including the figure below, explains how to calculate the quarter of a

calendar year and of fiscal years that begin in July or October.

To calculate a quarter for a calendar year, see the syntax of the

To calculate a quarter for a calendar year, see the syntax of the formula for Cell B2formula for Cell B2

as shown in Cell B15(see screen shot).

To calculate a quarter for a fiscal year that begins in October, see the

To calculate a quarter for a fiscal year that begins in October, see the syntax of thesyntax of the

formula for Cell B2 as shown in Cell

formula for Cell B2 as shown in Cell B17.B17.

To calculate a quarter for a fiscal year that begins in July,

To calculate a quarter for a fiscal year that begins in July, see the syntax of thesee the syntax of the

formula for Cell D2 as shown in Cell

formula for Cell D2 as shown in Cell B19.B19.

The formula

The formula INTINT rounds the result of the calculation to a rounds the result of the calculation to a whole number, whichwhole number, which

represents the quarter.

(24)

Tip 3.2 : Filtering by the date field in Mi

Tip 3.2 : Filtering by the date field in Microsoft Excelcrosoft Excel Excel does not sort

Excel does not sort datadata according to cell format, but according to cell according to cell format, but according to cell value. Whenvalue. When

sorting

sorting by date, Excel sorts theby date, Excel sorts thedatedate according to its according to its number.number.

For example, the serial number of the

For example, the serial number of thedatedate September 9, 2001 is 37164. If the September 9, 2001 is 37164. If the cellcell

format is changed to "mmmm", the result of

format is changed to "mmmm", the result of the format is September. Whenthe format is September. Whensortingsorting

the

the datadatalist, Excel ignores September and only relates to the number 37164.list, Excel ignores September and only relates to the number 37164.

With

With AutoFilterAutoFilter, as opposed to, as opposed to SortingSorting, Excel relates to the, Excel relates to thedatedate format and allowsformat and allows

you to filter

you to filter datadata according to format.according to format.

1.

1. Turn off theTurn off the AutoFilterAutoFilter. From the. From the DataData menu, selectmenu, select FilterFilter,, AutoFilterAutoFilter..

2.

2. Copy theCopy the DateDate column.column.

3.

3. Select two columns to the right of Date, Select two columns to the right of Date, right-click, and from the shortcutright-click, and from the shortcut

menu, select

menu, select Insert Copied CellsInsert Copied Cells (pasting by inserting copied cells allows(pasting by inserting copied cells allows

you to insert two columns and paste the copied column into them).

4.

4. In Cell D1, type the In Cell D1, type the headingheadingMonthMonth, and in Cell E1, , and in Cell E1, type the headingtype the headingYearYear..

5.

5. Select theSelect the MonthMonth field. To select it field. To select it quickly, select Cell D2, and pressquickly, select Cell D2, and press

Shift+SpaceBar Shift+SpaceBar..

6.

6. PressPress Ctrl+1Ctrl+1 (Format Cells).(Format Cells).

7.

7. In theIn the NumberNumbertab, selecttab, select CustomCustom..

8.

8. In theIn the TypeTypebox, enter the box, enter the format, "mmmmformat, "mmmm" (full month " (full month format).format).

9.

9. Click OK.Click OK.

10.

10.Select theSelect the YearYear field. To select it quickly, select Cell field. To select it quickly, select Cell E2, and pressE2, and press

Shift+SpaceBar Shift+SpaceBar..

11.

11.PressPress Ctrl+1.Ctrl+1.

12.

12.In the Number tab, select In the Number tab, select Custom.Custom. 13.

13.In the Type box, enter the format, "yyyy" (year format).In the Type box, enter the format, "yyyy" (year format). 14.

14.Click OK.Click OK. 15.

(25)

Tip 3.3 : Calculating the

Tip 3.3 : Calculating the difference between hours in Microsoft Exceldifference between hours in Microsoft Excel

Calculate an employee's working hours. In the screen shot, notice the format in Cells

E4:E8 is

E4:E8 is hh:mmhh:mm..

The number 1 in the formula

The number 1 in the formula IFIF represents arepresents a timetime value that exceeds 24 hours in avalue that exceeds 24 hours in a

day.

See line 5 in

See line 5 in the example. An employee named Mark arrived at work at 23:00 in thethe example. An employee named Mark arrived at work at 23:00 in the

evening and finished work at 7:00 the next morning. The result is calculated as 8:00

working hours.

(26)

4 Excel Formula and Fucntions

--

Working with Formulas

Tip 4.0 : Handling error

Tip 4.0 : Handling error in Formula results in Microsoft Excelin Formula results in Microsoft Excel Entering

Entering formulasformulas into cells in Excel sheets is into cells in Excel sheets is not foolproof. It is impossible tonot foolproof. It is impossible to

completely avoid mistakes, but there are ways t

completely avoid mistakes, but there are ways to keep them to o keep them to a minimum.a minimum.

Combining the

Combining the IFIF formula with theformula with theISERROR ISERROR formula returns a calculation whoseformula returns a calculation whose

result is TRUE, if the result of

result is TRUE, if the result of a calculation returns an error.a calculation returns an error.

For example: For example:

In Cell A1, type the

In Cell A1, type the number 100.number 100.

In Cell B1, type the

In Cell B1, type the formula =A1/A2. The calculation returns an error - #DIV/0!formula =A1/A2. The calculation returns an error - #DIV/0!

The error is created when you try to divide a

The error is created when you try to divide a number in a cell by 0 (A2).number in a cell by 0 (A2).

To avoid displaying an error in a cell: To avoid displaying an error in a cell:

1.

1. In Cell C1, enter tIn Cell C1, enter the formulahe formula=ISERROR(B1)=ISERROR(B1). The formula returns the result. The formula returns the result

TRUE. That is, Cell B1 contains an error in t

TRUE. That is, Cell B1 contains an error in the calculation of the formula (thehe calculation of the formula (the

formula

formula ISERROR ISERROR is located in the category Information in theis located in the category Information in thePastePaste Function

Function dialog box).dialog box).

2.

2. In Cell D1, enter an In Cell D1, enter an IF formula =IF(C1,0,B1).IF formula =IF(C1,0,B1).

3.

3. Combine theCombine the formulasformulas into one nested formula. From Cell C1, copy theinto one nested formula. From Cell C1, copy the

formula (without the = sign), and paste it into Cell D1

formula (without the = sign), and paste it into Cell D1 instead of C1.instead of C1.

4.

4. From Cell B1, copy the formula (without the = sign), and paste it twice From Cell B1, copy the formula (without the = sign), and paste it twice -

-instead of the digit 0 and -instead of B1.

instead of the digit 0 and instead of B1. The result - one nested formula:The result - one nested formula:

=IF(ISERROR(A1/A2),0,A1/A2).

Tip 4.1:

Tip 4.1: Printing the Formula syntax in Printing the Formula syntax in Microsoft ExcelMicrosoft Excel

Use the usual method of

Use the usual method of printingprinting in Excel to print from the sheet in Excel to print from the sheet whilewhileformulasformulas

are displayed using

(27)

--

Lookup formulas

Tip 4.3 : Combine the

Tip 4.3 : Combine the VLookup and Match formulas in Microsoft ExcelVLookup and Match formulas in Microsoft Excel

The

The VLookupVLookupformula returnsformula returns datadata from any column you choose in thefrom any column you choose in thedatadata table,table,

simply change the number of the

simply change the number of the column in the third column in the third argument.argument.

Although it sounds simple, there is a catch.

•

• How can you determine the number of a column in aHow can you determine the number of a column in a datadata table that containstable that contains

numerous columns?

•

• How can you easily change the number of the column in the How can you easily change the number of the column in the third argument of third argument of

the

the VLookupVLookupformula?formula?

•

• How can you easily How can you easily change the column number in multiplechange the column number in multipleformulasformulas in ain a

worksheet

worksheet from which complex reports are prepared or in a sheet thatfrom which complex reports are prepared or in a sheet that

contains multiple

contains multiple VLookupVLookupformulas?formulas?

Solution Solution

Insert the

Insert the MatchMatch formula in the third argument of theformula in the third argument of theVLookupVLookupformula.formula.

Step 1: Define 2 names

Step 1: Define 2 names, see screen shot, see screen shot

1.

1. Select Row 1, pressSelect Row 1, press Ctrl+F3Ctrl+F3, type the, type the namename Row1Row1 in thein the NamesNames inin

workbook field, and click OK.

2.

2. Select theSelect the datadatatable by pressingtable by pressing Ctrl+*Ctrl+*. Then press. Then press Ctrl+F3Ctrl+F3, enter, enter DataData inin

the

the NamesNames in workbook field, and click in workbook field, and click OK.OK.

Step 2: Enter the

Step 2: Enter the MatchMatch formulaformula

1.

1. Open an adjacent worksheet, and select cell Open an adjacent worksheet, and select cell A1.A1.

2.

2. In cell A1 type 4/1/2001.In cell A1 type 4/1/2001.

3.

3. In cell B1, enter In cell B1, enter the formula =MATCH (A1, Row1, 0). (Be careful to enter thethe formula =MATCH (A1, Row1, 0). (Be careful to enter the

value 0 in the

value 0 in the third argument to specify the search for an exact value.)third argument to specify the search for an exact value.)

4.

4. Results of calculation: 7.Results of calculation: 7.

Step 3: Enter the

Step 3: Enter the VlookupVlookup formulaformula

1.

1. Enter the account number 201 into cell A2.Enter the account number 201 into cell A2.

2.

2. Enter the formula =VLOOKUP(A2,DEnter the formula =VLOOKUP(A2,Data,B1) in cell ata,B1) in cell B2. In the B2. In the third argument of third argument of

the

the VLookupVLookupformula, select a cell which contains theformula, select a cell which contains theMatchMatch formula.formula.

3.

3. Calculation results: 7,981.Calculation results: 7,981.

•

(28)

1.

1. In the formula bar (In the formula bar (MatchMatch formula) of cell B1, select the formula) of cell B1, select the formula without theformula without the

= sign, press

= sign, press Ctrl+CCtrl+C, and click the Cancel sign (from the left , and click the Cancel sign (from the left of the formulaof the formula

in the formula bar). Select cell B2, and in

in the formula bar). Select cell B2, and in the formula bar, select thethe formula bar, select theaddressaddress

B1.

2.

2. PressPress Ctrl+VCtrl+V and press Enter.and press Enter.

3.

3. The final result The final result is a nested is a nested formula is =VLOOKUP (A2, Data, MATCH(A1, Rowformula is =VLOOKUP (A2, Data, MATCH(A1, Row

1, 0))

(29)

Tip 4.4 : Combine the

Tip 4.4 : Combine the Index and Match formulas in Microsoft ExcelIndex and Match formulas in Microsoft Excel Step 1: Define a name

Step 1: Define a name

Define a Name to column B ( see screen shot

Define a Name to column B ( see screen shot ). In the example, the Name is ColB.). In the example, the Name is ColB.

Step 2: Enter the

Step 2: Enter the Match formulaMatchformula

•

• Select a new sheet. In cell A1, enter oneSelect a new sheet. In cell A1, enter onetexttext from the expenses list.from the expenses list.

•

• Calculate the row number. In cell B1, enter the Calculate the row number. In cell B1, enter the formula =MATCH (A1, ColB,formula =MATCH (A1, ColB,

0). The result of the

0). The result of the calculation is 9.calculation is 9.

•

• In cell A2, enter theIn cell A2, enter the datedate 4/1/2001.4/1/2001.

•

• Calculate the column number. In cell B2, enter the formula =MATCH (A2,Calculate the column number. In cell B2, enter the formula =MATCH (A2,

Row1, 0). The result of the

Row1, 0). The result of the calculation is 7.calculation is 7.

Step 3: Enter the

Step 3: Enter the IndexIndex formulaformula

•

• In cell C1, enter In cell C1, enter the formula =INDEX(Dathe formula =INDEX(Data, B1, B2). The result is 345.ta, B1, B2). The result is 345.

Step 4: Combine (nesting) the

Step 4: Combine (nesting) the formulasformulas Now you will use the technique of

Now you will use the technique of copying and pasting a formcopying and pasting a formula from the formulaula from the formula

bar (without the = sign) into a different formula.

•

• From the formula bar of cell B1, copy the From the formula bar of cell B1, copy the Match formula without the =Match formula without the =

(equal) sign.

•

• Click the X to tClick the X to the left of the he left of the formula (to cancel).formula (to cancel).

•

• Select the C1 cell; in Select the C1 cell; in the formula bar, select the B1 address; and pressthe formula bar, select the B1 address; and press

Ctrl+V.

•

• Use the same technique to copy the Match formula from the B2 cell to Use the same technique to copy the Match formula from the B2 cell to thethe

formula bar in cell C1 (instead of the B2

formula bar in cell C1 (instead of the B2 address).address).

The combined formula is :

=INDEX(DA

(30)

(31)

5 Excel Data

Tip 5.1 : Guidelines to

Tip 5.1 : Guidelines to consider before sorting data in Microsoft Excelconsider before sorting data in Microsoft Excel

•

• SelectingSelecting cellcell

Do not select a column or

Do not select a column or datadata range in arange in a datadata list on a sheet. Select only alist on a sheet. Select only a

single cell. Click the

single cell. Click the SortSort icon to automatically sort the entire list. Theicon to automatically sort the entire list. Thedatadata

will be sorted according to the field of the

will be sorted according to the field of the cell selected.cell selected.

•

• FormulasFormulas

Be careful when

Be careful when sorting datasorting data if there areif there areformulasformulas in the cells.in the cells. SortingSorting data

data when cells are linked to other rows or when cells are linked to other rows or cells in other sheets could distortcells in other sheets could distort

the calculations. Be meticulous when

the calculations. Be meticulous whensortingsorting a list witha list with formulasformulas that havethat have

Names (which are defined with absolute references by

Names (which are defined with absolute references by default) or withdefault) or with

formulas

formulas that have absolute references.that have absolute references.

•

• Insert a sequence column to restore original Insert a sequence column to restore original orderorder

Insert an additional column into the

Insert an additional column into the datadata list with list with ascending numbersascending numbers,,

1,2,3,…(do not use a

1,2,3,…(do not use a formula) beforeformula) beforesortingsorting the data. If a list the data. If a list includes aincludes a

column with consecutive dates, use this column as the first

column with consecutive dates, use this column as the firstsortingsortingcolumncolumn

Tip 5.2 : Change the auto save default file location in Microsoft Excel

Tip 5.2 : Change the auto save default file location in Microsoft Excel From the

From the ToolsTools menu, selectmenu, select Options, GeneralOptions, General tab.tab.

A new Excel file is

A new Excel file is automatically saved for the firstautomatically saved for the firsttimetime (if you have not specified a(if you have not specified a

location on the hard disk) in the

location on the hard disk) in the My DocumentsMy Documents folder on the C: drive.folder on the C: drive.

Change the default location as

Change the default location as desireddesired

Tip 5.3 : Using the COUNTIF function to filter a list into a unique record in

Microsoft Excel

Microsoft Excel 1.

1. In Cell B1(see screen shot), enter theIn Cell B1(see screen shot), enter the texttext Unique Record.Unique Record.

2.

2. Select Cell Select Cell B2, and B2, and enter the enter the formula =IF(COUNTIFformula =IF(COUNTIF($A$2:A2,A2)>1,1($A$2:A2,A2)>1,1,0).,0).

3.

3. Copy the formula from Cell B2 to Cell Copy the formula from Cell B2 to Cell B40 (the customer list in Column AB40 (the customer list in Column A

extends through Cell A40).

4.

4. From theFrom the DataData menu, selectmenu, select FilterFilter..

5.

5. Open the filtering drop-down list in Cell B1 by clicking the arrow, and select 0.Open the filtering drop-down list in Cell B1 by clicking the arrow, and select 0.

6.

(32)

Explanation: Explanation:

The

The COUNTIFCOUNTIF function counts the number of cells within a range that meet thefunction counts the number of cells within a range that meet the

given criteria.

For example, the

For example, the COUNTIFCOUNTIF function returns the number of function returns the number of times a customertimes a customer

appears in a list. The IF function uses the

appears in a list. The IF function uses the results of theresults of theCOUNTIFCOUNTIF calculation. If thecalculation. If the

result of the calculation is greater than 1, the

result of the calculation is greater than 1, the result of calculating the formula is 1. If result of calculating the formula is 1. If

it is not, the

it is not, the result is 0. Because the range runs from an absolute cell ($A$2) to result is 0. Because the range runs from an absolute cell ($A$2) to aa

relative cell (A2), the cell range checked by the

relative cell (A2), the cell range checked by theCOUNTIFCOUNTIF functions changes whenfunctions changes when

the formula is copied.

With

(33)

Tip 5.4 : Consolidate with formulas if the row & column headers of all

worksheets are identical in Microsoft Excel

worksheets are identical in Microsoft Excel If you have several worksheets and every

If you have several worksheets and everyworksheetworksheet has an identical structure -has an identical structure

-that is, the headings in row 1

that is, the headings in row 1 and column A are identical from sheet to sheet, youand column A are identical from sheet to sheet, you

can create a total

can create a total worksheetworksheet using simpleusing simple formulasformulas..

Example: Example:

In the workbook 3

In the workbook 3 sheets - January, February and March contain tables with salarysheets - January, February and March contain tables with salary

data.

On every sheet, the items in cells

On every sheet, the items in cells A2:A8 are identical.A2:A8 are identical.

On every sheet, the employees listed in B1:H1 are identical and never change.

Add a new worksheet, change the

Add a new worksheet, change the worksheetworksheet name to Total. Use a formula like thename to Total. Use a formula like the

one shown in cell B2 (

one shown in cell B2 ( see screen shot ) to add the other sheets.see screen shot ) to add the other sheets.

The formula in Cell B2 is

The formula in Cell B2 is =SUM (January:March!B2)=SUM (January:March!B2)..

The technique for entering a formula to sum a single cell on

The technique for entering a formula to sum a single cell on a number of sheets.a number of sheets.

1.

1. Select Cell B2 in the sheetSelect Cell B2 in the sheetnamename Total.Total.

2.

2. In the cell, typeIn the cell, type =SUM=SUM, and press, and press Ctrl+ACtrl+A (the shortcut for displaying the(the shortcut for displaying the

formula argument box).

3.

3. In the first argument box, select theIn the first argument box, select the namename of the first sheet in of the first sheet in the rangethe range

January.

4.

4. PressPress ShiftShift, and select the last sheet , and select the last sheet in the range March.in the range March.

5.

(34)

6 Just a pinch of VBA

Tip 6.0 : Using

Tip 6.0 : Using the Macro Recorder in Microsoft Excelthe Macro Recorder in Microsoft Excel Open Excel and the

Open Excel and the VBEVBE ((Visual Basic EditorVisual Basic Editor). Unless it has been changed, the). Unless it has been changed, the VBEVBE

window contains the

window contains theProject Explorer Project Explorer window and thewindow and thePropertiesPropertieswindow (these can be accessedwindow (these can be accessed

from the

from the ViewViewmenu).menu).

Project Explorer:

Project Explorer: Works like a file manager. Helps you navigate around the code in Works like a file manager. Helps you navigate around the code in your your

workbook.

Properties Window:

Properties Window: Shows the properties of the currently acShows the properties of the currently active object (e.g.tive object (e.g. Sheet1Sheet1) of the) of the

current workbook (e.g.

current workbook (e.g.Book1Book1).).

Exercise 1: Recording a Macro. Exercise 1: Recording a Macro.

This exercise shows what happens when a macro is recorded and demonstrates the difference This exercise shows what happens when a macro is recorded and demonstrates the difference between recording absolute and relative references.

between recording absolute and relative references.

1.

1. On an emptyOn an empty worksheetworksheet in a new workbook, select cellin a new workbook, select cellC10C10

2.

2. Start theStart theMacro Recorder Macro Recorder with option to save macro inwith option to save macro inThis Workbook This Workbook . At this point the. At this point the

VBE

VBE creates a newcreates a newModulesModulesfolder. It's quite safe to go and look at folder. It's quite safe to go and look at it - your actions won't beit - your actions won't be

recorded. Click the

recorded. Click the [+][+]next to the folder and see that next to the folder and see that thetheVBEVBE has places a module in the folder has places a module in the folder

and named it

and named itModule1Module1. Double-click the module icon to open its code window. Switch back to. Double-click the module icon to open its code window. Switch back to

Excel.

3.

3. Make sure that theMake sure that theRelative ReferenceRelative Referencebutton on thebutton on theStop Recording Stop Recording toolbar is NOTtoolbar is NOT

pressed in.

4.

4. Select cellSelect cellB5B5and stop the recorder.and stop the recorder.

5.

5. Switch to theSwitch to the VBEVBE and look at the code:and look at the code:

Range("B5").Select Range("B5").Select 6.

6. Now record another macro, exactly the same way, but thisNow record another macro, exactly the same way, but this timetime with thewith theRelativeRelative Reference

Referencebutton pressed in.button pressed in.

7.

ActiveCell.Of

ActiveCell.Offset(-5, fset(-5, -1).Range("A1").Sel-1).Range("A1").Selectect 8.

8. Now record another macro, but instead of Now record another macro, but instead of selectingselectingcell B5, select a block of cells 3x3cell B5, select a block of cells 3x3

starting at B5 (select cells B5:F7)

9.

ActiveCell.Of

ActiveCell.Offset(-5, fset(-5, -1).Range("A1:B3").S-1).Range("A1:B3").Selectelect 10.

(35)

11.

11. Run Macro2 but force an error byRun Macro2 but force an error byselectingselecting a cell in row 5 or above. The macro a cell in row 5 or above. The macro tries totries to

select a non-existent cell because its code is telling it to select a ce

select a non-existent cell because its code is telling it to select a cell 5 rows above the startingll 5 rows above the starting

point, and that's off the top of the sheet.

point, and that's off the top of the sheet. PressPressDebugDebugto be taken to the pato be taken to the part of the macro thatrt of the macro that

caused the problem.

NOTE: When the

NOTE: When the VBEVBEis in Debug mode the line of code is in Debug mode the line of code that caused the problem is highlightedthat caused the problem is highlighted

in yellow. You must "reset" the macro before you can proceed. Click the

in yellow. You must "reset" the macro before you can proceed. Click the ResetResetbutton on thebutton on the

VBE

VBE toolbar or go totoolbar or go to Run > ResetRun > Reset. The yellow highlighting disappears and . The yellow highlighting disappears and thetheVBEVBE comes outcomes out

of Debug mode.

12.

12. It is important to try and anticipate user error like this. The simplest way is to modify the codeIt is important to try and anticipate user error like this. The simplest way is to modify the code

to simply ignore

to simply ignore errorserrorsand move on to the nand move on to the next task. Do this by adding the line…ext task. Do this by adding the line…

On Error Resume Next

… immediately above the first line of the macro (underneath the line

… immediately above the first line of the macro (underneath the line SubSubMacro1()Macro1() 13.

13. RunRunMacro2 Macro2 as before, starting too high on the sheet. Thisas before, starting too high on the sheet. This timetime the line you typed tellsthe line you typed tells

Excel to ignore the line of code that it can't execute.

Excel to ignore the line of code that it can't execute. There is no error message and the macroThere is no error message and the macro

exits having done all it can. Use this method of handling

exits having done all it can. Use this method of handling errorserrorswith caution. This is a verywith caution. This is a very

simple macro. A more complex macro would probably not perform as expected if

simple macro. A more complex macro would probably not perform as expected if errorserrors werewere

simply ignored. Also, the user has no idea that something has gone

simply ignored. Also, the user has no idea that something has gone wrong.wrong.

14.

14. Modify the code of Modify the code of Macro2 Macro2 to include a more sophisticated error handler thus:to include a more sophisticated error handler thus:

Sub

SubMacro2()Macro2()

On Error GoTo

On Error GoTo ErrorHandler ErrorHandler ActiveCell.Of

ActiveCell.Offset(-5, fset(-5, -1).Range("A1").Sel-1).Range("A1").Selectect Exit Sub

Exit Sub

ErrorHandler: ErrorHandler:

MsgBox

MsgBox"You must start below Row 5""You must start below Row 5" End Sub

End Sub

15.

15. ThisThis timetimethe user is presented with a dialog box when something goes wrong. If there the user is presented with a dialog box when something goes wrong. If there isis

no error the line

no error the line Exit SubExit Sub causes the macro to finish after it has done its causes the macro to finish after it has done its job - otherwise the user job - otherwise the user

would see the message even if there were no

would see the message even if there were no error.error.

Improving Recorded

Improving Recorded MacrosMacros

The good way to learn the basics of VBA is to

The good way to learn the basics of VBA is to record a macro and see how record a macro and see how Excel writes its ownExcel writes its own

code. Often, though, recorded

code. Often, though, recorded macrosmacroscontain much more code than is necessary. Thecontain much more code than is necessary. The

following exercises demonstrate how you can improve and streamline code that has been

produced by a recorded macro.

Exercise 2: Improving on Recorded

Exercise 2: Improving on Recorded MacrosMacros This exercise shows that when

This exercise shows that when macrosmacros are recorded, often more code is generated thanare recorded, often more code is generated than necessary. It demonstrates the use of the With statement to précis the code.

necessary. It demonstrates the use of the With statement to précis the code.

1.

1. Select any cell or block of cells.Select any cell or block of cells.

2.

2. Start the macro recorder and cStart the macro recorder and call the macro FormatCells. The Relative References settingall the macro FormatCells. The Relative References setting

will not be relevant.

(36)

3.

3. Go toGo toFormat > Cells > FontFormat > Cells > Font and chooseand chooseTimes New RomanTimes New RomanandandRed Red ..

Go to

Go toPatternsPatternsand chooseand chooseYellow Yellow ..

Go to

Go toAlignmentAlignmentand chooseand chooseHorizontal, Center Horizontal, Center

Go to

Go toNumber Number and chooseand chooseCurrency Currency ..

4.

4. ClickClickOKOKand stop the recorder.and stop the recorder.

5.

5. Click theClick theUndoUndobutton (or button (or Ctrl+Z Ctrl+Z ) to undo your changes to ) to undo your changes to the worksheet.the worksheet.

6.

6. Select a block of cells and run theSelect a block of cells and run the FormatCellsFormatCellsmacro. Note that it can not be undone! Typemacro. Note that it can not be undone! Type

in the cells to check the result of the formatting.

7.

7. Look at the code:Look at the code:

Sub

SubFormatSelection()FormatSelection() Selection.NumberF

Selection.NumberFormat = ormat = "$#,##0.00""$#,##0.00" With

WithSelectionSelection .HorizontalAlignm

.HorizontalAlignment = ent = xlCenter xlCenter .VerticalAlignm

.VerticalAlignment = ent = xlBottomxlBottom .WrapText =

.WrapText = FalseFalse

.Orientation = 0 .Orientation = 0 .ShrinkToFit =

.ShrinkToFit = FalseFalse

.MergeCells =

.MergeCells = FalseFalse

End With

With

WithSelection.FontSelection.Font

.Name = "Times New Roman" .Name = "Times New Roman" .FontStyle = "Regular"

.FontStyle = "Regular" .Size = 10

.Size = 10 .Strikethrough =

.Strikethrough = FalseFalse

.Superscript =

.Superscript = FalseFalse

.Subscript = .Subscript = FalseFalse

.OutlineFont =

.OutlineFont = FalseFalse

.Shadow = .Shadow = FalseFalse

.Underline =

.Underline = xlUnderlineStyxlUnderlineStyleNoneleNone .ColorIndex = 3

.ColorIndex = 3 End With

End With

With

WithSelection.Interior Selection.Interior .ColorIndex = 6 .ColorIndex = 6 .Pattern = xlSolid .Pattern = xlSolid