Tutorial excel 2016 PDF

Preview

Summary

Download Tutorial excel 2016 PDF

Description

Institute of Computer Technology in Suriname Address: Andesieststraat # 04, Maretraite V, Phone: 8787313, E-mail: [email protected] , website: www.ictssuriname.com, KKF. # 49020, Paramaribo-Suriname "leading the way in innovative IT and Best Practice Learning"

Basic Training

Basic Course Ms Excel 2016

Table of contents

Institute of Computer Technology in Suriname-ICTS

Page

Chapter 1 Introduction Ms Excel 2016.....................................................................................3 1.1 What can you do with it? ................................................................................................................ 3 1.2 Worksheet with cells ....................................................................................................................... 3 1.3 Numbers and words ........................................................................................................................ 3 Chapter 2 Starting Microsoft Excel 2016 .................................................................................4 2.1 The start screen of Ms Excel 2016 ............................................................................................... 4 2.2 Basic concepts: worksheet, cell, row, column, value ................................................................. 7 2.3 Rows and Columns ......................................................................................................................... 7 Chapter 3 Entering data in Excel..............................................................................................8 Chapter 4 Customizing Columns and Rows ...........................................................................9 4.1 Changing the width of columns using the mouse (Auto fit selection) ...................................... 9 4.2 Changing the Height of Rows Using the Mouse (Auto Fit Select) ........................................... 9 Chapter 5 TErasing .................................................................................................................. 11 5.1 Delete the contents of a cell......................................................................................................... 11 5.2 Erasing columns ............................................................................................................................ 11 5.3 Erasing rows................................................................................................................................... 11 Chapter 6 Formulas .................................................................................................................13 6.1 Arithmetic operations .................................................................................................................... 13 6.2 Copying formulas ........................................................................................................................... 14 Chapter 7 Absolute and Relative Cell References ...............................................................15 Chapter 8 Inserting and Renaming a Worksheet .................................................................17 Chapter 9 Features...................................................................................................................18 9.1 What are functions? ...................................................................................................................... 18 Chapter 10 Charts ....................................................................................................................19 10.1 Flipping rows of columns............................................................................................................ 20 10.2 Move chart to new worksheet .................................................................................................... 20 10.3 Add chart title ............................................................................................................................... 20 Chapter 11 Header and Footer ...............................................................................................22 Chapter 12 Printing Worksheets ............................................................................................28

2|P a g e

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

Chapter 1 Introduction Ms Excel 2016 Excel is one of the programs in Microsoft's Office office suite. It is intended as a calculation program. This means that you can perform calculations with it for all kinds of different purposes. It is used by companies as well as by individuals. 1.1 What can you do with it? You can do everything with it, such as keeping track of your administration, keeping track of meter readings, but also more creative goals such as weight loss or running schedules. You can combine text and numbers, for example in the form of tables. You can determine yourself which calculations should be performed with which numbers. For example, you can calculate the average of your expenses in your administration, or add and subtract what you have spent on what. 1.2 Worksheet with cells Excel works on the basis of cells, which are located in a large worksheet. Each cell has a name, which consists of a letter and a number. The rows all have a different number, and the columns all have a letter. Cell A1 is the first cell at the top left, next to it you will find B1 and so on. There are thousands of columns and rows in a standard worksheet, so you have plenty of room to include your data. Each "sheet" of cells is called a "sheet," and all sheets together are called a workbook. You can give the sheets different names to distinguish them from each other. You can also add sheets if you don't have enough. The worksheet (spreadsheet) window always shows you the worksheet that is selected at the bottom of the worksheet titles. You do almost all the work in this window. One worksheet can consist of 256 columns and 65,536 rows. The first 26 columns are labeled A to Z, then they are labeled AA to AZ, then BA to BZ etc etc ... The rows are simply numbered 1 to 65,536 We call the intersection point of a column with a row a cell. As below cell A1 is selected.

1.3 Numbers and words You can type data, words or numbers in each cell. You can use the numbers in calculations. Excel does the calculations on the basis of formulas. They are very easy to use, and you can specify which numbers are used in the calculation. To brighten things up, you can merge cells, color them and add borders, so that a table looks more like a separate entity, for example, instead of a series of numbers and words. Numbers are automatically right justified in a cell and texts are left justified in a cell. Excel distinguishes between numbers and words.

3|P a g e

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

Chapter 2 Starting Microsoft Excel 2016 -

Click the start button, all apps, Ms Excel 2016

2.1 The start screen of Ms Excel 2016

4|P a g e

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

