G-TEC COMPUTER EDUCATION

ISO 9001:2015 CERTIFIED

Advanced Excel
https://shortcutworld.com/Excel/win/Microsoft-Excel_2013_Shortcuts

What is Microsoft Excel?

Microsoft Excel is a spreadsheet program that is used to record and analyses numerical
data.
Spreadsheet is a collection of columns and rows that form a table. Alphabetical
letters are usually assigned to columns and numbers are usually assigned to rows. The
point where a column and a row meet is called a cell. The address of a cell is given by the
letter representing the column and the number representing a row.

Why Should I Learn Microsoft Excel?

We all deal with numbers in one way or the other. We all have daily expenses
which we pay for from the monthly income that we earn. For one to spend wisely, they
will need to know their income vs. expenditure. Microsoft Excel comes in handy when
we want to record, analyze and store such numeric data.
How to Open Microsoft Excel?
Running Excel is not different from running any other Windows program. If you are
running Windows with a GUI like (Windows XP, Vista, and 7) follow the following steps.

● Click on start menu

● Point to all programs
● Point to Microsoft Excel
● Click on Microsoft Excel
Alternatively, you can also open it from the start menu if it has been added there. You
can also open it from the desktop shortcut if you have created one.
Understanding the Ribbon
The ribbon provides shortcuts to commands in Excel. A command is an action that the
user performs. An example of a command is creating a new document, printing a
documenting, etc. The image below shows the ribbon used in Excel 2013.
Ribbon components explained
Ribbon start button - it is used to access commands i.e. creating new documents, saving
existing work, printing, accessing the options for customizing Excel, etc.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Ribbon tabs – the tabs are used to group similar commands together. The home tab is
used for basic commands such as formatting the data to make it more presentable,
sorting and finding specific data within the spreadsheet.

Ribbon bar – the bars are used to group similar commands together. As an example, the
Alignment ribbon bar is used to group all the commands that are used to align data
together.
worksheet ,Rows and Columns, Sheets, Workbooks
A worksheet is a collection of rows and columns
When a row and a column meet, they form a cell. Cells are used to record data.
Each cell is uniquely identified using a cell address. Columns are usually labelled with
letters while rows are usually numbers.
A workbook is a collection of worksheets. By default, a workbook has three
worksheets in Excel. You can delete or add more sheets to suit your requirements. By
default, the sheets are named Sheet1, Sheet2 and so on.
Customization Microsoft Excel Environment
Customization of ribbon
● Click on the ribbon start button
● Select options from the drop down menu. You should be able to see an Excel
Options dialog window
● Select the customize ribbon option from the left-hand side panel.
● On your right-hand side, remove the check marks from the tabs that you do not
wish to see on the ribbon. For this example, we have removed Page Layout,
Review, and View tab.
● Click on the "OK" button when you are done.
Adding custom tabs to the ribbon
You can also add your own tab, give it a custom name and assign commands to it. Let's
add a tab to the ribbon with the text Gtec
1. Right click on the ribbon and select Customize the Ribbon. The dialogue window
shown above will appear
2. Click on new tab button as illustrated in the animated image below
3. Select the newly created tab
4. Click on Rename button
5. Give it a name of GTEC
6. Select the New Group (Custom) under GTEC tab as shown in the image below

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

7. Click on Rename button and give it a name of My Commands

8. Let's now add commands to my ribbon bar
9. The commands are listed on the middle panel
10. Select All chart types command and click on Add button
11. Click on OK

Relative and Absolute Cell References

There are two types of cell references: relative and absolute. Relative and absolute
references behave differently when copied and filled to other cells. Relative references
change when a formula is copied to another cell. Absolute references, on the other
hand, remain constant no matter where they are copied.
Relative references
By default, all cell references are relative references. 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 become =A2+B2.
Relative references are especially convenient whenever you need to repeat the same
calculation across multiple rows or columns.
Absolute references
There may be times when you do not want a cell reference to change when filling cells.
Unlike relative references, absolute references do not change when copied or filled. You
can use an absolute reference to keep a row and/or column constant.
An absolute reference is designated in a formula by the addition of a dollar sign ($)
before the column and row. If it precedes the column or row (but not both), it's known
as a mixed reference.

Create an Absolute Reference

Select another cell, and then press the F4 key to make that cell reference
absolute. You can continue to press F4 to have Excel cycle through the
different reference types.

Conditional Formatting

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Open the Conditional Formatting dialogue box (Home–> Conditional Formatting–> New
Rule) [Keyboard Shortcut – Alt + O + D].

Here are the steps to highlight alternate rows using conditional

formatting in Excel.

● Select the dataset. In the example above, select A2:C13

(which excludes the header). If you want to include the
header as well, then select the entire data set.

● Open the Conditional Formatting dialogue box (Home–>

Conditional Formatting–> New Rule) [Keyboard Shortcut –

Alt + O + D].

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● In the dialogue box, select ‘Use a Formula to determine

which cells to format’ dialogue box.

● Enter the following formula in the field in the ‘Edit the Rule
Description’ section:
=ISODD(ROW())

● The above formula checks all the cells and if the ROW
number of a cell is odd, then it returns TRUE. The specified

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

conditional format would be applied to all the cell that return

TRUE.

● Set the format that you want to apply to the cells that are
blank or have errors. To do this, click on the Format button.
It will open the ‘Format Cells’ dialogue box, where you can
specify the format.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● Click OK.

That’s it! The alternate rows in the data set will get highlighted.

You can use the same technique in many cases. All you need to do is
use the relevant formula in the conditional formatting. Here are some
examples:

● Highlight alternate even rows: =ISEVEN(ROW())

● Highlight alternate add rows: =ISODD(ROW())
● Highlight every 3rd row: =MOD(ROW(),3)=0

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

7. Search and Highlight Data using Conditional Formatting

This one is a bit advanced use of conditional formatting. It would make

you look like an Excel rockstar.

Suppose you have a dataset as shown below, with Products Name,

Sales Rep, and Geography. The idea is to type a string in cell C2, and if
it matches with the data in any cell(s), then that should get highlighted.
Something as shown below:

Here are the steps to create this Search and Highlight functionality:

● Select the dataset.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● Go to Home –> Conditional Formatting -> New

Rule (Keyboard Shortcut – Alt + O + D).

● In the New Formatting Rule dialogue box, select the option

‘Use a formula to determine which cells to format’.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● Enter the following formula in the field in the ‘Edit the Rule
Description’ section:
=AND($C$2<>””,$C$2=B5)

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● Set the format that you want to apply to the cells that are
blank or have errors. To do this, click on the Format button.
It will open the ‘Format Cells’ dialogue box, where you can
specify the format.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● Click OK.

That’s it! Now when you enter anything in cell C2 and hit enter, it will
highlight all the matching cells.

How does this work?

The formula used in conditional formatting evaluates all the cells in the
dataset. Let’s say you enter Japan in cell C2. Now Excel would evaluate
the formula for each cell.

The formula would return TRUE for a cell when two conditions are met:

● Cell C2 is not empty.

● The content of cell C2 exactly matches the content of the

cell in the dataset.

Hence, all the cells that contain the text Japan get highlighted.

Important things to know about Conditional Formatting in Excel

● Conditional formatting in volatile. It can lead to a slow workbook. Use it only
when needed.
● When you copy paste cells that contain conditional formatting, conditional
formatting also gets copied.
● If you apply multiple rules on the same set of cells, all rules remain active. In
the case of any overlap, the rule applied last is given preference. You can,

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

however, change the order by changing the order from the Manage
Rules dialogue box.
Reference link
https://trumpexcel.com/excel-conditional-formatting/

Excel Data Validation, Filters, Grouping

Data validation
Data validation is very important in the sense that it helps us avoid mistakes that
can be avoided. Let's assume you are recording student exam marks and you know the
minimum is 0 and the maximum is 100. You can take advantage of validation features to
ensure that only values between 0 and 100 are entered.

● Click on the DATA tab

● Select the cells C2 to C6 (The cells that will be used to record the
scores)
● Click on Data validation drop down list.
● Click on Data validation.
● You will get the dialogue window
● Click on Error Alert tab
● Enter the alert title and message as shown in the diagram below.
● Click on OK button
● Try to enter a score greater than 200. You will get the following error
message

Data filters
Data filters allow us to get data that matches our desired criteria. Let's say we want to
show the results of all the students whose names start with "ja" or get scores that are
less than, greater than or equal to a certain value, we can use filters to get such data.
● Click on DATA tab on the ribbon
● Click on Sort & Filter drop down list as shown in the image below
● Click on the Name Filter
● Select text filters
● Select begins with
● You will get the following window.
● Enter "ja" and click on "OK" button
● You should be able to see only the results for Jane and James.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Group and Ungroup

Groups allow us to view easily and hide unnecessary details from either columns or
rows. In addition to that, we can also use groups to analyses data that belongs to a
common category.
What is a formula?
It is the simplest form, a formula is an expression made up of cell addresses and
arithmetic operators. Formulas can also be made up of discrete values i.e. =6*3. Excel
evaluates the formula to a value. An example of a formula looks as follows.
=A2 * D2 / 2
HERE,
"=" tells Excel that this is a formula, and it should evaluate it.
"A2" * D2" makes reference to cell addresses A2 and D2 then multiplies the values found
in these cell addresses.
"/" is the division arithmetic operator
"2" is a discrete value
What is a function? (Function Wizard)
A function is a predefined formula. Functions allow you to use descriptive names to
automatically apply formulas for you. Examples of functions include;

● SUM for summation of a range of numbers

● AVERAGE for calculating the average of a given range of numbers
● COUNT for counting the number of items in a given range
Common functions

statistical functions.

S/N FUNCTION CATEGORY DESCRIPTION USAGE

01 SUM Math & Trig Adds all the values in a range of cells =SUM(E4:E8)

02 MIN Statistical Finds the minimum value in a range of cells =MIN(E4:E8)

03 MAX Statistical Finds the maximum value in a range of cells =MAX(E4:E8)

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

04 AVERAGE Statistical Calculates the average value in a range of cells =AVERAGE(E4:E8)

05 COUNT Statistical Counts the number of cells in a range of cells =COUNT(E4:E8)

06 LEN Text Returns the number of characters in a string =LEN(B7)

text

