EXCEL tutorial practice sheets


Télécharger EXCEL tutorial practice sheets

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


   

Practice: Naming Ranges

 

Return to Contents

Exercise 1-Name a range

 

1.    Select range B6:C11.

2.    From Excel's menus choose    Insert, Name, Create.

3.    In the "Create Names" dialog,     click "Left column" and then    "OK".

 

January                 30

February                45

March                    22

April                      18

May                       10

June                      58

Exercise 2-Name a range

 

1.    Select range B20:B22.

2.    From Excel's menus choose    Insert, Name, Define.

3.    In the "Define Name" dialog,     supply a name for the selected     range or if Excel has provided     a name accept its suggestion.

4.    Click "OK".

 

January Data

35

44 66

Exercise 3-Name a range

1.    Select range B37:C39.

2.    Click in the "Name Box" at the    left of Excel's formula bar.

3.    In the "Name Box" type the     text Aqua_Range and hit the     enter key.

Range name text typed into the "Na at the left of Excel's formula b

Exercise 4-Display range names

1. Display the range names you've assigned by     clicking the drop-down arrow in the "Name

    Box".                                                                                                                                                    

Box".

2. Document in the worksheet the range names     you've assigned by clicking a cell in a blank     area of the worksheet and choosing the     commands Insert, Name, Paste, Paste List.

Exercise 5-Add a cell comment

100%

1. Hover the mouse pointer over the red triangle in    the cell above to see the associated comment. 2. Enter a value in an empty cell*. With that cell     selected, choose Insert, Comment from     Excel's menus. -Or-right click the cell and     choose Insert Comment from the pop-up menu     that displays.

3. Enter your comment in the text box provided.

* You can also add a comment to an empty cell.


 

An example of "paste listed" range n


 

Excel makes its best guess as to where your labels are in relation to your data. You may have more than one option selected. For example, "Top row" AND "Left column".

 

ame Box" bar. names.

Exercise 6-Merge and Center

1.    Select range B6:G6.

2.    From Excel's formatting     click the "Merge and Cen     tool.

* From

Exercise 7-Apply Formatting

Select each of the five cells in turn in the range B18 to B22. Format the cell by clicking the appropriate formatting tool button from the Formatting toolbar.

 

100  Currency

100  Percentage

1000000  Thousands comma separator

100.00  Increase decimals

Practice: Formatting

Return to Contents

100.00  Decrease decimals

   

Select the range of colored cells at left and use a border tool on the formatting toolbar to add a thick border around the outside. Your bordered range should look like this:

 

Exercise 9-Create a Text Box

Click the Text Box tool on the Drawing toolbar. Drag a rectangular shape at left, and enter text into the box. To add special formatting, rightclick an edge of the text box and choose "Format Text Box".

     

Exercise 10-Use the Format Painter

January

February

     
 

March

Use the Format Painter button on Excel's Standard Toolbar to quickly format the range B63:C66 in the same way as the range formatted at left.

 

Sales

$5,400.00

$3,152.00 $6,582.00

Sales

             January         $5,400.00

             February        $3,152.00

             March            $6,582.00

 toolbar nter"

 

 

 

Practice: Basic Editing

 

Return to Contents

Exercise 11-Edit Fill

   

1.    Select range B7:B8 at left.



2.    Position the pointer on the "fill box", the small black square in the lower     right corner of the selected range.

3.    Drag the fill box down so Excel     continues the sequence of numbers.

Do the same for C7:C8 and D7:D8.

1.    Select Cell B25 at left.

2.    Position the pointer on the "fill box",     the small black square in the lower     right corner of the selected range.

3.    Drag down several rows. Excel will fill     the cells with months of the year.

Follow the same process for Cell C25.

 

Exercise 12-Use Excel Custom Lists

         January     Sunday

       Exercise 13-Copy & Paste a Formula             Method 1

1. Make D47 the current cell.

                   Referenced value:         6%                          2. In the formula bar, drag over the formula,

Another referenced value:       100 and hit CTRL+C (Edit, Copy), then hit the

                                Formula:        6                            escape key.

3. Click in cell B49 and hit CTRL+V (Edit,    Paste). The same result (6) should    display. Excel does not adjust the cell     references in the formula.

    references in the formula.

                                                                                 Method 2

1.    Again make D47 the current cell and    click CTRL+C.

2.    Click in Cell B51 and hit CTRL+V. A     different result (0) should display. Excel     adjusts the cell references in the     copied formula.

Exercise 14-Edit Copy & Edit Paste Special to Convert Formulas to Values

1.    Click each of the cells B65 to B69 at left     and see in the formula bar that each is a     formula.

2.    Select the range B65:B69.

3.    From the menus choose Edit, Copy.

4.    Click Cell D65 and choose Edit, Paste    Special.  

5.    In the "Paste Special" dialog, toggle on     the "Values" option and click OK.

The numbers in the range D65:D69 should     appear the same as the numbers in the     range B65:B69. However, click each     value in the D column and see in the     formula bar that each has been     transformed from a formula to a constant

       Formulas                      Values

46

54 143 100 14

.

Exercise 15-Edit Copy & Edit Paste Special to Transpose Data

Data in Rows

       January                  55             35

       February                 23             29

       March                     12             18

Transposed Data


     
 
 

   

.


 

 

Practice: Formulas

 

Return to Contents

Exercise 16-Copying a Formula Using a Relative Reference

