1

UNIT 1

Microsoft Word is word processing software. It is developed by Microsoft and is p

art of Microsoft Office Suite. It enables you to create, edit and save professional d
ocuments like letters and reports.
There are following options available in MS-Word:
0

Toolbars
The key components of Office 2000 are Word, Excel, PowerPoint, and
Access. We will focus on these four applications throughout the Office
2000 skill sets.
 2

In the previous lesson, you learned to choose a command using menu

s. You can also choose a command using a toolbar.

Toolbars contain icons representing the most commonly used com

mands. Sometimes it's easier to click an icon than it is to open a menu
and search for a command.

Certain toolbar icons (and their corresponding commands) are uniq

ue to specific Office applications, but there are others such as New, O
pen, and Save that are common to all of them.

Forgot what an icon on the toolbar means?

Hover your mouse pointer over the icon. A label will appear telling you
what the button does. This label is called a tooltip.

It doesn't matter which way you choose to perform a command; it's j

ust a matter of preference.

Standard and Formattin

g toolbars
The Standard and Formatting toolbars are the two most common t
oolbars in Microsoft Office 2000.

The Standard toolbar is located just below the menu bar. It contains i
cons representing universal commands such as New, Open, and Sav
 3

e. The Formatting toolbar is located just below the Standard toolbar. I

t contains icons representing text-modifying commands like text size,
bold, and ordered lists.

Hiding and displaying th

e Standard and Formatti
ng toolbars
Many people find the Standard and Formatting toolbars helpful. If yo
u find displaying both the Standard and Formatting toolbars too bulk
y, you can:

● Hide a toolbar
● Display the Standard and Formatting toolbars on the
same line

To hide or display a toolbar:

● Choose View Toolbars from the menu bar.
● The cascading toolbar menu appears, as shown.
 4

● Check marks appear next to currently displayed too

lbars.
● Click the toolbar you want to display, or click the tool
bar you want to hide.

To display the Standard

and Formatting toolbars
together:
● Choose View Toolbars Customize from the men
u bar.
● A Customize dialog box appears.
● Click the Options tab.
● Click or clear the Standard and Formatting toolbar
s Share One Row check box.
● Click Close.
 5

To view all options on the combined Standard and Formatting tool

bar, click the double right-pointing arrows . These double arrows e
xpand your toolbar options just as they do in the menus.

Quick Access Toolbar

Quick Access Toolbar lies next to the Microsoft Office Button. It is a customizable

toolbar that comes with a set of independent commands. It gives you quick acce

ss to commonly used commands such as Save, Undo, Redo, etc.

See the image:

When you click the drop-down arrow next to toolbar it offers more commands. W

ith a left click you can add any of these commands to Quick Access Toolbar. You

can also remove the commands added to the tool bar. The indent, spacing value

s, individual styles and other features that appear on the ribbon cannot be adde

d to quick access toolbar. The following image is showing the menu of quick acc

ess toolbar.

See the image:

8.2M

63

Leaked Claims of 'Sentient' AI Result in Suspension of Google Software Engineer

 6

Create a document in Word

On the File tab, click New.

1. In the Search for online templates box, enter the type of document you want
to create and press ENTER.

Tip: To start from scratch, select Blank document. Or, for practice using Word features, try a le
arning guide like Welcome to Word, Insert your first table of contents, and more.
 7

Add and format text

1. Place the cursor and type some text.

2. To format, select the text and then select an option: Bold, Italic, Bullets, Nu
mbering, and more.

Add Pictures, Shapes, SmartArt, Chart, and more

1. Select the Insert tab.

2. Select what you want to add:
○ Tables - select Table, hover over the size you want, and selec
t it.
○ Pictures - select Pictures, browse for pictures from your com
puter, select a stock image, or even search Bing.
Note: Older versions of Word may have Online Pictures on the ribbo
n next to Pictures.
○ Shapes - select Shapes, and choose a shape from the drop-d
own.
○ Icons - select Icons, pick the one you want, and select Insert.
○ 3D Models - select 3D Models, choose from a file or online so
urce, go to the image you want, and select Insert.
○ SmartArt - select SmartArt, choose a SmartArt Graphic, an
d select OK.
○ Chart - select Chart, select the chart you want, and select OK.
○ Screenshot - select Screenshot and select one from the dro
p-down.
 8

Microsoft Word: Editing Your Document

Moving Around Using the Arrow Keys or Mouse

You may move your cursor to the right, the left, and up or down by using the Arrow keys.

You may move the cursor around by moving the mouse pointer to the place you have in mind and clicking t
he mouse.

If you have not done a lot of typing and don’t have extensive experience with the mouse, you may find the
arrows to be the easiest method of moving around. Once you have conquered the mouse, you will only occ
asionally use the arrow keys.

Note: You cannot have the cursor past the end of the document using either the Arrows or the mouse.

Deleting Characters or Words

There are a number of ways to delete characters or words. We will explore many of them. You should be a
ware that you will develop preferences for getting your work done that may be different from your neighbor.
MS Word is a very flexible application and there are many ways to get the same job done.

You may Delete characters moving to the right using the Delete key, or Delete words moving to the right by
typing CTRL Delete.

You can delete characters moving to the left by using the Backspace key (back-arrow), or Delete words mo
ving to the left by pressing CTRL Backspace.

You may delete characters or words by selecting them by highlighting them with the mouse and then using
the Delete key.

You can delete characters or words by selecting them by highlighting them with the mouse and then click th
e Edit button on the Menu Bar and then clicking Clear (delete).

Exercise

Experiment by moving your cursor around the paragraph using the arrows. Move the cursor around using t
he mouse pointer and click to select a new location.

· Move the cursor to the beginning of the first center, the cursor will be blinking.

· Use the Delete key to delete the first center, one character at a time, moving to the right.. Use CTRL Delet
e (hold down the CTRL and then press the Delete key). To delete the next center as an entire word.

· Move the cursor to the end of the last center. Use the backspace key to delete the last center one charact
er at a time moving to the left.

· Use CTRL Backspace to delete the previous center as an entire word. You should have three centers left.

· Now select one of the centers and highlight it by moving the mouse pointer to the beginning or end of the
word and, while keeping the left (primary) button depressed, highlight it by moving across the word. If this is
difficult, you can also use the SHIFT key and the Arrow key (from the right or left) to highlight. With center h
ighlighted, press the Delete key. This is especially useful when you want to delete several words or a sente
nce or more.

· You can also “double click” a word to highlight it. “triple clicking” will highlight a larger area, usually the par
agraph.
 9

You should now be down to just one center in the sentence.

Undo and Redo or Repeat Buttons

Don’t panic if you touch a wrong key, or do something that seems to change everything from the way you w
ant it.. There is an Undo Key and a Redo key.

The Undo and Redo keys are found on the Formatting Toolbar. They are represented by curved arrows. Un
do is on the left and curves backwards, and Redo or Repeat is on the right.

Click the Undo button. The last center that you deleted should reappear unless you have done some other
command since then. Click Redo and it will be gone again.

Undo and Redo are also on the Edit Menu. Click on Edit and the first two buttons are Undo and Repeat (or
Undo). Redo is grayed out meaning it is unavailable until you have hit the Undo key.

Bold, Italics, and Underline

These keys are found on the Formatting Toolbar and can easily dress up your document and make it appe
ar more professional. They are represented by the B, I, and U.

Exercise

Using our paragraph, let’s practice using these formatting tools. Highlight the center’s name and then click
on B for Bold. Highlight the information in the parentheses and click on I for Italics. Highlight ‘meet old and
new friends’ and click on the U for Underline. Your paragraph should now look something like this:

The Golden Hour Senior Citizen’s center (that’s right, seven centers) is a great place to relax, meet old a
nd new friends, and get training on computers and word processing.

Font Size and Fonts

Changing the size of words in your document is one of the best ways to call attention to specific parts or pr
ovide a title or sections.

You can change the size of the whole document, or just a word, or just a character. My paragraph above is
in 12-point size. We will increase the size to 14. Select and highlight the entire paragraph using your mouse.

With the paragraph selected, click on the point that is probably a 10 or 12. The standard text in documents
varies between 9 and 12. For effect, we’ll select 14.
 10

Click on the down arrow to the right of the point size. The matching size in the drop down menu is highlight
ed. Either select 14 by moving the mouse pointer to 14, or type 14 when the number in the box is highlighte
d. Your standard choices are 8 through 72 but you can enter any number between 1 and 99.

Let’s decrease the size of the words within the parentheses to 8 point. Select the words within the parenthe
sis by going to the beginning or end of the phrase, clicking with the mouse and then going across the words
to select them all. Then click on the down arrow to the right of the 14 and select the number 8. The text with
in the parentheses will now appear smaller than the rest.

Apply Formatting in MS Word

● Last Updated : 06 Jun, 2021

Read

Discuss

In MS Word there are multiple types of formatting we can apply to a given text
such as Fonts, Font Styles, Font Colors, Font Sizes, Text Highlight Color
s, Clearing Formats, Change Cases, etc. Let us apply these techniques to s
ome texts in the Word Processor and see what result it produces.

Font

Fonts are used to change the display of the text or Word Document, look more
appealing, and as per the requirement of the situation. There are multiple type
 11

s of fonts available in MS Word-like: Calibri, Times New Roman, Algerian, Ari

al, Century, etc.

Steps to Apply:

Step 1: Select the text you want to change the font of.

Step 2: Now, select the font of your choice from the Home menu bar in MS W
ord.
 12

Result:

Font Style
 13

Font Styles are used to change the Look ability of the text or give more empha
sis on some texts in the Word Document. There are three types of font styles i
n MS Word: Bold, Italics, and Underline.

Steps to Apply Bold Font Style:

Step 1: Select the text you want to change the Font Style.

Step 2: Now, select the Font Style of your choice from the Font Style bar und
er the Home menu bar.
 14

Result:

Steps to Apply Italics Font Style:

Step 1: Now, select the Font Style of your choice from the Font Style bar und
er the Home menu bar.

Result:
 15

Steps to Apply Underline Font Style:

Step 1: Now, select the Font Style of your choice from the Font Style bar und
er the Home menu bar.

Result:
 16

You could also use the shortcuts like:

● CTRL+B for Bold

● CTRL+I for Italics
● CRTL+U for Underline

Font Color

Font Colors have wide use in real life when using the Word Document. Be it hi
ghlighting the main points of the document or want to convey some important i
nformation. There are multiple types of Font Colors available in MS Word-like:
Red, Blue, Yellow, Orange, Light blue, etc.

Steps to Apply:
 17

Step 1: Select the text you want to change the Font Color.

Step 2: Now, select the Font Color of your choice from the Font Color bar un
der the Home menu bar.

Result:
 18

Font Size

Font Sizes are used to display the text in the Word Document larger or smaller
according to our needs. We generally display a heading with a Font Size of 15,
Subheadings with a Font Size of 14 or 13, and paragraphs with a Font Size of
12. Many Font Sizes are available in MS Word-like: 8, 9, 10, 11, 12, 14, 16, et
c.

Steps to Apply:

Step 1: Select the text you want to change the Font Size.
 19

Step 2: Now, select the Font Size of your choice from the Font Size bar under
the Home menu bar.

Result:
 20

Text Highlight Color

As the name itself suggests, the Text Highlight Color is used to highlight text i
n the Word Document. There are many Text Highlight Colors available in MS
Word-like: Yellow, Bright Green, Turquoise, Pink, Red, etc.

Steps to Apply:

Step 1: Select the text you want to change the Highlight Colors.
 21

Step 2: Now, select the Color of your choice from the Text Highlight Color b
ar under the Home menu bar.

Result:
 22

Clear Formats

MS Word provides a feature to clear the pre-formatted text. When we copy so

me text or information from somewhere it has some formatting applied already,
so to remove it and use it according to our need in our Word Document we ha
ve to clear the Formatting.

Steps to Apply:

Step 1: Select the text you want to clear the formatting.

 23

Step 2: Now, select the Clear All Formatting bar under the Home menu bar.

Result:
 24

Change Case

Change Case is used to display the text in the way we want in our Word Docu
ment. There are five types of Change Cases available in MS Word-like: Sente
nce case, Lowercase, Uppercase, Capitalize each word, and toggle case.

Steps to Apply:

Step 1: Select the text you want to change the Case.

 25

Step 2: Now, select the case of your choice from the Change Case bar under
the Home menu bar.

Result:
 26

How to insert a header and footer in Microsoft

Word document
In Microsoft Word, Headers and Footers are used to insert additional information
such as title, file name, date, page numbers, etc. The presence of both header
and footer in the Word document makes your document more professional and
easier to read as well as understand.

Headers appear at the top margin of the Word document, while Footers appea
r at the bottom margin of the Word document.

To insert a header and footer in Microsoft Word

, follow the below given basic steps -

Step 1: Open the new or an existing Word document in which you want to insert
header and footer.

45.9M

781

History of Java

Next

Stay
 27

Step 2: Go to the Insert tab at the top of the Ribbon.

Step 3: Click on either header or footer drop-down menu in the Header & Foot
er section.

Note: In our case, we are going to use Header drop-down option.

Step 4: A Header or Footer drop-down menu will display on the screen with a list
of built-in Header or Footer options. Select your desired option from the Built-in
list.

Note: In our case, we select the Blank option.

 28

Step 5: A Design tab with Header & Footer option will appear at the top of the do
cument (on the Ribbon), as shown in the below screenshot.

Step 6: Type your desired information into the header or footer section.
 29

Step 7: Once you type your desired text in the Header section, click on Close Hea
der and Footer under the Design section on the Ribbon or press the Esc key from
the keyboard to remove the dotted underline. Now, you can see that the Header
is inserted to the Word document.

Insert the Date or Time in a Header or Footer

To insert the Date or Time in a Header or Footer, follow the below instructions -

1. Go to the Insert tab on the Ribbon and click on the Date & Time option in the
Text section.

2. A Date and Time dialog box will appear on the screen in which do the following
-
 30

● Select Date format from the Available format.

● Select your desired language.

● Tick on the Update automatically checkbox.

● Click on the OK button at the bottom of the dialog box.

Now, you can see that your selected format will appear on the Word document.

Edit Header and Footer in Word document

Once you create Header and Footer in Word document, you can also edit it base
d on your requirement.

There are the following steps to edit Header and Footer in Word document.

Step 1: Go to the Insert tab on the Ribbon and click on either Header or Footer
drop-down menu that you want to Edit.

Note: In our case, we are going to edit a Header, so we select Header drop-down option.
 31

Step 2: A Built-In Header option window will appear on the screen. Click on the
Edit Header option.

Step 3: Edit Header based on your requirement. Once you edit Header, click on t
he Close Header and Footer option at the top right corner of the document to dis
appear the blue dotted lines.
 32

Now, you can see that Header is edit based on your requirement.

Delete Header and Footer from Word document

To Delete Header and Footer from Word document, follow the below steps -

1. Go to the Insert tab on the Ribbon and click on the Header & Footer opti

on.

2. A Header or Footer dialog will appear on the screen. Click on the Remove

Header or Remove Footer option.

 33

Add a Drop Cap in MS Word

● Last Updated : 06 Jun, 2021

Read

Discuss

MS Word provides a special feature known as a drop cap. A drop cap is

a large capital letter that presents at the beginning of a text block or a

paragraph and has the depth of two or more lines of regular text. Or in
 34

another word, we can say that a drop cap is the largest capital letter o

r a word(according to the user requirement) which is used as a decorat

ive element at the beginning of a paragraph. Generally, the size of the

drop cap is two or more lines, but you can also customize the size and

spacing according to your needs.

Now let us discuss how to use drop cap step by step:

Step 1: Open MS Word and write a paragraph like as shown in the belo

w image:

Now we use the drop cap option on the first letter of this paragraph, i.

e., P.
 35

Step 2: Click on the INSERT, then go to the Text section and click on th

e Drop Cap button. A list will display(like as shown in the below image).

Now, this list contains four options:

● None

● Dropped

● In margin

● Drop Cap Options

Step 3: Select the Drop Cap option that you want:

● To create a Drop Cap that fits within your paragraph, select

“Dropped” option.
 36

● To create a Drop cap that is in the margin, outside of your par

agraph, select “In Margin” option.

● To remove the Drop Cap letter select the “None” option.

 37

● To customize the Drop Cap letter select Drop Cap Options.

How to Create and Use AutoTe

xt Entries in Word
 38

Creating and using custom AutoText entries for frequently used phrases is a
great way to speed up the content creation process in Microsoft Word. And t
he good news is you can set up these time-savers in just a few clicks!

0 seconds of 1 minute, 13 secondsVolume 0%

00:37
01:13

How to Create a New AutoText Entry

To get started, highlight the text in your Word document that you’d like to
use to create your new AutoText entry.

Press Alt+F3 to open the “Create New Building Block” window. Next, type
a memorable name (within the 32-character limit) for your AutoText entry, a
nd then click “OK.”

Your AutoText entry is now saved.

 39

RELATED: How to Add Shortcut Keys to AutoText Entries in Word

How to Use an AutoText Entry

To use your AutoText entry, place your cursor where you want to insert the t
ext in your Word document. Navigate to the “Insert” tab and click the Expl
ore Quick Parts icon in the “Text” group.

In the drop-down menu that appears, hover over “AutoText.”

Advertisement

A list of AutoText entries appears; select the one you want to use. For this ex
ample, we’ll use our custom entry.
 40

The text is inserted into your Word document.

How to Delete an AutoText Entry

If your AutoText list is getting a bit long, or you made a mistake when you cr
eated a custom entry, you can delete them.

To do so, go back to the “Insert” tab and select the “Text” group. Click th
e Explore Quick Parts icon, and then hover over “AutoText.”

THE BEST TECH NEWSLETTER ANYWHERE

Join 425,000 subscribers and get a daily digest of features, articles, ne

ws, and trivia.

Sign Me Up!
 41

By submitting your email, you agree to the Terms of Use and Privacy Policy.

Advertisement

Right-click the AutoText entry you want to delete. In the menu that appears,
click “Organize and Delete.”

The “Building Blocks Organizer” window appears with the AutoText entry
you right-clicked highlighted.

At the bottom of the window, click “Delete.”

 42

A message appears asking you to confirm that you would like to delete this e
ntry; click “Yes.”

The AutoText entry is deleted.

Auto Correction in Word

The AutoCorrect feature automatically corrects common typographical erro

rs when you make them. Let us learn how to use the auto correction option

available in Microsoft Word 2010 to correct the spelling automatically as you

type the words in your documents.

Setting AutoCorrect
The following steps will help to enable the AutoCorrect feature in Microsoft

Word.

Step 1 − Click the File tab, click Options, and then click the Proofing option a

vailable in the left most column, it will display the Word Options dialog box.
 43

Step 2 − Click the AutoCorrect Options button which will display the AutoCor

rect dialog box and then click the AutoCorrect tab. Now you have to make su

re all the options are enabled, especially the Replace Text as you type option.

It is also recommended to be careful when you turn off an option.

 44

Step 3 − Select from among the following options, depending on your prefer

ences.

S.N Option and Description

Show AutoCorrect Options Buttons

This option will be used to display a small blue button or bar benea

1 th text that was automatically corrected. Click this button to see a

menu, where you can undo the correction or set AutoCorrect optio

ns.
 45

Correct TWo INitial CApitals

2 This option changes the second letter in a pair of capital letters to l

owercase.

Capitalize first letter of sentences

3 This option capitalizes the first letter following the end of a senten

ce.

Capitalize first letter of table cells

4 This option will be used to capitalize the first letter of a word in a ta

ble cell.

Capitalize names of days

5 This option will be used to capitalize the names of the days of the w

eek.

Correct accidental usage of cAPS LOCK key

This option will be used to correct capitalization errors that occur

6
when you type with the CAPS LOCK key depressed and turns off thi

s key.

Replace text as you type

7
This option replaces typographical errors with the correct words as

shown in the list beneath it.

 46

Automatically use suggestions from the spelling checker

8 This option tells Word to replace spelling errors with words from th

e dictionary as you type.

Although Word comes preconfigured with hundreds of AutoCorrect entries,

you can also manually add entries using the following dialog box and use th

e Replace and With text boxes to add more entries. I added an entry for Mark

iting which should be replaced with Marketing. You can use the Add button t

o add multiple entries.

Step 4 − Click OK to close the AutoCorrect Options dialog box and again click

OK to close the Word Options dialog box. Now try to type Markiting and as s

oon as you type this word, Microsoft Word autocorrects it with the correct w

ord Marketing word.

Replacements

Next, you’ll see the Replace text as you type checkbox. This is what turns auto
correct on and off, so it’s good to keep in mind should you decide to no longer
use the feature. Check the box to use autocorrect and then continue on with te
xt replacements.
 47

Review the list of replacements near the bottom. On the left is the typed text an
d on the right is what replaces it. You’ll notice symbols, emoticons, and many
common words. You can add, edit, and remove replacements within this list.

● Add a replacement: Enter the way the text is typed under Replace
and the way you want it corrected under With. Click Add.
● Edit a replacement: Select a replacement in the list, edit the Repla
ce or With field, and click Replace. (The Add button will change to
Replace automatically.)
● Delete a replacement: Select a replacement in the list and click De
lete. You will not be asked to confirm, so be sure you want to rem
ove it.
 48

Use the Plain Text or Formatted Text buttons as they apply to your replacemen
ts. Formatted Text normally applies to things like symbols and emoticons wher
e the typed text is replaced with something like an image.

Check the final box in the AutoCorrect Options window if you want to automati
cally use suggestions from the spelling checker if you use spell check in Word.

Math AutoCorrect
If you type equations and use math functions in Word, you may also want to u
se the Math AutoCorrect tab.

This tab works pretty much like the AutoCorrect tab. You can view a list of repl
acements, add, edit, or delete them, and look at recognized functions.

If you want to use Math AutoCorrect outside of math regions, check that box.
Note that when Math AutoCorrect and AutoCorrect conflict, the AutoCorrect ru
les will prevail.

Also, like the AutoCorrect options, you can turn on Math AutoCorrect by check
ing the box for Replace text as you type.
 49

Use Autocorrect in Word to Your Advantage

Autocorrect really can be your friend when it comes to using it in Microsoft Wo
rd. And since you have more time to review a Word document than a quick text
message, you can adjust if necessary when something strange pops up.

Spell Check in Word

Microsoft Word provides a decent Spelling and Grammar Checker which ena

bles you to search for and correct all spelling and grammar mistakes in your
 50

document. Word is intelligent enough to identify misspelled or misused, as

well as grammar errors and underlines them as follows.

● A red underline beneath spelling errors.

● A green underline beneath grammar errors.

● A blue line under correctly spelled but misused words.

Check Spelling and Grammar using Review tab

Here is the simple procedure to find out the spelling mistakes and fix them −

Step 1 − Click the Review tab and then click the Spelling & Grammar button.
 51

Step 2 − A Spelling and Grammar dialog box will appear and will display the

