Keyboard Shortcut key for Applying Filter !

First press Ctrl+ A to select the entire worksheet,

Than press Ctrl+shft+L,

Filter will be applied.


Selecting the entire worksheet is not necessary, it is done to make sure all the data is shown will applying or working with filters.


Cheers !

Keyboard Shortcut for Applying and Removing Cell Color !

Another unique keyboard shortcut for filling and removing color in Excel Cells.


Press "Alt + h + h" and the color selection tab will open in the excel sheet, you can chose your required color by clicking on it or by using the Arrow keys on the button.


For Removing the color from a cell,

Select the cell and Press "Alt + h + h + n".
The color will be removed.




Keeping visiting the blog for more interesting tips and tricks.

Redo Keyboard Shortcut !

Dear Followers here is a very rare known keyboard shortcut to improve your efficiency in MS Excel. 

REDO

If you have undo a previous activity that is done by pressing "Ctrl + Z".

You can Redo it by pressing "Ctrl + Y" and go to the same state of your job that was currently undone.

For More Tips keep visiting excel4excellence.blogspot,com

Also keep sharing it with your friends.

CONCATENATE

This function joins separate pieces of text into one item.

Syntax

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

Up to thirty pieces of text can be joined.

Note You can achieve the same result by using the & operator.

Create a Personal View of Your Workbooks

Excel enables you to have multiple workbooks showing simultaneously, and to have a customized view of your workbooks arranged in different windows. Then you can save your view workspaces as .xlw files and use them when it suits you.

Sometimes when working in Excel, you might need to have more than one workbook open on your screen to make it easier to use or view data from multiple workbooks. The next few paragraphs describe how to do this in a neat and organized way.

First, open all the workbooks you will need. To open more than one workbook at a time, select the Office button ➝ Open…, press the Ctrl key while selecting the workbooks you want to open, and then click Open (pre-2007, select File ➝ Open).

 From any of the workbooks (it doesn’t matter which one), select Windows ➝ View ➝ Arrange All (pre-2007, select Window ➝ Arrange). If “Windows of active workbook” is checked, uncheck it, and then select the window arrangement you prefer and click OK.

If you select Tiled, you will be presented with your workbooks in a tiled fashion.

Selecting Horizontal gives you a view of your workbooks in a single stack, one on top of the other.

Checking the Vertical option will place all your open workbooks side by side.

Selecting the Cascade option will layer all open workbooks one on top of the other.

Once your workbooks are displayed in your preferred view, you can easily move data between them (e.g., copy, paste, drag and drop). If you think you might want to return to a view you created, you can save this preferred view as a workspace.

To save a workspace, simply select Windows ➝ Save Workspace (pre-2007, File ➝ Save Workspace), enter the workspace’s filename in the File Name box, and click OK.

When saving your workspace, the file extension will be .xlw rather than the standard .xlsx of Excel 2007.

To restore your Excel workspace to one full window of a particular workbook, just double-click the title bar appearing on any one of your workbooks. You can also click the Maximize button on any of the windows in your workspace.

Close your workbooks as usual when you’re finished. Whenever you need to open those same workbooks, simply open the .xlw file, and the view you initially set up will be magically restored for all workbooks. If you need to open just one of those workbooks, open the file as usual. Any changes you make to the workbooks in the .xlw file will be saved automatically as you close the workspace as a whole, or you can save workbooks individually.

If you spend a small amount of time setting up some custom views for repetitive tasks that require multiple open workbooks, you’ll find that these tasks become easier to manage. You might decide to use different views for different repetitive tasks, depending on what the task is or how you’re feeling that day.

BEST REGARDS

Highlight Every Other Row or Column

You’ve surely seen Excel spreadsheets that have alternating row colors. For instance, odd-numbered rows might be white, while even-numbered rows might be gray.

Conditional formatting makes this easy. Alternating colors or shading looks professional and can make data easier to read. You can apply this formatting manually, but as you can imagine, or might have experienced, it’s a rather time-consuming task that requires constant updating as you add and remove data from the table. It also requires infinite patience. Fortunately, conditional formatting can reduce the amount of patience required and enhance your professional image.

We’ll assume your data occupies the range A1:H100. Select this range of cells, starting with A1, thus ensuring that A1 is the active cell in the selection.

Now select Home ➝ Conditional Formatting ➝ New Rule ➝ “Use a formula to determine which cells to format,” and type the following formula in the “Format values where this formula is true” box (pre-2007, go to Format

➝ Conditional formatting… ➝ change “Cell Value Is” to “Formula Is”),

=MOD(ROW( ),2)

Click the Format button and choose the format you want to apply to every second row. Click OK, and then click OK again. The format you specified should be applied to every second row in the range A1:H100. You also should have some patience left for the rest of the day.

If you need to apply this to columns rather than rows, use this formula instead:

=MOD(COLUMN( ),2)

Although this method applies the formatting specified to every second row or column quickly and easily, it is not dynamic. Rows containing no data will still have the formatting applied. This looks slightly untidy and makes reading the spreadsheet a bit more difficult. Making the highlighting of every second row or column dynamic takes a little more formula tweaking.

Highlighting Dynamically

Again, select the range A1:H100, ensuring that A1 is the active cell.

Select Home ➝ Conditional Formatting ➝ New Rule ➝ “Use a formula to determine which cells to format,” and in the “Format values where this formula is true” box (pre-2007, go to Format ➝ Conditional Formatting… ➝ change “Cell Value Is” to “Formula Is”), type the following formula:

=AND(MOD(ROW( ),2),COUNTA($A1:$H1))

