Saturday, March 29, 2014

Introduction to Excel VBA

Leave a Comment


In this topic we will understand the basics in Excel VBA. What can we do with Excel VBA? How it helps us with MS Excel.
.
Introduction to Excel VBA
EXCEL VBA is the acronym for Visual Basicfor Applications. It is an integration of the Microsoft’s event-driven programming language Visual Basic with Microsoft Office Applications such as Microsoft Excel. Excel VBA is a programming language for MS Excel Application. It allows you to automate the task which you are doing manually and repeatedly. Most of the times, we do the same kind of tasks daily in our daily work, you can automate it using VBA.
We can use Excel VBA for:
§  automating the repeated tasks
§  automating hourly, daily,weekly,monthly or yearly generated reports
§  preparing rich and interactive Excel Dashboards
§  connecting to various database to get the data and prepare the reports
§  building  the automated tools
§  interacting with other applications, including MS Office Tools using API
§  doing many more things using VBA based on your data and requirement

FOR EXAMPLE
» We fetch some data from different sources like database by using sql language and paste it in a worksheet and doing some manipulations and then we will format it and generate Automated Report
» We may extract some data from other file system like csv etc… to an Excel sheet, and then we will clean the data and create some reports and send through email..
» Many of us do the similar kind of work daily; there may be some change in the data but not the structure of the data, using vba to process steps which execute to deliver the output.
VBA HELPS…
§  avoid doing the same things manually & repeatedly
§  save the process time by automating the tasks
§  generate the interactive reports quickly
§  develop the tools for the organization
§  automate any Office Tool and Other Applications
§  save project execution time and spend the time on exploring the new things or technologies
Read More...

Indentation Paragraph, Set Line Spacing, Set Tabs

Leave a Comment


When you type information into Microsoft Word, each time you press the Enter key Word creates a new paragraph. As you know margin settings determine the blank space that appears on each side of a paragraph. For example, you can indent the first line of a paragraph, you can set the amount of space that separates paragraphs, and you can align a paragraph left, right, center, or flush with both margins. Styles are a set of formats you can quickly apply to a paragraph. For example, by applying a style, you can set the font, set the font size, and align a paragraph all at once. In this lesson, you will learn about the various formats you can apply to a paragraph and about styles.


Left Indentation
Left indentation means to move the left edge of the paragraph inward towards the center of the paragraph.


How to make Left Indentation
Step 1. Click anywhere on the paragraph you want to indent left and click Increase Indent button available on Home tab or simply press Ctrl + M keys. You can click multiple times to create deeper indentation.

Step 2 You can remove left indentation by clicking Decrease Indent button available on Home tab or simply press Ctrl + Shift+ M keys.

You can also use to Paragraph Dialog Box to set left and right indentations. We will see this dialog box in last section of this chapter.

Right Indentation
Right indentation means to move the right edge of the paragraph inward towards the center of the paragraph.

How to make right indent
Step 1 Click anywhere on the paragraph you want to indent right and click Increase Right Indent spinner available on Page Layout tab. You can click multiple the spinner times to create deeper indentation. You can use Left Indent spinners as well to set left indentation from the same place.


Step 2 You can remove right indentation by clicking the Decrease Right Indent spinner in opposite direction.




First Line Indentation
You can move the left side of the first line of a paragraph inward toward the center.

How to make first line indentation
Step 1. Click anywhere on the paragraph you want to indent right and click the Paragraph Dialog BoxLauncher available on Home tab.
Step 2. Click Before Text spinner to set left indentation and select First Line Option to move the left side of the first line of a paragraph inward toward the center. You can control the movement by setting Indentation Unit.


Hanging Indentation
You can move the left side of the first line of a paragraph leftward, away from the center which is called hanging indentation.

How to make Hanging indentation
Step 1 Click anywhere on the paragraph you want to indent right and click the Paragraph Dialog BoxLauncher available on Home tab.
Step 2  Click Before Text spinner to set left indentation and select Hanging Option to move the left side of the first line of a paragraph leftward, away from the center. You can control the movement by setting Indentation Unit.