wrong spellings or errors in grammar. You will also get suggestions to correc

t as shown below −

Now you have following options to fix the spelling mistakes −

● Ignore − If you are willing to ignore a word, then click this button

and Word ignores the word throughout the document.

● Ignore All − Like Ignore, but this ignores all occurrences of the sa

me misspelling, not just once but throughout the document.

● Add to Dictionary − Choose Add to Dictionary to add the word to

the Word spelling dictionary.

● Change − This will change the wrong word using the suggested c

orrect word.
 52

● Change All − Like Change, but this changes all occurrences of the

same misspelling, not just once but throughout the document.

● AutoCorrect − If you select a suggestion, Word creates an AutoCo

rrect entry that automatically corrects this spelling error from n

ow on.

Following are the different options in case you have grammatical mistake −

● Next Sentence − You can click Next Sentence to direct the gram

mar checker to skip ahead to the next sentence.

● Explain − The grammar checker displays a description of the rule

that caused the sentence to be flagged as a possible error.

● Options − This will open the Word Options dialog box to allow yo

u to change the behavior of the grammar checker or spelling opt

ions.

● Undo − This will undo the last grammar changed.

Step 3 − Select one of the given suggestions you want to use and click the Ch

ange option to fix the spelling or grammar mistake and repeat the step to fix

all the spelling or grammar mistake.

Step 4 − Word displays a dialog box when it finishes checking for spelling an

d grammar mistakes, finally Click OK.

 53

Check Spelling and Grammar using Right Click

If you will right-click the mouse button over a misspelled word, then it will s

how you the correct suggestions and the above mentioned options to fix the

spelling or grammar mistake. Try it yourself.

 54

How to Run Spell Check and Grammar

Check in Word Automatically

If you’re on Word for macOS, then the spell and grammar checks a
re probably running automatically. This means that spelling and gra
mmar errors are flagged while you type. On macOS, Word marks po
ssible spelling errors with a red squiggly line and potential gramma
r errors with blue double underlines.
 55

If not, follow these steps:

1. Opens the Word Preferences Dialog

Go to Word > Preferences….

2. Go to Spelling & Grammar

Click on the Spelling & Grammar button. The Spelling Grammar dial
og opens.

3. Activate Spelling and Grammar Check

Check the buttons for Check spelling as you type and Check gramm
ar as you type.
 56

You can also change other spelling and grammar settings in this dial
og box.

When you’re happy with the settings, click on the X in the corner o
f the screen to close it.

Word will now check your spelling and grammar and flag possible er
rors as you type in your document.

How to Add and Use Custom Dictionarie

s in Microsoft Word
If you have a lot of words you want to add to Word’s custom dictionary, you can man
ually edit the custom dictionary. You can also add third-party dictionaries with special
ized words, like medical and legal dictionaries. Today we’ll show you how to manuall
y edit the custom dictionary, create a new custom dictionary, and add third-party dicti
onaries.

Where are Word’s Custom Dictionaries?

 57

To access the custom dictionaries in Word, click the File tab. Then, click Options.

On the Word Options dialog, click Proofing on the left. Then, scroll down on the rig
ht and click Custom Dictionaries in the When correcting spelling in Microsoft Of
fice programs section.
 58

All available custom dictionaries are listed on the Custom Dictionaries dialog. You
can deactivate a dictionary by clearing the check box to the left of that dictionary.
 59

Manually Add and Remove Words in Word’s Custom Di

ctionary
To add and remove words in a custom dictionary, select the dictionary you want to e
dit in the Dictionary List on the Custom Dictionaries dialog. Then, click Edit Word
List.

Enter the word you want to add in the Word(s) box on the dialog for the selected dict
ionary and click Add.

To remove a word from the selected dictionary, select the word in the Dictionary list,
and click Delete. If you want to remove all the words from the dictionary, click Delete
All.
 60

To accept your changes and close the dialog, click OK.

Add Words to Word’s Custom Dictionary Using the Con

text Menu
You can also add words to the default custom dictionary when you have the setting e
nabled for automatically checking to spell as you type.

To enable this option, go to File > Options > Proofing, In the When correcting spe
lling and grammar in Word section, check the Check spelling as you type box. T
hen, click OK.
 61

You should also check to see which custom dictionary is the default, so words are ad
ded to the correct custom dictionary.

On the Custom Dictionaries dialog, the current default custom dictionary is labeled.
To change the default, select the dictionary you want to make the default and click C
hange Default.
 62

Now, when you right-click on a word marked as misspelled (with a red, squiggly und
erline), go to Spelling > Add to Dictionary.

Add a Third-Party Custom Dictionary

 63

If you write special types of documents, like medical or legal documents, you can ad
d a third-party dictionary to Word. This allows you to quickly and easily add many sp
ecialty words to Word for use when checking spelling.

To add a third-party dictionary, open the Custom Dictionaries dialog. For our exam
ple, we’re going to add the Raj&Co free medical dictionary.

A custom dictionary must be selected to be able to add a custom dictionary. Select a

ny of the dictionaries in the list and then click Add.

On the Add Custom Dictionary dialog, navigate to the location of the third-party cus
tom dictionary you want to add. Then, select the .dic file and click Open.
 64

The added dictionary displays in the Dictionary List. Click OK.

Add a New Additional Custom Dictionary

As we’ve shown, you can have multiple custom dictionaries that Word uses when ch
ecking spelling. In addition to third-party dictionaries, you may want to split up your o
wn custom dictionaries. For example, maybe you want one dictionary for personal us
e and one for work.
 65

To create a new custom dictionary, open the Custom Dictionaries dialog, and then
click New.

The location for the custom dictionaries is C:\Users\<username>\AppData\Roamin

g\Microsoft\UProof (change “<user name>” to your Windows user name) by default
and this directory is selected on the Create Custom Dictionary dialog box. Enter a
name for your new custom dictionary in the File name edit box and click Save.

NOTE: You must keep the .dic extension on the file name.
 66

Add words to your new dictionary as you did when manually adding words to an exist
ing dictionary. Click Edit Word List, add your words, and then click OK on both dialo
gs.

Remove a Custom Dictionary

If you ‘re not using a custom dictionary anymore, you can remove it from Word.

Open the Custom Dictionaries dialog, select the dictionary you want to remove, and t
hen click Remove.

NOTE: The .dic file is NOT removed from your hard drive, only from the list of custo
m dictionaries in Word.
 67

Change the Language Associated with a Custom Dictio

nary
When you add or create a new custom dictionary, Word associates all languages wit
h that dictionary, which means the dictionary is used to check spelling in any languag
e. If you want to associate a specific language with a custom dictionary, you can cho
ose the language for that dictionary. Word will then use that dictionary when you spel
l check a document written in that language.

To change the language associated with a custom dictionary, open the Custom Dict
ionaries dialog. Select the dictionary for which you want to change the language in t
he Dictionary List and then select the language you want from the Dictionary lang
uage drop-down list.

Click OK on the Custom Dictionaries dialog and then again on the Word Options
dialog.
 68

Back Up Your Custom Dictionaries

It’s a good idea to back up your custom dictionaries in case you need to transfer the
m to another computer or copy them back to the same computer if you need to reinst
all Office.

Copy the files in the C:\Users\<user name>\AppData\Roaming\Microsoft\Uproof

(change “<user name>” to your Windows user name) directory to another drive. The
n, you can simply copy these files back to the same directory (or equivalent user dire
ctory) to restore them.

Perhaps one of the most important things you can learn in Microsoft Word is how

to format your page with elements such as margins and page breaks. Formattin

g your pages makes them look more attractive and makes them easier to read.

Plus, it's easy to do with Word 2016.

 69

Page Margins

Page margins are the white space around the top, bottom, left, and right of your

document. Margins let Word know where to start placing text at the top of a doc

ument, when to move on to the next page at the bottom, where to start typing t

ext on the left side, and where to stop and move to the next line on the right.

Changing or setting page margins in Word 2016 only requires a few simple steps.

It really doesn't take any time at all once you know where to go and what to do.

To change or set the page margins, click the Layout tab on the ribbon, then go t

o the Page Setup group.

Click the Margins button. It looks like this:

Normal margins are one inch margins at the top, bottom, left, and right.
 70

To set your own margin, click Custom Margins at the bottom of the dropdown m

enu.

You will then see the Page Setup dialogue box.

Enter the margin measurement in inches for the top, bottom, left, and right mar

gins.

Click OK when you're finished.

How to Change the Orientation of a Page

When we talk about page orientation, there are two different orientations: landsc

ape and portrait. Landscape and portrait refer to how the document will be disp

layed on screen and printed. Choose Landscape and your document will be print

ed horizontally. This is the most common orientation. If you select Portrait, it will

be printed from top to bottom (vertically). This is best for professional photos of

several people. If your document contains several photos, you may want to cho

ose Portrait.
 71

To set the orientation, go to the Layout tab. Click the dropdown arrow below th

e Orientation button.

It looks like this:

You will be asked to select between Landscape and Portrait.

Change Page Size

Let's learn how to adjust the page size of your Word document.

The default page size in Word is 8.5 x 11, the same as standard printing paper.

However, for different types of documents, you may need to change the page siz

e.

To change the paper size, go to the Layout tab. Click the downward arrow below

the Size button.

Clicking on this will produce a drop down menu with different sizes that you can

choose from.
 72

Choose the size of your final document. You can choose legal, A4, credit card si

ze, etc.

Page Colors

Changing the page color is self-explanatory. To do so, click the Design tab and t

hen the Page Color button.

Simply select a color from the menu.

 73

To create your own color, click "More Colors."

Under the Standard tab, you can click on a color to select it, as shown above.

If you click the Custom tab, you can use the color picker by clicking your mouse

anywhere on the color that you want, then use the slider to adjust the color to cr

eate the exact shade you want.

 74

To add a gradient, texture, pattern, or picture, click "Fill Effects" in the Page Colo

r dropdown menu.

In the Fill Effects dialogue box, you can add a gradient, texture, pattern, or imag

e to use as a background for your document.

Borders and Shading

 75

Borders can be applied to an entire page, an entire document, or just certain sec

tions of the document. A border can also be applied to paragraphs.

To put a border around a page or a paragraph, go to the Design tab and click the

Page Borders button in the Page Background group. It looks like this:

When you click the Page Borders button, a new dialogue box opens.

Use this window to define the attributes of your border. You can choose to remo

ve a border by clicking the None box. Select a style, width, and color. You can ev

en select some predefined artwork from the Art dropdown box.

Using the Apply To dropdown, you can choose whether to apply these settings to

the entire document or just certain pages or sections.

Now take a look in the lower right hand corner of the window where it says "Opti

ons." Click that for even more control over your border and shading options.
 76

From here, you can choose the margins of your border, and whether or not to m

easure that from the edge of the page or the edge of the text. Click OK when yo

u're finished.

To add shading to a page, click Page Borders and then Shading from the Borders

and Shading dialogue window. Select a color from the Fill dropdown menu, and a

style. Click OK when finished.

Using and Formatting Multiple Columns

Want to learn more? Take an online course in Microsoft Word 2016.

By default, a blank document contains one column. The text begins at the left

margin and runs straight across the document to the right margin. However, if y

ou're creating a newsletter or brochure, you might want to add multiple columns

There are several benefits to using multiple columns. They can:

● Make your documents easier to read

 77

● Make them visually interesting

● Make them look professional
● Allow you to use space more efficiently

Below is an example of a document that was made more interesting simply by br

eaking the text up into columns.

Adding columns to a document is incredibly easy. Go to the Layout tab and click

the Columns Button in the Page Setup group.

Select how many columns you want to use in your document.

For precise control over the column width and spacing, click "More Columns…."
 78

In the dialogue box pictured above, you can adjust the width of the column, as

well as the amount of space between columns. You can also decide if you want

the columns to apply to the whole document or from the current point in the doc

ument forward.

Click OK when finished.

Insert Headers and Footers (Including Page Numbers)

A header appears at the very top of a document. It typically includes the page n

umber and title of the book.

A footer appears at the bottom of a page. It can also contain a page number.

If you want to insert header and footer information (page numbers, title, date, e

tc.) into your document, you first have to decide if you want headers or footers.

Remember, headers go at the top of the page. Footers go at the bottom.

To insert headers or footers, go to the Ribbon and click on the Insert tab.

Go to the Header & Footer Group, the click on either Header or Footer.
 79

We're going to choose Header.

After you click on Header, choose a header style from the dropdown menu, as pi

ctured below.

Once you select your style of header, it will take you back to your document whe

re you can then insert the header information, including title, page number, date,

etc.

The same steps are applied when you click on Footer.

 80

If you simply want to insert page numbers, then click the Page Number button t

hat is to the right of Footer button. A dropdown menu will appear which will allo

w you to choose where to place the page numbers.

Choose where you want to place the page numbers. You can also format and re

move page numbers.

NOTE: If all you want to do is add page numbers, this is quicker and easier to d

o then going through the Header or Footer dialogue.

Create a Page Break

A page break is the point where one page ends and another begins.

To create a page break:

● Go to the Insert tab.

● In the Pages group, click the Page Break button. It lo
oks like this:
 81

Insert a Cover Page

A cover page is inserted before the first page of your document. It contains infor

mation such as the name and author of the document. It can also contain image

and other graphics to make the page more attractive. Cover pages are commo

nly used for longer documents, such as reports and books.

To insert a cover page, click the Insert tab on the Ribbon, then click the Cover P

age button in the Pages group. It looks like this:

From the dropdown menu, choose a style for your cover page.

To edit the text on your cover page, hover your mouse over the placeholder text,

then click to see the cursor.

Type the text that you want to appear.

 82

If you want to remove the cover page, click the Cover Page button again and sel

ect "Remove Current Cover Page".

Insert a Blank Page

When creating longer documents, you may want to insert a blank page between

sections or chapters.

To insert a blank page, click the Insert tab on the Ribbon.

Select Blank Page from the Page group. It looks like this:

The blank page will appear after the current page.

Templates

Templates are documents that are already designed for you.

 83

When you use a template, all you have to do is replace the sample text and ima

ges with your own. They help you save oodles of time and keep your documents

consistent. In Word 2016, you can even edit the templates or create your own a

nd then use them again and again.

Changing the Default Document Template

Whenever you open a blank document in Word, you are opening a template--one

in which the margins, font, and themes are already defined. This is called the def

ault template. It's not as fancy as some of the templates you see pictured when

you go to File>New, but it's still a template. You can edit this template in the sa

me way you'd edit the fancier templates.

To make changes to the default template, let's take a look at the Font group und

er the Home tab.

 84

As you can see, the selected font is Calibri, size 11. This is the default font for W

ord 2016. But let's say you're a professional writer, and your editor is a stickler f

or old-school standards. That means he wants a fixed width font like Courier Ne

w, size 12, with double spacing. Since you write almost exclusively for this stodg

y old editor, you don't want to have to go in and change those values every time

you open Word.

The solution is easy. Click the little arrow at the bottom right corner of the Font

group to open the Font dialogue box. Click the Font tab.

Choose the font and size you want, and then click the "Set As Default" button in

the lower left hand corner of the window.

Another dialogue box will open, asking you if you'd like to set this font size and s

tyle as the default for this document, or all documents based on the Normal tem

plate.
 85

If you want to use 12pt Courier New every time you open a new document, selec

t "All documents based on the Normal template?" You can change these values a

nytime you like.

To change the default paragraph values, you click the box with the arrow in the l

ower right hand corner of the Paragraph section of the ribbon. You can change d

efault Margins in the Page Setup section, and so on.

Opening an Existing Template

Microsoft provides a number of templates to make your work easier. They can h

elp you create a budget, write professional letters, design flyers and for Microsof

t and more. If you are connected to the Internet, you can browse through and d

ownload literally thousands of existing templates from Office.com…without ever l

eaving Word 2016.

If you already have Word 2016 open, you can view the templates by clicking the

File tab, then going to New. You can then see the templates just as you did on th

e start screen.
 86

As you can see, the blank document template appears first, followed by a tour of

Word 2016. Next, you have a single spaced plain document. If you click on it to

open it, Word shows you the features of this template:

Click the Create button to create the template.

 87

Now, let's choose the Spring Event Flyer template, and open it up so we can sho

w you an example of a Word template.

Again, Word gives us information about this template.

Click the Create button. Word will load the template for you.

Search for a Template

 88

There are a lot of templates in Word. The thumbnails you see when you go to F

ile>New are only a small sampling.

Word 2016 gives you a few ways to search for a template you want. Go to File>

New. At the top of the screen, you'll see this:

You can type in the kind of template you want. Let's say we want a report.

Type in report.

Word then searches its online database for a report template, then shows you th

e results:

You can see all the results pictured on the screen. To the right, you'll see the cat

egory pane where you can search for a report template by category. Simply click

on a category to see its reports.

 89

You can also search for a template by clicking on one of the suggested searches

below the search box.

Create and Save a New Template

In addition to the thousands of templates available in Word 2016, you can create

and use your own. To do so, simply create the document and when you are finis

hed, then click File>Save As. Select the location you want to save the template t

o, then select "Word Template" or "Macro-Enabled Word Template" from the "Sa

ve as Type" drop down menu.

Change the attributes of the style to suit your preferences, then click OK.

Modifying a Template

Let's go back to our Spring Flyer Event template that we showed earlier:

Once you've opened a template, you can now make changes to it. You can chang

e the font size, font color, etc. if you want, and you can also customize the text t

o make the template your own document.

To edit the text, click on the text that you want to edit. It then selects the text, a

s shown below.
 90

Now simply type the text you want, as shown.

While the text is selected, you can also look in the Style gallery to see the style

applied to it.

You can modify the style to change the formatting.

Click on any image in a template, and you'll see a bounding box appear around it:
 91

You can then cut or copy the image.

If you want to change the colors used in the template (such as the border color

s), you can do that by changing the theme.

Working with Themes

Like templates and quick styles, themes are another way to achieve consistency

in your documents. Themes can include font styles, color palettes, and special ef

fects. What they do is give you a group of fonts, colors, and special effects that

work well together and even complement each other. This can be illustrated by t

aking a closer look at some of the themes.

Themes are located under the Design tab.

We can change the theme from the template above by simply choosing a new th

eme from the Themes dropdown menu.

 92

We can also choose a new color scheme by clicking the Color button on the far ri

ght side of the Document Formatting group and choosing a new one:

Let's choose Yellow Orange.

 93

That's all it took to change the colors in our template:

Applying a Document Theme

So far, we've talked about modifying or changing a template's theme. However,

you can also apply a theme to your documents that you create.

Before we learn how to do that, it's important to understand exactly what a the

me is, so you know how it applies to your text. A theme consists of three things:

Colors (of graphics and design elements, background color, text color, and hyper

links), fonts (headings and body text), and graphical effects such as 3-D, shadin

g, drop shadows, etc.

To apply a theme, select a theme from the Theme Gallery under the Design tab.

You can mouseover any theme to preview what it will look like in your document,

just like you did with styles. Simply click on a theme to apply it to a document.

Modify or Create a Theme

If you want to create a custom color theme (or a color scheme that you create in

stead of the ones Word has built in under the Color button), click the Color butto

n under the Design tab, then select Customize Colors.

 94

Choose the colors for the various elements, then enter a name for your new colo

r theme. Click Save when you're finished. This new theme will now appear with t

he other themes.

To create a custom font theme, click the Fonts button under the Design tab and

choose Customize Fonts.

Click Save when you're finished.

Mail Merge in MS Word

Many day-to-day applications require similar documents containing similar text
to be sent to a number of persons. These documents also have a typical com
mon layout. Invitation letters sent to guests have a more-or-less common cont
 95

ent and layout. Only the names of the recipients are different in these letters.
One obvious way to generate such letters is to type all of them individually, put
ting the same amount of effort again and again. Another solution could be to c
opy the same block of text again and again onto the new letters. The names a
nd addresses etc., which are different from each letter, can be entered separat
ely in the documents. Though this method saves a lot of effort, it still requires
proper caution. There should be a way where these kinds of documents can b
e prepared automatically. This task can be easily automated if we use the mail
merge feature of a word processor. So, it is time we learnt the mail-merge feat
ure of the word.
Components of mail merge:
The three main components of the merging process are the main document, t
he data source, and the merged document.
1. The main document contains the main body of your letter, field name
s, and merges instructions. The basic information within the main do
cument remains equivalent.
2. The data source (or Recipients’ list) stores the knowledge that chang
es for every document. This information is inserted in the main docu
ment one by one. An example of the data source is a name and addr
ess list from which the program gets what you want to include in the
main document.
3. The merged document contains the main text from the main docume
nt and data from a data source.

Steps for mail merger:

Step 1:

● Open MS Word and click on the command sequence: Mailings tab

→ Start mail merge group → Select recipients button → Type new

List.
 96

● A dialog namely “New Address List” will pop up(as shown in the be
low image). Type here the desired data under the given headings. T
o add a new record, click on the “New Entry” button at the bottom of t
he dialog and click OK when you are done.

Step 2: Prepare Master Letter

 97

The second step is to prepare our master letter for use in the mail merge. Bef
ore we enter all the letter text we’d like to link this Word file to our list of names.
● Create a blank word document.

● Click Mailings tab → Start Mail Merge group → Start Mail Merge →

Letters command.

● Then click the Mailings tab → Start Mail Merge group → Select Re

cipients button → Use Existing List command.

 98

● Now we can start typing the letter.

● Now we would like to add the name and address and other details fo
r the people on the list.

● Mailings tab→ Write & Insert Field group → Insert Merge Field but

ton.

● A pop-down will appear showing all the table headings, so choose Ti

tle and press the spacebar to create a space.
 99

Then do this again and choose FirstName, followed by a space (i.e., press onl
y spacebar key and no other key); then choose LastName but this time press t
he Enter key to create a new line. Then repeat the steps to choose the Addres
s field, and press enter key.

Step 3:
 100

Before we actually carry out the merge, we must first preview what the merge
d letters will look like.

● Mailings tab→ Preview Results group → Preview Results button

● Once we are happy with the preview, you can carry out the actual m
ail merge.

● To do this you click the Mailings tab → Finish group → Finish & M

erge button and choose Edit Individual Documents.

● In the Merge to New Document panel, click All to create a separate l

etter for each person on the Names list. Word then creates a fresh d
ocument with as many pages as there are names on your list, and ev
 101

ery page contains a wonderfully merged letter with all the correct indi
viduals’ details.

● We can save this with an appropriate name, such as ABC.docx

Sample Problems

Question 1: What is a mail merge?

Solution:
Mail Merge, a popular tool for personalizing printed letters, is nowadays also a
vailable for emails. Google Mail, Google Sheets make it happen for all Google
domain-based emails.

Question 2: what are the uses of mail merge?

Solution:
 102

● Writing a letter to a customer to tell them about upcoming offers or in

form about some changes in business context.
● Mailshot for sending out a survey to a large number of people.
● Invoices
● School names on to the certificate
● Personal