Note that you do not reference rows absolutely (with dollar signs), but you do reference columns this way. Click the dialog’s Format option and select the desired formatting, then click OK, and OK again. Any row within the range A1:H100 that does not contain data will not have conditional formatting applied. If you remove data from a specific row in your table, it too will no longer have conditional formatting applied. If you add new data anywhere within the range A1: H100, the conditional formatting will kick in.

This works because when you supply a formula for conditional formatting, the formula itself must return an answer of either TRUE or FALSE. In the language of Excel formulas, 0 has a Boolean value of FALSE, while any nonzero value has a Boolean value of TRUE. When you use the formula =MOD(ROW( ),2), it will return either a value of 0 (FALSE) or a number greater than 0. The ROW( ) function is a volatile function that always returns the row number of the cell it resides in. You use the MOD function to return the remainder after dividing one number by another. In the case of the formula you used, you are dividing the row number by 2, so all even row numbers will return 0, while all odd row numbers will always return a number greater than 0.

When you nest the ROW( ) function and the COUNTA function in the AND function, it means you must return TRUE (or any number greater than 0) to both the MOD function and the COUNTA function for the AND function to return TRUE. Note that COUNTA counts all nonblank cells.

BEST REGARDS,

Five tips for printing Excel data more efficiently

Many Excel users are skilled at manipulating data in their worksheets -- but when the time comes to print certain portions of the data, the results aren't what they want. Fortunately, once you master a few of Excel's print options, you can avoid frustration and save a lot of time. Here are several easy ways to enable the appropriate settings to print just what you need, when you need it.

1: Set a custom print area

Excel prints the active sheet unless you tell it differently. There are two quick methods for controlling what Excel prints:

• Set the Print Area to limit what Excel prints by default.
• Print selected cells for a one-time print job.

A combination of these two methods provides the flexibility you need to quickly print exactly what you need.

First, use the Print Area option for a print job that seldom changes. After you set the Print Area, subsequent print jobs will default to that specified area instead of the entire sheet. To set the Print Area, do the following:

1. Select the range you want to print.
2. Click the Page Layout tab.
3. In the Page Setup group, click Print Area | Set Print Area.

When you need to print something other than the Print Area, print a selected range as follows:

1. Select the range you want to print.
2. Click the Office button or the File tab.
3. Choose Print in the left pane.
4. Choose Print Selection from the first Settings drop-down (Print Active Sheet is the default).
5. Click Print.

For the normal print job, just print, and Excel will default to the Print Area. When you need to quickly print another area, print a selected range.

2: Use page breaks to your advantage

Initially, printing can be a bit tenuous; just what will you get? You print, reset, try again... that could go on for a while. Fortunately, there's help in the form of page breaks. To remove some of the guesswork, choose Page Break Preview from the View menu in Excel 2003. In Excel 2007 and 2010, choose Page Break Preview from the Workbook Views group on the Layout tab. Excel will prompt you with a quick reminder that you can adjust page breaks by clicking and dragging them. Click OK to clear the prompt and move on to Page Break Preview. This view is noticeably different from the other views but shares a lot of information, visually:

• The visible white area is the Print Area. You can adjust this area by moving the solid blue boundary lines.
• The dashed lines identify page breaks, which you can move.
• The dark gray watermark denotes the page number.

No more guesswork: Simply put the page breaks exactly where you want them. Sometimes, rather than moving an existing page break, you might want to insert a manual break, as follows:

1. Select the row or column where you want to insert the break.
2. Choose Page Break from the Insert menu. In Excel 2007 and 2010, click the Page Layout tab. Then, in the Page Setup group, click Breaks | Insert Page Break.

The break will appear above the selected row or to the left of the selected column.

3: Fit To Page

This feature is nice when you want all the data on a single sheet. You might not be able to read it easily, but that might not be your main concern. To print the active sheet on a single page, do the following:

1. Click the Page Layout tab.
2. Click the Scale To Fit group's launcher.
3. On the Page tab, click Fit To in the Scaling section.
4. Make sure both Wide and Tall settings are 1.
5. Click OK.

This option won't satisfy your everyday print chores, but when you need everything on one sheet, you can't beat it.

4: Use custom views in Excel 2007 and 2010

Custom views are a new feature that let you save specific display and print settings for a sheet. It's so flexible that a sheet can have multiple custom views and a Print Area -- so think of the possibilities! Once you've set up a custom view, just apply it and press [Ctrl] P to print it. How easy is that? You can create a custom view as follows:

1. Set the appropriate display and print settings you want to save to a custom view.
2. On the View tab, click the Workbook Views group and the click Custom Views | Add.
3. Enter a name for the custom view.
4. Specify which settings to include.
5. Click OK.

Before you print the custom view, you must apply it by clicking the View tab and then clicking Workbook Views | Custom Views. Choose the view you want to apply and click Show. To print the custom view, press [Ctrl] P.

Creating a custom view takes a bit of work, but once it's saved, printing it is just a few clicks away.

5: Copy Page Setup options to other sheets

Setting Page Setup options isn't a huge job, but it can be time consuming if you have to set them for every sheet in your workbook. Fortunately, you don't have to -- and you won't believe how simple the alternative is. If all the sheets in your workbook have the same settings, set the options for one sheet and then copy those settings to others, as follows:

1. Select the sheet whose Page Setup settings you want to copy.
2. Right-click any sheet tab and choose Select All Sheets.
3. Click the Page Layout tab.
4. Click the Page Setup group launcher.
5. Click OK.
6. Right-click any sheet tab and choose Ungroup Sheets.

Best Regards,