My Small World

Alternative for SUMIF and SUMIFS - Part 2 (SUMPRODUCT)

noreply@blogger.com (Arvind) — Mon, 20 Apr 2020 07:06:00 +0000

We already know the primary challenge of using SUMIF or SUMIFS i.e. unable to sum more than a single column or row as a sum range. Many of us in the course of our excel usage may have experienced that SUMIFS has a bigger disadvantage when used across workbooks i.e. the source workbook should be open inorder to enable to computation in the destination workbook. If the source workbook remains closed, the result in the destination workbook will be "#VALUE".

In my previous post, the alternative suggested not only does overcome the "#VALUE" challenge but also allow users to sum a larger range. In this post, I was able to decipher yet another alternative. I'm sure expert users may already be using this however to help other users, this post can be a ray of hope.

We may have used the SUMPRODUCT function to calculate weighted average. Extrapolating the usage of SUMPRODUCT with condition or conditions can help you achieve the SUMIFS goal.

Syntax:

=SUMPRODUCT( (Condition1) * (Condition2) * (ConditionN) * (sumrange))

The table below is an example where in we can compute the total of Range1 and Range2 based on a combination of Criteria1 and Criteria2.

Source Table (may or may not be in the same Workbook)

Suppose, we need to find the sum of the Range1 and Range2 if Criteria1 is "A" and Criteria2 is "w" let us see the syntax of the SUMPRODUCT function to be used.

If is it is in the same file in Sheet1:

=SUMPRODUCT((Sheet1!$B$2:$B$9=B2)*(Sheet1!$C$2:$C$9=C2)*Sheet1!$D$2:$E$9)

if is is in a different file (named "Sample.xlsx") in Sheet1

=SUMPRODUCT(([Sample.xlsx]Sheet1!$B$2:$B$9=I18)*([Sample.xlsx]Sheet1!$C$2:$C$9=J18)*[Sample.xlsx]Sheet1!$D$2:$E$9)

This will give you the identify the values against the combination of "A" and "w" and give the result as 14.

In this case you don't have to use the array feature unlike my previous post.

Hope this helps you in using your excel workbooks more efficiently and flawlessly.

Excel Keyboard Shortcuts - Handy during day to day work

noreply@blogger.com (Arvind) — Fri, 15 Mar 2019 13:06:00 +0000

I've had few posts earlier on few excel shortcuts.

So, here I am, back with some more interesting shortcuts which will make your excel spreadsheet experience simpler.

Let me start with some "CTRL" combinations which will be very handy for formating cells to match your requirements:

CTRL + 1 : Opens the "Format Cells" dialog box
CTRL + 2 : is to make the contents of the cell BOLD (this is a toggle key, to unbold use same CTRL + 2)
CTRL + 3 : is to make the contents of the cell ITALICS (this is a toggle key)
CTRL + 4 : is to underline the contents of the cell (this is a toggle key)
CTRL + 5 : is to ~~strike through~~ the contents of the cell (this is a toggle key)
CTRL + 9 : is to hide the entire row (to unhide use SHIFT + CTRL + 9)
CTRL + 0 : is the hide the entire column (to unhide use SHIFT + CTRL + 0)
CTRL + ~ : is to show or hide formula (this is a toggle key)
CTRL + - : brings up the delete cells dialog box
CTRL + + : brings up the insert cells dialog box

Another set of shortcuts is with the combination of SHIFT and CTRL:

SHIFT + CTRL + 1 : converts the number in the cell to decimal format with 2 decimal places
SHIFT + CTRL + 2 : converts the number in the cell to time format (your default system format)
SHIFT + CTRL + 3 : converts the number in the cell to date format (your default system format)
SHIFT + CTRL + 4 : converts the number in the cell to currency format (your default system format)
SHIFT + CTRL + 5 : converts the number in the cell to percentage format
SHIFT + CTRL + 6 : converts the number in the cell to scientific format (your default system format)
SHIFT + CTRL + 7 : adds outline border to the selected cell(s)
SHIFT + CTRL + - : Removes the border from the select cell(s)

Get used to these common shortcuts till I manage to compile few more.

You can find more such posts on the internet. So make sure you refer and make best use of the shortcuts.

Alternative for SUMIF and SUMIFS

noreply@blogger.com (Arvind) — Fri, 29 Jul 2016 11:59:00 +0000