Question 3: Give the advantage of mail merge?

Solution:
● Once the merge has been found out , thousands of letters are often
produced very quickly.
● Easier to check for spelling errors as we need to check and correct a
t one place only; all letters will show the changes.
● Letters can be personalized.
● A standard letter can be saved and reused.
● In male merge, we can reuse the same data source, and it reduces t
he risk of errors.

Question 4: What are the main components of the Mail Merge process?
Solution:
● The main document.
● The data source.
● The merged document.

Question 5: How many files are created in Mail Merge?

Solution:
There are two files created in Mail Merge. The first file is called the source file
that contains the content of the main document and the second file is known a
s the data source file that contains the name, address and other important det
ails of the beneficiary.
 103

Question 6: Can we insert an attachment when performing a merge to an

e-mail message?
Solution:
No, we cannot insert an attachment when performing a merge to an email me
ssage.

Macros in Microsoft Word

A macro is a series of commands that is recorded so it can be playe
d back (executed) at a later time. Macros are great for reducing the
amount of work you have to do on a series of steps that you perfor
m frequently. Here's how to create and test a macro in Microsoft W
ord.
Instructions in this article apply to Word for Microsoft 365, Word 201
9, Word 2016, Word 2013, and Word 2010.

Why Use a Macro

With a macro, you can get the same results by clicking a command
instead of going through all the steps. Some ways to use macros to
increase your productivity include:
Insert your company logo and name in a particular typeface.
Insert a table that you need to create regularly.
Format a document with certain characteristics, such as page nu
mbering and double-spaced paragraphs.
Creating and using macros is a learned skill but the resulting efficie
ncy is well worth the effort.

Plan for Effective Word Macros

The most important step in creating effective Word macros is carefu
l planning. This planning includes having a clear idea of what you w
 104

ant the Word macro to perform, how it will make your future work ea
sier, and the circumstances under which you intend to use it.
Once you have these things in mind, plan the actual steps. This is i
mportant because the recorder will remember everything you do an
d include it in the macro. For example, if you type something and th
en delete it, every time you run that macro, Word will make the sam
e entry and then delete it, making a sloppy and inefficient macro.
When you plan your macros, here are some things to consider:
Plan the commands and the order in which you want the macro t
o perform the commands.
Know the shortcut keys for the commands you plan to use. This i
s particularly important for navigation as you aren't able to use
the mouse for navigation within the document area when you r
un the recorder. Further, you will create a leaner macro if you
use a shortcut key rather than the arrow keys.
Plan for messages that Word might display and that will stop the
macro.
Use as few steps as possible to keep the macro efficient.
Do at least one test run before you start recording.
After you’ve planned your Word macro and done a run-through, yo
u're ready to record it. If you’ve planned your macro carefully, recor
ding it for later use will be the easiest part of the process. The only
difference between creating a macro and working on the document i
s that you have to press a few extra buttons and make a couple of s
elections in dialog boxes.

How to create a macro in Word document

Macro is a series of commands and instructions that you combine together as a s
ingle command to complete a task automatically.

Follow the below methods to create a macro in Word document -

Method 1: Record a macro using the Button option

 105

Microsoft Word has the ability to record and run macros to improve the efficienc
y of the document.

Note: You can also add macros in Microsoft Excel.

There are the following steps to record a macro in Microsoft Word

Play Video
x

Step 1: Open the new or an existing Word document.

Step 2: Click on the View tab on the Ribbon and click on the drop-down icon ass
ociated with the Macros in the Macros section.

Step 3: Click on the Record Macro from the drop-down menu.

 106

Step 4: A Record Macro dialog box will appear on the screen in which do the follo
wing -

1. Enter the Name for the macro in the Macro name text field.

2. To use the same macro for the further document, click on the All Docume

nts (Normal.dotm) option from the Store macro in drop-down menu.

3. Click on the Button icon in the Assign macro to section to run your macro.

Step 5: A Word Options window will appear on the screen with highlighted Quic
k Access Toolbar at the left pane.

1. Click on the new macro name (Normal.NewMacros.MyMacro) on the lef

t side of the screen.

 107

2. Click on the Add button to add the macro on the Quick Access Toolbar at t

he right side of the screen.

3. Click on the Modify button, as shown in the below screenshot.

Step 6: Select macro that you want to record from the Modify Button dialog bo
x, type the Display name, and click on the OK button.
 108

Step 7: Click on the OK button at the Word options window.

Step 8: Now, click on the View tab and click on the Macros drop-down menu. Cli
ck on the Stop Recording Macro from the drop-down menu.

Now, you can see that recorded macro will appear on the Quick Access Toolbar.

Method 2: Record a macro using keyboard option

Follow the below steps to record a macro using keyboard option -

 109

Step 1: Open the new or an existing Word document.

Step 2: Go to the View tab -> Macros and select Record Macro from the drop-do
wn menu.

Step 3: A Record Macro dialog box will appear on the screen in which do the foll
owing -

1. Enter the Name for the macro.

2. To use the same macro for the further document, click on the All Docume

nts (Normal.dotm) option from the Store macro drop-down menu.

3. Click on the Keyboard icon in the Assign macro to section to run your ma

cro.
 110

Step 4: A Customize Keyboard dialog box will appear on the screen. Press any
combination of shortcut keys in the Press new shortcut key dialog box. Click o
n the Assign button.

Note: In our case, we are going to use the Ctrl+Shift+M key.

Step 5: Once you click on the Assign button, Pressed combination of keys will ap
pear in the current key text box. Click on the Close button, as shown in the below
screenshot.
 111

Step 6: Perform the action that you want to record.

Step 7: Click on the View tab on the Ribbon and select on the Stop Recording fr
om the Macros drop-down menu.

Run a macro

1. Go to View -> Macros and select View Macros from the drop-down menu.

2. A Macros dialog box will appear on the screen. Select a Macro list that you wa
nt to run from the Macro name:.
 112

3. Click on the Run button, as shown in the screenshot below.

Add a macro button to the Ribbon

Adding a macro button to the Ribbon helps you to easily access the macro in the
Word document.

Follow the below steps to add a macro button to the Ribbon -

Step 1: Click on the File tab at the top left corner of the screen. A list of File optio
ns will appear on the screen. Click on the Options tab.
 113

Step 2: A Word Options dialog box will appear on the screen. Click on the Custo
m Ribbon at the left pane of the screen.

Step 3: Click on the Macros under the Choose Command from the section. Click
on the New Tab and then Click on the Add. A macros icon will appear on the scr
een.
 114

Step 4: Right-Click on the Macros and select Rename form the drop-down men
u.

Step 5: A Rename dialog box will appear on the screen. Select the symbol accord
ing to your requirement. Enter the symbol name in the Display name text box an
d click on the OK button.
 115

Now, you can see that macro is added to the Ribbon.

Tables in MS Word
Microsoft Word or MS-WORD is a graphical word processing program that us
ers can type with. It allows the users to type and save documents very similar
to other word processors. There are many versions of MS-word in market, whi
ch the user can install as per te. In this tutorial, we will learn about the tables i
n MS-Word.
Tables in MS Word are made up of rows and columns with an organized arran
gement of text. These tables can be used to align numbers in columns and the
n various operations can be performed on them. Tables can also be used to cr
eate page layouts. Rows in a table are series of data banks laid out horizontal
ly in a table or spreadsheet. Columns are vertical series of cells in a chart, tab
le, or spreadsheet.

How to Create a Table?

Tables in MS Word can be created in the following two ways:
1. Using the Grid
 116

2. Using Table Dialogue Box

Using the Grid

Following are the steps of creating a table using the Grid provided in MS Word:
Step 1: Go to the Insert tab and click on the Table button.

Step 2: In the dropdown menu, select the number of rows and columns from t
he Grid.

Using Table Dialogue Box

Following are the steps of creating a table using Table Dialogue Box in MS W
ord:
Step 1: Go to the Insert tab and click on the Table button.
 117

Step 2: Under the grid, you will see an Insert Table button. Click on it.

Step 3: In the Insert Table Dialogue box, mention the number of rows and nu
mber of columns as per the requirement and click on OK button.

How to Modify a Table?

We can also edit/modify a table to make it more creative. Multiple operations c
an be performed on a table like changing the layout, splitting of cells, merging
the cells, applying borders, etc. Here, we will see some of the operations perfo
rmed on a table in MS Word.

Changing Layout of a Table

Changing the layout of a table can be done with the help of the following steps:
 118

Step 1: Select the table for which the layout is to be changed. Go to the desig
n tab.

Step 2: Click on the dropdown menu to get various different types of layouts f
or your table.

Step 3: Select any layout as per the need.

Splitting the Cell

Splitting of a cell can be done with the help of the following steps:
Step 1: Select the cell that you want to split into multiple cells. Then go to the
Layout tab and click on the Split Cells button.
 119

Step 2: In the dialogue box, mention the new dimensions as per the require
ment.

Step 3: Click on the Ok button.

Merging the Cells

Merging of various cells can be done by the following steps:

Step 1: Select all the cells that are to be merged into a single cell. Then go ov
er the layout tab, and you will see a Merge Cell button.
 120

Step 2: Now click on the Merge Cell button and the selected cells will be merg
ed.

Applying Borders and Styles on a Table

Borders and styles can also be applied to a table in a similar way as the layout
of a table is changed. Go through the following steps to do the same:
Step 1: Select the entire table and go over the design tab.

Step 2: Select the style that you want to apply to your table.
 121

Converting Text to a Table

MS Word allows the conversion of existing text into a table with the help of the
following steps:
Step 1: Select the text that is to be converted into the table. Now go to the Ins
ert Tab and you will see a Table button.

Step 2: Click on the Insert Table button and in the drop-down menu, click on t
he Convert Text to Table button.

Step 3: In the dialogue box, mention the dimensions of the required table and
other data that is required.
 122

Step 4: Click on the OK button and the selected text will be converted to a Ta
ble.

Inserting Images in a Table

MS Word allows adding images inside the table cells. To insert an image in a t
able, go through the following steps:
Step 1: Select the cell in which you want to add the Image. Go to the Insert ta
b and select the Pictures button.

Step 2: You can either choose a picture from your device or select one online.
Step 3: Choose a picture from the browser window and click on the Insert butt
on.
 123

Step 4: Selected Image will be added in the cell selected.

Performing Calculations in a Table

Mathematical calculations can also be performed on the values present in the

table. Microsoft provides various formulas to perform these operations. By def
ault, the sum of the values lying in the rows to the left or column lying above ar
e calculated by Word. Following are the steps to do the same:
Step 1: Select the cell in which the result of the mathematical operations is re
quired.

Step 2: Now go to the Layout tab and select the formula button.
 124

Step 3: In the dialogue box, define the formula for the mathematical operation,
the default formula is the sum of the values to the left or above.

Step 4: After defining the formula, click on the OK button to apply the formula
on the cells given in the formula.

file management and printing

Folder (or directory) – a virtual storage space used to store and organize co
mputer files.
 125

● A folder can also contain additional folders (known as subfolders).

● A folder has a name and is represented by an icon.

File – a storage unit on a computer that stores information, data, etc. (such as a document, an
image, etc.).

● Folders contain files

● Files have names and are represented by various icons

Filename – a unique name given to a file to identify it. A filename can contain letters, symbo
ls, numbers, spaces, etc.

Various file operations are —

● Opening a document
● Saving a document
● Save As by using Save As dialog box
● Closing a document
● Exiting Word

Basic file management refers to a way to name, save, backup, organize files/folders and kee
p track of files on a computer.

Some file management tips

● Create folders/subfolders to organize files.

● Save your work frequently in folders/subfolders (organize by category).
● Save your files on a backup disk regularly (back up your files).
● Give files and folders short, but meaningful names.
 126

Save a file for the first time or save a file with a different
name or location (Microsoft Word)

1. Click the File tab in Microsoft Word.

1. Click the Save As command.

1. Choose a location on your computer (e.g., USB drive or hard disk drive) to save your
file.
2. Type a name for your document in the File Name box.
3. Click the Save button.

Save an existing file in its current location (Microsoft W

ord)

1. Click the File tab.

2. Click the Save command.
3. Or click the Save button icon.
 127

Operate a Printer

Printer – a device that is usually connected to a computer in order to transfer the text and gra
phic information to paper.
 128

Operate a printer

1. Load paper in the paper tray if needed.

2. Press the Power button to turn the printer on.
3. Open the file you want to print.
4. Click the File tab.
5. Click the Print command.
6. Select the print options: Number of Copies, Print All pages, or Pages (e.g., from page
2 to page 5), etc.
7. Click Print or Okay to start the printing process.
 129

Save Save As

When you save a new document f This command always displays a dial
or the first time, Word displays a og box where you can choose a doc
dialog box (see figure, below). Se ument name and disk (see figure, b
lect where you want to save your elow). Use the Save As. command w
document and give it a name. Wh henever you want to save a copy of
en you save an existing document the current document under a differ
that you have been editing, the n ent name or in a different folder (or
ewly saved version is written over disk). The newly saved copy become
the older version. s the active document.
 130

UNIT 2

SPREADHEET

BASIC SPREASDSHEET ELEMENTS

WORKBOOK AND WORKSHEET Each speadsheet file is known as a workbook an
d is stored with a default extension of .xls. Each workbook can contain many sheets,
so various kinds of related information can be organised in a single file. Each workbo
ok can contain up to 255 worksheets, but by default it displays only three. Worksheet
is the area where the data is stored and work is performed. Extra worksheets can be
added as and when required.
ROWS, COLUMNS AND CELLS The rows in a worksheet are numbered from top to
bottom along the left column of the worksheet. The columns are labeled from left to ri
 131

ght with letters. The total number of rows in Excel are 65536 and the total number of
columns are 256. Columns are named from A to IV. The rows are numbered from 1 t
o 65536.
A cell is the intersection of a row and a column. A cell is identified by an address that
consists of the column name followed by the row number. For example, the first cell i
s referred to as A1, which indicates that it lies at the intersection of the column A and
row 1. This is the active cell. The active cell is ready for accepting any action or input.
A small group of contiguous cells is defined as a range. The range is referred to by
writing the starting address of the cell in the range: Ending address of the cell in a ra
nge or vice versa. For example A1:A10 (can also be referred as A10 : A1).

Opening a Spread Sheet

To open a spreadsheet, we have to click on "Start" button and go to "All Progra

ms" and "Excel".

132
 133

Opening an existing worksheet

To open an existing document, go to file menu, File → Open → Recent workbook

s.
134
 135

Renaming a work sheet

Every sheet is given a name by default as sheet 1, sheet 2, etc. It is necessary to c

ustomize the name according to user reference. To change this name, right click

on sheet tab that is to be renamed. Name in the sheet tab gets highlighted and c

an be edited.
136
 137

Organization of worksheet
Spreadsheet is made up of number of books. Each book contains number of colu

mns and rows. Rows and columns are made of many cells. The cell pointer in cell

A1 is as shown the below −

 138

Cell Address
Cell is a small unit in the worksheet which is used to store data. A cell is refered b

y its column and row number.

Example
Cell B followed by row 6 is called as B6. Each cell in worksheet has a unique addr

ess. Using arrow keys in the keyboard, we can move from one cell to another cell.
 139

Title bar
Title bar displays the name of excel worksheet. It appears at the top of all windo

w programs. By default, excel opens with the name Book 1.

 140

Menu bar
Menu bar is located directly below the title bar. It displays a list of menus that ca

n be used to give commands to excel. Clicking on a menu bar displays a drop do

wn menu of icons. You can move across the menu bar and scroll down menus wit

h your mouse by highlighting one of the menu items and using arrow keys on yo

ur keyboard.

● File − It has options such as: save, save as, open a new document, pr

int, etc.

● Home − It has icons to change font size, style, alignment, borders, et

c.
 141

● Insert − It has icons to insert table, chart, pictures, screenshot, head

er, footer, etc.

● Page Layout − It has icons to set margins, orientation, size, breaks, i

ndent, etc.

● Formulas − It has insert function, auto sum, date and time, lookup a

nd reference, etc.

● Data − It has icons to import data from web, from access, refresh all,

from other sources, etc.

● Review − It has icons for spelling and grammar check, thesaurus, wo

rd count, etc.

● View − It has icons to zoom, print layout, switch windows, split, etc.
 142

Formula bar
Cell content can be edited directly in the cells or in formula bar. If cell contains a

formula, it will be displayed here. If there is no formula, then content of the cell i

s displayed. Formula bar allows you to view, enter and delete data in a selected c

ell.

Mathematical formula appears in the formula bar when a cell that includes a for

mula is selected in the worksheet. In the below example, formula to calculate av

erage grades is in cell E5. When E5 is selected, formula appears in the formula ba

r.
 143

Printing a Spread Sheet

Printing is creating a hard copy of any content. A spreadsheet can be printed by s

electing File → Print option. Shortcut to print is "Ctrl + P".

144
 145

Saving Workbooks
After typing our content in excel worksheet, we must save the worksheet for futu

re use. Shortcut key to save is "Ctrl + S". The process of saving consists of followi

ng steps −

● Click File menu.

● Click Save option from sub menu.

146
 147

_____________________________________________________________
A spreadsheet is a computer application that is designed to add, display, anal
yze, organize, and manipulate data arranged in rows and columns. It is the mo
st popular application for accounting, analytics, data presentation, etc. Or in ot
her words, spreadsheets are scalable grid-based files that are used to organiz
e data and perform calculations. People all across the world use spreadsheets
to create tables for personal and business usage. You can also use the tool’s f
eatures and formulas to help you make sense of your data. You could, for exa
mple, track data in a spreadsheet and see sums, differences, multiplication, di
vision, and fill dates automatically, among other things. Microsoft Excel, Googl
e sheets, Apache open office, LibreOffice, etc are some spreadsheet software
Among all these software, Microsoft Excel is the most commonly used spread
sheet tool and it is available for Windows, macOS, Android, etc.
A collection of spreadsheets is known as a workbook. Every Excel file is called
a workbook. Every time when you start a new project in Excel, you’ll need to c
reate a new workbook. There are several methods for getting started with an
Excel workbook. To create a new worksheet or access an existing one, you ca
n either start from scratch or utilize a pre-designed template.
 148

A single Excel worksheet is a tabular spreadsheet that consists of a matrix of r

ectangular cells grouped in rows and columns. It has a total of 1,048,576 rows
and 16,384 columns, resulting in 17,179,869,184 cells on a single page of a M
icrosoft Excel spreadsheet where you may write, modify, and manage your dat
a.
In the same way as a file or a book is made up of one or more worksheets that
contain various types of related data, an Excel workbook is made up of one or
more worksheets. You can also create and save an endless number of worksh
eets. The major purpose is to collect all relevant data in one place, but in man
y categories (worksheet).

Feature of spreadsheet

As we know that there are so many spreadsheet applications available in the

market. So these applications provide the following basic features:
1. Rows and columns: Rows and columns are two distinct features in a spre
adsheet that come together to make a cell, a range, or a table. In general, colu
mns are the vertical portion of an excel worksheet, and there can be 256 of th
em in a worksheet, whereas rows are the horizontal portion, and there can be
1048576 of them.

The color light green is used to highlight Row 3 while the color green is used t
o highlight Column B. Each column has 1048576 rows and each row has 256
columns.
2. Formulas: In spreadsheets, formulas process data automatically. It takes d
ata from the specified area of the spreadsheet as input then processes that da
ta, and then displays the output into the new area of the spreadsheet accordin
 149

g to where the formula is written. In Excel, we can use formulas simply by typi
ng “=Formula Name(Arguments)” to use predefined Excel formulas. When you
write the first few characters of any formula, Excel displays a drop-down menu
of formulas that match that character sequence. Some of the commonly used f
ormulas are:
● =SUM(Arg1: Arg2): It is used to find the sum of all the numeric data s
pecified in the given range of numbers.
● =COUNT(Arg1: Arg2): It is used to count all the number of cells(it wil
l count only number) specified in the given range of numbers.
● =MAX(Arg1: Arg2): It is used to find the maximum number from the g
iven range of numbers.
● =MIN(Arg1: Arg2): It is used to find the minimum number from the gi
ven range of numbers.
● =TODAY(): It is used to find today’s date.
● =SQRT(Arg1): It is used to find the square root of the specified cell.

For example, you can use the formula to find the average of the integers in col
umn C from row 2 to row 7:
= AVERAGE(D2:D7)

The range of values on which you want to average is defined by D2:D6. The f
ormula is located near the name field on the formula tab.
 150

We wrote =AVERAGE(D2:D6) in cell D9, therefore the average becomes (2 +

3 + 4 + 5 + 6 + 7)/6 = 27/6 = 4.5. So you can quickly create a workbook, work
on it, browse through it, and save it in this manner.
3. Function: In spreadsheets, the function uses a specified formula on the inp
ut and generates output. Or in other words, functions are created to perform c
omplicated math problems in spreadsheets without using actual formulas. For
example, you want to find the total of the numeric data present in the column t
hen use the SUM function instead of adding all the values present in the colu
mn.
4. Text Manipulation: The spreadsheet provides various types of commands
to manipulate the data present in it.
5. Pivot Tables: It is the most commonly used feature of the spreadsheet. Usi
ng this table users can organize, group, total, or sort data using the toolbar. Or
in other words, pivot tables are used to summarize lots of data. It converts ton
s of data into a few rows and columns.

Use of Spreadsheets

The use of Spreadsheets is endless. It is generally used with anything that co

ntains numbers. Some of the common use of spreadsheets are:
● Finance: Spreadsheets are used for financial data like it is used for
checking account information, taxes, transaction, billing, budgets, etc.
● Forms: Spreadsheet is used to create form templates to manage pe
rformance review, timesheets, surveys, etc.
● School and colleges: Spreadsheets are most commonly used in sc
hools and colleges to manage student’s data like their attendance, gr
ades, etc.
● Lists: Spreadsheets are also used to create lists like grocery lists, t
o-do lists, contact detail, etc.
● Hotels: Spreadsheets are also used in hotels to manage the data of
their customers like their personal information, room numbers, chec
k-in date, check-out date, etc.
 151

Components of Spreadsheets

The basic components of spreadsheets are:

1. TitleBar: The title bar displays the name of the spreadsheet and application.

2. Toolbar: It displays all the options or commands available in Excel for use.
3. NameBox: It displays the address of the current or active cell.
4. Formula Bar: It is used to display the data entered by us in the active cell.
Also, this bar is used to apply formulas to the data of the spreadsheet.
5. Column Headings: Every excel spreadsheet contains 256 columns and ea
ch column present in the spreadsheet is named by letters or a combination of l
etters.
6. Row Headings: Every excel spreadsheet contains 65,536 rows and each r
ow present in the spreadsheet is named by a number.
7. Cell: In a spreadsheet, everything like a numeric value, functions, expressio
ns, etc., is recorded in the cell. Or we can say that an intersection of rows and
columns is known as a cell. Every cell has its own name or address according
to its column and rows and when the cursor is present on the first cell then tha
t cell is known as an active cell.
8. Cell referring: A cell reference, also known as a cell address, is a way for d
escribing a cell on a worksheet that combines a column letter and a row numb
er. We can refer to any cell on the worksheet using cell references (in excel fo
rmulae). As shown in the above image the cell in column A and row 1 is referr
ed to as A1. Such notations can be used in any formula or to duplicate the val
ue of one cell to another (by using = A1).
9. Navigation buttons: A spreadsheet contains first, previous, next, and last
navigation buttons. These buttons are used to move from one worksheet to an
other workbook.
 152

