Ms Excel (10 Days) Course

By default, Microsoft Office Excel provides three worksheets (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) in a workbook, but you can insert additional worksheets.

Ø Managing The Work-Sheet                            

Inserting A Worksheet

Ø At the bottom where you see sheet1/sheet2/sheet3, you have folder like button, just click the button to insert a new sheet at the end of the current sheets.

Ø For inserting before a given sheet click “Home Menu – Cells Tab – Insert Icon” and select Insert Sheet from the pull down menu.

Ø For inserting more than one sheets, hold the shift key and select the number of sheets from the sheets tab below and click ““Home Menu – Cells Tab – Insert Icon” and select Insert Sheet from the pull down menu.

Ø For Inserting Chart, Macro etc right click on the sheet icon at the bottom and select “Insert” and select the desired sheet/chart/macro etc.

Renaming a Worksheet

Click on the sheet you desire to rename and rt click on it and select “Rename” option and enter the new name.

Deleting a Worksheet

Click on the sheet you desire to delete and rt click on it and select “Delete” option.

Alternately you can also click “Home Menu – Cells Tab – Delete icon” and select “delete sheet” from the pull down menu.

Changing Font & Size

Click on “Home Menu – Font Tab” and select Font style and size from the options.

Bold/Underline/Italic buttons are also provided to change the font style.

Small and Big A/A are provided for increasing and decreasing the size of the font size.

Creating Borders for cells

Select the cells from the sheet and click “Home Menu – Font Tab” and from the Grids Icon select the Lt/Rt/Top/Bottom or middle grids to be displayed.  You can also set the weight/style/Color of the line by select the “more borders” option from the pull down menu of Grids icon.

Creating background colors for Selection

First select the cells you want to paint with background color and click “Home Menu – Font Tab” and click on the background color Icon and select the color you need.

Text color

First select the cells you want to paint with background color and click “Home Menu – Font Tab” and click on the Text color Icon and select the color you need.

Hiding and Un-hiding Columns

First block the columns/cells with the help of the holding the shift key and using arrow keys (← / ↑ / → / ↓).

Then Click “Home Menu – Cells Tab – Format Icon” from the pull down menu under “Visibility” select “Hide & Unhide” option.  Now select “Hide Columns/Hide Rows” as desired.

For Un-hiding columns block the columns/rows adjacent to the hidden ones, and click “Home  Menu – Cells Tab – Foramat icon” from the pull down menu select “Hide  & Unhide” option and select “unhide Coloumns/Rows” as needed.

Hiding and un-Hiding Sheets

Select the Sheet(s) from the bottom sheet list and click “Home Menu – Cells Tab – Format Icon”, from the pulldown menu select “Hide  & Unhide” option and click “Hide sheet”.  OR Rt click on the sheet and select “hide” option.

For Un-hiding sheets click “Home Menu – Cells Tab – Format Icon”, from the pulldown menu select “Hide  & Unhide” option and click “Un-hide sheet”.  OR Rt click on any sheet and at the bottom and select “unhide”.

Changing Default Font or size for new worksheets

1.  Click the Microsoft Office Button , and then click Excel Options.

2.  In the Personalize category, under When creating new workbooks, click a font in the Use this font box, and then specify a font size in the Font Size box.

Freeze or lock rows and columns

Freezing : Making a Column/Row static so that they don’t move. 

If you want to see the heading of the column while scrolling down to rows far below then freeze the Top row containing heading.

If you want to see the Left column containing information while scrolling towards far right freeze Left column.

First place the cursor at the cell from which left and above columns are to be freezed and click “View Menu Windows Tab – Freeze Panes”, and select Freeze Top Row for freezing rows and select Freeze Left Column for freezing column.   If you desire to freeze both columns and rows select “Freeze Panes”.

To unfreeze click “View Menu – Window Tab – Freeze Panes Icon”, and select the option to unfreeze.

Split panes to lock rows/columns

1.  To split panes, point to the split box at the top of the vertical scroll bar or at the right end of the horizontal scroll bar.

2.  When the pointer changes to a split pointer or , drag the split box down or to the left to the position that you want.

3.  To remove the split, double-click any part of the split bar that divides the panes.

Inserting formulas

While inserting formulas you have to use “ = ” sign before entering the formula.  There are several in-build formulas in Excel, like Sum (adds the cell range), Average(gives average of the cell range), min(gives the min value out of the range), max(gives max value of the range) , acos(gives arc cosine of the given cell/value).  Range is specified by giving a “ : “ symbol between first and last. Cells should be in line or in diagonal position.