Ever wondered while using "SUMIF" function, the sum range cannot be more than one column!! This indeed is a big drawback while using a large data set where you need to sum matching certain conditions.

I often end up using multiple sumif with "+" which makes the formula very long. So was thinking of some alternative that can make life easier.

Thanks to the search engines these days, I did come across a workaround.

Refer to the above image:

In the above example, I have a table with 4 columns where H1 and H2 are identifiers. H3 and H4 is the data which needs to be summed up.

Condition 1 (alternative for SUMIF):
I need to sum values of H3 and H4 where the identifier H2 is "Y".
The formula will be =SUM(IF(B2:B5=B9,C2:D5)) followed by Ctrl + Shift + Enter which will make it a array formula. The final formula will look like this:
{=SUM(IF(B2:B5=B9,C2:D5))}

Condition 2 (alternative for SUMIFS):
I need to sum values of H3 and H4 where the identifier H1 is "N" and H2 is "Z".
The formula will be =SUM(IF(A2:A5&B2:B5=B10,C2:D5)) followed by Ctrl + Shift + Enter which will make it a array formula. The final formula will look like this:
{=SUM(IF(A2:A5&B2:B5=B10,C2:D5))}

Try this out in a larger data set and check your results.

Disclaimer: This will work only in a array i.e. without "Ctrl + Shift + Enter" , the result may be "#N/A"

Solving issues with "VLOOKUP"

noreply@blogger.com (Arvind) — Thu, 24 Dec 2015 11:50:00 +0000

VLOOKUP syntax

We often encounter "#N/A" while using VLOOKUP function to compare two lists. One of the most common problems that give rise to this error is extra spaces either to the end or to the beginning of the string.

What's the way out?

The simplest way out is to use "TRIM" function as a sub function.

Example

While trying to use VLOOKUP in the normal course, the result will be "#N/A", whereas with TRIM nested in the VLOOKUP function will give the positive result.

(TRIM function removes the unwanted spaces in the beginning and end of a string.)

Identify duplicate values in a list using "Countif"

noreply@blogger.com (Arvind) — Sat, 08 Feb 2014 17:43:00 +0000

Though MS Excel today allows to easily eliminate duplicate values in a list, have you ever tried to identify the number of entries that gets repeated in an excel list?

In the above example, we can identify the repeat entries using "COUNTIF" function.

Option 1

formula: =COUNTIF($D$1:D1,D1)
Explanation: This formula counts the occurrence of the value in the list i.e. on the first occurrence, the result will be one and on the second it will be 2 and like wise. (see the figure below)

Option 2

formula: =COUNTIF($D$1:$D$8,D1)

Explanation: This formula counts the total number of repetitions. If an entry appears thrice in a list, then the result will be 3 in all the occurrences of that entry. (see the figure below)

So start using whichever is convenient to you... :)

Power of Data Validation - 2

noreply@blogger.com (Arvind) — Thu, 03 Jan 2013 12:18:00 +0000

Have you ever wondered if we can have a dynamic drop down list i.e. a drop down using data validation that gets updated automatically with any change in the source of the drop down...

I often faced problems having a dynamic drop down list but ultimately I was able to find a solution to my problem.

Step 1:

