Who says Excel Functions for Numbers? TEXT FUNCTIONS!

I remember my first class in Excel and my tutor told me that MS Word originated from a Typewriter! MS PowerPoint Originated from the Overhead Projectors !MS Access Originated from a Filing Cabinet and MS Excel originated from a calculator. When I told my tutor i was scared of Excel because I am not good with figures! She told me that I was going to have a big problems with my girlfriends in the future!

I think there was some kind of misunderstanding between us as her interpretation of figure was totally different than mine! Anyways now that I have become a trainer myself and when i see people being scared of MS Excel as may be they had the same problem as me where either they did not find Maths Comfortable or may be they had a tutor like mine who had a habit of misunderstanding me. I tell the them that imagine Excel to be a Large Calculator which is there to make your life lot more easier! Well lots of people just think Excel as a calculator and don't realised that there is a great more deal MS Excel can do for them. Although there are lots and lots of Functions that are suitable for various industry but i would like to focus on the TEXT Functions:

Unfortunately, I am not going to explain each and everyone. I'll explain some and then may be refer you to some websites where you can download free course manuals and learn more of the functions.

The Functions I want to talk about are:

UPPER - This capitalises all the letters in a specific cell

lower - This make all the letters small case in a specific cell

Proper - this is like the Title case in Word where first letter of each word becomes capital

Left - This extracts certain no. of characters from the left in a cell

Right - This extracts certain no. of characters from the Right in a cell

Mid - This extracts certain no. of characters from the middle in a cell
LEN - Tells how many characters a word has in a cell

Rept - Repeats the word in a cell specific no. of times

Instead of me going through all of them individually let me attach a screen shot which gives example of all of the above.

If you need to know more type of function please feel free to go to the Mouse Training Website and download their Free training Manuals

Many thanks

Raj

HELP! I'm Lost in this Lookup World!!

I go to parties and all I hear people saying to each other that using Index and Match is better than VLookup! Obviously these are not the parties i used to go when i was a young and viberent man. having no clue about any of teh terms they used I would shake my head in agreement and say "Yes indeed! I totally agree with you!"

I knew what Vlookup was all about but had no idea what the other two cousions of Vlookup people were talking about.

So, I decided to do a research on these and would like to share my newly found knowledge and hope you benifit from them.

Well let's start off with something that I already know about and that is Vlookup!

Vlookup function is a very useful function when you want to retreive data from a very long list that may be on the same sheet or another sheet or even another workbook. Let me explain by giving you a simple and realistic example.

Let's say that you have three sheets namly Invoice, Customers and Products. On the Invoice sheet you want to put the Customer ID and magically want the Title, First Name, Last Name, Full Address to appear on the other cells. Or may be Enter the product Code and again magically get all the relevant product information such as Unit Price, Product description, etc... to appear for you.

So How and where does the all the magical information come from? If you are as smart as I am you'd be able to guess that the information comes from the other sheets namely Customers and Products. So how does it do what it does and more importantly where do you buy this Magic Wand that helps us to make our life so easy. I don't know about you but my genearl experience of life is that Nothing comes easy in life. You have to work hard to get what you want in life. Now I must have got you really concerend and you must have started thinking that I'll be asking you to go out on this epic adventure, like Harry Potter or Lord Of the Ring in search for our "Magic Wand" !

The good news is that it is not as intense as you might expect it to be.

Please see teh three sreen shots below of my scenario:

So on my Invoice sheet when I type in C002 in cell J1 I want all the relevant information to be filled in for that customer. Following are the steps to achive this.

