Professional advanced EXCEL tutorial xlsx


Télécharger Professional advanced EXCEL tutorial xlsx

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 :


PROFESSIONAL

ADVANCE EXCEL TIPS

2010

This Excel training gives you strong fundamentals to build on. You'll learn how to use your data and produce spreadsheets that communicate better and really get attention.        You'll discover professional Excel tips for brilliant,        distinctive output every time, on every worksheet.

A. Z. M. Akhlaqur Rahman

B.Sc. in Civil Engg. (BUET), MBA (IBA)

Towards

Excel

Excellence

Table of Content

 Part Description                                                                               Page

1.0          INTRODUCTION                                                                                            1 - 10

1.1                What is Excel                                                                                                 1

1.2                Specifications and Limitations                                                                       1

1.3                 Calculation operators and precedence                                                         2

1.4                References Styles in Excel                                                                            5

1.5                Cell Formatting                                                                                               6

2.0          CREATING A USABLE DATA TABLE                                                   11 - 16

2.1                Cleaning & Trimming of Text and Data                                                        11

2.2                Change Case                                                                                                11

2.3                Add New Line in Same Cell                                                                          11

2.4                Join Multiple Cell Values                                                                              11

2.5                Extract a portion of Cell Content                                                                  12

2.6                 Divide Cell Content into multiple Cells                                                         12

2.7                 Convert Date Format that Excel can understand                                        14

2.8                Use of Go To Command                                                                              15

2.9                 Filling the Blank Cells                                                                                   15

2.10              Creating Auto Serial Number                                                                       16

2.11              Use of Find All Command                                                                            16

3.0          USING LOGICAL FUNCTIONS                                                                17 - 23

3.1                Counting Data by COUNT Functions                                                           17

3.2                What are AND & OR                                                                                     18

3.3                IF and Nested IF Functions                                                                          18

3.4                Grouping Rows or Columns for quick references                                        18

3.5                Advance use of data SUBTOTAL                                                                 19

 Part Description                                                                               Page

3.6                Sort horizontally from Left to Right                                                               20

3.7                Multiple Conditional Formatting using Formula                                           22

3.8                Match Function                                                                                             23

4.0          EXCEL TABLES                                                                                          24 - 34

4.1                Data Validation                                                                                             24

4.2                Data Validation by using Multiple List                                                          30

4.3                 Lookup Function And Its Use                                                                       33

5.0          EXCEL CHARTS                                                                                         35 - 41

5.1                Elements of a Chart                                                                                      35

5.2                Available Chart Types                                                                                  36

6.0          PIVOT TABLE & PIVOT CHART                                                             40 - 42

6.1                What Is A PivotTable Report                                                                        40

6.2                What Is A PivotChart Report                                                                        41

6.3                Comparing a PivotTable Report and a PivotChart Report                           42

7.0          CREAT A MACRO                                                                                     43 – 43

7.1                Before you record a Macro                                                                           43

7.2                Recording a Macro                                                                                       43

8.0          SECURITIES OF WORKBOOK                                                               44 – 44

8.1                Protect Workbook Elements                                                                         44

8.2                Remove Protection from a Worksheet                                                         44

9.0          ANNEXURE                                                                                                 45 – 50

9.1                Chart Finder                                                                                                  45

9.2                Excel Shortcut Keys                                                                                      46


1.0       INTRODUCTION:

1.1       WHAT IS EXCEL

MS Excel or Excel is a computer application software, widely used to store, calculate and display alphanumeric data. It is said that whatever you can do using a piece of paper, a pen, a scale and a calculator, can be done by using MS Excel.

1.1.1 WHY USE EXCEL?

v  It is the most effective and efficient way to calculate, process and present alphanumeric data and associated graphics

v  It enables you to perform repetitive calculations quickly.

v  It provides short-cuts for creating formulas.

v  It provides a clean, easy-to-understand presentation of data.

v  It does much of the work involved in creating spreadsheets, compiling tables, developing graphs, composing reports, and many other functions associated with data manipulation.

v  You need only enter a formula for the spreadsheet to continually update totals whenever data is entered rather than having to continually re calculate.

v  It helps to get you, and keep you, organized.

1.2       SPECIFICATIONS AND LIMITATIONS

FEATURE

MAXIMUM LIMIT

Worksheet size

1,048,576 rows by 16,384 columns

Column width

255 characters

Row height

409 points

Total number of characters that a cell can contain

32,767 characters

Sort references

64 in a single sort; unlimited when using sequential sorts

Undo levels

100

Filter drop-down lists

10,000

Number precision

15 digits

Arguments in a function

255

Nested levels of functions

64

Earliest date allowed for calculation

January 1, 1900

Latest date allowed for calculation

December 31, 9999

Largest amount of time that can be entered

9999:59:59

1.3       CALCULATION OPERATORS AND PRECEDENCE

Operators specify the type of calculation that you want to perform on the elements of a formula. There is a default order in which calculations occur, but you can change this order by using parentheses.

1.3.1 TYPES OF OPERATORS

There are four different types of calculation operators:

1.    Arithmetic

2.    Comparison

3.    Text concatenation

4.    Reference.

1.3.2 ARITHMETIC OPERATORS

To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.

Arithmetic operator

Meaning

Example

+ (plus sign)

Addition

3+3

– (minus sign)

Subtraction Negation

3–1 

–1

* (asterisk)

Multiplication 

3*3

/ (forward slash)

Division

3/3

% (percent sign)

Percent 

20%

^ (caret)

Exponentiation

3^2

1.3.3 COMPARISON OPERATORS

You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value either TRUE or FALSE.

Comparison operator

Meaning

Example

= (equal sign)

Equal to 

A1=B1

> (greater than sign)

Greater than

A1>B1

< (less than sign)

Less than

A1<B1

>= (greater than or equal to sign)

Greater than or equal to

A1>=B1

<= (less than or equal to sign)

Less than or equal to

A1<=B1

<> (not equal to sign)

Not equal to

A1<>B1

1.3.4 TEXT CONCATENATION OPERATOR

Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.

Text operator

Meaning

Example

&

(ampersand)

Connects, or concatenates, two values to produce one continuous text value 

("North"&"wind")

1.3.5 REFERENCE OPERATORS

Combine ranges of cells for calculations with the following operators.

Reference operator

Meaning

Example

: (colon)

Range operator, which produces one reference

to all the cells between two references, including the two references 

B5:B15

, (comma)

Union operator, which combines multiple references into one reference 

SUM(B5:B15,D5:D15)

(space)

Intersection operator, which produces on reference to cells common to the two references

B7:D7 C6:C8

1.3.5.1 THE ORDER IN WHICH EXCEL PERFORMS OPERATIONS IN FORMULAS

In some cases, the order in which calculation is performed can affect the return value of the formula, so it's important to understand how the order is determined and how you can change the order to obtain desired results.

1.3.5.2 CALCULATION ORDER

Formulas calculate values in a specific order. A formula in Excel always begins with an equal sign (=). The equal sign tells Excel that the succeeding characters constitute a formula. Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Excel calculates the formula from left to right, according to a specific order for each operator in the formula.

1.3.5.3 OPERATOR PRECEDENCE

If you combine several operators in a single formula, Excel performs the operations in the order shown in the following table. If a formula contains operators with the same precedence — for example, if a formula contains both a multiplication and division operator — Excel evaluates the operators from left to right.

Operator

Description

: (colon) 

(single space)

, (comma)

Reference operators

Negation (as in –1)

%

Percent

^

Exponentiation

* and /

Multiplication and division

+ and –

Addition and subtraction

&

Connects two strings of text (concatenation)

=      <

>      <=

>=    <>

Comparison

1.3.5.4 USE OF PARENTHESES

To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because Excel calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.

=(5+2)*3

