﻿

# Microsoft EXCEL exams with correction

Télécharger Microsoft EXCEL exams with correction

#### 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 :

TABLE OF CONTENT

 1 Introduction 1 2 Basic Formatting                                                                                             2.1       Merge And Wrap                                                             2.2       Text Formatting                                                               2.3       Setting Up Row And Column                                           2.4       Row And Column Locking                                                2.5       Find And Replace 3 3   3 4              6  7 3 Sort And Filter 3.1       Sort 3.2       Data Filter 11 11 13 4 Managing Data  4.1       Remove Duplicate  4.2       Data Validation 15 15 17 5 Conditional Formatting 19 6 Graph And Chart 23 7 Cell Reference 26 8 Basic Formula & Logical Function 8.1       Basic Formula 8.2       Logical Function 28 28 31 9 Pivot Tables 33 10 Lookup 10.1     Hlookup 10.2     Vlookup 10.3     Match And Index 37 37 38 38

# CHAPTER 1

## 1.0 INTRODUCTION

Microsoft Excel is an electronic spreadsheet. You can use it to organize your data into rows and columns. You can also use it to perform mathematical calculations quickly. Microsoft Excel provides several layers of protection to control who can access and change your Excel files data. Besides, covered in formatting toolbar, conditional formatting and cell reference functions. Microsoft Excel also can create Simple Formulas and Functions. Chart created in Excel can be modified quite easily. Allow you to change the chart type, add in or remove legend and data table, and change text orientation.

a.   Main button to access the main menu in Microsoft Excel

b.   Quick Access Toolbar

c.    Title Bar represent the excel file name

d.   Tab for excel functions

e.   Cell name box

f.     Function box

g.   Worksheet tabs

h.   Rows and columns

i.     Zoom button

# CHAPTER 2

## 2.0 BASIC FORMATTING

This topic will discuss on how to setup the basic format of the data in the worksheet.

### 2.1 MERGE AND WRAP

1.   Click to select the cells and click on merge icon in order to merge the cell into one. So the selected cells or rows will be merge into single merged cell.

2.   Click to select the cells and click on wrap text in order to make sure the content fit into single cell.

### 2.2 TEXT FORMATTING

Default setting when key in data into cell will be aligns bottom and left. The data can be aligning for how you want the data appear in your document.

1.   Click and select cell or highlight the rows and column and select Alignment to align text horizontally top / middle / bottom

2.   Besides the text also can be align in vertically left, center, right or justify based on the document requirement.

### 2.3 SETTING UP ROW AND COLUMN

Set up the row height and column width

1.   Highlight the selected row and right click on row panel, Row Height windows will pop-up and you are required to enter the row size

2.   Hightlight the selected column and right click on column panel, Column Width windows will pop-up and enter the column size as required.

Insert and Delete row / column

1.   Highlight the selected row / column and right click or click Insert Sheet Rows / Insert Sheet Columns in order to insert sheet row / column

2.   Highlight the selected row / column and right click or click Delete Sheet Rows / Delete Sheet Columns in order to delete sheet row / column

### 2.4 ROW AND COLUMN LOCKING

Lock the row and column in order to keep rows and column visible while the rest of worksheet can be scrolls

1.   Highlight and select row or column to be freeze

2.   Go to VIEW and select Freeze Panes

3.   Freeze Top Row and Freeze First Column used to freeze the top row and the most left column in worksheet

### 2.5 FIND AND REPLACE

Replacing the data in worksheet using find and replace functions by typing the certain data to be search and click button search to search and replace

1.   Highlight the data column/ row to be replace

2.   Click Ctrl + F or Home > Editing > Find & Select.

Figure 2.10 Find & Select function

3.   Find and Replace windows pop-up and enter Find value and Replace value

4.   Click Find All to show the list of data and click Replace All

Figure 2.11 Find and Replace windows

5.   New pop-ups windows appear to inform user the data has been replaced

Figure 2.12 Pop-up information windows for find and replace

6.   The highlighted data only will be replaced

Find and Replace the data with an option (to change data with cell color)

1.   Highlight the selected data in column or row to be replace

2.   Ctrl + F > Enter Find value and Replace value in Find and replace windows > Click on Option button

Figure 2.14 Replace stabs to change with color

3.   Format button > select Fill colour > click OK

Figure 2.15 Select color from color palette

4.   The selected data will change the values and colour

Figure 2.16 Pop-up information windows for find and replace the data with cell color

# CHAPTER 3

