Font Files - Download and Install

A font is a graphical representation of text that may include a different typeface, point size, weight, color, or design

There are many websites for thousands of  fonts freely available to download.

• www.openfontlibrary.org

• www.dafont.com

• www.fontsquirrel.com

• www.1001fonts.com

• www.fontsc.com

Font Files

• Font files contain one or more fonts that can be accessed by operating system and applications.

• Most modern fonts are stored in either the open type or true type formats with extensions .OTF, .TTF

How to Download font files

• To download font files browse for the fonts you need to download. 

• Some of the websites are given above for freely available fonts on internet

• Click  on download to download the font

• A compressed folder gets downloaded, extract the files by right clicking on the folder

• Double Click on the extracted font file and click on Install

• Installation gets started

• To install more that one files at a time just copy and paste the Font files to the fonts folder present in Control Panel
• Open the extracted fonts folder
• Select all files(Ctrl + A) and copy all( Ctrl+C) to install all downloaded files

• Open Control Panel search and open Fonts in Appearance and Personalization category. Then paste the copied fonts

• You can check the installed fonts using font section in windows applications

Macros

Macros are one of the Excel's most powerful features, They can help you automate repetitive tasks.

• Steps/Tasks can be recorded using macro recorder

• Wherever you find using same thing again and again at that time we consider a macro

 

Steps to enable developer tab

• Macros and VBA tools can be found on the Developer tab, Which is hidden by default

   

• Click the File tab then Click Option

• On Excel Options Window Click on Customize Ribbon

• Under Customize the Ribbon and under Main Tabs, select the Developer check box

• Developer tab is enabled

Record Macro

• In the Code group on the Developer tab, click Record Macro

• Perform functions to automate, like entering the data in to a table

  Use Relative References 

• Macros are recorded with actions relative to the initial selected cell

Example for Macros- Adding data to the table

Adding Macro to the button to automate the process

Enter the details and click on save to save the details in table

VLOOKUP & HLOOKUP

What is Vlookup?

  Vlookup means vertically looking up for something and bring corresponding details

• You can use Vlookup to search for a value in the first column of the table.

• It returns a value in the same row based on the column index number specified. 

  

What is Hlookup?

   Hlookup means horizontal lookup, It is easy to understand Hlookup once  you understand Vlookup.
 

Syntax for Vlookup

  Example for  Vlookup in Google Sheets

 

Syntax for Hlookup 

Example for Hlookup in Google Sheets

Mail Merge

The automatic addition of names and addresses from a database to letters and envelopes in order to facilitate sending mail, especially advertising to many addresses.

Excel spreadsheet that contains the variable data for letters

  Create a Microsoft Word letter with common content

• In below picture Name, Company name and Position are variables/ Place holders 

In Microsoft Word click as numbered below, it opens Mail merge step by step wizard window 

 

Step 1: click "Next: Starting Document" on the bottom of the Wizard

Step 2: choose "Use the current document"

Step 3: choose "Use an existing list" then Browse for Database sheet Select and click "OK"

• Select recipients from the selected sheet

• Create merge fields. Highlight 'Name' then choose 'Name' from insert merge field

• After creating merge fields click on "Next: Write your letter"

Step 4: click on "Next preview your letters" to check whether fields displayed correctly

Step 5: click on "Next: Complete the merge" to complete merge action

Step 6: click on "Print" to print the letters/ click on "Edit individual letters" to edit

Format Cells

The look and feel of the worksheet has a lot to do with the formatting choices we make. The worksheet becomes easier to read and more visually appealing.

By default excel uses general format as shown below

Format a cell

• Number format

• Number Format- Date

• Font format

• Fill color, Text color and Alignment

• Borders

Shortcut keys

Excel Shortcut Keys

Shortcut	Action
F2	Edit selected cell
F4	Repeat last action
F5	Go to specific cell
Ctrl+Shift+F	Format cells dialog box
Ctrl+Shift+;	Enter the current time
Ctrl+;	Enter current date
Alt+Shift+F1	Insert new worksheet
Alt+Enter	Move to next line allowing multiple lines of text in one cell
Shift+F3	Open excel formula window
? / Alt	To select the menu item
Ctrl+1	Open format cells window
Ctrl+B	Bold content in the selected section
Ctrl+K	Insert link
Ctrl+S	Save the worksheet
Shift+Arrows	Select cells
Ctrl+Z	Undo last action
Ctrl+F9	Minimize the Workbook
Ctrl+F10	Maximize or restore the workbook
Ctrl+Tab	Move between two or more open excel files
Ctrl+Arrow key	Move to the next section of the text
Ctrl+ -	Delete selected row or column
Ctrl+Shift+=	Insert a new row or column
Ctrl+Home	Move to cell A1

Shortcut keys for formatting cells

Shortcut	Action
Ctrl+Shift+~	General Format
Ctrl+Shift+1	Comma Format
Ctrl+Shift+2	Time format
Ctrl+Shift+3	Date format
Ctrl+Shift+$	Currency format
Ctrl+Shift+%	Percentage format
Ctrl+Shift+6	Exponential format
Ctrl+2	Bold
Ctrl+3	Italic
Ctrl+4	Underline

VLOOKUP & HLOOKUP

What is lookup in excel?

  The Lookup function returns a value from a range or from an array. It is a predefined function in excel.

Why to use lookup function?

  Lookup function is used to search one column of data and find the data in the corresponding row or vice versa.

What is Vlookup and Hlookup?

  Hlookup and Vlookup are functions in MS Excel that allow you to use the section of your spreadsheet as a lookup table.

  

Vlookup

  When a Vlookup function is called, Excel searches for the lookup value in the leftmost column of a section of spreadsheet 

• Vlookup works in vertical area, column wise

• Function can be called from the same sheet or from different sheet

• Can be used in variety of different ways

Hlookup

  When a Hlookup function is called, Excel searches for the lookup value in the topmost row of a section of spreadsheet

• Hlookup works in Horizontal area, row wise

• Function can be called in same sheet or form different sheet