Step 1: On the Invoice sheet I click on J2 and start typing: =vlookup(

automatically a nice little box comes up and I see Lookup Value, Table_Array, Col_Index_Num, Range_Lookup

So what do these mean and, more importantly, when do I get to use the Magic wand?

Lookup Value is the cell or the text that needs to be referred to.

Table Array is the entire table where the information is situated.

Col_Index_Num is the column number in the table array where you want the data to be extracted from

Range Lookup is either 0 or 1 depending on if you want an exact match or an approximate match.

Let's put this in contecxt with our example:

In my Invoice sheet if I type in e.g. C002 on cell J1

J1 will be the Lookup value as J2 will always be referring to J1 to see what value is in J1. Just to let you know taht you can also enter a specific text surrounded by the inverted commas if you are likely to be searching ofr teh same text all teh time. That is not teh case in our example.

Table array in our case is NOT on the same sheet so we will have to click on the Customers Sheets and select teh entire table (A1:G3)

Column Index Num is going to be 2 as the Title in our table Array is situated on the second Column.

Range Lookup will be 0 or you can type False which means that we are looking sor an Exact match. This will ensure that if the entery in the Invoice sheet on cell J1 has not been enterted exactly same as in teh table array then instead of working out the closest match it will come up with an error.

So to summarize all the above arguments of the Vlookup function on Invoice sheet in Cell J2 we willl type in

=Vlookup(J1, Customers!A1:G3, 2,0)

Note that you don't have to type "Customers!" because as soon as you click on the customers sheet this should be entered for you automatically.

You will repeat the steps for all the other cells in Invoice sheet to get the details for rest of the fields such as First name, Surname, Address 1, Address 2, Post Code.

Those of you who understand what dollars do in Formulae and functions you can even put them dollars for J1 and A1:G3 in the original Vlookup on the J2 Cell and then simply copy and paste the cell down to J3: J7. All you’ll have to change is the Column Index Number depending on the position of the headings in our table array.

Similar can be done for the Products information on the invoice sheet.

I hope this all makes sense so far. Now the question is if we can get the result we need from Vlookup then what is all the fuss about the other two functions called Index and Match?

Well as with all great things Vlookup has it’s drawback. And the drawback is quite a significant one. Notice that Customer ID is the Lookup value in our Vlookup function. And coincidently in our table array on the customers sheet Customer ID happens to be our first column. Well what that means is that had our first column in our Customer sheet been anything else Vlookup would not have worked. The problem we have is that Vlookup can only look at the left most (first) column in the table array and match that with the Lookup Value and then provide you the answer. In our case this seemed to work perfectly but then there are cases when it doesn’t work at all and Vlookup’s cousins Match and Index come to its rescue.


Let’s see the example where Vlookup will not work:

Let’s say that you knew the name and wanted to find the customer ID! That would be a problem! Because Vlookup will still be looking for first column i.e. Customer ID whereas we wanted to actually to look into the Second column to find us the data in the first column.

So how do we achieve this?

MATCH(lookup_value,lookup_array,match_type)
Lookup_value is the value you use to find the value you want in a table.
Lookup_array is a contiguous range of cells containing possible lookup values.
Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order.

If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order.
If match_type is omitted, it is assumed to be 1.

So in our example we will type:

=MATCH(B6,B2:B3,0)

Notice that for the lookup array must be single column list. So if we had chosen A2:C3 you’ll get an error.

So the result of the Match function will be 2 because what Match tells you is the Row position of the entry in our Lookup value cell.

How helpful is this? Well not very much! So has Match Function disappointed us? Well Not really. At least it is telling us which Row number

Let’s See what Index Function can do for us.

=INDEX(data range, row number, column number)

Data range is the where you are looking into
Row Number is ,well, the row number
Column Number is well obviously the column number.

In our example the data range is easy to find that is the entire table. (A2:C3)
Column number is also easy to find as we know that we need the Customer ID to be extracted and that is in Column 1 in our data range , therefore, 1.
Although Row number in our example is not that difficult to find as we know that it is 2 but then the problem is that if we manually type 2 there the name we entered in cell B6 which is currently Mary Jones to john Smith who happens to be on row 1 unfortunately the result on cell B7 will not update as it will still be looking for Row 2 whereas the result should be coming from row 1.

So we need something in the Index Function that will automatically calculate the row number without us having to change it manually all the time. In addition to that if you have 20,000 rows of data would you manually want to search and type the row number yourself? I am hoping your answer is NO!

So what is the solution. That is where these Index and Match functions work in conjunction to get your desired result.

So your enter the Match function inside the Index function.

In our example the function would be something like this:

=INDEX(A2:C3,MATCH(B6,B2:B3,0),1)

So Match function tells the Index Function which row needs to be retrieved in order to get the correct result.


I hope this makes things clear for you!

Now for the BIGGEST question of All!! Where is that Blooming Magic Wand???? Well you don’t need one by understanding whatever is mentioned above you would be a proud owner of THREE Magic Wands: VLOOKUP, MATCH AND INDEX!!

I owe my newly founded knowledge to a company called Mouse Training whose free course manuals I downloaded and learnt the skills.

If you would like to download any of the MS Office step by step guides for Office 2003, 2007 and even 2010 then please feel free to download them from Mouse Training Free Manuals Link!

Well I am one of the proud MOUSE of the MOUSE training to get Trained!;)

