EXCEL intermediate training with data
...
Welcome:
Welcome to MS Excel - Intermediate training.
Trainer to introduce self.
Icebreaker:
Ask the delegates to Introduce themselves & list out an adjective with the first letter of their names which best describes the participant:
The introduction needs to include
- Name
- An adjective which best describes the person & starts with the first letter of the name of the delegate
- Their current knowledge of Excel
- What they want to learn in the training today
Participants have to create a name chain which would involve remembering each of the previous participant’s names and their adjectives. (This would be used as a part of the Finding data exercise)
Course Objectives:
Apply basic shortcut keys to simplify work
List the three types of data referencing
Calculate data using logical, text and lookup functions
Create charts and graphs using the chart wizard
Apply conditional formatting and provide data validations
Apply filters to extract data from a list
Create pivot tables for data analysis
Agenda:
Quick Tips
Finding & Replacing Data
Basic Functions
Referencing
IF Functions
Text Functions
Charts & Graphs
Lookup Function - Vlookup
Conditional Formatting
Data Validation
Sorting and Filtering
Protection
Pivot Tables
Review
Connect:
Ask delegates to write on a post-it what they want to be able to do in Excel by the end of the course. Have them place this on a blank flipchart at the front.
Handout and explain Participants Guide
Trainer to Demo and take the teams through some quick tips on
Excel (Keyboard shortcut Keys):
Ask each of the participants to try each of the shortcut keys in their respective Excel Intermediate Workbooks.
1 Adding new worksheet <Shift> <F11>
2 Moving between Sheets <Ctrl> Page Down
<Ctrl> Page Up
3 Selecting all Cells <Ctrl> A
4 Selecting a row <Shift> <Space>
5 Selecting a Column <Ctrl> <Space>
6 Selecting a table <Ctrl> < * >
7 Hiding Row <Ctrl> < 9 >
8 Unhiding Row <Ctrl> <Shift> <9>
9 Hiding Column <Ctrl> < 0 >
10 Unhiding Column <Ctrl> <Shift> < 0 >
11 Function Wizard <Shift> <F3>
Trainer to inform the participants that once the participants are comfortable using these basic shortcut keys they can try using the other shortcut keys listed at the back of their participant guides.
Trainer to open Excel Intermediate attachment and refer to Sheet (Finding Data)
Trainer to demo finding one name using the shortcut key (Ctrl F).
Ask participants if they remember each of the participant’s names & adjective. The adjectives aren’t too important, however each participant have to find & highlight all the participants names them by bolding each name. (Pre work to be completed by the trainer as mentioned above)
...
Excel finds the first instance of the data you typed and makes the cell that contains it the active cell (Circled in red above). Click Find Next to search for the next instance, or Close to end.
If you look at the excel sheet, we have a few mistakes on the Department Field.
We have some departments reflecting “Training” and some reflect “Trainings”
To correct this mistake at one shot, one can use the Replace option available on the same Find option:
Open the Edit menu and choose Replace. The Find and
Replace dialog box opens with the Replace tab displayed. You can also use the shortcut key (Ctrl H).
In the Find what text box, type the data you would like to find.
Press the Tab key to move the cursor to the Replace with text box, and type the replacement data.
Click Replace All to replace all instances of the data you typed. (Or, click Find Next to find the first instance of the data, and click Replace to replace it.)
Excel notifies you of the number of replacements it made; click OK. When you’re done using the Find and Replace dialog box, click its Close button to close it.
Trainer to demo Basic functions:
Trainer to inform participants that all the formulas used in excel will begin with an “=” sign
1) Add/Sum: The sum of the data in 2 or more cells
=(A1+A2+…) OR =SUM(A1, A2,…)
2) Subtract: the difference between in the data in 2 or more cells
=(A2-A1-…)
3) Multipy: The product of the data in 2 or more cells
=(A1*A2*…)
4) Divide: The division of data in 2 or more cells
=(A1/A2/…)
Please note that brackets are not really necessary for the formulas listed above to work, but it is best practise to use the brackets
5) Average: The arithmetic mean of the data in 2 or more cells
=Average(A1,A2,…)
6) Power: The data in one cell (base) raised to the power of another cell(exponent)
=(A1^A2) or =Power(A1, 3) [A1: Base, 3: Exponent)
Trainer to open Excel Intermediate File (attachment) and refer to
Sheet (Basic Functions)
Trainer to ask participants to complete the exercise by themselves & assist them with any doubts.
(Solutions attached alongside)
AutoFill:
It is monotonous to type in the same data over and over. And it certainly is not interesting to number lines 1, 2, 3, ... or fill in months of the year or days of the week. Excel's AutoComplete and
AutoFill features are just the thing to handle these repetitious actions!
You use AutoFill to:
Copy data to other cells in the row or column (e.g. 2 below)
Continue a pattern for data (e.g. 1, 3 & 4 below)
To use AutoFill, you just select a cell or cells and drag the fill handle in the bottom right corner of the selection across the cells you want to fill. The fill handle is the small black square in the corner of a selection (as shown below).
If you drag on the fill handle, the pointer changes to a small black cross (as shown below). Excel will fill each cell that you drag across with either a copy of the original cell or with a continuation of the pattern in your selection.
Excel can also work with patterns of words. If your sheet is a budget or a meeting calendar, for example, you might list the months of the year. Once you have January typed in, AutoFill can complete the rest of the months for you.
AutoFill also works with days of the week, years, etc. If you establish a pattern of any kind, Excel can repeat it down the column or across a row.
Trainer to open Excel Intermediate File (attachment) and refer to Sheet (Basic Formulas Exercise)
Trainer to ask participants to complete the exercise by themselves & assist them with any doubts
(Solutions attached alongside)
There are three types of referencing in Excel:
Can anyone here tell me difference between the three?
Relative Referencing: This is the most widely used type of cell reference in formulas. Relative cell references are basic cell references that adjust and change when copied or when using AutoFill.
To simplify this…
Trainer to refer to sheet (Referencing) of the same Workbook
(Excel Intermediate)
In the example above, we have the BID and ASK price for Reuters, Infosys and Reliance. If we calculate the mid price, the formula would be =AVERAGE(BID:ASK)
When we reference the cells =AVERAGE(B5:C5)
Now place the cursor at the end of the MID PRICE column, and drag and drop the formula to other cells.
The new formula for INFY.BO mid price will reflect as =AVERAGE(B6:C6)
The cell reference changes based on the change in the cell selected. This is called Relative Referencing
Absolute Referencing: Situations arise in which the cell references must remain the same when copied or when using
AutoFill. The Dollar signs are used to hold a column and/or a row reference constant.
Lets take another example:
Trainer to refer to Sheet (Referencing) on Workbook (Excel Intermediate)
If I have to add the value in Cell B13 to each value in Cells A14 to A17, how do I go about?
Here relative referencing would not work, because, when you drag and drop the formula, you row reference also tends to change.
That is, 6+2 = 8 would reflect in Cell B14 and 8+3=11 is what would reflect in cell B15 Hence we need to keep cell B13 constant in this case
So the formula would be =($B$13*A14), on cell B14
If you now drag and drop the formula, the cell B13 would remain constant.
This is called absolute referencing
Exercise for delegates to complete:
Answers:
The formula in cell B24 would be =POWER($A24, B$23)
You now need to drag the formula across to cell D26
The above is called Mixed Referencing
Trainer to open Sheet (Referencing Exercises) in the Excel
Intermediate Workbook
Trainer to ask participants to complete the exercise by themselves & assist them with any doubts
Answers:
Units Used = Subtract the Previous Reading from the Present Reading
Units Charged = Unit Cost * Units Used
Amount Payable = Units Charge + Standing
Charge (solutions attached alongside)
Trainer to open sheet 4 (IF Function) on Excel Intermediate Workbook
Q1. Categorize the employees on the basis of their grade as “Yes” or “No”. All employees with grade “A” have to be categorized as “Yes” & all other employees have to be categorized as “NO”
ANSWER:
Formula: =IF(B4=”A”, ”YES”, “NO”)
Q2. Categorize the employees on the basis of their grade as “Yes” or “No”. All employees with grade “A” or “B” have to be categorized as “Yes” & all other employees have to be categorized as “NO”
ANSWER:
Formula: =IF(OR(B4=”A”,B4=”B”), ”YES”, “NO”)
Exercise for delegates to complete:
Q3. Calculate BONUS for all employees at 10% of their Salary (A and B grade can avail bonus)
ANSWER:
Formula: =IF(OR(B16=”A”, B16=”B”), C16*10%, 0)
Q4. Rate the Salesmen based on their sales and the rating scale
ANSWER:
Formula: =IF(B31<2500, $H$32, IF(B31>5000, $H$34, $H$33))
Trainer to open sheet (IF Function – last ex.) on Excel Intermediate
This function counts the number of items which match criteria set by the user.
Q1. Find the number of times "3" is repeated in the Table
ANSWER:
Formula: =COUNTIF($A$41:$J$50, 3)
Q2. Find the number of values greater than 5
Trainer to ask participants if they can find the Text Functions sheet by using the appropriate Keyboard shortcut keys.
(The shortcut key used to scroll between sheets is “<Ctrl> Page
Down” OR “<Ctrl> Page Up”)
Trainer to open sheet (Text Functions) on Excel Intermediate
Workbook
This sheet contains a list, of the names of managers all pasted together in one cell. Sometimes when data is copied from other sources like PDF files or some of our products & pasted into excel we notice that all of the data is pasted in one cell.
If you look closely you’ll see that each name (both first & last name) is separated from the other by a semicolon. The first exercise focuses on separating the manager’s names from each other & placing them in different columns. This can be achieved by using the Text to Columns Wizard.
The Convert Text to Columns Wizard is an easy way to separate simple cell content, such as first names and last names, into different columns.
Depending on your data, you can split the cell content based on a delimiter, such as a space or comma, or based on a specific column break location within your data.
Now that the Manager’s names are separated the next step would be to rearrange the names from a horizontal position to a vertical position so that we have each manager’s name in separate rows.
In order to rearrange the direction of the text we use the Transpose function.
This function copies data from a range, and places in it in a new range, turning it so that the data originally in columns is now in rows, and the data originally in rows is in columns.
Now that the managers names are rearranged in rows can all of you insert serial numbers before each name using AutoFill?
We notice that the cells that contain the names of these managers have spaces before & after the text. We cannot use the Find & replace shortcut since this would also eliminate the space between the first & last names. Thus in order to eliminate the spaces before & after the text but not between the text, we use the Trim Function.
(Note: once applied to a cell use AutoFill to apply to the other cells)
If we click on the cells with the Trimmed text we notice that the text is in Trim formula, which means that we delete the old column with the spaces in the text, the New Trimmed row is lost as well.
Therefore we copy all the text in the Trimmed column, copy the text, & use paste special (shortcut key – Alt + E + S). Check the ‘Values’ checkbox from the “paste special” popup window. The Trimmed names are now converted into values.
The next step is to separate the Manager’s first names & last names & place them in separate columns. Can any of you recall how this could be done?
You can use Text to columns to separate out the first & last names using ‘space’ as the delimiter.
Now that we’ve separated the first & last names, you may come across a situation where you would have to combine the first & last names. In order to combine the two, we can use the
Concatenate function.
This function joins separate pieces of text into one item.
Formula: =CONCATENATE(B1, “ “, C1) (Note: once applied to a cell use AutoFill to apply to the other cells)
To change the case of the text to upper, sentence & lower case without manually typing it out, we can use the Upper, Proper & the
Lower formulas
This function converts all characters in a piece of text to upper case.
Formula: =UPPER(B2)
This function converts the first letter of each word to uppercase, and all subsequent letters are converted to lower case.
Formula: =PROPER(B2)
This function converts all characters in a piece of text to lower case
Formula: =LOWER(B2)
Trainer to open Excel Intermediate File (attachment) and refer to Sheet (Text Exercise)
Trainer to ask the participants to repeat all the steps demonstrated in the module and complete the exercise by themselves. Trainer to assist the participants with doubts, if any