EXCEL training education and learning

Télécharger EXCEL training education and learning

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 :

EXCEL training education and learning


  1. Excel Basics

This section explains how to get started with Excel with the basic operations of opening, closing and saving an Excel file, known as a workbook. A workbook is comprised of multiple spreadsheets, known as worksheets, which can be used and manipulated separately.

1.1 Opening/Closing the file

The first step to using Excel is launching the program and knowing how to close it when you’re finished.

To open a new Excel workbook: Simply double click the Excel icon and select the

Spreadsheet option

In order to close the workbook: Click the “X” in the upper right hand corner of the window

1.2 Saving the file

It is of extreme importance that you save your Excel workbook as you progress through your analysis to ensure your work is not lost for any reason.

In order to save your Excel file: Select the File tab at the top left to go to the

Backstage. Select Save As and navigate to the correct directory using Browse to locate your folder. Give a descriptive file name and save it as an Excel Workbook, meaning it will have “.xlsx” as an extension, indicating that it is an Excel 2013 document.

The saving process can be expedited by using the keyboard shortcut <Ctrl> and S, which automatically saves the document to the directory specified during the first save. Use this shortcut to quickly save while progressing through your analysis.

1.3 Installing the “Analysis ToolPak” for PC

Plugins in Excel can be installed to increase the functionality of the program and allow the user to complete operations that the original program doesn’t have automatically installed. Many engineering courses require the “Analysis ToolPak” plugin to be installed in Excel.

To install the “Analysis ToolPak”: Click the File tab to bring up the Backstage, and then select Options in order to bring up the Excel

Options menu. Navigate to the Add-Ins tab on the left menu. At the bottom, click the Go button beside the drop down menu, ensuring that you are managing Excel Add-ins. A pop-up window will appear with several unchecked boxes. Check the box that corresponds to “Analysis ToolPak”, and then click OK.

Microsoft Excel 2016 Tutorial | 2

1.4 Installing the “Analysis ToolPak” for Mac

Plugins in Excel can be installed to increase the functionality of the program and allow the user to complete operations that the original program doesn’t have automatically installed. Many engineering courses require the “Analysis ToolPak” plugin to be installed in Excel.

Please note that in Microsoft Excel 2016 the “Analysis ToolPak” is now available for Mac.

It is expected that all students are working in the 2016 version of Microsoft Excel.

To install the “Analysis ToolPak”: Click the Tools tab and select Add-Ins. Navigate to the Add-Ins available box, select the “Analysis

ToolPak” check box, and click OK. If the “Analysis

ToolPak” is not listed, click Browse to search for it.

If the “Analysis ToolPak” does not appear installed on your computer, click Yes to install it.

You should quit and restart Microsoft Excel.

Data Analysis should now be available on the Data tab.

  1. Entering data into Excel

Using a spreadsheet to effectively visualize and analyze data requires proper formatting. The boxes that make up the spreadsheet are called ‘cells’, and each cell can be characterized by its row (numbered) and column (lettered). An example of a cell referenced using its row and column is H7, which is cell in the 7th row of column H. This is especially useful to know when working with formulas.

2.1 Simple cell formatting

This section describes how to enter and format numbers into a spreadsheet.

2.1.1 Entering data into cells

The first thing to know when using worksheets is how to enter data into a cell.

To do this, simply type in all the raw data by leftclicking the cell under the correct column and typing in the numbers only, NO UNITS. In Excel, cells with numbers are automatically right aligned and cells with any lettered elements are left alighted. This can be changed using the Alignment options in the Home tab.

2.1.2 Spreadsheet Organization

It can be useful to organize worksheets using an identification section, especially when you have more than one Excel document in progress at one time. An identification section includes a title, the author’s name, the date of creation, and the file name.

To add an identification section: Click on cell A1 of Sheet 1 to make it the active cell.

Type the title and press <Enter>. The cursor should then move to Cell A2. Type in your name and the name of the file in cells A2 and A3 respectively.

The name of the file may or may not be the same as the title. Select cell C2 and type today’s date in the format year/month/date and press <Enter>.

It’s also useful to name worksheets, especially if you’re using more than one worksheet in a workbook.

To rename a worksheet: At the bottom of the page, double-click on the title

Sheet 1 (or right-click and select Rename). Type in your desired name then press <Enter>.

2.1.3 Subscripts and superscripts

In some notation schemes, the use of subscripted and superscripted numbers and text is essential in effectively labeling data.

In order to subscript or superscript a character or string of characters:

Highlight the text you wish to change. In the Home tab under the Font group, bring up the Font menu by clicking the arrow in the bottom right corner of the group. In the Font tab on the resulting menu, tick the Subscript or Superscript box depending on which you desire. Then press OK or press <Enter>.

2.1.4 Widening/condensing column widths

An important visual characteristic of your Excel tables is the column width. Sometimes it is desirable to widen or condense columns to improve the readability of your spreadsheet.

To change column width: Simply put the cursor on the line separating the letters at the top of the Excel window, changing the cursor to a vertical line with arrows pointing in opposite directions. Click and drag the cursor to the right to adjust the column width manually, or double-click to auto-adjust the column width to the longest entry.

2.1.5 Merge and center

To improve clarity and make tables aesthetically pleasing, it may be of use to have multiple cells merge into one.

To merge cells together: Select all the cells you wish to merge, then click the Merge and center button in the Alignment group of the Home tab.

2.1.6 Wrapping Text

If the content of a cell is significantly longer than the column width, the contents can be wrapped. This means that the cell will lengthen automatically such that the content fits within the cell.

To wrap text: Select the cell or column that you wish to format then in the Alignment Tab in the Home menu select Wrap Text.

2.1.7 Cell type

Sometimes it’s useful to define the type of number contained within a cell. For example, if you’re dealing with monetary values then formatting the cell to contain a currency automatically places a currency sign before the value. There are a number of cell types to select from with different formatting schemes.

To define cell type: Use the drop down menu in the Number group in the Home tab. You can select from General,

Number, Scientific, Percentage, and others. Use this formatting to show your data in the most appropriate method (in most cases, General is sufficient).

2.1.8 Decimal numbers

To adjust the number of decimal places shown in a cell:

Select the cells you wish to format and use the

One Less Decimal or One More Decimal buttons located in the Number group of the Home tab.

2.1.9 Autofill with patterns

When entering data, If there is a pattern to the raw data (for example if you want to calculate the areas of circles with radii of 1m, 2m, 3m, 4m etc.) then use can use an Excel trick to populate desired cells without typing all the values. This is called auto-filling cells.

In order to autofill cells: Type in the first few entries into the column. Then, select all the column entries thus far by clicking the middle of the topmost cell and dragging until all the column entries are highlighted and surrounded by the green border. Release the click, then move your cursor to the bottom right of your selected cells where there is a small green square, changing the cursor to a black plus sign. Click and drag down to populate as many lower cells as you intend.

This method also works with days of the week, months and written patterns (such as Week 1, Week 2, Week 3 etc.).