Excel Overview Workshop

Microsoft's spreadsheet application is intuitive to use and has developed many advanced features which allow flexible and integrated working.

  • Useful Excel Keyboard Shortcuts
  • Filling Cells Easily
  • Hints For Better Spreadsheets Example Excel Spreadsheets (Beginner)   Example Excel Spreadsheets (Intermediate Workshop)   Example Excel Spreadsheets (Advanced Workshop)  

    Useful Excel Keyboard Shortcuts
    F1 - Help
    F2 - Edit A Cell
    F3 - Displays the paste name box
    F4 - Changes cell reference from Relative to Absolute. Click in the cell then press the key to rotate from Relative to Absolute references
    F5 - Go To a cell or range name
    F6 - Next Pane
    F7 - Spell Check
    F8 - Activate range extension feature
    F9 - Recalculate Spreadsheet
    F10 - Activate the Menu Bar
    F11 - Insert New Chart
    F12 - Save As
    Ctrl-C - Copy Selection
    Ctrl-X - Cut Selection
    Ctrl-V - Paste Selection
    Ctrl-F - Find Text
    Shift-F4 - Repeat Find
    Ctrl-H - Replace Text
    Ctrl-O - Open a File
    Ctrl-N - Create a New File
    Ctrl-P - Print File
    Ctrl-S - Save File
    Ctrl-Z - Undo Last Action
    Ctrl-F3 - Display Create a Name Box
    Ctrl-F9 - Minimize Active Worksheet
    Ctrl-F10 - Restore Active Worksheet
    Shift-F2 - Edit/Create Cell Note
    Shift-F10 - Activate the Cell Popup Menu
    CTRL ; - Enters the current date
    CTRL : - Enters the current time

    Back to Top

    Filling Cells Easily
    Quick fill - If you have entries to the left of the active cell and want to copy the cell down to the first blank in the left column, double click on the fill handle. If the range below the active cell is filled and you want to copy down, hold the Ctrl key down when you double click.

    Control drag and fill - Instead of clicking on the fill handle and holding down the left mouse button, hold down the right button instead. When you release the mouse after dragging to fill the selection, you are given options to copy, fill series, fill values, or fill formats.

    Back to Top

    Hints for Better Spreadsheets
    • Use the IF(ISERROR(cell or formula),true,false) function to specify if a field returns an error that it displays a certain string.
    • Use IF(condition),"",condition) to leave sheets tidy and uncluttered, showing only results if a condition is met. See the Example Spreadsheet BudgetAdv.xls (16k) for an example of this whereby if no figures exist in a month's column, then the subtotal cell is left blank rather than having a zero in it.
    • DoubleClick a Worksheet Tab to Highlight its name and rename it.
    • Click on a column/ row label to hghlight it entirely.

    Back to Top

    Example Excel Spreadsheets (Beginners)
    You can download Excel97 examples of the spreadsheets used in the Introduction to Spreadsheet Workshops:
    1. Golf Card (16k) - A calculating scoring card for four players
    2. Budget (16k) - A simple monthly budgeter with built-in calculating formulae
    3. Timesheet (25k) - Fill in start and end times and get the time worked
    4. Schedule (15k) - Using conditional formatting, colours in a chart and counts no of hours worked
    5. Lottery Checker (15k) - Unformatted lottery lines checker
    6. Stock Control (18k) - A skeleton stock-control sheet
    Feel free to take these and play around with them. It is best always to save a copy first (use 'Save As') so you can come back to the original if need be.

    Back to Top

    Example Excel Spreadsheets (Intermediate)
    You can download Excel97 examples of the spreadsheets used in the Intermediate Spreadsheet Workshops:
    1. Lottery Checker (15k) - Formatted version lottery lines checker with hidden columns.
    2. Fuel Usage (20k) - For recording when you buy petrol to calculate running costs.
    3. Time Logger (20k) - Adds additional hours each time, with hidden decimal equivalents.
    4. TimeTable (16k) - A train timetable with calculating journey times.
    5. Lottery Checker (15k) - Formatted version lottery lines checker with hidden columns
    6. Subtotals (18k) - Demonstrating how a simple table can be subtotalled.
    7. Gannt (65k) - A clever project management sheet with plenty of calculations to examine, hence the size!
    8. Charts 1 (29k) - How bar and line charts can be extracted from data.
    9. Pivot 1 (13k) - A simple use of a Pivot Table to summarise data.
    10. Sheet with drawing insert (16k) - This table has a part that has been copied as a picture and is labelled to show a feature of the data.
    11. Lookup Table and Pivot (16k) - Column C auto-fills itself when B is filled in by referencing B with a pre-defined table mapping initials to company.
    Feel free to take these and play around with them. It is best always to save a copy first (use 'Save As') so you can come back to the original if need be.

    Back to Top

    Example Excel Spreadsheets (Advanced)
    You can download Excel97 examples of the spreadsheets used in various Advanced Spreadsheet Workshops:
    1. Fuel (22k) - The original fuel consumption calculator, showing preferences for the user to make (eg litres or gallons)
    2. Multiple Choice (68k) - A multiple choice form using 19 groups of 5 controls allowing a 1 in 5 selection.
    3. Control (21k) - Deliberately unfinished showing how 3 worksheets can correlate with data imput.
    4. Biorhythms (58k) - A date & time entered into a form generates a chart displaying your natural highs & lows (apparently!)
    5. Buffet Ordering (36k) - A scenario where the cost of a service is calculated based upon staff & material costs and profit desired.

    Back to Top

    Excel Detailled Workshop
    Using Excel & MS Query


    KnowledgeBase Index