In the example below, the parentheses around the first part of the formula force Excel to calculate B4+25 first and then divide the result by the sum of the values in cells D5, E5, and F5.

=(B4+25)/SUM(D5:F5)

1.4       REFERENCE STYLES IN EXCEL

A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, and to other workbooks. References to cells in other workbooks are called links.

1.4.1 THE A1 REFERENCE STYLE

By default, Excel uses the A1 reference style, which refers to columns with letters (A through IV, for a total of 256 columns) and refers to rows with numbers (1 through 65536). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B and row 2.

To refer to

Use

The cell in column A and row 10

A10

The range of cells in column A and rows 10 through 20

A10:A20

The range of cells in row 15 and columns B through E

B15:E15

All cells in row 5

5:5

All cells in rows 5 through 10

5:10

All cells in column H

H:H

All cells in columns H through J

H:J

The range of cells in columns A through E and rows 10 through 20

A10:E20

1.4.2 THE R1C1 REFERENCE STYLE

You can also use a reference style where both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful for computing row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.

Reference

Meaning

R[-2]C

A relative reference to the cell two rows up and in the same column

R[2]C[2]

A relative reference to the cell two rows down and two columns to the right

R2C2

An absolute reference to the cell in the second row and in the second column

R[-1]

A relative reference to the entire row above the active cell

R

An absolute reference to the current row

When you record a macro, Excel records some commands by using the R1C1 reference style. For example, if you record a command such as clicking the AutoSum button to insert a formula that adds a range of cells, Excel records the formula by using R1C1 style, not A1 style, references.

1.4.3 RELATIVE AND ABSOLUTE REFERENCES

1.4.3.1 RELATIVE REFERENCES    

A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.

1.4.3.2 ABSOLUTE REFERENCES

An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, and you need to switch them to absolute references. For example, if you copy an absolute reference in cell B2 to cell B3, it stays the same in both cells =$A$1.

1.4.3.3 MIXED REFERENCES

A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1.

1.5       CELL FORMATTING

Cells can be formatted by (with the cell selected) right-clicking the mouse key and selecting the Format Cell command. The resulting dialog box contains 6 tabs

Ø  Number— Options include currency, accounting, date, fraction, percentage, etc.

Ø  Alignment—includes horizontal and vertical alignment for the cell’s contents

Ø  Font—includes font type, font size, font color, font underline and font size

Ø  Border—Line type, width and color

Ø  Fill—Fill color and effects

Ø  Protection—locked and hidden

1.5.1 CUSTOM FORMATTING

Microsoft Office Excel provides many built-in number formats, but if these do not meet your needs, you can customize a built-in number format to create your own. To learn more about how to change number format codes, you may want to review the guidelines for customizing a number format before you get started.

To create a custom number format, you start by selecting one of the built-in number formats as a starting point. You can then change any one of the code sections of that format to create your own custom number format.

A number format can have up to four sections of code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values, and text, in that order.

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

For example, you can use these code sections to create the following custom format:

 [Blue]#,##0.00_);[Red](#,##0.00);0.00;"sales "@

You do not have to include all code sections in your custom number format. If you specify only two code sections for your custom number format, the first section is used for positive numbers and zeros, and the second section is used for negative numbers. If you specify only one code section, it is used for all numbers. If you want to skip a code section and include a code section that follows it, you must include the ending semicolon for the section that you skip.

1.5.2 USE OF TEXT AND ADDING SPACING

1.5.2.1 DISPLAY BOTH TEXT AND NUMBERS

To display both text and numbers in a cell, enclose the text characters in double quotation marks (" ") or precede a single character with a backslash (\). Include the characters in the appropriate section of the format codes. For example, type the format $0.00" Surplus";$0.00" Shortage" to display a positive amount as "$125.74 Surplus" and a negative amount as "$-125.74 Shortage." Note that there is one space character before both "Surplus" and "Shortage" in each code section.

The following characters are displayed without the use of quotation marks.

$

Dollar sign

{

Left curly bracket

!

Exclamation point

+

Plus sign

<

Less-than sign

&

Ampersand

(

Left parenthesis

=

Equal sign

~

Tilde

:

Colon

-

Minus sign

}

Right curly bracket

^

Caret

/

Slash mark

>

Greater-than sign

'

Apostrophe

)

Right parenthesis

Space character

1.5.2.2 INCLUDE A SECTION FOR TEXT ENTRY

If included, a text section is always the last section in the number format. Include an "at" character (@) in the section where you want to display any text that you type in the cell. If the @ character is omitted from the text section, text that you type will not be displayed. If you want to always display specific text characters with the typed text, enclose the additional text in double quotation marks (" "). For example, "gross receipts for "@

If the format does not include a text section, any nonnumeric value that you type in a cell with that format applied is not affected by the format. In addition, the entire cell is converted to text.

1.5.2.3 ADD SPACES

To create a space that is the width of a character in a number format, include an underscore character (_), followed by the character that you want to use. For example, when you follow an underscore with a right parenthesis, such as _), positive numbers line up correctly with negative numbers that are enclosed in parentheses.

1.5.2.4 REPEAT CHARACTERS

To repeat the next character in the format to fill the column width, include an asterisk (*) in the number format. For example, type 0*- to include enough dashes after a number to fill the cell, or type *0 before any format to include leading zeros.

1.5.3 USING DECIMAL PLACES, SPACES, COLORS, AND CONDITIONS

To format fractions or numbers that contain decimal points, include the following digit placeholders, decimal points, and thousand separators in a section.

0 (zero)

This digit placeholder displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, if you type 8.9, and you want it to be displayed as 8.90, use the format #.00.

#

This digit placeholder follows the same rules as the 0 (zero). However, Excel does not display extra zeros when the number that you type has fewer digits on either side of the decimal than there are # symbols in the format. For

example, if the custom format is #.##, and you type 8.9 in the cell, the number 8.9 is displayed.

?

This digit placeholder follows the same rules as the 0 (zero). However, Excel adds a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, the custom format 0.0? aligns the decimal points for the numbers 8.9 and 88.99 in a column.

. (point)

This digit placeholder displays the decimal point in a number.

