آموزش کامل Excel 2010-EN

[A M I R]

Creating Simple Formulas
​

To Create a Formula Using Cell References:



Select the cell where the answer will appear (B3, for example).

Type the equal sign (=).



Type the cell address that contains the first number in the equation (B1, for example).

Type the operator you need for your formula. For example, type the addition sign (+).



Type the cell address that contains the second number in the equation (B2, for example).

Press Enter. The formula will be calculated and the value will be displayed in the cell.

If you change a value in either B1 or B2, the total will automatically recalculate.

​

​

 

[A M I R]

[h=2]Creating Simple Formulas


[h=4]To Create a Formula using the Point and Click Method:

Select the cell where the answer will appear (B4, for example).

Type the equal sign (=).


Click on the first cell to be included in the formula (A3, for example).

Type the operator you need for your formula. For example, type the multiplication sign (*).


Click on the next cell in the formula (B3, for example).

Press Enter. The formula will be calculated and the value will be displayed in the cell.

​

[h=2]

 

[A M I R]

[h=2]Creating Simple Formulas

[h=4]To Edit a Formula:



Click on the cell you want to edit.


Insert the cursor in the formula bar and edit the formula as desired. You can also double-click the cell to view and edit the formula directly from the cell.


When finished, press Enter or select the Enter command

.

The new value will be displayed in the cell.

If you change your mind, use the Cancel command

in the formula bar to avoid accidentally making changes to your formula.

​

 

[A M I R]

[h=2]Creating Simple Formulas



[h=3]Challenge!



Open an existing Excel workbook. If you want, you can use this example.


Write a simple division formula. If you are using the example, write the formula in cell B18 to calculate the painting cost per square foot.


Write a simple addition formula using cell references. If you are using the example, write the formula in cell F5 to calculate the "Total Budget."



Write a simple subtraction formula using the point and click method. If you are using the example, subtract the

"Expand Bathroom" cost (C6) from the "Total" cost (C11). Calculate your answer in C12.


Edit a formula using the formula bar.
​

[h=2]

 

[A M I R]

[h=2]Worksheet Basics

[h=3]Introduction

Every Excel workbook contains at least one or more worksheets. If you are working with a large amount of related data, you

can use worksheets to help organize your data and make it easier to work with.


In this lesson, you will learn how to name and add color to worksheet tabs, and how to add, delete, copy, and

move worksheets. Additionally, you will learn how to group and ungroup worksheets, and freeze columns

and rows in worksheets so that they remain visible even when you are scrolling.

​

 

[A M I R]

[h=2]Worksheet Basics

[h=3]Introduction to Worksheets
When you open an Excel workbook, there are three worksheets by default. The default names on the

worksheet tabs are Sheet1, Sheet2 and Sheet3. To organize your workbook and make it easier to

navigate, you can rename and even color-code the worksheet tabs. Additionally, you can insert, delete, move, and copy worksheets.






​

 

[A M I R]

[h=2]Worksheet Basics

[h=4]To Rename Worksheets:
Right-click the worksheet tab you want to rename. The worksheet menu appears.



Select Rename.

1. 
   
   
   
   
   
   

The text is now highlighted by a black box. Type the name of your worksheet.

1. 
   
   
   
   
   
   
2. Click anywhere outside of the tab. The worksheet is renamed.
3. 
   
   
   
   

 

[A M I R]

[h=2]Worksheet Basics

[h=4]To Insert New Worksheets:

Click on the Insert Worksheet icon. A new worksheet will appear.

You can change the setting for the default number of worksheets that appear in Excel

workbooks. To access this setting, go into Backstage view and click on Options.

​

 

[A M I R]

[h=2]Worksheet Basics

[h=4]To Delete Worksheets:

Worksheets can be deleted from a workbook, including those that contain data.


Select the worksheets you want to delete.



Right-click one of the selected worksheets. The worksheet menu appears.


Select Delete. The selected worksheets will be deleted from your workbook.

​

 

[A M I R]

[h=2]Worksheet Basics

[h=4]To Copy a Worksheet:

Right-click the worksheet you want to copy. The worksheet menu appears.