10. Sheet tabs: As we know that a workbook is a collection of worksheets. So

this tab contains all the worksheets present in the workbook, by default it cont
ains three worksheets but you can add more according to your requirement.

Create a new Spreadsheet or Workbook

To create a new spreadsheet follow the following steps:

Step 1: Click on the top-left, Microsoft office button and a drop-down menu ap
pear.
Step 2: Now select New from the menu.

Step 3: After selecting the New option a New Workbook dialogue box will app
ear and then in Create tab, click on the blank Document.
 153

A new blank worksheet is created and is shown on your screen.

Note: When you open MS Excel on your computer, it creates a new Workbook
for you.

Saving The Workbook

In Excel we can save a workbook using the following steps:

Step 1: Click on the top-left, Microsoft office button and we get a drop-down m
enu:
 154

Step 2: Now Save or Save As are the options to save the workbook, so choos
e one.
● Save As: To name the spreadsheet and then save it to a specific loc
ation. Select Save As if you wish to save the file for the first time, or i
f you want to save it with a new name.
● Save: To save your work, select Save/ click ctrl + S if the file has alr
eady been named.

So this is how you can save a workbook in Excel.

Inserting text in Spreadsheet

Excel consists of many rows and columns, each rectangular box in a row or co
lumn is referred to as a Cell. So, the combination of a column letter and a row
number can be used to find a cell address on a worksheet or spreadsheet. We
can refer to any cell in the worksheet using these addresses (in excel formula
 155

s). The name box on the top left(below the Home tab) displays the cell’s addre
ss whenever you click the cell.

To insert the data into the cell follow the following steps:
Step 1: Go to a cell and click on it
Step 2: By typing something on the keyboard, you can insert your data (In that
selected cell).

Whatever text you type displays in the formula bar as well (for that cell).

Edit/ Delete Cell Contents in the Spreadsheet

To delete cell content follow the following steps:

Step 1: To alter or delete the text in a cell, first select it.
Step 2: Press the Backspace key on your keyboard to delete and correct text.
Alternatively, hit the Delete key to delete the whole contents of a cell. You can
 156

also edit and delete text using the formula bar. Simply select the cell and mov
e the pointer to the formula bar.

Formatting in Excel?
Formatting in Excel means a trick that we can use to modify the data's appearan
ce in a worksheet. We can format the data in various ways, like we can format th
e font of the cells or the table with the help of the styles and format tab present i
n the Home tab.

It's easier than ever to format worksheet (or sheet) data in Excel. There are vario
us quick and easy ways to generate professional-looking worksheets that efficie
ntly present our data. For example, we can utilize document themes to give our E
xcel spreadsheets a consistent design, style to apply predetermined formats, an
d other manual formatting capabilities to highlight essential data.

Microsoft Excel has several features that permit users to customize the way their
data is displayed. And there is a solid reason for it: formatting cells can help brin
g attention to essential data or show the content more properly (such as adding
$ to cells which comprise price values or configure cells that represent dates to a
standard display of xx/xx/xxxx).

Excel formatting is an optional step following data preparation, or all of the data
cleansing, structuring, enriching, and standardizing necessary to prepare the
data for analysis.

47.7M

809

History of Java

New data rarely comes without its own unique set of issues; it is up to the analyst
s to analyze their data and guarantee that it is ready to meet the exact requireme
nts of their analytical project. Splitting columns, eliminating rows with incomple
te data, and standardizing against a certain name.

Once completed, Excel formatting adds the finishing touches, ensuring that the
data is properly prepared and presented.

Good formatting will improve our data in various ways:

 157

● With the help of the formatting, we can present our data correctly; for exa

mple, formatting as dates or currency will provide more value to our data.

● Merging and aligning our data is a vital aspect of making our data more re

adable.

● Formatting our text by increasing the Size, bolding, adding italics, or ch

anging the fonts will improve the overall appearance of our worksheet.

● Using styles (like table styles) can make our data stand out and helps the

reader to focus on crucial portions of the worksheet.

● Conditional formatting is a useful tool for highlighting crucial portions of

our worksheet graphically or visibly. These are dynamic tools. The Highlig

hted region changes as our data changes.

Benefits of Data Formatting in Excel

The following are some of the benefits of Excel data formatting:

● The data appears to be more presentable.

● Data formatting saves a lot of time and effort.

● With the help of the chart, we can analyze the data.

● With the help of the formatting, we can highlight specific data such as pro

fit or loss in business.

How to Format Data in Excel?

Let's have a look at some easy instances of data formatting in Excel. Suppose we
have a simple sales report for a company that looks like this:
 158

We need to format the data in this report because it is not attractive to viewers.

Now, in order to format data in Excel, we'll do the following things:

● The Font size is larger.

● We make the text of the column head bold

● Center aligning the data

● We will apply the outline border with the help of the shortcut that is (Alt+H

+B+T),

● After selecting the full table (using Ctrl+A), use the shortcut key (Alt+H+O

+I) to adjust the column width.

● To alter the background, use the 'Fill Color' command in the 'Font' group

on 'Home'.

We will use a similar format for the 'Total' row, which is the last row of the table,
with the help of the 'Format Painter' command, present in the 'Clipboard' grou
p on the 'Home' tab.
 159

Because the amount gathered is in a currency so, we have to format the same as
currency, using the command available in the 'Number' group which is placed on
the 'Home' tab.

After selecting the cells, that need to be formatted as currency, click the arrow a
bove to open the 'Format cells' dialogue box.

Select the 'Currency' option and click on 'OK.'

 160

We can also apply the outline border to the table.

Now our next task is to generate a label for the report. With the help of the Shap
es, we will make the report's label. If we want to create the shape above from th
e table, we'll need to add two new rows. To do so, we have to select the row by
'Shift+Spacebar' and then press 'Ctrl+'+" twice to insert two rows.
 161

Now we can insert the shape, to insert shape, go to the 'Shapes' command in th
e 'Illustration' group of the 'Insert' tab and select an appropriate shape.

Create the shape that meets the requirement and add text to it by right-clicking
on the shape and selecting 'Edit Text.'
 162

We may also utilize the 'Format' contextual tab to format the shape with comma
nds like 'Text Outline', 'Text Fill', 'Shape Fill', Shape Outline.' etc. We can also
use the excel formatting on text using the commands available in the 'Front' gro
up, which is placed on the 'Home' tab.
 163

Conditional Formatting can also be used to draw the visitor's attention to the
'Top 3' and 'Bottom 3' salespeople. To perform this, we have to select the optio
n Top/Bottom rules in the conditional formatting and then we have to select the
option named Top 10 Items.
 164

After that, the dialog box will open, and in this dialog box, we have to fill the num
ber for top ranks and the color we want.

Similarly, we can do this for Bottom 3.

We can also use other options of conditional formatting, such as Data Bars.
 165

We can also generate the chart to display the data, which is also part of "Excel D
ata Formatting.'

Shortcut Keys to Format Data in Excel

There are various shortcut keys to format data in Excel:

● Ctrl+2 or Ctrl+B, we can use this shortcut to make the text bold.

● Ctrl+3 or Ctrl+l if we want to make the text italic, then we can use this sh

ortcut.
 166

● Ctrl+4 or Ctrl+U if we want to underline the text, then this shortcut is help

ful.

● Alt+H, FA shortcut is used to open the 'Alignment' Dialog box.

● Alt+H, FN shortcut is used to open the 'Front' Dialog box.

● Alt+H, FK is used to make the font size of the text smaller.

● Alt+H, FG is used to make the font size of the text larger.

● Alt+H, B is used to add border.

● Alt+H, A then C is used to center align cell content.

● Ctrl+1 is used to open the 'Format Cell' Dialog box.

● Ctrl+Shift+Ampersand (&) is used to apply an outline border to the selec

ted cells.

● Ctrl+Shift+Percent (%) is used to apply the percentage format with no d

ecimal places.

● Shift+F8 is used to add a non-adjacent cell or range to a selection of cells.

● Ctrl+5 is used to apply or remove strikethrough Data Formatting Excel.

Note: While data formatting in Excel makes the title stand out, bold, and appealing, it als
o guarantees that it clearly relates to the content we're presenting. Then slightly increase
the column and row headings and color them in a different hue. Readers will rapidly sca
n the column and row headings to understand how the worksheet's content is organized.
This will aid them in determining the most crucial information on the and page and where
they have to start.

What is Format Cells in Excel?

 167

In Excel, the format cells is used to modify the formatting of cell numbers withou
t modifying the actual number. With the help of the format cells, we can change t
he number, alignment, font style, Border style, Fill options, and Protection.

We can access this option with right-click of the mouse. After right-clicking, a p
op-up will display, and then we have to click on the Format Cells, or we can also
use the shortcut key Ctrl+1 on our keyboard.
 168

Format cells has five tabs for formatting the cells. Using this, we can modify the d
ata style, Alignments, time style, insert the border with a different style, prote
ct the cells, etc.

Number Tab

Excel Number Tab is used to change the decimal formatting of number cells, off
ering the appropriate format in terms of number, date, percentage, fractions,
and so on.

With the help of the Number Tab, we can format the numbers as per our require
ments. We can select from various choices such as currency, dates, times, percen
tage, etc.
 169

Alignment Tab

The alignment tab can be used to align the cell's text and merge the text of two
cells together. If the text is hidden, we can use the wrap text to display it approp
riately, and align the text in the desired direction.

Within the Formal cells dialogue box, the Alignment tab allows us to specify how
our values are aligned, both horizontally and vertically.
 170

Font Tab

With the help of the Font Tab, we may alter the font size, font style, font color,
etc. we can change the font effects, we can underline the text, and preview how
it will appear.

Quick Font changes can be made from the home tab; however, the Formal Cells
dialog box is more efficient for mass changes. From there, we can easily modify t
he typeface, font size, italicize, point size, bold underlining, italicize, and, col
or across the whole selection of cells.
 171

Border Tab

By using Border Tab, we can create colorful border line for various styles; if we d
on't want to provide the border outline, we can leave it blank.

