## Advanced Topics in Excel :

Excel over view

The Date in Excel

Excel Conditional Formatting

Excel VLOOKUP

Excel HLOOKUP

Using Excel’s Goal Seek Feature

Create Excel Pivot Tables

Microsoft Excel Sort Feature

Introduction to Using Macros in Microsoft Excel 2003

Protect Excel Sheet

Data base in excel

Copy, paste special function

Data showing thru Chart / graph

Working with share workbook

Data filter

Subtotal

Validation

Find & replace

## Microsoft Excel – Course outline

Are you want to learn MS Excel online? A free training session available for short time. What topic you want to learn? comments here

 Question Y/N Question Y/N Write basic formulas, eg = A1*A2 Write formulas using brackets where required, eg =(A1+A2)/A3 Write basic functions, eg SUM, AVERAGE,MAX, MIN Selecting ranges with cells together and separate Understand Absolute and Relative References in formulas, eg =B7 + \$A\$3 Create simple charts Create Logical functions, eg IF, AND, OR, NOT Create a nested IF function (multiple IF statements in the same function) Concatenate (join) text Trap errors using IFERROR Paste Values from formulas Change Calculation method, eg Manual or Automatic Create custom formats for numbers and dates Use Conditional Formatting Change the Page Layout, eg Margins, Headers, Footers, Background, Orientation, Paper Size Use Scale to a Percentage or to Fit when Printing Use different Views, eg Page Break Preview, Page Layout Insert and delete columns, rows and worksheets Hide and unhide worksheets Move and copy worksheets to the same or a different workbook Freeze row and/or column titles Use Find and Replace to change formulas Sort a list (including multiple sorts) Use Filtering – Compound filters, Multiple value filters, Custom filters Modify charts – adding titles, data tables, Trend lines, Error bars, text boxes Create a VLOOKUP function in both it’s forms and know the difference between them Create CHOOSE, INDEX, MATCH functions Create reference functions, e.g. ROW, COLUMN, ADDRESS, INDIRECT, OFFSET Modify charts – changing shape styles, colors, fills, shape effects, themes Create, use, modify and delete range names Create range names using text labels or a selection Create worksheet specific range names Using range names in formula Protecting cells, ranges, worksheets and workbooks Use automatic Subtotals (rather than manually creating them) Create and use Relative Names for Subtotals Linking between Worksheets and Workbooks Consolidate data from many workbooks into one workbook Create Pivot Tables Use Pivot Tables: Group, calculate, Slicer Create Pivot Charts Use the data analysis tools Goal Seek and Solver Use macro

These are basic level MS Excel training outline, if you interested to learn advance level formula and formatting, also write us / comments here

## Goal Seek in MS Excel !

Goal Seek In MS Excel !

When you know the desired result of a single formula but not the input value the formula needs to determine the result, you can use the Goal Seek feature available by clicking,
Data —> What-If Analysis —> Goal Seek

When goal seeking, Microsoft Excel varies the value in one specific cell until a formula that’s dependent on that cell returns the result you want.

For example, use Goal Seek to change the interest rate in cell D7 incrementally until the payment value in D8 equals 7,500

# HLOOKUP

Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.

The H in HLOOKUP stands for “Horizontal.”

Syntax

HLOOKUP (lookup_value, table_array, row_index_num, range_lookup)

Lookup_value    is the value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text string.

Table_array    is a table of information in which data is looked up. Use a reference to a range or a range name.

• The values in the first row of table_array can be text, numbers, or logical values.
• If range_lookup is TRUE, the values in the first row of table_array must be placed in ascending order: …-2, -1, 0, 1, 2,… , A-Z, FALSE, TRUE; otherwise, HLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
• Uppercase and lowercase text are equivalent.
• You can put values in ascending order, left to right, by selecting the values and then clicking Sort on the Data Click Options, click Sort left to right, and then click OK. Under Sort by, click the row in the list, and then click Ascending.

Row_index_num    is the row number in table_array from which the matching value will be returned. A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on. If row_index_num is less than 1, HLOOKUP returns the #VALUE! error value; if row_index_num is greater than the number of rows on table_array, HLOOKUP returns the #REF! error value.

Range_lookup    is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Remarks

• If HLOOKUP can’t find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.
• If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

 Column –> Row A B C 1 Axles Bearings Bolts 2 4 4 9 3 5 7 10 4 6 8 11

 Formula Description (Result) =HLOOKUP(“Axles”,A1:C4,2,TRUE) Looks up Axles in row 1, and returns the value from row 2 that’s in the same column. (4) =HLOOKUP(“Bearings”,A1:C4,3,FALSE) Looks up Bearings in row 1, and returns the value from row 3 that’s in the same column. (7) =HLOOKUP(“B”,A1:C4,3,TRUE) Looks up B in row 1, and returns the value from row 3 that’s in the same column. Because B is not an exact match, the next largest value that is less than B is used: Axles. (5) =HLOOKUP(“Bolts”,A1:C4,4) Looks up Bolts in row 1, and returns the value from row 4 that’s in the same column. (11) =HLOOKUP(3,{1,2,3;”a”,”b”,”c”;”d”,”e”,”f”},2,TRUE) Looks up 3 in the first row of the array constant, and returns the value from row 2 in same column. (c)