Format the source list as a table and name the table (here I've named the table as "Fruits")

Step 2:

Go to Formulas -> Name Manager and click on New

Define any name in the dialog box

In the example, I've named it as "Names". Then click on Refers to field and select the table in the excel sheet. The refer to will appear as =TableName[ColumnName]

Step 3:

Now create the drop down list using data validation and mention the source as the name you defined. In this case "Names"

Here you go. The drop down list hence created is dynamic. Any addition or deletion in the source table of the drop down options, the validated cell gets updated automatically...!!

Excel is great!

http://blog.arvindkumar.com/2010/05/custom-validation-in-excel.html

http://blog.arvindkumar.com/2012/03/data-validation-in-excel.html

Power of Data Validation - 1

noreply@blogger.com (Arvind) — Tue, 02 Oct 2012 04:02:00 +0000

In a series where I would like to present on the power of "Data Validation" option in MS Excel, the first and commonly used function is creation of a drop down list in a cell.

We often come across situation where we feel the necessity of having a drop down list in a worksheet where the inputs are predefined and the user gets ready option to populate the cell. The easiest way to create such a dropdown is typing out the list values in the data validation -> list option.

Step 1: Go to Data -> Data Validation -> Data Validation...

Step 2: Select "List" option in the "Allow" drop down box

Step 3: Write down the desired list values seperating them with comma in "Source" box

Result: You get the drop down with the desired options in the intended cell

What if you intend to create a long list where the entries are too much? Don't worry, excel offers you solution for this as well. Instead of the "Step 3" listed above, follow the step as below:

Step 3 (Alternate): Create a list in any given column in the same worksheet. Then select the listed range in the "Source" box

Limitation: This option is only possible when the source list and the cell where drop down is intended are on the same worksheet.

Don't worry, Excel offers a solution even to this limitation - "Named Range"!!

Step A: Go to a new work sheet and list the contents you want to feature in the dropdown
Step B: Type any name for the list in the "Name" box as displayed in the figure below

You can review such named lists in a worksheet by going to Formulas -> Name Manager. The "Name Manager" dialogue box displays the all named ranges in a given workbook. This can be used in case you wish to modify any list in the workbook.

Step 3 (Alternate): Now go to you original worksheet where you wish to create a drop down list. In the source option write the name of the intended list (in the example: "Greek")

So here you go... now you are ready to create drop down lists in excel!

For any queries/clarifications do write in to email@arvindkumar.com

Watch out for more on the "Power of Data Validation" series!!

Related posts:

http://blog.arvindkumar.com/2010/05/custom-validation-in-excel.html

http://blog.arvindkumar.com/2012/03/data-validation-in-excel.html

Printing Directory Structure in Windows

noreply@blogger.com (Arvind) — Sun, 23 Sep 2012 16:29:00 +0000

I had been wondering how can I get a complete stock of the directories as well as contents on my system. In this endeavor Google came to my rescue and directed me to a post where I could find a DOS command to print the directory structure in Windows environment.

For Example:

If you need to know the contents of the C drive, then go to the command prompt and type
"dir C:\ /s >output.txt"

where:

dir - the dos command for displaying the directories
C:\ - the location of which you want the directory structure
/s - the dos command extension for displaying not only the directories but the files as well
>output.txt - refers to the name of output file (in this case output.txt) which will be placed at the root of the folder where the command is getting executed. In case you want the output to your specified location, then include the complete path of the destination after ">".

Source: http://superuser.com/questions/258287/what-is-a-good-tool-to-export-a-directory-structure

Data Validation in Excel

noreply@blogger.com (Arvind) — Thu, 15 Mar 2012 17:34:00 +0000

(in continuation of my earlier post: "Custom Validation in Excel")

Today I seriously recognised the power of data validation. I was stuck in a situation wherein I had to create a template wherein the user needs to enter a date, however it the date cannot be a Saturday or a Sunday. After a brief "R&D", I realized using "data validation" was the apt option.

Go to Data -> Data Validation

In the dialogue box which pops up, choose "Custom" from the drop down under Settings -> Validation Criteria -> Allow

In the new field "Formula", just enter the criteria you want to achieve. In my case, I used "=WEEKDAY(A2,2)<6"

Then, you may click on the "Error Alert" tab and may put any customized error alert like the picture below:

So, now the desired cell is validated and user can only enter a weekday.

Another, popular validation that I often use is the "List" wherein one can easily create a drop-down list

Just mention the options you want in your drop-down list under the "Source" field separating with "commas". In the picture above, the drop-down list would be list of alphabet from A to E.

Try experimenting further...! :-)

Note:
"Weekday" functions returns the serial number of the given date in a week.
Syntax:WEEKDAY(cell reference,number format)
Number Format Options:
1 :1 (Sunday) through 7 (Saturday)
2 :1 (Monday) through 7 (Sunday).
3 :0 (Monday) through 6 (Sunday).
11 :1 (Monday) through 7 (Sunday).
12 :1 (Tuesday) through 7 (Monday).
13 :1 (Wednesday) through 7 (Tuesday).
14 :1 (Thursday) through 7 (Wednesday).
15 :1 (Friday) through 7 (Thursday).
16 :1 (Saturday) through 7 (Friday).

Extending options to Pivot Table

noreply@blogger.com (Arvind) — Mon, 12 Mar 2012 17:28:00 +0000

Excel 2007 onwards, playing around with Pivot Table has become even more user friendly. Pivot was seemingly a tool preferred by advanced users of excel but today its a child's play :)

Extended options over right click has made this a must use tool for all users who are required to handle a reasonable amount of data.

