Navigation

Previous
Next
Homepage
FAQ
What's new
Sitemap

Topics

Introduction
Basic commands
Presentation
Page layout and printing
Functions
Copy, Paste and
relative and absolute references
Charts (graphics)
Macro commands
Analysis tools
Comments
Data list
Pivot table
Exercise: creating a model
Quiz

Tutorials

Word
Excel

Excel 2007/2010/2013
PowerPoint
Access

Others

Demonstration files
Texte en français

Contact

By e-mail
Join our Newsletter

Excel - Basic operations

Introduction
Moving inside the spreadsheet
Blocs
Data entry
Formulas

• Numbers from another worksheet
• Numbers from another spreadsheet

Edit the content of a cell
The worksheet

• Add a worksheet
• Delete a worksheet
• Rename a worksheet
• Move a worksheet
• Copy a worksheet

Split the screen
Freeze a part of the screen

Introduction

Excel is a spreadsheet. It's role is the creation and analysis of decision-making models. Every document, or file, consists of several worksheets containing 256 columns (named A to IV) and of 65536 rows filled with cells in which you can enter either text, numbers or formulas. Every cell of the document has a unique address. It's determined by the place where it's located in the spreadsheet. For example, the very first cell in the upper left corner of every first sheet meets itself in the column A on the first row. So the address of that cell in regards to it's position in the columns and rows is called A1. It's at the junction of column A and row 1. The cell address just to its right is B1 and so on. Also, every cell can contain up to 65 000 characters.

These cells are all on the first worksheet named Sheet1. You can have up to 100 worksheet in a single spreadsheet file. That will allow you to make "in depth" calculations. That's more than enough room to create your own models. But sometimes, it's useful to get some numbers that are from other spreadsheet files. Excel can help you link those files together.

Moving inside the spreadsheet with only keys

There are several ways to move inside an Excel spreadsheet. You can always click on the worksheet to the cell or the worksheet that you need. Here is the list of keys that you can use to move without using the mouse.

Blocks

There will be times when you will want to select several cells at one time in order to change the formatting, to create charts or to take advantage many other possibilities offered by Excel. The software offers you at least three ways to select a range of cells.

But before starting, you must pay attention to the shape of the cursor. The cursor changes shape according to the place where it's located on the worksheet on even inside a cell. To select cells, the cursor must look like a big white "+" like this. You can't make a block when the pointer is in the shape of an arrow or any other shape. However, you can move a range of cells using that format. Place the pointer on the border of the cell or of a range of cells. Press and hold the left mouse button and move it to it's new location. You can also copy the content of a cell if you place the pointer on the dot at the button right corner of the active cell. The pointer changes to the format of a thin black "+". Using this format, you can copy the contents of the cell or the range of cells vertically or horizontally. So, before selecting a range of cells, always make sure that the cursor is in this format .

1-Use the mouse.

To select a range of cells by using the mouse.

Place the cursor over the first cell of your block.
Press and hold the left mouse button, move the cursor up to the last cell of your block.
Release the mouse button.

This is practical when the range of cells is relatively small. But what to do when you need to make a range of cells that's bigger than the screen? It's in this situation that using the mouse with the Shift key is more practical.

2-Use the Shift key.

Place the cursor over the first cell of your block.
Press the left mouse button.
Move the cursor over the last cell of your block.
Press and hold the Shift key and click the last cell of the block.

By doing a block this way, you don't have to keep a finger on the mouse button. It avoids overshooting the width of the block that you wanted and to go back and fourth until you select the right size.

There is a last method that you should know. Both first possibilities offer you the possibility to make a continuous block. All the cells between both extremities of the block will be selected. But what can you do when the cells that you require are not next to each other? The last method demonstrates how you can make many blocks of cells with cells that are located everywhere in the worksheet.

3-Use theCTRL key.

Make your first range of cells by using the mouse or with the Shift key.
Press and hold theCTRL key and make your second range of cells.
If you need the other blocks of cells, keep your finger on theCTRL key and use the mouse to make as many blocks as you need.

This last technique is very usefulare not located next to eachother. You can use it for example to select blocks that will be the source for a chart. But, it that last box, every block must represent at least a series of data for the chart, not just a lonely cell.

Data entry

There are three types of data that you can enter into a cell : text, numbers and formulas. The other objects, such as charts, drawings and maps, "float" over cells They don't affect the cells under it. To demonstrate the many way of entering your data, follow the following exercise:

Open Excel with a new worksheet.
Click on the A1 cell.
Enter 1 and press the Enter key.

The active cell moved to the A2 cell that's directly below A1.

Enter the text following "To the right" and press the Tab key or Tabulation key (next to the the Q key to the left of on the first row of keys).

The active cell moved to the right of the last cell which is B2. The combination of the Shift and Tab keys would move the active cell back to the left.

Enter the formula =A1+5 and press the down cursor on your keyboard.

