Some days ago i wrote some useful tips for excel part I which includes some handy techniques available in spreadsheet software, particularly Excel, that essentially make our tenuous task of data entry that much easier. Given that spreadsheet software have revolutionized the way we handle large amounts of tabular data, and manipulated them to find statistical anomalies or patterns, we have decided to dedicate this article to the pursuit. The task of entering data should become that much easier.
Most official purposes require the data to be stored only on the weekdays ie Mondays to Fridays. Omitting the weekends could be a hopeless task if done manually. For this particular situation you can utilise the ‘Fill Weekdays’ feature to fill in a range with dates while excluding the weekends. To do that, type the first day of the week, such as 16-Apr-2012, into a cell. Then click on that cell and drag the fill handle with the right mouse button down the column or across the row. You can estimate the amount of cells you are going to need. Then as you are releasing the mouse button, click ‘Fill Weekdays’ in the shortcut menu that appears. You will see the cells fill with the appropriate Monday-through-Friday dates.
A little known and used feature is that Excel tracks the entries you make in a particular column, so if numerous entries have the same value, you can access them directly and not type. Some of the data you work with in Excel will need repeating time and time again. Other pieces of data will require repetitive typing down of a column in a single worksheet and then never appear again. For the second condition, you can make data entry easier by selecting from a list of entries you’ve used previously. When entering data, if you are sure that you have typed the same entry earlier in the same column, press Alt-Down Arrow or right-click the cell and choose Pick From Drop-down List. In the list that appears, click the entry to use. This list contains only the items you’ve entered in the column cells above the current cell. Note that this feature works with text but not with dates or numbers, and that it works only down a column.
Where Do You Want Your Cursor?The way the cursor moves while entering data can also be cumbersome. For instance, while entering data horizontally it would be an inconvenience to have to change rows for every entry. For this you can set the movement of the Excel cell cursor when entering data, with this ‘Direction’ option. To enter data into a block of selected cells, you have to select the block before you start typing; then, press Enter to complete an entry and excel moves the cell cursor to the next cell. If you want to control the direction in which Excel moves the cursor, you need to indicate your preference in the Excel Options. Choose File, Options, Advanced. In the ‘Editing Options’ area, select your desired direction from the Direction drop-down menu below the ‘After pressing Enter, move selection’ label. If you set this menu to Right, Excel scrolls across the first selected row, and then wraps around to start at the first cell in the next row. If you select Down, Excel scrolls down the first selected column and then snakes back up to continue down the second column, and so on.
Manipulating numbers is one of the major sources of concern for users. To help with this Excel has a feature that automatically adds a decimal point for you. It is easier to enter values such as 20.30 in the format ‘2030’, and have Excel enter the decimal point automatically. If you think this can make you more efficient, simply configure Excel to work with this form of data entry. Choose File, Options, Advanced, and in the ‘Editing options’ section enable the Automatically insert a decimal point checkbox and set the Places value to 2. In the future, you won’t need to enter the decimal point manually. One point to note is that if you want to enter a value such as 100 into a cell, you now have to type either 10000 or 100.
Create A Custom Fill Series
So far, when we have used the right mouse button handle we have had to guess the cell up to which we had to drag. That is because the fill preference is chosen only after you’ve stopped dragging, and Excel can’t know what series you plan to create. If you want to be more accurate, however, you can create your fill series using the Series dialog box instead. The Series dialog box lets you specify what you want in your own custom series. For this, start by typing the first value into a cell. To enter the series 1, 2, 3, and so on as we did earlier, type 1 into a cell. Click in this cell, click the Home tab on the Ribbon toolbar, and click Fill, Series. In the dialog box, choose either Rows or Columns, depending on whether the values should run down the column or across the row. From the Type options click Linear, as this series is a linear one in which each number value counts for one more than the last. Click in the ‘Step value’ box, and type 1 (the difference between each two successive numbers). Then, click in the ‘Stop value’ box and type the last number in the series; for example, to enter all the numbers up to 100, you would type 100. Click OK, and Excel will enter the number sequence for you.
Source: The Kathmandu Post