Course PDF to learn excel quickly


Télécharger Course PDF to learn excel quickly

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 :


   

Topics for Discussion

n   Spreadsheet Basics

n   Modifying A Worksheet 

n   Formatting Cells 

n   Formulas and Functions 

n   Sorting and Filling 

n   Charts 

n   Page Properties and Printing 

 

n   Excel allows you to create spreadsheets much like paper ledgers that can perform automatic calculations. 

n   Each Excel file is a workbook that can hold many worksheets.

n   The worksheet is a grid of columns (designated by letters) and rows (designated by numbers).

n   The letters and numbers of the columns and rows (called labels) are displayed in gray buttons across the top and left side of the worksheet.

n   The intersection of a column and a row is called a cell.

n   Each cell on the spreadsheet has a cell address that is the column letter and the row number.

n   Cells can contain either text, numbers, or mathematical formulas. 

   

n   The worksheets in a workbook are accessible by clicking the worksheet tabs.

n   By default, three worksheets are included in each workbook.

n   To add a sheet, select Insert|Worksheet from the menu bar  .

Renaming Worksheets

n   To rename the worksheet tab, right-click on the tab with the mouse and select Rename from the shortcut menu.

n   Type the new name and press the ENTER key. 

The Standard Toolbar

n    Located just below the menu bar at the top of the screen and allows you to quickly access basic Excel commands. 

         

Save

 

n    Use the spell checker to correct spelling errors on the worksheet.  

 

Undo and Redo Actions

n    Click the backward Undo arrow to cancel the last action you performed, whether it be entering data into a cell, formatting a cell, entering a function, etc.

n    Click the forward Redo arrow to cancel the undo action. 

 

Modifying a Worksheet

n    Moving Through Cells 

n    Adding Worksheets, Rows, and Columns

n    Resizing Rows and Columns 

n    Selecting Cells 

n    Moving and Copying Cells 

Moving Through Cells

n    Use the mouse to select a cell you want to begin adding data to and use the keyboard strokes listed in the table below to move through the cells of a worksheet.

Moving Through Cells

Movement

Keystroke

One cell up 

up arrow key 

One cell down 

down arrow key or ENTER

One cell left 

left arrow key 

One cell right 

right arrow key or TAB 

Top of the worksheet (cell A1) 

CTRL+HOME

End of the worksheet (last cell containing data)  

CTRL+END

End of the row 

CTRL+right arrow key 

End of the column 

CTRL+down arrow key 

 

n   Add a worksheet to a workbook by selecting Insert|Worksheet from the menu bar. 

   

Resizing Rows and Columns

n   Resize a row by dragging the line below the label of the row you would like to resize. Resize a column in a similar manner by dragging the line to the right of the label corresponding to the column you want to resize.

n   - OR – 

n   Click the row or column label and select Format|Row|Height or Format|Column|Width from the

menu bar to enter a numerical value for the height of the row or width of the column. 

 

n   Before a cell can be modified or formatted, it must first be selected (highlighted). 

n   Click on an individual cell, Column header letter, Row header number to select a an individual Cell, Entire Column and entire row of a worksheet.

n   To activate the contents of a cell, double-click on the cell or click once and press F2

Selecting an Individual Cell

 

Selecting an Entire Row

   

Select an Entire Worksheet

n    Click the Select All cell at the top left corner of the worksheet to select the entire worksheet.

 

Select a range of Cells

n    Click on the first cell in the range and drag over the cells you want in the range.



n    -OR-

n    Click on the first cell in the range move the mouse to the opposing cell in the range , hold down the Shift key and click on the opposing cell. The following is the example of selecting a range from  cells A1 to D4.  

 

n    To move the contents of one cell to another cell select Edit|Cut from the menu bar or click the Cut button on the standard toolbar. 

   

n    To copy the cell contents, select Edit|Copy from the menu bar 

n    -OR-

n    Click the Copy button on the standard toolbar. 

 

Pasting Cut and Copied Cells 

n    Highlight the cell you want to paste the cut or copied content into and select Edit|Paste from the menu bar

n    -OR-

n    Click the Paste button on the standard toolbar. 


Freeze Panes

n   If you have a large worksheet with column and row headings, those headings will disappear as the worksheet is scrolled. By using the Freeze Panes feature, the headings can be visible at all times.

n   Click the label of the row below the row that should remain frozen at the top of the worksheet. 

n   Select Window|Freeze Panes from the menu bar. 

n   To remove the frozen panes, select Window|Unfreeze Panes.

 

n   The contents of a highlighted cell can be formatted in many ways. 

n   Font and cell attributes can be added from shortcut buttons on the formatting bar.

n   If this toolbar is not already visible on the screen, select View|Toolbars|Formatting from the menu bar.

   

Format Cells Dialog Box

n   Number tab - The data

type can be selected from the options on this tab. 

n   Select General if the cell contains text and number, or another numerical category if the cell is a number that will be included in functions or formulas. 


 

n   Alignment tab - These options allow you to change the position and alignment of the data within the cell

 

n   Font tab - All of the font attributes are displayed in this tab including font face, size, style, and effects. 

 

n   Border and Pattern tabs - These tabs allow you to add borders, shading, and background colors to a cell. 


 