# VLOOKUP

Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.

The V in VLOOKUP stands for “Vertical.”

Syntax

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

• If range_lookup is TRUE, the values in the first row of table_array must be placed in ascending order: …-2, -1, 0, 1, 2,… , A-Z, FALSE, TRUE; otherwise, HLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
• You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.
• The values in the first column of table_array can be text, numbers, or logical values.
• Uppercase and lowercase text are equivalent.

Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Remarks

• If VLOOKUP can’t find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
• If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
• If VLOOKUP can’t find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

The example uses values for air at 1 ATM pressure.

 Column–> Row A B C 1 Density Viscosity Temperature 2 0.457 3.55 500 3 0.525 3.25 400 4 0.616 2.93 300 5 0.675 2.75 250 6 0.746 2.57 200 7 0.835 2.38 150 8 0.946 2.17 100 9 1.09 1.95 50 10 1.29 1.71 0
 Formula Description (Result) =VLOOKUP(1,A2:C10,2) Looks up 1 in column A, and returns the value from column B in the same row (2.17) =VLOOKUP(1,A2:C10,3,TRUE) Looks up 1 in column A, and returns the value from column C in the same row (100) =VLOOKUP(.7,A2:C10,3,FALSE) Looks up 0.746 in column A. Because there is no exact match in column A, an error is returned (#N/A) =VLOOKUP(0.1,A2:C10,2,TRUE) Looks up 0.1 in column A. Because 0.1 is less than the smallest value in column A, an error is returned (#N/A) =VLOOKUP(2,A2:C10,2,TRUE) Looks up 2 in column A, and returns the value from column B in the same row (1.71)

## Computer .. Important shortcut keys !

Computer…. Important Shortcut Keys
System
CTRL+A. . . . . . . . . . . . . . . . . Select All
CTRL+C. . . . . . . . . . . . . . . . . Copy
CTRL+X. . . . . . . . . . . . . . . . . Cut
CTRL+V. . . . . . . . . . . . . . . . . Paste
CTRL+Z. . . . . . . . . . . . . . . . . Undo
CTRL+B. . . . . . . . . . . . . . . . . Bold
CTRL+U. . . . . . . . . . . . . . . . . Underline
CTRL+I . . . . . . . . . . . . . . . . . Italic
F1 . . . . . . . . . . . . . . . . . . . . . . Help
F2 . . . . . . . . . . . . . . . . . . . . . Rename selected object
F3 . . . . . . . . . . . . . . . . . . . . . Find all files
F4 . . . . . . . . . . . . . . . . . . . . . Opens file list drop-down in dialogs
F5 . . . . . . . . . . . . . . . . . . . . . Refresh current window
F6 . . . . . . . . . . . . . . . . . . . . . Shifts focus in Windows Explorer
F10 . . . . . . . . . . . . . . . . . . . . Activates menu bar options
ALT+TAB . . . . . . . . . . . . . . . . Cycles between open applications
ALT+F4 . . . . . . . . . . . . . . . . . Quit program, close current window
ALT+F6 . . . . . . . . . . . . . . . . . Switch between current program windows
ALT+ENTER. . . . . . . . . . . . . . Opens properties dialog
ALT+SPACE . . . . . . . . . . . . . . System menu for current window
ALT+¢ . . . . . . . . . . . . . . . . . . opens drop-down lists in dialog boxes
BACKSPACE . . . . . . . . . . . . . Switch to parent folder
CTRL+ESC . . . . . . . . . . . . . . Opens Start menu
CTRL+ALT+DEL . . . . . . . . . . Opens task manager, reboots the computer
CTRL+TAB . . . . . . . . . . . . . . Move through property tabs
CTRL+SHIFT+DRAG . . . . . . . Create shortcut (also right-click, drag)
CTRL+DRAG . . . . . . . . . . . . . Copy File
ESC . . . . . . . . . . . . . . . . . . . Cancel last function
SHIFT . . . . . . . . . . . . . . . . . . Press/hold SHIFT, insert CD-ROM to bypass auto-play
SHIFT+DRAG . . . . . . . . . . . . Move file
KamranMahar . . . . . . . . . . PaNoAkiLiaNs
SHIFT+F10. . . . . . . . . . . . . . . Opens context menu (same as right-click)
SHIFT+DELETE . . . . . . . . . . . Full wipe delete (bypasses Recycle Bin)
ALT+underlined letter . . . . Opens the corresponding menu
PC Keyboard Shortcuts
Document Cursor Controls
HOME . . . . . . . . . . . . . . to beginning of line or far left of field or screen
END . . . . . . . . . . . . . . . . to end of line, or far right of field or screen
CTRL+HOME . . . . . . . . to the top
CTRL+END . . . . . . . . . . to the bottom
PAGE UP . . . . . . . . . . . . moves document or dialog box up one page
PAGE DOWN . . . . . . . . moves document or dialog down one page
ARROW KEYS . . . . . . . move focus in documents, dialogs, etc.
CTRL+ > . . . . . . . . . . . . next word
CTRL+SHIFT+ > . . . . . . selects word
Windows Explorer Tree Control
Numeric Keypad * . . . Expand all under current selection
Numeric Keypad + . . . Expands current selection
Numeric Keypad – . . . Collapses current selection
¦ . . . . . . . . . . . . . . . . . . Expand current selection or go to first child
‰ . . . . . . . . . . . . . . . . . . Collapse current selection or go to parent
Special Characters
‘ Opening single quote . . . alt 0145
’ Closing single quote . . . . alt 0146
“ Opening double quote . . . alt 0147
“ Closing double quote. . . . alt 0148
– En dash. . . . . . . . . . . . . . . alt 0150
— Em dash . . . . . . . . . . . . . . alt 0151
… Ellipsis. . . . . . . . . . . . . . . . alt 0133
• Bullet . . . . . . . . . . . . . . . . alt 0149
® Registration Mark . . . . . . . alt 0174
© Copyright . . . . . . . . . . . . . alt 0169
™ Trademark . . . . . . . . . . . . alt 0153
° Degree symbol. . . . . . . . . alt 0176
¢ Cent sign . . . . . . . . . . . . . alt 0162
1⁄4 . . . . . . . . . . . . . . . . . . . . . alt 0188
1⁄2 . . . . . . . . . . . . . . . . . . . . . alt 0189
3⁄4 . . . . . . . . . . . . . . . . . . . . . alt 0190

PC Keyboard Shortcuts
Creating unique images in a uniform world! Creating unique images in a uniform world!
é . . . . . . . . . . . . . . . alt 0233
É . . . . . . . . . . . . . . . alt 0201
ñ . . . . . . . . . . . . . . . alt 0241
÷ . . . . . . . . . . . . . . . alt 0247

File menu options in current program
Alt + E Edit options in current program
F1 Universal help (for all programs)
Ctrl + A Select all text
Ctrl + X Cut selected item
Shift + Del Cut selected item
Ctrl + C Copy selected item
Ctrl + Ins Copy selected item
Ctrl + V Paste
Shift + Ins Paste
Home Go to beginning of current line
Ctrl + Home Go to beginning of document
End Go to end of current line
Ctrl + End Go to end of document
Shift + Home Highlight from current position to beginning of line
Shift + End Highlight from current position to end of line
Ctrl + f Move one word to the left at a time
Ctrl + g Move one word to the right at a time

MICROSOFT® WINDOWS® SHORTCUT KEYS
Alt + Tab Switch between open applications
Alt +
Shift + Tab
Switch backwards between open
applications
Alt + Print
Screen
Create screen shot for current programu
Ctrl + Alt + Del Reboot/Windows® task manager
Ctrl + Esc Bring up start menu

## Excel in MS Excel

Now a day there are many software (ERP System) such as SAP, Oracle etc as well as some local developed ERP System adopted by every Company to develop / keep check on their systems, way of working, Inventory, Purchase, Production, Sales, Income Statement, Balance Sheet etc, but importance of MS Excel in any working office always there.

MS Excel is big tool of daily working, you are well come to ask or provide any tips in MS Excel. May be I start tip of day in short period.

You may contact at wehelpf9@gmail.com

## How to convert a numeric value into English words in Excel

How to convert a numeric value into English words in Excel

This Addin will make it easier to use. This Addin can be installed on any computer having Excel 2007 and above versions. Once Installed you can use SpellCurr() formula in any excel file on that computer.

However if you send file to another person you need to send the Addin along and ask that person to install it on his/her machine so that your formula can work there.

Method 1: Cell reference

You can refer to other cells in the workbook. For example, enter the number 32.50 into cell A1, and type the following formula into another cell:

=SpellCurr(A1)

Method 2: Insert Function

To use Insert Function, follow these steps:

1.                  Select the cell that you want.

2.                  Click Insert Function on the Standard toolbar.

3.                  Under Or select a category, click User Defined.

4.                  In the Select a function list, click SpellCurr, and then click OK.

5.                  Enter the number or cell reference that you want, and then click OK.