Copying and Pasting Cells

Select the cells you want to copy first by blocking them with shift key.

For pasting click  “Home Menu – Clipboard Tab”, and from the Paste Icon pull down the menu and select the option you need to paste.

Paste : This is equal to using Ctrl + C and Ctrl + V, but the result does not appear immediately.  You have click on the folder icon for options (a) Keep Source Formatting, (b) Use Destination theme (c) match destination formatting (d) Values only (e) Values and No formatting (f) Values and source formatting. (g) Keep source column widths (h) formatting only (i) links.

Formulas :  only formulas are copied to the destination.

Paste Values :  Only values are copied to the destination.

No Borders : Values are copied without borders in it.

Transpose : Columns are changed into respective rows.

Paste Link :  Refer to the Source.  ie., if source number/text changes even the destination changes.

Paste Special : You have all the options available in the form of check boxes for copying.

As Picture : The source is copied as an image.  You cannot edit the cells.

Cut :  removes the source and places as destination cells.

Format Painter :  Select the cells from which you want to select its format and Click Home Menu – Clipboard Tab and select the format painter icon (brush) and click in the cell/cells you want to copy the formats.

Using if condition and producing text labels

S No.	Education	Code
5001	Anup	3
5002	Krishna	2
5003	Tamang	1
5004	Manisha	4
5005	Purush	5

Using this table produce Text labels. Codes are as follows: -

Educational Code             Remark

1                                                                 PhD.

2                                                                 Masters

3                                                                 Graduate

4                                                                 Under Graduate

5                                                                 SLC

Educational Code : ED.

HINT:  =if(ED =1, “PhD”, if(ED=2, “Masters”, if(ED=3, “Graduate”, if(ED=4, “Under Graduate”, if(ED=5, “SLC”) ) ) ) )

Replace all ED with the cell no of Code

Text Alignment/Wrapping

Select the cell/cells you want to align text/nos.  Click on Home Menu and select “Alignment” tab : and

(a)                     Select the Top/Middle/Bottom icons for aligning the text on to top/middle/bottom of the cells.

(b)                    Select Lt/Centre/Rt icons for aligning the text left/middle/right.

(c)                     Select the Text direction icon for changing the direction of the text.  You can toggle repeatedly to change the text direction.

(d)                    Indent Text 5 spaces left or right by clicking on Lt indent/Rt indent.

(e)                     Click on Text wrapping icon to wrap text so that it is displayed in cells across or below as needed.

(f)                       For Merging /Un-merging cells select the merging icon and click the option you need.

Formatting Numbers

Select the cells you want to format first and click “Home Menu – Number Tab” and

(a)                     Select Text area displayed with “General” and click on the format you need like General, number, currency, accounting, date, time, percentage, Fraction, scientific, text, special, custom.

(b)                    Three recently used icons are present on the Number tab.  Two icons to increase/decrease the decimal places of number are also provided.

Conditional Formatting

Conditional formatting is formatting the shades/colors of the cells based on certain rules.  There are different types of rules.  Always the minimum values and maximum values are taken into consideration, and an average point is also taken.  The patterns/colors are applied based on these values as follows.

Highlight Cell Rules

Select the rows and Click “Home Menu – Styles Tab -  “Conditional Formatting” - “Highlight Cell Rules” and select one of the following.

“Greaten than” allows you to highlight the numbers from the selection which are greaten than the given number.

“ Less than” allows you to highlight the values lesser than the given value.

“Between” allows you to highlight the values between the given two values.

“Equal to” allows you to highlight the values which are equal to the given value.

“Text that contains”, allows you to highlight the text from selection which matches the given text.

“a date occurring” allows you to highlight the date from the selection.

“duplicate values” allows you to highlight the duplicate values, if any, in the given selection.

Top/Bottom Rules

Select the rows and Click “Home Menu – Styles Tab - “Conditional Formatting” - “Top/Bottom Rules” and select one of the following.

Top 10 Items Allows you to highlight top ten items in value from the given selection.

Top 10% Allows you to highlight cells whose values are within top 10% of the given selection.

Bottom 10 Items Allows you to highlight bottom ten items values from the given selection.

Bottom 10 % Allows you to highlight cells whose values are within bottom 10% of the given selection.

Above Average  Allows you to highlight all values whose values are above average.

