3. Formatting in Excel

Number formatting

There's much that can be done with number formatting. In the Format Cells dialog box the whole of the first tab is dedicated to formatting numbers in a variety of different ways.

Make your students aware that it's best to leave Excel to align things as it pleases, then you can run your eye down a column of figures and see at a glance if it's interpreted something as text - it'll have a different alignment.
Bear in mind that it's best to start off with a general intro to the concepts by the five buttons on the formatting toolbar which give a great overview. You can then play around with changing the symbol from £ to $ or what-have-you. Bear in mind that the Currency button (or so it's called according to the screen tip) actually applies the Accounting style, not the Currency style - in 2007 I see they've actually fixed the labelling. Ask your students to experiment and see if they can find out the difference between Currency and Accounting styles.

Make sure you run through how to remove a number formatting style. Point out that the format will remain 'in' the cell, even if the cell is blank (which includes formatting such as italic, or text colouring).

If you use the Percentage style, bear in mind that the formula does not need to have /100 or *100.

If you introduce your students to Date and Time formatting, you can do this in conjunction with the Date functions. Format the cell to General and ask them why they are getting a number like 39624 and explain about serial dates; it aids their comprehension of how date and time is processed in Excel.

The Fractions style is very handy when teaching numeracy, so you can demonstrate equivalent fractions/decimals.

Remember Text formatting can be created by prefixing values with a ' which will only be visible in the Formula Bar (so if your cell starts with a quotation, stick two in, otherwise one will disappear).

Don't forget that the formatting will only affect what's in the cell, rather than the entry in the Formula Bar. For instance, £50.00 will appear as 50 in the formula bar. There are a few exceptions, of course, but these are obvious.

Text and cell formatting

What I mean by 'text' formatting is basically things such as Bold, Underline, Font Colour, Alignment and Indents. You should also cover wrapping text (including introducing the wrap where you want it, rather than where Excel places it), merging cells and the Merge and Centre button, vertical alignment and orientation.

Indenting is very handy to know about if you have a column of numerical data followed by a column of text values. Some people put in a very thin blank column to increase the visual distance between the two sets of values, but this will mess up sorting and any other kind of data manipulation. Use indenting instead.

Cell formatting is application of borders and fill, both initially from the toolbar buttons and then from the Format Cells dialog box.

I tend to introduce the Borders tab round the wrong way - i.e. I start from selecting line colour at bottom right, then line style at right, then where to place it at left. That way you can layer up lots of different instructions on the same cell, such as blue left line, dotted red right line, thick green top line, pale yellow internal lines and orange bottom line. The more colours you can pile in the better: it gets the message across!

Teaching the Patterns tab is irritating, given that the drop-down for pattern closes once you've selected your pattern, so you have to reopen it to change the colour of the pattern. But it's good to show how you can layer a pattern over a fill, it just gives more choices.

You can include how to resize columns and rows to a specific width or height in this section, too.

Don't forget your format painter, of course!

All these options can be kept to the end of a session to brighten everyone up, let them play and experiment. You can use it as a filler as well.

Paste Special

This is a very underrated tool. Knowing that you can just lift the answer to a formula instead of retyping it, or switching a bunch of data from columns to rows, is so handy.

There are two ways to access it.

1. You can either use the drop-down to the right of the Paste button (which gives you access to the most commonly-used features, Paste Formulas, Values, No Borders, Transpose, and Paste Link) or
2. you can right-click in your destination cell and choose Paste Special from there. You can access many more handy features such as the option to copy the width of columns.

Incidentally, when you copy and paste from a range with hidden columns or rows, it will paste everything, hidden data included. If you just want to paste what you see, then select the data, follow Edit -> GoTo and click the Special button, then select Visible Cells Only. From there you can copy and paste as normal.

Conditional formatting

This type of formatting will only apply when certain conditions have been met, such as if a value is over a given amount. It's sometimes known as 'traffic-light formatting' because it allows up to three conditions, which are often set to red, amber and green.

You have two choices:

1. setting an individual cell to flag up when a condition applies, or
2. setting a whole row to flag up when a condition applies to a value in only one cell.