We can create borders around a single cell or a group of cells in Excel. We can ch
oose the thickness, color, and line style of the lines and where they will be crea
ted (for example, only on the cell's top or on all horizontal sides).
 172

Fill Tab

We may use the Fill Tab to fill a cell or a range with colors in various ways, combi
ne two colors, and even put a picture into a cell using the Fill option.
 173

Protection Tab

The Protection tab can be used to protect cell, range, cells, sheets, a formula c
ontaining, etc.
 174

The Protection tab doesn't apply unless we've already protected our worksheet.
To do this, click on Protection in the Tools menu, select Protect Sheet, and the
n select the Contents check box to determine how the worksheet will be protect
ed. When the Locked option is selected, we are forbidden to do the following:

● Modifying the data or formulas of a cell.

● Typing data in an empty cell.

● Moving the cell.

● Resizing the cell.

● Deleting the cell or its contents.

 175

When we choose the Hidden option, all the formulas used to calculate values wi
ll no longer be viewed in the formula bar (although we can still see the end result
of that formula).

Formatting in Excel Example 1:

We have the below-mentioned disorganized data, which appears to be quite stra
ightforward. Now we'll use Excel to format the data and show it in a readable fas
hion.

The following are the steps that must be followed when formatting data:
 176

● First, we have to select the header field and make it bold.

 177

● Then, we will select the entire data and select the option named "All Bord

ers" under the border.

● As a result, the data will appear as follows:

 178

● Now, we will choose the header field and use the "Thick box borders" op

tion under the border to create a thick border.

● After that, the data will appear as follows:

 179

● Next, we will make the header Field in the center.

 180

● Also, select a background color other than white. We'll use a light green c

olor here.

● Now the data appears more presentable.

 181

Formatting in Excel-Example 2
Let us understand more about formatting with the help of the following example.

In this example, we have a day-wise weather production of different cities.

Now we have to highlight the Friday data.

● First, we have to select the entire record of that data means the data of col

umn "F", and then will do the right-click. After that, the drop-down box
 182

will appear. We will select the option named "Format Cells".

 183

● The Format cells dialogue box will appear. As we can see, there are many

possibilities for changing the font style in various ways.

 184

● With the help of the Number button, we can modify the data type such as

percentage, decimal, date, time etc.

 185

● With the help of the Alignment button, we can align the font in a different

style.
 186

● Click on the Font button. There are numerous font styles options availabl

e for this button. We can alter the font type, size, style, and color, etc.

● In this example, we'll use a bold font size of 11 and the color green. By cli

cking on the Border button, we can also make the data's border more evi
 187

dent.

● Now our data looks like the screenshot below:

Formatting in Excel-Example 3
In this example. We're using the same data as before. In this, we will try to presen
t the data in a pictorial format.
 188

● Select the entire data set and go to the insert menu. Select Stacked Colu

mn from the 2-D column chart by clicking on Column Chart.

● Now our data looks like the below screenshot.

 189

Formatting Tricks for Excel Users

The majority of Excel users are familiar with basic numeric and text formatting. U
sers who go beyond the basics, on the other hand, will produce more readable a
nd effective sheets. Furthermore, knowing how to quickly and simply apply the a
ppropriate formatting to specific cells helps users work more productively. Thes
e formatting tips will help users in getting the most out of Excel's formatting feat
ures.

1. Copy Styles Between Workbooks

If we use similar cell styles in multiple workbooks, don't waste time re-creating c
ustom cell styles. Rather, copy the style from one file to another in the following
manner:

● First, we have to open the source workbook and a destination workbook.

● Then from the destination workbook, we have to click on the Cell Styles i

n the Styles group on the Home tab. In Excel 2003, select Styles from the

Format menu.

● Next, we have to choose Merge Styles at the bottom of the gallery.

● In the resulting dialog, select the open worksheet, which comprises the st

yles we need to copy.

● Click on OK twice.

Open Excel's default workbook, book.xltx. as the destination, if we want all new
workbooks to have the same custom design. (In Excel 2003, open book.xlt). After
we have added the style, save and close the template file. The unified styles will
be included in all new workbooks based on the book.xltx.

2. Add a Background Image

 190

It is so easy to add a background image to the sheets that we might be tempted t

o brighten up all our sheets. (Of course, we are not going to do that). To add an i
mage to the background of the sheet, perform the following:

● First, we have to click on the Page Layout tab.

● Then, we have to click on the Background option in the Page Setup grou

p.

● Lastly, browse to the file and double-click it.

3. Quickly Apply Table Formatting

If we select a range and choose a built-in format from the Format As Table dro
p-down, Excel (2007 and later) converts the range to a Table object. If the format
works for us, but we do not need the table object, we can keep the format and d
ump the table.

It takes a few clicks to do, but probably less than formatting it manually. To quic
kly format a data range using the built-in table format, do the following:

● First, we have to click anywhere inside the data range.

● Then on the Home tab, we have to click on the Format As Table drop-do

wn and select a format from the gallery.

● Select properly when Excel asks if the range has headers and click OK.

● Click anywhere within the table.

● With the contextual Design tab current, select Convert To Range in the To

ols group.

● Then we have to click Yes to confirm the action.

4. Create a Cell Style That Indicates Purpose

 191

The usage of a Cell Style to determine the purpose helps users in acclimating fas
ter. It's also a simple technique for a company to maintain uniformity. For examp
le, the color could be used to separate input and label cells. The use of a Cell Sty
le is a quick and easy technique to implement the rule. Make a Cell Style for inpu
t cells to demonstrate this concept:

● Click on the Home tab and then click Cell Styles group. In Excel 2003, sele

ct from the Format menu and skip to the 3rd step.

● Then we have to click on the New Cell Style at the bottom of the list.

● Enter a name for the style in the resulting dialog box, like Input cell.

● Then click on the Format. In Excel 2003, we have to click on Modify.

● Click on the Border tab and select the Outline option in the Presets secti

on.

● Select light blue from the Fill tab.

● Click OK to view the selected formats as shown below:

● Click OK again.

5. Change Colors in a Snap

Suppose we have made a spreadsheet model or dashboard. We also want to mo

dify the colors to something more vibrant. Simply select a color scheme from the
Colors box on the top left of the Page Layout ribbon. Microsoft has come up wit
h some fantastic color palettes. These are nicely contrasted and look fantastic o
n our computer screen. We can create our own color schemes as well (to match c
orporate style). Furthermore, we can set font schemes or combine the two to cre
ate a new theme.
 192

6. Use Format Painter

Format painter is a lovely tool that comes with all office programs. This is useful
for copy formatting from one place to another. See the below screenshot in orde
r to understand how it works. Format painter is located on the top left corner of t
he Home ribbon.
 193

After applying this formula, the output is:

 194

7. Formatting Keyboard Shortcuts

There are a number of shortcuts in Excel which we can use to format our data. Fo
rmatting is an everyday activity. We do it while composing an email, preparing a
report, creating a workbook, making a presentation or drawing something. Kno
wing a few formatting shortcuts will help us improve productivity. The following
are some formatting keyboard shortcuts:

● Ctrl+1: - Using this shortcut, we can open the format dialog for whatever

we've chosen (charts, drawing shapes, charts, etc.).

● Ctrl + B: - This shortcut is used to bold the text.

● Ctrl + U: - This shortcut is used to underline the text.

● Ctrl + I: - This shortcut is used to Italicize the text.

● ALT + EST: - We used this shortcut to paste formats. It works like the form

at painter.
 195

● Ctrl + T: - We used this shortcut to apply formatting to the current region o

f the cells.

● ALT + Enter: - While editing a cell, we can use this shortcut to add a new li

ne.

8. Use Templates

It may be enjoyable and challenging to develop our own spreadsheets, but there
is no reason to do so with so many pre-made templates available. For example,
we can get a C# spreadsheet library that allows us to read, modify, and create
spreadsheets without having to open Excel!

There are various templates for calendars, invoices, budgets, etc.

Move data in Excel

Moving your cells, rows, or columns is a common operation performed by ever
y Excel user in their day-to-day life.

Let's understand this using an example. Suppose I have a dataset (given below),
and I want to move the highlighted row to the last position of the Excel table.
 196

The common method that most of you will think of is by copying the content of t
he row, pasting the copied row to the desired location, and later deleting the con
tent from its original location.

But don't you think it's a bit of a lengthy process. So why not shift to a faster wa
y to move rows and columns in Excel. While working with Excel, you may regul
arly need to insert, move or delete cells. Whenever cells are inserted, moved, or
deleted, the existing cells shift, along with their data, to accommodate for the m
odifications to adjacent cells.

Play Video
x

In this tutorial, we will discover some methods that will quickly help you to move
your rows and columns from one place to another.

Move Data using Cut and Insert

 197

1. Select the data you want to move from one place to another.

2. Place your cursor on any of the selected cells and right-click on it. The foll

owing window will be displayed. Select the cut option.

 198

3. Next, choose the cell where you want to shift the data. For instance in the

below example I have chosen B9 cell.

 199

4. Again right-click on the selected cell, and from the window pane, select th

e Insert option-> Insert Copied Cells.

5. As you can see in the following output, the data will be moved to the desir

ed location.
 200

Move Data using Shift key

Moving data using the shift key is basically the same as cutting and pasting data,
and it delivers the same output. Both the Excel operations enable the users to m
ove large chunks of Excel data from one place to another. But move data using t
he shift key is essentially used to move the data over a short distance where
the mouse cursor could be easily dragged.

Let's see how we can easily move Excel rows and columns using the Shift key.

Move Rows in Excel

Let's suppose you have the following data entries in your Excel worksheet. You w
ant to move the highlighted row to cell B9.

Follow the below-given steps to move rows in Excel quickly:

 201

1. Place your cursor on the respective row to select it.

2. Press the Shift Key on your keyboard.

3. Move your mouse pointer to the end of the selected row. You would notice

that the cursor will change to the move icon (a four-directional arrow).
 202

4. You still require pressing the shift key and moving the cursor to the new l

ocation where you want to place the information.

5. The entire row will be cut from its original place and moved to the new all

otted location.

6. At this point, leave the mouse cursor and the Shift key.

Note: Though I referred to row, in the above example, I have selected some cells from th
e row. But by following the above steps, you can move an entire row as well, and it will q
uickly move the entire row to a new location.

Here are some key points one should know about the Excel 'Move' method:

● With the help of this method, one can only move contiguous cells, rows,

or columns. This method is not applicable for non-contiguous or adjacent

 203

cells, rows, or columns. For instance, you can't move rows 2 and 7 togethe

r, and you have to move them one by one. However, you can move rows 2

and 3 together.

● Whenever you move or shift cells in a row or column using the move shift k

ey method, it will not affect any other data (above or below / to the right

or left of the cell) in your Excel spreadsheet.

Move Columns in Excel

So far we learned how to move cells of a row from one place to another. Using th
e same method can also be applied for moving columns in Excel.

Follow the below-given steps to move rows in Excel quickly:

1. Place your cursor on the respective column (or adjacent columns) to selec

t it.
 204

2. Press the Shift Key on your keyboard.

3. Move your mouse pointer to the end of the selected row. You would notice

that the cursor will change to the move icon (a four-directional arrow).

4. You still require pressing the shift key and moving the cursor to the new l

ocation where you want to place the selected column. For instance, in the
 205

below example we want to move to column H.

5. The entire column will be cut from its original place and shifted to the n

ew allotted location.

6. At this point, leave the mouse cursor and the Shift key.

How to copy paste data in Excel

When we work with Excel worksheets, we may require the same data at multiple
places. Each time writing the same data takes a lot of time. So, it is good to use t
he copy and paste option in Excel. It will save your time and effort to do the same
task again and again.

Not only Excel but almost all text and image editors allow copying data and pasti
ng it to another place. For this, they all have a common copy and paste shortcut
command. Ctrl+C to copy the data and Ctrl+V to paste the data.
 206

In this chapter, we will take examples to brief the copy-paste options in Excel an
d how they are used in Excel.

Excel copy paste options

Excel enables the user to copy the data from one or more cell and paste to anoth
er cells, worksheet, or wherever you want. Besides the simple copy-paste operati
on, it also offers an option of special paste.

Play Video
x

Note: The simplest trick to copy and paste data is - Select targeted data + press Ctrl+C
+ go where you want to paste the data + press Ctrl+V.

Excel provides different types of paste options to paste the data differently like si
mple paste and special paste. We will show you all these methods in this chapter
with their detailed example.

The one biggest advantage of copy and paste option is that - it does not remove t
he data from where you have copied. Whereas, if you cut and paste the data fro
m one place to another, it removes the data from the previous place.

Step to simple copy-paste in Excel

 207

These steps are containing an example of the simple copy-paste of data. This dat
a does not contain any formula, formatting, or validation. It needs only a few sim
ple steps, same as other editors and you can easily copy the data to another plac
e.

Following are the steps to copy the data and paste it to another place by using a
simple copy-paste operation.

Step 1: We have this data stored in an Excel worksheet. Select the data which yo
u want to copy or use the Ctrl+A to select all data.

Step 2: We have selected the first four rows including header, to copy from this E
xcel sheet. You can also select a particular cell, row, or column data.
 208

Step 3: Now, use the Ctrl+C shortcut command to copy the selected data. (You c
an also copy the data by right-clicking on selected data and then choose the Cop
y command.)
 209

Step 4: Go to the place where you want to paste the data and take the cursor the
re, then use the Ctrl+V shortcut command to paste the copied data.

We have added a new sheet in the same Excel file to place the copied data.
 210

Step 5: You can see that the data is successfully pasted to another place without
any interruption. Similarly, you can paste the data wherever you want.
 211

Increase the size of the column width to make the data completely visible. Now,
save the file to keep the changes done here.

Note: When you copy and paste the data to another place, the original data does not re
move from the place you copied. It still remains at its location.

Step 6: You can also go back to the sheet from where you copied the data to see
that it is still there.
 212

It was an example of simple copy-paste.

Paste special method

The above steps we have described are only for simple data to copy-paste. A sim
ple copy-paste operation will not work if your data contains any formatting, for
mula, or validation applied to it. It would help if you used Paste Special option t
o copy-paste the values with formatting.

We will define the steps for it. This method will take more steps than the above e
xample. Following are the steps to copy the data and paste it with or without for
matting applied to it using Paste Special method to another place by using simpl
e copy-paste operation.

When to use this method?

For example, we have a dataset with formula, formatting, or conditional formatt
ing applied on this data. If you simply copy-paste this column value, cell referenc
e will be changed. This will cause that all values become 0 and formatting will als
o not keep as it is.

Hence, we will suggest you use the Paste Special method. We will use Paste speci
al option to keep the formatting even after pasting the data.

Examples
 213

In this chapter, we will show you three different examples to illustrate the usage
of the Paste Special method. With the help of them, you will better learn that wh
en and how to use Paste Special method.

1. Copy only values without color formatting

2. Copy with column width

3. Copy only values not formula

Learn these examples in detail one by one.

Copy only value without color formatting

Sometimes, we apply color formatting on Excel data to analyze the data based u
pon certain conditions. We use conditional formatting on Excel data for this. In c
ase If we need this data at another place but without color formatting, the simpl
e copy-paste method does not work. It copies the data along with color formatti
ng.

We can use Paste Special option to keep the only data without color formatting.
Copy the targeted data and paste it using Paste Special method. You have to cho
ose a format while pasting the data without formatting.

Let's see with an example to copy data without color formatting -

Step to copy and paste special

Step 1: We have the following data with color formatting in an Excel worksheet.
Select this data and copy it using the Ctrl+C shortcut command.
 214

Step 2: We want to paste this data into a new worksheet. So, press CTRL+N com
mand to create a new worksheet.

Step 3: Right-click here and choose Paste Special option from this list.
 215

Step 4: A Paste Special panel will open where select Value radio button under P
aste option and click OK.

Step 5: Now, you will see the copied data is pasted along column width and widt
h of the column has not destroyed as a simple paste.
 216

Copy with column width

You have seen that when you copy the data and paste it simply, the width of the
column is changed. We have to increase and decrease the width of the column m
anually. It looks something like this -
 217

We have a solution for it, i.e., Paste Special. You can copy and paste the data wit
h column width using it. Paste Special method enables several advance features
to keep the formatting and formulas as it is. It keeps the cell formatting same.

Let's see with an example to copy data with column width -

Example

Step 1: Copy the Product column data simply using the Ctrl+C shortcut comman
d.

Step 2: Go to the cell or worksheet where you want to paste the data.

Step 3: Right-click here and choose Paste Special option from this list.
 218

Step 4: A Paste Special panel will open where select column width radio button
under Paste option.

Leave other options as default and click OK.

Step 5: Now, you will see that it has been automatically adjusted the column wid
th according to the width of the copied data.
 219

Step 6: Now, use the CTRL+V shorted key to paste the copied data here in the sel
ected cells/column (Column F).

You can note that the width of the column has not been destroyed as simple past
e. Similarly, you can keep the formatting with the help of Paste Special option.
 220

Copy only value not formula

Sometimes, users only want to copy the value, not the formula applied to it. If w
e simply copy and paste the values from one place to another, formula applied t
o those values will also come with it. It might be possible that the cell reference c
hanges, and you got an incorrect value (0 or anything else).

For example - total price = Price*Quantity, i.e., D= B*C. It means column D value
comes with the multiplication of price and quantity.

See how formula come with it when we paste the data simply -

We have a solution for it, i.e., Paste Special. Use the Paste Special method to pas
te only value not the formula, along with it.

Let's see for its solution using Paste Special method -

Example

Step 1: Copy the Product column data simply using the Ctrl+C shortcut comman
d.
 221

Step 2: Go to the cell or worksheet where you want to paste the data.

Step 3: Right-click here and choose Paste Special option from this list.
 222

Step 4: A Paste Special panel will open where select Values radio button under P
aste option.

Leave other options as default and click OK.

Step 5: Now, you will see that copied data is pasted to column F successfully.
 223

Step 6: You can click any of the cells of column F (pasted data) and see that ther
e is no formula is applied on the pasted value now.

Now, these values are simple values with no formula.

Quick Excel Functions

Microsoft Excel, commonly called Excel, is powerful spreadsheet software that al
lows users to record large amounts of data sets and perform various arithmetic c
alculations. It is a versatile spreadsheet program for solving math problems, anal
yzing or comparing data, or making graphical representations of data. However,
 224

all such things in Excel are done with the help of existing functions and formulas
created by us.

This tutorial discusses the list of the most used essential quick excel functions, in
cluding the relevant examples. With this tutorial, we'll learn when to use the vari
ous functions and perform basic tasks quickly without any calculators or extra w
ork.

What is an Excel function?

An Excel function refers to a predefined formula that helps us perform a specific
operation on the recorded data within the worksheet. The functions contain fam
iliar names and eliminate the need to enter complex formulas manually. In Excel,
the formulas may contain one or more functions in the equation. Since the functi
ons are typically another form of formulas in Excel, they also start with an equal
sign (=).

Basic Excel Functions that we can use quickly in Exc

el
Excel has over 400 predefined functions, and this number steadily increases from
version to version with every upgrade. We usually don't need to memorize them
all. Instead, we can access the desired function using the function wizard. Howev
er, it is beneficial to know some quick Excel functions that we often need to use.
Below we discuss some basic but valuable quick Excel functions that we often ne
ed to use as a beginner:

Play Video
x
 225

SUM

The SUM function in Excel helps us add two or more values directly in an Excel ce
ll. Additionally, we can also pass the cell references, ranges, or the mix of argume
nts. The SUM function is the most basic built-in Excel function and requires at lea
st one argument.

The syntax of the SUM function is defined as below:

1. =SUM(number1, [number2], ...)

Where the number1 is the mandatory argument and may contain a number, cell
reference, range, or a mix of all three. Additionally, we can add more than one ar
gument as per our requirements.

Example: Suppose we have a list of month-wise sales data for any item, and we
need to calculate the total sales for the entire year. We can use the SUM function
and quickly add sales for each month. If our sales data is recorded in cells from B
2 to B13, we can use the SUM function in the following way to record the yearly s
ales in any resultant cell:

=SUM(B2:B13)

When the above equation is entered in cell B14, we get the addition of values in t
he corresponding cells, as shown below:
 226

Excel also provides another quick way to record the addition of column values or
rows of numbers. When we have values in an adjacent (contiguous) range, we ca
n go to the very next cell after the data and click on the AutoSum (Shortcut: pres
s 'Alt' and '=' keys simultaneously) button from the Home tab. This will immediat
ely insert the SUM function and calculate the SUM of all the previous cells with n
umbers.

AVERAGE

The AVERAGE function in Excel is another basic function that is often used by beg
inners, especially when learning the concepts of Excel functions and applying th
em within the worksheet. As the name suggests, the function helps us calculate t
he average value or arithmetic mean for the given numbers.
 227

Like other essential functions, the AVERAGE function also eliminates the need to
create a formula to calculate the average manually. Instead, we only need to pas
s the cells or a range, and results will be calculated accordingly in the resultant c
ell. The function performs all the calculations behind the scenes. It first adds all t
he given numbers and then divides the results by the total count of given numbe
rs.

The syntax of the AVERAGE function is defined as below:

1. =AVERAGE(number1, [number2], ...)

The syntax is almost similar to the SUM function, and it is mandatory to pass at l
east one argument in the function. We can pass arguments as numbers, cell refer
ences, range, or a mix of all three. We can pass more than two arguments or num
bers in the AVERAGE function as per our requirements.

Example: The application of the AVERAGE function is precisely similar to the SU

M function. If we take the same example data of month-wise sales again, the ave
rage in cell B13 can be calculated in the following way:

=AVERAGE(B2:B13)
 228

For the quick implementation of the AVERAGE function, we can select a cell next
to our contiguous data set and go to Home > AutoSum drop-down (under Editi
ng section) > Average.

COUNT

The COUNT function in Excel is another essential function used to count the total
number of cells in a given range that contains numeric values. Using the COUNT f
unction, we quickly know about the number/ count of numeric values (numbers)
entries. The COUNT function usually ignores data sets other than numbers.

The syntax of the COUNT function is defined as below:

1. =COUNT(value1, [value2], ...)

The first argument is mandatory, while we can add more arguments if required.

Example: In the previous example sheet, we have month-wise sales data. So, the
total number of cells that contain numbers is 12 because we have the month fro
m January to December. Let us now check the results using the COUNT function
by giving the range in the function as follows:
 229

=COUNT(B2:B13)

In the above image, we can see that the result is 12, the same as expected.

Suppose we enter any text value (i.e., "No Sales") in any of the cells of the range
B2:B13. So, the results change to 11 because the COUNT function ignores the cell
s with text values.
 230

We can apply the COUNTA function to measure all the cells in a range irrespectiv
e of the numbers or text values.

For the quick implementation of the AVERAGE function, we can select a cell next
to our contiguous data set and go to Home > AutoSum drop-down (under Editi
ng section) > Count Numbers. However, it only fetches the range of the adjacen
t cells with numbers. So, we must double-check the range within the function.

MAX & MIN

MAX and MIN are the two different basic functions in Excel and are quick to use w
ithin the desired cell. Both functions have almost the opposite operations. The M
AX function helps us get the maximum value in a given set of data, cells, or range
s. In contrast, the MIN function helps us get the minimum value in a specific rang
e. However, the syntax and the application process of MAX and MIN functions are
similar.

The syntax of the MAX function is defined as below:

 231

1. =MAX(number1, [number2], ...)

The first argument is mandatory, while we can add more arguments if required.

Example: When we apply the MAX function in our month-wise sales data, we get
the highest number of sales. We can use the MAX function for the range B2:B13 in
the following way:

=MAX(B2:B13)

This returns 1800, the highest/ maximum number in our range.

The syntax of the MIN function is defined as below:

1. =MIN(number1, [number2], ...)

Like the MAX function, the first argument in the MIN function is mandatory, while
we can add more arguments as per our requirements.
 232

Example: On the same example sheet, we can use the MIN function for the range
B2:B13 in the following way:

=MIN(B2:B13)

This returns 921, the highest/ maximum number in our range.

RANDBETWEEN

The RANDBETWEEN function in Excel lets us pick or record any random number f
rom the given set of numbers, and it only returns a random integer number. The
function is categorized under the Math and Trigonometry functions section. We
need to specify a range of numbers by passing a minimum and maximum numbe
r in a formula.

The syntax of the RANDBETWEEN function is defined as below:

1. =RANDBETWEEN(bottom, top)
 233

Here, we must specify the lowest number as the bottom argument and the highe
st number as the top argument in the formula.

Example: Suppose we have a list of 10 people, and we want to pick any random
person amongst them for any specific purpose (for example- choosing a winner r
andomly). We can use the RANDBETWEEN function and pass respective argumen
ts in the following way:

=RANDBETWEEN(1, 10)

This will return a random number between 1 and 10. Each time we apply it to the
cell, the value returned can be the same or different.

TRIM

When creating formulas in an Excel cell, we often encounter unexpected error co

des instead of the expected results. The formula returns errors due to several rea
sons. One of the most common causes of formula errors may be the presence of
unnecessary spaces in a cell. Therefore, we may need to remove such unwanted
spaces in our worksheets. However, deleting the extra spaces within the large da
 234

ta set might be somewhat tricky if we try to check and delete them manually. Thi
s is where the TRIM function is helpful.

Although there are multiple ways to delete extra unwanted spaces from our wor
ksheet, the TRIM function makes it more accessible. The function eliminates all e
xtra spaces in given cells but retains a single space character between two or mo
re words.

The syntax of the TRIM function in Excel is defined as below:

1. =TRIM(text)

We must pass the desired cell reference where we want to remove extra spaces.

Example: Suppose we have a list of data in column A where we see extra spaces i
n values. We apply the TRIM function in the following way:

=TRIM(A2)

After applying the formula in the first resultant cell B2, we copy the formula in th
e other cells below.

The above image shows how the TRIM function has removed the extra spaces fro
m the data in column A and recorded the resultant values in the following colum
n B.

LEN
 235

The LEN function in Excel helps us know the total number of characters present i
n the given cell. It is essential to note that the function counts all the characters,
whether numbers, text or symbols. Also, the space characters are included in the
count results of the LEN function.

The syntax of the LEN function is defined as below:

1. =LEN(text)

We must pass the desired cell reference where we want to count the total charac
ters.

Example: In the previous example data, we apply the LEN function in the followi
ng way:

=LEN(A2)

The formula is copied to other cells below in column B, where cell references cha
nge accordingly.

In the above image, the function calculates the total characters in values recorde
d in column A and records them in the subsequent column B.

CONCATENATE

The CONCATENATE function in Excel helps us quickly combine values from two o
r more cells into a new cell.
 236

The syntax of the CONCATENATE function is defined as below:

1. =CONCATENATE(text1, [text2], ?)

Where the 'text1' is the mandatory argument; however, we can pass optional arg
uments if required.

Example: Suppose we want to combine values from cells A1 and B1. We can appl
y the CONCATENATE function in the following way:

=CONCATENATE(A1,B1)

If we want to separate the combined values using the space, we can pass the spa
ce character as an argument in the CONCATENATE function in the following way:

=CONCATENATE(A1," ",B1)

TODAY & NOW

In Excel, the TODAY and NOW are two different functions where the TODAY functi
on helps us return the current date while the NOW function returns the current ti
me and time both. Both functions are helpful if we don't remember the date or ti
me but need to use them within the Excel cells. The beauty of these two function
s is that we don't even need to pass any arguments in them.
 237

We can use the syntax of TODAY and NOW functions in the following ways, respec
tively:

=TODAY( )

=NOW( )

Example: The following image shows how the TODAY and NOW functions are ap
plied to record the current date and time in cells:

Quick Functions accessible from the Status Bar

Some of the functions discussed above can also be added to the status bar locat
ed at the bottom of the Excel window. Adding such functions to the status bar he
lps us see those functions' results without even applying them in our excel sheet.
Instead, as soon as we select cells or ranges in an Excel sheet, the results of the a
dded functions automatically appear on the status bar.

The following image shows the AVERAGE, COUNT, MINIMUM, MAXIMUM, and SUM
for the selected range of cells.
 238

We can choose between the available functions to add to our status bar. We have
to press the right-click button on the status bar and select the function to add. A
tick mark/symbol appears when we select a function to add to our status bar. It l
ooks like this:

Important Points to Remember

● When using Excel functions, we must ensure not to enclose numbers withi

n double quotes. If we use double quotation marks around the numbers, E

xcel will treat them as text values, which can lead to unexpected formula r

esults.
 239

● It is recommended never to format the numbers used in the Excel function

s.

● We must ensure that each opening parenthesis has the ending parenthesi

s, especially when there is more than one set of parenthesis.

● Although we can copy-paste functions from one cell to another, we must e

nsure that the cell references are correct. It is better to use absolute refere

nces when there is a need to copy the functions/ formulas.

Excel formula
Excel formula refers to the formulas used for various calculations. Let's first disc
uss the concept of formula.

What is the formula?

A formula is a set of mathematical elements that specify a rule. It connects one o

r more elements with an equal sign. The formula can be used to find the elemen
t's value if one or more elements are known.

For example,

Sum = a + b

50.7M

910

Prime Ministers of India | List of Prime Minister of India (1947-2020)

If we know the value of a and sum, we can find the value of b (b = Sum - a). Simila
rly, if we know the value of a and b, we can find the sum (sum = a + b).

Or

Mean = Sum of all the observations/ Number of observations

 240

The above formulas consist of three elements. If we know the value of any two el
ements, we can easily find the value of third element.

What is a formula in excel?

The formula in excel is calculated based on the cells enclosed within the bracket
s of the function. It means that these formulas have two parameters, function n
ame and the cells declared in a function. For example, B + C + D find the sum of t
he range of values from B to D. The format of the functions defined in excel as a f
ormula is given by:

Function (cell range1: cell range2)

Where,

Function: It defines the predefined formula in excel, such as SUM and AVERAGE.
The names given to the function are familiar.

For example,

SUM (B1: B4)

Here, B1 and B4 are the cell range. The SUM() function will sum all the values fro
m B1 to B4. Similarly, other formulas in excel are defined based on the same con
cept.

Automatic search for formulas

Excel also provides us an option to find the available functions formulas in the fo
rm of a list. So, if anyone is not aware of the available formulas, it is best to find t
he available formulas in excel quickly. It is given by:

'Insert function'

We can find this function under the formula option present on the tool bar. To op
erate, consider the below steps.
 241

1. On the excel home page, click on the Formulas option present above the t

oolbar -> click on the Insert function option, as shown below:

2. A dialogue box will appear.

3. We can select the functions from the list, as shown above.

We can also search for a function by specifying the statement for the corre

sponding function. For example,

We need to find the minimum value, but there is no function present in the

function list. So, we can specify the statement 'to find the minimum valu

e' and press on the 'Go' option. The functions according to the specified st

atement will appear in the function table, as shown below:

We can use the appropriate function from the table to find the minimum v
 242

alue.

Similarly, other functions can be easily found by specifying the related stat

ement.

Note: Excel provides a detailed explanation of the given formula. It can be seen on the b
ottom of the function dialogue box. The statement related to the formula will also appear
when we type a function on the formula bar with an equal (=) sign mentioned at the begi
nning.

Formulas in Excel
Let's discuss the most common formulas in excel. We will also discuss examples
based on each formula.

1. SUM ()

The sum function is used as the addition function. It is used to add two or more t
han two numbers. The addition using excel is quick as compared to the calculato
r. We can add hundreds or thousands or more numbers easily using the sum func
tion.

Shortcut Method

The AutoSum symbol available on the toolbar can be directly used to add the sel
ected numbers in a single click. It is present on the toolbar under the formula tab.

Example 1: To find the sum of Price of various products.

 243

Consider the below steps to find the sum.

a. Click on any cell outside the given table.

b. Drag the mouse on the formula bar and type '=SUM(C5:C11).' Here, C5 an

d C11 are the name of the first and last element of the price column. Excel

will add the numbers from C5 to C11.

c. Press Enter. The sum will appear on the specified cell, as shown below:

OR
 244

a. On the excel home page, select the numbers to be added with the help of

a cursor.

b. Click on the AutoSum option under the formula tab, as shown below:

c. The sum will appear beneath the cell of the last element.

Example 2: To find the sum of specific elements of the column.

Consider the below steps:

a. Click on any cell outside the given table.

b. Drag the mouse on the formula bar and type '=SUM(C5:C7,C9,C11).' Here,

the sum will be calculated of cells C5, C6, C7, C9, and C11. The selected cel

ls for addition are shown below:

 245

c. Press Enter. The sum will appear on the specified cell, as shown below:

Note: We can type the formula either on the formula bar or on the specified cell. The for
mula will appear at both the places (formula bar and the specified cell).

2. Subtraction

It is similar to the addition process. We only need to insert a negative sign behind
the number we want to subtract.

The formula is given by:

SUM(cell1, -cell2)

For example,

SUM(A1, -A3)

The above formula will be used to subtract value of cell A3 from A1. It will be con
sidered as A1 - A3.

Let's consider an example.

 246

Example: To find the difference between the change in prices of commoditie

s.

Consider the below steps:

a. Click on the first cell of the difference column, as shown below:

b. Drag the mouse on the formula bar and type '=SUM(D3, -C3).' The value of

cell C3 will be subtracted from the value given in cell D3.

c. Press Enter. The difference between two specified cells will appear. It is sh

own below:

Similarly, we can find perform the subtraction of multiple cells.

3. Multiplication and Division

A particular name does not specify these formulas. We can directly use the multi
plication and division symbol between the two numbers to compute multiplicati
on and division.

It is given by:

A1 * A2

A1 / A2

Let, A1 = 4, and A2 = 2.

Multiplication = A1*A2 = 4*2 = 8

 247

Division = A1/A2 = 4/2 = 2

Let's understand it with an example.

Example: to find the multiplication of two values in columns A and B.

Consider the below steps:

a. Click on the first cell of the multiplication column.

b. Drag the mouse on the formula bar and type '=C3*D3.'

c. Click on the bottom-right corner of that block and drag to the last cell of t

he multiplication column.

d. Press Enter. The result will appear on all the specified cells of the column.

It is shown below:

Now, we will consider the same steps for the division. We only need to insert the
formula = 'C3/D3.' It is shown below:

4. LEFT, MID, and RIGHT

 248

These three formulas are used to break down the cell into different segments. Le
t's discuss this in detail.

LEFT

The LEFT formula is used to extract the starting elements from the specified cell.
It is given by:

LEFT(text, Number_of_characters)

Where,

Text refers to the specified cell from which we want to extract the elements.

Number_of_characters refers to the characters we want to extract from the star

ting from the left most character.

MID

The MID formula is used to extract the number of elements in the middle positio
n from the specified cell. It is given by:

MID(text, start position, Number_of_characters)

Where,

Text refers to the specified cell from which we want to extract the elements.

Start position refers to the position from where we want to start extracting.

Number_of_characters refers to the characters we want to extract.

RIGHT

The RIGHT formula is used to extract the number of elements from the last or rig
ht-end. It is given by:

RIGHT(text, Number_of_characters)

Where,

Text refers to the specified cell from which we want to extract the elements.
 249

Number_of_characters refers to the characters we want to extract starting from

the right-end character.

Let's consider an example for better understanding.

Example:

Consider the below table in excel.

The steps to extract the characters are as follows:

a. Click on the first cell of the First column, as shown below:

b. Drag the mouse on the formula bar and type =' =LEFT(B3,4).'

c. Press Enter. The extracted characters from the specified cell will appear.
 250

d. Drag the bottom-right corner of the first cell to the last element. It will no

w appear as:

e. Now, click on the first cell of the Middle column and type formula =' =MID

(B3,5,3)' and press Enter.

f. Similarly, click on the first cell of the Last column and type formula = '=RIG

HT(B3,1)'and press Enter.

g. Drag the bottom-right corner of the first cell to the last element of both Mi

ddle and Last column. The extracted characters will appear as:

5. AVERAGE

The average function is same as the mean function of mathematics. It is used to

find the average of the selected numbers or cells. We can calculate the average o
f multiple numbers easily.

It is given by:

AVERAGE(number1, number2,....)

In case of range of cells, we can specify it as:

 251

AVERAGE(cell1: cell2)

AVERAGE() is similar to the SUM()/Number of elements in a given column

For example,

AVERAGE(5, 10, 15, 20) = SUM(5, 10, 15, 20)/ 4

Shortcut Method

To access the average symbol, select the numbers with the help of cursor -> click
on the drop-down arrow in front of the AutoSum symbol under the formula tab
and click on the Average option. The desired average will appear beneath the se
lected number cells, as shown below:

We can also access more functions by clicking on the 'more functions' option at
the bottom.

Example 1: To find the average of the price of the given data table.

Consider the below steps:

 252

a. Click on any cell outside the given table.

b. Drag the mouse on the formula bar and type '=AVERAGE(C5:C9).' Here, th

e average will be calculated from the range C5 to C9 or (C5, C6, C7, C8, C9).

c. Press Enter. The average of the specified data will appear on the selected

cell. It is shown below:

The above example finds he average of the elements presents in a particular colu
mn. Let's find the average of the elements of a particular row.

Example 2: To find the average of a particular student of a class.

Here, we will find the average marks of the second student Dimple.

Consider the below steps:

 253

a. Click on the second cell under the Average marks column to find the aver

age of a student Dimple.

b. Drag the mouse on the formula bar and type '=AVERAGE(B6:F6).' Here, th

e average will be calculated from the range B6 to F6 or (B6, C6, D6, E6, F6).

The specified range is shown below:

c. Press Enter. The average of the specified data will appear on the selected

cell. It is shown below:

We can calculate the percentage of rest of the students by dragging downs

ide the bottom-right corner of the cell.

6. COUNT

Count function is used to count the number of data cells in the specified column
or row.

COUNT(number1, number2,....)

In case of range of cells, we can specify it as:

COUNT(cell1: cell2)
 254

Shortcut Method

To access the count symbol, select the numbers to count-> click on the drop-do
wn arrow in front of the AutoSum symbol under the formula tab -> click on the c
ount option. The desired count will appear beneath the selected number cells. It
is shown below:

Example 1: To find the count of the number of students in the given data.

Consider the below steps:

a. Select any cell near the given data table.

 255

b. Drag the mouse on the formula bar and type '=COUNT(C3:C14).' Excel will

count the cells from C3 to C14.

c. Press Enter. The counts will appear on the selected cell. It is shown below:

Similarly, we can count the number of elements in a row or column.

Note: he SUM(), AVERAGE(), and COUNT() can only be applied to the list of numbers. I
t cannot be applied to the data in the form of alphabets. If applied, it will not consider that
data or will return 0 as a result.

But, what to do if we want to count the cells with alphabets, such as names of st
udents, name of objects, etc. To resolve this, we have a function named COUNTA
(). It can count numbers, alphabets, etc. It means that it can count the cells irres
pective of the data inside it. It will also ignore the empty spaces in between.

COUNTA

COUNTA() function is similar as COUNT(). Unlike COUNT(), it can count the cells c
ontaining data other than the numbers. Let's consider an example.
 256

Example 2: To count the number of students with respect to their names.

In the above example, we have counted in terms of the marks of students. But, h
ere we will count with respect to the names.

Consider the below steps:

a. Select any cell near the given data table.

b. Drag the mouse on the formula bar and type '=COUNTA(B3:B15).' Excel wil

l count the cells from B3 to B15. The B15 cell is empty. Thus, excel will igno

re the extra space and will count the rest of the cells.

c. Press Enter. The counts will appear on the selected cell. It is shown below:

7. MAX and MIN

The MAX() function find the maximum value, and the MIN() function finds the min
imum value from the specified data of an array or table.

MAX(number1, number2,....)
 257

In the case of the range of cells, we can specify it as:

MAX(cell1: cell2)

In the case to find the minimum value, the formula is given by:

MIN(number1, number2,....)

In the case of the range of cells, we can specify it as:

MIN(cell1: cell2)

Shortcut Method

To access the MAX()/MIN() symbol, select the numbers-> click on the drop-down
arrow in front of the AutoSum symbol under the formula tab -> click on the Max
()/Min() option. The desired maximum or minimum number from the selected n
umbers will appear. It is shown below:

Example 1: To find the topper of the class.

The topper of the class would score highest marks among all the students. Thus,
we need to find the average marks from the list of available students.
 258

Consider the below steps:

a. Select any cell near the given data table.

b. Drag the mouse on the formula bar and type '=MAX(C3:C16).' Excel will fin

d the maximum value from the range of values between C3 to C16.

c. Press Enter. The maximum value will appear on the selected cell. It is sho

wn below:
 259

Thus, the student named Dimple is the topper of the class with the highest mark
s of 92.

Example 2: Find the student with least marks.

Here, we need to find the minimum average numbers of a student from the list of
students. The data is the same as in example 1.

Consider the below steps:

a. Select any cell near the given data table.

b. Drag the mouse on the formula bar and type '=MIN(C3:C16).' Excel will find

the minimum value from the range of values between C3 to C16.

c. Press Enter. The minimum value will appear on the selected cell. It is sho

wn below:
 260

Thus, the student named Mohan scored the least marks (55) in the class.

8. TRIM

The TRIM() function removes the empty spaces between the data in the given arr
ay. It is quite useful when we want to arrange the large data in a sequential order
without any extra spaces. But, the TRIM() formula can only be applied to a single
cell at a time. We can further calculate by dragging down the bottom-right corne
r of that cell.

The formula is given by:

=TRIM(cell)

For example, TRIM (A2)

Let's consider an example.

Example: To remove the extra space from the given data.

Here, we will use the TRIM() formula to remove the extra spaces.
 261

Consider the below steps:

a. Click on the first cell of the second column (C).

b. Drag the mouse on the formula bar and type '=TRIM(B3).' The TRIM() form

ula here will remove all the extra spaces between the words in the specifie

d cell.

c. Press Enter. The specified statement after the removal of extra spaces will

appear on the specified cell, as shown below:

d. Drag the bottom-right corner of that cell to the last cell of the column C. T

he TRIM() formula will be applied automatically to the other cells beneath

it, as shown below:

9. IF

The IF() function is used to sort the given data s per the logic. It means that excel
check the logic specified within the IF() function.
 262

The formula is given by:

IF(logic, [value_if_true], [value_if_false])

The above formula specifies that if the logic specified is correct, excel will return
the value specified in [value_if_true]. If the logic specified is incorrect, it will retu
rn

[value_if_false].

For example,

Let, A = 5, and B = 7 and the formula = IF(A >B, True, False)

Output = False

Explanation: The logic specified in the function is incorrect because A is less tha
n B. Hence, excel will return the value False.

If the formula is IF(A >B, 1, 0), it will return 0 because the value specified in the ca
se of false logic is 0.

But, there are some errors to be avoided using the IF() formula, which are listed a
s follows:

● The declared value in place of true and false, should either be a number or

alphabet. We can write it as IF(C2>D3, 1, False), but cannot write is as IF(C2

>D3, 1, False0).

● There should be no space between two or more numbers specified in plac

e of true and false. We can write it as IF(C2>D3, 12, False), but cannot write

is as IF(C2>D3, 1 2, False).

● We cannot use any other word in place of true and false. We can only use n

umbers without spacing.

Similarly, we can set the desired value to return in the above formula.
 263

Example: To find if price1 is greater or price 2 with the help of IF() formula.

Consider the below steps:

a. Click on the cell at the end of the first row, as shown below:

b. Type the formula '=IF(D3>C3, 1, 0)' on the specified cell.

c. Press Enter. If the statement is true, 1 will appear otherwise 0.

d. Drag the bottom-right corner of the cell to the last element of the table, as

shown below:

The corresponding result will appear on other cells as well.

It depicts that the statement with 0 depicts a false statement and 1 depicts a true
statement.

Similarly, we can implement multiple formulas using the 'automatic search for
formulas' method discussed above.
 264

Note: Remember to insert an equal sign (=) before every formula.

10. PROPER

The PROPER formula is used to organize the names or the words in a standard m
anner. It means that the first word in a cell must begin with a capital letter. In cas
e the first letter is small; the PROPER formula can be used to organize such word
s when there is a large volume of text present in excel.

Let's understand with the help of an example.

Consider the below steps:

a. Click on the first cell of the Proper column, as shown below:

b. Type the formula on the specified cell as' =PROPER(B3).'

c. Press Enter. The proper form of the given name will appear. It will be the s

ame word, but with the first letter as Uppercase. If the first letter is already

in uppercase, no changes will occur. Drag the cursor to the last element of
 265

the column, as shown below:

11. RANDBETWEEN

The RANDBETWEEN formula generates a random number between the specified

numbers. It is given by:

RANDBETWEEN(bottom, top)

For example,

RANDBETWEEN(1,10)

It will display a random number between 1 and 10. It can be any number. It is ge
nerally used to find a lucky draw number in excel among various students or na
mes in excel. We only need to type the formula in the form of '=RANDBETWEEN(b
ottom, top).' We can set any range as per the requirements.

