Spreadsheet Text and Video Tutorials

Click on a hyperlink for text, the thumbnail to see the diagram or for video tutorial:

thumbnail

Text - can be different Font , Size , Colour , Style , Alignment Numbers - can be Number , Currency , Date , Percentage Formulas 1 – Show/Unshow , Sum , Goal Seek , Fraction Example , Absolute Cells , Replicate , Copy Cell (Fill)   Formulas 2 - Countif ,  If , Conditional formatting , Charts , Data Validation , Drop Down List Rows and Columns - can be Inserted, Deleted , Resized , Hidden / Unhidden Cells - can have different Borders , Colours , Comments and Protection

There are 3 types of cell – Text, Number or Formula.  The cells can be formatted to make it easy to understand the information.

You need to be able to use the following techniques to make your spreadsheet FIT-FOR-PURPOSE for your chosen AUDIENCE

Text - can be different Font, Size, Colour, Style, Alignment

Input Text

    Type in text and press ‘Enter’.

To Format:

    Select the text then Format -> Cells -> Font, Size; Colour; Bold /Italics/Underlined; 

    Justification Left/Centred/Right.  Hint: Use arial, tahoma or verdana to be clear.

Numbers - can be Number, Currency, Date, Percentage

Input Number

    Type in Number and press ‘Enter’. 

To Format

    Format -> Cells -> Number and choose Number or Currency to 2 decimal places e.g. £22.34

Formulas 1 – Show/Unshow, Sum, Goal Seek, Fraction Example, Absolute Cells, Replicate, Copy Cell (Fill)

            Show/Unshow Formulas

                Tools -> Options -> tick/ untick formula box

            AutoSum

                Select the cell beneath/beside the cells you want to total.  Click the Σ icon on the

                formatting toolbar.

            Goal Seek

                Tools -> Goal Seek -> select cell to make your chosen value, type in value,

                choose cell to change.  Click OK. 

            Fraction Example

                Make a single cell a fraction, then use that cell in every formula.

            Absolute Cells

                Select a cell, then press F4 to change to various absolute values $A$4, A$4, $A4, A4

            Replicate

              Select a cell, then drag the little cross down/along to replicate cells

                (keeps track of the row/column you are on).

          Copy Cell (Fill)

                Select a cell, then drag down/across to select the cells you want to copy into. 

                Edit -> Fill -> Down, Series etc

Formulas 2 - Countif, If, Conditional formatting, Charts, Data Validation

            Countif

                =COUNTIF(range,criteria), eg =COUNTIF(C2:C6,$A$9)

            If

                =IF(logical_test, value_if_true, value_if_false)

            Conditional formatting

                Select a cell, Format -> Conditional Formatting -> type in condition 1

                and the format required (up to 3). Click OK.

            Charts

                Select the columns you want to chart (use CTRL and select). 

                Insert -> Chart.  Follow the 4 steps. 

            Data Validation

              Select a cell, then Data -> Validation.  Choose Settings, Input Message and Error Alert.

                Click OK.

            To create a Drop Down List

                (which is a type of validation because it avoids 'typos'):

                a. Type in your list to vertical cells (perhaps colour them white so they can't be seen).

                b. Select a cell, then Data -> Validation.  Choose Settings, Input Message.

                c.  Click on Allow and List.  Click in the source box then select your list cells.  Click OK.

Rows and Columns - can be Inserted, Deleted, Resized, Hidden / Unhidden

    To Delete or Insert:

        Select the row(s) or column(s) then either 'delete' or Insert -> Row or Column

    To Resize:

        Select the row(s) or column(s) then drag to size

    To Hide, Unhide

        Format -> Column -> autofit selection, hide, unhide, or

        Format -> Row -> autofit selection, hide, unhide

Cells - can have different Colours, Borders, Comments and Protection

    To change colours

        Select the cell(s) [use ctrl or Shift to select more than one cell]

        Colours - Format -> Cells -> Patterns -> Colour

     To choose Borders

        Select the cell(s) [use ctrl or Shift to select more than one cell]       

        Borders - Format -> Cells -> Borders, choose the type of border, style and colour

Insert Comments to help user

To Insert Comment

    Either write text in cells to explain OR click Insert -> Comment

    Type comment then click enter. 

    This adds a little red mark which prints a comment if hovered over.

To Delete Comment

    Right Click and choose Delete Comment

Protection - this means that only chosen cells can be changed by the user.

Step 1 – Choose locked cells

    Select ALL the cells by clicking CTRL and A. 

    Format -> Cells -> Protection

    Tick the box for ‘locked’ and OK

Step 2 – Select the cells you WANT the user to change

    use ctrl or Shift to select more than one cell

    Format -> Cells -> Protection

    Untick the box for ‘locked’ and OK

Step 3 – Protect Sheet

    Select Tools -> Protection -> Protect Sheet. 

    Ensure the box is ticked for Select Unlocked Cells.  Click OK

Summary diagram:

[back to top]