NAME - GEETU

COURSE - CDEO
 INTRODUCTION

TCIL IT EDUCATION & TRAINING, PORT BLAIR

TCIL IT EDUCATION & TRAINING IN PORT BLAIR. COMPUTER TRAINING INSTITUTES

One of the highly recognized training centres in this city, Tcil It Education & Training
in M G Road was established a while ago. It has been playing a pivotal role ever since its
establishment by enabling the students to have easy access to rich learning programs. It has
been constantly empowering the students by helping them identify the uniqueness of their
courses and curriculum while at the same time making them understand as to how to use
these courses to their best advantage. The establishment is run by a team of qualified and
professional people, having enormous industry knowledge and skills. Located on M G Road,
Opposite ANIIDCO Petrol Pump, Goalghar, finding the centre is a piece of cake for anyone,
coming from any corner of the city. The locality around is well-connected with different parts
of the city due to which accessing the institute is a convenient affair. This listing is also
listed in Computer Training Institutes.

 Services Offered:
Tcil It Education & Training in Port Blair enjoys a good infrastructure. The facility is endowed
with a conducive learning and knowledge sharing environment. Students have convenient
access to essential learning tools and study materials. The faculty team is very
approachable; always keen to help and support whenever a student needs that. Each of them
is very passionate about teaching and mentoring. A lot of its previous students are doing
great in their professional life, many of whom recognize this institute for that and feel lucky
to have had enrolled here. This establishment has been successful in shaping the future of
many of its students and continues to do so. It identifies the talent in each individual,
analyses their needs and nurtures them accordingly so that they can obtain the needed
excellence. In order to talk to one of the office executives, one can visit here anytime
between 06:00 - 20:00. To know more about the fee structure, one can use the phone
numbers mentioned on the top of this page.
MICROSOFT EXCEL
 ABSTRACT
 STUDENT ATTENDANCE MANAGEMENT

system deals with the maintenance of the student’s attendance details. It is generates the
attendance of the student on basis of presence in class. It is maintained on the daily basis of
their attendance. the staffs will be provided with the separate username & password to make
the student’s status. The staffs handling the particular subjects responsible to make the
attendance for all students. Only if the student present on that particular period, the attendance
will be calculated. The students attendance reports based on weekly and consolidate will be
generated.

DEFINITION - WHAT DOES MICROSOFT EXCEL MEAN ?

 MICROSOFT EXCEL
is a spreadsheet developed by microsoft for windows, macos, android and ios. It features
calculation, graphing tools, pivot tables, and a macro programming language called visual
basic for applications. it has been a very widely applied spreadsheet for these platforms,
especially since version 5 in 1993, and it has replaced lotus 1-2-3 as the industry standard for
spreadsheets. excel forms part of the microsoft office suite of software.Microsoft Excel is a
software program produced by Microsoft that allows users to organize, format and calculate
data with formulas using a spreadsheet system. This software is part of the Microsoft Office
suite and is compatible with other applications in the Office suite.

 EXPLAINS MICROSOFT EXCEL

Excel is a commercial spreadsheet application produced and distributed by Microsoft for

Microsoft Windows and Mac OS. It features the ability to perform basic calculations, use
graphing tools, create pivot tables and create macros.Excel has the same basic features as all
spreadsheet applications, which use a collection of cells arranged into rows and columns to
organize and manipulate data. They can also display data as charts, histograms and line
graphs. Excel permits users to arrange data so as to view various factors from different
perspectives. Visual Basic is used for applications in Excel, allowing users to create a variety of
complex numerical methods. Programmers are given an option to code directly using the
Visual Basic Editor, including Windows for writing code, debugging and code module
organization.

 CREATE A BASIC ATTENDANCE SHEET IN EXCEL

If you're a teacher in any type of school, whether it's high school, college, or middle school,
it's imperative that you keep an attendance sheet. The paper and pen route can get pretty
messy, and very disorganized, and that's where Microsoft Excel comes in. With this
software, you can create a simple yet functional attendance sheet to keep track of your
students.Using the method outlined below, you can automate the entry of many fields, as
well as total up attendance days using SUM and IF functions. You'll also be able to create a
print area where attendance totals are associated with their students.This guide uses the
latest Windows version of Excel, but these steps will work on all legacy versions of Excel for
Windows and Mac OS X (now macOS), as well as Excel online. If you're using Excel on a
Mac computer, just note that the CTRL key for Windows will be the CMD key for Mac.
 OBJECTIVE:

“attendance management system”

 Ref,
Is software developed for maintaining the attendance of the student on the daily
basis in the collage. Here the staffs, who are handling the subjects, will be responsible to
mark the attendance of the students. Each staff will be given with a separate username and
password based onthe subject they handle. An accurate report based on the student
attendance isgenerated here. This system will also help in evaluating attendance eligibility
criteria of a student. Report of the student’s at tendance on weekly and monthly basis
isgenerated.

 MONTHLY ATTENDANCE TEMPLATES IN MS EXCEL

These generic monthly attendance templates allow up to 30 student names to be

entered.Simply enter the Month and Year required at the top of the attendance sheet,
together with your class details.Enter the day of the week under the corresponding day of
the month.We recommend that you identify weekends by adding cell shading. This will
allow you to more easily identify the day of the week.

BASIC ATTENDANCE SHEET IN MS EXCEL IN JPG FILE

CREATE MARKSHEET IN MS EXCEL?
 CREATE MARK SHEET IN MS EXCEL

Marksheet creation is really not an easy task for the best look. But, MS Excel made this
apparent by its finest formula. Creating a mark sheet is about to use the grade calculator in
Excel. To make the grading sheet in Excel you need to enter some of the syntaxes to get it
done in the quickest way. However, there are many templates available to create mark
sheet in MS Excel.You can download the Excel formula list (syntax) to complete any work
which can be done with Excel. Nowadays every school prefers Excel to create the result
mark sheet easily. You can use the percentage formula in MS Access to add this also to the
grade sheet. This article here describes the procedure of calculating Total, Average, and
Grade in MS Excel. This process can be applied on MS Excel 2003 & 2007.

CREATE MARKSHEET IN MS EXCEL WITH (TOTAL, AVERAGE, AND GRADE)

Let's explain the Total, Average and Grade in MS-Excel. Suppose, we need to create mark
sheets for ‘ABC Academy’ and this institute has over 100 students that need to create Mark

Sheet annually.
 HOW TO CALCULATE THE TOTAL IN EXCEL MARK SHEET?

 Firstly, select ‘Total’ into the cell.

 Next input the syntax.
 Syntax: =Sum(click on 1st subject cell number:last subject cell number) then hit Enter from
the keyboard.

 HOW TO CREATE THE AVERAGE IN EXCEL GRADE SHEET?

 Select ‘Average’ cell.
 Later input the proper syntax over there.

 Syntax: - =(click on ‘Total’ cell/total number of subjects), then press Enter.

 HOW TO CALCULATE GRADE IN MARK SHEET ON EXCEL?
 At first select ‘Grade’ cell
 Then input syntax
 Syntax: - =IF(click on Average cell number >=90,“AA”,IF(click on Average cell number
>=80, “A+”, IF(click on Average cell number >=65,“A”,IF(click on Average cell number
>=45,“B+”,IF(click on Average cell number >= 30,“B”,“Fail”))))) then press Enter button
from the keyboard.

 Note: Then-
 Select the 1st Total cell (like – 373).
 Select cell pointer and drag on upto the last name.
 Select the 1st Average cell (like – 93.25).
 Select cell pointer and drag on upto the last name.
 Select 1st Grade cell (like – AA).
 Select cell pointer and drag onto the last name.

 Just follow the above steps to complete the creation of a mark

sheet in the shortest time possible.
HOW TO USE PIVOT TABLES IN EXCEL
 MICROSOFT EXCEL : PIVOT TABLES EXPLAINED

Excel is great for keeping lists of information, but sometimes these lists can be large and
difficult to analyze.

For example, say you own a grocery store chain with stores in several states. You have
sales data for each store, and you want to analyze the sales by region. Some of the stores
are located in malls while others are stand-alone buildings. You'd like to see how the
different types of stores compare.

Unfortunately, all this information is in a long Excel list that's hard to comprehend.
Fortunately, a PivotTable can organize and summarize this information to help you analyze
it quickly and easily.

 WHAT'S A PIVOT TABLE?

A PivotTable is a dynamic table that extracts data from an Excel list or an outside source
and organizes it in a summarized tabular format that's easy to analyze.