If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. If the format contains only number signs (#) to the left of the decimal point, numbers less than 1 begin with a decimal point; for example, .47.

To display

As

Use this code

1234.6

1234.6

####.#

8.9

8.9

#.000

0.631

0.6

0.#

12

1234.6

#.0#

12

1234.6

#.0#

44.398

44.398

???.???

102.65

102.65

???.???

2.8

2.8

???.???

5.25

5 1/4

# ???/???

5.3

5  3/10

# ???/???

12000

12,000

#,###

12000

12

#,

1E+07

12.2

0.0,,

1.5.4 GUIDELINES FOR DATE AND TIME FORMATS

To display numbers as date formats (such as days, months, and years), use the following codes in a section.

Displays the month as a number without a leading zero.

mm

Displays the month as a number with a leading zero when appropriate.

mmm

Displays the month as an abbreviation (Jan to Dec).

mmmm

Displays the month as a full name (January to December).

mmmmm

Displays the month as a single letter (J to D).

d

Displays the day as a number without a leading zero.

dd

Displays the day as a number with a leading zero when appropriate.

ddd

Displays the day as an abbreviation (Sun to Sat).

dddd

Displays the day as a full name (Sunday to Saturday).

yy

Displays the year as a two-digit number.

yyyy

Displays the year as a four-digit number.

h

Displays the hour as a number without a leading zero.

[h]

Displays elapsed time in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, use a number format that resembles [h]:mm:ss.

hh 

Displays the hour as a number with a leading zero when appropriate. If the format contains AM or PM, the hour is based on the 12-hour clock. Otherwise, the hour is based on the 24-hour clock. 

m

Displays the minute as a number without a leading zero.

Note   The m or mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, Excel displays the month instead of minutes.

[m]

Displays elapsed time in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss.

mm 

Displays the minute as a number with a leading zero when appropriate.

s

Displays the second as a number without a leading zero.

[s] 

Displays elapsed time in seconds. If you are working with a formula that returns a time in which the number of seconds exceeds 60, use a number format that resembles [ss].

ss

Displays the second as a number with a leading zero when appropriate. If you want to display fractions of a second, use a number format that resembles h:mm:ss.00. 

am/pm, a/p

Displays the hour using a 12-hour clock. Excel displays AM, am, A, or a for times from midnight until noon and PM, pm, P, or p for times from noon until midnight.

2.0       CREATING A USABLE DATA TABLE

 2.1      CLEANING & TRIMMING OF TEXT AND DATA

Function CLEAN removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system.

                                                                      Syntax:           CLEAN(text)

Similarly, TRIM Function removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.



                                                                         Syntax:          TRIM(text)

2.2       CHANGE CASE

Case of text can be changed in Excel by using functions Function UPPER converts text to uppercase.

                                                                      Syntax:           UPPER(text)

Function LOWER converts text to uppercase.

                                                                     Syntax:           LOWER(text)

Function PROPER capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

                                                                    Syntax:           PROPER(text)

2.3       ADD NEW LINES IN THE SAME CELL

Unlike MS Word, if you press an ENTER while using Excel, the selection goes to one cell below the current cell. To enter a new line in the same cell, press ALT+ENTER. The operation can be done as many times as required.

2.4       JOIN MULTIPLE CELL VALUES

The CONCATENATE function joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, or a combination of those items. Syntax: CONCATENATE(text1, [text2], )

You can also use the ampersand (&) calculation operator instead of the CONCATENATE function to join text items. For example,

=A1 & B1returns the same value as =CONCATENATE(A1, B1)

2.5       EXTRACT A PORTION OF CELL CONTENT

Part of cell content can be extracted by three functions LEFT, RIGHT and MID. LEFT returns the first character or characters in a text string, based on the number of characters you specify.

Syntaxes: LEFT(text, num_chars)

RIGHT returns the last character or characters in a text string, based on the number of characters you specify.

Syntaxes: RIGHT(text, num_chars)

MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

Syntaxes: MID(text, start_num, num_chars)

2.6       DIVIDE CELL CONTENT INTO MULTIPLE CELLS

Depending on the way your data is arranged, you can split the cell content based on a delimiter, such as a space or a character (such as a comma, a period, or a semicolon), or you can split it based on a specific column break location within your data.

Use this method if your names have a delimited format, such as "First_name Last_name" (where the space between First_name and Last_name is the delimiter) or "Last_name, First_name" (where the comma is the delimiter).

Split space-delimited content

1.    Select the range of data that you want to convert.

2.    On the Data tab, in the Data Tools group, click Text to Columns. 

3.    In Step 1 of the Convert Text to Columns Wizard, click Delimited, and then click Next.

4.    In Step 2, select the Space check box, and then clear the other check boxes under Delimiters. 

The Data preview box shows the first and last names in two separate columns. 

5.    Click Next. 

6.    In Step 3, click a column in the Data preview box and then, under Column data format, click Text. 

Repeat this step for each column in the Data preview box.

7.    If you want to insert the separated content into the columns next to the full name, click the icon to the right of the Destination box, and then select the cell next to the first name in the list (B2, in this example). 

If you do not specify a new destination for the new columns, the split data will replace the original data.

8.    Click the icon to the right of the Convert Text to Columns Wizard. 

9.    Click Finish.

You can also customize how you want your data to be separated by specifying a fixed column break location. For example, a column containing first names may allow up to 6 characters, and a column containing last names may allow up to 10 characters. Any unused characters for each column may be filled with spaces. So, in the text file that you are importing, the name AKHLAQUR RAHMAN may appear as AKHLAQ RAHMAN (followed by 4 spaces).

1.    Select the cell or range of cells that contains the data that you want to split.

2.    On the Data tab, in the Data Tools group, click Text to Columns. 

3.    In Step 1 of the Convert Text to Columns Wizard, click Fixed Width, and then click Next. 

4.    In the Data preview box, drag a line to indicate where you want the content to be divided.

5.    Click Next. 

6.    In Step 3, select a column in the Data preview box and then, under Column data format, click a format option. 

7.    If you want to show the split content in the columns next to the full name, click the icon to the right of the Destination box, and then click the cell next to the first name in the list. 

8.    Click the icon to the right of the Convert Text to Columns Wizard. 

9.    Click Finish.

2.7       CONVERT DATE FORMAT THAT EXCEL CAN UNDERSTAND

Numbers separated by Slash (/) or Dash (-) are considered as Date by Excel. Excel cannot recognize any other form of numbers that are indented as date but considered as text. So, you cannot perform any mathematical operation on it. To convert data in such a form that Excel can know, you need to replace all separator characters with Slash (/) or Dash (-).

For example, 16/12/1971 or 16-12-1971 is considered as December 16, 1971 but 16.12.1971, 16_12_1971 etc. are nor recognized by Excel and hence, Excel considers these forms of dates as Text. So, underscores (_) of 16_12_1971 needs to be replaced by Slash (/) to make Excel understand the date.

1.    Select the cell or range of cells that contains the data that you want to replace.

2.    Press CTRL+H.

3.    In the box next to Find what write the character/s you wish to replace (here _ ).

4.    In the box next to Replace with write the character/s you wish to place (here / ). 5. Click Replace All Button

6. Click Close Button.

2.8       USE OF GO TO COMMAND

You can use the Go To command to quickly find and select all cells that contain specific types of data (such as formulas) or only cells that meet specific criteria (such as the last cell on the worksheet that contains data or formatting).

1.    Select the range, rows, or columns that you want. 

2.    On the Home tab, in the Editing group, click Find & Select, and then click Go To

3.    Click Special.

4.    In the Go To Special dialog box, click one of the following options. 

Click

To select

Comments

Cells that contain comments.

Formulas

Cells that contain formulas.

Blanks

Blank cells.

Objects

Graphical objects, including charts and buttons, on the worksheet and in text boxes.

Precedents

Cells that are referenced by the formula in the active cell. Under Dependents. 

Dependents

Cells with formulas that refer to the active cell. 

Visible cells only

Only cells those are visible in a range that crosses hidden rows or columns.

Conditional formats

Only cells that have conditional formats applied. Under Data validation. 

Data validation

Only cells that have data validation rules applied. 

2.9       FILLING THE BLANK CELLS

This is a tricky use of Go To command. To fill the blanks, do the followings:

1.    Select the range you with to fill.

2.    Select banks only by using Go To Command

3.    Write ‘=’ (Equal sign)

4.    Press UP Arrow once

5.    Press CTRL+ENTER

6.    Repeat Step 1

7.    Copy All

8.    Paste as Value

2.10     CREATING AUTO SERIAL NUMBER

ROW Function returns Row Number of a cell. Using the property, you can use the function by subtracting the row number of header from the cell row number

Formula: ROW(Relative Cell Reference) – ROW(Absolute Header Reference)

2.11     USE OF FIND ALL COMMAND

1.      CTRL+F opens a new window named Find and Replace.

2.      Writing a text that want to find and hit Find All Button. You will find your search items as shown below.

3.      Click at any point of the list of finding

4.      Press CTRL+A to select All

5.      Now change format of the cells at a time, using Ribbon.

3.0       USING LOGICAL FUNCTIONS

Different mathematical operations can be done by Excel. Few operations take sequential execution of a set of individual mathematical operations. To ease the work, the set of sequential operations are called FUNCTION and generally, coded under some understandable name. 

A function is a preset formula in Excel. Like formulas, functions begin with the equal sign ( = ) followed by the function's name and its arguments. The function name tells Excel what calculation to perform. The arguments are contained inside round brackets.

For example, the most used function in Excel is the SUM function, which is used to add together the data in selected cells. The SUM function is written as -

= SUM (Cell Reference1, Cell Reference2, Cell Reference3, ………)

Here the function adds the contents of cell references and displays the answer in cell where the function is applied

3.1       COUNTING DATA BY COUNT AND COUNTA

The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.

Syntax: COUNT(value1, [value2], )

The COUNTA function counts the number of cells that are not empty in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.). Syntax: COUNTA(value1, [value2], )

