Unit-3.pptUnit-3.pptUnit-3.pptUnit-3.ppt

hhh

1. Editing and Formatting Tools Editing and Formatting Tools
and Techniquesand Techniques

2. Editing and formatting tools and techniquesEditing and formatting tools and techniques
- Substitute function- Substitute function
- Text to Column- Text to Column
- Mid, Right, Left Functions- Mid, Right, Left Functions
- Find Function- Find Function

3. Substitute Substitute – Replaces existing – Replaces existing texttext with new with new text text on the on the
stringstring

4. SubstituteSubstitute
Exercise 6:Exercise 6:Capitalize the letter "a of the word arthur" in Capitalize the letter "a of the word arthur" in
the name Macarthurthe name Macarthur

5. Text to Column Function Text to Column Function – Used to parse data, – Used to parse data,
especially for those with consistent delimitersespecially for those with consistent delimiters

6. Text to Column FunctionText to Column Function
Exercise 7:Exercise 7:Create separate columns for "Item" based on Create separate columns for "Item" based on
the details after each hyphenthe details after each hyphen

7. Text to Column FunctionText to Column Function
Exercise 7:Exercise 7:Create separate columns for "Item" based Create separate columns for "Item" based
on the details after each hyphenon the details after each hyphen
1. Copy the item number to column F. 1. Copy the item number to column F.
2. Select the entire range of data in column F. Place the 2. Select the entire range of data in column F. Place the
cell pointer in F2. Hit the End key. While holding down cell pointer in F2. Hit the End key. While holding down
the Shift key, hit the Down Arrow key to select the entire the Shift key, hit the Down Arrow key to select the entire
range.range.

8. Text to Column FunctionText to Column Function
Exercise 7:Exercise 7:Create separate columns for "Item" based Create separate columns for "Item" based
on the details after each hyphenon the details after each hyphen
3. From the menu, select Data – Text to Columns. The 3. From the menu, select Data – Text to Columns. The
Wizard will work on either data that is delimited or on Wizard will work on either data that is delimited or on
data that has a fixed width to each segment. Our data is data that has a fixed width to each segment. Our data is
delimited by a dash. As shown in Step 1, leave the radio delimited by a dash. As shown in Step 1, leave the radio
button on the Delimited setting. Click Next.button on the Delimited setting. Click Next.

9. Text to Column FunctionText to Column Function
Exercise 7:Exercise 7:Create separate columns for "Item" based Create separate columns for "Item" based
on the details after each hyphenon the details after each hyphen
4. Uncheck the Tab checkbox. Check the Other 4. Uncheck the Tab checkbox. Check the Other
checkbox. In the Other textbox, enter a dash. Click Next.checkbox. In the Other textbox, enter a dash. Click Next.
5. In Step 3, you can optionally specify the data type of 5. In Step 3, you can optionally specify the data type of
the columns. Unless you have, the General type is OK. the columns. Unless you have, the General type is OK.
Click FinishClick Finish

10. Text to Column FunctionText to Column Function
Exercise 7:Exercise 7:The resulting data should be like thisThe resulting data should be like this

11. Find, Mid, Left and Right FunctionsFind, Mid, Left and Right Functions
Exercise 8:Exercise 8:Use the Find, Mid, Left and Right Functions Use the Find, Mid, Left and Right Functions
to get the results as in Exercise 7to get the results as in Exercise 7
Find(Find_text, Within_Text, Start_num)Find(Find_text, Within_Text, Start_num)
Mid(Text, Start_num, Num_chars) Mid(Text, Start_num, Num_chars)
Left(Text, Num_chars) Left(Text, Num_chars)
Right(Text, Num_chars) Right(Text, Num_chars)

12. Find, Mid, Left and Right FunctionsFind, Mid, Left and Right Functions
Exercise 8:Exercise 8:Use the Find, Mid, Left and Right Functions Use the Find, Mid, Left and Right Functions
to get the results as in Exercise 7to get the results as in Exercise 7
1. Use =FIND(“-”,A2) in cell I2 to locate the first dash.1. Use =FIND(“-”,A2) in cell I2 to locate the first dash.
2. Enter =FIND(“-”,A2,I2+1) in cell J2. The I2+1 2. Enter =FIND(“-”,A2,I2+1) in cell J2. The I2+1
parameter tells Excel that you want to find a dashparameter tells Excel that you want to find a dash
starting in the fourth character position of cell A2.starting in the fourth character position of cell A2.

13. Find, Mid, Left and Right FunctionsFind, Mid, Left and Right Functions
Exercise 8:Exercise 8:Use the Find, Mid, Left and Right Functions Use the Find, Mid, Left and Right Functions
to get the results as in Exercise 7to get the results as in Exercise 7
3. Use =LEFT(A2,I2–1) in K2. The formula in K2 locates 3. Use =LEFT(A2,I2–1) in K2. The formula in K2 locates
the first segment of the part number.the first segment of the part number.
4. Use =MID(A2,I2+1,J2–I2-1) in L2. The formula in L2 4. Use =MID(A2,I2+1,J2–I2-1) in L2. The formula in L2
locates the middle segment of the part number.locates the middle segment of the part number.