As you make changes to the Excel list data source, you can refresh the PivotTable to pull
the updated list information.

 CREATING A PIVOT TABLE

To make a PivotTable, you first need a well-organized Excel list with column labels. You
can also use an external database file or Internet information as your data source, but many
of these sources will still require you to retrieve the data and put it in an Excel list format.
Most of the time, you'll use an Excel list, so that's what we'll assume in this article.

Once you have your Excel list ready to go, click any cell in the list to start creating a
PivotTable. Select Data > PivotTable and PivotChart Report (PivotTable Report in Excel 97)
from the menu to open the PivotTable and PivotChart Wizard.

 THE PIVOT TABLE WIZARD

Excel 2000, 2002, 2003: In Step 1 of the Wizard, make sure the "Microsoft Office Excel list
or database" and "PivotTable" options are selected, then click the Next button. In Step 2 of
the Wizard, select the range of the data you want to include in the PivotTable. This range
should include your whole Excel list, and it should already be correctly selected for you in
the Range box. If it's not, select the correct range with your mouse. Then click Next.
Step 3 of the Wizard asks if you want to put the new PivotTable report in a new worksheet
or the existing worksheet. Select "Existing worksheet" and click a blank cell in the
worksheet near your list. This is where your PivotTable report will go.

When the PivotTable is inserted, design its layout. Click the Layout button to display the
Layout screen.

Drag desired fields from the field list on the right onto the ROW and COLUMN areas of the
diagram. Then drag fields that contain the data you want to display onto the DATA area.

Once you move a field into the DATA area, double-click it and select a calculation to be
performed on the data. For example, if you choose Average, Excel will average the list data
before it presents it in the PivotTable.

Let's walk through the layout of the grocery store PivotTable example. The goal of the
grocery store PivotTable is to display the sales by region, while also comparing mall vs.
stand-alone store sales.
To do this, we drag the Region field into the ROW area so that the different regions will
appear in separate rows. Next, we drag the Store Type field into the COLUMN area so that
we'll have a column for mall stores and one for stand-alone stores.

Finally, we drag the Sales field into the DATA area because the Sales numbers are what
we want to organize and analyze. By default, the PivotTable calculates the sum of the
Sales amounts by each region and store type, but by double-clicking on the Sales field, we
can calculate something else, such as the average sales, instead.

To remove fields from the layout, drag them off the diagram. Once you like your layout, click
OK and click the Finish button. The new PivotTable appears in your worksheet!

Excel 97: The Excel 97 Wizard works the same as the other versions, it just has an extra
Wizard screen for the layout. You don't need to click the Layout button to open the layout
screen because it appears on its own.
 WORKING WITH PIVOTTABLE ITEMS

Once you've created a PivotTable, you can move an item by dragging its field button
around the table. Simply drag it to a new position to create a new layout. To remove an
item, click the field button and drag it off the PivotTable area.

To add an item in Excel 2002 and 2003, click the PivotTable to display the PivotTable Field
List and drag the desired field onto the PivotTable area. In Excel 97 and 2000, reopen the
Wizard by clicking the PivotTable to display the PivotTable toolbar. Click the PivotTable
button list arrow and select PivotTable Wizard. Make changes to the layout in the Wizard,
and click Finish.

Finally, if you change the source data, click the Refresh Data button on the PivotTable
toolbar to refresh the PivotTable.

 SCHEDULE A CLASS ON EXCEL

Request an on-site Excel class from Applied Office. Sessions are priced per hour, not per
person, and your employees will be shown how to create spreadsheets, charts, and use
formulas that will save them a lot of time.
 UPCOMING WORKSHOPS
Excel: Making it Look Good (Apr 11)

Discover how easy it is to create attractive spreadsheets that communicate your ideas with
ease. Through the use of spacing, borders, font, and color, you'll observe how a simple
spreadsheet can be made to be more easily understood. Learn how to create charts and
adjust their appearance.
The Magic of Excel's Pivot Tables (May 2)

One of Excel's best kept secrets is its ability to quickly summarize thousands of records in
just a few clicks. The Pivot Table feature sorts, counts, and totals your data and lets you
quickly review that data in a variety of ways. That long list of sales transactions can finally
be turned into something useful! Basic experience with Excel is a must.
 QUICK REFERENCE CARD