12. CONCATENATE

The CONCATENATE formula can join two or more cells and combine it into one c
ell. The data to be combined can be in the form of alphabets or numbers. It is giv
en by:

CONCATENATE(cell1, cell2)

Let's consider an example.

The steps are as follows:

 266

a. Click on the first cell of the column concatenate, as shown below:

b. Type the formula '=CONCATENATE(B3, C3)' on the cell and press Enter. He

re, B3 and C3 are the specified cell numbers.

c. The specified cells in the formula will be combined together, as shown bel

ow:

Similarly, we can join multiple cells.

13. EVEN and ODD

The EVEN function rounds off the decimal value to a near even number, and the
ODD function rounds off the decimal value to the near-odd number. The same co
ndition applies when we are working with negative numbers. In case of an intege
r, it will also be converted to the nearest even or odd number.

It is given by:

EVEN (cell_number) and ODD (cell_number)

Here, cell number refers to the value that we want to convert into even or odd.

Let's consider an example.

Consider the below steps:

a. Click on the first cell of the even column.

 267

b. Type the formula '=EVEN(B3)' and press Enter. The rounded off value will

appear, as shown below:

c. Drag down the cursor to the last element of the table. All the specified valu

es in the table will be rounded off to the nearest even number, as shown b

elow:

d. Similarly, type the formula '=ODD(B3)' on the first cell of the odd column,

and press Enter. Drag down the cursor to the last element. The rounded v

alue will now appear as:

 268

14. TODAY

TODAY formula replaces the need to manually type the current date whenever w
e open the excel sheet. The formula automatically updates the current date on t
he specified cell.

It is given by:

TODAY()

We need to specify the above formula on the specified cell as '=TODAY()' and sim
ply press Enter.

We are not required to specify anything inside the parenthesis. Every time we op
en the excel sheet, the date will be automatically updated.

15. Converting Numbers into the currency

The method to specify the same amount in terms of dollars can be accomplished
with just a simple click. The shortcut method is given by:

Ctrl +Shift + $

The converted value will be complete in the form of commas, dollar sign, and the
decimal point.

Let's understand with the help of an example.

Consider the below steps:

a. Select the given data under the Price1$ column, as shown below:
 269

b. Press Ctrl +Shift + $.

c. The select values in the form of numbers will be converted into dollar for

m, as shown below:

Similarly, we can use Ctrl +Shift + % to convert the corresponding values in perc
entage. In such case, the values will be automatically multiplied by 100 with a pe
rcentage symbol.

Complex Formulas
The complex formula refers to the mixed use of addition, subtraction, multiplic
ation, and division formulas. We will simply compute the formula on the formul
a bar and excel will produce the desired result in few seconds. We can create mul
tiple complex formulas based on the above formulas.

Let's consider an example.

Example: To compute the equation and find out the value.

Consider the below steps:

a. Click on the first cell of the result column.

 270

b. Drag the mouse on the formula bar and type '=(C3*E3) + D3 - C3/D3 * E3.'

c. Press Enter. The result will appear on the specified cell, as shown below:

d. Drag the cursor of the first cell to the last cell of the result column. The res

ulted value will appear on all the specified cells, as shown below:

Note: The calculations will be performed based on the BODMASS rule in mathematics

Basic Terms in Excel

There are two basic ways to perform calculations in Excel: Formulas an

d Functions.

1. Formulas

In Excel, a formula is an expression that operates on values in a range of

cells or a cell. For example, =A1+A2+A3, which finds the sum of the rang
e of values from cell A1 to cell A3.

2. Functions
 271

Functions are predefined formulas in Excel. They eliminate laborious m

anual entry of formulas while giving them human-friendly names. For e
xample: =SUM(A1:A3). The function sums all the values from A1 to A3.

Key Highlights
● Excel is still the industry benchmark for financial analysis and mo
deling across almost all corporate finance functions. This course i
s designed to highlight some of the most important basic Excel for
mulas.
● Mastering these will help a learner build confidence in Excel and
move on to more difficult functions and formulas.
● There are also several different ways to enter a function in Excel, a
s shown below.

Five Time-saving Ways to Insert Data into Excel

When analyzing data, there are five common ways of inserting basic Exc
el formulas. Each strategy comes with its own advantages. Therefore, b
efore diving further into the main formulas, we’ll clarify those method
s, so you can create your preferred workflow earlier on.

1. Simple insertion: Typing a formula inside the cell

Typing a formula in a cell or the formula bar is the most straightforward

method of inserting basic Excel formulas. The process usually starts by t
yping an equal sign, followed by the name of an Excel function.

Excel is quite intelligent in that when you start typing the name of the fu
nction, a pop-up function hint will show (see below). It’s from this list
you’ll select your preference. However, don’t press the Enter key afte
r making your selection. Instead, press the Tab key and Excel will autom
atically fill in the function name.
 272

2. Using Insert Function Option from Formulas Tab

If you want full control of your function’s insertion, using the Excel Ins
ert Function dialogue box is all you ever need. To achieve this, go to the
Formulas tab and select the first menu labeled Insert Function. The dial
ogue box will contain all the functions you need to complete your finan
cial analysis.
 273

3. Selecting a Formula from One of the Groups in Formula Tab

This option is for those who want to delve into their favorite functions q
uickly. To find this menu, navigate to the Formulas tab and select your
preferred group. Click to show a sub-menu filled with a list of functions.

From there, you can select your preference. However, if you find your pr
eferred group is not on the tab, click on the More Functions option – it’
s probably just hidden there.
 274

4. Using AutoSum Option

For quick and everyday tasks, the AutoSum function is your go-to optio
n. Navigate to the Formulas tab and click the AutoSum option. Then cli
ck the caret to show other hidden formulas. This option is also available
in the Home tab.

5. Quick Insert: Use Recently Used Tabs

 275

If you find re-typing your most recent formula a monotonous task, then
use the Recently Used selection. It’s on the Formulas tab, a third men
u option just next to AutoSum.

Free Excel Formulas YouTube Tutorial

Watch CFI’s FREE YouTube video tutorial to quickly learn the most imp
ortant Excel formulas. By watching the video demonstration you’ll qui
ckly learn the most important formulas and functions.

Seven Basic Excel Formulas For Your Workflow

Since you’re now able to insert your preferred formulas and function c
orrectly, let’s check some fundamental Excel functions to get you start
ed.

1. SUM

The SUM function is the first must-know formula in Excel. It usually aggr
egates values from a selection of columns or rows from your selected ra
nge.

=SUM(number1, [number2], …)
 276

Example:

=SUM(B2:G2) – A simple selection that sums the values of a row.

=SUM(A2:A8) – A simple selection that sums the values of a column.

=SUM(A2:A7, A9, A12:A15) – A sophisticated collection that sums value

s from range A2 to A7, skips A8, adds A9, jumps A10 and A11, then finally
adds from A12 to A15.

=SUM(A2:A8)/20 – Shows you can also turn your function into a formul
a.

2. AVERAGE

The AVERAGE function should remind you of simple averages of data, su

ch as the average number of shareholders in a given shareholding pool.

=AVERAGE(number1, [number2], …)

Example:
 277

=AVERAGE(B2:B11) – Shows a simple average, also similar to (SUM(B2:

B11)/10)

3. COUNT

The COUNT function counts all cells in a given range that contain only n
umeric values.

=COUNT(value1, [value2], …)

Example:

COUNT(A:A) – Counts all values that are numerical in A column. Howev

er, you must adjust the range inside the formula to count rows.

COUNT(A1:C1) – Now it can count rows.

 278

4. COUNTA

Like the COUNT function, COUNTA counts all cells in a given rage. Howe
ver, it counts all cells regardless of type. That is, unlike COUNT that only
counts numerics, it also counts dates, times, strings, logical values, erro
rs, empty string, or text.

=COUNTA(value1, [value2], …)

Example:

COUNTA(C2:C13) – Counts rows 2 to 13 in column C regardless of type.

However, like COUNT, you can’t use the same formula to count rows. Y
ou must make an adjustment to the selection inside the brackets – for e
xample, COUNTA(C2:H2) will count columns C to H
 279

5. IF

The IF function is often used when you want to sort your data according
to a given logic. The best part of the IF formula is that you can embed fo
rmulas and functions in it.

=IF(logical_test, [value_if_true], [value_if_false])

Example:

=IF(C2<D3,“TRUE”,”FALSE”) – Checks if the value at C3 is less than

the value at D3. If the logic is true, let the cell value be TRUE, otherwise,
FALSE

=IF(SUM(C1:C10) > SUM(D1:D10), SUM(C1:C10), SUM(D1:D10)) – An e

xample of a complex IF statement. First, it sums C1 to C10 and D1 to D1
0, then it compares the sum. If the sum of C1 to C10 is greater than the s
um of D1 to D10, then it makes the value of a cell equal to the sum of C1
to C10.
 280

6. TRIM

The TRIM function makes sure your functions do not return errors due t
o extra spaces in your data. It ensures that all empty spaces are eliminat
ed. Unlike other functions that can operate on a range of cells, TRIM onl
y operates on a single cell. Therefore, it comes with the downside of ad
ding duplicated data to your spreadsheet.

=TRIM(text)

Example:

TRIM(A2) – Removes empty spaces in the value in cell A2.

 281

7. MAX & MIN

The MAX and MIN functions help in finding the maximum number and th
e minimum number in a range of values.

=MIN(number1, [number2], …)

Example:

=MIN(B2:C11) – Finds the minimum number between column B from B

2 and column C from C2 to row 11 in both columns B and C.

=MAX(number1, [number2], …)

Example:

=MAX(B2:C11) – Similarly, it finds the maximum number between colu

mn B from B2 and column C from C2 to row 11 in both columns B and C.
 282

Type of charts in Excel

It is sometimes difficult to interpret the Excel data due to complexity and size of
data. So, charts are a way to represent the data graphically and interpret the dat
a easily. Charts are the visual representation of data.

Excel provides charts to take advantage of graphical representation. The data re

presented through charts is more understandable than the data stored in an Exc
 283

el table. This makes the process of analyzing data fast. Excel users can fast analy
ze the data.

Graphical representation of data using charts makes complex data analysis easie
r to understand. Excel has a variety of charts, each with its own different function
ality and representation style.

Charts offered by Excel

Excel offers many charts to represent the data in different manners, such as - Pie
charts, Bar charts, Line charts, Stock charts, Surface charts, Radar charts, and m
any more. You can use them according to your data and analysis. All these charts

Play Video
x

There is a list of basic and advanced level of charts used for different purposes to
interpret the data.

1. Column Chart

2. Line Chart

3. Bar Chart

4. Area chart
 284

5. Pie chart or Doughnut chart

6. Surface chart

These are the most used charts of Excel that an Excel user usually requires.

Microsoft Excel introduce one more new chart called Treemap chart for 2016 an
d newer version. It has come with some advanced features and representation st
yles.

We will illustrate each chart and its functionality with an example in this chapter.
Learn carefully and use them accordingly.

Column Charts

A column chart is basically a vertical chart that is used to represent the data in ve
rtical bars. It works efficiently with different types of data, but it is usually used f
or comparing the information.

For example, a company wants to see each month sell graphically and also want
s to compare them. Column charts are best for it that help to analyze and compa
re each month's data with each other.

Excel offers 2D and 3D column charts.

Line Chart
 285

Line charts are most useful for showing trends. Using this chart, you can easily a
nalyze the ups and downs in your data over time. In this chart, data points are co
nnected with lines.

For example, a company wants to analyze the sell of products for the last five ye
ars graphically. Additionally, it also wants to analyze the ups and downs of each
year product sell.

Excel offers 2D and 3D line charts.

Bar chart

Bar charts are horizontal bars that work like column charts. Unlike column chart
s, Bar charts are horizontally plotted. Or you can say that bar charts and column
charts are just opposite to each other.

For example, a company uses the bar chart to analyze the data through vertical
bars to represent the data graphically. You can see as well as compare the values
to each other, respective to data.
 286

Area chart

Area charts are just like line charts. Unlike the line charts, gaps are filled with col
or in area charts. Area charts are easy to analyze the growth in business as its sho
ws ups and downs through line.

Similar to the line charts, data points in area charts are connected with lines.

Pie chart
 287

A pie chart is a rounded shape graph that is divided into slices of pie. Using this c
hart, you can easily analyze data that is divided into slices. It makes the data eas
y to compare the proportion.

Pie charts make it easy to analyze which values make up the percentage of whol
e. Pie chart is also known as Doughnut chart. Excel offers 2D and 3D pie charts.

Surface chart

Surface chart is actually a 3D chart that helps to represent the data into a 3D lan
dscape. These charts are best to use with a large dataset. This chart allows to dis
playing a variety of data at the same time.

A large dataset is not easy to represent using other charts. Surface chart solve thi
s problem that allows displaying large datasets using this 3D chart.
 288

Choose your charts wisely

Excel offers too many charts as well as their 2D and 3D type. You can use any of t
hem but choose them wisely according to your data. Different scenario requires
different charts. Though, it can display all and correct information.

We have a list of some points for each type of chart that helps you to choose the
chart wisely. Read them carefully -

Chart Type When to choose this chart