The COUNTA function counts cells containing any type of information, including error values and empty text (""). For example, if the range contains a formula that returns an empty string, the COUNTA function counts that value. The COUNTA function does not count empty cells.

Similarly, COUNTBLANK Function counts empty cells in a specified range of cells Syntax: COUNTBLANK(Range)

The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify. For example, you can count all the cells that start with a certain letter, or you can count all the cells that contain a number that is larger or smaller than a number you specify.

Syntax: COUNTIF(Range, Criteria)

3.2       WHAT ARE AND & OR

Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE. One common use for the AND function is to expand the usefulness of other functions that perform logical tests.

Syntax: AND(logical1, [logical2], )

Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.

Syntax: OR(logical1, [logical2], )

The arguments must evaluate to logical values such as TRUE or FALSE, or references that contain logical values. If a reference argument contains text or empty cells, those values are ignored. If the specified range contains no logical values, AND/ OR returns the #VALUE! error value.

3.3       IF FUNCTION AND NESTED IF FUNCTION

The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.

Syntax: IF(logical_test, [value_if_true], [value_if_false])

Use of more than 1 IF function in same formula is called nested IF. Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests.

Syntax: IF(logical_test1, [value1_if_true], logical_test2, [value2_if_true], ………., [value_if_false])

3.4       GROUPING ROWS OR COLUMNS FOR QUICK REFERENCES

If you have a list of data that you want to group and summarize, you can create an outline of up to eight levels, one for each group. Each inner level, represented by a higher number in the outline symbols, displays detail data for the preceding outer level, represented by a lower number in the outline symbols. Use an outline to quickly display summary rows or columns, or to reveal the detail data for each group. You can create an outline of rows (as shown in the example below), an outline of columns, or an outline of both rows and columns.

3.5       ADVANCE USE OF DATA SUBTOTAL

SUBTOTAL function returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function. Syntax: SUBTOTAL(function_num,ref1,[ref2], ]) The SUBTOTAL function syntax has the following arguments:

Function_num

Function

Function_num

Function

1

101

AVERAGE

7

107

STDEV

2

102

COUNT

8

108

STDEVP

3

103

COUNTA

9

109

SUM

4

104

MAX

10

110

VAR

5

105

MIN

11

111

VARP

6

106

PRODUCT

If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting. 

For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide Rows command under the Hide & Unhide submenu of the Format command in the Cells group on the Home tab. Use these constants when you want to subtotal hidden and non-hidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide Rows command. Use these constants when you want to subtotal only non-hidden numbers in a list. 

The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.

The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.

3.6       SORT HORIZONTALLY FROM LEFT TO RIGHT

1.  Select the column headings you wish to sort and the complete data below them. Do not select any columns on the left or right that you do not want to be sorted. In the screenshot below, columns A and F are not selected, as I want to keep in place the Month and Total columns.

2.  On the Home tab, click on the Sort & Filter button. From the menu, choose Custom Sort… 

3.  In the Sort window, click on the Options… button.

4.  In the Sort Options window, click the option next to Sort left to right. Then click OK.


5.  Back in the Sort window, click on the drop-down box next to Sort by, and select the appropriate row that contains your headings. In this example, Row 1 is selected because that is where the column headings are located.

6.  Make sure that the Order option is correct. The default given is A to Z. However, if you want your headings sorted in reverse order, choose Z to A from the drop-down menu.

7.  Press OK.

Now, the headings have been sorted alphabetically, and the data underneath has moved with the column headings. Both the Month and Total columns remain unmoved.

3.7       MULTIPLE CONDITIONAL FORMATTING USING FORMULAS

If your conditional formatting needs are more complex, you can use a logical formula to specify the formatting criteria. For example, you may want to compare values to a result returned by a function or evaluate data in cells outside the selected range.

1.    On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. 

The Conditional Formatting Rules Manager dialog box is displayed.

2.    Do one of the following: 

•    To add a conditional format, click New Rule. The New Formatting Rule dialog box is displayed.

•    To change a conditional format, do the following: 

1.    Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box. 

2.    Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet or other worksheets, and then by selecting Expand

Dialog . 

3.    Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box is displayed.

3.    Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by: 

•    Selection, click Just these cells. 

•    Corresponding field, click All <value field> cells with the same fields. 

•    Value field, click All <value field> cells.

4.    Under Select a Rule Type, click Use a formula to determine which cells to format. 

5.    Under Edit the Rule Description, in the Format values where this formula is true list box, enter a formula. You must start the formula with an equal sign (=) and the formula must return a logical value of TRUE (1) or FALSE (0).

                                                                                                                | P a g e

3.8       MATCH FUNCTION

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula

=MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of

the item itself. 

Syntax: MATCH(lookup_value, lookup_array, [match_type])

The following table describes how the function finds values based on the setting of the match_type argument. Match_type Behavior 

1 or omitted MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: -2, 1, 0, 1, 2, , A-Z, FALSE, TRUE. 

0 MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order. 

-1 MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, 2, 1, 0, -1, -2, , and so on. 


4.0       EXCEL TABLES

When you create a table (previously known as list) in a Microsoft Excel worksheet (worksheet: The primary document that you use in Excel to store and work with data. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.), you can manage and analyze the data in that table independently of data outside the table. For example, you can filter table columns, add a row for totals and apply table formatting.

If you no longer want to work with your data in a table, you can convert the table to a regular range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of data while keeping any table style formatting that you applied. When you no longer need a table and the data that it contains, you can delete it.

4.1       DATA VALIDATION

You use data validation to control the type of data or the values that users enter into a cell. For example, you may want to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that only positive whole numbers are entered.

This article describes how data validation works in Excel and outlines the different data validation techniques available to you. It does not cover cell protection, which is a feature that lets you "lock" or hide certain cells in a worksheet so that they can't be edited or overwritten.

4.1.1 WHAT IS DATA VALIDATION?

Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. You can configure data validation to prevent users from entering data that is not valid. If you prefer, you can allow users to enter invalid data but warn them when they try to type it in the cell. You can also provide messages to define what input you expect for the cell, and instructions to help users correct any errors.

For example, in a marketing workbook, you can set up a cell to allow only account numbers that are exactly three characters long. When users select the cell, you can show them a message such as this one:

If users ignore this message and type invalid data in the cell, such as a two-digit or five-digit number, you can show them an actual error message.

In a slightly more advanced scenario, you might use data validation to calculate the maximum allowed value in a cell based on a value elsewhere in the workbook. In the following example, the user has typed $4,000 in cell E7, which exceeds the maximum limit specified for commissions and bonuses.

If the payroll budget were to increase or decrease, the allowed maximum in E7 would automatically increase or decrease with it.

Data validation options are located on the Data tab, in the Data Tools group.

You configure data validation in the Data Validation dialog box.

4.1.2 WHEN IS DATA VALIDATION USEFUL?

Data validation is invaluable when you want to share a workbook with others in your organization, and you want the data entered in the workbook to be accurate and consistent. 

Among other things, you can use data validation to do the following:

•    Restrict data to predefined items in a list For example, you can limit types of departments to Sales, Finance, R&D, and IT. Similarly, you can create a list of values from a range of cells elsewhere in the worksheet. 

•    Restrict numbers outside a specified range For example, you can specify a minimum limit of deductions to two times the number of children in a particular cell. 

•    Restrict dates outside a certain time frame For example, you can specify a time frame between today's date and 3 days from today's date. 