n   Highlight the cells that will be formatted.

n   Select Format|AutoFormat from the menu bar. 

 

n   On the AutoFormat dialog box, select the format you want to apply to the table by clicking on it with the mouse. Use the scroll bar to view all of the formats available.

n   Click the Options button to select the elements that the formatting will apply to. 

n   Click OK when finished. 

Formulas and Functions

n   The distinguishing feature of Excel is that it allows you to create mathematical formulas and execute functions. Otherwise, it is not much more than a large table for displaying text.

n   This section will show you how to perform these calculations. 

 

n   Formulas are entered in the worksheet cell and must begin with an equal sign "=".

n   The formula then includes the addresses of the cells whose values will be manipulated with appropriate operands placed in between.

n   After the formula is typed into the cell, the calculation executes immediately and the formula itself is visible in the formula bar.

 

n   See the example to view the formula for calculating the sub total for a number of textbooks. 

n   The formula multiplies the quantity and price of each textbook and adds the subtotal for each book.

 

n   Functions can be a more efficient way of performing mathematical operations than formulas.

n   For example, if you wanted to add the values of cells D1 through D10, you would type the formula

"=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10".

n   A shorter way would be to use the SUM function and simply type "=SUM(D1:D10)".

n   Several other functions and examples are given in the table below

   

n   View all functions available in Excel by using the Function Wizard.

 

 

n   To execute a basic descending or ascending sort based on one column, highlight the cells that will be sorted and click the Sort Ascending (A-Z) button or Sort Descending (Z-A) button on the standard toolbar. 

 

n   The Autofill feature allows you to quickly fill cells with repetitive or sequential data such as chronological dates or numbers, and repeated text.

n   Type the beginning number or date of an incrementing series or the text that will be repeated into a cell. 

n   Select the handle at the bottom, right corner of the cell with the left mouse button and drag it down as many cells as you want to fill. 

n   Release the mouse button. 

AutoFilling Functions

n   Autofill can also be used to copy functions.  



n   In the example below, column A and column B each contain lists of numbers and column C contains the sums of columns A and B for each row.

n   The function in cell C2 would be "=SUM(A2:B2)".

n   This function can then be copied to the remaining cells of column C by activating cell C2 and dragging the handle down to fill in the remaining cells. The autofill feature will automatically update the row numbers as shown below if the cells are reference relatively.

AutoFilling Function Cont…

   

n   Charts allow you to present data entered into the worksheet in a visual format using a variety of graph types. 

n   Before you can make a chart you must first enter data into a worksheet.

n   This section explains how you can create simple charts from the data. 

 

n   The Chart Wizard brings you through the process of creating a chart by displaying a series of dialog boxes.

n   Enter the data into the worksheet and highlight all the cells that will be included in the chart including headers.


n   Click the Chart Wizard button on the standard toolbar to view the first

Chart Wizard dialog box. 

n   Choose the Chart type and the Chart subtype if necessary. Click Next.

n   Chart Source Data:

n   Select the data range (if different from the area highlighted in  step 1) and click Next.

n   Chart Options:

n   Enter the name of the chart and titles for the X- and Yaxes. Press Next to move to the next set of options.

n   Chart Location :

n   Click As new sheet if the chart should be placed on a new, blank worksheet or select As object in if the chart should be embedded in an existing sheet and select the worksheet from the drop-down menu. 

n   Click Finish to create the chart.

 

Page Properties and Printing

n  Page Setup

n  Print Preview

n  Print

   

n  Select the Orientation under the Page tab in the Page Setup window to make the page Landscape or Portrait. 

n  The size of the worksheet on the page can also be formatting under Scaling.

n  To force a worksheet to print only one page wide so all the columns appear on the same page, select Fit to 1 page(s) wide.


 

n  Change the top, bottom, left, and right margins under the Margins tab. 

n  Enter values in the header and footer fields to indicate how far from the edge of the page this text should appear.

n  Check the boxes for centering horizontally or vertically on the page.

Headers and Footers

n   Add preset headers and footers to the page by clicking the drop-down menus under the Header/Footer tab.

n   To modify a preset header or footer, or to make your own, click the Custom Header and Custom Footer buttons.

n   A new window will open

allowing you to enter text in the left, center, or right on the page.

Headers and Footers Cont..

n   Format Text - Click this button after highlighting the text to change the font, size, and style.

n   Page Number - Insert the page number of each page.

n   Total Number of Pages - Use this feature along with the page number to create strings such as "page 1 of 15".

n   Date - Add the current date.

n   Time - Add the current time.

n   File Name - Add the name of the workbook file.

n   Tab Name - Add the name of the worksheet's tab.

 

n   Check Gridlines if you want the gridlines dividing the cells to be printed on the page. 

n   If the worksheet is several pages long and only the first page includes titles for the columns, select Rows to repeat at top to choose a title row that will be printed at the top of each page.

 

n   Select File|Print Preview from the menu bar to view how the worksheet will print.

 

   

n   Copies - Choose the number of copies that should be printed. Check the Collate box if the pages should remain in order. 

n   Click OK to Print the Worksheet.

n   Note: Excel files have an extension of  .XLS.


 



1