07 SUMIF Math & Trig Adds all the values in a range of cells that meet =SUMIF(D4:D8,">=1000",C4:C
a specified criteria. =SUMIF(range,criteria,
[sum_range])

08 AVERAGEI Statistical Calculates the average value in a range of cells =AVERAGEIF(F4:F8,"Yes",E4:

F that meet the specified criteria.
=AVERAGEIF(range,criteria,
[average_range])

09 DAYS Date & Time Returns the number of days between two dates =DAYS(D4,C4)

10 NOW Date & Time Returns the current system date and time =NOW()

Numeric Functions
As the name suggests, these functions operate on numeric data. The
following table shows some of the common numeric functions.

S/ FUNCTION CATEGORY DESCRIPTION USAGE

N

1 ISNUMBER Information Returns True if the supplied =ISNUMBER(A3)

value is numeric and False if it
is not numeric

2 RAND Math & Trig Generates a random number =RAND()

between 0 and 1

3 ROUND Math & Trig Rounds off a decimal value to =ROUND(3.14455,2)

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

the specified number of

decimal points

4 MEDIAN Statistical Returns the number in the =MEDIAN(3,4,5,2,5)

middle of the set of given
numbers

5 PI Math & Trig Returns the value of Math =PI ( )

Function PI(π)

6 POWER Math & Trig Returns the result of a number =POWER(2,4)

raised to a
power. POWER( number,
power )

7 MOD Math & Trig Returns the Remainder when =MOD(10,3)

you divide two numbers

8 ROMAN Math & Trig Converts a number to roman =ROMAN(1984)

numerals

String functions
These functions are used to manipulate text data. The following table shows
some of the common string functions.

S/N FUNCTION CATEGORY DESCRIPTION USAGE COMMEN

1 LEFT Text Returns a number of specified characters from the start (left- =LEFT("GURU99",4) Left 4 Char
hand side) of a string of "GURU9

2 RIGHT Text Returns a number of specified characters from the end (right- =RIGHT("GURU99",2) Right 2 Ch
hand side) of a string of "GURU9

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

3 MID Text Retrieves a number of characters from the middle of a string =MID("GURU99",2,3) Retrieving
from a specified start position and length. =MID (text, Characters
start_num, num_chars)

4 ISTEXT Information Returns True if the supplied parameter is Text =ISTEXT(value) value - The
to check.

5 FIND Text Returns the starting position of a text string within another =FIND("oo","Roofing",1) Find oo in
text string. This function is case-sensitive. =FIND(find_text, "Roofing",
within_text, [start_num]) is 2

6 REPLACE Text Replaces part of a string with another specified =REPLACE("Roofing",2,2,"xx") Replace "o
string. =REPLACE (old_text, start_num, num_chars, "xx"
new_text)

Date Time Functions

https://www.excelfunctions.net/excel-date-and-time-functions.html

These functions are used to manipulate date values. The following table
shows some of the common date functions

S/N FUNCTION CATEGORY DESCRIPTION USAGE

1 DATE Date & Time Returns the number that =DATE(2015,2,4)

represents the date in excel
code

2 DAYS Date & Time Find the number of days =DAYS(D6,C6)

between two dates

3 MONTH Date & Time Returns the mo nth from a =MONTH("4/2/2015")

date value

4 MINUTE Date & Time Returns the minutes from a =MINUTE("12:31")

time value

5 YEAR Date & Time Returns the year from a date =YEAR("04/02/2015")

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

value

Excel EDATE Function

Function Description

The Excel Edate function returns a date that is a specified number of months
before or after a supplied start date.

The syntax of function is:

EDATE( start_date, months )

Where the arguments are as follows:

start_date - The initial date, from which to count the number of

months.

months - The number of months to add to (or subtract from)

the start_date.

Date Arguments
Note that Microsoft advises that you do not type dates directly into functions, as
Excel interprets text representations of dates differently, depending on the date
interpretation settings on your computer.

Therefore the start_date argument for the Edate function should be input as
either:
● A reference to a cell containing a date
or
● A date returned from another function or formula.

Edate Function Examples

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Column B of the following spreadsheet shows six examples of the Excel Edate
function.

Formulas: Results:

A B A B

1 31-Dec-2015 =EDATE( A1, 1 ) 1 31-Dec-2015 31-Jan-2016

2 31-Dec-2015 =EDATE( A2, -1 ) 2 31-Dec-2015 30-Nov-2015

3 31-Dec-2015 =EDATE( A3, 2 ) 3 31-Dec-2015 29-Feb-2016

4 28-Feb-2016 =EDATE( A4, 12 ) 4 28-Feb-2016 28-Feb-2017

5 29-Feb-2016 =EDATE( A5, 12 ) 5 29-Feb-2016 28-Feb-2017

6 29-Feb-2016 =EDATE( A5, -12 ) 6 29-Feb-2016 28-Feb-2015

The Excel WORKDAY Function

Function Description
The Excel Workday function returns a date that is a supplied number of working
days (excluding weekends and holidays) ahead of a given start date.

The syntax of the function is:

WORKDAY( start_date, days, [holidays] )

where the arguments are as follows:

start_date - The initial date, from which to count the number of

workdays.

days - The number of workdays to add onto start_date.

[holidays] - An optional argument, which specifies an array of dates

(in addition to weekends) that are not to be counted as

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

working days.

Note that Microsoft recommends that the start_date and [holidays] arguments
should be input as either:
● References to cells containing dates
or
● Dates returned from formulas.

If you attempt to input Excel date arguments as text, there is a risk that Excel
may misinterpret them, depending on the date system or date interpretation
settings on your computer.

Workday Function Examples

In the spreadsheets below, the Excel Workday function is used to find a date that
is 25 work days after Dec 01, 2015. The first example (in cell A7) only excludes
weekends from the calculation, while the second example (in cell A8) excludes
weekends and a supplied list of holidays.

Formulas:

A B C

1 Start Date: 12/01/2015

2 Holidays: 12/25/2015

3 12/28/2015

4 01/01/2016

6 Date Falling 25 work days after Dec 1st, 2015:

7 =WORKDAY( B1, 25 ) - No Holidays

- Excludes holidays in cells B2-

8 =WORKDAY( B1, 25, B2:B4 ) B4

Results:

A B C

6 Date Falling 25 work days after Dec 1st, 2015:

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

7 01/05/2016 - No Holidays

- Excludes holidays in cells B2-

8 01/08/2016 B4

Excel EOMONTH

The Excel EOMONTH function returns the last day of the month, n months in
the past or future. You can use EDATE to calculate expiration dates, due
dates, and other dates that need to land on the last day of a month. Use a
positive value for months to get a date in the future, and a negative value to
get a date in the past.

The Excel DAYS360 Function

Function Description
The Excel Days360 function returns the number of days between 2 dates, based
on a 360-day year (12 x 30 months).

The syntax of the function is:

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

DAYS360( start_date, end_date, [method] )

where the arguments are as follows:

start_date - The start of the period.

end_date - The end of the period.

[method] - An optional logical argument, which gives the method to

be used in the calculation. This can be either:

FALSE (or - US (NASD) method used

omitted)

TRUE - European method used

VLOOKUP function
The VLOOKUP function is used to perform a vertical look up in the left most
column and return a value in the same row from a column that you specify.
Let's explain this in a layman's language. The home supplies budget has a
serial number column that uniquely identifies each item in the budget.
Suppose you have the item serial number, and you would like to know the
item description, you can use the VLOOKUP function. Here is how the
VLOOKUP function would work.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

=VLOOKUP (C12, A4:B8, 2, FALSE)

HERE,

● "=VLOOKUP" calls the vertical lookup function

● "C12" specifies the value to be looked up in the left most column
● "A4:B8" specifies the table array with the data
● "2" specifies the column number with the row value to be returned by
the VLOOKUP function
● "FALSE," tells the VLOOKUP function that we are looking for an exact
match of the supplied look up value

The animated image below shows this in action

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

HLOOKUP Function

HLOOKUP stands for Horizontal Lookup and can be used to retrieve

information from a table by searching a row for the matching data and
outputting from the corresponding column. While VLOOKUP searches for the
value in a column, HLOOKUP searches for the value in a row.

Formula

HLOOKUP(value to look up, table area, row number)

Now, if our objective is to fetch the marks of student D in Management, we can use
HLOOKUP as follows:

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

HLOOKUP function in Excel comes with the following arguments:

The next step would be to give the table array. Table array is nothing but rows of data
in which the lookup value would be searched. Table array can be a regular range or a
named range, or even an Excel table. Here we will give row A1:F5 as the reference.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Next, we would define ‘row_index_num,’ which is the row number in the table_array
from where the value would be returned. In this case, it would be 4, as we are fetching
the value from the fourth row of the given table.

Suppose, if we require marks in Economics then we would put row_index_num as 3.

The next is range_lookup. It makes HLOOKUP search for exact or approximate value.
As we are looking out for an exact value, it would be False.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

The result would be 72.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

As you can see in the screenshot above, we need to give the lookup_value first. Here, it
would be student D as we need to find his marks in Management. Now, remember that
lookup_value can be a cell reference or a text string, or it can be a numerical value as
well. In our example, it would be student name as shown below:

What is a Logical Function?

It is a feature that allows us to introduce decision-making when executing formulas
and functions. Functions are used to;
● Check if a condition is true or false
● Combine multiple conditions together

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

What is a condition and why does it matter?

A condition is an expression that either evaluates to true or false. The expression could
be a function that determines if the value entered in a cell is of numeric or text data
type, if a value is greater than, equal to or less than a specified value, etc.
IF Function example
We will work with the home supplies budget from this tutorial. We will use the IF
function to determine if an item is expensive or not. We will assume that items with a
value greater than 6,000 are expensive. Those that are less than 6,000 are less
expensive. The following image shows us the dataset that we will work with.

● Put the cursor focus in cell F4

● Enter the following formula that uses the IF function
=IF(E4<6000,"Yes","No")
HERE,
● "=IF(…)" calls the IF functions
● "E4<6000" is the condition that the IF function evaluates. It checks the value of
cell address E4 (subtotal) is less than 6,000
● "Yes" this is the value that the function will display if the value of E4 is less than
6,000
● "No" this is the value that the function will display if the value of E4 is greater
than 6,000
When you are done press the enter key
You will get the following results

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Excel Logic functions explained

The following table shows all of the logical functions in Excel

S/N FUNCTION CATEGORY DESCRIPTION USAGE

01 AND Logical Checks multiple conditions and returns true if they all =AND(1 > 0,ISNUMBER(1)) The
the conditions evaluate to true. above function returns TRUE
because both Condition is True.

02 FALSE Logical Returns the logical value FALSE. It is used to compare FALSE()
the results of a condition or function that either
returns true or false

03 IF Logical Verifies whether a condition is met or not. If the =IF(ISNUMBER(22),"Yes", "No")

condition is met, it returns true. If the condition is 22 is Number so that it return
not met, it returns false. =IF(logical_test, Yes.
[value_if_true],[value_if_false])

04 IFERROR Logical Returns the expression value if no error occurs. If an =IFERROR(5/0,"Divide by zero
error occurs, it returns the error value error")

05 IFNA Logical Returns value if #N/A error does not occur. If #N/A =IFNA(D6*E6,0) N.B the above
error occurs, it returns NA value. #N/A error means a formula returns zero if both or
value if not available to a formula or function. either D6 or E6 is/are empty

06 NOT Logical Returns true if the condition is false and returns false =NOT(ISTEXT(0)) N.B. the
if condition is true above function returns true.
This is because ISTEXT(0)
returns false and NOT function
converts false to TRUE

7 OR Logical Used when evaluating multiple conditions. Returns =OR(D8="admin",E8="cashier")

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

true if any or all of the conditions are true. Returns N.B. the above function returns
false if all of the conditions are false true if either or both D8 and E8
admin or cashier

08 TRUE Logical Returns the logical value TRUE. It is used to compare TRUE()
the results of a condition or function that either
returns true or false

Nested IF functions
A nested IF function is an IF function within another IF function.
The formula for the above flowchart is as follows
=IF(B1="Sunday","time to rest",IF(B1="Saturday","party well","to do list"))
HERE,
● "=IF(….)" is the main if function
● "=IF(…,IF(….))" the second IF function is the nested one. It provides further
evaluation if the main IF function returned false.

What is a chart?
A chart is a visual representative of data in both columns and rows. Charts are usually
used to analyse trends and patterns in data sets. Let's say you have been recording the
sales figures in Excel for the past three years. Using charts, you can easily tell which year
had the most sales and which year had the least. You can also draw charts to compare
set targets against actual achievements.
Types of charts

The following table shows some of the most commonly used charts and
when you should consider using them.

S/N CHART WHEN EXAMPLE

TYPE SHOULD I
USE IT?

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

1 Pie When you

Chart want to
quantify
items and
show them as
percentages.

2 Bar When you

Chart want to
compare
values across
a few
categories.
The values
run
horizontally

3 Column When you

chart want to
compare
values across
a few
categories.
The values
run vertically

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

4 Line When you

chart want to
visualize
trends over a
period of time
i.e. months,
days, years,
etc.

5 Combo When you

Chart want to
highlight
different
types of
information

The importance of charts

● Allows you to visualize data graphically
● It's easier to analyse trends and patterns in the charts
● Easy to interpret compared to data in cells

Step by step example of creating charts in Excel

In this tutorial, we are going to plot a simple column chart that will display
the sold quantities against the sales year.

● Open Excel
● Enter the data from the sample data table above
● Your workbook should now look as follows

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

To get the desired chart you have to follow the following steps

● Select the data you want to represent in graph

● Click on INSERT tab from the ribbon
● Click on the Column chart drop down button
● Select the chart type you want

You should be able to see the following chart

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Change in Charts Group

The Charts Group on the Ribbon in MS Excel 2013 looks as follows −

You can observe that −

● The subgroups are clubbed together.
● A new option ‘Recommended Charts’ is added.

Let us create a chart. Follow the steps given below.

Step 1 − Select the data for which you want to create a chart.
Step 2 − Click on the Insert Column Chart icon as shown below.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

When you click on the Insert Column chart, types of 2-D Column Charts,
and 3-D Column Charts are displayed. You can also see the option of
More Column Charts.
Step 3 − If you are sure of which chart you have to use, you can choose a Chart
and proceed.
If you find that the one you pick is not working well for your data, the
new Recommended Charts command on the Insert tab helps you to create a
chart quickly that is just right for your data.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Chart Recommendations
Let us see the options available under this heading. (use another word for
heading)
Step 1 − Select the Data from the worksheet.
Step 2 − Click on Recommended Charts.
The following window displaying the charts that suit your data will be displayed.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 3 − As you browse through the Recommended Charts, you will see the
preview on the right side.
Step 4 − If you find the chart you like, click on it.
Step 5 − Click on the OK button. If you do not see a chart you like, click on All
Charts to see all the available chart types.
Step 6 − The chart will be displayed in your worksheet.
Step 7 − Give a Title to the chart.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Fine Tune Charts Quickly

Click on the Chart. Three Buttons appear next to the upper-right corner of the
chart. They are −

● Chart Elements
● Chart Styles and Colors, and
● Chart Filters
You can use these buttons −

● To add chart elements like axis titles or data labels

● To customize the look of the chart, or
● To change the data that’s shown in the chart

Select / De-select Chart Elements

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 1 − Click on the Chart. Three Buttons will appear at the upper-right corner
of the chart.
Step 2 − Click on the first button Chart Elements. A list of chart elements will be
displayed under the Chart Elements option.

Step 3 − Select / De-select Chart Elements from the given List. Only the
selected chart elements will be displayed on the Chart.

Format Style

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 1 − Click on the Chart. Three Buttons will appear at the upper-right corner
of the chart.
Step 2 − Click on the second button Chart Styles. A small window opens with
different options of STYLE and COLOR as shown in the image given below.
Step 3 − Click on STYLE. Different options of Style will be displayed.

Step 4 − Scroll down the gallery. The live preview will show you how your chart
data will look with the currently selected style.
Step 5 − Choose the Style option you want. The Chart will be displayed with the
selected Style as shown in the image given below.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Format Color
Step 1 − Click on the Chart. Three Buttons will appear at the upper-right corner
of the chart.
Step 2 − Click on Chart Styles. The STYLE and COLOR window will be
displayed.
Step 3 − Click on the COLOR tab. Different Color Schemes will be displayed.

Step 4 − Scroll down the options. The live preview will show you how your chart
data will look with the currently selected color scheme.
Step 5 − Pick the color scheme you want. Your Chart will be displayed with the
selected Style and Color scheme as shown in the image given below.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

You can change color schemes from Page Layout Tab also.
Step 1 − Click the tab Page Layout.
Step 2 − Click on the Colors button.
Step 3 − Pick the color scheme you like. You can also customize the Colors and
have your own color scheme.

Filter Data being displayed on the Chart

Chart Filters are used to edit the data points and names that are visible on the
chart being displayed, dynamically.
Step 1 − Click on the Chart. Three Buttons will appear at the upper-right corner
of the chart.
Step 2 − Click on the third button Chart Filters as shown in the image.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 3 − Click on VALUES. The available SERIES and CATEGORIES in your

Data appear.

Step 4 − Select / De-select the options given under Series and Categories. The
chart changes dynamically.
Step 5 − After, you decide on the final Series and Categories, click on Apply.
You can see that the chart is displayed with the selected data.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

The Format pane is a new entry in Excel 2013. It provides advanced formatting
options in clean, shiny, new task panes and it is quite handy too.
Step 1 − Click on the Chart.
Step 2 − Select the chart element (e.g., data series, axes, or titles).
Step 3 − Right-click the chart element.
Step 4 − Click Format <chart element>. The new Format pane appears with
options that are tailored for the selected chart element.

Format Axis
Step 1 − Select the chart axis.
Step 2 − Right-click the chart axis.
Step 3 − Click Format Axis. The Format Axis task pane appears as shown in
the image below.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

You can move or resize the task pane by clicking on the Task Pane Options to
make working with it easier.

The small icons at the top of the pane are for more options.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 4 − Click on Axis Options.

Step 5 − Select the required Axis Options. If you click on a different chart
element, you will see that the task pane automatically updates to the new chart
element.
Step 6 − Select the Chart Title.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 7 − Select the required options for the Title. You can format all the Chart
Elements using the Format Task Pane as explained for Format
Axis and Format Chart Title.

Provision for Combo Charts

There is a new button for combo charts in Excel 2013.

The following steps will show how to make a combo chart.

Step 1 − Select the Data.
Step 2 − Click on Combo Charts. As you scroll on the available Combo Charts,
you will see the live preview of the chart. In addition, Excel displays guidance on
the usage of that particular type of Combo Chart as shown in the image given
below.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 3 − Select a Combo Chart in the way you want the data to be displayed.
The Combo Chart will be displayed.

Ribbon of Chart Tools

When you click on your Chart, the CHART TOOLS tab, comprising of
the DESIGN and FORMAT tabs is introduced on the ribbon.
Step 1− Click on the Chart. CHART TOOLS with
the DESIGN and FORMAT tabs will be displayed on the ribbon.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Let us understand the functions of the DESIGN tab.

Step 1 − Click on the chart.
Step 2 − Click on the DESIGN tab. The Ribbon now displays all the options
of Chart Design.

The first button on the ribbon is the Add Chart Element, which is the same as
the Chart Elements, given at the upper right corner of the Charts as shown
below.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Quick Layout
You can use Quick Layout to change the overall layout of the Chart quickly by
choosing one of the predefined layout options.
Step 1 − Click on Quick Layout. Different possible layouts will be displayed.

Step 2 − As you move on the layout options, the chart layout changes to that
particular option. A preview of how your chart will look is shown.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 3 − Click on the layout you like. The chart will be displayed with the chosen
layout.

Change Colors
The Change Colors option is the same as in CHART ELEMENTS → Change
Styles → COLOR.

Chart Styles
The Chart Styles option is the same as in CHART ELEMENTS → Change
Styles → STYLE.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Switch Row / Column

You can use the Switch Row / Column button on the ribbon to change the
display of data from X-axis to Y-axis and vice versa. Follow the steps given
below to understand this.

Step 1 − Click on Switch Row / Column. You can see that the data will be
swapped between X-Axis and Y-Axis.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Select Data
You can change the Data Range included in the chart using this command.

Step 1 − Click on Select Data. The Select Data Source window appears as
shown in the image given below.
Step 2 − Select the Chart Data Range.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

The window also has the options to edit the Legend Entries
(Series) and Categories. This is the same as Chart Elements → Chart Filters
→ VALUES.

Change Chart Type

You can change to a different Chart Type using this option.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 1 − Click on the Change Chart Type window. The Change Chart
Type window appears.

Step 2 − Select the Chart Type you want. The Chart will be displayed with the
type chosen.

Move Chart
You can move the Chart to another Worksheet in the Workbook using this
option.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Click on Move Chart. The Move Chart window appears.

You can have aesthetic and meaningful Data Labels. You can

● include rich and refreshable text from data points or any other text in your data labels
● enhance them with formatting and additional freeform text
● display them in just about any shape
Data labels stay in place, even when you switch to a different type of chart.
You can also connect them to their data points with Leader Lines on all charts
and not just pie charts, which was the case in earlier versions of Excel.

Formatting Data Labels

We use a Bubble Chart to see the formatting of Data Labels.
Step 1 − Select your data.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 2 − Click on the Insert Scatter or the Bubble Chart.

The options for the Scatter Charts and the 2-D and 3-D Bubble Charts appear.

Step 3 − Click on the 3-D Bubble Chart. The 3-D Bubble Chart will appear as
shown in the image given below.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 4 − Click on the chart and then click on Chart Elements.

Step 5 − Select Data Labels from the options. Select the small symbol given on
the right of Data Labels. Different options for the placement of the Data
Labels appear.
Step 6 − If you select Center, the Data Labels will be placed at the center of the
Bubbles.

Step 7 − Right-click on any one Data Label. A list of option appears as shown in
the image given below.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 8 − Click on the Format Data Label. Alternatively, you can also click
on More Options available in the Data Labels options to display the Format
Data Label Task Pane.

The Format Data Label Task Pane appears.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

There are many options available for formatting of the Data Label in the Format
Data Labels Task Pane. Make sure that only one Data Label is selected while
formatting.
Step 9 − In Label Options → Data Label Series, click on Clone Current
Label.

This will enable you to apply your custom Data Label formatting quickly to the
other data points in the series.

Look of the Data Labels

You can do many things to change the look of the Data Label, like changing the
Fill color of the Data Label for emphasis.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 1 − Click on the Data Label, whose Fill color you want to change. Double
click to change the Fill color for just one Data Label. The Format Data
Label Task Pane appears.
Step 2 − Click Fill → Solid Fill. Choose the Color you want and then make the
changes.
Step 3 − Click Effects and choose the required effects. For example, you can
make the label pop by adding an effect. Just be careful not to go overboard
adding effects.
Step 4 − In the Label Options → Data Label Series, click on Clone Current
Label. All the other data labels will acquire the same effect.

Shape of a Data Label

You can personalize your chart by changing the shapes of the Data Label.
Step 1 − Right-click the Data Label you want to change.
Step 2 − Click on Change Data Label Shapes.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 3 − Choose the shape you want.

Resize a Data Label

Step 1 − Click on the data label.
Step 2 − Drag it to the size you want. Alternatively, you can click on Size &
Properties icon in the Format Data Labels task pane and then choose the size
options.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Add a Field to a Data Label

Excel 2013 has a powerful feature of adding a cell reference with explanatory
text or a calculated value to a data label. Let us see how to add a field to the
data label.
Step 1 − Place the Explanatory text in a cell.
Step 2 − Right-click on a data label. A list of options will appear.

Step 3 − Click on the option − Insert Data Label Field.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 4 − From the available options, Click on Choose Cell. A Data Label
Reference window appears.

Step 5 − Select the Cell Reference where the Explanatory Text is written and
then click OK. The explanatory text appears in the data label.
Step 6 − Resize the data label to view the entire text.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

A Leader Line is a line that connects a data label and its associated data point.
It is helpful when you have placed a data label away from a data point.
In earlier versions of Excel, only the pie charts had this functionality. Now, all the
chart types with data label have this feature.

Add a Leader Line

Step 1 − Click on the data label.
Step 2 − Drag it after you see the four-headed arrow.

Step 3 − Move the data label. The Leader Line automatically adjusts and
follows it.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Format Leader Lines

Step 1 − Right-click on the Leader Line you want to format.

Step 2 − Click on Format Leader Lines. The Format Leader Lines task pane
appears. Now you can format the leader lines as you require.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 3 − Click on the icon Fill & Line.

Step 4 − Click on LINE.
Step 5 − Make the changes that you want. The leader lines will be formatted as
per your choices.

New Functions in Excel 2013

Date and Time Functions
● DAYS − Returns the number of days between two dates.
● ISOWEEKNUM − Returns the number of the ISO week number of the year for a
given date.
Engineering Functions
● BITAND − Returns a 'Bitwise And' of two numbers.
● BITLSHIFT − Returns a value number shifted left by shift_amount bits.
● BITOR − Returns a bitwise OR of 2 numbers.
● BITRSHIFT − Returns a value number shifted right by shift_amount bits.
● BITXOR − Returns a bitwise 'Exclusive Or' of two numbers.
● IMCOSH − Returns the hyperbolic cosine of a complex number.
● IMCOT − Returns the cotangent of a complex number.
● IMCSC − Returns the cosecant of a complex number.
● IMCSCH − Returns the hyperbolic cosecant of a complex number.
● IMSEC − Returns the secant of a complex number.
● IMSECH − Returns the hyperbolic secant of a complex number.
● IMSIN − Returns the sine of a complex number.
● IMSINH − Returns the hyperbolic sine of a complex number.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● IMTAN − Returns the tangent of a complex number.

Financial Functions
● PDURATION − Returns the number of periods required by an investment to reach a
specified value.
● RRI − Returns an equivalent interest rate for the growth of an investment.

Information Functions
● ISFORMULA − Returns TRUE if there is a reference to a cell that contains a
formula.
● SHEET − Returns the sheet number of the referenced sheet.
● SHEETS − Returns the number of sheets in a reference.

Logical Functions
● IFNA − Returns the value you specify if the expression resolves to #N/A, otherwise
returns the result of the expression.
● XOR − Returns a logical exclusive OR of all arguments.

Lookup and Reference Functions

● FORMULATEXT − Returns the formula at the given reference as text.
● GETPIVOTDATA − Returns data stored in a PivotTable report.

Math and Trigonometry Functions

● ACOT − Returns the arccotangent of a number.
● ACOTH − Returns the hyperbolic arccotangent of a number.
● BASE − Converts a number into a text representation with the given radix (base).
● CEILING.MATH − Rounds a number up, to the nearest integer or to the nearest
multiple of significance.
● COMBINA − Returns the number of combinations with repetitions for a given
number of items.
● COT − Returns the cotangent of an angle.
● COTH − Returns the hyperbolic cotangent of a number.
● CSC − Returns the cosecant of an angle.
● CSCH − Returns the hyperbolic cosecant of an angle.
● DECIMAL − Converts a text representation of a number in a given base into a
decimal number.
● FLOOR.MATH − Rounds a number down, to the nearest integer or to the nearest
multiple of significance.
● ISO.CEILING − Returns a number that is rounded up to the nearest integer or to the
nearest multiple of significance.
● MUNIT − Returns the unit matrix or the specified dimension.
● SEC − Returns the secant of an angle.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● SECH − Returns the hyperbolic secant of an angle.

Statistical Functions
● BINOM.DIST.RANGE − Returns the probability of a trial result using a binomial
distribution.
● GAMMA − Returns the Gamma function value.
● GAUSS − Returns 0.5 less than the standard normal cumulative distribution.
● PERMUTATIONA − Returns the number of permutations for a given number of
objects (with repetitions) that can be selected from the total objects.
● PHI − Returns the value of the density function for a standard normal distribution.
● SKEW.P − Returns the skewness of a distribution based on a population: a
characterization of the degree of asymmetry of a distribution around its mean.
Text Functions
● DBCS − Changes half-width (single-byte) English letters or katakana within a
character string to full-width (double-byte) characters.
● NUMBERVALUE − Converts text to number in a locale-independent manner.
● UNICHAR − Returns the Unicode character that is references by the given numeric
value.
● UNICODE − Returns the number (code point) that corresponds to the first character
of the text.

User Defined Functions in Add-ins

The Add-ins that you install contain Functions. These add-in or automation
functions will be available in the User Defined category in the Insert
Function dialog box.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● CALL − Calls a procedure in a dynamic link library or code resource.

● EUROCONVERT − Converts a number to euros, converts a number from euros to a
euro member currency, or converts a number from one euro member currency to
another by using the euro as an intermediary (triangulation).
● REGISTER.ID − Returns the register ID of the specified dynamic link library (DLL) or
code resource that has been previously registered.
● SQL.REQUEST − Connects with an external data source and runs a query from a
worksheet, then returns the result as an array without the need for macro
programming.

Web Functions
The following web functions are introduced in Excel 2013.
● ENCODEURL − Returns a URL-encoded string.
● FILTERXML − Returns specific data from the XML content by using the specified
XPath.
● WEBSERVICE − Returns the data from a web service.

● Flash Fill helps you to separate first and last names or part names and
numbers, or any other data into separate columns.
● Step 1 − Consider a data column containing full names.
●

● Step 2 − Enter the first name in the column next to your data and press
Enter.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● Step 3 − Start typing the next name. Flash Fill will show you a list of
suggested names.
●

● Step 4 − Press Enter to accept the list.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● Step 5 − Enter a last name in the next column, and press Enter.
●

● Step 6 − Start typing the next name and press Enter. The column will be
filled with the relevant last names.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● Step 7 − If the names have middle names also, you can still use Flash
Fill to separate the data out into three columns by repeating it three
times.

● Flash Fill works with any data you need to split into more than one
column, or you can simply use it to fill out data based on an
example. Flash Fill typically starts working when it recognizes a pattern in
your data.
Excel 2013 has a new feature Recommended PivotTables under
the Insert tab. This command helps you to create PivotTables automatically.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 1 − Your data should have column headers. If you have data in the form of
a table, the table should have Table Header. Make sure of the Headers.
Step 2 − There should not be blank rows in the Data. Make sure No Rows are
blank.
Step 3 − Click on the Table.
Step 4 − Click on Insert tab.
Step 5 − Click on Recommended PivotTables. The Recommended
PivotTables dialog box appears.
Step 6 − Click on a PivotTable Layout that is recommended. A preview of that
pivot table appears on the right–side.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 7 − Double-click on the PivotTable that shows the data the way you want
and Click OK. The PivotTable is created automatically for you on a new
worksheet.

Create a PivotTable to analyze external data

Create a PivotTable by using an existing external data connection.
Step 1 − Click any cell in the Table.
Step 2 − Click on the Insert tab.
Step 3 − Click on the PivotTable button. A Create PivotTable dialog box
appears.

Step 4 − Click on the option Use an external data source. The button below
that, ‘Choose Connection’ gets enabled.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 5 − Select the Choose Connection option. A window appears showing all
the Existing Connections.

Step 6 − In the Show Box, select All Connections. All the available data
connections can be used to obtain the data for analysis.

The option Connections in this Workbook option in the Show Box is to reuse
or share an existing connection.

Connect to a new external data source

You can create a new external data connection to the SQL Server and import
the data into Excel as a table or PivotTable.
Step 1 − Click on the Data tab.
Step 2 − Click on the From Other Sources button, in the Get External Data
Group.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

The options of External Data Sources appear as shown in the image below.

Step 3 − Click the option From SQL Server to create a connection to an SQL
Server table.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

A Data Connection Wizard dialog box appears.

Step 4 − Establish the connection in three steps given below.

● Enter the database server and specify how you want to log on to the server.
● Enter the database, table, or query that contains the data you want.
● Enter the connection file you want to create.

Using the Field List option

In Excel 2013, it is possible to arrange the fields in a PivotTable.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 1 − Select the data table.

Step 2 − Click the Insert Tab.
Step 3 − Click on the PivotTable button. The Create PivotTable dialog box
opens.
Step 4 − Fill the data and then click OK. The PivotTable appears on a New
Worksheet.

Step 5 − Choose the PivotTable Fields from the field list. The fields are added
to the default areas.
The Default areas of the Field List are −
● Nonnumeric fields are added to the Rows area
● Numeric fields are added to the Values area, and

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● Time hierarchies are added to the Columns area

You can rearrange the fields in the PivotTable by dragging the fields in the
areas.
Step 6 − Drag Region Field from Rows area to Filters area. The Filters
area fields are shown as top-level report filters above the PivotTable.

Step 7 − The Rows area fields are shown as Row Labels on the left side of the
PivotTable.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

The order in which the Fields are placed in the Rows area, defines
the hierarchy of the Row Fields. Depending on the hierarchy of the fields, rows
will be nested inside rows that are higher in position.
In the PivotTable above, Month Field Rows are nested inside Salesperson
Field Rows. This is because in the Rows area, the field Salesperson appears
first and the field Month appears next, defining the hierarchy.
Step 8 − Drag the field - Month to the first position in the Rows area. You have
changed the hierarchy, putting Month in the highest position. Now, in the
PivotTable, the field - Salesperson will nest under Month fields.

In a similar way, you can drag Fields in the Columns area also. The Columns
area fields are shown as Column Labels at the top of the PivotTable.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

PivotTables based on Multiple Tables

In Excel 2013, it is possible to create a PivotTable from multiple tables. In this
example, the table ‘Sales’ is on one worksheet and table - ‘Products’ is on
another worksheet.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 1 − Select the Sales sheet from the worksheet tabs.

Step 2 − Click the Insert tab.
Step 3 − Click on the PivotTable button on the ribbon. The Create
PivotTable dialog box,
Step 4 − Select the sales table.
Step 5 − Under “choose whether you want to analyze multiple tables”, Click Add
this Data to the Data Model.
Step 6 − Click OK.

Under the PivotTable Fields, you will see the options, ACTIVE and ALL.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 7 − Click on ALL. You will see both the tables and the fields in both the
tables.
Step 8 − Select the fields to add to the PivotTable. You will see a
message, “Relationships between tables may be needed”.

Step 9 − Click on the CREATE button. After a few steps for creation of
Relationship, the selected fields from the two tables are added to the PivotTable.

Data Model is used for building a model where data from various sources can
be combined by creating relationships among the data sources. A Data Model
integrates the tables, enabling extensive analysis using PivotTables, Power
Pivot, and Power View.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

A Data Model is created automatically when you import two or more tables
simultaneously from a database. The existing database relationships between
those tables is used to create the Data Model in Excel.
Step 1 − Open a new blank Workbook in Excel.
Step 2 − Click on the DATA tab.
Step 3 − In the Get External Data group, click on the option From Access.
The Select Data Source dialog box opens.
Step 4 − Select Events.accdb, Events Access Database file.

Step 5 − The Select Table window, displaying all the tables found in the
database, appears.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 6 − Tables in a database are similar to the tables in Excel. Check

the ‘Enable selection of multiple tables’ box, and select all the tables. Then
click OK.

Step 7 − The Import Data window appears. Select the PivotTable

Report option. This option imports the tables into Excel and prepares a
PivotTable for analyzing the imported tables. Notice that the checkbox at the
bottom of the window - ‘Add this data to the Data Model’ is selected and
disabled.

Step 8 − The data is imported, and a PivotTable is created using the imported
tables.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

You have imported the data into Excel and the Data Model is created
automatically. Now, you can explore data in the five tables, which have
relationships defined among them.

Explore Data Using PivotTable

Step 1 − You know how to add fields to PivotTable and drag fields across areas.
Even if you are not sure of the final report that you want, you can play with the
data and choose the best-suited report.
In PivotTable Fields, click on the arrow beside the table - Medals to expand it
to show the fields in that table. Drag the NOC_CountryRegion field in
the Medals table to the COLUMNS area.
Step 2 − Drag Discipline from the Disciplines table to the ROWS area.
Step 3 − Filter Discipline to display only five sports: Archery, Diving, Fencing,
Figure Skating, and Speed Skating. This can be done either in PivotTable
Fields area, or from the Row Labels filter in the PivotTable itself.
Step 4 − In PivotTable Fields, from the Medals table, drag Medal to
the VALUES area.
Step 5 − From the Medals table, select Medal again and drag it into
the FILTERS area.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 6 − Click the dropdown list button to the right of the Column labels.
Step 7 − Select Value Filters and then select Greater Than…
Step 8 − Click OK.

The Value Filters dialog box for the count of Medals is greater than appears.
Step 9 − Type 80 in the Right Field.
Step 10 − Click OK.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

The PivotTable displays only those regions, which has more than total 80
medals.

You could analyze your data from the different tables and arrive at the specific
report you want in just a few steps. This was possible because of the pre-
existing relationships among the tables in the source database. As you imported
all the tables from the database together at the same time, Excel recreated the
relationships in its Data Model.
If you do not import the tables at the same time, or if the data is from different
sources or if you add new tables to your Workbook, you have to create
the Relationships among the Tables by yourself.

Create Relationship between Tables

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Relationships let you analyze your collections of the data in Excel, and create
interesting and aesthetic reports from the data you import.
Step 1 − Insert a new Worksheet.
Step 2 − Create a new table with new data. Name the new table as Sports.

Step 3 − Now you can create relationship between this new table and the other
tables that already exist in the Data Model in Excel. Rename the Sheet1
as Medals and Sheet2 as Sports.
On the Medals sheet, in the PivotTable Fields List, click All. A complete list of
available tables will be displayed. The newly added table - Sports will also be
displayed.

Step 4 − Click on Sports. In the expanded list of fields, select Sports. Excel
messages you to create a relationship between tables.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 5 − Click on CREATE. The Create Relationship dialog box opens.

Step 6 − To create the relationship, one of the tables must have a column of
unique, non-repeated, values. In the Disciplines table, SportID column has
such values. The table Sports that we have created also has
the SportID column. In Table, select Disciplines.
Step 7 − In Column (Foreign), select SportID.
Step 8 − In Related Table, select Sports.
Step 9 − In Related Column (Primary), SportID gets selected automatically.
Click OK.
Step 10 − The PivotTable is modified to reflect the addition of the new Data
Field Sport. Adjust the order of the fields in the Rows area to maintain

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

the Hierarchy. In this case, Sport should be first and Discipline should be the
next, as Discipline will be nested in Sport as a sub-category.

PowerPivot is an easy to use data analysis tool that can be used from within
Excel. You can use PowerPivot to access and mashup data from virtually any
source. You can create your own compelling reports and analytical applications,
easily share insights, and collaborate with colleagues through Microsoft Excel
and SharePoint.
Using PowerPivot, you can import data, create relationships, create calculated
columns and measures, and add PivotTables, slicers and Pivot Charts.

Step 1 − You can use Diagram View in PowerPivot to create a relationship. To

start, get some more data into your workbook. You can copy and paste data
from a Web Page also. Insert a new Worksheet.
Step 2 − Copy data from the web page and paste it on the Worksheet.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 3 − Create a table with the data. Name the table Hosts and rename the
Worksheet Hosts.

Step 4 − Click on the Worksheet Hosts. Click the POWERPIVOT tab on the
Ribbon.
Step 5 − In the Tables group, click on Add to Data Model.

Hosts Table gets added to the Data Model in the Workbook.

The PowerPivot window opens.
You will find all the Tables in the Data Model in the PowerPivot, though some of
them are not present in the Worksheets in the Workbook.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 6 − In PowerPivot window, in View group, click on Diagram View.

Step 7 − Use the slide bar to resize the diagram so that you can see all tables in
the diagram.

Step 8 − Rearrange the tables by dragging their title bar, so that they are visible
and positioned next to one another.
Four tables Hosts, Events, W_Teams, and S_Teams are unrelated to the rest
of the tables −

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 9 − Both, the Medals table and the Events table have a field
called DisciplineEvent. Also, DisciplineEvent column in the Events table
consists of unique, non-repeated values. Click on Data View in Views Group.
Check DisciplineEvent column in the Events table.

Step 10 − Once again, click on Diagram View. Click on the field Discipline
Event in the Events table and drag it to the field DisciplineEvent in the Medals
Table. A line appears between the Events Table and the Medals Table,
indicating a relationship has been established.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 11 − Click on the line. The line and the fields defining the relationship
between the two tables are highlighted as shown in the image given below.

Data Model using Calculated Columns

Hosts table is still not connected to any of the other Tables. To do so, a field with
values that uniquely identify each row in the Hosts table is to be found first.
Then, search the Data Model to see if that same data exists in another table.
This can be done in Data View.
Step 1 − Shift to Data View. There are two ways of doing this.
● Click on Data View in the View group.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● Click on the Grid button on Task Bar.

The Data View appears.

Step 2 − Click on the Hosts table.
Step 3 − Check the data in Hosts Table to see if there is a field with unique
values.
There is no such field in Hosts Table. You cannot edit or delete existing data
using PowerPivot. However, you can create new columns by using calculated
fields based on the existing data. In PowerPivot, you can use Data Analysis
Expressions (DAX) to create calculations.
Adjacent to the existing columns is an empty column titled Add Column.
PowerPivot provides that column as a placeholder.

Step 4 − In the formula bar, type the DAX formula −

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

= CONCATENATE([Edition],[Season])
Press Enter. The Add Column is filled with values. Check the values to verify
that they are unique across the rows.

Step 5 − The newly created column with created values is

named CreatedColumn1. To change the name of the column, select the
column, right-click on it.
Step 6 − Click on the option Rename Column.

Step 7 − Rename the column as EditionID.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 8 − Now, Select the Medals Table.

Step 9 − Select Add Column.
Step 10 − In the Formula Bar, type the DAX Formula,
= YEAR ([EDITION])
and press Enter.
Step 11 − Rename the Column as Year.

Step 12 − Select Add Column.

Step 13 − Type in the Formula Bar,
= CONCATENATE ([Year], [Season])

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

A new column with values similar to those in the EditionID column in Hosts
Table gets created.
Step 14 − Rename the column as EditionID.
Step 15 − Sort the Column in Ascending Order.

Relationship using calculated columns

Step 1− Switch to Diagram View. Ensure that the
tables Medals and Hosts are close to each other.

Step 2 − Drag the EditionID column in Medals to the EditionID column

in Hosts.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

PowerPivot creates a relationship between the two tables. A line between the
two tables, indicates the relationship. The EditionID Field in both the tables is
highlighted indicating that the relationship is based on the column EditionID.

Once you connect your Excel workbook to an external data source, such as a
SQL Server database, Access database or another Excel workbook, you can
keep the data in your workbook up to date by "refreshing" the link to its source.
Each time you refresh the connection, you see the most recent data, including
anything that is new or has been deleted.
Let us see how to refresh PowerPivot data.
Step 1 − Switch to the Data View.
Step 2 − Click on Refresh.
Step 3 − Click on Refresh All.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

The Data Refresh window appears showing all the Data Tables in the Data
Model and tracking the refreshing progress. After the refresh is complete, the
status is displayed.

Step 4 − Click on Close. The data in your Data Model is updated.

Update the Data Connections

Step 1 − Click any cell in the table that contains the link to the imported data file.
Step 2 − Click on the Data tab.
Step 3 − Click on Refresh All in Connections group.
Step 4 − In the drop-down list, click on Refresh All. All the data connections in
the Workbook will be updated.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Automatically Refresh Data

Here we will learn how to refresh the data automatically when the workbook is
opened.
Step 1 − Click any cell in the table that contains the link to the imported Data
file.
Step 2 − Click on the Data tab.
Step 3 − Click on Connections in the Connections group. The Workbook
Connections window appears.

Step 4 − Click on Properties. The Connection Properties Window appears.

Step 5 − You will find a Usage tab and a Definition tab. Click on the Usage tab.
The options for Refresh Control appear.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 6 − Select Refresh data while opening the file.

You also have an option under this: ‘Remove data from the external data
range before saving the workbook’. You can use this option to save the
workbook with the query definition but without the external data.
Step 7 − Click OK.
Whenever you open your Workbook, the up-to-date data will be loaded into your
Workbook.

Automatically refresh data at regular intervals

Step 1 − Click any cell in the table that contains the link to the imported Data
file.
Step 2 − Click on the Data tab.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 3 − Click on the Connections option in Connections group. A Workbook

Connections window appears.
Step 4 − Click on Properties. A Connection Properties Window appears.
Step 5 − Click on the Usage tab. The options for Refresh Control appear.
Step 6 − Now, select “Refresh every” and enter 60 minutes between each
refresh operation.
Step 7 − Click OK. Your data will be refreshed every 60 minute that is every
hour.

Enable Background Refresh

For very large data sets, consider running a background refresh. This returns the
control of Excel to you instead of making you wait several minutes for
the refresh to finish. You can use this option when you are running a query in
the background. However, you cannot run a query for any connection type that
retrieves data for the Data Model.
Step 1 − Click any cell in the table that contains the link to the imported Data
file.
Step 2 − Click on the Data tab.
Step 3 − Click on Connections in the Connections group. The Workbook
Connections window appears.
Step 4 − Click on Properties. Connection Properties Window appears.
Step 5 − Click on the Usage tab. The Refresh Control options appear.
Step 6 − Click on Enable background refresh and then click OK.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Power View is a feature of Microsoft Excel 2013 that enables interactive data
exploration, visualization, and presentation encouraging intuitive ad-hoc
reporting.

Create a Power View Sheet

Make sure Power View add-in is enabled in Excel 2013.
Step 1 − Click on the File menu and then Click on Options.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

The Excel Options window appears.

Step 2 − Click on Add-Ins.
Step 3 − In the Manage box, click the drop-down arrow and select Excel Add-
ins.
Step 4 − All the available Add-ins will be displayed. If Power View Add-in is
enabled, it appears in Active Application Add-ins.

If it does not appear, follow these steps −

Step 1 − In the Excel Options Window, Click on Add-Ins.
Step 2 − In the Manage box, click the drop-down arrow and select COM Add-
ins

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 3 − Click on the Go button. A COM Add-Ins Dialog Box appears.

Step 4 − Check the Power View Check Box.
Step 5 − Click OK.

Now, you are ready to create the Power View sheet.

Step 1 − Click on the Data Table.
Step 2 − Click on Insert tab.
Step 3 − Click on Power View in Reports Group.

An Opening Power View window opens, showing the progress of Working on

opening Power View sheet.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

The Power View sheet is created for you and added to your Workbook with
the Power View. On the Right-side of the Power View, you find the Power
View Fields. Under the Power View Fields you will find Areas.
In the Ribbon, if you click on Design tab, you will find
various Visualization options.

Financial Functions in Excel

#1 – Future Value (FV)

#2 – FVSCHEDULE

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

#3 – Present Value (PV)

#4 – Net Present Value (NPV)

#5 – XNPV

#6 – PMT

#7 – PPMT

#8 – Internal Rate of Return (IRR)

#9 – Modified Internal Rate of Return (MIRR)

#10 – XIRR

#11 – NPER

#12 – RATE

#13 – EFFECT

#14 – NOMINAL

#15 – SLN

1. Future Value (FV):

If you want to find out the future value of a particular investment which has a
constant interest rate and periodic payment, use the following formula –

FV (Rate, Nper, [Pmt], PV, [Type])

Rate = It is the interest rate/period

Nper = Number of periods

[Pmt] = Payment/period

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

PV = Present Value

[Type] = When the payment is made (if nothing is mentioned, it’s

assumed that the payment has been made at the end of the period)

FV Example

A has invested the US $100 in 2016. The payment has been made yearly. The interest
rate is 10% p.a. What would be the FV in 2019?

Solution: In excel, we will put the equation as follows –

= FV (10%, 3, 1, – 100)

= US $129.79

2. FVSCHEDULE: Financial Function in Excel

This financial function is important when you need to calculate the future value with
the variable interest rate. Have a look at the function below –

FVSCHEDULE = (Principal, Schedule)

Principal = Principal is the present value of a particular investment

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Schedule = A series of interest rate put together (in case of excel, we will use different
boxes and select the range)

FVSCHEDULE Example:

M has invested US $100 at the end of 2016. It is expected that the interest rate will
change every year. In 2017, 2018 & 2019, the interest rates would be 4%, 6% & 5%
respectively. What would be the FV in 2019?

Solution: In excel, we will do the following –

= FVSCHEDULE (C1, C2: C4)

= US $115.752

3.Present Value (PV): Financial Function in Excel

The Excel PV function is a financial function that returns the present value of an
investment. You can use the PV function to get the value in today's dollars of a series of
future payments, assuming periodic, constant payments and a constant interest rate. Get
the present value of an investment.

PV = (Rate, Nper, [Pmt], FV, [Type])

● Rate = It is the interest rate/period

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

● Nper = Number of periods

● [Pmt] = Payment/period
● FV = Future Value
● [Type] = When the payment is made (if nothing is mentioned, it’s assumed that
the payment has been made at the end of the period)

PV Example:
The future value of an investment is US $100 in 2019. The payment has been made
yearly. The interest rate is 10% p.a. What would be the PV as of now?

Solution: In excel, we will put the equation as follows –

= PV (10%, 3, 1, – 100)

= US $72.64

4. Net Present Value (NPV):

Net Present Value is the sum total of positive and negative cash flows over the years.
Here’s how we will represent it in excel –
Negative Cash Flow Meaning
Negative cash flow means a business scenario when the firm spends more cash than it
generates.
positive cash flow.
positive cash flow. Normal situation where the cash inflows during a period are higher
than the cash outflows during the same period. Positive cash flow does not necessarily

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

means profit, and is usually due to a careful management of cash inflows and
expenditure.
Here’s how we will represent it in excel –
● NPV = (Rate, Value 1, [Value 2], [Value 3]…)
● Rate = Discount rate for a period
● Value 1, [Value 2], [Value 3]… = Positive or negative cash flows
Here, negative values would be considered as payments and positive values would be
treated as inflows.
NPV Example
Here is a series of data from which we need to find NPV –
Details In US $
Details In US $ Dates
Rate of Discount 5%
Initial Investment -1000 1st December 2011
Return from 1st year 300 1st January 2012
Return from 2nd year 400 1st February 2013
Return from 3rd year 400 1st March 2014
Return from 4th year 300 1st April 2015

Find out the NPV.

Solution: In Excel, we will do the following –

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

=NPV (5%, B4:B7) + B3

= US $240.87
5. XNPV: Financial Function in Excel
This financial function is similar as the NPV with a twist. Here the payment and income
are not periodic. Rather specific dates are mentioned for each payment and income.
Here’s how we will calculate it –
XNPV = (Rate, Values, Dates)

● Rate = Discount rate for a period

● Values = Positive or negative cash flows (an array of values)
● Dates = Specific dates (an array of dates)
● XNPV Example
Here is a series of data from which we need to find NPV –

Details In US $ Dates
Rate of Discount 5%
Initial Investment -1000 1st December 2011
Return from 1st year 300 1st January 2012
Return from 2nd year 400 1st February 2013
Return from 3rd year 400 1st March 2014
Return from 4th year 300 1st April 2015
Solution: In excel, we will do as follows –

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

=XNPV (5%, B2:B6, C2:C6)

= US$289.90
5.PMT: Financial Function in Excel
In excel, PMT denotes the periodical payment required to pay off for a particular period
of time with a constant interest rate. Let’s have a look at how to calculate it in excel –
PMT = (Rate, Nper, PV, [FV], [Type])

● Rate = It is the interest rate/period

● Nper = Number of periods
● PV = Present Value
● [FV] = An optional argument which is about the future value of a loan (if nothing
is mentioned, FV is considered as “0”)
● [Type] = When the payment is made (if nothing is mentioned, it’s assumed that
the payment has been made at the end of the period
PMT Example
US $1000 needs to be paid in full in 3 years. The interest rate is 10% p.a. and the
payment needs to be done yearly. Find out the PMT.
Solution: In excel, we will calculate it in the following manner –

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

= PMT (10%, 3, 1000)

= – 402.11

Calculating a Monthly Mortgage Payment in Excel

Let's say you are shopping for a mortgage and want to know what your monthly
payment would be. To calculate, all you need are three data points:

● Interest rate
● Length of loan
● The amount borrowed

We've inserted example data points below. Here, we are calculating the monthly
payment for a 30-year mortgage of $250,000 at an interest rate of 5.0%:

6. PPMT:
It is another version of PMT. The only difference is this – PPMT calculates
payment on principal with a constant interest rate and constant periodic payments.
Here’s how to calculate PPMT
PPMT = (Rate, Per, Nper, PV, [FV], [Type])

● Rate = It is the interest rate/period

● Per = The period for which the principal is to be calculated
● Nper = Number of periods
● PV = Present Value
● [FV] = An optional argument which is about the future value of a loan (if nothing
is mentioned, FV is considered as “0”)

● [Type] = When the payment is made (if nothing is mentioned, it’s assumed that
the payment has been made at the end of the period)

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Calculating a Monthly Mortgage Payment in Excel

Let's say you are shopping for a mortgage and want to know what your monthly
payment would be. To calculate, all you need are three data points:

● Interest rate
● Length of loan
● The amount borrowed

We've inserted example data points below. Here, we are calculating the monthly
payment for a 30-year mortgage of $250,000 at an interest rate of 5.0%:

To calculate our payment, we enter this data into the appropriate field in the PMT
function. Note that because our mortgage is based on monthly payments, we will divide
the interest rate by 12 (to give us the monthly interest rate) and multiply the number of
payments by 12 (to give us the total number of payments):

As you can see below, the monthly principal and interest payment for this mortgage
comes out to $1,342.05. This is shown in Excel as a negative figure because it represents

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

monthly money being spent. If you wanted to show it as a positive figure, you can enter
a negative sign in front of the amount borrowed.

https://investinganswers.com/articles/how-calculate-monthly-loan-payment-excel
PPMT Example
US $1000 needs to be paid in full in 3 years. The interest rate is 10% p.a. and the
payment needs to be done yearly. Find out the PPMT in the first year and second year.
Solution: In excel, we will calculate it in the following manner –
1st year,

=PPMT (10%, 1, 3, 1000)

= US $-302.11
2nd year,

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

=PPMT (10%, 2, 3, 1000)

= US $-332.33
8. Internal Rate of Return (IRR):
IRR is defined as the discount rate that sets the NPV of a project to zero is the project’s
IRR.
To understand whether any new project or investment is profitable or not, the firm uses
IRR. If IRR is more than the hurdle rate (acceptable rate/ average cost of capital), then
it’s profitable for the firm and vice-versa. Let’s have a look, how we find out IRR in excel
–
IRR = (Values, [Guess])
● Values = Positive or negative cash flows (an array of values)
● [Guess] = An assumption of what you think IRR should be
IRR Example
Here is a series of data from which we need to find IRR –

Details in US $

Initial Investment -1000

Return from 1st year 300
Return from 2nd year 400
Return from 3rd year 400
Return from 4th year 300

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Find out IRR.

Solution: Here’s how we will calculate IRR in excel –

= IRR (A2:A6, 0.1)

= 15%
9.Modified Internal Rate of Return (MIRR):
Modified Internal Rate of Return is one step ahead of the Internal Rate of Return. MIRR
signifies that the investment is profitable and is used in business. MIRR is calculated by
assuming NPV as zero. Here’s how to calculate MIRR in excel –
MIRR = (Values, Finance rate, Reinvestment rate)

● Values = Positive or negative cash flows (an array of values)

● Finance rate = Interest rate paid for the money used in cash flows
● Reinvestment rate = Interest rate paid for reinvestment of cash flows

MIRR Example
Here is a series of data from which we need to find MIRR –

Details In US $
Initial Investment -1000
Return from 1st year 300
Return from 2nd year 400

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Return from 3rd year 400

Return from 4th year 300
Finance rate = 12%; Reinvestment rate = 10%. Find out IRR.
Solution: Let’s look at the calculation of MIRR –
= MIRR (B2:B6, 12%, 10%)
= 13%

10. XIRR: The Microsoft Excel XIRR function returns the internal rate of return for a
series of cash flows that may not be periodic. ... It can be used as a worksheet function
(WS) in Excel. As a worksheet function, the XIRR function can be entered as part of a
formula in a cell of a worksheet.
XIRR = (Values, Dates, [Guess])

Values = Positive or negative cash flows (an array of values)

Dates = Specific dates (an array of dates)
[Guess] = An assumption of what you think IRR should be
XIRR Example
Here is a series of data from which we need to find XIRR –

Details In US $ Dates
Initial Investment -1000 1st December 2011

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Return from 1st year 300 1st January 2012

Return from 2nd year 400 1st February 2013
Return from 3rd year 400 1st March 2014
Return from 4th year 300 1st April 2015
Solution: Let’s have a look at the solution –

= XIRR (B2:B6, C2:C6, 0.1)

= 24%
11.NPER: The Excel NPER function is a financial function that returns the number of
periods for loan or investment. You can use the NPER function to get the number of
payment periods for a loan, given the amount, the interest rate, and periodic payment
amount. Get number of periods for loan or investment.
NPER = (Rate, PMT, PV, [FV], [Type])

● Rate = It is the interest rate/period

● PMT = Amount paid per period
● PV = Present Value
● [FV] = An optional argument which is about the future value of a loan (if nothing
is mentioned, FV is considered as “0”)
● [Type] = When the payment is made (if nothing is mentioned, it’s assumed that
the payment has been made at the end of the period)
NPER Example

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

US $200 is paid per year for a loan of US $1000. The interest rate is 10% p.a. and the
payment needs to be done yearly. Find out the NPER.
Solution: We need to calculate NPER in the following manner –

= NPER (10%, -200, 1000)

= 7.27 years
12. RATE:T he Excel RATE function is a financial function that returns the interest rate
per period of an annuity. You can use RATE to calculate the periodic interest rate,
then multiply as required to derive the annual interest rate. The RATE function
calculates by iteration.
● RATE = (NPER, PMT, PV, [FV], [Type], [Guess])

● Nper = Number of periods

● PMT = Amount paid per period
● PV = Present Value
● [FV] = An optional argument which is about the future value of a loan (if
nothing is mentioned, FV is considered as “0”)
● [Type] = When the payment is made (if nothing is mentioned, it’s
assumed that the payment has been made at the end of the period)
● [Guess] = An assumption of what you think RATE should be
RATE Example
US $200 is paid per year for a loan of US $1000 for 6 years and the payment needs to be
done yearly. Find out the RATE.
Solution:
= RATE (6, -200, 1000, 0.1)
= 5%

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

13. EFFECT: The Excel EFFECT Function returns the effective annual interest rate, from
the nominal annual interest rate (APR), and the number of compounding periods
per year.
EFFECT = (Nominal_Rate, NPERY)

● Nominal_Rate = Nominal Interest Rate

● NPERY = Number of compounding per year
EFFECT Example
Payment needs to be paid with a nominal interest rate of 12% when the number of
compounding per year is 12.
Solution:

= EFFECT (12%, 12)

= 12.68%
14. NOMINAL: Returns the normal distribution for the specified mean and standard
deviation. This function has a very wide range of applications in statistics, including
hypothesis testing.
NOMINAL = (Effect_Rate, NPERY)

● Effect_Rate = Effective annual interest rate

● NPERY = Number of compounding per year
NOMINAL Example
Payment needs to be paid with an effective interest rate or annual equivalent rate of
12% when the number of compounding per year is 12.
Solution:

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

= NOMINAL (12%, 12)

= 11.39%
15.SLN: The Microsoft Excel SLN function returns the depreciation of an asset for a period
based on the straight-line depreciation method. ... It can be used as a worksheet function
(WS) and a VBA function (VBA) in Excel. As a worksheet function, the SLN function can be
entered as part of a formula in a cell of a worksheet.

SLN = (Cost, Salvage, Life)

● Cost = Cost of asset when bought (initial amount)

● Salvage = Value of asset after depreciation
● Life = Number of periods over which the asset is being depreciated

SLN Example

The initial cost of machinery is the US $5000. It has been depreciated in the Straight Line
Method. The machinery was used for 10 years and now the salvage value of machinery is the
US $300. Find depreciation charged per year.

= SLN (5000, 300, 10)

= US $470 per year

INFORMATION FUNCTION
https://www.excelfunctions.net/excel-information-functions.html

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Function Description

CELL function Returns information about the formatting,

location, or contents of a cell

Note: This function is not available in Excel for

the web.

ERROR.TYPE Returns a number corresponding to an

function error type

INFO function Returns information about the current

operating environment

Note: This function is not available in Excel for

the web.

ISBLANK Returns TRUE if the value is blank

function

ISERR function Returns TRUE if the value is any error

value except #N/A

ISERROR Returns TRUE if the value is any error

function value

ISEVEN Returns TRUE if the number is even

function

ISFORMULA Returns TRUE if there is a reference to a

function cell that contains a formula

ISLOGICAL Returns TRUE if the value is a logical value

function

ISNA function Returns TRUE if the value is the #N/A error

value

ISNONTEXT Returns TRUE if the value is not text

function

ISNUMBER Returns TRUE if the value is a number

function

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Function Description

ISODD function Returns TRUE if the number is odd

ISREF function Returns TRUE if the value is a reference

ISTEXT Returns TRUE if the value is text

function

N function Returns a value converted to a number

CELL function
The Excel CELL function returns information about a given cell. This can be information
in relation to the contents, formatting or location of the cell.
The syntax of the function is:
CELL( info_type, reference )

info_type A text value that specifies what type

of cell information you want to
return. The following list shows the
Required possible values of the Info_type
argument and the corresponding
results.

reference The cell that you want information

about. If omitted, the information
specified in the info_type argument
Optional is returned for the last cell that was
changed. If the reference argument
is a range of cells, the CELL function
returns the information for only the
upper left cell of the range.

Examples

Formulas: Results:

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

A B
- =CELL( "address", A1 A B
1 9,999 ) -
2 =CELL( "col", A1 ) 1 9,999 $A$1

3 =CELL( "color", A1 ) 2 1

=CELL( "contents", 3 1
4 A1 ) 4 -9999
=CELL( "filename", C:\
5 A1 ) [Book1.xlsx]Shee
6 =CELL( "format", A1 ) 5 t1

=CELL( "parentheses" 6 ,0-

7 , A1 ) 7 0
8 =CELL( "prefix", A1 ) 8
9 =CELL( "protect", A1 ) 9 1
1 1
0 =CELL( "row", A1 ) 0 1
1 1
1 =CELL( "type", A1 ) 1 v
1 1
2 =CELL( "width", A1 ) 2

SHEETS function
Excel for Office 365 Excel for Office 365 for Mac Excel for the
web Excel 2019 More...
This article describes the formula syntax and usage of the SHEETS
function in Microsoft Excel.
Description
Returns the number of sheets in a reference.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Syntax
SHEETS(reference)
The SHEETS function syntax has the following arguments.
● Reference Optional. Reference is a reference for which you
want to know the number of sheets it contains. If Reference is
omitted, SHEETS returns the number of sheets in the workbook
that contains the function.
Remarks
● SHEETS includes all worksheets (visible, hidden, or very
hidden) in addition to all other sheet types (macro, chart, or
dialog sheets).
● If reference is not a valid value, SHEETS returns the #REF!
error value.
● SHEETS is not available in the Object Model (OM) because the
Object Model already includes similar functionality.
Example
Copy the example data in the following table, and paste it in cell A1 of
a new Excel worksheet. For formulas to show results, select them,
press F2, and then press Enter. If you need to, you can adjust the
column widths to see all the data.
Formula Description Result

=SHEETS() Because there is 3

no Reference
argument
specified, the
total number of
sheets in the
workbook is
returned (3).

=SHEETS(My3DRef) Returns the 2

number of
sheets in a 3D

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Formula Description Result

reference with
the defined
name My3DRef,
which includes
Sheet2 and
Sheet3 (2).

Database Functions

1. DAVERAGE

Averages the values in a column of a list or database that match conditions you specify.

Description

The DAVERAGE function returns the average of the values in a column of a list or database that match
the conditions specified.

Syntax

DAVERAGE (database, field, criteria)

● Database: The range of cells that makes up the list or database. A database is a list of
related data in which rows of related information are records, and columns of data are fields.
The first row of the list contains labels for each column.
● Field:Indicates for which column the Average is calculated. Enter the column label enclosed
between double quotation marks, such as "Age" or "Yield," or a number (without quotation
marks) that represents the position of the column within the list: 1 for the first column, 2 for the
second column, and so on.
● Criteria: The range of cells that contains the conditions you specify. You can use any
range for the criteria argument, as long as it includes at least one column label and at least one
cell below the column label in which you specify a condition for the column

Example

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

2.DCOUNT

Counts the cells that contain numbers in a column of a list or database that match conditions you specify.

Syntax

DCOUNT (database, field, criteria)

Argument Description Required/

Optional

The range of cells that makes up the list or database.

database A database is a list of related data in which rows of Required

related information are records, and columns of data
are fields. The first row of the list contains labels for
each column.

Indicates which column is used in the function.

Enter the column label enclosed between double

quotation marks, such as "Age" or "Yield," or a
number (without quotation marks) that represents the
field position of the column within the list: 1 for the first Optional
column, 2 for the second column, and so on.

If this Argument (field) is omitted, this Function

(DCOUNT) counts all records in the database that
match the criteria.

criteria The range of cells that contains the conditions that Optional
you specify. You can use any range for the criteria
argument, as long as the argument includes at least

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

one column label and at least one cell below the

column label in which you specify a condition for the
column.

3. DGET

Returns a single value from a column of a list or database that matches conditions you specify.

Syntax

DGET (database, field, criteria)

Database: The range of cells that makes up the list or database. A database is a list of related data in
which rows of related information are records, and columns of data are fields. The first row of the list
contains labels for each column.

Field: Indicates which column is used in the function. Enter the column label enclosed between double
quotation marks, such as "Age" or "Yield," or a number (without quotation marks) that represents the
position of the column within the list: 1 for the first column, 2 for the second column, and so on.

Criteria: The range of cells that contains the conditions that you specify. You can use any range for the
criteria argument, as long as the argument includes at least one column label and at least one cell below
the column label in which you specify a condition for the column.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

DMAX
Returns the largest number in a column of a list or database that matches conditions you specify.

Syntax

DMAX (database, field, criteria)

DMIN

Returns the smallest number in a column of a list or database that matches

conditions you specify.

Syntax

DMIN (database, field, criteria)

ADVANCED EXCEL
G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

ADVANCED EXCEL
G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

ADVANCED EXCEL
G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

VBA
ADVANCED EXCEL
G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

HOW TO WRITE A SIMPLE MACRO IN A STEP BY STEP

Step 1 − First, enable 'Developer' menu in Excel 20XX. To do the same, click
File → Options.
Step 2 − Click ‘Customize the Ribbon’ tab and check 'Developer'. Click 'OK'.
Step 3 − The 'Developer' ribbon appears in the menu bar.
Step 4 − Click the 'Visual Basic' button to open the VBA Editor
Step 5 − Start scripting by adding a button. Click Insert → Select the
button.
Step 6 − Perform a right-click and choose 'properties'.
Step 7 − Edit the name and caption as shown in the following screenshot.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Step 8 − Now double-click the button and the sub-procedure outline will be
displayed as shown in the following screenshot.

Step 9 − Start coding by simply adding a message.

Private Sub say_helloworld_Click()

MsgBox "Hi"
End Sub
Step 10 − Click the button to execute the sub-procedure. The output of the
sub-procedure is shown in the following screenshot.

Modules
Modules is the area where the code is written. This is a new Workbook, hence
there aren't any Modules.

To insert a Module, navigate to Insert → Module. Once a module is inserted

'module1' is created.
Within the modules, we can write VBA code and the code is written within a
Procedure. A Procedure/Sub Procedure is a series of VBA statements
instructing what to do.
Procedure

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Procedures are a group of statements executed as a whole, which instructs

Excel how to perform a specific task. The task performed can be a very simple
or a very complicated task. However, it is a good practice to break down
complicated procedures into smaller ones.

The two main types of Procedures are Sub and Function.

Function
A function is a group of reusable code, which can be called anywhere in your
program. This eliminates the need of writing the same code over and over again.
This helps the programmers to divide a big program into a number of small and
manageable functions.

Apart from inbuilt Functions, VBA allows to write user-defined functions as well
and statements are written between Function and End Function.

Sub-Procedures
Sub-procedures work similar to functions. While sub procedures DO NOT
Return a value, functions may or may not return a value. Sub procedures CAN
be called without call keyword. Sub procedures are always enclosed within Sub
and End Sub statements.
MsgBox function
The MsgBox function displays a message box and waits for the user to click a
button and then an action is performed based on the button clicked by the user.

Syntax
MsgBox(prompt[,buttons][,title][,helpfile,context])
Parameter Description
Prompt − A Required Parameter. A String that is displayed as a message in the
dialog box. The maximum length of prompt is approximately 1024 characters. If
the message extends to more than a line, then the lines can be separated using
a carriage return character (Chr(13)) or a linefeed character (Chr(10)) between
each line.

Buttons − An Optional Parameter. A Numeric expression that specifies the type

of buttons to display, the icon style to use, the identity of the default button, and
the modality of the message box. If left blank, the default value for buttons is 0.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Title − An Optional Parameter. A String expression displayed in the title bar of

the dialog box. If the title is left blank, the application name is placed in the title
bar.

Helpfile − An Optional Parameter. A String expression that identifies the Help file
to use for providing context-sensitive help for the dialog box.

Context − An Optional Parameter. A Numeric expression that identifies the Help

context number assigned by the Help author to the appropriate Help topic. If
context is provided, helpfile must also be provided
Example
Function MessageBox_Demo()
'Message Box with just prompt message
MsgBox("Welcome")

'Message Box with title, yes no and cancel Butttons

int a = MsgBox("Do you like blue color?",3,"Choose options")
' Assume that you press No Button
msgbox ("The Value of a is " & a)
End Function
Input Box function
The InputBox function prompts the users to enter values. After entering
the values, if the user clicks the OK button or presses ENTER on the keyboard,
the InputBox function will return the text in the text box. If the user clicks the
Cancel button, the function will return an empty string ("").

Syntax
InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])
Parameter Description
Prompt − A required parameter. A String that is displayed as a message in the
dialog box. The maximum length of prompt is approximately 1024 characters. If
the message extends to more than a line, then the lines can be separated using
a carriage return character (Chr(13)) or a linefeed character (Chr(10)) between
each line.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Title − An optional parameter. A String expression displayed in the title bar of the
dialog box. If the title is left blank, the application name is placed in the title bar.

Default − An optional parameter. A default text in the text box that the user
would like to be displayed.

XPos − An optional parameter. The position of X axis represents the prompt

distance from the left side of the screen horizontally. If left blank, the input box is
horizontally centered.

YPos − An optional parameter. The position of Y axis represents the prompt

distance from the left side of the screen vertically. If left blank, the input box is
vertically centered.

Helpfile − An optional parameter. A String expression that identifies the helpfile

to be used to provide context-sensitive Help for the dialog box.

context − An optional parameter. A Numeric expression that identifies the Help

context number assigned by the Help author to the appropriate Help topic. If
context is provided, helpfile must also be provided.

Example
Let us calculate the area of a rectangle by getting values from the user at run
time with the help of two input boxes (one for length and one for width).

Function findArea()
Dim Length As Double
Dim Width As Double

Length = InputBox("Enter Length ", "Enter a Number")

Width = InputBox("Enter Width", "Enter a Number")
findArea = Length * Width
End Function

Variable

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Variable is a named memory location used to hold a value that can be changed
during the script execution. Following are the basic rules for naming a variable.
● You must use a letter as the first character.
● You can't use a space, period (.), exclamation mark (!), or the characters
@, &, $, # in the name.
● Name can't exceed 255 characters in length.
● You cannot use Visual Basic reserved keywords as variable name.
Syntax
In VBA, you need to declare the variables before using them.
Dim <<variable_name>> As <<variable_type>>
Data Types
There are many VBA data types, which can be divided into two main categories,
namely numeric and non-numeric data types.
Numeric Data Types
Following table displays the numeric data types and the allowed range of values.

Type Range of Values

Byte 0 to 255

Integer -32,768 to 32,767

Long -2,147,483,648 to 2,147,483,648

Single -3.402823E+38 to -1.401298E-45 for negative values

1.401298E-45 to 3.402823E+38 for positive values.

Double -1.79769313486232e+308 to -4.94065645841247E-324 for negative

values
4.94065645841247E-324 to 1.79769313486232e+308 for positive
values.

Currency -922,337,203,685,477.5808 to 922,337,203,685,477.5807

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Decimal +/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use

+/- 7.9228162514264337593543950335 (28 decimal places).

Non-Numeric Data Types

Following table displays the non-numeric data types and the allowed range of
values.

Type Range of Values

String (fixed length) 1 to 65,400 characters

String (variable length) 0 to 2 billion characters

Date January 1, 100 to December 31, 9999

Boolean True or False

Object Any embedded object

Variant (numeric) Any value as large as double

Variant (text) Same as variable-length string

Operator
An Operator can be defined using a simple expression - 4 + 5 is equal to 9. Here,
4 and 5 are called operands and + is called operator. VBA supports following types of
operators −

● Arithmetic Operators
● Comparison Operators
● Logical (or Relational) Operators
● Concatenation Operators
Assume variable A holds 5 and variable B holds 10, then −
Show Examples

Operator Description Example

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

+ Adds the two operands A + B will give 15

- Subtracts the second operand from the first A - B will give -5

* Multiplies both the operands A * B will give 50

/ Divides the numerator by the denominator B / A will give 2

% Modulus operator and the remainder after an integer B % A will give 0

division

^ Exponentiation operator B ^ A will give

100000

The Comparison Operators

There are following comparison operators supported by VBA.
Assume variable A holds 10 and variable B holds 20, then −
Show Examples

Operator Description Example

= Checks if the value of the two operands are equal or not. If (A = B) is False.
yes, then the condition is true.

<> Checks if the value of the two operands are equal or not. If (A <> B) is True.
the values are not equal, then the condition is true.

> Checks if the value of the left operand is greater than the (A > B) is False.
value of the right operand. If yes, then the condition is true.

< Checks if the value of the left operand is less than the value of (A < B) is True.
the right operand. If yes, then the condition is true.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

>= Checks if the value of the left operand is greater than or equal (A >= B) is False.
to the value of the right operand. If yes, then the condition is
true.

<= Checks if the value of the left operand is less than or equal to (A <= B) is True.
the value of the right operand. If yes, then the condition is
true.

The Logical Operators

Following logical operators are supported by VBA.
Assume variable A holds 10 and variable B holds 0, then −
Show Examples

Operator Description Example

AND Called Logical AND operator. If both the a<>0 AND b<>0 is False.
conditions are True, then the Expression is true.

OR Called Logical OR Operator. If any of the two a<>0 OR b<>0 is true.

conditions are True, then the condition is true.

NOT Called Logical NOT Operator. Used to reverse the NOT(a<>0 OR b<>0) is
logical state of its operand. If a condition is true, false.
then Logical NOT operator will make false.

XOR Called Logical Exclusion. It is the combination of (a<>0 XOR b<>0) is true.
NOT and OR Operator. If one, and only one, of
the expressions evaluates to be True, the result is
True.

The Concatenation Operators

Following Concatenation operators are supported by VBA.
Assume variable A holds 5 and variable B holds 10 then −
Show Examples

Operator Description Example

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

+ Adds two Values as Variable. Values are Numeric A + B will give 15

& Concatenates two Values A & B will give 510

Assume variable A = "Microsoft" and variable B = "VBScript", then −

Operator Description Example

+ Concatenates two Values A + B will give MicrosoftVBScript

& Concatenates two Values A & B will give MicrosoftVBScript

Note − Concatenation Operators can be used for both numbers and strings. The
output depends on the context, if the variables hold numeric value or string
value.

1. USER FORMS

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Add the Controls

To add the controls to the Userform, execute the following steps.

1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project
Explorer.
2. Click Insert, Userform. If the Toolbox does not appear automatically, click View,
Toolbox. Your screen should be set up as below.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

3. Add the controls listed in the table below. Once this has been completed, the result
should be consistent with the picture of the User form shown earlier. For example,
create a text box control by clicking on Text Box from the Toolbox. Next, you can
drag a text box on the User form. When you arrive at the Car frame, remember to
draw this frame first before you place the two option buttons in it.

4. Change the names and captions of the controls according to the table below.
Names are used in the Excel VBA code. Captions are those that appear on your
screen. It is good practice to change the names of controls. This will make your code
easier to read. To change the names and captions of the controls, click View,
Properties Window and click on each control.

Control Name Caption

Userform DinnerPlannerUserForm Dinner Planner

Text Box NameTextBox

Text Box PhoneTextBox

List Box CityListBox

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Combo Box DinnerComboBox

Check Box DateCheckBox1 June 13th

Check Box DateCheckBox2 June 20th

Check Box DateCheckBox3 June 27th

Frame CarFrame Car

Option Button CarOptionButton1 Yes

Option Button CarOptionButton2 No

Text Box MoneyTextBox

Spin Button MoneySpinButton

Command
OKButton OK
Button

Command
ClearButton Clear
Button

Command
CancelButton Cancel
Button

7 Labels No need to change Name:, Phone Number:, etc.

Note: a combo box is a drop-down list from where a user can select an item or fill in
his/her own choice. Only one of the option buttons can be selected.

Show the User form

To show the User form, place a command button on your worksheet and add the
following code line:
Private Sub CommandButton1_Click()

DinnerPlannerUserForm.Show

End Sub

We are now going to create the Sub User Form _Initialize. When you use the Show
method for the User form, this sub will automatically be executed.

1. Open the Visual Basic Editor.

2. In the Project Explorer, right click on DinnerPlannerUserForm and then click View
Code.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

3. Choose Userform from the left drop-down list. Choose Initialize from the right drop-
down list.

4. Add the following code lines:

Private Sub UserForm_Initialize()

'Empty NameTextBox
NameTextBox.Value = ""

'Empty PhoneTextBox
PhoneTextBox.Value = ""

'Empty CityListBox
CityListBox.Clear

'Fill CityListBox
With CityListBox
.AddItem "San Francisco"
.AddItem "Oakland"
.AddItem "Richmond"
End With

'Empty DinnerComboBox
DinnerComboBox.Clear

'Fill DinnerComboBox
With DinnerComboBox
.AddItem "Italian"
.AddItem "Chinese"
.AddItem "Frites and Meat"
End With

'Uncheck DataCheckBoxes
DateCheckBox1.Value = False
DateCheckBox2.Value = False
DateCheckBox3.Value = False

'Set no car as default

CarOptionButton2.Value = True

'Empty MoneyTextBox
MoneyTextBox.Value = ""

'Set Focus on NameTextBox

NameTextBox.SetFocus

End Sub

Explanation: text boxes are emptied, list boxes and combo boxes are filled, check
boxes are unchecked, etc.

Assign the Macros

We have now created the first part of the Userform. Although it looks neat already,
nothing will happen yet when we click the command buttons on the Userform.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

1. Open the Visual Basic Editor.

2. In the Project Explorer, double click on DinnerPlannerUserForm.

3. Double click on the Money spin button.

4. Add the following code line:

Private Sub MoneySpinButton_Change()

MoneyTextBox.Text = MoneySpinButton.Value

End Sub

Explanation: this code line updates the text box when you use the spin button.

5. Double click on the OK button.

6. Add the following code lines:

Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet1 active

Sheet1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = PhoneTextBox.Value
Cells(emptyRow, 3).Value = CityListBox.Value
Cells(emptyRow, 4).Value = DinnerComboBox.Value

If DateCheckBox1.Value = True Then Cells(emptyRow, 5).Value =

DateCheckBox1.Caption

If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value =

Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption

If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value =

Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption

If CarOptionButton1.Value = True Then

Cells(emptyRow, 6).Value = "Yes"
Else
Cells(emptyRow, 6).Value = "No"
End If

Cells(emptyRow, 7).Value = MoneyTextBox.Value

End Sub

Explanation: first, we activate Sheet1. Next, we determine emptyRow. The variable

emptyRow is the first empty row and increases every time a record is added. Finally,
we transfer the information from the Userform to the specific columns of emptyRow.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

7. Double click on the Clear button.

8. Add the following code line:

Private Sub ClearButton_Click()

Call UserForm_Initialize

End Sub

Explanation: this code line calls the Sub UserForm_Initialize when you click on the
Clear button.

9. Double click on the Cancel Button.

10. Add the following code line:

Private Sub CancelButton_Click()

Unload Me

End Sub

Explanation: this code line closes the Userform when you click on the Cancel button.

Test the User form

Exit the Visual Basic Editor, enter the labels shown below into row 1 and test the User
form.

Result:

User form and Ranges

You can use a Reedit control in Excel VBA to get a range from a user. The Userform we are going to
create colors the minimum value of the range stored in the re edit control.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

To create this User form, execute the following steps.

1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.

2. Click Insert, User form. If the Toolbox does not appear automatically, click View, Toolbox. Your screen
should be set up a

3. Add the label, RefEdit control and command buttons. Once this has been completed, the result should
be consistent with the picture of the Userform shown earlier. For example, create a RefEdit control by
clicking on RefEdit from the Toolbox. Next, you can drag a RefEdit control on the Userform.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Note: If your toolbox does not have a RefEdit control, set a reference to RefEdit control. Click Tools,
References, and check Ref Edit Control.

4. You can change the names and the captions of the controls. Names are used in the Excel VBA code.
Captions are those that appear on your screen. It is good practice to change the names of the controls,
but it is not necessary here because we only have a few controls in this example. To change the caption
of the Userform, label and command buttons, click View, Properties Window and click on each control.

5. To show the Userform, place a command button on your worksheet and add the following code line:

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

We are now going to create the Sub UserForm_Initialize. When you use the Show method for the
Userform, this sub will automatically be executed.

6. Open the Visual Basic Editor.

7. In the Project Explorer, right click on UserForm1 and then click View Code.

8. Choose User form from the left drop-down list. Choose Initialize from the right drop-down list.

9. Add the following code lines:

Private Sub User FormInitialize()

Sheet1.Cells.Font.Color = vb Black

UserForm1.RefEdit1.Text = Selection.Address

End Sub

Explanation: the first code line changes the font color of all the cells on sheet1 to black. The second code
line obtains the address of the current selection and displays it in the RefEdit control.

We have now created the first part of the Userform. Although it looks neat already, nothing will happen
yet when we click the command buttons on the Userform.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

10. In the Project Explorer, double click on UserForm1.

11. Double click on the Go button.

12. Add the following code lines:

Private Sub CommandButton1_Click()

Dim addr As String, rng, cell As Range, minimum As Double

addr = RefEdit1.Value

Set rng = Range(addr)

minimum = WorksheetFunction.Min(rng)

For Each cell In rng

If cell.Value = minimum Then cell.Font.Color = vbRed

Next cell

End Sub

Explanation: first, we get the address from the Ref Edit control and store it into the String variable add r.
Next, we set rng to the range specified in the Ref Edit control. Next, we use the worksheet function Min to
find the minimum value in the range. Finally, we color the minimum value(s) using a loop.

13. Double click on the Cancel button.

14. Add the following code line:

Private Sub CommandButton2_Click()

Unload Me

End Sub

Explanation: this code line closes the User form when you click on the Cancel button.

14. Test the User form.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

When programming using VBA, there are few important objects that a user
would be dealing with.

● Application Objects
● Workbook Objects
● Worksheet Objects
● Range Objects

Application Objects
The Application object consists of the following −

● Application-wide settings and options.

● Methods that return top-level objects, such as ActiveCell, ActiveSheet, and so on.

Example

'Example 1 :
Set xlapp = CreateObject("Excel.Sheet")
xlapp.Application.Workbooks.Open "C:\test.xls"

'Example 2 :
Application.Windows("test.xls").Activate

'Example 3:
Application.ActiveCell.Font.Bold = True

Workbook Objects
The Workbook object is a member of the Workbooks collection and contains all
the Workbook objects currently open in Microsoft Excel.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Example

'Ex 1 : To close Workbooks

Workbooks.Close

'Ex 2 : To Add an Empty Work Book

Workbooks.Add

'Ex 3: To Open a Workbook

Workbooks.Open FileName:="Test.xls", ReadOnly:=True

'Ex : 4 - To Activate WorkBooks

Workbooks("Test.xls").Worksheets("Sheet1").Activate

Worksheet Objects
The Worksheet object is a member of the Worksheets collection and contains all
the Worksheet objects in a workbook.

Example

'Ex 1 : To make it Invisible

Worksheets(1).Visible = False

'Ex 2 : To protect an WorkSheet

Worksheets("Sheet1").Protect password:=strPassword,
scenarios:=True

Range Objects
Range Objects represent a cell, a row, a column, or a selection of cells
containing one or more continuous blocks of cells.
'Ex 1 : To Put a value in the cell A5
Worksheets("Sheet1").Range("A5").Value = "5235"

'Ex 2 : To put a value in range of Cells

Worksheets("Sheet1").Range("A1:A4").Value = 5

User Defined Function

Below we will look at a program in Excel VBA that creates a User Defined Function. Excel has a large
collection of functions. In most situations those functions are sufficient to get the job done. If not, you can

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

create your own function called User Defined Function or custom Excel function. You can access a User
Defined Function just like any other Excel function.

We want to create a function called SUMEVENNUMBERS that finds the sum of the even numbers of a
randomly selected range.

User defined functions need to be placed into a module.

1. Open the Visual Basic Editor and click Insert, Module.

2. Add the following code line:

Function SUMEVENNUMBERS(rng As Range)

The name of our Function is SUMEVENNUMBERS. The part between the brackets means we give Excel
VBA a range as input. We name our range rng, but you can use any name.

3. Next, we declare a Range object and call it cell.

Dim cell As Range

4. We want to check each cell in a randomly selected range (this range can be of any size). In Excel
VBA, you can use the For Each Next loop for this. Add the following code lines:

For Each cell In rng

Next cell

Note: cell is randomly chosen here, you can use any name.

5. Next, we check for each value in this range whether it is even or not. We use the Mod operator for this.
The Mod operator gives the remainder of a division. So 7 mod 2 would give 1. 7 is divided by 2 (3 times)
to give a remainder of 1. Having said this, it is easy to check whether a number is even or not. Only if the
remainder of a number divided by 2 is 0, the number is even. 8 mod 2 gives 0, 8 is divided by 2 exactly 4
times, and therefore 8 is even. Add the following If statement to the For Each Next loop.

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

If cell.Value Mod 2 = 0 Then

End If

6. Only if this statement is true, we add the value to SUMEVENNUMBERS. Add the following code line in
the If statement.

SUMEVENNUMBERS = SUMEVENNUMBERS + cell.Value

7. Don't forget to end the function (outside the loop).

End Function

8. Now you can use this function, just like any other Excel function, to find the sum of the even numbers
of a randomly selected range

Hide All Worksheets Except the

Active Sheet
If you're working on a report or dashboard and you want to hide all the
worksheet except the one that has the report/dashboard, you can use
this macro code.

'This macro will hide all the worksheet except the active sheet
Sub HideAllExceptActiveSheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden
Next ws
End Sub

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Unhide All Worksheets at One Go

If you are working in a workbook that has multiple hidden sheets, you
need to unhide these sheets one by one. This could take some time in
case there are many hidden sheets.

Here is the code that will unhide all the worksheets in the workbook.

'This code will unhide all sheets in the workbook

Sub UnhideAllWoksheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

The above code uses a VBA loop (For Each) to go through each
worksheets in the workbook. It then changes the visible property of the
worksheet to visible

Sort Worksheets Alphabetically Using VBA

If you have a workbook with many worksheets and you want to sort these alphabetically, this macro code
can come in really handy. This could be the case if you have sheet names as years or employee names
or product names.

'This code will sort the worksheets alphabetically

Sub SortSheetsTabName()

Application.ScreenUpdating = False

Dim ShCount As Integer, i As Integer, j As Integer

ShCount = Sheets.Count

For i = 1 To ShCount - 1

For j = i + 1 To ShCount

If Sheets(j).Name < Sheets(i).Name Then

Sheets(j).Move before:=Sheets(i)

End If

Next j

Next i

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

Application.ScreenUpdating = True

End Sub

Protect All Worksheets At One Go

If you have a lot of worksheets in a workbook and you want to protect
all the sheets, you can use this macro code.

It allows you to specify the password within the code. You will need this
password to unprotect the worksheet.

'This code will protect all the sheets at one go

Sub unProtectAllSheets()
Dim ws As Worksheet
Dim password As String
password = "Test123" 'replace Test123 with the password you want
For Each ws In Worksheets
ws.Protect password:=password
Next ws
End Sub

https://trumpexcel.com/excel-macro-examples/

Background Colors

Changing background colors in Excel VBA is easy. Use the Interior property to return an Interior object. Then
use the ColorIndex property of the Interior object to set the background color of a cell.

Place three command buttons on your worksheet and add the following code lines:

ADVANCED EXCEL
 G-TEC COMPUTER EDUCATION
ISO 9001:2015 CERTIFIED

1. The code line below sets the background color of cell A1 to light blue.

Range("A1").Interior.ColorIndex = 37

2.The following code line sets the background color of cell A1 to 'No Fill'.

Range("A1").Interior.ColorIndex = 0

3.If you want to know the ColorIndex number of a color, simply ask Excel VBA.

MsgBox Selection.Interior.ColorIndex

ADVANCED EXCEL