Get the Quick Reference Card on Microsoft Excel! Download it for free and print it on your
own printer. You might even want to laminate it.
WHAT IS DATA VALIDATION AND CONSOLIDATION
 WHAT IS DATA VALIDATION AND CONSOLIDATION?

In computer science, data validation is the process of ensuring that a program operates on
clean, correct and useful data. It uses routines, often called "validation rules"
"validation constraints" or "check routines", that check for correctness, meaningfulness, and
security of data that are input to the system.

Data Validation
Data Validation Example | Create Data Validation Rule | Input Message |
Error Alert | Data Validation Result
Use data validation in Excel to make sure that users enter certain values into a cell.
Data Validation Example
In this example, we restrict users to enter a whole number between 0 and 10.

Create Data Validation Rule

To create the data validation rule, execute the following steps.
1. Select cell C2.
2. On the Data tab, in the Data Tools group, click Data Validation.On the Settings tab:

3. In the Allow list, click Whole number.

4. In the Data list, click between.

5. Enter the Minimum and Maximum values.

 INPUT MESSAGE

Input messages appear when the user selects the cell and tell the user what to enter.
On the Input Message tab:
1. Check 'Show input message when cell is selected'.
2. Enter a title.
3. Enter an input message.

 ERROR ALERT

If users ignore the input message and enter a number that is not valid, you can show them
an error alert.
On the Error Alert tab:
1. Check 'Show error alert after invalid data is entered'.
2. Enter a title.
3. Enter an error message.
4. Click OK.

Data Validation Result

1. Select cell C2.

2. Try to enter a number higher than 10.

Result:

Note: to remove data validation from a cell, select the cell, on the Data tab, in the
Data Tools group, click Data Validation, and then click Clear All. You can use Excel's
Go To Special feature to quickly select all cells with data validation.
DATA CONSOLIDATION
 EXCEL – DATA CONSOLIDATION

FEB 21

Let’s say you have asked people to fill out a simple time sheet. Everyone fills out their sheet
and sends it back. You now have to total up all the hours that everyone worked for each
day of the week or whatever time period you have asked them to complete.

The problem you face is that not everyone works on every day of the week and they may
have worked on different projects. So how to add everything up and allocate it to the correct
day and project?

You have 2 possible strategies you could take;

1. Consolidation
2. Pivot tables
In this example, because the data is limited and simple I am going to go for DATA
CONSOLIDATION.

If you have managed to bring all the worksheets into a single workbook then it’s worth
naming each range as it makes referencing the data a bit simpler. However, it’s not a
problem if you can’t as you can easily pick out ranges from separate worksheets and
workbooks.

Click in a blank cell below or to the right of any existing tables or on a blank sheet. This is
important otherwise you could end up overwriting a data table by accident when you create
the consolidated table.

Go to the DATA tab and click on;

This will open the CONSOLIDATE window;

At the top you have the option to choose from a list of functions. These are all the usual
functions; SUM, AVERAGE, COUNT, MAX, MIN etc. Select the appropriate function for
what you are trying to achieve. In this case I want to add up all the hours people have
completed so I’d go for SUM.
Then you need to select all the tables that people have filled out for you. Make sure you
include all row and column headings.

Click on the range selection button…..

…and then select the ranges. After selecting a range of cells, click on ADD to add it to you
references list. If you forget to do this (and it’s easily done….I’ve done it many times myself)
your consolidation will not happen. So whenever you are consolidating repeat to yourself
“Select…..add, select….add”.

Keep going until all the table ranges have been added.

Now for the important bit. At the bottom of the CONSOLIDATION window there are two little
tick boxes.

You need to tick at least one of these so that the end result shows you some labels
associated with the data you have just consolidated. In this example I want to see both the
days and the project numbers against the number of total hours, so here I would tick both
boxes. You may well find that in most cases you will do so this anyway.
Click on OK and there you have your consolidated data!

CONSOLIDATED DATA FROM MY THREE TABLES