For eg:

"Summarize Values By" on right click gives you the option to summarize data by sum or count or average or to choose from all available options. Earlier, one had to be conversant with the options to actually use it.

"Show Values As" on right click allows to apply calculations to the data area.

Microsoft is making more and more features available for average / less than average users who actually are keen to use the features. Lets all of us make use of this.

Note: In case my readers want, I can explore on writing a basic step by step guide on basics of Pivot Table!

IFERROR funtion

noreply@blogger.com (Arvind) — Sun, 11 Dec 2011 15:28:00 +0000

We often tend to encounter instances while using formulae performing day to day operations where the formulae results in an error. Often, some basic users prefer manually correcting those errors and so advance users go one step ahead using a combination of "IF" & "ISERROR" functions. Even I'm one of those users who prefer using the "IF" & "ISERROR" combination until I came across the "IFERROR" function.

Syntax: IFERROR(value,value_if_error)

According to Microsoft, "IFERROR" evaluates #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! type of errors.

Say: Cell A1 contains a numeric value and Cell A2 contains 0 as value. You intend to perform a simple operation A1 divided by A2 (=A1/A2). In our example, the result would be #DIV/0!. Now, lets redo this simple division using "IFERROR" function. The formula can be written as "=IFERROR(A1/A2,0)" wherein the formula would asses that if the result of A1/A2 is an error the formula should put in 0 instead of the error.

This is indeed very helpful function which can be used with ease to perform various operations in an Excel sheet.

Turn Off AutoRecover For Individual Workbooks

noreply@blogger.com (Arvind) — Mon, 29 Aug 2011 05:21:00 +0000

In Excel 2007 and 2010, there is a feature called AutoRecover that saves your open workbooks at regular intervals. In the event of a power failure or computer crash or freeze up, this can help you recover your unsaved work. This feature is turned on by default and is hardly noticeable as it happens in the background.

However, you may notice AutoRecover saving files, and may even get annoyed by it, if you have one or more large workbooks open, as it could tie you up for 5 to 10 or more seconds. I often have very large workbooks open that contain only reference information (i.e. files that I'm not making changes to) and other workbooks which I don't require to be saved by the AutoRecover feature.

To avoid the unnecessary saving of these files, there is an option available to turn off AutoRecover for individual workbooks.

Here's how...

1) Click the File tab (2010) or the Office Button (2007);

2) Click Options (2010) or Excel Options (2007);

3) Click the Save option on the left;

4) In the 'AutoRecover exceptions for' section, check the 'Disable AutoRecover for this workbook only' option.
(adapted from "excel addict")

"DATEDIF" function to calculate the number of days/months/years between dates

noreply@blogger.com (Arvind) — Fri, 19 Aug 2011 15:14:00 +0000

A simple subtraction between two date values gives you the number of days between the two days. However, to actually calculate the exact number of days or number of completed months or number of completed years, Excel offers a hidden function called "DATEDIF".

Syntax: =datedif(date 1, date 2,criteria), where
date 1 is the start date or the older of the two dates
date 2 is the end date or the recent date
Criteria can be as follows:
"d" - number of completed days between the two dates
"m" - number of completed months between the two dates
"y" - number of completed years between the two dates
"yd" - number of completed days between dates ignoring the year i.e. considering them to be in the same year
"ym" - number of completed months between dates ignoring the year i.e. considering them to be in the same year
"md" - number of completed days between dates ignoring the year & month i.e. considering them to be in the same year and same month

Therefore if I want to calculate my age, I can write the formula as:

Date 1: 1-Jan-1980 (H4 cell)
Date 2: 19-Aug-2011 or today (I4 cell)
Formula: =DATEDIF(H4,I4,"y")&" years "&DATEDIF(H4,I4,"ym")&" months and "&DATEDIF(H4,I4,"md")&" days"
Result: 31 years 7 months and 18 days

Caution:

Date 1 ought to be lesser than or older than Date 2
of course, both dates should be valid date formats

Try it... its interesting!!

Thanks Charan for reminding me on this function, which I had stopped using.

Changing case of text in a cell

noreply@blogger.com (Arvind) — Sat, 12 Mar 2011 05:23:00 +0000

Excel 2007 and 2010 versions allow easy shortcut keys to change the case of the text in any given cell.