•    Restrict times outside a certain time frame For example, you can specify a time frame for serving breakfast between the time when the restaurant opens and 5 hours after the restaurant opens. 

•    Limit the number of text characters For example, you can limit the allowed text in a cell to 10 or fewer characters. Similarly, you can set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1), plus 10 characters. 

•    Validate data based on formulas or values in other cells For example, you can use data validation to set a maximum limit for commissions and bonuses of $3,600, based on the overall projected payroll value. If users enter more than $3,600 in the cell, they see a validation message.

4.1.3 DATA VALIDATION MESSAGES

What users see when they enter invalid data into a cell depends on how you have configured the data validation. You can choose to show an input message when the user selects the cell. Input messages are generally used to offer users guidance about the type of data that you want entered in the cell. This type of message appears near the cell. You can move this message, if you want to, and it remains until you move to another cell or press ESC.

You can also choose to show an error alert that appears only after users enter invalid data.

You can choose from three types of error alerts:

Icon

Type

Use to

Stop  Prevent users from entering invalid data in a cell. A Stop alert  message has two options: Retry or Cancel.

Warning 

Warn users that the data they entered is invalid, without preventing them from entering it. When a Warning alert message appears, users can click Yes to accept the invalid entry, No to edit the invalid entry, or Cancel to remove the invalid entry.

Information  Inform users that the data they entered is invalid, without  preventing them from entering it. This type of error alert is the most flexible. When an Information alert message appears, users can click OK to accept the invalid value or Cancel to reject it. 

You can customize the text that users see in an error alert message. If you choose not to do so, users see a default message.

Input messages and error alerts appear only when data is typed directly into the cells. They do not appear under the following conditions:

•    A user enters data in the cell by copying or filling. 

•    A formula in the cell calculates a result that is not valid. 

•    A macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) enters invalid data in the cell. 

4.1.4 TIPS FOR WORKING WITH DATA VALIDATION

In the following list, you will find tips and tricks for working with data validation in Excel.

•    If you plan to protect the worksheet or workbook, protect it after you have finished specifying any validation settings. Make sure that you unlock any validated cells before you protect the worksheet. Otherwise, users will not be able to type any data in the cells.

•    If you plan to share the workbook, share it only after you have finished specifying data validation and protection settings. After you share a workbook, you won't be able to change the validation settings unless you stop sharing. However, Excel will continue to validate the cells that you have designated while the workbook is being shared.

•    You can apply data validation to cells that already have data entered in them. However, Excel does not automatically notify you that the existing cells contain invalid data. In this scenario, you can highlight invalid data by instructing Excel to circle it on the worksheet. Once you have identified the invalid data, you can hide the circles again. If you correct an invalid entry, the circle disappears automatically. 

•    To quickly remove data validation for a cell, select it, and then open the Data Validation dialog box (Data tab, Data Tools group). On the Settings tab, click Clear

All.

•    To find the cells on the worksheet those have data validation, on the Home tab, in the Editing group, click Find & Select, and then click Data Validation. After you have found the cells that have data validation, you can change, copy, or remove validation settings.

•    When creating a drop-down list, you can use the Define Name command (Formulas tab, Defined Names group) to define a name for the range that contains the list. After you create the list on another worksheet, you can hide the worksheet that contains the list and then protect the workbook so that users won't have access to the list. 

4.1.5 RESTRICT DATA ENTRY TO VALUES IN A DROP-DOWN LIST

It is not possible to change the font or font size for items in a list.

1.    Select one or more cells to validate. 

2.    On the Data tab, in the Data Tools group, click Data Validation. 

3.    In the Data Validation dialog box, click the Settings tab. 

4.    In the Allow box, select List. 

5.    Click the Source box and then type the list values separated by the Microsoft Windows list separator character (commas by default). 

6.    Make sure that the In-cell dropdown check box is selected. Otherwise, you won't be able to see the drop-down arrow next to the cell. 

7.    To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. 

8.    Optionally, display an input message when the cell is clicked. 

9.    Specify how you want Microsoft Office Excel to respond when invalid data is entered. 

10.  Test the data validation to make sure that it is working correctly. 

4.1.6 USE A FORMULA TO CALCULATE WHAT IS ALLOWED

1.    Select one or more cells to validate. 

2.    On the Data tab, in the Data Tools group, click Data Validation. 

3.    In the Data Validation dialog box, click the Settings tab. 

4.    In the Allow box, select Custom. 

5.    In the Formula box, enter a formula that calculates a logical value (TRUE for valid or FALSE for invalid entries). 

For example: 

To make sure that

Enter this formula

The cell for the picnic account (B1) can only be updated if nothing is budgeted for the discretionary account (D1) and the total budget (D2) is less than the $40,000 allocated.

=AND(D1=0,D2<40000)

The cell that contains a product description (B2) only contains text. 

=ISTEXT(B2)

To make sure that

Enter this formula

For the cell that contains a projected advertising budget (B3), the subtotal for subcontractors and services (E1) must be less than or equal to $800, and the total budget amount (E2) must also be less than or equal to $97,000. 

=AND(E1<=800,E2<=97000)

The cell that contains an employee age (B4) is always greater than the number of full years of employment (F1) plus 18 (the minimum age of employment).

=IF(B4>F1+18,TRUE,FALSE)

All the data in the cell range A1:A20 contains unique values. 

=COUNTIF($A$1:$A$20,A1)=1  

The cell that contains a product code name (B5) always begins with the standard prefix of ID- and is at least 10 characters long.

=AND(LEFT(B5, 3) ="ID-",LEN(B5) >

9)  

6.    To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. 

7.    Optionally, display an input message when the cell is clicked. 

8.    Specify how you want Microsoft Office Excel to respond when invalid data is entered. 

9.    Test the data validation to make sure that it is working correctly. 

4.2       DATA VALIDATION BY USING MULTIPLE LISTS

You can limit the choices in an Excel data validation list, by using named ranges and the INDIRECT function, to create dependent data validation lists. In this example, if Fruit is selected as the Category, only Fruit appears in the Item drop-down list.

4.2.1 CREATE NAMED LISTS

Start by creating Named Lists, which will be the choices in the dependent data validation drop down lists. In this example, the first list will be named Produce. 

It contains the Produce categories -- Fruit and Vegetable.

1. Create the first Named List

a.    In an empty area of the workbook, type the entries you want to see in the drop-down lists. These should be one-word entries, to match the dependent list names that will be created.

b.    Select the cells in the list (but not the heading). 

c.  Click in the Name box, to the left of the formula bar 

d.    Type a one-word name for the list, e.g. Produce. 

e.    Press the Enter key. 

2. Create the supporting Named Lists

a.    Type the entries you want to see in the Excel data validation drop-down list for one of the Produce categories. 

b.    Select the cells in the list. 

c.  Click in the Name box, to the left of the formula bar 

d.    Type a one-word name for the list, e.g. Fruit. This name must be exactly the same as the matching entry in the Produce list. 

e.    Press the Enter key.

f.   Create another list with the items for the next category -- Vegetable in this example

4.2.2 APPLY THE EXCEL DATA VALIDATION

The cells in the Category column will allow a List. The cells in the Item column will use the INDIRECT function to select a list.

1. Apply the Excel Data Validation 

a.    Select the cells in which you want to apply Excel data validation using the Category

List 

b.    From the Data menu, choose Validation.

c.  From the Allow drop-down list, choose List 

d.    In the Source box, type an equal sign and the list name, for example: =Produce  e. Click OK. 

2. Create the Dependent Data Validation

a.    Select the cells in which you want to apply dependent data validation using the Fruit or Vegetable List, dependent on which Category has been selected 

b.    From the Data menu, choose Validation. 

c.  From the Allow drop-down list, choose List 