For instance, if you have a list of staff in different departments, you could set a CondFormat so that all the cells in the 'department' column containing the word 'Marketing' are in blue. Or, you could set it so that the whole row flags up in blue, not just single cells in the 'department' column, so you can easily track your eyes across and see the names of those in the Marketing department.

1. Select the whole column, bar the header label. Follow Format -> Conditional Formatting, and set your condition(s). Set your format(s) and away you go. If you want to flag up a cell containing a particular text value, then your condition will be Cell Value Is...Equal To
="[yourtext]".

 2. Select the whole range of data, bar the header row. Follow Format -> Conditional Formatting, and change 'Cell Value Is' to 'Formula Is'. In the condition panel, enter =$E2>=10, where column E contains the data you want to set the condition to, row 2 is the first row of your data (assuming that row 1 is a header row) and >=10 is your condition. Set your format, and away you go. You will need the semi-absolute on the column only, as you want only the data in column E to respond to the condition, but of course you want rows 3, 4, 5, etc to respond too.

You can get quite cunning with your CondFormatting, if you're neat. You could set up a reference cell with a MAX or MIN, or even a drop-down list containing values you want to flag up, then when a user selects from the list, the formatting will apply to those cells or rows which contain a match to the reference. You'd use 'Formula Is' and =$C$1=$B5 where $C$1 contains the drop-down list or MAX/MIN formula, and $B5 is the fixed column and first row to match in your data range.

Notes:

• When you select data for either choice 1 or 2 above, only ever select from the top to the bottom, or top left down to the bottom right. If you start at the bottom and work up, it just won't work.
• If you set a numerical condition on a range of cells with text in, your text will flag up. That's because letters are considered greater than zero (see the rules in sorting for more info).
• If your conditions are failing, check your cell refs, and check you haven't got overlapping conditions.

Data validation

You may want to restrict the type of information your users can enter into specific cells. There are essentially three things you can set here:

1. Setting the restriction

• In the first tab of your Data Validation dialog box, Settings, pick what you want from the Allow drop-down list.
• Let's say we want to allow Whole numbers, between 0 and 100.

When setting the restriction, allowing a List means that when the users click into the cell, they will see a drop-down appear.

• Start off by creating your list somewhere else on the spreadsheet.
• Choose Allow: List
• Click in the Source panel then click and drag to select the cells where your list is.
• Continue as below.

You can Name your list range, which will allow you to place it on another worksheet. If you do, make sure you type = before the list name, in the Source panel.

2. Helping your users input the right data

• In the second tab of your Data Validation dialog box, Input Message, you can create a little message that will pop up to help users when they click into the cells.
• The Title will appear in bold. It's optional.
• The Input message should be as explicit as possible. Trick: Don't let your users get to the third step and get cross! Create a comprehensive message to start off with, to avoid their frustration. Remember, if you don't, you'll get it in the neck.

3. Telling your users off when they get it wrong

• In the third tab of your Data Validation dialog box, Error Alert, you can create a little message that will pop up to tell users they've got it wrong.
• From the Style drop-down, choose Information first. (Later, amend to Warning, and then to Stop. Get your students to play around with these three settings and see exactly what it allows you to do - whether it will let you override the restriction or force you to behave.)
• The Title is optional - you know where 'Data Validation' appears across the top of the Data Validation dialog box? That's the title. Not many people notice that, so I tend not to bother writing something here.
• The Error message bit is fun. Remind students that you have a delicate constitution and you won't stand for anything rude. ;-)

Protecting cell entries

You may want to prevent your users from being able to enter data in specific cells. To do this requires two steps:

1. Locking/unlocking