## 3.0 SORT AND FILTER

### 3.1 SORT

Sort the data by ascending and decending the data based on document requirement

1. Highlight the selected data > select Sort & Filter icon

a.   Sort A to Z – sort data ascendingly

b.   Sort Z to A – sort data decendingly

Custom Sort to sort data ordered by colour

1. Highlight the selected data > select Sort & Filter icon > Custom Sort 2. In Sort windows > select customize the selection data and colour

Custom Sort to sort by more than one criteria (eg: year and month)

1.   Highlight data > select Sort & Filter icon > Custom Sort

2.   In Sort window > select 1 data > Click Add Level to add 2nd data to be sorted

3.   Custom Lists window and select the existing list or create new List entries on order to cater the table data > click Add to add the new list > OK

### 3.2 DATA FILTER

Data Filter will filter the range of data and the drop down arrow in header used to select data

1.   Highlight the selected data > go to Data > and select Filter

2.   Choose data to be appear in table

3.   Advance filter will appear the selected data

## CHAPTER 4

### 4.0 MANAGING DATA

#### 4.1 REMOVE DUPLICATE

Delete the duplicate data by removing the row in order to avoid redundancy

1.   Highlight the selected cells > go to Data > select Remove Duplicates

2.   In Remove Duplicates windows, select column to validate the duplicates data

3.   Popup windows to acknowledge user the number of data been deleted

Figure 4.3 Confirmation windows

#### 4.2 DATA VALIDATION

1.   Setting data list in data validation

a.   Go to Data > Data Validation > List

b.   In Data Validation pop-up window, select List and select the Source to be in List and click OK

c.    List will be appear as in selected data

2.   Setting up range data that can validate the user entered data

a.   Highlight selected column / row > go to Data > and select Validation Data windows

b.   Set up the minimum and maximum number

c.    In Input Message tab, the message can be input to inform user the information should be entered in the cells.

d.   In Error Alert tab the message for error will be set up in this tab to warning the user who enter the data wrongy

e.   The pop up window that appear when data entered wrongly. There are three types of Error Alert

i.    Stop

ii.     Warning

iii.   Information

## CHAPTER 5

### 5.0 CONDITIONAL FORMATTING

Conditional Formatting is to highlight cell with color in order to differentiate the result

1.   Highlight cell rules with value between

a.   To Highlight Cells Rules with certain options

b.   Fill the cells with certain color setting (between option)

2.   Top/ Bottom Rules with 10 top value

a.   Top/ Bottom Rules with an options

b.   Fill the cells with certain color setting

3.   Data Bars / Color Scale / Icon Set

a.   Data bars

Color Bars will be set up based on the value in selected rows or columns

b.   Color scales

Color scales can be set up besides the color shades will be based on minimum and maximum value in cells

c.    Icon Sets

Icon sets is similar as color scales whereby the icons can be set up based on the cells value

# CHAPTER 6

## 6.0 GRAPH/ CHART

Here is a step by step guides for creating graphs/charts:

1. Adding graph / chart.

a.   Key in the data below. Click on any cell within the data containing the information that you wish to display as a chart.

b.   Click Insert > Click the Column icon on the Standard toolbar. This will display the 2-D Column as show below. You can select any Column that you wish to create.

Select/ Reselect Data Source of Chart.

Once the chart is created, you can still change the chart ranges by tweaking the values manually. If you want to re-select your chart data source you can do with a single click option.

The Select Data option is included in the Design chart tab, which can be used after selecting the chart area. Once the tab appears on the ribbon, just click Select Data option from Data group to display the  Data Source dialog to modify the data range by defining the columns and rows range.

You can select the data source using the mouse, or manually enter the fields  you want to enter. In addition, you can also see the hidden and empty cells.

In order to select the chart data source, select the chart and heading > Design tab and > click Select Data > Insert data in Select Data Sources window.

Select Data Source dialog will appear. Here, you can change the data source, edit horizontal and vertical axis.

Click Hidden and Empty Cells button opens a dialog Hidden and Empty Cells Settings, allowing you to enable/disable show data in rows and columns hidden options, and show empty cells in the source data as gaps or zeroes. Once finished, click OK, you will see a changing data source is displayed in the chart.

2.   The chart develop based on the data insert into the Data Sources window.

3.   Determine the location of the chart .

You can choose to place the chart on an existing worksheet as an object, or you can place it on a new worksheet. Two options for you to choose:

New sheet:     It will place the chart in the new worksheet.