1 Column Cha Use the column chart when you want to compare the multip

. rt le values across a few categories. The values are shown thro

ugh vertical bars.

2 Line Chart Choose this chart when you want to show the treads (ups an

. d downs) over a period of time, like for months or years.

3 Bar Chart Like the column chart, use this chart to compare the values

. across a few categories.

In this chart, values are displayed in the horizontal bar.

4 Area chart Area chart has the same pattern as the line chart. This chart

. is best to use for indicating a change among different sets.

5 Pie or Dough Pie chart is best to use when you want to quantify the value

. nut chart s and show them as percentage.

6 Surface char Surface chart is different than other charts. Use it when you

. t need to analyze the optimum combination between two set

s of data.
 289

How to insert a chart?

Excel enables easy to use user interface using which you can easily insert a requir
ed chart for your data. You need to follow few simple steps, Excel > Insert tab > C
hart section > choose a chart.

We will illustrate these steps from start to end for creating the chart for Excel dat
a. Following are the steps to insert a chart in Excel.

Step 1: We have the following dataset (Animal population rate for six years from
2015-2020) for which you want to create a chart in Excel.

Step 2: Select the data, including column header and row label for which you wa
nt to create a chart. This data will be the source data for your chart.
 290

Step 3: Navigate to the Insert tab in the Excel header, where you will see a chart
s section that contains a list of all these charts.

Step 4: Choose a chart from here according to your data. We have chosen a 3D C
olumn chart containing vertical bars for your data.
 291

Step 5: The selected chart is inserted into your Excel worksheet. Initially, the cha
rt looks like this for the data selected in step 2.

Currently, this chart does not have a valid title, clear values for analysis, and mor
e. You can set up all these things in your chart by modifying it.
 292

Step 6: Double-tap on the Chart Title to make it editable and then provide a ne
w valid title that suites to it.

Here, Blue color vertical bar is representing to Dolphin, Orange vertical bar to Pe
nguin, and Grey vertical bar to White Bear population count.

Step 7: You can also define each vertical bar for its year so that the user can easil
y analyze the values. Click on the Chart Filters icon here.

Step 8: Click on the Select data present at the bottom of the list to replace the y
ears 2015 for each vertical bar.
 293

Step 9: Here, select the number 1 to replace it with year 2015 and click the Edit b
utton.

Step 10: Enter the year and click OK.

 294

Step 11: The year 2015 will immediately reflect on the chart, and all other becom
e blank. Now, to put all other years for each vertical bar, click one more time her
e.

Step 12: Add more years from 2016 to 2020 inside curly braces separated by a co
mma and click OK.

{2015,2016,2017,2018,2019,2020}
 295

Step 13: All values are now added. So, click OK.

Step 14: See the charts that the years are reflected on the chart correspond to ea
ch vertical bar.

You can see that the exact value is not defined at the end of each bar. Only the gr
aph is showing. Excel enables the users to choose a detailed bar.

Step 15: Choose another chart style for the Column chart for detailed descriptio
n from the Chart Style in the ribbon. We have chosen Style 2.
 296

Note: The Chart Style option enables only when the chart inserted in your Excel sheet is
selected. So, make sure chart is selected.

Step 16: You can now see the exact value is also showing for each bar in this char
t.

Similarly, you can insert, create, and modify other charts in your Excel worksheet
for your data.

Read a chart
 297

You should learn how to read a chart to understand and analyze the data presen
ted through it. It is most important to learn how to read a chart. A chart contains
several components, such as elements and parts that help you to interpret the d
ata.

This is one of the most important points because if you unable to read and under
stand the chart, you might misinterpret the value and analyze the data.

Excel Macros
MS Excel, or Microsoft Excel, is powerful spreadsheet software that most people r
egularly use for certain tasks, such as recording data or performing various math
ematical or analytical operations. While working with Excel regularly, users often
encounter scenarios when they repeatedly have to perform certain tasks every o
ne or two days. Doing the same tasks repeatedly from scratch takes time and eff
ort. However, we can perform some repetitive tasks using Excel with just a few cl
icks. This is where the Excel Macros feature comes into play.

This tutorial discusses the brief introduction of Excel Macros and other essential
topics like recording, saving, editing, and executing them at our convenience.

What is the Macros in Excel?

 298

Macros refers to an existing record and playback tool that helps us record an Exc
el action or a set of actions. These actions are saved as instructions in the form of
codes. We can execute the recorded actions as many times as needed and when
ever needed. Since all mouse clicks and keystrokes are recorded sequentially, Ex
cel executes them step-by-step on the given data. Furthermore, the recorded Ma
cro can be later edited to make minor changes to suit our needs accordingly.

Excel offers two different ways to create Macros. The first method involves a basi
c record and play process, executing them later to perform repeated steps or acti
ons. Another method involves coding with VBA (Visual Basic for Applications), re
quiring good coding knowledge.

593.8K

Vodafone has launches Pro II - The UK’s fastest WiFi tech

How to access Macros in Excel?

To enable or use Macros in our Excel workbook, we have to take advantage of th
e Developer tab. The Developer tab in Excel contains advanced options or tools,
allowing users to work with amazing hidden features. Unfortunately, the Develo
per tab itself is hidden in Excel by default. Therefore, we must first unhide the De
veloper tab and access the respective tools. On the Developer tab, we can access
amazing features like Macros, Add-ins, VBA (Visual Basic for Applications), etc.

Enabling a Developer tab is a one-time process and includes the following steps:
 299

● First, we need to go to the File tab and click Options from the menu list.

● We need to click the 'Customize Ribbon' from the left-side list in the Excel

Options window. This will display respective options in the right-side pane
 300

l.
 301

● We must click or select the checkbox associated with the Developer tab a

nd click the OK This will enable the Developer tab on the Ribbon.

Once the Developer tab is enabled, we will see it inside the Excel ribbon, similar t
o any other typical tab. We can find the Macros button under the Code section in
side the Developer tab, as shown below:

How to record a Macro?

Once we have enabled the Developer tab, we can access the Macros, including th
e recording feature. We can record a Macro using the following steps:
 302

● First, we need to navigate the Developer tab and click the 'Record Macro'

button under the 'Code' section.

● As soon as we click the 'Record Macro' button, we see a 'Record Macro' dia

logue box, as shown below:

In the dialogue box, we need to enter the following data:

○ Macro Name: We can assign different names for different Macros ba

sed on their action or working. However, we must ensure not to use

space characters or other special characters. It is better to use only

alphabets in combination with underscores if needed. Also, we can

not use the same Macro name multiple times.

 303

○ Shortcut Key: We can assign a key combination for the respective

Macro we will create. This will be helpful while executing the corres

ponding Macro. We can execute the associated Macro by pressing c

ertain assigned keys. We must ensure not to assign the shortcut key

combinations that are already in use, or else Excel will overwrite the

m. It is recommended to assign the desired shortcut by combining i

t with the Shift key.

○ Store Macro in: It is the drop-down list containing the following 3 o

ptions: 'This Workbook', 'Personal Macro Workbook', and 'New Wor

kbook'.

1. This Workbook- It creates Macro in the current workbook onl

y. If we try to use the created Macro in a new Excel workbook,

the created Macros earlier will not be displayed. That way, we

can only use such Macros in the respective workbook.

2. Personal Macro Workbook- It creates and stores Macros in t

he workbook. However, Macros will only be displayed on a ne

w Excel sheet.

3. New Workbook- It opens a new Excel workbook, and the acti

ons performed in a newly created workbook will be recorded

for future use.

 304

○ Description: We can write a description for each Macro that will hel

p us understand the purpose of the created Macro. It is recommend

ed to write a detailed description to make it easy for anyone to kno

w the associated action (s) for the respective Macro.

● Once we have given all the required data in the 'Record Macro' dialogue b

ox, we must perform all the actions we want to save in a Macro. The action

s refer to the steps to complete any particular task in Excel, such as adjusti

ng the formatting.

● After we have completed our task (or performed the desired set of actions),

we must again navigate the Developer tab and click on the 'Stop Recordi

ng' button. This will record/ create a Macro.

How to save an Excel workbook with Macro?