You can also enter data and use the cursors to quickly pass to another cell and enter more data. The active cell should be the B3 cell.

Place the cursor on the formula bar.
Enter 4 and press the button or press the Enter key.

As just you discovered, you may enter the contents of a cell by using the Enter, Tab and cursors keys and the formula bar.

Formulas

One of the biggest advantages of a spreadsheet is the automation of the calculations. By entering a formula instead of a number, it's easier to change the model and get the wanted results faster. Formulas should be used in every time it's possible. You use a spreadsheet to analyze and interpret the results. Leave the task of crunching the numbers to the spreadsheet.

Every formulas begins with the +, - or = sign. An Excel functions always begin with a =.

Ex: =a1+a2 , =sum(a1:z20) , =average(a1:z20) , =vlookup(a1,B1:c20,2)

A mistake that's often committed by beginners using an Excel functions is to place a space between the name of the function and the first bracket. Ex: =sum () instead of =sum(). The name of the function and it's first bracket must be glued together: no spaces. Also, some functions need more than one argument or information to work properly such as the =vlookup function above. A comma is used to separate these parameters.

To see the list of all the Excel functions, depending on the version of Excel that you have.

Press the down arrow beside the AutoSum button .
Select the More Functions option.
OR
From the Insert menu, select the Function option.

It's possible to make a formula that uses the contents of cells that are in a different worksheet or even a different file! To do this, you must specify the name of the worksheet with the cell address. An exclamation point (!) is used to separate the name of the worksheet from the cell address.

Ex: =a1+sheet2!A1 , =sum(sheet1!A1:sheet3!A1)

To really take advantage of a spreadsheet, you must be able to use numbers coming from other worksheets and even from other files. The next part consists in explaining how to reach the contents of cells in other worksheets and then from other spreadsheets. If the file does not contain at least three worksheets, you can add them by using from the Insert menu the Worksheet option.

Numbers from another worksheet

From the first worksheet, enter 100 in the A1 cell.
Click on the tab of the second worksheet.
OR
Press theCTRL and the PgDn (Page Down) keys.

In this worksheet, enter 200 in the A1 cell.

And now for the formula. It consists in adding the contents of the A1 cells from the first two worksheets of the file into the third worksheet.

Click on the tab of the third worksheet.
In the A1 cell, enter the = sign.

This is the start of the formula. It's now necessary to select the needed cells in the formula.

Click on the tab of the first worksheet (Sheet1).
From the first worksheet, Click on the A1 cell.
Press the key +.
Click on the tab of the second worksheet (Sheet2).
On this worksheet, Click on the A1 cell.
Press the Enter key.
OR
In the A1 cell of the third worksheet, enter the following formula: =sheet1! A1+sheet2! A1.

The formula is complete. The result should be 300. In fact, to reach any cell of the file that's not on the present worksheet, you must first write the name of the worksheet followed by the exclamation mark and the then address of the cell. Ex: =Sheet1! A1. There is another way of arriving at the same result.

From the third worksheet, place the cursor in the A2 cell.
In the A2 cell, enter the beginning of the formula =sum(
Click on the tab of the first worksheet (Sheet1).
Click on the A1 cell.
Press and hold the Shift key, click the tab of the second worksheet.
To end the formula, close the bracket ) and press the Enter key.
OR
Enter the following formula: =sum(sheet1:sheet2! A1).

To see if the formula works, change the value of the A1 cell of the first worksheet from 100 to 300.

The new result of the A1 and A2 cells of the third worksheet should be 500.

All the functions can be also accomplished in "3D". That is by using the content of cells of other worksheets of the file.

Numbers from another worksheet

Imagine the possibilities if you could also use numbers coming from the other files! It's possible. This exercise demonstrates how to do it.

Open a new file.
Enter 100 the A1 cell.
Save the file under the name test1.xls.
Do not close the file.

The content of the first file is completed. The second file will come to look for this number in a few moments. It's easier to carry out this operation when both files are opened. Excel can open up to 99 files at the same time. To pass from a file to another, go to the Window menu and to choose the required file.

From the File menu, select the New option.
From the Task pane, select the Blank workbook option.
Press the OK button.
OR
From the standard toolbar, press the button.
Place the cursor in the A1cell.
Press the key =.

From the Window menu, select the Test1 file.
OR
For those using Excel 2000 or a later version, click on the Test1 file on the taskbar at the bottom of the screen.

Click on the cell that you need.

For this exercise, select the A1 cell.
Press the Enter key.
OR
Press the button of the formula bar.

The formula should be =[ test1.xls ]Sheet1!$A$1.The name of the file is between brackets, followed by the name of the worksheet, an exclamation mark and the cell address.

Save the file under the name Test2.xls.
Close the Test1.xls and test2.xls files.
Open once again the Test2.xls file.