Proper case (1st letter of every word in upper case) - shift + ctrl + p
Upper case (all characters in upper case) - shift + ctrl + u
Lower case (all characters in lower case) - shift + ctrl + l

Isn't it handy??

Published with Blogger-droid v1.6.7

Shortcut to AutoFilter

noreply@blogger.com (Arvind) — Sun, 26 Dec 2010 10:32:00 +0000

One of the most frequently used feature of Excel is the autofilter. Most power users tend to use Alt+D-F-F for turning the filter on. Excel gives a shortcut for this... "Shift+Ctrl+L".

Shift+Ctrl+L provides the ease of applying or removing filter.

This is applicable for 2003 version onwards.

"CountIF" or "SumIF" with an extra "S" makes the difference...

noreply@blogger.com (Arvind) — Sun, 12 Dec 2010 16:36:00 +0000

Most of the users are very used to formula like "COUNTIF"or "SUMIF"... Excel offers "S" factor adding spice to these handy formulas...

Sumif: usual syntax is =Sumif(criteria range, criteria, sum range) where you want to sum a given range of cells based on a condition in another range of cells. "Sumifs" acts one step further.

The syntax is =Sumifs(Sum range, Criteria range1, criteria1, Criteria range2, criteria2...) and go on to add upto 9 criteria. This is handy when we need to sum a range based on more than one criteria.

Similarly, we are quite used to "Countif" formula while counting a given range based on a filter criteria. Just like sumifs, countifs offers the same operation with multiple criteria filtration. Countif syntax: =Countif(Count range, Criteria). The countifs syntax gets changed to =countifs(Criteria range1, criteria1, criteria range2, criteria2....). This too works upto 9 criteria.

Excel really makes life simpler.... Isn't it??

Shortcut to insert current date in a cell

noreply@blogger.com (Arvind) — Sun, 26 Sep 2010 13:10:00 +0000

Want to insert the current date in a cell with just a key stroke...?

The answer is simple - "ctrl + ;". This inserts the current system date as per the format configured on your system!

Check it out... this is applicable on all versions of excel.

Copy visible cells...

noreply@blogger.com (Arvind) — Fri, 06 Aug 2010 14:29:00 +0000

Have you ever tried copying a list where there are multiple hidden rows...? Whats the result??

You will see all the values irrespective of the hidden or visible cells...!

Shortcut:

Copy the given cell range
Press "Alt + C"
Press "Ctrl + C"
Paste at the desired location (Ctrl + V)

Now see the difference...!

Simple... isnt it?

The power of "&" operator in Excel

noreply@blogger.com (Arvind) — Thu, 24 Jun 2010 06:53:00 +0000

Have you ever tried combining the contents of two cell... the first thing that comes to mind is "concatenate" (an inbuilt function in excel to combine to strings). The usual syntax for using this function is '=concatenate(Text1,Text2)' where text can be entered directly within quotes or use cell reference i.e. '=concatenate(A1,A2)'....

This may be at time cumbersome to remember. The easy solution... "&" operator which serves the same function as concatenate. The syntax for using "&" is '=(text1 & text2)' where the text is to be entered within quotes.

Lets try creating an address book for a company where the email naming concept is firstname.lastname@companyname.com. We just have the list of names i.e. First Name in Column A & Last Name in Column B. Just go to column C and try writing the formula '=(A2 & "." & B2 & "@companyname.com") and just see the merged contents in the new cell...

Isn't this a handy operator... Try using this in multiple ways and let me know if this helps...!

Custom Validation in Excel

noreply@blogger.com (Arvind) — Fri, 14 May 2010 06:05:00 +0000

Have you ever tried to create a worksheet where you dont want the user to create duplicate entries...??

Its quite possible in a fairly simple way in MS Excel..!

Say you are typing a list of names, where you don't want any name to be repeated once:

Step 1:

Step 2:

Step 3:

The formula is a relative formula where it counts the values between the first cell of the column till the current cell to check if the value entered in the current cell is not repeated.

Step 4:

You may define custom error messages too!

Step 5:

Copy the first cell to the desired number of cells in the list in order to apply the validation to all the cells in the list.

Now try entering few names in the column.

Result:

So... isnt it easy? Try it out yourself.. and do let me know if this works!!

Navigation powered by "Ctrl" in Excel

noreply@blogger.com (Arvind) — Wed, 14 Apr 2010 21:25:00 +0000

Have you ever wondered the power of the key "Ctrl" while you are navigating in an excel worksheet or workbook...?