When we try to record a Macro in Excel, it asks us to choose between storing a Ma
cro in the same workbook (as 'This Workbook') or a personal workbook (as 'Pers
onal Macro Workbook'). Depending on the option we choose to store a Macro, w
 305

e have to perform different methods to save it. Let us discuss both the ways one
by one:

● When selecting to store Macro in as 'This Workbook': When we select to

store the Macro by choosing the 'This Workbook' option while recording,

we can save the workbook by going to File > Save As > Excel Macro-Enabl

ed Workbook. In this case, we don't need to save the Macro explicitly. It w

ill be saved automatically in the workbook.

● When selecting to store Macro in as 'Personal Macro Workbook': When

we select to store the Macro choosing the 'Personal Macro Workbook' opti

on, we have to save the Macro explicitly. In this case, we cannot save it like

other Excel formats. Instead, we save it using the pop-up dialog that Excel

displays when we try to close the workbook. We must use the 'Save' butto

n in the pop-up. If we don't save it using the pop-up, the Macro will be dele
 306

ted after closing the workbook.

How to view/edit the code of Macro?

As discussed above, Excel Macro records all the actions in the form of VBA codes.
Therefore, we can view and edit the VBA codes after recording them in Excel. Bas
ed on the recorded actions, Excel generates the codes.

Suppose our workbook contains a Macro. We need to navigate the View tab > M
acros > View Macros. Also, we can press the keyboard shortcut 'Alt + F8' to quic
kly access this feature. It will list all the existing Macros of the respective workbo
ok inside the Macro dialogue box.

If several Macros are created for the corresponding workbook, we can type the n
ame inside the 'Macro Name' box to instantly locate the desired Macro. After that,
we must select the Macro by clicking. After we select any Macro, the buttons on t
he right side of the window get activated. We can execute, edit, or delete the sele
cted Macro using the respective button.
 307

To view or edit the recorded Macro, we need to click the Edit button. This will op
en/ launch the VBA window, displaying the code of the selected Macro. We can m
ake changes in the code accordingly. However, it requires basic knowledge of VB
A codes and programming.

Additionally, we can directly access the sheet codes by pressing the keyboard sh
ortcut 'Alt + F11'.

How to run/execute a Macro in Excel?

Many methods can be used to run/ execute a Macro. However, the most useful m
ethod is to use keyboard shortcut keys that we assign while recording a Macro. T
he following are a few other commonly used ways by which a Macro can run:

Executing a Macro using the Shape

 308

One of the easiest ways to run a macro is to use (click or press) an inbuilt Shape.
We can insert a particular shape and associate the 'Run Macro' command with it
to be executed. This way, whenever we need to execute a script, we can click on t
hat shape. One advantage of using shapes to run a macro in a sheet is that we ca
n insert multiple shapes, with each associated with a different unique macro.

Below are the steps we should follow to run a macro using an Excel Shape:

● First, we need to navigate the Insert tab and click on the Shapes Next, we

must click on any desired shape to insert it into our active sheet.

● After selecting the desired shape, we must click on any area to insert the s

hape within the sheet. We can move the position within the sheet accordin

gly. Also, we can format the shape and insert the text inside the shape to
 309

make it more meaningful for the Macro.

● Once the shape has been inserted into the sheet, we must press the right-

click button on it and select the 'Assign Macro' option.

 310

● In the next window, we must select any existing recorded Macro that we w

ish to assign for the inserted (or selected) shape in the sheet. After that, we

must click the OK

Executing a Macro using the Button

When we insert a shape to execute a Macro, we can format it accordingly. Howev

er, this is not possible with the Macro button. It follows the standard Excel forma
t. Like the previous method, we can assign any desired Macro to a button and cli
ck on it to execute that associated Macro whenever needed. Below are the steps
we should follow to run a macro using Excel Button:
 311

● First, we need to go to the Developer tab and click on the Button tool und

er the' Controls' section.

● In the next step, we must click on any area on the sheet where we want to

insert the Macro-enabled button. As soon as we click anywhere in the shee

t, we see the 'Assign Macro' window. We need to select the Macro that we

want to link with the button to execute and click the OK

 312

● After that, we get a rectangular button inserted in the area we clicked. Clic

king on this button will immediately execute the respective Macro.

Although we cannot adjust the formatting for the button, like color and sh

ape, we can edit the text inside it. We can type any desired text to make it

easy to understand the functioning of that particular button.

To edit the text inside the Macro button, we must press the right-click butt

on and select the 'Edit Alt Text' option. After that, we can enter any desire
 313

d name accordingly.

Executing a Macro using the Ribbon

When multiple macros are saved or recorded in the same workbook, defining diff
erent tasks, it is better to use the ribbon tool to execute a specific macro. All mac
ros are listed under a single list when we use the Macros tool from the Ribbon, m
aking it easy to select and execute any desired macro. Below are the steps we sh
ould follow to run a macro using Excel Ribbon:

● First, we need to go to the Developer tab (enable it, if not enabled), then c

lick on the Macros tool/ button under the section named 'Code'.
 314

● We see a list containing all Macros created or saved in the opened workbo

oks (Excel Windows) in the next window.

● Next, we need to select a Macro and click on the 'Run' button.

Executing a Macro using the VBA

If the user and creator of the Macros are the same, then we can also run/ execute
Macro using the VBA editor. Below are the steps we should follow to run a macro
using Excel VBA:
 315

● First, we need to go to the Developer tab on the Ribbon and click on the

'Visual Basic' button, as shown below:

● We must locate the 'Run Macro' button in the next window. Also, we can p

ress the 'F5' function key.

● We will see a list of all Macros associated with that particular workbook in

the next window. Like the previous method, we must select the desired M

acro and click the 'Run' button.

 316

As soon as the 'Run' button is pressed, the selected Macro will be executed,

and the respective action (s) will be performed.

Enabling the Macro Security Settings in Excel

When we try to open an Excel workbook with Macros, we usually see a security w
arning saying: "Macros have been disabled". It is displayed under the Ribbon, an
d we must click the 'Enable Content' button to access/ use Macros in the corresp
onding worksheet.
 317

Apart from this, we can also prevent this security setting from occurring in our Ex
cel. We need to change the 'Trust Center Settings' from Excel Options. It is a one-
time process and includes the below steps:

● First, we need to navigate the 'File' tab, click 'Options', and select the 'Tr

ust Center' in the category list. Under this category, we must click the 'Tr

ust Center Settings' button.

This will launch an additional window/ dialogue box of the 'Trust Center'.

● In the next (Trust Center) window, we must select 'Macro Settings' from t

he category list. We must click on the radio button associated with the 'En

able all macros' option here. After that, we must click the OK
 318

Once we have completed the above steps, we will not see any notification

while opening a macro-enabled Excel file. However, it is recommended to

use the default option (Disable all macros with notification) as it prevents

macros from running without our knowledge. It is helpful to use default se

ttings when we download excel file from the internet or get it from other u

nknown/ untrusted sources.

Step by Step Example of Recording and Executing a

Macro in Excel
Now, we have already discussed and understood the basics of Excel Macros. Let
us now understand the concept and importance of using Macros in Excel with th
e help of an example.

Suppose we frequently receive text files from our clients, and we import these te
xt files into Excel and format the data in the same pattern every time. So, we can
create a Macro and execute it whenever we need to work with the newly received
text files.
 319

Consider the following text file as an example data set, where we have details of
the different items along with their prices and sold quantities:

Suppose that our text file is saved in a 'javaTpoint' folder in drive 'C'. We must pe
rform the below steps to record a Macro to execute the process of exporting the
data from the text file into an Excel file and format it accordingly.

● First, we need to open an Empty Excel workbook. We must go to the Devel

oper tab > Record Macro in this newly created workbook. This will open a

n additional window, as shown below:

In the above image, we need to provide all the details. For example purpos

es, we type the 'Macro name' as 'Sample_Macro_javaTpoint' and provide

 320

other data like the below image:

● After giving all the necessary data, we click the OK button, and recording f

or the Macro starts immediately. We must now perform the actions/ opera

tions that we want to record. Thus, we place the cursor in the first cell, nav

igate the Data tab, and select the 'From Text' button under the 'Get Exter

nal Data' section.

This will launch File explorer, using which we can select our text file from t
 321

he 'javaTpoint' folder under drive 'C'.

● After we select/ import the text file into Excel, we will see 'Text Import Wi

zard'. It will look like this:

 322

We must ensure that the displayed (highlighted) options are arranged the

same as in the image above. After that, we must click the 'Next' button to

go to Step 2.

● In the next step, we must select the 'Delimiters' to separate the data in our

text file into different columns. Since the data in our example text file is se

parated using the tabs, we select the 'Tab' option in step 2. After that, we
 323

must click the 'Next' button.

 324

● In step 3, we must select the 'General' option and click the 'Finish' button.

This will import the data from the text file into our Excel workbook.

● After that, we can format the data accordingly. For example purposes, we

make the text in headers Bold.

 325

● Once all the desired actions are performed, we must click on the 'Stop Re

cording' button under the 'Developer' tab.

● Next, we delete all the imported data from our worksheet and save the wo

rkbook as 'Excel Macro-enabled Workbook'. We can also create another

copy of this 'Macro-enabled workbook' based on the number of text files

we need to convert.

We can go to 'View > Macros > View Macro' in our newly created Macro fil
 326

e to see the recorded Macro.

● Whenever we receive a text file to convert, we need to open a copy of the

Macro-enabled Excel file and execute the Macro we created. Since we crea

ted a shortcut (Ctrl + Shift + B) to execute a Macro while creating it, we can

execute our script using the shortcut.

Conditional formatting in Excel

Conditional formatting is an especial feature (formatting feature) of Excel used t
o find unique and duplicates values by formatting the cells. This feature is availa
ble in various spreadsheet applications, and Excel is one of them.

As the name implies, conditional formatting allows the users to format the cells
and their data based on some conditions specified by the user. Using conditional
formatting, you can highlight a cell with a certain color and its content with differ
ent font.

Conditional formatting enables various features to the users to make the data m
ore informatic and readable. It also allows you to format the cells and their data,
which meet the specified criteria.

In this chapter, you will learn the several uses of conditional formatting and how
it applies to an Excel worksheet to make the data more useful.

Pause

Unmute

Current TimeÂ
 327

0:17

DurationÂ

4:57

Loaded: 100.00%

Fullscreen

Topics covered in this chapter

The following list of topics, we are going to cover in this chapter -

● Features of conditional formatting

● Where the conditional formatting option is available?

● Conditional formatting Basics

● Pre-set conditional formatting

○ Highlight Cell Rules

○ Top/Bottom Rules
 328

○ Data Bars

○ Color Scales

○ Icon Sets

● Highlight the cells

● Apply data bars on data

● Apply color scales on data

● Clear conditional formatting

● Define Custom Conditions (new rules)

Features of conditional formatting

Excel enables several features of conditional formatting, such as -

● You can highlight the cells with a background color to make the data more

valuable.

● You can also format the cell content with a special font.

● It also allows you to format the cells and their data, which meet the specifi

ed criteria.

● Excel allows to put conditions on data and format the cells based on follo

wing conditions, like greater than, less than, duplicate values, unique valu

es, equal, if text containing specified data.

● You can set the rules to format the data and also clear the rules set by you

either from selected cells or the entire sheet earlier.

 329

● Conditional formatting enables you to do a number of things with data, lik

e you can set the icons with the data, such as indicators, shapes, direction

al, etc.

● Conditional formatting makes the data more readable and informatic to t

he reader for the analysis purpose.

● For the analysis purpose of the spreadsheet, conditional formatting is a go

od choice to format the data.

Where conditional formatting option available?

It is very easy to find the conditional formatting option in Excel as it presents in t
he first Excel tab. It presents inside the Home tab, i.e., Home > Style > Condition
al Formatting.

Under these conditional formatting options, you will get several conditions to ap
ply and format the spreadsheet data. Choose wisely and use them according to y
our needs.

Conditional formatting basics

Before moving to apply conditions on an Excel spreadsheet, let's understand the
basic concepts of conditional formatting.

If-then Logic

Conditional formatting logic is based upon the if-then logic. It works in such an i
f-then manner to format the cells.

For example, X is a certain condition and Y is formatting to be applied on data. I

n such a scenario, if the X condition is satisfied (if TRUE), Y formatting will be app
lied to the data.
 330

Hence, it can be written as X →Y. Simply, it means that if X is True, then Y is appli
ed. All conditional formatting follows the same logic.

Pre-set conditions

Conditional formatting offers several pre-set conditions that a user usually need
s, such as greater than, less than, duplicate values, unique values, etc. Hence, to
save the time of users in writing formulas, Excel offers them some pre-set conditi
ons.

Following are the pre-set conditions. You can use any of them from here.

Excel has vast library of pre-set conditions that a user usually wants to apply usin
g functions.

Custom conditions

Often, pre-set conditions do not meet with criteria that you want to apply. Excel
allows you to manipulate the pre-set conditions and define your own custom co
nditions. It means that you can create your rules as well to format the data.

From here, you can define custom conditions.

 331

Applying multiple conditions

Sometimes, data requires multiple conditions to be applied to get the exact resu
lt that you want. Excel allows to apply multiple conditions on a single cell. Note t
hat - be aware with the hierarchy and precedence to use them.

Conditional Formatting
In this chapter, we are going to discuss each possible pre-set condition in brief to
give you an overview. By learning the basics of them, you can easily use them acc
ordingly. When you navigate to the Conditional Formatting option in the Home
tab, it enables several pre-set conditions.
 332

1. Highlight Cells Rules

2. Top/Bottom Rules

3. Data Bars

4. Color Scales

5. Icon Sets

Highlight Cells Rules

This conditional formatting rule allows the users to highlight the cells by using pr
e-set conditions that meet the specified criteria. It offers such conditions to the E
xcel users, such as Greater than, Less than, Between, Equal to, Text that cont
ains, Duplicate Values.

These conditions are usually comparison operator conditions.

Top/Bottom Rules

This conditional formatting contains top/bottom rules to format the cell data by
highlighting the cells. It is usually used to highlight the cells from the top or the b
ottom of the column, like top 10 cells or bottom 5 cells.
 333

Top/Bottom rules contain such pre-set conditions, Top 10 items, Bottom 10 ite
ms, Top 10%, Bottom 10%, Above Average, and Below Average.

Data Bars

Data bars are the colored bars apply on Excel data to represent the value of a cell.
These data bars are different than the above two conditional formatting. They ar
e used to show the higher/low status of the data contained in the cells clearly.

"Higher the value, longer the bar. Similarly, Lower the value, shorter the ba
r".

● Data bars are the graphical representation of data. Or you can say that gra

phical way to show the data.

● Data Bars help the Excel users to show the clear status on the based on ov

erall data by highlighting the cells with a color according to the high and l

ow value in the cell.

● Remember one thing that the data bars only apply to the numeric type of

data. On String data, these are worthless.

● Conditional formatting offers several colors to use in Data bars representa

tion.

With the help of an example, we will discuss you later how data bars actually wor
k.

Color Scale

In conditional formatting, a color scale is applied to a range of cells. The color in

dicates where each cell value falls within the cell range. In Excel, conditional for
matting offers twelve color scales to represent the data.

This color scale conditional formatting applies only to numeric values in an Excel
worksheet.
 334

Icon Sets

In Excel, you can represent the cells data through the different icon sets. Conditi
onal formatting contains several types of icon sets, such as Directional, Shapes,
Indicators, Ratings.

For example, by using a directional icon set, data can be represented in three w
ays high, low, and average value in a column.

These icon sets only attach with the numeric values in an Excel worksheet, not w
ith String data.

Highlight the cells

We will take an example to highlight the cells by putting certain conditions on a c
olumn. For example, highlight all the cells that are having data greater than a val
ue (specified by you for conditional formatting).

Follow the given steps:

Step 1: Select a range of cells in your Excel sheet, e.g., B2 to B8.

Step 2: Go to the Conditional Formatting present inside the Home tab and click
on it.
 335

Step 3: Hover the mouse to Highlight Cells Rules in dropdown list and then clic
k on the Greater than condition rule.

Similarly, you can choose any other conditions from here and perform the opera
tion accordingly.

Step 4: Here, specify a value with which you want to check all the selected value
s. We have entered 65 here.
 336

Step 5: Also specify the color to highlight the greater values and press the OK bu
tton.

Step 6: Look in the below screenshot, all the values that are greater than 65 are
highlighted with yellow color.

Step 7: Change the B5 cell value to greater than 500 and press the Enter key.
 337

Step 8: See that the cell color is changed Yellow automatically, and the cell is hig
hlighted.

Clear conditional formatting

In Excel, the users can clear all formatting applied to the cells in an Excel spreads
heet at once. Excel allows to clear the conditional formatting either from the enti
re worksheet or the selected cells (certain cells). See both methods below -

1. Clear formatting from entire worksheet

In the Home tab, go to Conditional formatting > Clear Rules > Clear Rules fro
m Entire Worksheet.
 338

It will clear all the pre-set and custom condition rules from the entire worksheet.

For example,

Step 1: Select all cells to clear the formatting from an entire worksheet and follo
w the step to clear the formatting rule for the entire worksheet.

Step 2: By clicking on the Clear Rules from entire worksheet, all formatting will
be removed from the entire worksheet. Look at the screenshot below:
 339

Here, you can see that the formatting is removed from the entire worksheet (all c
ells).

2. Clear formatting from selected cells

In addition, you can also clear the conditional formatting from the selected rang
e of cells. Steps are almost the same.

In the Home tab, go to Conditional formatting > Clear Rules > Clear Rules fro
m Selected Cells.

For example,

Step 1: Select the range from B2 to B7 but leave the last one, i.e., B8 and use the
clear formatting rule for the selected cells.

Step 2: By clicking on the Clear Rules from Selected Cells, formatting will be re
moved only from the selected cells. See the screenshot below:
 340

You can see that the formatting has been only removed from the selected cell, n
ot from all.

Apply top/bottom rule and highlight the cells

Conditional formatting enables six pre-set top/bottom rules to highlight the cells
It allows the users to highlight the cells from the top or from the bottom of the c
olumn, like top 10 cells or bottom 5 cells, etc.

Top/Bottom rules contain such pre-set conditions, Top 10 items, Bottom 10 ite
ms, Top 10%, Bottom 10%, Above Average, and Below Average.

See the steps how top/bottom rules apply on a column:

Step 1: Select a column (range of cells) and go to the Conditional Formatting in

side the Home tab.
 341

Step 2: Click on this Conditional Formatting option and navigate to the Top/Bo
ttom Rules in the list, then choose one of the Rule from here.

We have chosen Above Average to highlight the values which are above average.
 342

Step 3: Set a color to format the color of the cell and click the OK button.

Step 4: See that all the values which are above the average of the column are hig
hlighted.
 343

Apply data bars on data

Conditional formatting consists of data bars. You can use these data bars on nu
meric data in a column to represent the value of the cells graphically. See the ste
ps how could it be done:

Step 1: Select a column (range of cells) and go to the Conditional Formatting in

side the Home tab.
 344

Step 2: Click on this Conditional Formatting option and navigate to the Data B
ars in the list, then choose any of the Data Bar from here.

Step 3: Now, you can see each cell of the column is represented by the bar.

"Higher the value, longer the bar. Similarly, Lower the value, shorter the ba
r".
 345

Apply color scale on data

Conditional formatting consists of several color scales. You can use these color s
cales only on numeric data in a column to represent the value of the cells graphi
cally. The color indicates where each cell value falls within the cell range.

See the steps how could it be done and how data is represented:

Step 1: Select two or more columns (range of cells) containing numeric data and
go to the Conditional Formatting inside the Home tab.
 346

Step 2: Click on this Conditional Formatting option and navigate to the Color S
cales in the list, then choose any of the Color Scales from here.
 347

Step 3: Now, you can see that the numeric data of the selected column is repres
ented by the color scale.

You can note that the color scale has been applied only on numeric data, not on
strings.

Apply icon sets on data

In Excel, conditional formatting offers four types of icon sets, i.e., directional, sha
pes, indicators, and ratings. You can use these sets of icons on numeric data in a
column to represent the cell values in a column or range of cells. We will describ
e you the directional icon set.

Following are the steps to use the icon sets with the cell data and represent the
m:

Step 1: Select the column (range of cells) containing numeric data and go to the
Conditional Formatting inside the Home tab.
 348

Step 2: Here, from the conditional formatting option list, click on the Icon Sets.
When you click on it, you will get different icon sets; choose any of them whichev
er you want.
 349

Step 3: Now, you will see that the icon set is attached with cell values and repres
enting them with an arrow.

Define custom conditions

In Excel, conditional formatting offers pre-set conditions and also allows the use
rs to define their custom rules/conditions. In the Home tab, go to the Conditiona
l Formatting > New Rule.
 350

From here, you can set custom rules. Let's take an example to understand better.

For example, Row to row comparison of two columns and highlighting the cell if
matches found.

Step 1: We have this dataset for comparison. Select a row, which you want to ch
eck whether the values are the same or not.
 351

Here, we can easily compare for simple data only by seeing the data. But for com
plex data, it is not easy to match the values.

Step 2: Now, under the Home tab, go to Conditional Formatting > New Rule.
 352

Step 3: Here, click on the Use a formula to determine which cell to format fro
m the rule type list.
 353

Step 4: Here, inside the formula field, specify the cells you want to compare in th
e following format, e.g., =$A2=$B2 for the second row.

Step 5: In the end, specify the format for matched cell by clicking on the Format
button and see the preview of it inside the Preview box.
 354

Step 6: Here, navigate to the Fill tab and choose a background color to highlight
the matches and click the OK button.

Step 7: See the preview inside the preview section and see how row will look like
if the match is found. After finalizing all the things, click the OK button to save th
e changes.
 355

Step 8: You will see that the selected row has not been highlighted because the
A2 and B2 cells do not contain the same values.
 356

Step 9: By following the same steps, compare the next row data. See that everyt
hing is set up successfully. Now, click the OK button to get the result.

Step 10: Look at the following screenshot, that the 3 rd row has been highlighted
because it gets the same data in both columns.
 357

Step 11: Similarly, we will check for all rows one-by-by. See the Excel worksheet
after comparing both columns that row having the same data in both columns h
as been highlighted and remains are as it is.
 358

It will highlight all the matching data row in the format you have chosen previou
sly. Like this, we can define the custom conditions in conditional formatting.

UNIT-3
Excel charts:
https://www.tutorialspoint.com/excel_charts/excel_charts_elements.htm

How to View Layers in Powerpoint

PowerPoint revolves around visual hooks, bringing slide show presentations to life via bullete
d lists, animations, charts, videos and images. Among the other customization options offered
by the program, PowerPoint allows you to layer these objects, one "atop" the other, in a singl
e slide. Although viewing layers created by another presenter is as easy as opening and clicki
ng through a PowerPoint presentation, you'll have to create your own layers if you want to vi
ew them in your own slide show.

1. Add the objects that you wish to layer to your PowerPoint slide with the “Insert” tab.

By default, the most recent object added to a slide appears on the top layer of objects.

PowerPoint doesn't place a limit on the number of objects you can use per slide; it's u

p to you to limit the number of objects to avoid confusion.

 359

2. Click on a single object, or layer, to select it. You can modify its position among the o

ther objects, which act as additional layers of your slide, under the “Drawing” tab on

PowerPoint's main control ribbon. From here, click the “Arrange” button.

3. Select one of the four options – “Bring to Front,” “Send to Back,” “Bring Forward” or

“Send Backward” – to reposition the layer you have selected. “Bring Forward” moves

the object up one layer while “Send Backward” moves it back one layer. “Bring to Fr

ont” puts it on the top layer and “Send to Back” positions it on the bottom layer.

4. Save your presentation with layers intact as a PPT or PPTX file with PowerPoint's "Sa

ve" or “Save As” option. Open your creation in Microsoft PowerPoint or Microsoft P

ointPoint Viewer to see your layers in action.

Why Layer Objects?

There are two main reasons you may want to layer objects in a PowerPoint presentation. First ,
layering objects gives you the freedom to arrange your presentation in any layout that you de
sire. Rather than working in a linear fashion as with a word processor, you can place and laye
r objects all you want.

Second, layering lets you take advantage of all of the white space in your presentation. Text b
oxes in particular take up a lot of room. By layering objects, you can use all of the space on a
slide without changing the position of other objects.

Layering Objects in PowerPoint

Suppose you add four boxes to your PowerPoint presentation; first a red one, then green, then
blue, then yellow. Each box represents an object. When you add a new object to a PowerPoin
t presentation, the application automatically places the newest objects on top of older objects.
If you add the boxes in the order mentioned above, the layering of the objects will look like t
his:
 360

Notice that the newest box (yellow) is on top of the others. Using PowerPoint’s layering funct
ion, you can layer these objects in any order. To layer the objects differently from the default
order, begin by clicking on the Home tab on the Ribbon and locating the section titled Draw
ing. In the Drawing section, locate and click on the button titled Arrange.

Notice that on the menu that pops up, there are four options titled:

1. Bring to Front
2. Send to Back
3. Bring Forward
4. Send Backward
 361

With these commands, you can layer objects in PowerPoint any way you want. Notice, howe
ver that the menu items are grayed out; you can’t use them unless you first make an object act
ive by clicking on it before you click on the Arrange button. As an example, let’s arrange the
yellow box on top to a different layer.

Begin by clicking on the yellow box to make it the active object. Then, click on the Arrange
button and select Send Backward from the menu. Notice that now the yellow box has moved
back one layer to be between the blue box and the green box.

Now select the blue box and this time select Send to Back from the Arrange menu. Notice t
hat now the blue box occupies the lowest level and is now behind the yellow, green, and red b
oxes. Using the four arrange functions on the Arrange button, you can choose whether an obj
ect moves one level up or down or all the way to the highest or lowest level.
 362

It may take some getting used to working with layers in PowerPoint, but the menu system is q
uite intuitive. Use the Send Back and Bring Forward commands to move an object one laye
r back or forward and use the Send to Back and Bring to Forward commands to move an ob
ject all the way to the front or back of the layering order.

You can also simply right-click on the object and choose those options from the context men
u as shown below.
 363

In addition, it’s worth noting that you can also make your layers fully transparent or semi-tran
sparent, which can result in some cool effects. In our little example, I made the yellow layer s
emi-transparent and then added some text to the green box.

You can make a layer transparent by right-clicking on it and then clicking on the Style button.
You’ll see a bunch of boxes with different colors and styles. Towards the middle/bottom, yo
u’ll find the options for transparent and semi-transparent.
 364

There is virtually no limit to the number of layers the objects on a PowerPoint slide can occu
py. However, to keep things neat and simple for your audience, consider using no more layers
than you need on a single slide.

Use too many layers and you risk making too complicated a slide to follow. Experiment with
arranging and layering objects in PowerPoint and you can reclaim unused white space to crea
te a more visually appealing PowerPoint presentation. Enjoy!

Add the Animation

1. Select the image you want to add animation to.
2. Select Animation tab.
3. Click the Add Animation button.
4. From the menu, select More Motion Paths.
5. A menu will appear where you can select from a variety of motion pat
hs. Choose the one you want.
6. Select OK.
7. Your animation has now been added to the slide. You can adjust the
animation from here if it needs further settings.

Add Audio to the Animation

1. From the Animation Pane, select the drop down menu that you can
access from the arrow on the animation.
2. Click Effect Options.
3. A new menu will appear. Under Enhancements in the menu, you hav
e the Sounds option to select from a list of pre-loaded audio snippets,
or you can select “Other Sound…” from the list to import your own.
4. Click OK once you’ve selected a sound.
 365

5. Now your audio is attached to your animation.

Part 1: How to Add Sound Effects to PowerPoint

Do you want to make your PowerPoint presentations more interesting? Want to add
some sound effect to it? Here's a step-by-step tutorial on how to add sound to Pow
erPoint.

Step 1: To add a sound effect in PowerPoint, first, you need to add an animation to t
he text or object on the slide. Select the text/object, click on "Animations" and choos
e the desired effect.
 366

Step 2: Now, to add a sound effect to the animation, go to "Animation Pane". Click o
n the down arrow in the menu on the right and select "Effect Options".

Step 3: A pop-up box will appear, from where you can select the built-in sound effect
s to add to your animated text/object, the timing, and other settings.

Step 4: If you want to use a sound effect other than the ones listed, click on "Other S
ound" in the drop-down menu and select the sound file from your computer.
 367

And that is how to add sound effects to PowerPoint.

However, using PowerPoint to add sound effects to your presentations is too mainstr
eam. If you want to use software that gives a professional touch to your videos, we w
ould recommend giving iMyFone Filme a try.

How to Insert a Picture in MS Powerpoi

nt?
To insert a picture from a file:
1. Select the Insert tab, then click the Pictures comma
nd in the Images group.
 368

2. A dialog box will appear. Locate and select the desir

ed image file, then click Insert.

3. The picture will appear on the currently selected slid

e.

You can also click the Pictures command in a placeholder to insert images.
 369

Inserting online pictures

If you don't have the picture you want on your computer, you can find a picture online to ad
d to your presentation. PowerPoint offers two options for finding online pictures.

● Bing Image Search: You can use this option to sear

ch the Internet for images. By default, Bing only sho
ws images that are licensed under Creative Comm
ons, which means you can use them for your own pr
ojects. However, you should go to the image's websi
te to see if there are any restrictions on how it can b
 370

e used.

● OneDrive: You can insert an image stored on your

OneDrive. You can also link other online accounts
 371

with your Microsoft account, like Facebook or Flickr.

To insert an online picture:

1. Select the Insert tab, then click the Online Pictures
command.

2. The Insert Pictures dialog box will appear.

 372

3. Choose Bing Image Search or your OneDrive. In o

ur example, we'll use Bing Image Search.

4. Press the Enter key. Your search results will appear

in the dialog box.
 373

5. Select the desired image, then click Insert.

6. The image will appear on the currently selected slide.

 374

You can also click the Online Pictures command in a placeholder to insert online images.

When adding images, videos, and music to your own projects, it's important to make sure yo
u have the legal right to use them. Most things you buy or download online are protected by
copyright, which means you may not be allowed to use them. For more information, visit ou

r Copyright and Fair Use lesson.

Moving and resizing pictures

Once you've inserted a picture, you may want to move it to a different location on the slide
or change its size. PowerPoint makes it easy to arrange pictures in your presentation.

To select a picture:
Before you can modify a picture, you'll need to select it.
 375

● Simply click to select a picture. A solid line will appe

ar around a selected picture.

To resize a picture:
● Click and drag the corner sizing handles until the p
icture is the desired size.

The corner sizing handles will resize a picture while preserving its original aspect ratio. If y
ou use the side sizing handles, the image will become distorted.
 376

To rotate a picture:
● Click and drag the arrow above an image to rotate it
right or left.

Hold the Shift key on your keyboard when rotating an image to snap it to angles in 15-degre
e increments.

To move a picture:
● Click and drag to move a picture to a new location o
n a slide.
 377

To delete a picture:
● Select the picture you want to delete, then press the
Backspace or Delete key on your keyboard.

You can access even more picture formatting options from the Format tab. We'll talk more a

bout these options in our lesson on Formatting Pictures.

Inserting screenshots
Screenshots are basically snapshots of your computer screen. You can take a screenshot o
f almost any program, website, or open window. PowerPoint makes it easy to insert a screen
shot of an entire window or a screen clipping of part of a window in your presentation.

To insert screenshots of a window:

1. Select the Insert tab, then click the Screenshot co
mmand in the Images group.
2. The Available Windows from your desktop will app
ear. Select the window you want to capture as a scr
 378

eenshot.

3. The screenshot will appear on the currently selected

slide.

To insert a screen clipping:

 379

1. Select the Insert tab, click the Screenshot comman

d, then select Screen Clipping.

2. A view of other open windows will appear. Click and

drag to select the area you want to capture as a scre
en clipping.

3. The screen clipping will appear on the currently sele

cted slide.
 380

To insert a sound file from your computer:

● Select the slide where you want to add sound.
● Select the Insert tab.
● Click the drop-down arrow on the Sound command i
n the Media Clips group.

● Select Sound from File from the menu. The Insert S

ound dialog box will appear.

● Locate the sound file on your computer.

● Select the file.
● Click OK. A sound icon and a dialog box will appear.
 381

● Select Automatically or When Clicked. Automatica

lly will start the sound automatically as soon as the s
lide appears in Slide Show view, while When Clicked
will start the sound when you click.

Click, drag, and release the button to move the sound icon to a differe
nt location on the slide.

Sound options
 382

On the Sound Tools Options tab, there are several options you can
control that determine how the sound is used in the presentation. The
se include:

● Preview: Listen to the sound that will play

● Slide Show Volume: Change the volume to low, me
dium, high, or mute
● Hide During Show: Hide or display the sound icon d
uring the slide show
● Loop Until Stopped: Have sound play until you stop
it by clicking or advancing to the next slide

The Picture Tools Format tab appears when a sound is inserted bec
ause the sound icon is a picture. You can format the sound icon just li
ke any picture.

To delete the sound:

● Select the sound icon.
● Press the Delete key on your keyboard.

Sounds in the clip organizer

To insert a sound from the clip organizer:
 383

● Select the slide where you want to add sound.

● Select the Insert tab.
● Click the drop-down arrow on the Sound command i
n the Media Clips group.

● Select Sound from Clip Organizer from the menu.

The Clip Art task pane will appear.

● Enter keywords in the search field.

● Click Go. Clip art sound results will appear in the tas
k pane.
● Click a sound file in the task pane to insert it. A soun
d icon and a dialog box will appear.
 384

● Click Automatically or When Clicked. Automaticall

y will start the sound automatically as soon as the sli
de appears in Slide Show view, while When Clicked
will start the sound when you click.

To preview sound from the clip organizer:

● Enter keywords in the search field.
● Click Go. Clip art sound results will appear in the tas
k pane.
● Move your cursor over a sound option, and a drop-d
own arrow will appear.
● Click the drop-down arrow.
● Select Preview/Properties from the menu. The sou
nd file will play, and a dialog box will appear.
 385

About the Preview dialog box

○ Information on the right includes file na
me and type.
○ Use the sound controls to play, pause,
and stop the sound file.
○ The name of the current sound file is to
the right of the word Caption, below the
sound controls.
○ Click the Next and Previous arrows to o
pen and listen to an additional sound fil
e.
 386

○ Click the Close button to close the dialo

g box. The last active sound file appear
s selected in the Clip Art task pane.

● Insert the sound file if you want.

Sounds on CD
To play tracks from a CD:
● Select the Insert tab.
● Click the drop-down arrow on the Sound command.
 387

● Select Play CD Audio Track from the menu. The Ins

ert CD Audio dialog box will appear.

● Enter the Start at track: and End at track: informati

on in the Clip selection section.

● Choose whether you want to Loop the tracks until

stopped and adjust the volume in the Play Options
section.
● Choose to display or hide the sound icon during the
slide show in the Display Options section.
● Click OK. A CD sound icon and a dialog box will app
ear.
● Select Automatically or When Clicked.
 388

To add sound from a CD, you must have a music CD inserted into you
r computer's CD-ROM drive.

The CD Audio Tools Options tab

Once you have inserted tracks from a CD, a CD Audio Tools Options t
ab appears. Many of the commands are similar to the commands avai
lable when you insert other sounds; however, some of the options are
different.

Functions in the Play and Setup groups include:

● Preview: Listen to the sound that will play

● Slide Show Volume: Change the volume to low, me
dium, high, or mute
● Edit the Track and Time fields: Change the tracks t
hat play and the time in the track that playback starts
or stops
● Change How to Play Track: Click the drop-down m
enu next to Play Track to change whether the CD tra
cks play automatically or when clicked
● Hide During Show: Hide or display the sound icon d
uring the slide show
● Loop Until Stopped: Have sound play until you stop
it by clicking or advancing to the next slide
 389

Tally
Tally accounting software provides a solution around inventory management, stock management, invo
icing, purchase order management, discounting, stock valuation methodology, etc.

Tally accounting software also comes with drill down options, which can track every detail of transacti
on. It helps in maintaining simple classification of accounts, general ledger, accounts receivable and p
ayable, bank reconciliation, etc.

The technology employed by tally makes data reliable and secure. Tally software supports all the maj
or types of file transfer protocols. This helps in connecting files across multiple office locations.

Tally accounting software is capable of undertaking financial analysis and financial management. It pr
ovides information around receivables turnover, cash flow statement, activity consolidation and even b
ranch accounting.

Tally accounting software is east to set up and simple to use. A single connection can support multiple
users. It can be easily used in conjunction with the Internet making possible to publish global financial
reports.

Tally accounting software can seamlessly connect with various Microsoft applications.

Benefits of Tally Accounting Software

Any business owner understands the importance of maintaining proper books of account. This practic
e ensures that finance for the company is always in order and are correct at all given points of time. C
ompany should always be aware of its financial positions.

Earlier, most of the businesses were employing manual practice in maintaining books of account. How
ever, with the advent of modern information technology, this task can be performed by accounting soft
ware. Tally is one such all powerful accounting software.

Tally accounting software provides a solution to all the problems real businesses have to encounter. S
ingle software takes care of all tasks required for enterprise management. Accounting task such as re
cords keeping, accounts receivable and payable management and bank reconciliation are made simpl
e through tally.

Financial management is also made simpler under Tally software. The software allows management o
f finances across multiple locations can handle multiple currency transactions, manage cash flow and
interest payment.

Thus, Tally software is flexible, reliable, secure, easy to use and affordable.

https://tallyerp9book.com/Pages/Web-Page/Home-TallyERP9Bo
ok/Cmp-Info/1-Introduction-Tally-ERP9.html