Every time you'll open this file, it's going to ask you is you wish to verify and update the references of the other files. For this example, it's about the contents of the Test1.xls file. To update the file, you must press the Yes button. Otherwise, the file will use the same data from the last update.

You must also pay attention where you put the "linked" file. If you delete it, or move it or rename the reference file, Excel will not be able to find it.

If you moved or renamed the file, you will have to indicate to Excel the new location or the new name of the file. After you selected to correct file, press the OK button to update the link.

You'll find more information about some Excel functions on the Web page on functions and formulas on this site.

Edit a cell's content

Instead of having to rewrite the contents of a cell, you can change it's contents to correct "typos" or the formula. There are three ways to change a cell's content.

You must first place the cursor in the cell to be changed. Then,

Press the F2 key.
OR
Double-click in the cell to be changed
OR
Click in the formula bar to change the contents.

The worksheets

It's on the worksheets that you can write down the text, the numbers, the formulas and the other elements of our model. It's possible that you want to distribute your model on to several worksheets. Every worksheet has a name that you will find at the bottom of the screen. By default, they are called Sheet1 , Sheet2 . This next part is going you to demonstrate how to add worksheets, to rename them, to move them and even to copy a complete worksheet.

Add a worksheet

Even by placing your numbers on the worksheets that are shown, it's very possible that you need more worksheets. A spreadsheet can contain up to 100 worksheets.

From the Insert menu, select the Sheet option.
OR
Place the cursor on the tab of one of the worksheets.
Press the right mouse button.
From the context menu, select the Insert option.

Delete a worksheet

For aesthetic needs or any other reason, it's always possible to remove a worksheet of the file.

Place the cursor on the worksheet that you want to remove.
From the Edit menu, select the Delete a sheet option.
Confirm the deletion by pressing on the OK button.

Rename a worksheet

It's much more practical to help you to find of the information you need when the names of worksheets are more familiar to you such as "Budget" or "Forecasts" instead of Sheet1. There are various ways to rename a worksheet.

From the Format menu, select the options Sheet and Rename.
OR
Double-click on the tab of the worksheet.
OR
Place the cursor over the tab of the worksheet.
Press on the right mouse button
From the context menu, select the Rename option.

Move a worksheet

Excel offers you also the possibility to quickly move a worksheet in your spreadsheet. You can move them between other worksheets.

Place the cursor over the tab of the worksheet to be moved.
Press and hold the left mouse button, move the tab towards the left or the right according to your needs.

A small black arrow will indicate you the point of insertion if you Release the mouse button at this moment.

Copy a worksheet

Excel allows you also to copy all the contents of a working sheet with a variation of the last technique.

Place the cursor over the tab of the worksheet to be copied.
While pressing on theCTRL key and on the left mouse button, move the new worksheet in the wished place.

Copy the contents without creating a new worksheet

Click the gray box of the upper left corner of the worksheet; between the " A " and " 1 " headers.

From the Edit menu, select the Copy option.
Place the cursor on the worksheet on that you want to copy the data.
Place the cursor in the cell where you want to insert the data.
From the Edit menu, select the Paste option.

Split the screen

This option splits the screen into several windows. This is very practical when you want to compare data that's distant from one to another. For example, you want to compare this month's numbers with last year's or the forecast for next year. However, there is twelve months worth of data separates the two. The next exercise consists of showing you how to put two cells next to another by splitting the screen.

Open a new file or place the cursor on a new worksheet.
Place the cursor in the D6 cell.
From the Windows menu, select the Split option.

The worksheet is now separated into four parts. It's also possible to separate the screen only vertically or horizontally. Just above the vertical bar and to the right of the horizontal bar you will find the divider bars. You can place them in any place on the worksheet. You can also change the place any time or even to remove them. Just place the cursor on the divider bar, press and hold the left mouse button, and "pull" it in its new location.

Press the F6 key several times.

The F6 key serves for moving from one area to another. If you press it several times, you'll notice that the cursor moves in a clockwise fashion. The Shift and F6 keys "turns" the cursor counter clockwise. Zones move in a synchronized way as you move horizontally or vertically.

To remove the divider bars

From the Windows menu, select the Delete the dividers option.
OR
Place the cursor on the divider bar.
Press and hold the left mouse button, move the bar towards one of the borders of the screen.
OR
Double-click on the divider bar to be removed.

Freeze a part of the screen

On a worksheet, it's important to have titles that describe the numbers. It's important to see these titles at all times. After all, the numbers are used to describe something. The Excel designers, as well as the other spreadsheet programs available on the market, recognized this fact. The option to freeze part of the screen allows you to always have certain area visible on the screen.

Write the text in the appropriate cells.
Place the cursor in the B2 cell.
From the Window menu, select the Freeze shutters option.
Press several times the PgDn (PageDown) key.

Although you moved on the worksheet, the text that you wrote above and to the left of the place that you fixed the shutters is always visible. To return to a normal page.

From the Windows menu, select the Release shutters option.