d.    In the Source box, type an equal sign and INDIRECT function, referring to the first data cell in the Category column: =INDIRECT(A2) 

e.    Click OK. 

4.2.3 TEST THE DEPENDENT DATA VALIDATION

1.    Click on a cell in the Category column, and select either Fruit or Vegetable, from the Produce drop down list. 

2.    Press the Tab key on your keyboard, to move to the Item column, in the same row. 

3.    In the Item column, click the drop down arrow, and select an item. The drop down list shows either Fruit or Vegetable items, depending which has been selected in the Category column 

4.3       LOOKUP FUNCTION AND ITS USE

You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range. For example, suppose that you have a list of employees contained in the range A2:C10. The employees' ID numbers are stored in the first column of the range, as shown in the following illustration.

If you know the employee's ID number, you can use the VLOOKUP function to return either the department or the name of that employee. To obtain the name of employee number 38, you can use the formula =VLOOKUP(38, A2:C10, 3, FALSE). This formula searches for the value 38 in the first column of the range A2:C10, and then returns the value that is contained in the third column of the range and on the same row as the lookup value ("Axel Delgado").

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your

comparison values are located in a column to the left of the data that you want to find.

Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

•    When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not contain leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP might return an incorrect or unexpected value. 

•    When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP might return an incorrect or unexpected value. 

•    If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters — the question mark (?) and asterisk (*) — in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

5.0       EXCEL CHARTS

Tabular data form gives merely a vague idea of what it is meant for. Graphs or charts help us to visualize the facts represented by the abstract figures. Excel is arguably the best of the Office Applications when it comes to creating and/or using charts. It allows us to create almost any kind of chart needed with minimal effort. There are 14 different types of charts each with between 2 and 7 sub-types. On top of these there are also many “Custom Types”, although the custom types are mainly just templates of the other chart types.

Charts in Excel can range from the very simple to the very complex and can be used for a multitude of reasons. They can be used to keep track of spending, stock performance, statistics, employee details and much more. Most charts are used to show a comparison of past data in a highly visual style. A well set up chart should be able to inform the user at a glance exactly what the picture is that it is painting.

5.1       ELEMENTS OF A CHART

A chart has many elements. Some of these elements are displayed by default; others can be added as needed. You can change the display of the chart elements by moving them to other locations in the chart, resizing them, or by changing the format. You can also remove chart elements that you do not want to display.

1.    The chart area of the chart.

2.    The plot area of the chart.

3.    The data points of the data series that are plotted in the chart.

4.    The horizontal (category) and vertical (value) axis along which the data is plotted in the chart.

5.    The legend of the chart.

6.    A chart and axis title that you can use in the chart.

7.    A data label that you can use to identify the details of a data point in a data series.

5.2       AVAILABLE CHART TYPES

Microsoft Excel supports many types of charts to help you display data in ways that are meaningful to your audience. When you create a chart or change the type of an existing chart in Microsoft Excel or other Microsoft Office programs such as Microsoft Word, Microsoft PowerPoint, or Microsoft Outlook, you can select one of the following chart types.

5.2.1 COLUMN CHARTS

Data that is arranged in columns or rows on a worksheet can be plotted in a column chart. Column charts are useful for showing data changes over a period of time or for illustrating comparisons among items. 

In column charts, categories are typically organized along the horizontal axis and values along the vertical axis.

5.2.2 LINE CHARTS

Data that is arranged in columns or rows on a worksheet can be plotted in a line chart. Line charts can display continuous data over time, set against a common scale, and are therefore ideal for showing trends in data at equal intervals. In a line chart, category data is distributed evenly along the horizontal axis, & all value data is distributed evenly along the vertical axis. 

You should use a line chart if your category labels are text, and are representing evenly spaced values such as months, quarters, or fiscal years. This is especially true if there are multiple series — for one series, you should consider using a scatter chart. You should also use a line chart if you have several evenly spaced numeric labels, especially years. If you have more than ten numeric labels, use a scatter chart instead. 

5.2.3 PIE CHARTS

Data that is arranged in one column or row only on a worksheet can be plotted in a pie chart. Pie charts show the size of items in one data series, proportional to the sum of the items. The data points in a pie chart are displayed as a percentage of the whole pie.

Consider using a pie chart when:

•    You only have one data series that you want to plot.

•    None of the values that you want to plot are negative.

•    Almost none of the values that you want to plot are zero values.

•    You do not have more than seven categories.

•    The categories represent parts of the whole pie.

Pie charts have the following chart subtypes:



5.2.4 BAR CHARTS

Data that is arranged in columns or rows on a worksheet can be plotted in a bar chart. Bar charts illustrate comparisons among individual items.

Consider using a bar chart when:

•    The axis labels are long.

•    The values that are shown are durations.

Bar charts have the following chart subtypes:

5.2.5 AREA CHARTS

Data that is arranged in columns or rows on a worksheet can be plotted in an area chart. Area charts emphasize the magnitude of change over time, and can be used to draw attention to the total value across a trend. For example, data that represents profit over time can be plotted in an area chart to emphasize the total profit.

By displaying the sum of the plotted values, an area chart also shows the relationship of parts to a whole.

5.2.6 XY (SCATTER) CHARTS

Data that is arranged in columns and rows on a worksheet can be plotted in a xy (scatter) chart.

Scatter             charts show    the relationships among the numeric values in several data series, or plot two groups of numbers as one series of xy coordinates.

A scatter chart has two value axes, showing one set of numeric data along the horizontal axis (x-axis) and another along the vertical axis (y-axis). It combines these values into single data points and displays them in irregular intervals, or clusters. Scatter charts are typically used for displaying and comparing numeric values, such as scientific, statistical, and engineering data. 

Consider using a scatter chart when:

•    You want to change the scale of the horizontal axis.

•    You want to make that axis a logarithmic scale.

•    Values for horizontal axis are not evenly spaced.

•    There are many data points on the horizontal axis.

•    You want to effectively display worksheet data that includes pairs or grouped sets of values and adjust the independent scales of a scatter chart to reveal more information about the grouped values.

•    You want to show similarities between large sets of data instead of differences between data points.

•    You want to compare many data points without regard to time — the more data that you include in a scatter chart, the better the comparisons that you can make.

To arrange data on a worksheet for a scatter chart, you should place the x values in one row or column, and then enter the corresponding y values in the adjacent rows or columns.

5.2.7 SURFACE CHARTS

Data that is arranged in columns or rows on a worksheet can be plotted in a surface chart. A surface chart is useful when you want to find optimum combinations between two sets of data. As in a topographic map, colors and patterns indicate areas that are in the same range of values.

You can use a surface chart when both categories and data series are numeric values.

5.2.8 BUBBLE CHARTS

Data that is arranged in columns on a worksheet so that x values are listed in the first column and corresponding y values and bubble size values are listed in adjacent columns, can be plotted in a bubble chart.

For example, you would organize your data as shown in the following example.

5.2.9 RADAR CHARTS

Data that is arranged in columns or rows on a worksheet can be plotted in a radar chart. Radar charts compare the aggregate values of several data series. 

6.0       PIVOTTABLE AND PIVOTCHART REPORTS

A PivotTable report is useful to summarize, analyze, explore, and present summary data. A PivotChart report can help you visualize PivotTable report summary data so that you can easily see comparisons, patterns, and trends. Both reports enable you to make informed decisions about critical data in your enterprise. The following sections provide an overview of PivotTable and PivotChart reports.

6.1       WHAT IS A PIVOTTABLE REPORT

A PivotTable report is an interactive way to quickly summarize large amounts of data. Use a PivotTable report to analyze numerical data in detail and to answer unanticipated questions about your data. A PivotTable report is especially designed for:

•    Querying large amounts of data in many user-friendly ways. 

•    Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas. 

•    Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas of interest to you. 

•    Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the source data. 

•    Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data to enable you to focus on the information that you want. 