1. Check to see that the cell C11 at lef     holds the SUM formula =SUM(C8:C 2. Make cell C11 the current cell.

3. Position the mouse pointer on the f    black square at the lower right-hand    corner of cell C11 and drag the poin    across to cell E11. The result should    like this:

4

    =

. Examine the copied formulas in cell

D11 and E11. Excel has adjusted th cell references so they refer to the correct values in their columns. Tha

SUM(C8:C10) becomes =SUM(D and =SUM(E8:E10).

                        Quarter 1    Quarter 2 Quarter 3

        March           $500          $250            $35

           April           $300          $120            $45

            May           $100            $95                                                                               $55 i

Total: $   900.00

8

Exercise 17-Copying a Formula Using an Absolute Reference


Tax rate:         4%

                        Quarter 1     Quarter 2 Quarter 3

        March           $500          $250             $35

           April           $300          $120             $45

            May           $100            $95            $55

 

            Tax:             $36

Example

                         Quarter 1       Quarter 2 Quarter 3

        March           $500          $250             $35

            April           $300          $120             $45

May $100 $95 $55 Tax: $36 $19 $5

1. Check to see that the cell C35 at le     holds the formula =SUM(C32:C34) 2. Make cell C35 the current cell.

3. Position the mouse pointer on the f    black square at the lower right-hand    corner of cell C11 and drag the poin    across to cell E35. The result shoul    like this:

    The formulas in Cells D35 and E35     incorrect as copied. Excel has used     default relative referencing in the c     formulas but that's not appropriate     the reference to the tax rate in Cell 4. Modify the "master formula" in Cell     so it looks like this:

=SUM(C32:C34)*$C$29     and then copy the modified formula     across for Quarters 2 and 3 to get     correct results.

Check the completed example (with g background) to see another instance.


 

Exercise 18-Use Built-in Functions

1.    Write a function in each of Cells C    at left to calculate the sum, average minimum value, and maximum valu     the range C59:C62 (named SALES     Your result should look like this:



2.    Enter the TODAY function in Cell      C     return the current date. The syntax     the function is: =TODAY()

6

Sales

May $        235

June $        544

July $        829

August $        610

Sum: Average: Min: Max: Today's date:

Exercise 19-Using Excel Logical Functions

1.    Write an IF function in Cell C82 tha    compares the sales in Quarters 1 a     and returns the text "Q1 better than     "Q2 better than Q1". Your formula s     look like this:

    =IF(C77>C78, "Q1 better than Q2"

"Q2 better than Q1.")

2.    Write an IF statement in Cell C84 th    includes a nested MAX function an returns the text "Exceeded $600M     in one quarter" if any quarter meets     criteria or "Quota not met" if not. Yo     formula should look like this:

=IF(MAX(C77:C80)>600, "Exceede    $600M sales in 1 quarter","Quota no    met")

3.    Write an IF statement in Cell 86 tha    compares sales in Q1 and Q2. If Q     sales are greater, return the differe     If Q1 sales are less, return the incre     Your formula should look like this:

    =IF(C77>C78, C77-C78, C78-C77)

Sales ($ millions)

                           Quarter 1              500

                           Quarter 2              350

                           Quarter 3              495

                           Quarter 4              620

Which did better? Met $600M Q goal? Q1 vs. Q2:

Exercise 20-Using Excel's Formula Auditing Tools

 

1. Turn on Excel's "Formula Auditing"

1.    Turn on Excel's "Formula Auditing"     toolbar by choosing View, Toolbars,    Formula Auditing from Excel's menu.

2.    Click Cell C105 and click the "Trace    Precedents" button on the toolbar to    the values used by the C105 formula 3. Click Cell C101 and click the "Trace    Dependents" button on the toolbar to    the formula values that depend on th    rate value in C101.

4. Click the "Remove All Arrows" button     the toolbar to remove auditing indica

C10).

illed

ter d look

ls

10)

at is,

D

ft

d

he

8:

   

Tip-Building a Formula with Absolute Addressing

An absolute reference is indicated by the dollar signs before the row and column indicators; e.g., $C$29. An alternative to typing in the dollar signs is to

1.    Position the mouse pointer on the cell reference in the formula bar.

2.    Tap the F4 key until the type of reference you want is displayed.

The F4 key toggles through four options:

C29     - relative

$C$29  - absolute row and column

$C29    - absolute column, relative row

C$29    - absolute row, relative column

 

64:C67 e, ue in S).  

C69 to of

at and 2 n 2" or should

",

hat

nd that  sales s that our

ed ot

at Q1 ence. ease.

)

 

.


 

Practice: Data Tables

 

Return to Contents

 

Exercise 21-The One-Input Data Table

1.  Complete the one-input Data Table that varies interest rate by highlighting the range B22     choosing Data, Table from Excel's menus, and entering the model interest rate cell (D6)     the "Column" prompt. Hit OK.

2.  Complete the one-input Data Tables below that vary term and principal in the same fashi

                         Vary Interest Input                             Vary Term Input                              Vary Principal

Principal

$100,000 $150,000 $200,000 $250,000 $300,000 $350,000 $400,000

Exercise 22-The Two-Input Data Table

Complete the two-input Data Table that varies both interest rate and term by highlighting the range B43:H50, choosing Data, Table from Excel's menus, entering the model interest rate cell (D6) in the "Column" prompt, and entering the model term (D7) in the "Row" prompt. Hit OK to complete execution.

   

2:C29, in ion.

l Input

 
 

Practice: Charting

 

Return to Contents

Exercise 23-Generate a Quick Chart

Sales

                          Quarter 1          $500

                          Quarter 2          $550

                          Quarter 3          $650

Exercise 24-Use the Chart Wizard to Create a Chart

   

     scatter plot should look somethin


 

Excel's hart )" chart s. Your ng like the ng like the



12