• By default (if you were to skip this and go straight to step 2) all cells in a worksheet are locked. That means that you can't type in them or alter the information in any way. Usually you'd want to allow access to only a small number of cells, so you unlock these before applying protection. (If it were the other way round, it would be excessively cumbersome - for instance, say you only want your users to be able to type in 10 cells in the whole worksheet. There'd be 16.75 million cells - minus the ten you want to be able to write in - to lock. Eek!)
• To unlock the cells, select the cells first, then go to Format -> Cells. On the Protection tab, take the tick out of the 'Locked' box.
• Incidentally, 'Hidden' refers to formulae - if you choose this and then protect the worksheet, the cell's formula will not be visible in the Formula Bar.

2. Protecting

• Go to Tools -> Protection -> Protect Worksheet.
• Depending on the version of Excel you're using, you can set a variety of different access permissions. I would advise that you allow users to select locked cells, which basically means they can still click in locked cells. If they can't click in locked cells, they'll keep calling Helpdesk saying that their spreadsheet's broken.
• You can leave the password panel completely empty, and it will still protect the worksheet. Most people don't know to look here, so it's still relatively secure.
• Exercise caution when setting passwords. If you forget it, your Helpdesk will laugh at you for being a numpty (which is only fair enough when you see what else they have to deal with in a day's work). If you're really desperate, you can use the services of data-recovery companies, who will charge around £5,000 to unlock your workbook or worksheet. If you're brave, you can find online solutions, but they involve macros and unless you know what you're doing, you're risking untold damage to your data. Not only that, but it can involve installing unauthorised software which is contrary to most companies' use-of-IT policies. You have been warned.

To protect an entire workbook, you have a choice: You can prevent anyone but password-holders from looking at the data ('password to open'), or you can allow non-password-holders to look at the data but not make changes to it ('password to modify'). To be honest, if you want to let people see but not touch, print it out or turn it into a pdf.

Either way, go to File -> Save As and in the dialog box, at the top, is a button saying Tools. Choose General Options and you can set one or both of the password settings above.

There is a saying in IT: Make something idiot-proof, and you get a better class of idiot. Do, do, do test your spreadsheet out on people before letting it out into the wild!

Be warned about doing this, though - see above under step 2 Protecting.

Custom number formatting

Some number formats aren’t already found in Excel, e.g. dd/mm/yy. We can base our custom format on an existing format. Go to Format -> Cells and on the Number tab click in the Custom category. You may find what you're looking for here.

If not, pick the nearest format and modify it - just above the scroll-panel is a small panel with your chosen format in, which you can modify there. Don't forget to have a look at your sample, just above, to see how it's looking.

Date formatting can be modified in several ways.

• A single letter will display only the number of the day or month (e.g. d/m/y will show 4/3/08).

• A pair of letters will display a 'leading zero' (e.g. dd/mm/yy will show 04/03/08).

• Three letters will display the abbreviation of the day or month (e.g. ddd/mmm/yyy will show Tue/Mar/2008).

• If you have dddd, dd mmmm yyyy this will show Tuesday, 04 March 2008.

• Various other combinations can be made from varying the number of letters, spaces, commas, slashes, dashes or whatever.

Number formatting is a little more tricky to follow. It's broken down into four sections and uses a variety of symbols to display or hide values.

• The four sections are separated by a semi-colon, and correspond to what you want to do to the following types of value:
      positive;    negative;    zero;    text

• For example:
      #,##0;-#,##0;“Nil”;General
  shows that firstly we want a comma to separate the thousands in the positive numbers; that negative numbers start with a minus symbol and have a comma to separate the thousands; that if there is nothing in the cell we want it to say 'Nil'; and lastly if there is text in the cell to display it as per the General number format.

• It's possible to have only a single instruction, pertaining to positive numbers only. For instance, a code of 00000 000 000 will display telephone numbers with a leading zero and two spaces, e.g. 01234 567 890.

The 0s and #s mean the following:

• 0 – if there is no figure in the cell, display a 0

• # - if there is no figure in the cell, display nothing at all
  e.g. if you wanted 450.8 to display as 450.800 you would choose ##0.000 to fix the number of decimals.

Notes:

• If you want a cell to display text, don't forget to enclose it in double-quotes.

• If you want a value to appear in a certain colour, precede the instruction with a colour code in square brackets, i.e. [Red]#,##0.0. You have a choice of red, blue, yellow, black, white, cyan, magenta, green.

• If you ever see numbers indented or set in from the edge of the cell, it's likely that a 'buffer' code has been used, to insert a gap of a specified width. This is done by, for instance, #,##0_);(#,##0) - the second instruction tells Excel to place negative numbers in brackets, so in order for the positive numbers to line up the same way in the cells, the underscore in the first instruction tells Excel to insert a gap, of one bracket-width. Try it and see!