Raj

A - Z of Excel Shortcuts (Except E,J,M,Q,T)

It can be a daunting task to remember all the shortcut keys in Excel. Although Office 2007 has brought in a new feature called Access keys which are for the people who have a terrible memory and can't remember the shortcut keys but I still like my old shortcut keys and feel privileged that I have been blessed with a excellent memory ONLY to remember the Office shortcut keys! When it comes to remembering where I have kept my keys unfortunately I don't have a shortcut key that can help me retrieve them!

I think the trick with remembering the shortcut keys is that you have to keep on using them on a regular basis in order for you not to forget them. In my case, I am a trainer and everyday I have to let people know the shortcut keys for them to be more productive.

I would like to share my invaluable knowledge and experience of the Excel shortcut keys.

My suggestion is that the best way to remember the shortcut keys is to print out the list of most common ones and stick it next to your workstation. Whenever you want to use the feature simply refer to the list until you don't have to look at the list at all! It is similar to driving but of course not exactly the same. Can you imagine every time you have to change the gear you stare at it. Who on earth will be keeping eye on the road? the Driving Instructor!!!

KEY	DESCRIPTION
CTRL+(	Unhides any hidden rows within the selection.
CTRL+)	Unhides any hidden columns within the selection.
CTRL+&	Applies the outline border to the selected cells.
CTRL+_	Removes the outline border from the selected cells.
CTRL+~	Applies the General number format.
CTRL+$	Applies the Currency format with two decimal places (negative numbers in parentheses).
CTRL+%	Applies the Percentage format with no decimal places.
CTRL+^	Applies the Exponential number format with two decimal places.
CTRL+#	Applies the Date format with the day, month, and year.
CTRL+@	Applies the Time format with the hour and minute, and AM or PM.
CTRL+!	Applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values.
CTRL+-	Displays the Delete dialog box to delete the selected cells.
CTRL+*	Selects the current region around the active cell (the data area enclosed by blank rows and blank columns). In a PivotTable, it selects the entire PivotTable report.
CTRL+:	Enters the current time.
CTRL+;	Enters the current date.
CTRL+`	Alternates between displaying cell values and displaying formulas in the worksheet.
CTRL+'	Copies a formula from the cell above the active cell into the cell or the Formula Bar.
CTRL+"	Copies the value from the cell above the active cell into the cell or the Formula Bar.
CTRL++	Displays the Insert dialog box to insert blank cells.
CTRL+1	Displays the Format Cells dialog box.
CTRL+2	Applies or removes bold formatting.
CTRL+3	Applies or removes italic formatting.
CTRL+4	Applies or removes underlining.
CTRL+5	Applies or removes strikethrough.
CTRL+6	Alternates between hiding objects, displaying objects, and displaying placeholders for objects.
CTRL+7	Displays or hides the Standard toolbar.
CTRL+8	Displays or hides the outline symbols.
CTRL+9	Hides the selected rows.
CTRL+0	Hides the selected columns.
CTRL+A	Selects the entire worksheet. If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet. When the insertion point is to the right of a function name in a formula, displays the Function Arguments dialog box. CTRL+SHIFT+A inserts the argument names and parentheses when the insertion point is to the right of a function name in a formula.
CTRL+B	Applies or removes bold formatting.
CTRL+C	Copies the selected cells. CTRL+C followed by another CTRL+C displays the Microsoft Office Clipboard.
CTRL+D	Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
CTRL+F	Displays the Find dialog box. SHIFT+F5 also displays this dialog box, while SHIFT+F4 repeats the lastFind action.
CTRL+G	Displays the Go To dialog box. F5 also displays this dialog box.
CTRL+H	Displays the Find and Replace dialog box.
CTRL+I	Applies or removes italic formatting.
CTRL+K	Displays the Insert Hyperlink dialog box for new hyperlinks or theEdit Hyperlink dialog box for selected existing hyperlinks.
CTRL+L	Displays the Create List dialog box.
CTRL+N	Creates a new, blank file.
CTRL+O	Displays the Open dialog box to open or find a file. CTRL+SHIFT+O selects all cells that contain comments.
CTRL+P	Displays the Print dialog box.
CTRL+R	Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
CTRL+S	Saves the active file with its current file name, location, and file format.
CTRL+U	Applies or removes underlining.
CTRL+V	Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you cut or copied an object, text, or cell contents.
CTRL+W	Closes the selected workbook window.
CTRL+X	Cuts the selected cells.
CTRL+Y	Repeats the last command or action, if possible.
CTRL+Z	Uses the Undo command to reverse the last command or to delete the last entry you typed. CTRL+SHIFT+Z uses the Undo or Redo command to reverse or restore the last automatic correction when AutoCorrect Smart Tags are displayed.

What is the Function of the Function keys?

Answer:

KEY	DESCRIPTION
F1	Displays the Help task pane. CTRL+F1 closes and reopens the current task pane. ALT+F1 creates a chart of the data in the current range. ALT+SHIFT+F1 inserts a new worksheet.
F2	Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off. SHIFT+F2 edits a cell comment.
F3	Pastes a defined name into a formula. SHIFT+F3 displays the Insert Function dialog box.
F4	Repeats the last command or action, if possible. CTRL+F4 closes the selected workbook window.
F5	Displays the Go To dialog box. CTRL+F5 restores the window size of the selected workbook window.
F6	Switches to the next pane in a worksheet that has been split (Windowmenu, Split command). SHIFT+F6 switches to the previous pane in a worksheet that has been split. CTRL+F6 switches to the next workbook window when more than one workbook window is open. NOTE When the task pane is visible, F6 and SHIFT+F6 include that pane when switching between panes.
F7	Displays the Spelling dialog box to check spelling in the active worksheet or selected range. CTRL+F7 performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press ESC.
F8	Turns extend mode on or off. In extend mode, EXT appears in the status line, and the arrow keys extend the selection. SHIFT+F8 enables you to add a non-adjacent cell or range to a selection of cells by using the arrow keys. CTRL+F8 performs the Size command (on the Control menu for the workbook window) when a workbook is not maximized. ALT+F8 displays the Macro dialog box to run, edit, or delete a macro.
F9	Calculates all worksheets in all open workbooks. F9 followed by ENTER (or followed by CTRL+SHIFT+ENTER for array formulas) calculates the selected a portion of a formula and replaces the selected portion with the calculated value. SHIFT+F9 calculates the active worksheet. CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation. CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated. CTRL+F9 minimizes a workbook window to an icon.
F10	Selects the menu bar or closes an open menu and submenu at the same time. SHIFT+F10 displays the shortcut menu for a selected item. ALT+SHIFT+F10 displays the menu or message for a smart tag. If more than one smart tag is present, it switches to the next smart tag and displays its menu or message. CTRL+F10 maximizes or restores the selected workbook window.
F11	Creates a chart of the data in the current range. SHIFT+F11 inserts a new worksheet. ALT+F11 opens the Visual Basic Editor, in which you can create a macro by using Visual Basic for Applications (VBA). ALT+SHIFT+F11 opens the Microsoft Script Editor, where you can add text, edit HTML tags, and modify any script code.
F12	Displays the Save As dialog box.

Other Useful Shorcut keys:

KEY	DESCRIPTION
ARROW KEYS	Move one cell up, down, left, or right in a worksheet. CTRL+ARROW KEY moves to the edge of the current data region in a worksheet. SHIFT+ARROW KEY extends the selection of cells by one cell. CTRL+SHIFT+ARROW KEY extends the selection of cells to the last nonblank cell in the same column or row as the active cell. LEFT ARROW or RIGHT ARROW selects the menu to the left or right when a menu is visible. When a submenu is open, these arrow keys switch between the main menu and the submenu. DOWN ARROW or UP ARROW selects the next or previous command when a menu or submenu is open. In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of options. ALT+DOWN ARROW opens a selected drop-down list.
BACKSPACE	Deletes one character to the left in the Formula Bar. Also clears the content of the active cell.
DELETE	Removes the cell contents (data and formulas) from selected cells without affecting cell formats or comments. In cell editing mode, it deletes the character to the right of the insertion point.
END	Moves to the cell in the lower-right corner of the window when SCROLL LOCK is turned on. Also selects the last command on the menu when a menu or submenu is visible. CTRL+END moves to the last cell on a worksheet, in the lowest used row of the rightmost used column. CTRL+SHIFT+END extends the selection of cells to the last used cell on the worksheet (lower-right corner).
ENTER	Completes a cell entry from the cell or the Formula Bar, and selects the cell below (by default). In a data form, it moves to the first field in the next record. Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command. In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often theOK button). ALT+ENTER starts a new line in the same cell. CTRL+ENTER fills the selected cell range with the current entry. SHIFT+ENTER completes a cell entry and selects the cell above.
ESC	Cancels an entry in the cell or Formula Bar. It also closes an open menu or submenu, dialog box, or message window.
HOME	Moves to the beginning of a row in a worksheet. Moves to the cell in the upper-left corner of the window when SCROLL LOCK is turned on. Selects the first command on the menu when a menu or submenu is visible. CTRL+HOME moves to the beginning of a worksheet. CTRL+SHIFT+HOME extends the selection of cells to the beginning of the worksheet.
PAGE DOWN	Moves one screen down in a worksheet. ALT+PAGE DOWN moves one screen to the right in a worksheet. CTRL+PAGE DOWN moves to the next sheet in a workbook. CTRL+SHIFT+PAGE DOWN selects the current and next sheet in a workbook.
PAGE UP	Moves one screen up in a worksheet. ALT+PAGE UP moves one screen to the left in a worksheet. CTRL+PAGE UP moves to the previous sheet in a workbook. CTRL+SHIFT+PAGE UP selects the current and previous sheet in a workbook.
SPACEBAR	In a dialog box, performs the action for the selected button, or selects or clears a check box. CTRL+SPACEBAR selects an entire column in a worksheet. SHIFT+SPACEBAR selects an entire row in a worksheet. CTRL+SHIFT+SPACEBAR selects the entire worksheet. · If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the entire worksheet. · When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet. ALT+SPACEBAR displays the Control menu for the Excel window.
TAB	Moves one cell to the right in a worksheet. Moves between unlocked cells in a protected worksheet. Moves to the next option or option group in a dialog box. SHIFT+TAB moves to the previous cell in a worksheet or the previous option in a dialog box. CTRL+TAB switches to the next tab in dialog box. CTRL+SHIFT+TAB switches to the previous tab in a dialog box.

I hope you found these shortcut keys useful. I would request you to go to Mouse Training Website and download all your Office 2003 and 2007 manuals with some quick reference cards FREE OF CHARGE! Office 2010 manuals will be uploaded in the near future! I know Free doesn't always sound right! It sometimes sounds too good to be true! Don't worry we are there to make money like any other business but by providing FREE Manuals we are doing our bit for the society!

If you want to see the shortcut keys for the other packages please go to the relevant blog e.g. MS Word, PowerPoint and Outlook!

Any constructive criticism will be appreciated!