Select Move or Copy.

The Move or Copy dialog box appears. Check the Create a copy box.

Click OK. Your worksheet is copied. It will have the same title as your original worksheet, but the title will include a version number, such as "January (2)".

​

 

[A M I R]

[h=2]Worksheet Basics

[h=4]To Move a Worksheet:
Click on the worksheet you want to move. The mouse will change to show a small worksheet icon

.

Drag the worksheet icon until a small black arrow

appears where you want the worksheet to be moved.

Release your mouse and the worksheet will be moved.

​

 

[A M I R]

[h=2]Worksheet Basics

[h=4]To Color-Code Worksheet Tabs:

You can color worksheet tabs to help organize your worksheets and make your workbook easier to navigate.



Right-click the worksheet tab you want to color. The worksheet menu appears.

Select Tab Color. The color menu appears.


Select the color you want to change your tab.

The tab color will change in the workbook. If your tab still appears white, that is

because the worksheet is still selected. Select any other worksheet tab to see the color change.

​

 

[A M I R]

[h=2]Worksheet Basics

[h=3]Grouping and Ungrouping Worksheets

You can work with each worksheet in a workbook individually, or you can work with multiple worksheets at

the same time. Worksheets can be combined together into a group. Any changes made to one

worksheet in a group will be made to every worksheet in the group.






​

 

[A M I R]

[h=2]Worksheet Basics

[h=4]To Group Worksheets:

Select the first worksheet you want in the group.

Press and hold the Ctrl key on your keyboard.

Select the next worksheet you want in the group. Continue to select worksheets until all of the worksheets you want to group are selected.

Release the Ctrl key. The worksheets are now grouped. The worksheet tabs appear white for the grouped worksheets.

While worksheets are grouped, you can navigate to any worksheet in the group and make changes that will

appear on every worksheet in the group. If you click on a worksheet tab that is not in the group, however, all of

your worksheets will become ungrouped. You will have to regroup them.






​

 

[A M I R]

[h=2]Worksheet Basics

[h=4]To Ungroup All Worksheets:

Right-click one of the worksheets. The worksheet menu appears.


Select Ungroup. The worksheets will be ungrouped.
​

 

[A M I R]

[h=2]Worksheet Basics

[h=3]Freezing Worksheet Panes

The ability to freeze specific rows or columns in your worksheet can be a very useful feature in Excel. It is called freezing panes. When

you freeze panes, you select rows or columns that will remain visible all the time, even as you are scrolling. This is particularly

helpful when working with large spreadsheets.

​

 

[A M I R]

[h=2]Worksheet Basics

[h=4]To Freeze Rows:

Select the row below the rows that you want frozen. For example, if

you want rows 1 & 2 to always appear at the top of the worksheet even as you scroll, then select row 3.

Click the View tab.



Click the Freeze Panes command. A drop-down menu appears.


Select Freeze Panes.

A black line appears below the rows that are frozen in place. Scroll down in the worksheet to see the rows below the frozen rows.

 

[A M I R]

[h=2]Worksheet Basics

[h=4]To Freeze Columns:
Select the column to the right of the columns you want frozen. For example, if you want

columns A & B to always appear to the left of the worksheet even as you scroll, then select column C.

Click the View tab.

Click the Freeze Panes command. A drop-down menu appears.

Select Freeze Panes.

A black line appears to the right of the frozen area. Scroll across the worksheet to see the columns to the right of the frozen columns.

​

 

[A M I R]

[h=2]Worksheet Basics

[h=4]To Unfreeze Panes:
Click the View tab.

Click the Freeze Panes command. A drop-down menu appears.

Select Unfreeze Panes. The panes will be unfrozen and the black line will disappear.

​

 

[A M I R]

[h=2]Worksheet Basics

[h=3]Challenge!

Open an existing Excel 2010 workbook. If you want, you can use this example.

Insert a new worksheet.

Change the name of a worksheet.

Delete a worksheet.

Move a worksheet.

Copy a worksheet.

Try grouping and ungrouping worksheets.

Try freezing and unfreezing columns and rows.
​