By Many of the worksheets that you create with Excel 2016 require the entry of a series of sequential dates or numbers. Excel’s AutoFill feature makes short work of this kind of repetitive task. All you have to enter is the starting value for the series.
In most cases, AutoFill is smart enough to figure out how to fill out the series for you when you drag the fill handle to the right (to take the series across columns to the right) or down (to extend the series to the rows below). The AutoFill (or fill) handle looks like this — + — and appears only when you position the mouse on the lower-right corner of the active cell (or the last cell, when you’ve selected a block of cells). If you drag a cell selection with the white-cross mouse pointer rather than the AutoFill handle, Excel simply extends the cell selection to those cells you drag through. If you drag a cell selection with the arrowhead pointer, Excel moves the cell selection.
In this video, you’ll learn the basics of modifying columns, rows, and cells in Excel 2016. Visit for our text-based lesson. Add an Image as a Watermark to Your Workbooks in Excel 2016. Compared to Microsoft Word documents, formatting in Excel is a little unintuitive. Working with watermarks in Excel is no exception.
When creating a series with the fill handle, you can drag in only one direction at a time. As you drag the fill handle, the program keeps you informed of whatever entry will be entered into the last cell selected in the range by displaying that entry next to the mouse pointer (a kind of AutoFill tips, if you will). After extending the range with the fill handle, Excel either creates a series in all of the cells that you select or copies the entire range with the initial value. To the right of the last entry in the filled or copied series, Excel also displays a drop-down button that contains a shortcut menu of options. You can use this shortcut menu to override Excel’s default filling or copying. You can use AutoFill to enter a row of months, starting with January in cell B2 and ending with June in cell G2.
To do this, you simply enter Jan in cell B2 and then position the mouse pointer (or your finger or stylus) on the fill handle in the lower-right corner of this cell before you drag through to cell G2 on the right (as shown here). To enter a series of months, enter the first month and then drag the fill handle in a direction to add sequential months. When you release the mouse button or remove your finger or stylus from the touchscreen, Excel fills in the names of the rest of the months (Feb through Jun) in the selected cells (as shown here). Excel keeps the cells with the series of months selected, giving you another chance to modify the series.
(If you went too far, you can drag the fill handle to the left to cut back on the list of months; if you didn’t go far enough, you can drag it to the right to extend the list of months farther.). Release the mouse button, and Excel fills the cell selection with the missing months. Also, you can use the options on the AutoFill Options drop-down menu shown. To display this menu, you click the drop-down button that appears on the fill handle (to the right of Jun) to override the series created by default. To have Excel copy Jan into each of the selected cells, choose Copy Cells on this menu. To have the program fill the selected cells with the formatting used in cell B2 (in this case, the cell has had bold applied to it), you select Fill Formatting Only on this menu. To have Excel fill in the series of months in the selected cells without copying the formatting used in cell B2, you select the Fill Without Formatting command from this shortcut menu.
Creating a custom company location list from a range of existing cell entries. Click in the Import List from Cells text box and then select the range of cells in the worksheet containing the custom list. As soon as you start selecting the cells in the worksheet by dragging your mouse or Touch Pointer, Excel automatically collapses the Options dialog box to the minimum to get out of the way. The moment you release the mouse button or remove your finger or stylus from the screen, Excel automatically restores the Options dialog box to its normal size.
Click the Import button to copy this list into the List Entries list box. Skip to Step 6. Select the List Entries list box and then type each entry (in the desired order), being sure to press Enter after typing each one. When all the entries in the custom list appear in the List Entries list box in the order you want them, proceed to Step 5. Click the Add button to add the list of entries to the Custom Lists list box.
Finish creating all the custom lists you need, using the preceding steps. When you’re done, move to Step 6. Click OK twice, the first time to close the Options dialog box and the second to close the Excel Options dialog box and return to the current worksheet in the active workbook. After adding a custom list to Excel, from then on you need only enter the first entry in a cell and then use the fill handle to extend it to the cells below or to the right.
As you can check on the, Excel doesn't have any tool for doing that, but you can fill it with the ROW function. Quoting:. Select the first cell in the range that you want to fill. Type the starting value for the series.
Type a value in the next cell to establish a pattern. For example, if you want the series 1, 2, 3, 4, 5., type 1 and 2 in the first two cells. If you want the series 2, 4, 6, 8., type 2 and 4. Select the cells that contain the starting values. Drag the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.). Selected cell with fill handle across the range that you want to fill.
To fill in increasing order, drag down or to the right. To fill in decreasing order, drag up or to the left. The simplest solution is to use the tables feature of excel - it provides you a which is expanded to all new rows in such table. If you'd like to create autonumber in this format: 1/2/./187/.
Insert this formula in the column of the table where autonumber should be placed: =ROW-ROW(Table1#Headers;AutoNum) (replace table name with yours - here 'Table1', and change reference to name of the autonumber column name - here 'AutoNum') If you'd like to create text autoID in this format: 'RowID001'/'RowID002'/./'RowID187'/. Insert this formula in the column of the table where autoID should be placed: =CONCATENATE('RowID';TEXT(ROW-ROW(Table1#Headers;AutoNum);'000')) (the format of number within autoID is defined by the ). The Most Wonderful way to do this just fill the number in your column and in the next column do this column above+1 and then use the fill series button on the home right corner of the excel sheet and it will autofill the whole column.
For eg: What i Did was I Put the number in A1 column as 100 and In A2 column I did apply the formula that is =A1+1 and then press enter so value comes here as 101 and then select A2 Cell then use the fill Option where you get the option fill down the series. And Hence I got all the numbers in increasing order. After you have got the numbering then copy the whole A column where you have got the value then again then Use right click option to use Paste special as values. So formula will be gone and you will get your int values In there.