14. Find, Mid, Left and Right FunctionsFind, Mid, Left and Right Functions
Exercise 8:Exercise 8:Use the Find, Mid, Left and Right Functions Use the Find, Mid, Left and Right Functions
to get the results as in Exercise 7to get the results as in Exercise 7
5. Use the formula, =RIGHT(A2,LEN(A2)–J2) to get the 5. Use the formula, =RIGHT(A2,LEN(A2)–J2) to get the
last segment of the part numberlast segment of the part number

15. Find, Mid, Left and Right FunctionsFind, Mid, Left and Right Functions
Exercise 8:Exercise 8:The resulting data should be like thisThe resulting data should be like this

16. Often Used Advanced MS Excel Often Used Advanced MS Excel
Formula FunctionsFormula Functions

17. Often used advanced MS Excel FunctionsOften used advanced MS Excel Functions
IF Function – IF Function – Can be used for various purposes Can be used for various purposes
generally to achieve results which have conditionsgenerally to achieve results which have conditions
=IF (logical_test, value_if_true, value_if_false)
logical_test 
- a value or expression that is tested to see if it is true
or false.
value_if_true 
- the value that is displayed if logical_test is true. 
value_if_false 
- the value that is displayed if logical_test is false. 

18. Often used advanced MS Excel FunctionsOften used advanced MS Excel Functions
IF FunctionIF Function
Exercise 9: Exercise 9: Give grade as follows:Give grade as follows:
A 
If the student scores 85 or above
B 
If the student scores 70 to 84
C 
If the student scores 55 to 69
D 
If the student scores 40 to 54
FAIL 
If the student scores below 40

19. Often used advanced MS Excel FunctionsOften used advanced MS Excel Functions
IF FunctionIF Function
Exercise 9: Exercise 9: Use the following dataUse the following data

20. Often used advanced MS Excel FunctionsOften used advanced MS Excel Functions
IF FunctionIF Function
Exercise 9: Exercise 9: The result should be as follows:The result should be as follows:

21. Often used advanced MS Excel FunctionsOften used advanced MS Excel Functions
VLOOKUP Function – VLOOKUP Function – searches for value in the left-searches for value in the left-
most column of the table_array and returns the value in most column of the table_array and returns the value in
the same row based on the index_number.the same row based on the index_number.
=VLOOKUP(value, table_array, index_number,
not_exact_match*)
* Enter FALSE to not_exact_match field to find an exact match
while enter TRUE to find an approximate match

22. Often used advanced MS Excel FunctionsOften used advanced MS Excel Functions
IF FunctionIF Function
Exercise 10: Exercise 10: Based on the data in Table 1, determine Based on the data in Table 1, determine
the total amount of the items in Table 2 using the the total amount of the items in Table 2 using the
VLOOKUP functionVLOOKUP function

23. Often used advanced MS Excel FunctionsOften used advanced MS Excel Functions
IF FunctionIF Function
Exercise 10:Exercise 10:The result should look like thisThe result should look like this

24. Working With Pivot TablesWorking With Pivot Tables

25. Pivot TablePivot Table
A Pivot Table (under the Insert tab of the Ribbon Bar) is a
way to present information in a report format. The idea is
that you can click drop down lists and change the data
that is being displayed.

26. Pivot TablePivot Table
Exercise 11: Exercise 11: Using the Data in Table 2 in Exercise 10, Using the Data in Table 2 in Exercise 10,
Find the total sales of each product using the Pivot Table Find the total sales of each product using the Pivot Table
reportreport

27. Pivot TablePivot Table
1. Go to Insert tab and Choose Pivot Table tool. A dialog 1. Go to Insert tab and Choose Pivot Table tool. A dialog
box will appear.box will appear.

28. Pivot TablePivot Table
3. Select the icon with a red arrow then highlight the cells 3. Select the icon with a red arrow then highlight the cells
to be included in the report (Cell E3 to J18).to be included in the report (Cell E3 to J18).

29. Pivot TablePivot Table
4. Below the dialog box, choose where you want you 4. Below the dialog box, choose where you want you
save the Pivot Table report (either new worksheet or save the Pivot Table report (either new worksheet or
existing worksheet). Click OK.existing worksheet). Click OK.

30. Pivot TablePivot Table
5. In the field list. Drag the "Item" field 5. In the field list. Drag the "Item" field
button to the Row Labels Field and the button to the Row Labels Field and the
"Total Amount" field button to the Values"Total Amount" field button to the Values
Field. Field.

31. Pivot TablePivot Table
Exercise 11: Exercise 11: The report should look like thisThe report should look like this