In this Excel timetable project, we'll set ourselves some chores to do around the house.
We'll plan an exact time to start a task, and how long it will take to finish. We'll be adding
one time to another. So create the same spreadsheet as the one in the image below (you
don't need to use the same colours):

What we're going to do is enter a Start Time for our chores. This will be 9 in the morning.
Then we'll estimate how long it takes to wash the pots, which we'll place in the Time Job
Takes column. We'll add the "Time Job Takes" to the "Start Time" to get a new start time
for the Hoover chore. But you'll see how it works as we go along.
The first thing to do is to format the Start Time column:
 Highlight the Start Time column, from cell B3 to cell B8
 From the Excel menu bar, click on Home
 Locate the Number panel:

Click on the arrow (circled above) to bring up the Format Cells dialogue box, and then click
the Time category:
Under Category on the left, click on Time. Under the time Types on the right, select the
first one.

Don't click OK yet, but have a look at the time format that Excel is going to enter:

Excel will enter the hours, then minutes and the seconds. We don't need the seconds.
Unfortunately, this version of Excel doesn't give you a time format without seconds. To
remedy this, click on Custom under the Category list on the left. Then, under Type, select
"h:mm AM/PM", as in the image below:
Click OK when you're done. We'll now enter our first time.

 Click on cell B3, then click inside the formula bar

 Type in 09:00 (the colon in between the numbers is important)
 Press the enter key on your keyboard
 Excel will now see cell B3 as a time - 9.00 AM

There is a simpler way to format a cell as Time, though. Try this:

 Click inside cell C3

 Click inside the formula bar
 Type in 0:15
 Press the Enter key on your keyboard
Because you included the colon (:), Excel knows that you want to format the cell as a time.
The 0:15 then means 15 minutes (We'll assume that we're very fast at washing pots - it's all
that practice!).
But your spreadsheet will now look like this:
If we started at 9.00, and the job took 15 minutes, the next start time will be 9.15. We can
enter a formula for this:

 Click into cell B4 to highlight it

 Then click inside the formula bar
 Enter the following formula:= B3 + C3
 Press the enter key
 Excel will place a time of 09:15 AM in cell B4

The start time for our next chore, then is 9.15 AM. We can use AutoFill for the rest of the B
column:
 Click in to cell B4 on your spreadsheet
 Move your mouse the bottom right of cell B4, and the pointer will change shape. When
you see the black cross, the AutoFill cursor, hold down your left mouse button and drag
down to cell B9
 Let go of the left mouse button and Excel will AutoFill the other formulas
Because we haven't yet entered any other figures for the "Time job Takes" column, a
time of 9.15 will appear in all the cells.
 Click onto cell C4 on your spreadsheet
 Now click in to the formula bar at the top, and type in 01:00 (meaning one hour)
 Hit the Enter key on your keyboard and Excel will change all the cells from B5 to B8
to a time of 10:15 AM
 Your spreadsheet should look like ours:
Complete the rest of the spreadsheet for yourself. Enter these times in the C column:
Rest: 30 minutes
Dust: 30 minutes
Windows: One hour
Rest 30 minutes
If you complete it all correctly, you should have a spreadsheet like ours in the image
below:<

In the image above, you'll notice that there is a time in cell B9 of 12:45 PM. You should
easily be able to get the same figure in your spreadsheet!

Working with date and times can be quite tricky. But it's well worth getting the hang of. We'll
move on, though, and have a go at financial functions in Excel.
 CALENDAR 2020
January February March April May June July August September October November December
1 We New Year’s 1 1 Su 1 We 1 Fr 1 Mo 23 1 We 1 Sa 1 Tu 1 Th 1 Su 1 Tu
Day
2 Th 2 Sa
Su 2 Mo 10 2 Th 2 Sa 2 Tu 2 Th 2 Su 2 We 2 Fr 2 Mo 45 2 We
3 Fr 3 Mo 6 3 Tu 3 Fr 3 Su 3 We 3 Fr 3 Mo 32 3 Th 3 Sa 3 Tu 3 Th
4 Sa 4 Tu 4 We 4 Sa 4 Mo 19 4 Th 4 Sa 4 Tu 4 Fr 4 Su 4 We 4 Fr
5 Su 5 We 5 Th 5 Su 5 Tu 5 Fr 5 Su 5 We 5 Sa 5 Mo 41 5 Th 5 Sa
6 Mo 2 6 Th 6 Fr 6 Mo 15 6 We 6 Sa 6 Mo 28 6 Th 6 Su 6 Tu 6 Fr 6 Su
7 Tu 7 Fr 7 Sa 7 Tu 7 Th 7 Su 7 Tu 7 Fr 7 Mo 37 7 We 7 Sa 7 Mo 50

8 We 8 Sa 8 Su 8 We 8 Fr Early May 8 Mo 24 8 We 8 Sa 8 Tu 8 Th 8 Su 8 Tu
Bank Hol.
9 Th 9 Su 9 Mo 11 9 Th 9 Sa 9 Tu 9 Th 9 Su 9 We 9 Fr 9 Mo 46 9 We
10 Fr 10 Mo 7 10 Tu 10 Fr Good Friday 10 Su 10 We 10 Fr 10 Mo 33 10 Th 10 Sa 10 Tu 10 Th
11 Sa 11 Tu 11 We 11 Sa 11 Mo 20 11 Th 11 Sa 11 Tu 11 Fr 11 Su 11 We 11 Fr
12 Su 12 We 12 Th 12 Su 12 Tu 12 Fr 12 Su 12 We 12 Sa 12 Mo 42 12 Th 12 Sa
13 Mo 3 13 Th 13 Fr 13 Mo Easter 13 We 13 Sa 13 Mo 29 13 Th 13 Su 13 Tu 13 Fr 13 Su
Monday
14 Tu 14 Fr 14 Sa 14 Tu 14 Th 14 Su 14 Tu 14 Fr 14 Mo 38 14 We 14 Sa 14 Mo 51

15 We 15 Sa 15 Su 15 We 15 Fr 15 Mo 25 15 We 15 Sa 15 Tu 15 Th 15 Su 15 Tu
16 Th 16 Su 16 Mo 12 16 Th 16 Sa 16 Tu 16 Th 16 Su 16 We 16 Fr 16 Mo 47 16 We
17 Fr 17 Mo 8 17 Tu 17 Fr 17 Su 17 We 17 Fr 17 Mo 34 17 Th 17 Sa 17 Tu 17 Th
18 Sa 18 Tu 18 We 18 Sa 18 Mo 21 18 Th 18 Sa 18 Tu 18 Fr 18 Su 18 We 18 Fr
19 Su 19 We 19 Th 19 Su 19 Tu 19 Fr 19 Su 19 We 19 Sa 19 Mo 43 19 Th 19 Sa
20 Mo 4 20 Th 20 Fr 20 Mo 17 20 We 20 Sa 20 Mo 30 20 Th 20 Su 20 Tu 20 Fr 20 Su
21 Tu 21 Fr 21 Sa 21 Tu 21 Th 21 Su 21 Tu 21 Fr 21 Mo 39 21 We 21 Sa 21 Mo 52

22 We 22 Sa 22 Su 22 We 22 Fr 22 Mo 26 22 We 22 Sa 22 Tu 22 Th 22 Su 22 Tu
23 Th 23 Su 23 Mo 13 23 Th 23 Sa 23 Tu 23 Th 23 Su 23 We 23 Fr 23 Mo 48 23 We
24 Fr 24 Mo 9 24 Tu 24 Fr 24 Su 24 We 24 Fr 24 Mo 35 24 Th 24 Sa 24 Tu 24 Th
25 Sa 25 Tu 25 We 25 Sa 25 Mo Spring 25 Th 25 Sa 25 Tu 25 Fr 25 Su 25 We 25 Fr Christmas Day
Bank Hol.
26 Su 26 We 26 Th 26 Su 26 Tu 26 Fr 26 Su 26 We 26 Sa 26 Mo 44 26 Th 26 Sa Boxing Day

27 Mo 5 27 Th 27 Fr 27 Mo 18 27 We 27 Sa 27 Mo 31 27 Th 27 Su 27 Tu 27 Fr 27 Su
28 Tu 28 Fr 28 Sa 28 Tu 28 Th 28 Su 28 Tu 28 Fr 28 Mo 40 28 We 28 Sa 28 Mo Substitute day
29 We 29 Sa 29 Su 29 We 29 Fr 29 Mo 27 29 We 29 Sa 29 Tu 29 Th 29 Su 29 Tu
30 Th 30 Mo 14 30 Th 30 Sa 30 Tu 30 Th 30 Su 30 We 30 Fr 30 Mo 49 30 We
31 Fr 31 Tu 31 Su 31 Fr 31 Mo August Bank 31 Sa 31 Th
Hol.
THANK YOU…………………..