•    Presenting concise, attractive, and annotated online or printed reports.

You often use a PivotTable report when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. In the PivotTable report illustrated below, you can easily see how the third-quarter golf sales in cell F3 compare to sales for another sport, or quarter, or to the total sales.

In a PivotTable report, each column or field in your source data becomes a PivotTable field that summarizes multiple rows of information. In the preceding example , the Sport column becomes the Sport field, and each record for Golf is summarized in a single Golf item.

A value field, such as Sum of Sales, provides the values to be summarized. Cell F3 in the preceding report contains the sum of the Sales value from every row in the source data for which the Sport column contains Golf and the Quarter column contains Qtr3. By default, data in the Values area summarize the underlying source data in the PivotChart report in the following way: numeric values use the SUM function, and text values use the COUNT function.

To create a PivotTable report, you must define its source data, specify a location in the workbook, and lay out the fields.

6.2       WHAT IS A PIVOTCHART REPORT

•    A PivotChart report provides a graphical representation of the data in a PivotTable report, which in this case is called the associated PivotTable report. Like a PivotTable report, a PivotChart report is interactive. When you create a PivotChart report, PivotChart report filters are displayed in the chart area so that you can sort and filter the underlying data of the PivotChart report. Changes that you make to the field layout and data in the associated PivotTable report are immediately reflected in the PivotChart report.

•    A PivotChart report displays data series, categories, data markers, and axes just as standard charts do. You can also change the chart type and other options such as the titles, the legend placement, the data labels, and the chart location. ? A PivotChart report of sport sales by quarter

•    You can automatically create a PivotChart report when you first create a PivotTable report, or you can create a PivotChart report from an existing PivotTable report.

6.3       COMPARING A PIVOTTABLE REPORT AND A PIVOTCHART REPORT

•    When you create a PivotChart report from a PivotTable report, the layout of the PivotChart report, that is, the position of its fields, is determined initially by the layout of the PivotTable report. When you create the PivotChart report first, you determine the chart layout by dragging fields from the PivotTable Field List to specific areas on the chart sheet.

•    The following PivotTable and PivotChart reports of sales data illustrate the relationship between the two.

7.0       CREATE A MACRO

If you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks. A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes. After you create a macro, you can edit it to make minor changes to the way it works.

Suppose that every month, you create a report for your accounting manager. You want to format the names of the customers with overdue accounts in red, and also apply bold formatting. You can create and then run a macro that quickly applies these formatting changes to the cells you select.

7.1       BEFORE YOU RECORD A MACRO 

Make sure the Developer tab is visible on the ribbon. By default, the Developer tab is not visible, so do the following:

1.    Click the File tab, click Options and then click the Customize Ribbon category. 

2.    Under Customize the Ribbon, in the Main Tabs list, click Developer, and then click OK.

7.2       RECORD A MACRO 

1.    In the Code group on the Developer tab, click Record Macro, and then click OK to start recording. 

2.    Perform some actions in your worksheet, such as typing some text, selecting some columns or rows, or filling down some data. 

3.    In the Code group on the Developer tab, click Stop Recording

8.0       SECURITIES OF A WORKBOOK

To prevent a user from accidentally or deliberately changing, moving, or deleting important data from a worksheet or workbook, you can protect certain worksheet or workbook elements, with or without a password. You can remove the protection from a worksheet as needed.

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. 

8.1       PROTECT WORKBOOK ELEMENTS

1.    On the Review tab, in the Changes group, click Protect Workbook

2.    Under Protect workbook for, do one or more of the following: 

§  To protect the structure of a workbook, select the Structure check box. 

§  To keep workbook windows in the same size and position every time the workbook is opened, select the Windows check box. 

3.    To prevent other users from removing workbook protection, in the Password (optional) box, type a password, click OK, and then retype the password to confirm it. 

Note The password is optional. If you do not supply a password, then any user can unprotect the workbook and change the protected elements. Make sure that you choose a password that you can remember, because if you lose the password, you cannot gain access to the protected elements in the workbook.

8.2       REMOVE PROTECTION FROM A WORKSHEET

1.    On the Review tab, in the Changes group, click Unprotect Sheet

Note The Protect Sheet option changes to Unprotect Sheet when a worksheet is protected.

2.    If prompted, type the password to unprotect the worksheet.

9.0       ANNEXURE

9.1       CHART FINDER

9.2       EXCEL SHORTCUT KEYS

NAVIGATE INSIDE WORKSHEETS

Arrow Keys

Move one cell up, down, left, or right in a worksheet.

Page Down/Page Up

Move one screen down / one screen up in a worksheet.

Alt+Page Down/Alt+Page Up

Move one screen to the right / to the left in a worksheet.

Tab/Shift+Tab

Move one cell to the right / to the left in a worksheet.

Ctrl+Arrow Keys

Move to the edge of next data region (cells that contains data)

Home

Move to the beginning of a row in a worksheet.

Ctrl+Home

Move to the beginning of a worksheet.

Ctrl+End

Move to the last cell with content on a worksheet.

Ctrl+f

Display the Find and Replace dialog box (with Find selected).

Ctrl+h

Display the Find and Replace dialog box (with Replace selected).

Shift+F4

Repeat last find.

Ctrl+g (or f5)

Display the 'Go To' dialog box.

Ctrl+Arrow Left/Ctrl+Arrow Right

Inside a cell: Move one word to the left / to the right.

Home/End

Inside a cell: Move to the beginning / to the end of a cell entry.

Alt+Arrow Down

Display the AutoComplete list e.g. in cell with dropdowns or autofilter.

End

Turn 'End' mode on. In End mode, press arrow keys to move to the next nonblank cell in the same column or row as the active cell. From here use arrow keys to move by blocks of data, home to move to last cell, or enter to move to the last cell to the right.

SELECT CELLS

Shift+Space

Select the entire row.

Ctrl+Space

Select the entire column.

Ctrl+Shift+* (asterisk)

Select the current region around the active cell.

Ctrl+a (or

ctrl+Shift+spacebar)

Select the entire worksheet or the data-containing area. Pressing ctrl+a a second time then selects entire worksheet.

Ctrl+Shift+Page Up

Select the current and previous sheet in a workbook.

Ctrl+Shift+o

Select all cells with comments.

Ctrl+Shift+Arrow Key

Extend the selection to the last cell with content in row or column.

Shift+Page Down/Shift+Page Up

Extend the selection down one screen /up one screen.

Shift+Home

Extend the selection to the beginning of the row.

Ctrl+Shift+Home

Extend the selection to the beginning of the worksheet.

Ctrl+Shift+End

Extend the selection to the last used cell on the worksheet (lower-right corner).

EDIT INSIDE CELLS

F2

Edit the active cell with cursor at end of the line.

Alt+Enter

Start a new line in the same cell.

Shift+Enter

Complete a cell entry and move up in the selection.

Tab/Shift+Tab

Complete a cell entry and move to the right / to the left in the selection.

Ctrl+Delete

Delete text to the end of the line.

Ctrl+; (semicolon)

Insert current date.

Ctrl+Shift+: (colon)

Insert current time.

MANAGE ACTIVE SELECTIONS

F8

Turn on extension of selection with arrow keys without having to keep pressing shift.

Shift+F8

Add another (adjacent or non-adjacent) range of cells to the selection. Use arrow keys and shift+arrow keys to add to selection.

Shift+Backspace

Select only the active cell when multiple cells are selected.

Ctrl+Backspace

Show active cell within selection.

Ctrl+. (period)

Move clockwise to the next corner of the selection.

Enter/Shift+Enter

Move active cell down / up in a selection.

Tab/Shift+Tab

Move active cell right / left in a selection.

Ctrl+Alt+Arrow

Right/Ctrl+Alt+Arrow Left

Move to the right / to the left between non-adjacent selections (with multiple ranges selected).

Esc

