Microsoft® Excel Tips and Tricks

Given below are Excel Tips and Tricks that will help you to work faster with Excel. It is not about knowing more Excel functions, formulas or features. It is about using the functions and formulas to solve tedious problems that take up a lot of your time. And the good news is, you don’t have to be expert to use them. It is about applying the Microsoft® Excel formulas in a creative way and improve your efficiency when working with Excel. It will make Excel work harder for you without using macro programming / VBA.

If you also own some Tips and Tricks, please feel free to share with us at our ask  page.

Assuming that you have a list consisting of 5 columns and you need to add new records to the list. You go to the empty row immediately below the list and started to enter data into the cells. After you input the data into the cell in column A, you hit the ENTER key and use the mouse to click the cell on the right. Or after inputing the data in column A, press the right arrow key to move to the cell on the right. When you reach the last column of that record, you would have to use your mouse to click to the first cell in the next row.

But the fastest way is to use the Tab key to move to the cell on the right. If you start using the Tab key right from the first column to move to the cell right, you can hit the ENTER key when you completed the last cell in the same row. The active cell will automatically jump back to the next row directly below the cell where you have first started using the Tab key.

Turning the hyperlink formula into a search function

Combining the use of CELL, ADDDRESS, MATCH formula, the HYPERLINK worksheet formula could be converted to a cool search function.

Hidden Secrets in Excel 2007/2010 Quick Access Toolbar

Using the new Quick Access Toolbar, you can add commands and/or functions that you will not find in the Ribbon. One of them is on changing the shape of the comments.

Maximizing the use of your Excel toolbars

If you start using Excel without noticing the toolbar, it is about time you should take a good look at it. You will realize that you have lost hours doing thing the long way. This tips and tricks is for Excel 2003 and below.

Improve your efficiency with Excel icons (Excel 2003 and below)

Once you have make the toolbars available, you can consider adding more icons to your toolbar that will help you work more effectively.

Creating a Table of Content Page for a Workbook

Using the hyperlink function, you can create a table of contents for your workbook so that you can move quick to different worksheets. This is especially useful if there are many worksheets in the workbook.

Convert Text Formatted Numbers to Proper Numbers

This write up shows you different methods of converting formatted numbers to proper numbers. It could be caused by data downloaded from your systems, data copied from the web, etc.

How to separate the text into two lines in a cell

I am sure you will love this if you have not discovered how to separate the text in a cell into 2 lines. Not by putting the second line in the cell below the original cell but within the same cell. Know how to do this gives you the added advantage over others who are spending lots of time trying to get a perfect width for their reports.

Remove duplicate records from a list

You will love this if you are managing hundreds and thousands of rows of data.

Excel Challenge 1

Here is the file for Excel Tips and Tricks Challenge 1 which contains 4,000 records of company name, sales amount and quantity sold. Find out how fast you can copy the average selling price formula (given in cell D2) to all the 4,000 rows.

Can you copy the formula down faster than me? At best, you can be on par with me. Don’t believe me? Try it out and see how long you take. I will let you know how fast I did it once you took the test.

This is one of those tips and tricks that will boost your speed by leaps and bounds, especially if you are always working with a large data list.

Just use your first name and primary email below to get the answer – then click the “I want the Tip” button.