RANI SHUKLA
TYBCOM CSA
MODULE2 – EXCEL
*CREATING AND USING TEMPLATES
TEMPLATES –
A template is a file that serves as a starting point
for a new document. Templates contain
placeholder fields you can fill in and professionally-
designed styles and layouts. Word processors, presentation
programs, desktop publishing programs, and some website
hosts often include template galleries you can browse
when creating a new file.
USES OF TEMPLATES –
Using a template can save you a lot of time by
doing a lot of the design work for you. For example,
starting a new presentation using a PowerPoint template
will automatically generate text placeholders for titles,
subtitles, and slide content; it will also include design
elements like backgrounds, shapes, font combinations, and
color schemes.
USES OF TEMPLATES IN EXCEL –
An Excel template is a predesigned sheet that can
be used to create new worksheets with the same layout,
formatting and formulas. With templates, you don't need to
recreate the basic elements every time as they are already
integrated into the spreadsheet.
HOW TO CREATE AND USE TEMPLATES -
Instead of starting with a blank sheet, you can quickly
create a new workbook based on an Excel template. The
right template can really simplify your life since it makes
the most of tricky formulas, sophisticated styles and other
features of Microsoft Excel that you might not be even
familiar with.
In Excel 2010, you can either:
Select from Sample templates - these are basic
Excel templates that are already installed on your
computer.
CREATING AND LINKING MULTIPLE
SPREADSHEETS –
1. Open a Microsoft Excel workbook.
2. Click your destination sheet from the sheet
tabs. You will see a list of all your worksheets at the
bottom of Excel. Click on the sheet you want to link to
another worksheet.
3. Click an empty cell in your destination sheet. This
will be your destination cell. When you link it to another
sheet. Type = in the cell. It will start a formula in your
destination cell.
5. Click your source sheet from the sheet tabs. Find
the sheet where you want to pull data from, and click on
the tab to open the worksheet.
6.
Click ↵ Enter on your keyboard. This will finalize the
formula, and switch back to your destination sheet. Your
destination cell is now linked to your source cell, and
dynamically pulls data from it. Whenever you edit the data
in your source cell, your destination cell will also be
updated.
USING FORMULAS AND LOGICAL
OPERATORS -
The IF function allows you to make a logical
comparison between a value and what you
expect by testing for a condition and returning
a result if that condition is True or False.
=IF(Something is True, then do
something, otherwise do something
else)
But what if you need to test multiple
conditions, where let’s say all conditions need
to be True or False (AND), or only one
condition needs to be True or False (OR), or if
you want to check if a condition
does NOT meet your criteria? All 3 functions
can be used on their own, but it’s much more
common to see them paired with IF functions.
Here are overviews of how to
structure AND, OR and NOT functions
individually. When you combine each one of
them with an IF statement, they read like this:
AND – =IF(AND(Something is True,
Something else is True), Value if True,
Value if False)
OR – =IF(OR(Something is True,
Something else is True), Value if True,
Value if False)
NOT – =IF(NOT(Something is True),
Value if True, Value if False)
Examples –
DISCRIPTION-
CREATING AND USING NAMED RANGE –
1. Select the range B1:B5.
2. Click on the Formulas tab.
3. Then click on Define Name.
4. Give a new Name(PriceTotal in our example) & click
Ok
5. Type the name of the range with which you wish to
construct the Named Range in the Name Box on the
left.
