Few Excel Tips and Tricks One Must Be Knowing
Data Entry is the demand of every organization and to handle it we need to spend much of our time as well as effort. Follow these tips to make your Data Entry work easy, quick and effortless while using the Excel. You will be finding these tips convenient for handling all your data entry work, whether you are dealing with your personal stuffs or your professional data work.
Highlights of this article are
- Using shortcut Keys
- Making Excel speak
- Effective use of Excel Data Entry form
- Keeping row header visible when scrolling down
Excel sheet has to be the mirror image of your work. In a simple and easy way one must be able to handle the data efficiently. These ten tips are going to make your Microsoft Excel learning very easy.
For your better understanding the complete section is divided into four segments.
They are
- Effortless Excel Data Entry work
- Excel as mirror
- Data Entry Form
- Making Visual headers
Segment 1: Effortless Data Entry work
Entering duplicate data time and again is time killing and prone to mistakes too. So to avoid these mistakes and time wastage, some excel built-in data entry tools are there with which the work efficiency can be magnified.
Using TAB and ENTER keys for cursor movement
Combining these two keys is very in entering duplicate data values and taking less time.
The TAB key is to move forward in the right from your current cell while ENTER key is for moving your current cell down by one cell.
Keep these steps in mind when you are supposed to enter data in a row.
Step 1: Go to the initial cell of the row
Step 2: Type first desired/required value in it
Step 3: Now press TAB key to move forward from your current cell to the right
Step 4: Type another desired data
Step 5: For moving your current cell to the right use TAB key
Step 6: Iterate the same till you reach the last column
Step 7: Press Enter at the end of the column to move your current cell to succeeding row
Other shortcut keys for use are:-
� Use arrow key to go back
� Use SHIFT+TAB for moving to previous cell in same row
� Use SHIFT+ENTER for moving to one cell up
Configuring Excel default Cell movement:
The default movement pattern in Excel can be changed, so setting depends on your choice.
Steps to be followed for changing the settings in the Excel 2007/2010:
First of all select �OPTIONS� from the �OFFICE BUTTON� or �FILE� menu, then choose advanced menu from that and do one of things
Step 1: Select the check box for �After pressing Enter, move selection�. Then choose the direction you wish (left/right/up/down) to set.
Step 2: Clear the check box �After pressing Enter, move selection�. This is if you wish to prevent any movement when you press Enter.
If you are working on Excel 2003 then go to tools menu and select OPTIONS from there.
After this choose the EDIT tab where you can do one of these two:
Step 1: Select the check box for �Move Selection After Enter�. Then choose the direction as you wish (Left/Right/Up/down) to set.
Step 2: Clear the check box �Move selection After Enter�. This is if you wish to prevent any movement when you press Enter.
2. Use of CTRL+D fill down command
Using CTRL+D command you can fill the topmost selected content to the down cells up to which you have selected.
Fill the content in a cell or a complete row
Select the above content you want to copy in the down cells and move your cursor down up to which cell you want to copy this. Then press CTRL+D command and your content will be filled down in selected cells. The content can be overwritten in the right also by using CTRL+R command.
Another way is select the desired cell you wish to copy in down cells. Hold SHIFT key and press the down arrow to select the cells you wish to copy. At last press CTRL+D command and content will be copied there.
Two other important shortcuts related to this are:-
� CTRL+� (single quote)
Copying formula(s) from above cell
� CTRL+� (double quote)
Copying values from above cell
(If even formula(s) are there then only values will be copied)
3. Autocomplete using TAB key
If some data has been already entered anywhere in a column of Excel sheet then here is a simple trick to save your precious time.
Suppose you have entered John in the first row. When you will move to the next row and type �J� then �ohn� will be highlighted for your convenience. It�s because that is already in that Excel sheet. Now it�s on you that you want to select it or not.
Excel will give suggestion only for alphabetical entries or alphanumeric entries. The suggestion is even case sensitive so will match with upper case and lower case and gives the suggestion output.
Once you got the suggestion you can go for the following:
� Use TAB key to select that suggestion with moving one cell right.
� Use ENTER key to select that suggestion with moving one cell down.
� Carry on writing to enter your entry.
� Use BACKSPACE to delete the entry automatically written.
4. Use of ALT+Down arrow key
The combination of ALT+Down arrow key is used for viewing the autocomplete list.
Through that drop down list you can choose entry according to your requirement by using arrow keys and ENTER key. Even you can select the value by clicking it.
5. Use of CTRL+ENTER
Case 1: For entering same entry in multiple cells
Use CTRL+ENTER combination key for entering value into multiple cells. If you need to save the time from writing duplicate data then first select all the cells and use
CTRL+ENTER, to enter the same entry in all the selected cells.
In case of non contiguous cells, keep pressing CTRL key and simultaneously click on cells you want to select.
Case 2: Correcting data entry in multiple cells
If you have to make a correction and replacement into multiple continuous cells then select all the cells you have to alter. Type your required entry and press CTRL+ENTER.
For non contiguous one, keep holding the CTRL key and click to select desired cells.
Case 3: Easily altering in upward cells
Even alteration in upward cells can be easily done by use of the CTRL and ENTER key only but somehow in different way.
Select/Choose the cells you wish to alter and then press F2 to move to edit mode. Now press CTRL+ENTER combination key to copy the correct entry.
Segment 2: Making Excel Talk
6. Excel should talk whenever you enter data
There are situations when you need to move numbers from paper to Excel or from a geographical PDF document to Excel sheet. In that scenario you may be taking step to use OCR but mostly the resulting file is error frequent which is very hectic and time taking to resolve manually.
This can be easily solved by making Excel to speak. It�s an enchanting and nice trick for data entry work.
Options of �speak cells on Enter� will do it for you. It means when you enter the data then the Excel will check it and will inform you know that you are entering right data or wrong one.
This command is not part of main Excel ribbon or not even standard Excel dialogs.
For using this command need to follow these steps so that you can add it to quick access
toolbar (Excel 2007/2010)
Step 1: Go to file and select �Options� from there.
Step 2: Select Quick Access Toolbar
Step 3: customize the Quick Access Toolbar
Or
Directly go to the Customize Quick Access Toolbar
Step 4: Select more Commands from the Customize Quick Access Toolbar
Step 5: Choose the command from �Commands Not in the Ribbon�
Step 6: From there select the option of �Speak Cells on Enter�
Step 7: Add it to the Customize Quick Access Toolbar
Step 8: It can be shifted by using up/down arrow keys.
Step 9: Save with �OK� otherwise reject using �Cancel�
This is a toggle button so once press it to make it start talking and again press to make it stop talking.
Segment 3: Data Entry Form
7. Make Data entry Form
For many users it�s convenient to use data entry form instead of entering data into a worksheet. It provides easy to do method and space for entry of data.
First have to understand the Excel Data Form:-
In common words Excel Data Form is a dialogue box with different fields for different entries. The column header of Data Form is taken as a reference for making field labels.
At maximum 32 columns can be in the Data Form.
One can note these differences between Data Entry Form and Worksheet:-
1) One record visible at a time on Data Form whether one row is visible in your worksheet.
2) Fields are displayed vertically in Data Form whether in worksheet it�s horizontal.
3) Use of shortcut keys for moving between different parts of the form. See the underlined characters and with combination of ALT key you can move to that field.
Note: – Autocomplete facility is not carried by Excel Data Entry Form.
How to get the Data form?
While working with Excel 2003 its quite simple to open it. Select the data range with the column labels and select FORM from the DATA menu.
When working with Excel 2007/2010 then there are two ways to get the Data Form.
1) Use of shortcut key ALT+D+O
2) Adding a form button to the Quick Access Toolbar (QAT)
Steps to do it:
i. Go to Quick Access Toolbar, Right click it and then right click �More
Commands�
ii. Go to �Choose commands from� option
iii. Set �Popular Commands� to �Commands Not in the Ribbon�
iv. From <separator> select Form
v. Add it to the Customize Quick Access Toolbar
vi. Click �OK� to add the form to it otherwise click �Cancel� to reject the form adding
Segment 4: Keeping Row Header Visible
If your worksheet has plenty of data??? You need to scroll down for rows and rows and even getting problem in analyzing the concerned row description. Then there are three options in front of you to solve this:
I. Freeze panes option
II. Split window option
III. Excel Table
8. Freeze the panes using (ALT�W�F�F) keyword
If you want to make a part of worksheet still/stagnant then use freeze panes command.
Mainly it works for row heading and column. Then you can scroll to the other parts of the worksheet keeping that header portion still.
How to do this
Excel Tips for 2007/2010
1) Click the current cell according to which you wish to freeze the panes
2) Go to View�Freeze panes
3) From these three options select any one
a. Freeze panes
b. Freeze Top Row
c. Freeze First Column
4) You will see a thin black line between these sections, showing the separation
5) When you will scroll through these then the freeze portion will be still and other portions on the worksheet will scroll.
To come out of this and unfreeze it, go to View�Freeze Panes�Unfreeze Panes
9. Split Window using command ALT�W�S
Monitoring different portions becomes very easy with this. Splitting worksheet stretches work ability, to scroll worksheet in each pane by seeing the entry and comparing effectively.
How to do this
Excel Tips and Tricks for 2007/2010
1) Click the current cell accordingly where you wish to split into the window
2) Go to View�Split
(This is a toggle button so clicked once it will split into, clicked again will remove it)
A thick gray divider will be seen to you where the window has been split.
The split window can be reposition also for that just click on the split bar and drag it to the place you want to reposition it. By double clicking the split bar it will be removed.
10. Using Excel Table
Using CTRL+T shortcut key you can add a table to the worksheet. Table feature is available in Excel 2007 or other new versions but before this it was not there.
Main advantage is that when you choose a cell inside a table then you can scroll down where the column headings are kept in view.
In place of the normal column headings which are labeled as A, B, C, D, E etc�, they will be appearing.
It�s like range but has extra features. Some interesting features are:-
i. Automatically table will enlarge or shrink as we add more rows with data.
ii. Banded row and banded columns can be included for quickly customizing the table style.
iii. Every table has its own filter.
�
image courtesy : Holistic