When you open Excel 2016, you will see the new start screen. On the left side of the screen you will see the files you have recently opened (1). If you want to see a file in this list every time you open Excel, then click to the right of the file to pin it to the top of the list (A). If it is the first time that you open Excel, no file will be shown in this list. At the bottom of this list you will find a link (2) that allows you to open a file in another workbook. On the right side we find a number of templates (3), and a search box (4) for searching for templates online. At the top right (5) we find the data with which you are registered for your SkyDrive or SharePoint account. You use SkyDrive when you place files on the Microsoft server, SharePoint is often used by companies to place files on their server.

To open a new workbook, click the template "Blank workbook" (B), or click the Escape key on your keyboard.

5|P a g e

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

Quick access toolbar

Title bar The ribbon

Backstage

(Tabs and Groups)

Formula bar Name box

Cell pointer Columns

Rows

Mouse pointer

Scroll bars

Worksheet

Status bar

6|P a g e

Display buttons

Zoom in and out

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

2.2 Basic concepts: worksheet, cell, row, column, value An Excel spreadsheet consists of one or more worksheets (see the tabs at the bottom left of the window). A worksheet contains a grid of cells. The 'name' of a cell is determined by the letter of the column (A, B, ..., Z, AA, AB, etc.) and the number of the row (1, 2, 3, etc.). So the first cell at the top left of the worksheet is cell A1. A cell can contain a value (text, number, percentage, currency, date, time, etc.) or a formula (resulting in a value). However, a new spreadsheet does not yet contain any values or formulas, all cells are basically empty. Directly below the toolbars are the name box (which displays the name of the selected, and therefore active, cell) and the formula bar fx (which displays and edit the value or formula of the active cell).

Formula bar

The name box 2.3 Rows and Columns There are all boxes on your worksheet. We call them cells. We can designate each cell with a name, made of a letter and a number. Take a good look at the picture below.

The columns (vertical) have a letter. The blue column has the letter C. The rows (horizontal) have a number. The yellow row is No. 5. The cells are identified by their column letter and row number. The selected cell (the green one) is therefore called C5 (is a cell address). Selecting: You can select cells, rows, columns and groups of cells by clicking on them. The cell will be outlined in black, if you select an entire row, click on the row number, if you want to select a column, click on the column number. You select a group of cells with the mouse pressed down.

7|P a g e

Basic Course Ms Excel 2016

Chapter 3 Entering data in Excel Enter the information below:

8|P a g e

Institute of Computer Technology in Suriname-ICTS

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

Chapter 4 Customizing Columns and Rows 4.1 Changing the width of columns using the mouse (Auto fit selection) Do one of the following: •

To change the width of a column, drag the border on the right side of the column header until it is the width you want.

•

To change the width of multiple columns, select the columns you want to point and then drag the border on the right side of a selected column heading. To adjust the width of columns to fit the content, select the column or columns that you want to map, then double-click the border on the right side of a selected column heading. To change the width of all columns in the worksheet, click the Select All button and then drag the border on the right side of a column header.

•

•

4.2 Changing the Height of Rows Using the Mouse (Auto Fit Select) Do one of the following: •

To change the height of a row, drag the border below the row header until it is the height you want.

•

To change the height of multiple rows, select the rows to point, then drag the border below a selected row heading.

9|P a g e

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

•

To change the height of all rows in the worksheet, click the Select All button and then drag the border below a row heading.

•

To adjust the height of rows according to the content, double-click the border below a selected row header.

10 | P a g e

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

Chapter 5 Erasing 5.1 Delete the contents of a cell 1. Select the cell whose contents you want to delete 2. Right click, choose “Clear contents” from the menu

5.2 Erasing columns 1. Right-click with the mouse button on the column he 2. Choose from the menu for “delete”

Note To delete multiple columns, select multiple Columns. Right-click on one of the column headings and choose For "Delete"

5.3 Erasing rows 1. Right-click with the mouse button on the row header 2. Choose from the menu for “delete”

5. 4 Inserting rows and columns To add columns we do the following: Suppose we want to add two columns between column F and I. Then we select column G and H, right click on the selected columns and choose insert.

11 | P a g e

Basic Course Ms Excel 2016

12 | P a g e

Institute of Computer Technology in Suriname-ICTS

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

Chapter 6 Formulas To create a formula, we first select the cell where we want the result to be. Then we type an = sign in the formula bar, formulas ALWAYS start with an = sign. And only then do we type our formula.

