"How to Separate a Part Number into Three Columns"

Question : In the Parts catalog the items have a unique part numbers to identify the subject items from the catalog.  The unique number contains three segments namely Main assembly; sub assembly and item code.  All three are separated by a "-".  You have downloaded the parts number to a excel sheet from the ERP software. How do you separate the part number in to three columns and group it for further use.

Solutions :

Hint : We need to use the text column function given in the Data Ribbon (Excel 2007 above). In Excel 2003 the command can be called with the short cut key Alt D, E.

Step 1 :  Insert 3 columns next to parts number column. if you have data in the successive column.
Step 2 :  Select the parts number column
Step 3 :  Click on Text columns icon in the Data /Data tools ribbon the wizard will start run
Step 4 :  Select the Delimit option in the first step
Step 5 :  In the second screen select delimiter as "other" and mention "-" in the box to specify the special character.
Step 6 :  Click on the finish button.
Step 7 :  Excel may ask your permission to over write the content in the next columns.
Step 8 :  Click OK and find the results.

If you satisfy with the results please post your comments which will help us to improve on our publications content. 

Customizing the chart to prepare a professional presentation

Question: How do I customize the chart for the meaningful presentation

Answer: In Excel 2007 and above the charts can be easily customized with the command Format object (chart).  This is a common window have the properties needs to be changed.  In the below example the pie chart pan and pan explosion angle can be rotated by the properties window.

Copy paste the data in a excel sheet and select the data to insert the pie chart.

Sl No.	Month	Sales
1	Dec/10	10000
2	Jan/11	3000
3	Feb/11	25000
4	Mar/11	2300
5	Apr/11	3400
6	May/11	19000
7	Jun/11	400
8	Jul/11	4567
9	Aug/11	6000
10	Sep/11	6000

 

Right click the chart and select the format command.  In the menu select your choice and customize the chart as required and give a professional look.

How to Add the visible rows values in a list - MS Excel 2003, 2007, 2010

Questions :  Teacher want to put the total of the marks in a list of all students in the school.  He want to use the filter to get the data of class; section and subjects.

Solutions :  Use the '=SUBTOTAL(9,E8:E61)' to get the desired result.

Step 1:  Open the list of values
Step 2:  Select all the data and apply the auto filter function.  Short cut Alt D, F, F.
Step 3:  Goto the last cell of the column where the marks ends.
Step 4:  In the next row use the Auto sum function. Short cut 'Alt =' and press enter key.
Step 5:  Excel will build the subtotal function since the filter is applied in the data.
Step 6:  Change the reference series in the function as required to calculate the sum of marks in the visible cells.

Copy and paste the formula in a excel sheet tips - Relative and Absolute references

Question : How do we enter a formula in a sheet can be copy - pasted to other cells.  I am not getting the desired result in one shot and I need to change the formula after the paste activity.  Need a short cut to do it in one shot;

Answer : In excel we can use to types of reference i.e. absolute reference and relative reference.

Absolute Reference - will not change the focus while doing the copy and paste activity.  To do this use the '$' symbol in the formula.  The shortcut key is 'F4' key in the keyboard.  Press number of time to Toggle from freeze and non freeze the reference.

Relative Reference - This is the default option and when you copy and paste excel will automatically changes the references with respect to the movement of destination cell.

Example :

	E	F	G	H
	Amount	Running Sum	Discount	0.1
5	1446.94	=E5	=E5*$H$4
6	2135.89	=E6+F5	=E6*$H$4
7	1258.26	=E7+F6	=E7*$H$4
8	2137.48	=E8+F7	=E8*$H$4
9	2137.48	=E9+F8	=E9*$H$4
10	2137.48	=E10+F9	=E10*$H$4
11	2137.47	=E11+F10
12	1259.19	=E12+F11
13	1259.19	=E13+F12
14	1259.19	=E14+F13
15	1259.19	=E15+F14
16	1573.99	=E16+F15

 
The formula in the F column is in relative reference formula.
The formula in the G column is in absolute reference formula.