The best accelerator key for navigation in excel is "Ctrl"...

Move to the beginning of a row Ctrl + left arrow
Move to the end of a row Ctrl + right arrow
Move to the beginning of a column Ctrl + up arrow
Move to the end of a column Ctrl + down arrow
Move to a different worksheets Ctrl + Page Up or Page Down
Juggle between active workbooks Ctrl + tab
Move to the beginning of worksheet Ctrl + home

Isnt this is a power house...?

So next time you use excel.... dont forget your power key to navigate...!

Excel Formula: Round()

noreply@blogger.com (Arvind) — Wed, 18 Nov 2009 10:10:00 +0000

Intermediate to advance users of Excel may be quite familiar with the formula "Round()" where one can round off decimals.

Microsoft offers something beyond decimals rounding off by this formula.

The usual syntax for rounding off a number is "Round(#ref,1)" where #ref is the cell reference or the number to be rounded off and 1 is the number of decimal places the number needs to be rounded off i.e. if you want two digits after the decimal it should be 2, for one digit it should be 1 and no digits after decimal it should be 0.

Have you ever tried using -ve number for rounding off...?

Yes..! Using a negative number in the syntax will round of the number to the unit place you desire i.e. "Round(#ref,-1)". Eg: You wish to round off 123456 to the nearest thousands so the formula would be "Round(123456,-3)" - Result: 123000!!

This would be quite handy for the finance and sales people who need to quote rounded figures at various forums..!!

Microsoft excel rocks..!

Word: Change case

noreply@blogger.com (Arvind) — Thu, 22 Oct 2009 17:57:00 +0000

Oops... typed something in caps where you had to have in small case...?? Dont worry... Word provides the ease of using "Shift + F3".

"Shift + F3" works in a toggle mode where one can toggle between

All CAPs
All in lower case
Title Case i.e. 1st letter in Caps and rest in lower case

All can be just a word or a sentence or any selection.

SO why WORRY... start USING "Shift + F3"...!

Keyboard Shortcut to add/delete rows or columns in Excel

noreply@blogger.com (Arvind) — Fri, 16 Oct 2009 09:37:00 +0000

"Ctrl + +" (add rows or columns) and "Ctrl + -" (delete rows or columns) are the easy shortcuts one may remember while adding and deleting rows/columns in Microsoft Excel.

Adding rows or columns:

If you wish to add rows above the current cell... just press "shift + spacebar" (discussed in my earlier post) to select the current row and then press "Ctrl + +". It will insert a row over the current row. Press "Ctrl + +" repeatedly to insert as many rows.

Similarly, "ctrl + spacebar" and then "Ctrl + +" adds a column before the current column.

You may try pressing "Ctrl + +" without selecting a row or a column. In such a case, you may get a pop-up (as shown in the image) where you can choose an option of adding a row or a column.

Deleting rows or columns:

"shift + spacebar" followed by "Ctrl + -" deletes the selected row & "ctrl + spacebar" followed by "Ctrl + -" deletes the selected column.

In case no row or column is selected, pressing "Ctrl + -" gives another pop-up asking you to choose what you want to perform.

Excel: Some cell formatting shortcuts

noreply@blogger.com (Arvind) — Sat, 10 Oct 2009 01:32:00 +0000

Here are some keyboard shortcuts for some commonly used tasks. You may find it difficult to remember initially but gradually you would love to remember them.

Format as currency with 2 decimal places	CTRL + SHIFT +$
Format as percent with no decimal places	CTRL + SHIFT +%
Apply number format	CTRL + SHIFT + !
Apply date format	CTRL + SHIFT + #
Apply time format	CTRL + SHIFT + @
Apply general format	CTRL + SHIFT + ~
Apply exponential number format	CTRL + SHIFT + ^
Apply outline border to selection	CTRL + SHIFT + ;
Remove outline border to selection	CTRL + SHIFT + _

Note: These shortcut will execute system default formating eg: if the default currency of the system is "$" the currency formated cell will bear "$". If you wish to apply any other formating other than the system default, use "Ctrl + 1" to open to cell formating dialogue box.

Tip: "CTRL + SHIFT + _" also works in Acrobat Reader 7 & later version to rotate the page anticlockwise..!!

The best option to use these shortcuts is to place a print of these shortcuts in your workstation so that you may refer to them until you get used to it.