| 1. Basic Themes in Excel 2. Formulae 3. Formatting |
It's important to note that Excel takes advantage of contiguous data ranges. You shouldn't have to select any ranges in order to sort, filter, chart, analyse etc. If you have any gaps in your data - entire blank columns or rows - get rid of them. Keep your data together.
For beginners the idea of not selecting anything is quite counter-intuitive, but something they get their heads around very quickly. For intermediate and advanced users, whose habits are more entrenched, it can be harder. I scare them by placing a 5,000-row spreadsheet in front of them and challenging them to sort it in no more than two seconds. ;-)
Start with a simple range of data. Remind the students not to select any data first but merely to place their active cell somewhere in the data.
Work through the Chart Wizard, drawing attention to key parts such as clicking and holding in Step 1 to view a sample and the sundry tabs in Step 3. Place the chart as an object in the same sheet. Ask them to spot anything new on the screen (which will namely be the Chart toolbar and the Chart drop-down menu). Guide through the different formatting and display options. (For more confident students, ask them if they can pull out one individual slice of a pie chart, or rotate it round so a different slice is at the front.)
Type extra data on the worksheet and add this to the chart.
Create another chart by F11, and demonstrate that the four parts of the Chart Wizard are available from the Chart drop-down menu.
If you have time, demonstrate how you can create a chart from non-contiguous ranges, or the pitfalls of having a data series which is dates and how to get around that.
If you need, show how to 'layer' two charts over the top of each other - such as dual axes for different data, or having a line chart for one dataset and a bar/column for another dataset. Fun can also be had by using the stack and stretch options in Format Data Series -> Fill Effects -> Picture. Remember, though, that your imported picture needs to be tiny.
Sometimes, complex chart types are needed, in which case I can't suggest more than going to PeltierTech's magnificent website.
N.B.: Pick a data range with dates, text and numeric values, so students can see the different ways sorting and filtering occur.
Start sorting with the A->Z and Z->A buttons, then move onto multicolumn sorting. For a bright group try and get them to find out how to sort by column instead of by row. Introduce them to the concept of a header row: it's very important with stuff like Pivot tables.
I ask them, if I have a column that contains numbers as well as text, whether the text would end up at the top, or the numbers. After some debate, you can reveal that numbers sort before text, i.e. 0-9-A-Z. Then you can ask how symbols would sort as well! Mostly, symbols sort before numbers. I've never been bored enough to create an entire column of symbols and sort them to find out their order, though ;-)
Because numbers are considered before text (or rather, just as 2 is greater than 1, A is greater than 9), it explains why conditional formatting can sometimes go wonky.
With filtering, use AutoFilters first of all. Ask them to describe to you what they see at each step - for instance, filter on one column and the drop-down will be blue, as will the row numbers, so you can see a filter has been applied. Very observant students may even notice the message in the Status Bar. (I confess that I never saw it until a student pointed it out!)
Move onto Custom filtering, asking students to find all items starting with the letter A, all between a given date range, and all over a certain numeric value. Ask them if there is a restriction on the number of columns that can be filtered - of course the limit, when sorting, is three, but with filtering it's only limited by the number of columns in your data range.
Ask students to investigate whether copying a filtered range and pasting it in a new location will bring all the automatically-hidden rows as well, or leave them out (q.v. Paste Special).
With Advanced Filters, there are several steps to getting it right.
Copy to another location - as above, but at step 6, click in the Copy to: panel and click in the top left-hand cell where you want your data to start.
Filtered data can be copied to another worksheet. Say your data is on Sheet1 and you want your filtered data to copy onto Sheet2. Start by clicking somewhere on Sheet2. Go to Data -> Advanced Filter and follow the steps above, moving to Sheet1 to find your List range and Criteria range. At Step 6, click in the Copy to: panel and click back into Sheet2. You'll now be able to paste your data there.
Unique records only - the steps are similar to the above.
Being able to pull out unique records only, in just a few clicks, really does impress people. ;-)
Again, no prior selection should be needed if your data is contiguous.
Bear in mind this fails to work if your data isn't sorted. It works by looking down a given column and subtotalling each time it spots a change in the data, so if it's unsorted it will spot changes all over the place and give you inaccurate subtotals.
You will need to stress the importance of working through the whole dialog box before clicking OK. Choose an exercise which allows students to think about the purpose of the function in the second section of the dialog ('Use function') such as Count rather than Sum.
Once the subtotals have been presented, show how the data has been grouped. This is also a great opportunity to demonstrate the use of Visible Cells Only (see Paste Special), so you can either copy & paste or format subtotal rows only.
Add a further level of subtotalling, asking learners how we can do this without removing previous subtotals. As an extension, you can explain the syntax of the function that's been inserted in each row of subtotal. There's a code in it which corresponds with the action of your subtotal - average, min, sum, what-have-you - which you can discuss if two types of subtotal have been used (one level of average, one of count, for instance).
Finally, guide the students to find out how they can remove all subtotals.
For best results, teach this before Hide columns/rows, for reasons which will become apparent.
Ask them, first of all, how we can get the top row (where you have a header row) to stay in place no matter how far down we scroll. Some will remember it having used it in the past, but unless they have actually done it themselves, they can find it quite hard to appreciate the need to be in the row below or the column to the right of before freezing.
Splitting is obviously very similar but allows you to see two very separate parts of the worksheet in question. If you apply it after freezing panes, it replaces the freeze lines. I tend to show how you can introduce and remove them manually rather than using the Window drop-down. Trick: if you are stuck with a protected workbook and need to be able to see two separate places at once, then chances are Hide Columns will not be available, which is where Split comes into its own.
To view two different sheets in the same workbook at the same time, go to Window -> New. Note your file name in the Title Bar is suffixed with :1 or :2, and you have two instances of the file in your taskbar. You can now arrange them on your screen however you prefer.
The best way to demonstrate this is with a spreadsheet with confidential data on it - such as one listing staff members' salaries - as well as standard info like Department and Extension number. Tell them that the IT department has asked for a list of everyone's phone numbers, but would you be happy emailing this over? (No!) Ask students how they can prevent them from seeing it.
Some students suggest (very inventively) formatting the text so that it's white - but of course this information can still be read. (A good follow-on question is: Where?). Sometimes they suggest cutting it and pasting it in another column further to the right. Fine, until you are a master of keyboard shortcuts. You might come across other equally interesting suggestions. If they haven't arrived at the knowledge already, you can tell them about hiding entire columns.
If you are teaching this after Freeze Panes you may find one or two of them use the Hide tool from the Window drop-down. This is a great place to show them what happens when they accidentally choose this option - if they do it back in the workplace or at home, they have the tools to rectify it. The best thing is to show it via right-clicking, as it's a faff via the drop-downs.
Run through hiding a column in the middle of the data. Run through unhiding (quite tricky for many). Then ask them to hide Column A and unhide. They'll have varying degrees of success depending on which version they're using and whereabouts they right-click. This is when you can show how auto-fitting by double-clicking will automatically resize all columns - including your hidden ones.
(Back to your scenario. So you've hidden the column. You're sending this to the IT department, though, so what's to stop them from autofitting straight away and discovering all of this info? Oh yes, we can password protect, but they're in IT and they can probably hack them (I certainly can). Eventually someone might suggest printing the data or doing a Save As and deleting the confidential data from the 'open' version - both of these are, in my opinion, the only ways to keep confidential information secure. Leave it in situ and you risk it being discovered.)
You can show the 'hide sheet' feature at this point, too, depending on how advanced your group is. I've seen it go down better with more competent users.
Surprising how few students know about this feature. Teaching all aspects of it takes around 10 minutes, and for extra fun, show how you can change the colour of the comment itself!
Do all of these via the right-click menu for ease of purpose. It's all in the same place, then. Ask them if they can establish how long a sheet name can be (there is a limit) as well as find out how to move or copy a sheet to a completely separate workbook. Note: You may find with some learners it's easier to use Insert -> Worksheet for a new worksheet as the right-click -> Insert will bring up a dialog box where you have to choose from a range of templates - one of which is a Macro sheet which could be very dodgy for inexperienced users.
Asking students what happens when you delete a sheet can be fun. If there is data on the sheet of course they will be prompted to double-check the action. Ask them to undo and bring the deleted sheet back - which can lead nicely into contingency plans in case you delete the wrong one! Ask them to try and delete all of the sheets.
Introducing the idea of creating a 'template' sheet here can be very useful; say you wish to create a worksheet with 13 sheets for each month of the year and a summary sheet (particularly if you're using 3D linking). Create the one and copy it over and over. If you have used tab colouring, this will copy too.
This tool is designed to remove guess-work. If you have a formula cell, you usually have two 'input' cells to give the result in the formula cell. Say you know the result, but not the value of one of the input cells. You could try guessing this, or that, but that involves way too much hassle. This is what Goal Seek is for - Excel varies the value in the input cell until the desired result is found in the formula cell. It can be quite a hard tool to explain, which is why I have a demonstration example plus three further exercises - if it hasn't been understood in the group demo, you then have a chance to run through on a one-to-one basis.
It's important to note that the first two instructions are related: "Set cell [x] to value [y]", and that cell [x] must contain a formula. The final instruction, "By changing [z]", points to a cell which must be a value. You can't set cell [x] if it's a value, and you can't change [z] if it's a formula.
If you bear these rules in mind, you will always have success with Goal Seek, even if it means you have to think pretty carefully about your [x], [y] and [z] information.
If you wish to retain your original value, click Cancel.
These are related to Goal Seek in that they allow you to test out various options. Students should make sure they are fully aware of the reason for this tool before proceeding, otherwise you end up having lots of very confused faces and a great deal of one-to-one work to do. Cell variables must be values, rather than formulae. The point is that you change these values to see the impact on the formulae, but rather than type them in manually, you can create a variety of different scenarios which you then run across the worksheet to see the impact. It's typically used for financial forecasting.
When you add a Scenario, make sure you give it a meaningful name. Some users like to create a 'reset' scenario to reset the changed data back to its original values. Be wary also of any cell values which are percentages - either enter them as a percentage (e.g. 13%) or a decimal (e.g. 0.13) - because if you don't (e.g. 13) it will be multiplied by 100 and your output/formulae cells can go a little berserk.
When you choose to display a summary report all the scenarios' variable (input) cells and the result (output/formula) cells are displayed in a table on a fresh worksheet. The only fault I can draw with this is that the row headings are cell references (the original cell references from your spreadsheet) - which is fair enough as of course Excel doesn't know which of the cells on your original sheet contain the labels - but you can rectify this by copying and pasting from the original sheet.
These are wicked fun. There are many, many more things you can do with them than at first meets the eye, although in the real world most people only create very straight-forward examples. The point is to take a 'wall' of raw data and analyse it in meaningful ways which aren't at first possible just by looking at the basic figures. Note: you can only create a pivot table from data in rows with the first row being your field (or column) headings.
Again, you shouldn't need to select the extent of your data if it's contiguous. Follow the steps of the wizard, but at the final step (Step 3 of 3, with the chequered flag) click the Layout tab to drag and drop your parameters, otherwise you'll create an empty Pivot Table and it's very hard to explain the point! If you wish to change the function which applies to your data field, then double-click on the field button to bring up a list of choices. From this dialog also, you can change the format of the number and set a variety of options. Note: you will get different options depending on whether you double-click on the field button on the right, or after you've dragged it into the Data field area.
Once you've created it (or, if they have a good attention span, before!), do point out to students that they shouldn't close the toolbar which will appear; and also that the options will close once they click out of the table. Demonstrate or ask how students can show only one or two items from the entire set of column fields or row fields. They should also become acquainted with the 'layering' and 'reordering' aspects of pivots, such that you can drop two (or more) fields into each area of the pivot table - this can be very useful but can create very cumbersome tables, especially if your raw data has numerical data in it. For this, consider grouping - which you will find from the context menu available from right-clicking directly over the field button.
Create a pivot chart (using the button on the toolbar) from your table, and continue to modify. They operate in exactly the same way but you must bear in mind that the chart comes off the back of the table, so if you modify the chart layouts, you modify the table accordingly, and vice versa. You can create a stand-alone chart by returning to the raw data, should you require.
If you return to your raw data and prepare to create a further table or chart, you will receive a message such as 'Your new report will use less memory if you base it on your existing report'. That implies that your new intended table will also be modified when you modify your original pivot table/chart combo. This is not strictly so. What happens is that Excel gathers up all the raw data and places it in a sort of 'bubble' attached to the workbook, from which it can draw down whatever information you require for your separate pivot tables/charts. If you click 'No' at this point, you are telling it to create a second - identical - 'bubble', which is pointless. If you need to analyse your data in a great deal of ways then all these identical bubbles will slow your working right down. On the other hand, if you've grouped data in one table, ungrouping it in the other will ungroup it in the first. If you refresh the data in one of your tables, all the other tables drawn down from that 'bubble' will automatically be refreshed. If you don't want this to happen, create a new bubble - i.e. click 'No' at the 'your new report will use less memory' option.
On a practical level, I've never needed to explain pivoting in that much detail. As I mentioned above, real-world uses tend to be relatively low-key. If a user has a high-level demand for pivots they tend to have other people in the office to explain exactly what detail is required and how to go about achieving it. I tend to demonstrate the more advanced features of pivoting if I need to pad out a session or if I've had learners who've specifically asked for it.
This is a nice tool to summarise data. If you've covered Subtotals your students will be familiar with the look and feel of what Outlining can provide. If manual, it's called Grouping; if automatic, it's Outlining. If grouping, then make sure your final column (or row) of each section isn't included in the selection, so that it still displays when the levels are collapsed. If outlining, make sure your data contains columns and/or rows with formulae in, otherwise it won't work. It uses the presence of these to work out where to break each level.
Like Goal Seek and Scenarios, above, it's not really used that often; however by equipping students with the knowledge of these tools' existence, they are better equipped should they ever encounter a spreadsheet which uses them. As mentioned before, for a lot of these features I train them not on the basis that the student must or should use them, but that they know more about Excel's capabilities and are left more equipped to explore in confidence should they need; and to appreciate what they find when exploring other people's spreadsheets.
When using this tool, you can bring in data from a text file, an Excel spreadsheet, an Access database, or any other tabulated data. You have the choice of splitting the text data by delimiting (tab, space or semi-colon data being the commonest forms) or by fixed width (in which case you can move the column dividers about to suit). If Access or Excel data is used you will be given the option to choose which table or sheet to import.
So what's the big deal? Surely you can just copy and paste? The point here is that information is linked, so you can refresh the imported data thus checking for changes in the source data.
The other thing you can do is bring information over from the internet. For this choose New web query, then enter the URL in the dialog box that appears. Any importable table will appear with a yellow arrow next to it. When you select it, it will turn green. The best examples for this kind of feature are exchange rate and stocks/shares sites where data changes daily. A good website to use is this exchange rate website but for heaven's sake do stress the spelling of the URL as a slip-up (for instance, D rather than S) can cause red faces!!
Use the External Data toolbar for extra choices.