Below Average Allows you to highlight all values whose values are below average.

Data Bars

This selection has different colors representing the bar diagrams on the values according to their selection. Ie, the minimum values and maximum values are taken and bar diagrams are formed according to the values.

Select the cells and Click “Home Menu – Styles Tab -  “Conditional Formatting” - “Data Bars” and select one of the pre-defined pattern colors.

Color scales

This selection has different colors representing warning and peace messages. Ie, the minimum values are marked with green showing that they are OK. and maximum values are shown with red color to have attention of the user

Select the cells and Click “Home Menu – Styles Tab -  “Conditional Formatting” - “Color Scales” and select one of the pre-defined pattern colors.

Icon Sets

The values of the selection are represented like a traffic lights, racing flags, angled arrows etc. to indicate how much a value is different from the minimum and maximum values.

Select the cells and Click “Home Menu – Styles Tab -  “Conditional Formatting” - “Icon Sets” and select one of the pre-defined pattern colors.

Formatting cells with table style.

The selected cells are painted with different colors schemes with different intensity to show the differences between the values it contains.

Select the cells and Click “Home Menu – Styles Tab -  “Format as Table” and select one of the pre-defined pattern colors.

Formatting with Cell Styles.

The selected cells can be painted individually with different color/comments/calculation/explanation etc.

Select the cells and click “Home Menu – Styles Tab – Cell Styles and select one of the predefined patterns.

Inserting Cells/Sheet Rows/Columns

Click “Home Menu – Cells Tab – Insert Icon” and select “Insert cells” to insert the cells at the place of the cursor.  The remaining cells/entire row or column can be moved in either left/up or entire row/column..

Select “Insert sheet Rows” for inserting rows above the current cell.

Select “Insert Sheet Columns” for inserting columns before the current cell.

Deleting Cells/Sheet Rows/Columns

Click “Home Menu – Cells Tab – Insert Icon” and select “Insert cells” to insert the cells at the place of the cursor.  The remaining cells/entire row or column can be moved in either left/up or entire row/column.

Select “Delete sheet Rows” for deleting rows below the current cell.

Select “Delete Sheet Columns” for deleting columns below the current cell.

Setting Cells Height/Width

Click Home Menu – Cells Tab – Format Icon and select one of the following.

Row Height  will allow you to Set the height with the desired size in numbers.

Autofit Row Height :  Select the cells first and then click on this option to set the Height of the cells according to their contents.

Column Width will allow you to set the width to the desired size in numbers.

Autofit Column Width Select the cells first and then click on this option to set the Width of the cells according to their contents.

Default Width Select this option to set the width to default value which is 8.43.

Changing the Tab Color of the sheet.

Select Home Menu – Cells Tab – Format Icon, and from the pull down menu, under organize sheets select “tab colors” and click on the color you need to use for the current sheet which will appear in the tab below.

Protecting Cells

Cells/sheets can be protected from unauthorized editing of the contents.  We can provide protection at different levels like Cells formatting, inserting/deleting cells, inserting hyperlinks etc.  Once the cells are protected they can be assigned a password so that you are prompted with password before you can again set it to editing mode.

Click “Home Menu – Cells Tab – Protect Sheet”, and select the desired level of protection by clicking the checkbox and enter a password in the text area.  You will be prompted with a dialogue box to reconfirm the password.

For setting the cells for editing mode click on the Format Icon and select “unprotect sheet”.

Auto summing the values

Select the Cells you want to sum the values and click “Home Menu – Editing Tab “, and pull down the menu under the icon “ ∑ ”  and select “Sum”.  A product sum of the selected cells appears under the selection.

Creating Average of Values

Select the Cells you want to sum the values and click “Home Menu – Editing Tab “, and pull down the menu under the icon “ ∑ ”  and select “Average”.  An average of the selected cells appears under the selection.

Counting Number of values

Select the Cells you want to sum the values and click “Home Menu – Editing Tab “, and pull down the menu under the icon “ ∑ ”  and select “Count Number”.  A no indicating the number of values appears under the selection.

Finding Max/Min of values

Select the Cells you want to sum the values and click “Home Menu – Editing Tab “, and pull down the menu under the icon “ ∑ ”  and select Max or Min.  A number is placed under the selection indicating that it is the Max/Min.

You can also find more functions under “More Function” option under this Icon.

Filling cells with a given value

We can fill the cells right/left/up/down with a certain value/text instead of typing repeatedly.

First enter a value/text for the cell and block the cells towards right/left/up or down along with the cell containing the value/text and click “Home Menu – Editing Tab – and select “Fill Icon ê  under “ ∑ ”  icon.  Select down/right/up/left options as you desire to fill.

You can also drag the cell selection to the empty place adjacent to it with the dragging corner of the cell.  Then you are prompted with    button.  You can select the auto fill options as following (a) Fill Format Only (b) Fill Without Formatting (c) copy cells (d) Fill Series.

Filling Cells with Series.

To fill with a series.  Select atleast two items of the series and select “Home Menu – Editing Tab – fill Icon ê ”, From the pop up window select the series with a step value and if needed, a stop value in the columns provided and click ok.

“Linear” will allow you to increase the value by a step by adding and “Growth” will allow you to multiply the value with the step value.

Pay Roll Development with Excel Sheet :

Name	Basic	Dearness Allowance	House Rent Allowance	Total Income	Insurance	Medical	Total Deductions	Net Salary
Ram	3000
Sham	3200
Krishna	4000
Vijay	5200
Anuj	2700
Dhiraj	5000

Conditions are

DA =  45% of Basic

HRA = 15 % of Basic

Total Income = Basic + DA + HRA.

Insurance = 500

Medical = 150

Total Deductions = Insurance + Medical

Net Salary = Total Income – Total Deductions.

Link cells to give changes in the rate of DA and HRA.

Hint : Make a cell apart from the table and refer this value to DA/HRA.  Ex: = B1 * (A10  * .45).  Where A10 would be the cell for entering DA/HRA.

Calculate Net Salary.

Clearing Formats/contents/Comments

You can clear the cells with different elements like clearing formatting only, contents, and comments.  Select the cells you want to clear and Click Home Menu - Editing Tab” and select the icon with “eraser like tool”. Select “Clear All” for clearing all the formats contents etc.  Select “clear Format” for clearing only formats while keeping the values.  Select “clear Contents” for clearing the values/contents and keeping the formats. Select “comments” for clearing only comments.

Sorting Cells

Select the cells for sorting and click “Home Menu – Editing Tab – Sort & Filter Icon”, and from the pull down menu Select “Largest to Smallest” option to sort from largest to smallest value.

Select “Smallest to Largest” option to sort from smallest to largest value.

Select “Custom Sort” option if you need to mention a special sorting order base on single/double condition level.  In this option you can add/remove different levels of sorting on same column/row and also have options like case sensitive, top to bottom, left to right etc.

Filtering Cells

You can filter the cells without disturbing the figures entered into the cells for analysis.  Select the cells you need to filter and click ““Home Menu – Editing Tab – Sort & Filter Icon”, and from the pull down menu select “Filter” option.  An icon appears on the top click that and see the display while filtering with the available options of “ascending and descending” options.  You can also have filters based on colors and numbers.  To remove them click the “clear” option available under the “filter” option.

Find/Replace Cells

You can find and replace values, formulas, comments etc.

Click Home Menu – Editing Tab – Find & Select Icon”, and select the type from the options available under the line.  Select “Goto” for going to a specified location.  Select Find to find format/value/text etc.  Select Replace to replace the contents with the given options. Constants gives values, formulas gives formulas, and likewise.  All the found values will be colored with ask color.

Show current Date and Time

Click on the cell you want to enter the date and type “=now()”

EX:  Prepare Electric Billing Sheet.

Upto 20 units      -      Only Rs 78.00

21 to 250 units   -      Rs.7 per unit

Above 250 units        -      Rs.10 per unit

Consumer No (B3)	Name	Current Reading	Previous Reading	Unit consumed	Bill Amount
501	Suraj	4546	4496	= D4 – E4
502	Anuj	5657	5450	= D5 – E5
503	Dhiraj	2342	2200	= D6 – E6
504	Vanitha	5646	5402	= D7 – E7
505	Arpana	3424	3120	= D8 – E8

      

Units Consumed : UC

UC = Previous Reading – Current Reading

Bill Amount formula = if(UC<=20, 78,  if(UC<=250,(UC-20)*7+78,   if(UC>250, (UC-250)*10+(250-20)*7+78)))

Hint:  replace UC with your cell containing the Units Consumed details.

Using Pivot Tables in Excel

PivotTable is a table form of data which will allow you to analyze and form reports based on certain conditions.

Ex:

Ser No.	Date	Item	Rate	Qty	Amt
1024	3-4-2008	P4 Processor	8000	12
4395	5-4-2008	Mother Board	2200	22
4321	3-4-2008	SMPS	240	42
5432	4-4-2008	Flat Screen	12000	7
6435	4-4-2008	UPS	2300	25

Enter this data into a sheet.  Enter Amt as “=Qty *Rate.

Select Ser No to Amt by blocking and click on “Insert Menu – Tables Tab and click on the icon Pivot Table.  Select Pivot Table.  Automatically the selection appears in the cells given.  Select “Existing table” and click on empty cell and select the fields from the right panel. Select Date first, then Item, and then Qty.  You will have a detailed analysis in the pivot table.  You can even filter the table if needed by pressing on the pull down button.

What if Analysis using a Table.

This type of analysis is useful for getting results based on a single/double factors.

For analyzing monthly payment for a given mortgage loan with different values of “Terms and Rates” enter the following table.  We use PMT inbuild function which calculates the payment for a loan based on constant payments and a constant interest rate.

Mortgage loan analysis
down payment	None		180	360
interest rate	7.50%	9.75%
term (months)	360	9.80%
Loan Amount	80,000	10.15%

Type the formula in the empty column provided beside NONE as “= PMT(Interest Rate/12, Term, - Loan Amt)”, select and block the cells from “empty cell” downwards till cell containing “360” column.  Now click “Data menu – Data Tools Tab – What-If-Analysis”, and select Data Table.  You are prompted with a window containing two text boxes.  In the “Row input Cell” enter cell no containing “360”, in the “column input cell” enter cell no containing “7.50%” and click ok.  You are given the results in the empty cells.

Inserting Picture, Clip Art, shapes, Smart Art

To insert picture, clip art, shapes and smart art click “Insert Menu – Illustrations Tab” and select the icon you want and insert.

Inserting Charts

To insert Charts click “Insert Menu – Charts Tab” and select Column/Line/Pie/Bar/Area/Scatter/Other Chart, as needed. A sample data is entered by default,  you can enter the data by right clicking on the chart diagram and selecting edit data.

Inserting Hyperlink

Place the cursor on the cell you want to hyper link and click Insert Menu – Links Tab – and select “Hyperlink” icon.  From the File dialogue box browse the file you want to connect and click ok.

Inserting Text Box, Header & Footer, WordArt, Signature Line, Object, Symbol

Click “Insert Menu – Text Tab” and from the given options select the one you need and place them on the sheet.

Inserting themes, Colors, Fonts, Effects

Themes are predefined styles of text.  Click “Page Layout Menu – Themes Tab” and select the icons you want to use.

Adjusting Margins, Orientation, Size, Print Area, Breaks, Background, Print Titles

While printing excel provides you with a variety of options like print area select, complete page etc.  Especially it allows the data to be reduced and increased based on percentage.  Lot of adjustments can be done and viewed in the print preview.  Click “Page Layout Menu – Page Setup Tab” and select the icons you want.

Grouping graphic images, wrapping text

Images are grouped into single so that the adjustment is not disturbed by any one.  If four images are grouped together, all of them move when you try to move one image.  For grouping select the images, and click “Page Layout Menu – Arrange Tab and select the group icon.  Wrapping and aligning is also done from “Arrange” pane itself.

Types of functions

There is a rich library of different functions offered by the Excel

(a)                     Add-in and automation functions : Help in connecting database, and other programming aspects.

(b)                    Cube Functions :  Analysis of members of a types of data and their properties, like monitoring a organisaztions performance etc.

(c)                     Database functions: Performs calculations on Database entries.

(d)                    Date & Time functions: performs calculations on date and time.

(e)                     Engineering functions: calculations on numbers like binary, decimal, measurements, logarithm etc.

(f)                       Financial functions :  performs calculations on money like interest, depreciation, loan amount etc.

(g)                    Information functions: gives information on the given cells like format, location, type of value in cell.

(h)                    Logical functions:  gives results based on logical decision like true, false, if, not, or.

(i)                       Lookup & ref functions: gives results based on some query for tables, cells, refs

(j)                       Math & Trigonometry functions :  performs calculations on mathematical functions like LCM, ACOS.

(k)                    Statistical functions: helps in statistical analysis of the data in the cell like average, count, max, min.

(l)                       Text functions :  helps in manipulating text like trim, upper, search, len etc.

For using these functions click “Formulas Menu – function library Tab – and select the function you want to insert.  Some basic functions are provided in the “AutoSum” icon and recently used functions are automatically placed in “Recently used” icon for ease of working.

Using names instead of cell range

When we are performing tasks on a specific cell range repeatedly, we can name the range and use the name in place of cell ranges. Ex: we can name a cell range B1:B2 etc with “myrange” and use myrange as a variable in places where we use ranges, like “= SUM(myrange)”.

Cell names have scope for usage. You can manage all the names with “Name Manager”. Name Manager carries the list of all the names, its values, references, scope & comment, if any.  You can add new names and also filter the existing name cell ranges.

Enter few numbers in cells, and click “Formulas Menu – Defined Names Tab – Define Name Icon”, and click on Define Name option.  In the window enter the name, scope of availability, comments, and cell range in “refers to” text box and click OK.

Place the cursor in new cell and sum the range by using “=SUM (name)”.  You will get the result of the sum.

Creating names & using in formulas

Enter the “aptech” in a cell and enter “this is in       kantipath” in the cell besides it.  Now place the cursor in other cell and click on “create from selection” and select these two cells with aptech and “this is in     kantipath” click on left column in the window and click ok.  Enter “=trim(“ in a cell and click on “aptech” from the list and enter “)”. And click enter.  You find that the second cell is trimmed and shown in the new cell.

Watching Formulas

 Watch Windows is a concept for watching the formulas in another section/sheet/workbook.  There is no need to scroll all over to the place where the formulas lies.  A window with all the formulas you enter is available as a toolbar, which can be placed anywhere.  You can add and remove entries in the watch window.

Enter few formulas in the sheet and click “Home Menu – Editing Tab – Find & Select- Go to Special”, and click the check box with “formulas” in the window.  Now Click “Formulas Menu – Formula Auditing Tab – Watch Window”.  You are provided with a window with all the formulas in the sheet.  You can enter/delete from the list.

Trace Precedents

This option shows the values or cells which effect this particular cell which as been selected.

Enter a value in a cell(B2) and click on another cell(F10) and enter ref of the first cell (type “=B2”).  Now click “Formulas Menu – Formula Auditing Tab – Trace Precedents Icon.  An arrow appears from the first cell to the one you are located, indicating that this cell is dependent on it.

Trace Dependents

This facility allows you to see the cells which are depending on the result of the current cell. 

Enter text in a cell (B2) as “My value” and refer this cell in a second cell (enter “=B2” in F10 and F14).  Now click on the first cell (B2) and click “Formulas Menu – Formula Auditing Tab – Trace Dependents”, you will see two arrows coming from the first cell (B2) to the two cells (F10 & F14), showing that F10 and F14 are dependent on B2 cell.  Now change the value of B2 to “Values” and see that both the values of (F10 & F14) changing.

To remove the arrows Click on “Remove Arrows” icon in the same tab.

Showing Formulas

Click “Formulas Menu – Formula Auditing Tab – Show Formulas” and you can see all the formulas in the present sheet.  You can use it as a toggle for on/off seeing formulas.

Trace Errors

Excel provides with a system for checking the errors in the entire sheet at once.  For this click “formulas Menu – Formula Auditing Tab – Error Checking Icon”.  It will automatically check for all the errors in the sheet.

Evaluating Formula

Excel provides you to check the formulas in a step by step procedure. Place the cursor in the cell you are having the formula and which you want to see step by step and click “Formulas Menu – Formula Auditing Tab – Evaluate Formula icon”.  A window with step by step procedure is provided to see how the formula is calculating the formula.

Auto Configuring Calculations

Calculations can be done automatically as and when you update a formula or data. You can also manually perform the calculations if necessary. Generally the Table data takes more time when included in the calculations.  So you can exclude table data while performing calculations.

Click Formulas menu – Calculation Tab – Calculation Options and check the “Automatic” or “Automatic except for table data”. If you want to stop automatic calculations and perform them manually, click “Manual” in the above options.

For performing calculations manually click Formulas Menu – Calculation Tab” and click the bottom last icon. OR (Shift + F9).  For performing calculations for the entire Work Book use “top right” button (OR) F9.

Accessing External Database

Other databases can be incorporated into MS Excel.  Click “Data Menu – Get External Data Tab” and select

(a)                     “From Access” for importing data from MS Access.

(b)                    “From Web” for importing data from Website.

(c)                     “From Text:” for importing data from a text file.

(d)                    ”From Other Sources” for importing data from SQL Server, XML file, or any other sources with the data connection wizard or MS query.

Most of the data imported may not be up to your expected levels of formats.  You might need some formatting.  Manage the existing connections with the help of “Existing Connections” icon which is located in the Get External Data tab of Data Menu. And configure the connections through “connections tab”.

Converting text into columns

You can convert text into multiple columns based on the delimiters.  For example enter the following data in the cells.  Every Name should be entered in single cell.

Rana Pratap singh

Avinash Datta Ch

Ranga Babu Chakra

Mani Kanta thapa

Viren Pratap Singh

You can split the names into three columns containing first name, second name, and last name.

Click on the cells and click “Data Menu – Data Tools Tab – Text to Columns Icon” and select the “Delimited” option from the window and click “next” button and select “Space” check box and click next button and in the next option select “Text” and click finish button.  You can see the names divided into three cells.

Filtering Duplicates

Enter data in a column and select the range.  Click Data Menu – Data Tools Tab – Remove Duplicates Icon” and select “Continue with the current selection” option from the window and click OK.

Data Validation

This is a process to ensure that the user enters only valid data. Some times invalid data may pose problems for calculations.  You can either stop or process with a warning.

Select cell(s) for validating the data and Click “Data Menu – Data Tools Tab – Data Validation Icon” and select “Data Validation”.

(a)                     Settings Tab will let you to set the limits for the data entered, like whole numbers, text length etc. You can enter the condition for the data like values between, values lesser than etc in the second text box. Set the minimum and maximum values in respective columns.

(b)                    Input Message Tab will allow you to enter the message to be displayed when the cell is highlighted.

(c)                     Error Alert Tab will allow you to “Stop” Entering Data,  Give “Warning”  and give “Information” while entering data which is out of the range. A message also may be given to display when error.

After the above settings click OK.  And try entering data out of the specified range.

You can circle the invalid data by clicking “Data Menu – Data Tools Tab – Data Validation” and select “Circle Invalid data”.  If you don’t want to see the circles you can enter “clear validation circles”.

Consolidating Data sheets

Data can be brought from different sheets/workbooks and imported into one sheet. While importing, the data can also be filtered and kept connected with the data source.  When the source data changes the consolidated data can also be projected accordingly.

Make two books viz book1 and book2.  Enter the following data in book1.

	Rate	Monday	Tuesday	Wednesday
Processor	6000	23	43	25
Monitor	9000	33	54	34
SMPS	470	43	33	44
UPS	1700	44	2	55
Mother Board	2100	12	45	39

Enter the following data in book2.

	Rate	Monday	Tuesday	Wednesday
Processor	6000	34	22	13
Monitor	9000	43	45	23
SMPS	470	33	32	32
UPS	1700	54	43	43
Mother Board	2100	2	55	42

Keep both the book1 and book2 open.  We are going to merge the table of book 1 with the data of sheet 1 in the Sheet 2 of Book2. 

Now goto Sheet 2 in Book 2 and click “Data Menu – Data Tools Tab – Consolidate Icon”, and click on “Browse” and select Book1 and click OK.  While the dialog box is active click on the Book 1 which is open and select the range of data and click on “Add” button of the open dialog box. 

Now click “Browse” button and select book2 and click OK.  Click on sheet 1 of book 2 and select the range and click “Add” in the open dialogue box.

Now click check boxes “Top Row” and “Left Column” and also select “Create links to source data”. Goto Book 2 sheet 2 and click on B3 cell.  Now ensure the reference text box is clear in the open dialogue box and click OK.

You have the values summed and placed in sheet 2.  Change the data in sheet1 or book1 and see the difference effecting the values of the book2, sheet 2.

Grouping Cells

Group cells together so that you can collapse or pull down when you need.

Select the cells you want to group and click “Data Menu – Outline Tab” and select Rows/Columns together and click OK. You can click on the plus sign beside the group for pulling down or “-“ sign for collapsing the group.  You can ungroup by pressing “Ungroup” icon.

Goal Seek

Goal seek is a process which will help you give the required figure, if you know the target value which have to arrive at.  Enter the value you want to seek and give the cell address of which should be the deterrent for the target value.

Lets use X² + Y² = R² the formula for finding coordinates on a circle.  If you know X and want to adjust Y so that it should equal a known value of R.  Enter some values for X, Y, Z.

36

64

Enter “=SQRT(POWER(C3,2)+POWER(D3,2))” in E3 while the values of x and y are in C3 and D3.  Now click “Data Menu – Data Tools Tab – What-If-Analysis Icon” and select “Goal Seek” option.  You are prompted with (A) Set cell (B) to Value (c) By changing Cell.  In the “Set Cell” type “E3”, in the “To Value” set the desired number (645), and in “By Changing Cell” type D3.  You can see that the Goal seek will change the value in D3 so that to reach the set value of R in E3 and a result is displayed.

Scenario Manager

In the above you have seen that the values are changing inside the sheet itself.  At times when you need to know the result but do not want to disturb the present sheet values, you can use Scenario Manager.

This scenario summary will show what total profit would you get by selling certain qty of items.  In three scenes we have different total profits which would be the result of changing the qty of the items.  Enter the following table in the cells keeping B3 as top left corner.

(B3) Cell	Cost Price	Selling Price	Profit	Quantity	Total
CPU	14000	18500	=D4 - C4	12	E4 * F4
SMPS	450	640	=D5 – C5	13	E5 * F5
UPS	1200	1470	=D6 – C6	14	E6 * F6
					=SUM(G4:G6)

                                        Changing cells

Click on the “Data Menu -  Data Tools  Tab – What-if-analysis Icon”, and select “Scenario Manager”.  Click “Add” button and type “one” in Scenario name text box.  Enter “F4:F6” and click OK. 

In the 1^st text box enter “8”, in 2^nd text box enter 7 and in the 3^rd text box enter 12.

Click “Add” button and type “one” in Scenario name text box.  Enter “F4:F6” and click OK.

In the 1^st text box enter “16”, in 2^nd text box enter 26 and in the 3^rd text box enter 24.

Click “Add” button and type “one” in Scenario name text box.  Enter “F4:F6” and click OK.

In the 1^st text box enter “20”, in 2^nd text box enter 24 and in the 3^rd text box enter 22.

Three values which you are entering are the quantity of items, by selling which we would get the different profits.

Click “Summary” and select scenario summary.  Type G7 in Result cells text box.

Working with comments

Adding Comment :  Place the cursor in the cell and click “Review Menu – Comments Tab – New Comment Icon” and add the comment in the text box.

Navigating Comments: click “Review Menu – Comments Tab – Next / Previous” and see the comments.

To see all comments press click “Review Menu – Comments Tab – show all comments”.

Protecting Cells, sheets and sharing

Protecting Formulas

To hide any formulas that you don't want to be visible, do the following:

In the worksheet, select the cells that contain the formulas that you want to hide.  On the Home tab, in the Cells group, click Format, and then click Format Cells.  On the Protection tab, select the Hidden check box, and then click OK.  And then protect the sheet.  Once the protection is done the formulas will not be visible.

Protect sheet : click “Review Menu – Changes Tab – Protect Sheet Icon.  Select what you want to lock and how other users access this sheet by clicking in the check boxes.  Type the password in the text area and click OK.

Protect Workbook :  click “review Menu – Changes Tab – Protect Workbook Icon”. And select “protect structure and windows” option.   You have two options (a)Structure & ( b)windows.

(a)                     Structure option will protect the workbook elements from editing, deleting etc.

(b)                    Windows option will let you protect you window so that whenever you freeze or resize your window according to your specific needs and wanting to see them the same way when you open the window next time.

You can type the password, you cannot open it without password, so remember it.

Working with views

Click “view Menu – Workbook views”

(a)                     “Page Layout” will allow you to see the sheets as different pages.

(b)                    “Page Break Preview” will show you page breaks a blue lines, which can be dragged with mouse to set the size of the page.

(c)                     “full screen” view will allow you to see in full screen.  Press Esc for getting back to normal.

Zooming

To zoom to a particular selection select the cells and click “View – zoom – zoom to selection”.

Opening new window with same document

Click “View – Window – New window”.

Arranging Open windows

Click “view – window – Arrange All”

Splitting window views

Click “view – window – split”, will give you multiple views of different parts of the same sheet.

Hiding Window

Click “view – window – Hide”, will hide the window to unhide the current window select “Unhide”.

Using Macros

Click “View – Macros – Record Macro”, type the name in the text box and a description, if needed, then perform some operations and after finishing click “macros – stop recording”.

Click macros – view macros “ and select the macro you want and click “run”. 

To delete a macro click “Macros – View macros – View macros” and click on “Delete”.