Object in:        It will place the chart in the existing worksheet or graph.

Right click chart > Move Chart > Select the new location to place the chart.

# CHAPTER 7

## 7.0 CELL REFERENCE

### 7.1 RELATIVE REFERENCE

1.   By default, Excel using relative reference. Look at the formula in cell I6 below. Cell I6 references (points to) cell I3 and cell I4. Both references are relative.

2.   When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will be =A2+B2.

3.   Relative references are especially convenient when you have to repeat the same calculation on multiple rows or columns.

### 7.2 ABSOLUTE REFERENCE

1.   Do not change when copied or filled. You can use an absolute reference to keep a row and/or column constant

2.   An absolute reference is designated in a formula by the adding a dollar sign (\$) in front of the column letter and row number of cell .It can precede the column reference, the row reference, or both.

3.   To create an absolute reference to cell J3, place a \$ symbol in front of the column letter and row number of cell C7 (\$C\$7).

4.   Or you can use F4 to create absolute reference.

# CHAPTER 8

## 8.0 BASIC FORMULA & LOGICAL FUNCTION

### 8.1 BASIC FORMULA 1. SUM

To sum a range of cells, use the SUM function.

a.   Highlight cells data with extra one blank cells > click Autosum icon and get the result

b.   Use SUM function

c.    SUMIF function

d.   SUMIFS function

e.   SUMPRODUCT function

2.   MOD

Function returns the remainder of two numbers after division

a. Highlight the numbers and identify number to devide

3.   COUNT

To count cells based on one criteria

a.   COUNT - count number cell that contain value

b.   COUNTBLANK - count cell without value

c.    COUNT the number of occurrence text in range

*          wildcard

?          number character count

d. COUNT logical value

Figure 8.7 Count Logical Value

4.   COUNTIFS – To count cells based on multiple criteria

### 8.2 LOGICAL FUNCTION

The IF function checks whether a condition is satisfied, and returns one value if TRUE and another value if FALSE.

1.   If if and.. if or

Figure 8.9  COUNTIF –count with  multiple criteria

a.   If function

Return result PASS if value more than 50

=IF(SUM(C3,D3)>50,"pass","fail")

b.   If and function

Return result PASS for both value TRUE and return result FAIL for both value  FALSE

=IF(AND(C3>40,D3>40),"pass","fail")

c.    If or function

Return result PASS if any of result are TRUE and return result FAIL if both value are FALSE

=IF(OR(C3:C12>40,D3:D12>40),"pass","fail")

d.   if with mod

=IF(MOD(E3,2)=1,"Lelaki","Perempuan")

2.   Nested if function

a.   Using nested if to validate data in cells

=IF(P2>4,"Excellent",

IF(P2>3,"Good","Warning Letter"))

b.   Nested if with average

=IF(AVERAGE(K2:O2)>75,"A",

IF(AVERAGE(K2:O2)>50,"B",

IF(AVERAGE(K2:O2)>35,"C","D")))

## CHAPTER 9

### 9.0 PIVOT TABLES

A pivot table allows you to extract the significance from a large, detailed data set.

Create Pivot Table in blank sheet

1.   Open blank sheet  > Insert > PivotTable

2.   Highlight data in Create PivotTable windows

3.   In PivotTable Tools, Choose field to be insert in report

Create Chart from Pivot Table

1. Choose data > Insert > select chart to represent the PivotTable data

Use formula in calculate data for Pivot Table

#### 1. In PivotTable Tools > Formula > Calculated Field

2. Insert Calculated Field > select value (quatity * price) > Add the value

Format number in Pivot Table

#### 1. In PivotTable Tools > Field Settings

2. In Value Field Settings windows > click on Number Format > choose the number category.

## CHAPTER 10

### 10.0 LOOKUP

To look up a value or a series of values from a large database and append a column to a table using data from a larger database

#### 10.1 HLOOKUP

1. Look up value in horizontal column

 1                            3 =HLOOKUP ( lookup value , table_array , row_index_num , [range_lookup] ) 2                            4

#### 10.2 VLOOKUP

1. Look up value in vertical row

 1                                                       3 =VLOOKUP(lookup value, table_array, col_index_num, [range_lookup]  2                                                       4

#### 10.3 MATCH and INDEX

The MATCH function returns the position of a value in a given range.

The INDEX function returns a specific value in a two-dimensional range.

1.   Lookup two dimensional range

2.   Get data combination of both Vlookup and Hlookup using MATCH and INDEX

4