How to find the duplicate records in the database

My friends question;  Which is the method, good to find the duplicate records in the set of records entered in a worksheet.

Answer:

There are various methods followed to find the duplicate records in a database stored in a excel.

a.  In Excel 2007 and above, Conditional Formatting  is the easiest way.

b.  Use the 'countif' formula

c.  Use  a Pivot table function

d.  Use  Subtotal function

e.  Use Advanced filter

f.    Remove duplicate button  Alt A, M 

User has to chose the appropriate method to find the duplicate records. 

How to find the Principle and Interest component in monthly equal monthly installment by MS Excel 2010

Questions :  My friend has a request to create a sheet in which he wants to find the interest and principle component in his equal monthly installment on all the payment schedule.

My Solution :
Excel has a build in function PPMT() to find principle part of the EMI paid by him.  The PMT() Function will be used for find the EMI Value of the loan part.

The steps to create the sheet is;

A	B	C	D	E	F	G	H
5
6	Loan Amount	(5,00,000)
7	Term	18	Months
8	Rate	14%
9
10	PMT	Rs.30,958	=PMT(C8/12,C7,C6,0)
11				=PPMT($C$8/12,$C13,$C$7,$C$6)
12		Payment	Date	Principal	Interest	Balance
13		1	1-Dec-10	25,124	5,833	25,124
14		2	1-Jan-11	25,417	5,540	50,542
15		3	1-Feb-11	25,714	5,244	76,256
16		4	1-Mar-11	26,014	4,944	1,02,269
17		5	1-Apr-11	26,317	4,640	1,28,587
18		6	1-May-11	26,624	4,333	1,55,211
19		7	1-Jun-11	26,935	4,023	1,82,146
20		8	1-Jul-11	27,249	3,708	2,09,396
21		9	1-Aug-11	27,567	3,390	2,36,963
22		10	1-Sep-11	27,889	3,069	2,64,852
23		11	1-Oct-11	28,214	2,743	2,93,066
24		12	1-Nov-11	28,543	2,414	3,21,609
25		13	1-Dec-11	28,876	2,081	3,50,486
26		14	1-Jan-12	29,213	1,744	3,79,699
27		15	1-Feb-12	29,554	1,404	4,09,253
28		16	1-Mar-12	29,899	1,059	4,39,152
29		17	1-Apr-12	30,248	710	4,69,399
30		18	1-May-12	30,601	357	5,00,000

Step 1:  Enter the Loan amount in negative in a cell
Step 2 : Enter the Payment terms and rate of interest to be paid
Step 3: Use the PMT function to find the equal monthly installment "

=PMT(C8/12,C7,C6,0)"

Step 4: Create a table to find the every month principle with function. "=PPMT($C$8/12,$C13,$C$7,$C$6)"
 Step 5:  The interest component can be calculated by the formula "=$C$10-E13"
Step 6 :  The remaining outstanding value of the loan value will be estimated by the formula =$C$2+E13
Step 7 : Copy and paste the value above in a sheet and paste it in a excel sheet to understand the functions.

Please post your comments to make it useful for our friends. 

How to find absent date

My HR friend Question:

He got the personnel data from Time office in a excel book.  The book has number of sheets belongs to various employee attendance, i.e. one sheet for each employee.  The data has date and time recorded by the system on use of punch card / Tag.

My Solution:

He can use the 'Networkdays' function to find the missing dates. Please follow the below steps

Step1 : Insert a column next to present dates
Step 2 : Use the function - "=NETWORKDAYS(H5,H6,"Holidayrange")"
Step 3 : Copy and paste the function for all rows
Step 4 :  If value is 2 then he is continuously present. If value is more than 2 the he has taken leave in the period. 

Note - The function is applicable for five day working only. It will calculate Monday to Friday has working days.