Microsoft Excel (Full Explanation)

Introduction:

Excel is the spreadsheet program created by Microsoft. You can use any spreadsheet program for analyzing data. It has become an integral part of most business organizations all over the world. Some organizations use this spreadsheet software for generating memos track sales and other business data. MS Excel has a simple interface that allows user to easily understand this software & helps to perform basic activities.

File Tab:

You can use this tab to open or save files, create new sheets, print a sheet and do other file related operations.

Quick Access Toolbar:

This toolbar is just above the file tab and it provides a convenient resting place for the Excel's most frequently used commands.

Ribbon:

Excel has a unique ribbon which contains various tab like: Home, Insert, Page layout, Formulas, Data, Review & view. The Home Tab Contains the most frequently used commands in Excel.

Title Bar:

This lies in the middle & at the top of the window. Title bar shows the program & the sheet titles.

Help:

The Help icon can be used to get Excel related help anytime you need.

Zoom Control:

Zoom control lets you zoom in for a closer look at your text. The zoom control consists of a slider that you can slide left to right to zoom in our out.

View Buttons:

The group of three buttons located to the left of the zoom control, near the bottom of the screen is view buttons.

Sheet Area:

The area where you enter data is called sheet area.

Row Bar:

Rows are numbered from 1 on wards & keep on increasing as you keep entering data. Maximum limit is the maximum number of row are 1,048,576 in Excel.

Column Bar:

Column are numbered from A on wards & keeps on increasing as you keep entering data. After Z, it will start the series of AA, AB and so on. Maximum number of columns are 16,384 in Excel.

Working With Spelling & Grammar Errors

Working With Excel:

Working with spelling & Grammar Errors Choose Review & select Spelling option or press F7

Select the range before you activate the spell checker.

If the spell checker finds any words it does not recognize as correct, it displays the Spelling dialogue with suggested options.

There are various options with the spell check dialogue box which are listed below.

Ignore Once: It ignore the word & continues the spell check.
Ignore All: It ignores the word & all subsequent occurrences of it.
Add to Dictionary: It add the words to the dictionary.
Change: It changes the word to the selected word in the Suggestions list.
Change All: It changes the word to the selected word in the Suggestions list & changes all subsequent occurrences of it without asking.
AutoCorrect: It add the misspelled word & correct spelling (Which you select from the list) to AutoCorrect list.

Zoom Slider:

The default screen is displayed at 100% in Excel. You can change the zoom percentage from 10% (small) to 400% (large). Zooming does not change the font size, so it has no any effect while printing the document. You can view the zoom slider at the bottom of the workbook.

Zoom In:

You can zoom in the workbook by moving the slider to the right. It will change the only view of the workbook. You can have maximum of 400% zoom in.

Zoom Out:

You can zoom out the workbook by moving the slider to the left. It will change the only view of the workbook. You can have maximum of 10% zoom out.

Using Symbols in Excel:

Click in insert & Select Symbols
Choose Symbol to view available symbols.
Select the symbol you want to add & click insert to use the symbol.

Working With Special Characters:

Go to Insert Tab.
Select Symbols & select Special Characters to view the available special characters.
You can see many special characters available there like Copyright, Registered etc.
Select the special character you want to add & click insert, to use the special character.

Inserting Comment:

Various formatting options are available for comments. For formula a comment you can use the following steps.

Right click on cell & go on insert comment.
Type required text on the box.
Place the mouse over the text that to see the comment.

Text Boxes:

Text Boxes are special graphic objects that combine the text with a rectangular graphic object. Text boxes are cell comments are similar in displaying the text in rectangular box. But text boxes are always visible, while cell comments become visible after selecting the cell.

Adding Text Boxes:

Choose Insert & select Text Box.
Choose text box or draw it.

Type the required text inside the text box.

Formatting Text Box:

After you have added the text box, you can edit it by changing the font size, font style, and alignment, etc. We can format the text box with the help of following steps.

Right click over the text box area.
Select format shape option.
Fill: It specifies the filling of text box like no fill, solid fill. It also specify the transparency of text box fill.
Line Color: It specifies the line color & transparency of the line.
Line Style: It specifies the line style & width.
Properties: It specifies some properties of the text box.
Text Box: It specifies text box layout, Auto-fit option & internal margins.

Formatting a Cell:

MS Excel Cell can hold different types of data like Numbers, Currency, Dates, etc. You can set the cell type in various ways, Right Click on the cell & choose Format cells.

