Check all computers are working, on, have the application and launch okay.
Check all toolbars are the same.
Check all exercises (if required) are loaded on the machines.
Check you have all (if any) photocopied handouts ready.
Write one's name, date and course title on the board (If supplied).
Once the candidates have arrived.
Introduce yourself, the course and explain the fire exits, toilets, coffee and ask them to sign in (if relevant).
Tell them a bit about yourself, your background and knowledge.
Explain the timing of breaks, (1100am ish / 1500pm ish) lunch (1300pm) and estimated time of course completion. (1600-30pm)
Ask the candidates to introduce themselves, assess their level of knowledge and ask them what they hope to achieve from the day.
Explain that what you will be showing them is My preferred' way and therefore not definitive; there are usually other ways of achieving similar if not the same results. If they know of a different way of doing something, great.
After the course
Hand out Course Evaluation Forms and get candidates to fill them in.
Clear the relevant/amended files you have used from each PC.
Check all PC's are shut down properly and switched off.
Make sure the room is left clean and tidy - as you would like to find it next morning.
Enter Course Evals onto the website.
Excel Advanced Course Structure:
To be written onto the board
Analyzing and Displaying Data
1. Managing Lists
Sorting
Subtotals
AutoFilter
2. Advanced Filtering
Database functions
3. Pivot Tables
4. Goal Seek
5. Scenarios and Views.
1: MANAGING LISTS
EXERCISE FILE: SHIRTS UNSORTED
File data opens up mixed around.
Some Basic Do's and Don'ts for spreadsheet set-up
Label Column headers sensibly.
Avoid words like SUM, MIN MAX AVERAGE (i.e. function names)
Avoid punctuation.
Plain word headers are best
*Familiar with range naming? Don't use range names as headers can confuse Excel.
Don't leave blank rows at the top of data
( Select the data (CTRL*)). Deselect it and highlight row 2 insert an extra row, then try re highlighting the text see how it misses the column headers. Take the row out CTRL-.
Sorting Data
Key thing to remember when data sorting do not highlight the column you are working in, as this will sort ONLY that column.
Only need to click into the column you'll be working in, so click once in the SUPPLIER column. Use the A Z sort button to sort all the data in that sheet by Suppliers.
To unsort the data, use the undo button. However this could get quite irritating. Easiest way to workaround this is to insert an extra column next to A column and call it ORIGINAL ORDER.
In A2, enter number 1,
A3 , number 2 and fill this down.
Sort by Supplier again. Then sort back using the ORIGINAL ORDER column of data.
If necessary, you can HIDE the column of data. So click in Column A and do CTRL 0 (on the numbers at the top of the keyboard.)
Delete this column.
More Advanced Filtering. (Multiple Level Sort)
Initially, sort by SUPPLIER again. Then go to:
DATA SORT . A box appears. Through this box, you can sort your data by up to 3 criteria ONLY.
Sort by SUPPLIER FABRIC PATTERN .
Subtotals
Go to
DATA SUBTOTALS . Box appears.
Excel takes a guess at the sort of sub-totaling you're after. In our case this is correct, so choose OK and see how subtotals have appeared in your data, along with outlining options around the top and left of your data. 123 numbers and + or symbols that will show or hide different levels of data.
Choose another sub-totaling criteria. ( DATA SUBTOTALS) and choose average supplier shirt cost (Supplier Average PRICE ) . Remove the tick from TOTAL.
Takes away previous Subtotal info. To show various subtotals at any one given time -
Data Subtotals Take the tick out of Replace Current Subtotals'. Both options will appear in a list format and will continue down adding more info as you select it.
To remove all subtotalling options:
DATA SUBTOTALS REMOVE ALL . This sets the data back to raw data.
AUTOFILTER
DATA FILTER AUTOFILTER.
Not the grey arrows in boxes that appear.
Click on Supplier and choose HOF . When row numbers appear in blue, this indicates that there are some hidden rows of data. The arrow attached to that relevant data also highlights blue, indicating it is that particular data being sorted.
To unhide/show everything, click on any of the boxes with arrows and choose "ALL".
Click on the Price filter option all prices appear not tremendously useful but try " TOP 10" shows 10 most expensive. Once you display the data, you can then click into the column and sort via the A Z buttons to sort them into order.
This is not done automatically.
Unfilter this data.
To see for example 2 suppliers at once, Click on the arrow attached to the SUPPLIER column and choose: CUSTOM FILTER. In the options that appear, choose HOF OR FOX.
Don't use AND why? Would only show results that have HOF and FOX in the column none would appear.
Show all again.
Filter by SUPPLIER, custom, choose does not equal' WINDSOR .
Show all again.
Filter by PRICE this time and CUSTOM: Is greater than or equal to 25 to see all shirts costing 25 or more.
Show all again.
To remove AUTOFILTERING:
DATA FILTER AUTOFILTER and remove the tick.
Close the file not saving changes
*EXERCISE: Open file: Projects'
1. Apply an AUTOFILTER.
2. Find all profits over 2k.
3. Show all data.
4. Find the average of all profits over 2k.
5. In cell E17 type Average', in F17, type =AVERAGE(F2:F14) and hit enter.
6. Reapply another custom filter for totals over 2k.
This reveals a flaw. The AVERAGE doesn't update.
ADVANCED FILTERING/DATABASE FUNCTIONS
Take off the AutoFilter.
Advanced Filters
For advanced filters to work successfully, you need an area of DATA and an area of CRITERIA:
Figure 1:
DATA and CRITERIA layout.
DRAW ON BOARD
For this advanced filtering, the criteria table must echo the headings of the block of data.
First course of action is to move the data block down a couple of rows and insert this criteria data.
Highlight row 1 to 5 and use CTRL+ to insert extra rows.
Highlight A6 to F6 copy it and paste them into A1.
Highlight the data. CTRL* it to select it. In the CELL NAME box, type in the word DATABASE & hit Enter.
Click away to deselect this data and check the name has saved okay.
Highlight A1 to F2 and call this area CRITERIA.
Change the ZOOM view to 39% and you should see if these names appear in that view. Criteria will be difficult to see, but DATABASE should be clearly visible.
Reset the view to 100%.
Click back in F2, type in " > XP" and hit enter.
Go to DATA FILTER ADVANCED FILTER .
This tests the data and the criteria range. OK this, should filter the information successfully, i.e. show all criteria that are over XP.
InF2, replace > XP with > 5000.
Go back to DATA FILTER ADVANCED and choose OK again, reshows the reset criteria.
Go to DATA FILTER SHOW ALL .
You can set Excel up so that it EXTRACTS the records with profits over 5k. They can then be pasted into another location so that you have a separate block with that specific data.
Go to DATA Filter ADVANCED . Check the area "Copy To Another Location" Choose an area lower in the sheet. You can't do it in another sheet or file.
DELETE these cells and any residual formatting left over.
We can now start using DATABASE FUNCTIONS.
So, click in E24. Type in " DATABASE AVERAGE".
In F24 type =DAVERAGE( CTRL+SHIFT+A, replace the first part with " DATABASE", table area, 2 nd part with " 6" and 3 rd part with " CRITERIA"
=DAVERAGE(DATABASE,6,CRITERIA) ( Should return 8,100).
Change F2 to > 4000
Price quoted > 1000 affects subtotals. So, under Database Average:
You can also use the other DATABASE functions on this data. DMIN/DMAX/DSUM, so in a neat column under what has already gone, use each of these functions.
PIVOT TABLES
Close this file and open up "BANKING UK ".
Make sure you have clicked once into the spreadsheet. not double clicked in a cell to select it just somewhere in the data.
Go to DATA PIVOT TABLE REPORT .
The 1 st step of the PIVOT Table wizard appears.
Figure 2:
PIVOT TABLE WIZARD.
Step 1 of 4 Where is the data?
Usually chooses the correct option. Hit NEXT.
Step 2:
Figure 3:
PIVOT TABLE WIZARD.
Step 2 of 4 Where is the data 2?
Guesses the Data range you are after as long as you had been clicked in the data, this should select the correct area.
Hit Next.
Step 3:
Figure 4:
PIVOT TABLE WIZARD.
Step 3 of 4 Pivot table design.
Here you choose the layout of your data and how it will be displayed. The headings you have in the right hand section are reflective of the column headers.
So, drag "Acct Type" into the DATA field.
Hit Next, Step 4:
Figure 5:
PIVOT TABLE WIZARD.
Step 4 of 4 Location of pivot table.
This is where your table will be created. Make sure it's New Worksheet'.
Baby pivot table appears. This table is the chosen data at it's most basic. It shows a SUM of all the different account types.
You should also get a small floating Pivot Table toolbar appear. If not, go to VIEW TOOLBARS PIVOT TABLE .
On the floating toolbar, use the top button that looks like the columns and rows detail being swapped. (Pivot Table Wizard). This re-launches the last steps of the chart wizard. Here you can change the Pivot Table functions.
Take "Account Type" from the right again and drop it into "ROW" too. Take " BRANCH" and put it into "COLUMN HEADING".
This breaks the data down as much as you need. Look at the original data sheet to see how efficient the pivot table is
Choose the chart wizard button again.
Move " BRANCH" from COLUMN to ROW but make sure it's ABOVE "ACCT TYPE".
See how this reflects the data in a slightly different way.
Chart Wizard Button: Remove BRANCH/ACCT TYPE & Finish. Click in B4 no formula in here.
Go back to the Original datasheet and Highlight Rows 2,3,4. Delete them. Check the pivot table. Stays the same does not live' update as you work you must refresh it manually.
This button will refresh your data.
Click on the Chart Wizard Button again. Place "BRANCH" in Row and "SUM OF AMOUNT" in DATA.
To find the Average of this data, instead of the SUM, double click on the SUM OF AMOUNT grey bar itself and a list of functions will appear. Choose AVERAGE from this list. When you do FINISH, it will show the average of each branch's account detail.
Go back into the Chart Wizard.
Place "ACCT TYPE" into the COLUMN heading, their breaks down each branch's data into an average for each of the account types.
Pivot table is a very useful and powerful tool. Close and don't save the changes.
SCENARIOS AND VIEWS.
Open the sheet "NAMES AND SALARIES"
Click in F2: have a look at the formula there.
=E2*$B$13
Change B13 to 20% and this will update all the calculations for you. Reset the pension to 10%
SCENARIOS
Can allow you to work out best or worst case scenarios using your data.
Set PENSION to 20%
TAX to 0%
NI to 0%
See how the results are reflected in the data table.
This would be a fantastic situation, but to then set a worst case scenario would require manually overtyping this data again or does it?
Undo to 10% / 24% / 8%
Highlight B13 to B15
Go to TOOLS SCENARIO ADD
Figure 6:
SCENARIO Set up.
Naming the scenarios.
First scenario we'll create is HEAVEN' So type this into scenario NAME. When you click OK you get this window:
Figure 7:
SCENARIOS.
Cells to be changed.
Enter " 20 0 0". Click OK you will come back to the scenario manager.
Add another one call it HELL and make the numbers " 0 50 20"
OK and ADD another, this one is PURGATORY and consists of the numbers " 10 24 8".
ADD these and remain in the SCENARIO MANAGER. To affect these changes on the spreadsheet itself, either highlight one of the scenarios and choose SHOW, OR sharp double click on a scenario. It will display.
To change between these scenarios, you need to go to TOOLS SCENARIOS and click between the 3 options. You can store as many scenarios as necessary.
HOWEVER , You can set up a customized toolbar option to allow you to access different scenarios very simply, but it does take longer to set up.
Either go to VIEW TOOLBARS CUSTOMISE or do a single RIGHT MOUSE CLICK on the grey toolbars at the top of the screen. In the drop down menu that appears, choose CUSTOMISE.
The customise toolbars box appears. Choose the COMMANDS tab at the top.
Find the TOOLS MENU category and scroll down the commands until you find scenarios. You can then click on it and drag it onto your toolbar. (Next to the Office Assistant button will probably be best.)
Close down the CUSTOMISE section and you can start using the scenarios.
*Don't forget to drag these extra bars/drop downs off when finished.
CUSTOM VIEWS/VIEW MANAGEMENT
GOAL SEEK.
With this, you can tell Excel the answer or outcome you want and it will work out the raw data you require.
Open a blank sheet.
In A1 type SALES
A2 COSTS
A3 PROFITS
B1 100
B2 60
B3 =B1-B2
Then go to TOOLS GOAL SEEK . You then have to choose the cell to set.
Set Cell B3
To VALUE 100
By Changing Cell B2
You can then re-enter this area and reset the goal if you need to and Excel will change the quantitative data as necessary.
If you then wanted to, you could turn any of this data into an instant chart hit F11.
Microsoft Excel XP ADVANCED
PART ii
EXCEL DATABASE / INTRO TO MACROS
1. EXCEL DATABASE:
CREATE FILE
SHARING FILES
DATA VALIDATION RULES
PROTECTING FILES
2. INTRO TO MACROS
Recording Macros
Different Ways To Run
Basic VBA Code Editing
In a New Sheet:
A1 NAME (Fill down to A11 10 names)
B1 SALARY - 12k, 24k (Fill down)
> > SAVE THE FILE < < - call it your 1 st name.
C1 GROSS MONTHLY PAY C2 =B2/12
D1 PENSION D2 =C2*10%
E1 TAXABLE E2 =C2-D2
F1 TAX F2 =E2*24%
G1 NI G2 =E2*8%
H1 NET PAY H2 =E2-F2-G2
Save THE FILE .
In A13 the word " SUM" in B13 do an AutoSum ( ALT=)
* Highlight B2 B11. Have a look at the status bar at the bottom see a summary of data selected. Right click on this summary and you can choose which function to preview before you set it up.
A14 AVERAGE B14 =AVERAGE(B2:B11)
A15 MIN B15 =MIN(B2:B11)
Save this as Your name.
Highlight Column C (Whole thing) and insert an extra column. CTRL+.
In C1 Call it APPRAISAL. Start in the top row and put A, underneath, B, then carry on down to E.
Fill this series down.
Highlight Column D and insert another blank column. Header this new one APPRAISAL DESCRIPTION'.
Come down to A19 and Enter " A", in A20 " B" etc to " E".
In B19, next to A20 enter " BRILL", " GOOD", " SO-SO", " BIT SIMPLE", " COMPLETE MUPPET".
Highlight the small table and give it a name in the cell name window. Call it " GRADE" and hit enter.
In D2 now, type (use SHIFT CTRL + A) =VLOOKUP(C2,GRADE,2)
Copy this down and change one of the people's grades. Change it to Z. See how Excel cannot recognize the figure.
Change the grade back to whatever it was before. Save your file.
FILE SHARING
Go to TOOLS SHARE WORKBOOK .
Figure 8:
SHARE WORKBOOK.
Choose if you want your workbook to be shared.
In here you can choose to share your workbook.
Put a tick in the "Allow changes by more than one user at the same time" box. This also allows workbook merging." By doing this, it prompts you to save the file add a " 1" after the filename and choose OK. When the file is saved, you will see the word [Shared] on the Application bar at the top.
Whoever then has opened and saved that workbook becomes the ADMINISTRATOR. Whoever accesses and edits the workbook will appear in this list.
If you open TOOLS SHARE WORKBOOK later on, when people are working in the book, this is when you can see who's editing it. If you needed to, you can opt to remove' users from the list. If you omit them here, they will get no error messages but they will simply be told they will not be able to save their changes.
Come back to the spreadsheet. Change the 1 st person's salary from 12k to 50k. Also change their grade to " E"
Go back to TOOLS TRACK CHANGES HIGHLIGHT CHANGES
Tick WHEN and WHO. OK this.
A small triangle should be present over the cell B2. When you then hover the mouse over it, a box appears telling you the changes made in that cell.
Go to TOOLS TRACK CHANGES ACCEPT/REJECT CHANGES .
You can choose if you wish to accept or reject the changes made since you last opened the workbook.
Accept the Pay Rise Reject the grade change.
Unshare the workbook TOOLS SHARE WORKBOOK take the tick back out of the box " Allow changes by more than one user at the same time. This also allows workbook merging." This will ask if you're sure. OK this and exclusive ownership of the workbook will be retained.
DATA VALIDATION
Highlight B2:B11.
Go to DATA VALIDATION
Set ANY VALUE' to WHOLE NUMBER'.
Make sure data is " BETWEEN", then make the MINIMUM figure " 1" and set the MAXIMUM to " 100,000". OK this.
Try changing any of the salaries to 5k or 120k. When you enter, you get the error message.
Re-highlight the cells and go back to
DATA VALIDATION . Choose the second tab at the top INPUT MESSAGE TAB. Make the title " ATTENTION". In the Input message section enter " Salaries between 1 and 100,000 only please." Choose OK and try clicking in any of the figures in that column.
Re-select the cells. Go back to DATA VALIDATION . Delete the INPUT MESSAGE and click on the ERROR ALERT TAB.
Don't use this : The CLEAR ALL option on the bottom left will clear all criteria.
In ERROR REPORT, choose the STOP option. (This option will physically not let you input the wrong data into the cell, whereas WARNING and INFORMATION are advisory only.)
Title it " ERROR" and set the " INPUT MESSAGE" as " Choose only salaries between 1 and 100,000, you crazy fool".
OK this and check it has worked in the sheet. When done, Re highlight the cells and go to DATA VALIDATION and simply remove the tick from the ERROR ALERT tab.
Come back to the sheet and make the bottom 3 over 100,000.
Go to TOOLS AUDITING SHOW AUDITING TOOLBAR . Hover the mouse over the second button from the right CIRCLE INVALID DATA. Click on this and it will draw circles round any data that falls outside of your chosen criteria. To remove these circles, use the last button on the floating toolbar CLEAR VALIDATION CIRCLES.
To set up validation so that you can pick from a list of options is straightforward too.
Highlight C2 C11 . Go to:
DATA VALIDATION SETTINGS TAB .
Change the Any Value to LIST. Click in the SOURCE box and highlight A19 A23 . Choose OK. Come back to the sheet and you will see that when you click onto the cells in that column that arrows will appear and you can click the drop down and choose from a list of options.
Protecting Files
To protect the entire sheet.
TOOLS PROTECTION PROTECT SHEET.
Make sure the ticks are all in the boxes . Just choose OK here don't set a password. Note though that you can still change the data in the validation cells. This could be a good thing or a bad thing depending on your POV.
Unlock the sheet again. You can choose various cells to be changeable when the sheet is locked.
Highlight B2 B11 . Go to FORMAT CELLS PROTECTION . Take the tick out of the locked box and close this window (OK).
Re-protect the sheet and see how the cells you unlocked are now editable.
Go to FILE SAVE AS and click on the box OPTIONS.
Figure 9:
SAVE OPTIONS.
Set a password to open/edit workbook.
See here that you have the option to protect your sheet on a further 2 levels someone could be able to open it to look at it without being prompted to enter a password, but if they try and edit it, they will be asked for a password.
MACROS
Macros are labour saving devices' that record your movements around Excel. You can then assign those movements to a button or keyboard shortcut for use on another section at a later date.
Also bear in mind that when you run a macro, its effects cannot be undone.
Open a new spreadsheet. We will start small!
In A1, type YOUR NAME. Drag it across till it is 10 columns across ( J). Then keep them all highlighted and drag them down to 20.
The macro we will create will make text in a cell turn green and grow to size 14.
So first of all, go to TOOLS MACRO RECORD NEW MACRO .
Give it a name " HEADINGS". Bear in mind macro names, like cell and range names cannot have spaces. Use the underscore if you need it.
You then get some options for where you store the macro. MAKE SURE it goes into PERSONAL and that it goes in THIS WORKBOOK.
You then get a description of who set it up and date / time etc.
OK this then DO NOT DO ANYTHING. As soon as you have pressed OK, you are RECORDING. Therefore whatever you do now with the mouse, you will add into the macro.
Make doubly careful you do not make any mistakes from now until we stop the recording.
Using the format toolbar, change the size of the text to 14 and make it DARK GREEN.
Then on the tiny floating toolbar that has appeared, hit STOP. (The blue square).
Check the macro has recorded successfully by clicking in a cell that hasn't been formatted yet and go to TOOLS MACRO MACROS.
Check you're looking into YOUR WORKBOOK and see the macro name there. Select it and choose RUN. This should change the contents of the cell you clicked in.
Try the CTRL+Z to UNDO won't let you.
Select al the data and go to EDIT CLEAR FORMATS .
Open the SALARIES file.
Select the data block. Go to FILE PRINT SELECTION radio button. DO NOT OK THIS.
If you wanted to automate this process, a macro can do it.
Close this window and highlight from A1 B11 .
Go TOOLS MACRO RECORD NEW MACRO . Call it PRINT SELECTION. Store it in this workbook. OK this and you are now recording.
Go to FILE PRINT put a dot in " Selection" and OK that. Hit the STOP button on the macro toolbar.
Close it and come back to your PRACTISE file.
Now we could go to TOOLS MACRO and RUN the macro again, but this gets tedious after a while. Lets find some better ways of running the macros.
TOOLS MACRO MACROS . (The keyboard shortcut to get to this window is ALT F8).
Select (highlight) HEADINGS and click OPTIONS. Assign it to CTRL+H. If you need to, you can hold the shift key as you type H and it will only then work on SHIFT CTRL+H. This could be useful if the key you are assigning it to already has something assigned to it, e.g. CTRL+C/CTRL+V.
If you do choose a key that has already got a function assigned to it, the MACRO will overwrite it. Close this box and click in an unformatted cell. Try CTRL+H. This should work.
You can also assign macros to custom buttons. Right click on the grey toolbars at the top of the screen. From the drop down list choose CUSTOMISE.
On the COMMANDS tab, scroll down the categories until you find macros. On the right, choose the smiley face icon and drag it onto the toolbar at the top, next to the HELP/OFFICE ASSISTANT button. Right click on that new button and choose ASSIGN MACRO at the bottom. Choose HEADING from the list. OK this.
Close the customise area. Try the button check it has worked OK. That's all very nice you might not want a smiley face as your button though.
Right click on the smiley face and choose CUSTOMISE. Do another right click on it and in the NAME section type in HEADINGS. This will then appear when you hover your mouse over the button.
Then choose CHANGE BUTTON IMAGE and from this list choose the little piggy. Do another right click, choose EDIT BUTTON IMAGE. In this area you get a grid and you can change the button in any way you want. Colour the little piggy in pink.
Choose OK. Just for a sample, right click on any other button and see how they appear. All buttons are simply pixel images.
Close this and shut the customise window.
Hover the mouse over the button to see the tool tips appear.
Try the button out.
Back in the worksheet, go to TOOLS MACRO RECORD NEW MACRO . Call it SHADING and check it's saving into the same workbook. Assign it to SHIFT+CTRL+S and OK.
Change the shading of the cell you're in by using the paintpot button. Stop recording.
Click into another cell and do SHIFT+CTRL+S to check it worked OK.
Go back to TOOLS MACRO MACROS . Select SHADING and choose the option STEP INTO
This will open up the Visual Basic Editor as a separate window.
Macros are basically a list of instructions in text form that program Excel to tell it what that macro does. So, in this view, we can edit a macro.
Things to note:
We can see all macros we have set up here. The line divides them.
KEYWORDS appear in BLUE. Sub(Name)
COMMENTS appear in green.
Main text in black, is the basic setup of the macro, you can see in there Font = size 14
Delete the 14 and put 72.
As long as the cursor is somewhere between the top and bottom sections of the macro, (SUB / END SUB), if you hit F5 on the keyboard, it will run the macro for you.
Change the word FALSE next to STRIKETHROUGH to TRUE. It turns black, but to accept the changes, you must hit the down arrow. Turns it blue. Press F5 to run the macro and check it worked in Excel.
Change it back to false, down arrow, then change the colour index to 3 (Red). Do F5 again.
Back in Excel now, TOOLS MACRO NEW MACRO Call it "Best_Fit". Put it in this workbook again.
Assign it to CTRL+B. Choose OK.
Then go to FORMAT COLUMN AUTOFIT SELECTION . Stop recording.
Click in an unformatted cell and try CTRL+B. Also run SHIFT+CTRL+S as well as CTRL+H.
Return to the VB editor.
Do a CTRL+HOME to get to the top, hit ENTER 3 times and do CTRL+Home again.
Type:
Sub ALL (Enter)
Call Headings (Enter)
Call Shading (Enter)
Call Best_Fit (Enter)
Then try F5 to see if it worked successfully. Go back to Excel and click in an unformatted cell/column.
TOOLS MACRO MACROS . See ALL should be there. DBLE CLICK it or select it and choose RUN.
Should then run all the previously set up macros as one.
Close VB Editor
> > Remove extra buttons from Excel and Close it. < <
Disclaimer: All information on this site is provided for informational purposes only! By no means is any
information presented herein intended to substitute for the advice provided to you by any health care or other professional
or organization.
tweet this!