Spacing between Lines
Line Spacing is the space between two lines of the paragraph in your document.
Following are the simple steps to adjust spacing between two lines of the document.

How to make line spacing
Step 1 Select the paragraph or paragraphs for which you want to define spacing.
Step 2 Click the Line and Paragraph Spacing Button triangle to display a list of options to adjust space between the lines.

Spacing between Paragraphs:
You can also set distance between two paragraphs.


How to make Spacing between paragraph
Step 1 Select the paragraph or paragraphs for which you want to define spacing and click the Paragraph Dialog Box Launcher available on Home tab.
Step 2 Click Before spinner to increase or decrease the space before the selected paragraph. Similar way click After spinner to increase or decrease the space after the selected paragraph. Finally click OK button to apply the changes.


In conclusion, Microsoft word provides us many features and operations to work with our document. It has many other features, you can explore and try it by yourself to to how it work.
Read More...

Copying Moving and Pasting Data in Excel

Leave a Comment


When we are working with the data most of the time we may required to copy/move the data from one location (range) to another location. This tutorials will explains you Copying Moving and Pasting Data in Excel.

Moving a Range in Excel

We can Move the data from one range to another range by using either short cuts or in-built commands.
If you want to Move the data from one range another location:
First we need to Select the existing Range => Cut it => Choose a New Range => Paste it
Select Range to Move:

Cut it: Press Ctrl+ X or right click and select Cut command.
Choose a New Loaction:
 

Paste it: Press Ctrl+ V or right click and select Paste commands.
 

Output:

Copying a Range in Excel

We can Copy the data from one range to another range by using either short cuts or in-built commands.
If you want to Copy the data from one range another location:
First we need to Select the existing Range => Copy it => Choose a New Range => Paste it
Select Range to Copy:
Copy it: Press Ctrl+ C or right click and select Copy command.
 

Choose a New Loaction:
 
Paste it: Press Ctrl+ V or right click and select Paste commands.
Output:
 

Read More...

Formatting Data and Formatting Cells in Excel

Leave a Comment


Microsoft Excel provides us the tools to make rich formats, we can format the numbers, fonts, font color, background color, alignments and many more things we can do in Excel. This tutorial section will help you to learn Formatting Data and Formatting Cells in Excel.
We have many tools in Excel to work with Formatting

Home Tab

If you observe the Home Tab, you can see many commands to work with formats.

Font
We can use these commands to format the Fonts Styles like Bold, Underline, Italic, Font size, Background Color, Font Color etc. You can click on the icon at bottom-right corner to see the more Font Settings.

Alignment
We can align the data in a Cell using these Commands to align Left,right, Center, etc. Also we have another two frequently used Commands like Wrap Text and Merge Commands
Number
We can format the numbers to currency, percentages, decimals and other formats. We will see in more detailed manner in another topic only on number formats.


Right Click: Format Cells Dialog/ Quick Options

You can also right click on the Cell and Select ‘Format Cells…” Command from the properties list to view the Format Cells Dialog.


Useful Shortcut Keys for Formatting Data

You can use the following Shortcut Keys to Quickly Format the Data.
To Format
Short-cut Key
Bold
Ctrl+b
Italic
Ctrl+I
Underline
Ctrl+u
Strike through
Ctrl+5
Open the style dialog box
alt+’
Open the format cells dialog box
Ctrl+1
Apply the general format
Ctrl+Shift+~
Apply the currency format
Ctrl+Shift+$
Apply the percentage format
Ctrl+Shift+%
Apply the date format
Ctrl+Shift+#
Apply the time format
Ctrl+Shift+@
Apply the number format
Ctrl+Shift+!
Apply the exponential number format
Ctrl+Shift+^
Apply an outline border to the selection
Ctrl+Shift+&
Remove an outline border from the selection
Ctrl+Shift +_


Read More...