If we click Enter, Excel will give us the result in our worksheet, but note that the contents of cell C4 are still our formula. If Excel displays the formula as text anyway, first check that you have not typed a space for the = sign. A formula MUST start with the = sign. Again, the actual contents of the cell will be the formula, but the display in the cell will be the result.

6.1 Arithmetic operations Adding up

+

Multiply

Subtraction

-

Percentage

Share

/

Exponentiation

* % ^

Excel uses a specific order for the calculations. If you want to include multiple edits in one formula, it will be a bit more difficult. Excel will first calculate the percentage, then perform the exponentiation, then multiply or divide that are treated at the same level and always performed from left to right, and finally add and subtract that are also treated at the same level and are also treated from left to right. executed eg: 1.percentage 2. exponentiation 3. multiply and divide 4. addition and subtraction

13 | P a g e

% ^ * and / + and -

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

6.2 Copying formulas Using the fill handle (AutoFill)copied. By selecting the cell where the formula is located. Then the mouse pointer is placed on the dot of the cell pointer. The pointer is then dragged to the cells where the answers should appear. AutoFill is a handy feature in Excel that automatically fills in repeating data. For example, we can easily have Excel fill in days of the week, months of the year, or any other self-repeating data. Type Monday in the first cell, select the fill button at the bottom right of the cell, and drag it to the right or the bottom.

Excel will automatically add the following days of the week.

We can do the same for numbers, for example, type 2 in the first cell, 4 in the underlying cell, select both cells and drag the fill handle down, Excel will fill the following cells with an interval of 2.

14 | P a g e

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

Chapter 7 Absolute and Relative Cell References As mentioned earlier, the intersection of a column with a row is a cell. Each cell has a cell address. The cell address consists of the letter of the column and the row number. The cell address of the selected cell is listed in the name box.

7.1 Relative cell reference Now that we know what a cell address is, we can refer to it in our formula, eg = A2 * B2. When you copy this formula now, the cell references in this formula will be adjusted automatically. For example, if you have the formula = A2 * B2 in cell C2, and you are going to copy it to cell C3, the copied formula will automatically adjust, and it becomes = A3 * B3. We call this a relative cell reference.

7.2 Absolute Cell Reference However, if you refer in your formula to a cell that must remain the same every time you copy, you must make this cell reference absolute. You do this by placing the dollar sign ($) in front of the column and before the row number. If you copy your formula to the bottom, top, left or right, the cell reference with the $ signs will always remain the same. This is called an absolute cell reference. The ABS key is the f4 key. With the f4 key we add the dollar signs.

15 | P a g e

Basic Course Ms Excel 2016

16 | P a g e

Institute of Computer Technology in Suriname-ICTS

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

Chapter 8 Inserting and Renaming a Worksheet •

To insert a new worksheet, click on the button with a '+' next to the sheet tab.

•

To rename a worksheet double click or right click on the sheet name and choose rename from the menu, type in the new name, enter.

17 | P a g e

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

Chapter 9 Features 9.1 What are functions? Functions are predefined formulas that perform operations with one or more values in the correct order. The most commonly used are Sum, Average, Max (Maximum) and Min (Minimum).

The tab "Formulas" in the ribbon All functions were placed in different categories, under the tab "Formulas" in the ribbon First we have the category "Autosum". In this category are the functions: Sum, which calculates the sum of the selected Range. Average, which calculates the average of the selected Range. Max, which represents the maximum value from the selected Range. And Min, which represents the minimum value from the selected Range.

18 | P a g e

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS

Chapter 10 Charts In Excel we can compile different types of charts: Pie (pie charts), column charts, line charts, etc. To insert a chart, we first select the data that we want to insert into our chart. Then select the "Insert" tab in the ribbon. There are different types of chart categories in the ribbon. Depending on the data you want to include in your chart, certain types will be more suitable than others. Clicking on an icon will show us a drop-down menu. To insert a chart, click the chart in the dropdown menu.

But titles, even tables, can be included in charts, and the legend can be moved up, right, or bottom. Instead of having to do this manually, we can use the Graph layout for this. Clicking the bottom arrow opens a pop-up menu with different layouts. 19 | P a g e

Basic Course Ms Excel 2016

Institute of Computer Technology in Suriname-ICTS For example, the former will add a title to our chart. Another format will then move the legend to the bottom, there is also one in between that adds a data table to our chart. All these pre-installed formats can save us a lot of time, but you can add or remove items yourself if you wish. Just try these out, you can always use the "Undo" button in the "Quick access" toolbar to return to previous designs.

10.1 Flipping rows of columns To do this, we click the "Switch row / Column" button, ...