Cancel Selection.

EDIT ACTIVE OR SELECTED CELLS

Ctrl+d

Fill complete cell down (Copy above cell).

Ctrl+r

Fill complete cell to the right (Copy cell from the left).

Ctrl+"

Fill cell values down and edit (Copy above cell values).

Ctrl+' (apostrophe)

Fill cell formulas down and edit (Copy above cell formulas).

Ctrl+l

Insert a table (display Create Table dialog box).

Ctrl+-

Delete Cell/Row/Column Menu

Ctrl+- with row / column selected

Delete row / delete column.

Ctrl+Shift++

Insert Cell/Row/Column Menu

Ctrl+Shift++ with row / column selected

Insert row/ insert column.

Shift+F2

Insert / Edit a cell comment.

Shift+f10, then m

Delete comment.

Alt+F1

Create and insert chart with data in current range as embedded Chart Object.

F11

Create and insert chart with data in current range in a separate Chart sheet.

Ctrl+k

Insert a hyperlink.

enter (in a cell with a hyperlink)

Activate a hyperlink.

HIDE AND SHOW ELEMENTS

Ctrl+9

Hide the selected rows.

Ctrl+Shift+9

Unhide any hidden rows within the selection.

Ctrl+0 (zero)

Hide the selected columns.

Ctrl+` (grave accent)

Alternate between displaying cell values and displaying cell formulas. Accent grave /not a quotation mark.

Alt+Shift+Arrow Right

Group rows or columns.

Alt+Shift+Arrow Left

Ungroup rows or columns.

Ctrl+6

Alternate between hiding and displaying objects.

Ctrl+8

Display or hides the outline symbols.

Ctrl+6

Alternate between hiding objects, displaying objects, and displaying placeholders for objects.

FORMAT CELLS

Ctrl+1

Format cells dialog.

Ctrl+b (or ctrl+2)

Apply or remove bold formatting.

Ctrl+i (or ctrl+3)

Apply or remove italic formatting.

Ctrl+u (or ctrl+4)

Apply or remove an underline.

Ctrl+5

Apply or remove strikethrough formatting.

Ctrl+Shift+f

Display the Format Cells with Fonts Tab active. Press tab 3x to get to font-size. Used to be ctrl+shift+p, but that seems just get to the Font Tab in 2010.

Alt+' (apostrophe / single quote)

Display the Style dialog box.

NUMBER FORMATS

Ctrl+Shift+$

Apply the Currency format with two decimal places.

Ctrl+Shift+~

Apply the General number format.

Ctrl+Shift+%

Apply the Percentage format with no decimal places.

Ctrl+Shift+#

Apply the Date format with the day, month, and year.

[email protected]

Apply the Time format with the hour & minute, and indicate A.M. or P.M.

Ctrl+Shift+!

Apply the Number format with two decimal places, thousands separator, and minus sign (-) for negative values.

Ctrl+Shift+^

Apply the Scientific number format with two decimal places.

F4

Repeat last formatting action: Apply previously applied Cell Formatting to a different Cell

APPLY BORDERS TO CELLS

Ctrl+Shift+&

Apply outline border from cell or selection

Ctrl+Shift+_ (underscore)

Remove outline borders from cell or selection

Ctrl+1, then Ctrl+Arrow Right/Arrow Left

Access border menu in 'Format Cell' dialog. Once border was selected, it will show up directly on the next Ctrl+1

Alt+t*

Set top border

Alt+b*

Set bottom Border

Alt+l*

Set left Border

Alt+r*

Set right Border

Alt+d*

Set diagonal and down border

Alt+u*

Set diagonal and up border

*In Cell Format in 'Border' Dialog Window

FORMULAS

=

Start a formula.

Alt+=

Insert the AutoSum formula.

Shift+F3

Display the Insert Function dialog box.

Ctrl+a

Display Formula Window after typing formula name.

Ctrl+Shift+a

Insert Arguments in formula after typing formula name. .

Shift+F3

Insert a function into a formula .

Ctrl+Shift+Enter

Enter a formula as an array formula.

F4

After typing cell reference (e.g. =E3) makes reference absolute (=$E$4)

F9

Calculate all worksheets in all open workbooks.

Shift+F9

Calculate the active worksheet.

Ctrl+Alt+F9

Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.

Ctrl+Alt+Shift+F9

Recheck dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.

Ctrl+Shift+u

Toggle expand or collapse formula bar.

ALIGN CELLS

Alt+h, ar

Align Right

Alt+h, ac

Align Center

Alt+h, al

Align Left

MANAGE MULTIPE WORKSHEETS

Shortcuts to delete, rename, or move worksheets are based on the old Excel 2003 menus which still work in Excel 2010.

Shift+F11/Alt+Shift+F1

Insert a new worksheet in current workbook.

Ctrl+Page Down/Ctrl+Page Up

Move to the next / previous worksheet in current workbook.

Shift+Ctrl+Page

Down/Shift+Ctrl+Page Up

Select the current and next sheet(s) / select and previous sheet(s).

Alt+o then h r

Rename current worksheet (format, sheet, rename)

Alt+e then l

Delete current worksheet (Edit, delete)

Alt+e then m

Move current worksheet (Edit, move)

MANAGE MULTIPLE WORKBOOKS

F6/Shift+F6

Move to the next pane / previous pane in a workbook that has been split.

Ctrl+F4

Close the selected workbook window.

Ctrl+n

Create a new blank workbook (Excel File)

Ctrl+Tab/Ctrl+Shift+Tab

Move to next / previous workbook window.

Alt+Space

Display the Control menu for Main Excel window.

Ctrl+F9

Minimize current workbook window to an icon. Also restores ('unmaximizes') all workbook windows.

Ctrl+F10

Maximize or restores the selected workbook window.

Ctrl+F7

Move Workbook Windows which are not maximized.

Ctrl+F8

Perform size command for workbook windows which are not maximzed.

Alt+F4

Close Excel.

VARIOUS EXCEL FEATURES

Ctrl+o

Open File.

Ctrl+s

Save the active file with its current file name, location, and file format.

F12

Display the Save As dialog box.

f10 (or alt)

Turn key tips on or off.

Ctrl+p

Print File (Opens print menu).

F7

Display the Spelling dialog box.

Shift+F7

Display the Thesaurus dialog box.

Alt+F8

Display the Macro dialog box.

Alt+F11

Open the Visual Basic Editor to create Macros.

AUTO FILTER

Alt+Arrow Down

On the field with column head, display the AutoFilter list for the current column .

Arrow Down/Arrow Up

Select the next item / previous item in the AutoFilter list.

Alt+Arrow Up

Close the AutoFilter list for the current column.

Home/End

Select the first item / last item in the AutoFilter list.

Enter

Filter the list by using the selected item in the AutoFilter list.

PIVOT TABLES

Arrow Keys

Navigate inside Pivot tables.

Home/End

Select the first / last visible item in the list.

Alt+c

Move the selected field into the Column area.

Alt+d

Move the selected field into the Data area.

Alt+l

Display the PivotTable Field dialog box.

Alt+p

Move the selected field into the Page area.

Alt+r

Move the selected field into the Row area.

Ctrl+Shift+* (asterisk)

Select the entire PivotTable report.

arrow keys to select the cell that contains the field, and then alt+Arrow Down

Display the list for the current field in a PivotTable report.

arrow keys to select the page field in a PivotChart report, and then alt+Arrow Down

Display the list for the current page field in a PivotChart report.

Enter

Display the selected item.

Space

Select or clear a check box in the list.

Ctrl+tabctrl+Shift+Tab

select the PivotTable toolbar.

enter then arrow down /Arrow Up

On a field button: select the area you want to move the selected field to.

Alt+Shift+Arrow Right

Group selected PivotTable items.

Alt+Shift+Arrow Left

Ungroup selected PivotTable items.



2