Working with data in Excel…

Entering data in a cell

 To enter any data in Excel, whether it is text, numbers or formulas, you first need to select the cell in which the data is to be entered. Using the mouse, click in the required cell to make it active (a thick, black border will appear around the edge of the cell), then type the data directly into the cell. Notice how the data also appears in the Formula bar as you type. When you have finished typing the data, press Enter. Until you press Enter, the cell is still active and any subsequent typing or action will be entered into the cell.

Editing data in the formula bar

 If you need to amend data in a cell, you do not need to retype the data again. You can edit the text within the Formula bar.

     1. Select the cell you want to edit by clicking on the cell using the left mouse button
     2. Click in the Formula bar to the right of the character(s) you want to change
     3. Use the Backspace key to delete the existing unwanted characters, then type any new characters (if required)
     4. Press Enter to complete editing that cell

Selecting cells

 Selecting a single cell only requires the user to click somewhere on that cell with the left mouse button Selecting more than one cell is a little trickier. If you move your mouse around the screen, you will notice that the mouse pointer looks like a large white cross. However, if you hold your mouse over an active cell (i.e. click on the cell first), the mouse pointer can be one of 3 different shapes:

Large white cross – Select – hold the mouse over the centre area of the cell
Small black plus sign – Fill / Copy – hold the mouse over the bottom right corner of the cell
Cross with arrows – Move – hold the mouse over the edge of the cell

The shape of the mouse pointer determines what will happen when you hold down the left mouse button. You must ensure that the ‘Select’ mouse pointer is displayed when trying to select one or more cells, otherwise you may find yourself moving data around the worksheet by accident.

Entering a range of data

If you want to enter data in a contiguous range of cells, i.e. cells that are adjacent to each other, a quick method is to select the whole range of cells, then enter the data, one cell at a time, using the Tab key to move between the cells. The active cell, within a range, is the white cell. To select a range of cells, drag your mouse from the top left corner to the bottom right corner of the required range. To deselect a range of cells, left click once elsewhere on the screen.

Using AutoFill and custom lists

Filling a text series

Certain data can be completed automatically using the AutoFill function. This automatic completion of a data list is based on certain rules in Excel and any custom lists of data that you specified. Certain words are identified by default in Excel as being ‘list’ words such as days of the week and months of the year.

For example, if the word ‘January’ is typed in Excel then further data can be AutoFilled, i.e. Excel can automatically fill in a series of text with February, March etc..

Go to Tools > Options > Custom Lists. Here you will see a few lists of words that have been identified as being list words in Excel. You will see a list January, February etc and also days of the week: Monday, Tuesday etc. To fill a list of data, one of the words contained in the list must be typed in a cell. Then, hold the mouse over the bottom right corner of the cell until the mouse pointer turns to a small cross (like a plus sign). Holding the left mouse button down drag in the direction you require the remaining list items to be filled.

Creating a new custom list

New custom lists can be added to existing ones. For example, if you regularly create worksheets containing a list of your companys’ offices within Australia, you could set up a custom list of those offices. Then you can type one of the names in the list and use the AutoFill function to automatically complete the adjacent cells with the remaining office names (either going down or across the spreadsheet). In the Custom Lists tab (see above), click in the List entries box and type in your list of data, separated by a comma. Then click on Add.

Filling a series combining text and numbers

When you combine text and a number in a cell, you can use AutoFill to create a series, where it copies the text, but increases the number with a step value of 1.

Filling a numerical series

You can also use the AutoFill function to fill a series of numbers in a contiguous range of cells. As a number is not identified in any custom list, the AutoFill function does not automatically create a series, but instead copies the data from one cell to the next. For example, if the number 1 is entered into a cell, and the AutoFill is used on that cell, the number 1 will be copied into all of the selected cells. Excel assumes that you just wish to copy the data, rather than filling a series.

However, if you enter the number “1” in one cell, then the number “2” in the cell to the right, you can then select both of the cells, then AutoFill to the right. This will fill a series of numbers, increasing with a step value of 1. Excel assumes that because you have entered 2 different numbers with a difference of 1 you want to fill a series rather than copy. You can also create a series where the number increases by 10, for example. Entering 10 in the first cell, 20 in the next, than AutoFill the 2 cells together along a range.

Copying and moving data

As you have already seen, data can be copied by holding the mouse over the bottom right corner of the cell and dragging the mouse to adjacent cells. However, you may need to copy data to a cell that is not adjacent or you may want to copy text that is contained in a custom list. To do this, you use the Copy function from the Edit menu.

Data can also be moved from one place to another by using the Cut function, also found on the Edit menu. There are 2 steps to remember when using either the Copy or the Cut commands:

Steps:

 Data can be moved or copied in the following ways:

• one cell to one cell
• one cell to many cells
• range of cells to range of cells

Clipboard task pane

This task pane can display (up to) the last 24 items that have been copied or cut from anywhere within Microsoft Office applications thus allowing data to be pasted one or more times, without having to recopy the data.

The clipboard keeps a record of those items that were copied or cut and allows you to paste the data in any order and as many times as required. This clipboard is the same clipboard viewed from any Microsoft Office application. Therefore, if you opened Microsoft PowerPoint or Microsoft Word, for example, you will see these items on the clipboard in those applications, allowing you to quickly and easily paste items from one application to another. These items will be stored on the clipboard until all Microsoft Office applications have been closed.

Other editing functions

There is no toolbar specifically for the editing functions. Many of the functions are displayed on the Standard toolbar and can also be found if you right mouse click when data in your worksheet is selected. The most commonly used functions on the Edit menu, some previously described – are:

• Undo
• Cut
• Copy
• Paste
• Fill
• Delete
• Find
• Replace

 Undo

Undo is a very useful function indeed! This function enables you to cancel the last action you performed. In later versions of Excel, it allows you to undo a maximum of 16 actions (although this default number can be changed). However, you cannot undo an action if you have saved the document since you performed that action.

Delete

The Delete function allows you to delete a particular cell, row or column. You can choose in which direction cells are deleted and where the remaining data is shifted.

Find and replace

The Find command allows you to find specific data (text or numbers) or formatting within a worksheet. You can even search within formulas and functions. The Replace command allows you to first find specific data or formatting, then to replace it with other data or formatting. For example, you could find all of the red text in a spreadsheet and change it to green, or you could replace the word “costs” with the word “expenses”. Rather than having to find each instance and change these manually, the Replace command allows to make all of the changes at once.

Learn more about Microsoft Excel >>