Explanation:

General: This is the default cell format of cell.

Number: This displays cell as number with separator.

Currency: This displays cell as currency i.e. with currency sign like $.

Accounting: Similar to Currency, used for accounting purpose.

Date: Various Date formats are available under this like 17-09-2016, etc.

Time: Various Time formats are available under this like 2:30 PM or 14:30 etc.

Percentage: This displays cell as percentage with decimal place like 50,00%

Fraction: This displays cell as fraction like 1/2, 1/4 etc.

Scientific: This displays cell as exponential like 5.6+E+01.

Text: This displays cell as normal text.

Special: Special formats of cell like Zip code, Phone Number.

Custom: You can use custom format by using this.

Changing Background Color:

The background color of the cell is while. You can change it as per your need.

Click on Home Tab from font group
Select Background color option

Changing Foreground Color:

The foreground or text color is black in MS Excel. You can change it as per your need.

Click on Home Tab from font group.
Select required foreground color from the option.

Home Tab, Font group & Foreground Color:

You can also change the foreground color by selecting the cell Right Click, Format Cell, Font Tab & Color Option.

Merge Cells:

You can Merge to or more than two cells. When you merge cells, you don't combine the contents of the cells. You can combine a group of cells into a single cell that occupies the same space.

Choose Merge & Center Control on the Ribbon.
Select the cells that you want to merge & then click the Merge & Center button.
Choose Alignment Tab of the Format Cells dialogue box to merge the cells.

Additional Options:

Merge Across: When a multi-row range is selected, this command creates multiple merge cells one for each row.

Merge Cells: Merges the selected cells without applying the center attribute.

Unmerge Cells: Unmerges the selected cells.

Using Filters in Excel:

Filtering refers to displaying only those records which meets the certain condition on the basis of certain criteria.

Place a cursor on the Header Row.
Choose Data Tab & Select Filter to set filter.
Click the drop-down arrow in the Area Row Header & remove the check mark from select all, which unselect everything.
Then select the check mark for anything which filter the data & displays data.

Using Multiple Filters:

You can filter the records by multiple conditions by multiple column values.

Wrap Text & Shrink to Fit:

If the text is too wide to fit the column width but do not want the text to spill over into adjacent cells, you can use either the wrap Text option or the shrink to fit option to accommodate that text.

Cell References in Formulas:

Most formulas you create include references to cells or ranges. These references enable formulas to work with the contained in those cells or rangers. For example, if your formula refers to cell C2 and you change the value contained in C2, the formula result reflects new value automatically. If you didn't use references in your formulas, you would need to edit the formulas themselves in order to change the values used in the formulas. When you use a cell (or range) references in a formula, you can use three types of references; relative, absolute and mixed references.

Relative Cell References:

The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column. By default, Excel creates relative cell references in formulas.

Absolute Cell References:

The row and column references do not change when you copy the formula because the reference is to an actual cell address. An absolute reference uses two dollar signs in its address : one for the column letter & one for the row number (for example, $F$7).

Mixed Cell References:

Both the row & column references is relative & the other is absolute. Only one of the address parts is absolute (for example, $A5 or A$5).

Functions in Excel:

A Function is a predefined formula that performs calculations using specific values in a particular order. All spreadsheet programs include common functions that can be used for quickly finding the sum, average, count, maximum value, and minimum value for a range of cells.

The Parts of a Function:

A function must be written in a specific way, which is called the syntax. The basic syntax for a function is an equal sign(=), the function name (SUM) and one or more argument. Arguments contain the information you want to calculate. The function in the example below add the values of the cell range A1:A20.

Working With Arguments:

Arguments can refers to both individual cells & cell ranges & must be enclosed within parentheses. You can include one argument or multiple arguments, depending on the syntax required for the function. For example, the function =AVERAGE(B1:B9) calculate the average of the values in the cell B1:B9. This function contains only one argument. Multiple arguments must be separated by a comma. For example, the function =SUM(A1:A3, C1:C3, E2) will add the values of all cells in the three arguments.

There are Various Functions. Some of the common functions are given below:

SUM: This function adds all the values of the cells in the argument.

AVERAGE: This function determines the average of the values included in the argument. It calculates the sum of the cells & then divides that value by the number of cells in the argument.

COUNT: This function counts the number of cells with numerical data in the argument. This function us useful for quickly counting items in a cell range.

MAX: This function determines the highest cell value included in the argument.

MIN: This included determines the lowest cell value included in the argument.

Links: