Excel Data Basics: Select, Copy, Paste, and Autofill

Key Takeaways

Working with data is the fundamental purpose of Excel. So, let's get started with data. Thomas Bradshaw from Westcal Publishing knows that Excel contains several tools to help reduce your workload and streamline tasks. Let's explore a few tools and techniques that you can use when working with formulas and data. Let's begin with some of the more common techniques that you can use to select various parts of a worksheet. To select a cell, simply click on the cell. To select a contiguous range of cells, click the first cell included in the range, hold down the shift key, and then click on the last cell in the range. To select an entire row, click on the numerical header. To select an entire column, click on the alphabetic header. To select an entire worksheet, click the worksheet selector next to column A or press Ctrl and A on your keyboard. And to select a contiguous range of cells, rows, or columns, click and hold the left mouse button over the first cell, column, or row in the range. Then drag the mouse to the last cell, column, or row. Then release the left mouse button. Just like many applications, Excel allows you to move and or copy cells and their contents to other areas of the workbook or even other applications. You can move a cell's contents using the cut and paste commands. You can find these commands in the clipboard group of the home tab. These commands are also available on the rightclick menu. Or you can use keyboard shortcuts for these commands. To cut, you want to press Ctrl and X. To copy, it's control and C. And to paste, you press Ctrl and V. These shortcuts are universal across most programs. To use the range of paste options, click the drop- down on the paste command in the home tab to access the many options on how to paste data and/or attributes from your copied data. The standard paste option at the top left is used to paste all formatting along with the contents of the cell. The paste values option is used to paste the values from the copied data only without the formulas or formatting. To review the alternate paste methods, you can hover your mouse over the icons to display a description or click paste special. The paste special dialogue box will appear displaying the available options for pasting copied data. Live preview is used to give you an idea of how copied objects will look when pasted in a selected area using regular paste or a paste special option. These previews are enabled by default and will be displayed automatically when you hover your cursor over a paste option on the paste drop-own command or on the context menu. A preview of how the data will look when pasted will be displayed and any overlaying menus will become transparent. You'll also find this feature when formatting your worksheets by changing fonts and adding styles. The transpose option is a type of paste command that includes options to shift the vertical and horizontal orientation of any columns and rows in a worksheet. For example, suppose that you had a data range that displayed product names and numbers as rows with days of the week displayed as columns. You could transpose the columns and rows by copying this data range and then placing your cursor on the worksheet where you want the copied data to be pasted. Next, click home, then paste, and then transpose. The result will flip the row and columns. You can also move Excel data by selecting cells and dragging and dropping them to a new location. To do this, hover your mouse over the border of the selected cells until the move cursor appears. Then, click and drag the selected cells to the new location. Excel has a couple of useful features which can automatically repeat, sequence, combine or extract data across many cells if it recognizes a pattern. Autofill can help you quickly enter repeated or incremental text and numbers. For example, imagine that you must enter all the years from 2010 to 2020 in a worksheet. Rather than typing each year manually, you can use autofill to enter the data quickly and easily. In order to use autofill, you need to establish a pattern if you want anything other than default. Firstly, type 2010 into cell A1 and 2011 into cell A2. Drag and select both cells and hover over the green box in the corner. Now, drag downwards and let go of the mouse. The corresponding years autofill as a result. You can click the autofill options button that appears after you have selected a range of cells to configure how the autofill works. Excel also comes pre-programmed with some other common autofill sequences, including days of the week and months of the year. Let's try another method. Type 2010 into cell A1 and 2012 in cell A3. Then select the range of cells from A1 to A3. Next, navigate to the home tab and click the autofill icon and then select series from the drop-own menu. A series dialogue box will open containing options for configuring how you want the series autofill to operate. Excel will automatically choose options related to the range of cells you selected. Here the series will be in a column in a linear type with a step value of one. Click okay. Now cell A2 will be populated with the missing year in the sequence. FlashFill is similar to autofill, but flashfill will automatically extract or combine data when it senses a pattern in the adjacent cells. As an example, you can use flashfill to separate the first name from a column containing full names. Start by typing the first name from cell A2 into cell B2. You can then select cell B3 and click data then flashfill. Sensing a pattern, Excel will automatically fill the remaining cells in column B with the first name from column A. Like many other applications, the undo command allows you to reverse the last action that you completed. The redo command, on the other hand, will repeat the last action. Both commands are available in the quick access toolbar. Clicking the drop-down arrow on either command will allow you to select multiple actions at once from your recent activity. You can also perform the undo command by pressing control and zed on your keyboard. The shortcut for the redo command is controll and Y.